Нестерова Практикум 2011 Информатика

Ширина столбцов не подстраивается автоматически. Способы изменения ширины столбцов и высоты строк приведены в прил. 4.

Возможности MS Excel позволяют весьма просто создавать самые разные пользовательские таблицы. Создание пользовательских таблиц предполагает два этапа:

1)ввод шапки и данных таблицы,

2)форматирование шапки и данных таблицы.

Условно все заголовки таблицы можно разбить на подуровни (рис. 8.3). Сначала нужно ввести общий заголовок шапки таблицы, а затем названия полей уровня 1, уровня 2 и т.д. Заголовки одного уровня должны находиться в одной строке и следовать друг за другом.

Ксредствам форматирования относятся следующие элементы:

1)формат числа,

2)шрифт,

3)выравнивание и направление содержимого ячейки,

4)обрамление ячеек,

5)заполнение фона ячеек,

6)защита ячеек.

Общее правило форматирования выглядит следующим образом: 1)выделить область ячеек;

2)выбрать из вкладки Главная группы Ячейки команду: Формат и да-

3)задать параметры форматирования в диалоговом окне Формат яче-

ек.

Рис. 8.3. Пример построения заголовков таблицы

Обработка данных, хранимых в ячейках рабочих листов Excel, осуществляется по формулам, определенным пользователем.

121

Формулы представляют собой выражения, по которым выполняются вычисления. Формула всегда начинается со знака равно (=) и может включать функции, ссылки на ячейки или имена, операторы и константы.

Например, в формуле «=СУММ(В2:В8)*30»: «СУММ()» – функция; «В2» и «В8» – ссылки на ячейки; «:» (двоеточие) и «*» (звездочка) – операторы; «30» – константа.

Функции – заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Структура функции: имя функции, открывающая скобка, список аргументов, разделенных точками с запятой, закрывающая скобка. Аргументом функции может быть число, текст, логическое значение, массив, значение ошибки, ссылка на ячейку. В качестве аргументов используются также константы, формулы или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.

Ссылка указывает на ячейку или диапазон ячеек листа, которые требуется использовать в формуле. Можно задавать ссылки на ячейки других листов текущей книги и на другие книги. Ссылки на ячейки других книг называются связями.

Оператором называют знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок.

Константой называют постоянное (невычисляемое) значение. Формула и результат вычисления формулы константами не являются.

Таблицу, как правило, следует подготовить к печати. Режим просмотра Разметка страницы обеспечивает отображение документа в таком виде, в каком он будет напечатан. В этом режиме можно настроить параметры страниц, создать и оформить колонтитулы, установить особенности печати документа. Однако в некоторых случаях может потребоваться предварительный просмотр документа перед печатью.

По завершении текущего сеанса работы Выход из Excel осуществляется с помощью команды: Закрыть кнопки Office или сочетанием клавиш

Alt+F4.

?Вопросы для самоподготовки

1.Перечислите основные элементы рабочей книги Excel 2007.

2.Как можно подобрать ширину столбца В по содержимому наибольшей ячейки в столбце?

3.Какую команду необходимо выполнить, чтобы изменить ширину всех столбцов чистого листа?

4.Каким символом отделяются при вводе чисел десятичные знаки?

5.Какие элементы можно использовать для выбора шрифта?

6.Для какой цели можно использовать абсолютные ссылки в формулах?

122

7.С помощью какого элемента можно вызвать Мастер диаграмм?

8.Какие символы можно использовать в формулах в качестве знака умножения?

9.С помощью каких формул можно определить 25 % от числа, помещенного в ячейку A1?

10.Что означает знак $ в строке формул?

11.Что означает запись =СУММ(В2:В4) в строке формул?

Рекомендуемая литература

[1, подраздел 2.6]; [2, глава 2, §7]; [3, подраздел 3.5]

Порядок выполнения

Задача: Вычисление показателей производства

Предприятие имеет заказ на выпуск некоторого вида продукции, план выпуска которой определен для каждого месяца календарного года.

Необходимо выполнить расчет и анализ показателей производства по месяцам, а также итоговые и статистические расчеты за год.

Для каждого месяца в таблице необходимо отразить:

•план выпуска;

•фактически выпущенное количество;

•процент выполнения плана;

•отношение продукции, выпущенной за месяц, к годовой (доля месяца в годовом выпуске).

По приведенным в таблице данным необходимо выполнить анализ итогов работы предприятия за год: определить и представить в таблице итоговые данные за год и другие статистические показатели.

Задача решается в четыре этапа:

1)заполнение таблицы исходными данными;

