Задача показатели производства

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

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

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

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

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

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

Разделим решение ЗАДАЧИ 1 на этапы:

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

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

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

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

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

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

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

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

Таб. 2. Показатели производства

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

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

2. Сохраните создаваемую таблицу в личной папке, дав файлу подходящее имя, например, works (будет создан файл works.exe). Не забывайте регулярно сохранять файл во избежание потери результатов работы.

3. Выделите диапазон ячеек А4:G4 и подготовьте его для ввода заголовков столбцов таблицы, занимающих несколько строк. Для этого из строки меню или контекстного меню вызовите диалоговое окно Формат ячеек, выберите вкладку Выравнивание, а в ней - выравнивание по центру по горизонтали и по вертикали и режим переноса по словам. После того как это сделано, введите в ячейки В4, D4, Е4, F4, G4 названия для столбцов таблицы (соответственно):

Месяцы, План выпуска. Фактически выпущено,

Процент выполнения плана,

Выполнено в % к фактически выпущенномуза год.

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

5. Далее необходимо заполнить три столбца под общим заголовком «Месяцы» (см. рис. 35.). И хотя на первый взгляд эти столбцы дублируют друг друга, всего лишь по-разному графически обозначая одни и те же времен­ные промежутки, существенное отличие все же есть - здесь использованы альтернативные способы работы в режиме Автозаполненчя: построение числовых рядов, рядов из дат, использование стандартного списка (см. раздел 4). Поэтому рекомендуется в учебных целях применить все три способа, отрабатывая навыки работы в этом режиме.

6. В диапазоне ячеек А5:А16 постройте числовой ряд со значениями от 1 до 12.

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

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

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

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

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

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

9. Заполните диапазон ячеек D5:D16 числами, соответствующими плану выпуска продукции. В качестве контрольного примера рекомендуется использовать данные из таблицы на рис. 35.

10. Заполните диапазон ячеек Е5:Е16 числами, соответствующими фактическому выпуску продукции. Их с целью контроля также возьмите из таблицы 2.

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

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

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

При вводе формул пользуйтесь эффективными средствами ввода: создайте панель Знаки операций, ссылки на ячейки получайте по щелчку мыши, для ввода в формулу функции используйте Мастер функций.

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

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

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

• Щелкните на кнопке Автосумма на панели Стандартная. В ячейке и в строке формул появится формула =СУММ(D5:D17)

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

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

3. В ячейке Е18 для вычисления значения суммы фактически выпущен­ной продукции за год аналогичным образом постройте формулу =СУММ(Е5:Е16)

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

=Е5/D5 .

Внимание! Не набирайте на клавиатуре адреса ячеек, а получайте их щелчком левой кнопки мыши на соответствующей ячейке.

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

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

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

=Е18/D18

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

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

=Е5/$Е$18

Для получения абсолютной ссылки $Е$ 18 необходимо поместить в форму­лу ссылку Е18 и затем нажать клавишу F4.

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

10. В ячейку В20 введите текст Максимально за месяц.

11. В ячейку В21 введите текст Минимально за месяц.

12. В ячейку В22 введите текст В среднем за месяц.

13. В ячейке Е20 с помощью Мастера функций постройте формулу

=МАКС(Е5:Е16)

выбрав функцию МАКС либо из 10 недавно использовавшихся, либо в кате­гории Статистические.

14. В ячейке Е21 с помощью Мастера функций постройте формулу

=МИН(Е5:Е16)

15. В ячейке Е22 с помощью Мастера функций постройте формулу

=СРЗНАЧ(Е5:Е16)

16. Выделите диапазон ячеек Е20:Е22 и скопируйте его на диапазон F20:F22, используя маркер заполнения.

17. Для ячейки Е22 установите числовой формат вывода целых чисел (без цифр дробной части); для этого воспользуйтесь кнопкой Уменьшить разрядность панели Форматирование.

18. Для диапазона F20:F22 установите процентный формат с двумя цифрами дробной части.

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