2)расчет и анализ итогов работы предприятия;

3)оформление таблицы «Показатели производства»;

4)графическое представление данных таблицы.

На первом этапе задается форма таблицы, т.е. определяется назначение и необходимое число строк и столбцов, даются имена объектам таблицы; таблица заполняется исходными данными для расчета.

На втором этапе в таблицу вводятся формулы для расчетов по месяцам и за год. Таблица дополняется формулами для статистических расчетов.

На третьем этапе выполняются действия, позволяющие улучшить внешний вид таблицы: форматирование заголовков и данных таблицы, проведение линий и рамок, изменение шрифтов, окрашивание отдельных ячеек или блоков ячеек.

На четвертом этапе рассматриваются различные способы графического представления данных таблицы.

123

Этап 1. Заполнение таблицы исходными данными:

1.Сохраните создаваемую таблицу в папке Мои документы / Группа / Фамилия, дав файлу название: «Лабораторная работа № Группа Фамилия», например: «Лабораторная работа 8 412 Иванов.xlsx». Не забывайте во время работы регулярно, каждые 2-3 минуты, сохранять файл (нажатием сочетания клавиш на клавиатуре Ctrl+S) во избежание потери результатов работы.

2.На листе 1 введите в ячейку А1 заголовок таблицы Показатели про-

изводства.

3.Выделите диапазон ячеек A4:G4 и подготовьте его для ввода заголовков столбцов таблицы, занимающих несколько строк. Для этого из

вкладки Главная группы Ячейки выбором команды: Формат вызовите диалоговое окно Формат ячеек .

4.В окне Формат ячеек выберите вкладку Выравнивание, а в ней — выравнивание по горизонтали – по центру, по вертикали – по центру и

выберите режим переноса по словам.

5.После того, как это сделано, введите в ячейки В4, D4, Е4, F4, G4 названия для столбцов таблицы соответственно: Месяцы, План выпуска,

Фактически выпущено, Процент выполнения плана, Выполнено, %, к фактически выпущенному за год.

6.Измените ширину столбцов таким образом, чтобы заголовки столбцов таблицы приняли такой же вид, как показано на рисунке. Для этого подведите курсор мыши к вертикальному разделителю между заголовками столбцов рабочего листа, например к разделителю между столбцами G и Н. Курсор изменит форму и примет вид крестика с горизонтальными

стрелками . Нажмите ЛКМ и, не отпуская ее, передвиньте этот разделитель вправо или влево до необходимой ширины, затем отпустите кнопку мыши:

7.Проделайте аналогичные действия, изменив высоту строки 4 до нужного размера, используя разделитель строк между строками 4 и 5.

8.Заполните три столбца под общим заголовком «Месяцы» (рис. 8.4), выполняя последовательно действия в соответствии с пунктами 9-11. На первый взгляд эти столбцы дублируют друг друга, так как обозначают все-

124

го лишь по-разному графически одни и те же временные промежутки, однако существенное отличие все же есть – здесь использованы альтернативные способы работы в режиме Автозаполнение: построение число-

вых рядов, рядов из дат, использование стандартного списка. Поэтому рекомендуется в учебных целях применять все три способа отработки навыков в этом режиме.

Рис. 8.4. Результат выполнения пунктов 9-11

9.В диапазоне ячеек А5:А16 (рис. 8.4) постройте числовой ряд в режиме Автозаполнение с шагом, равным 1, и значениями от1 до12. Для этого:

9.1.Введите первое число ряда в первую ячейку диапазона А5;

9.2.Нажмите и удерживайте нажатой клавишу Ctrl;

9.3.Установите курсор мыши на маркер заполнения (маленький черный квадратик в правом нижнем углу ячейки) ячейки с первым числом ряда. Курсор должен принять вид тонкого черного крестика (в правом верхнем углу крестика маленький черный плюс);

9.4.Нажмите ЛКМ и потяните, выделив диапазон до последней ячейки выделяемого диапазона А16;

9.5.Отпустите ЛКМ;

9.6.Отпустите клавишу Ctrl

10.В диапазоне ячеек В5:В16 постройте ряд из дат последних чисел каждого месяца:

10.1.Наберите в ячейках В5 и В6 даты 31.01.99. и 28.02.99. соответст-

венно;

125

10.2.Выделите диапазон ячеек В5:В6, установите курсор мыши на маркер заполнения и протяните его до ячейки В16 включительно, распространяя закономерность на весь диапазон. В диапазоне ячеек В5:В16 будет образован ряд из дат от 31.01.99. до 31.12.99.

11.Для диапазона ячеек С5:С16 воспользуйтесь стандартным списком из названий месяцев:

11.1.Наберите в ячейке С5 текст Январь или Янв;

11.2.Выделите ячейку С5 и установите курсор мыши на маркер запол-

нения;

11.3.Нажмите ЛКМ и протяните ее до ячейки С16 включительно; отпустите кнопку мыши. В диапазоне ячеек С5:С16 будет образован ряд из названий месяцев.

12.Заполните диапазон ячеек D5:D16 числами, соответствующими плану выпуска продукции: 2340; 3200; 2800; 3000; 3100; 2500; 2600; 3000; 3200; 3000; 2800; 3240.

13.Заполните диапазон ячеек Е5:Е16 числами, соответствующими фактическому выпуску продукции: 2000; 3200; 3000; 3100; 3200; 2400; 2800; 3200; 3200; 3100; 3000; 3300.

14.Сохраните текущее состояние таблицы.

Этап 2. Расчет и анализ итогов работы предприятия

На этапе 2 таблица заполняется всеми необходимыми формулами для расчетов. Сначала производится расчет суммарных значений по плану выпуска и факту за год, а также процент выполнения плана по месяцам и доли фактического выпуска по месяцам в годовом выпуске. В последнюю очередь выполняются статистические расчеты: вычисляются наибольшее, наименьшее и среднее значение фактического выпуска по месяцам в абсолютных значениях и в процентах. При вводе формул рекомендуется ис-

пользовать Мастер функций в строке формул. Далее приведена последовательность действий этапа 2.

1.В ячейке В18 наберите текст Итого за год:

2.В ячейке D18 необходимо получить значение планового задания по выпуску на год — сумму значений в диапазоне D5:D16. Для этого лучше всего воспользоваться режимом Автосуммирования. Для этого:

2.1.Выделите ячейку D18;

2.2.Сделайте щелчок по кнопке Автосумма в группе Редактирование вкладки Главная. В ячейке и в строке формул появится форму-

126

ла: =СУММ(D5:D17). При этом аргумент функции СУММ будет выделен цветом, а диапазон D5:D17 обрамлен бегущей рамкой:

2.3. Измените аргумент функции СУММ на диапазон D5:D16; для этого поместите курсор мыши в ячейку D5 (он примет вид широкого белого крестика), нажмите левую кнопку и протяните курсор до ячейки D16 включительно, отпустите кнопку мыши. Теперь бегущей рамкой обрамлен диапазон D5:D16, и он же стал аргументом функции СУММ:

2.4. Подтвердите ввод формулы.

127

3. В ячейке Е18 для вычисления значения суммы фактически выпушенной продукции за год аналогичным образом постройте формулу

=СУММ(Е5:Е16).

4.Введите в ячейку F5 формулу для вычисления процента выполнения плана за месяц: =E5/D5. Внимание! Не набирайте на клавиатуре адреса ячеек, а получайте их щелчком левой кнопки мыши на соответствующей ячейке:

5.Выделите ячейку F5 и выполните автоматическое заполнение формулами диапазона ячеек F5:F16, используя маркер заполнения:

128

6. Выполните форматирование диапазона ячеек F5:F16, представив данные в процентном формате. Для этого выделите диапазон F5:F16 и нажмите кнопку Процентный формат во вкладке Главная группа Число, а затем дважды нажмите кнопку Увеличить разрядность (это позволит вывести два десятичных знака в значении процента):

7. В ячейку F18 введите формулу для вычисления процента выполне-

ния плана за год =E18/D18 .

8. Отформатируйте ячейку F18 в процентном формате с двумя цифрами дробной части. Это можно сделать описанным выше способом или скопировать формат, используя кнопку Формат по образцу (форматная кисть):

9. В диапазоне ячеек G5:G16 предстоит вычислить для каждого месяца его долю (в процентах) в годовом выпуске, которая рассчитывается как отношение выпущенного в каждом месяце к выпущенному за год. Так как во всех формулах диапазона в качестве делителя выступает одна и та же ячейка (сумма за год), то в формуле адрес этой ячейки должен быть задан

129

в виде абсолютной ссылки, чтобы он не изменялся при копировании формулы:

9.1. Формула в ячейке G5 должна иметь вид: =Е5/$Е$18. Для получения абсолютной ссылки $E$18 необходимо поместить в формулу ссылку Е18 и затем нажать клавишу F4:

9.2. Выделите ячейку G5 и выполните автоматическое заполнение формулами диапазона ячеек G5:G16, используя маркер заполнения. Выполните форматирование диапазона G5:G16 в процентном формате с двумя цифрами дробной части:

130