Для выполнения этого задания удобно воспользоваться сводной таблицей.
1 Разработать макет таблицы, занести исходные данные и расчетные формулы в ячейки электронной таблицы.
2 Для агрегирования данных выбрать пункт меню Данные → Сводная таблица…
Рис. 10 Форматирование таблицы и ввод исходных данных
3 На первом шаге работы мастера сводных таблиц необходимо указать источник данных, которым может являться список или база данных MS Excel, внешний источник данных, диапазоны консолидации или другая сводная таблица. Также надо указать вид отчета: сводная таблица или сводная диаграмма. В нашем примере источник данных – список MS Excel, вид отчета – сводная таблица.
4 Для перехода к следующему шагу работы мастера нажать кнопку «Далее >».
5 На втором шаге работы мастера требуется указать диапазон, содержащий исходные данные. Диапазон можно задать в строке ввода с клавиатуры, указав адрес левой верхней ячейки и через двоеточие – адрес правой нижней ячейки диапазона, можно выделить диапазон с помощью мыши непосредственно в таблице. Если окно мастера мешает выделению его можно свернуть, нажав на кнопку «Свернуть окно» рядом с полем ввода, а затем восстановить окно мастера при помощи кнопки «Развернуть окно». Если данные находятся в другом файле нужно предварительно открыть его, используя кнопку «Обзор…», расположенную на окне мастера. В нашем примере надо задать диапазон A1:G9, т. е. всю рабочую область листа. После того, как диапазон задан, перейти к следующему шагу, нажав на кнопку «Далее >».
6 На последнем шаге работы мастера необходимо указать, где будет размещена сводная таблица – на новом листе книги или на существующем листе. Если сводная таблица размещается на существующем листе, то в поле ввод требуется задать ссылку на ячейку, с позиции которой будет вставлена таблица. Для нашего примера разместим таблицу на новом листе.
|
|
7 Также на третьем шаге работы мастера можно создать макет сводной таблицы. Для этого надо нажать кнопку «Макет…» в окне мастера.
8 Для формирования макета необходимо перетащить кнопки полей в области сводной таблицы. Для нашего примера: в область страниц перетащим дату, в строки таблицы – наименование товара, в столбцы – поставщика, в область данных – количество и сумму (по их значениям будет проводиться агрегирование, по умолчанию – суммирование).
Рис. 11 Макетирование сводной таблицы
9 Для завершения работы мастера нажать кнопку «Готово». В результате на новом листе рабочей книги исходные данные будут агрегированы в форме сводной таблицы.
10 Для управления сводной таблицей можно воспользоваться кнопками панели инструментов «Сводные таблицы». Если она отсутствует на экране, необходимо выбрать пункт меню Вид → Панели инструментов → Сводные таблицы.
11 Если требуется изменить операцию агрегирования данных в сводной таблице надо установить курсор в поле данных для которого изменяется операция и нажать кнопку «Параметры поля» на панели инструментов сводной таблицы. Будет открыто окно вычисления полей сводной таблицы.
12 В этом окне надо выбрать необходимую операцию агрегирования (например, количество значений, среднее значение и т. д.) и нажать кнопку ОК.
13 Для отображения агрегированных данных по конкретной дате нажать на кнопку списка рядом с полем «Дата» (область страницы сводной таблицы) и выбрать интересующую дату.
|
|
14 Можно скрыть отдельные строки и столбцы сводной таблицы. Для этого надо нажать на кнопку списка в заголовочной части столбцов или строк сводной таблицы и снять пометки выделения для скрываемых данных в появившемся списке.
15 Если исходные данные в списке были изменены, то для обновления значений в сводной таблице надо нажать кнопку «Обновить данные».
Рис. 12 Управление сводной таблицей
16 Сохранить результаты в файле, выбрав пункт меню Файл → Сохранить и указав имя файла.
ДОПОЛНИТЕЛЬНЫЕ ЗАДАНИЯ
1 Используя справочную систему MS Excel познакомьтесь со встроенными функциями для агрегирования итогов в сводной таблице.
2 Познакомьтесь с возможностями сортировки данных в таблице (пункт меню Данные → Сортировка).
3 Познакомьтесь с возможностями агрегирования данных на основе промежуточных итогов (пункт меню Данные → Итоги). Имейте в виду, что для вычисления промежуточных итогов необходимо сгруппировать строки, по которым нужно подвести итоги. После этого можно подсчитать промежуточные итоги любого столбца, содержащего числа.
4 Познакомьтесь с возможностями агрегирования данных на основе консолидации данных (пункт меню Данные → Консолидация).
ЗАДАНИЯ ПО ВАРИАНТАМ
1 Имеется перечень основных средств машиностроительного завода:
| Наименование основных средств | Группа основных средств | Сумма, тыс.руб. |
| 1 Конторские шкафы | ||
| 2 Котельные установки | ||
| 3 Воздушные линии электропередач | ||
| 4 Здание заводоуправления | ||
| 5 Паровые турбоагрегаты | ||
| 6 Пишущие машинки | ||
| 7 Цементопровод |
Продолжение
|
|
| 8 Энергетические установки | ||
| 9 Отбойные молотки | ||
| 10 Путепровод магистральный | ||
| 11 Маршрутно-контрольное устройство | ||
| 12 Персональный компьютер | ||
| 13 Верстаки | ||
| 14 Мост железобетонный | ||
| 15 Гидроагрегаты | ||
| 16 Станок рельсобалочный | ||
| 17 Вагоны пассажирские | ||
| 18 Контрольно-измерительная аппаратура | ||
| 19 Машины для монтажной сварки | ||
| 20 Автопогрузчики | ||
| 21 Приборы для изменения давления | ||
| 22 Дымососы | ||
| 23 Отстойники | ||
| 24 Здание сборочного корпуса | ||
| 25 Сети водопроводные | ||
| 26 Кабельная линия связи | ||
| 27 Трактор гусеничный | ||
| 28 Автомат кузнечно-прессовый | ||
| 29 Вибраторы | ||
| 30 Станок металлорежущий | ||
| 31 Мост железнодорожный | ||
| 32 Здание склада готовой продукции | ||
| 33 Инструмент разный |
Проведите группировку основных средств по видам и определите стоимость основных средств в каждой группе.
Пояснения: для нахождения суммарных и средних значений используйте операцию консолидации данных (меню Данные → Консолидация).
2 Решите задачу № 1, используя операцию вычисления промежуточных итогов.
Пояснения: отсортируйте таблицу по полю группировки, используя операцию сортировки (меню Данные → Сортировка). Для нахождения суммарных или средних значений используйте операцию вычисления промежуточных итогов (меню Данные → Итоги).
3 Имеются следующие данные по работникам склада:
| Табельный номер | ФИО | Профессия | Разряд | Оклад, руб. |
| Иванов А.П. | Кладовщик | |||
| Крылов А.Р. | Грузчик | 1140,64 | ||
| Колесов В.И. | Грузчик | |||
| Соколов Р.В. | Уборщик | 832,36 | ||
| Смирнов И.А. | Кладовщик | 1140,64 | ||
| Михайлов П.Р. | Грузчик |
Определите число работников каждого разряда, средний и суммарный заработок рабочих каждой профессии.
Пояснения: для нахождения суммарных и средних значений используйте операцию консолидации данных (меню Данные → Консолидация).
4 Решите задачу № 3, используя операцию вычисления промежуточных итогов.
Пояснения: отсортируйте таблицу по полю группировки, используя операцию сортировки (меню Данные → Сортировка). Для нахождения суммарных или средних значений используйте операцию вычисления промежуточных итогов (меню Данные → Итоги).
5 Имеются данные о передаче материалов в производство в течение месяца:
| Дата | Наименование материала | Цена, руб. | Количество, кг. | Сумма, руб. |
| 1.03 | Болты стальные | 2,0 | ||
| 1.03 | Листы алюминиевые | 5,3 | ||
| 2.03 | Болты латунные | 1,6 | ||
| 3.03 | Пруток медный | 6,2 | ||
| 6.03 | Медь листовая | 3,2 | ||
| 6.03 | Болты латунные | 4,1 | ||
| 9.03 | Болты стальные | 5,6 | ||
| 12.03 | Медь листовая | 2,3 | ||
| 18.03 | Болты латунные | 3,9 | ||
| 18.03 | Пруток медный | 6,1 | ||
| 22.03 | Листы алюминиевые | 1,8 | ||
| 25.03 | Болты стальные | 5,4 | ||
| 28.03 | Пруток медный | 3,8 | ||
| 28.03 | Листы алюминиевые | 4,7 |
Определите среднюю цену за месяц по каждому виду материалов, материальные затраты цеха (по видам материалов) в натуральной и стоимостной оценке.
Пояснения: для нахождения суммарных и средних значений используйте операцию консолидации данных (меню Данные → Консолидация).
6 Решите задачу № 5, используя операцию вычисления промежуточных итогов.
Пояснения: отсортируйте таблицу по полю группировки, используя операцию сортировки (меню Данные → Сортировка). Для нахождения суммарных или средних значений используйте операцию вычисления промежуточных итогов (меню Данные → Итоги).
7 Имеются сведения о производстве продукции цехом по месяцам:
| Месяц | Продукция | Выпуск, шт. | Затраты на единицу продукции, тыс.руб. | Затраты на выпуск, тыс.руб. |
| Январь | А100 | 12,5 | ||
| А101 | 3,5 | |||
| А140 | 10,0 | |||
| А200 | 5,2 | |||
| А201 | 2,0 | |||
| Февраль | А100 | 12,2 | ||
| А101 | 3,5 | |||
| А140 | 10,0 | |||
| А200 | 5,5 | |||
| А201 | 2,0 | |||
| Март | А100 | 12,2 | ||
| А101 | 3,6 | |||
| А140 | 10,0 | |||
| А200 | 5,2 | |||
| А201 | 2,3 |
Рассчитайте затраты цеха на выпуск каждого вида продукции по месяцам. Определите общий объем выпуска по видам продукции, суммарные затраты по месяцам, средние затраты на производство единицы каждого вида продукции.
Пояснения: для нахождения суммарных и средних значений используйте операцию консолидации данных (меню Данные → Консолидация).
8 Решите задачу № 7, используя операцию вычисления промежуточных итогов.
Пояснения: отсортируйте таблицу по полю группировки, используя операцию сортировки (меню Данные → Сортировка). Для нахождения суммарных или средних значений используйте операцию вычисления промежуточных итогов (меню Данные → Итоги).
9 Имеются данные о продаже макаронных изделий основных производителей в регионе:
| Производитель | Продукция | Объем продаж, т./год |
| ОАО «ЛИМАК» | Макаронные изделия в/с | |
| Макаронные изделия 1 с. | ||
| Макаронные изделия 2 с. | ||
| ОАО «МАКФА» | Макаронные изделия в/с | |
| Макаронные изделия 1 с. | ||
| Макаронные изделия 2 с. | ||
| ПБОЮЛ Кокин А.В. | Макаронные изделия в/с | |
| Макаронные изделия 1 с. | ||
| Макаронные изделия 2 с. |
Определите емкость рынка макаронных изделий в регионе, емкость рынка по видам макаронных изделий, рыночные доли основных производителей.
Пояснения: Для агрегирования данных используйте сводную таблицу (меню Данные → Сводная таблица).
ЛАБОРАТОРНАЯ РАБОТА № 4
РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ В MS EXCEL
Для решения задач линейного и нелинейного программирования в MS Excel удобно использовать надстройку «Поиск решения». Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.
ЗАДАНИЕ
Бройлерное хозяйство птицеводческой фермы насчитывает 20000 цыплят, которые выращиваются до 8-недельного возраста и, после соответствующей обработки, поступают в продажу. Хотя недельный расход корма для цыплят зависит от их возраста, в дальнейшем будем считать, что в среднем (за 8 недель) он составляет 1 фунт.
Для того чтобы цыплята достигли к восьмой неделе необходимых весовых кондиций, кормовой рацион должен удовлетворять определенным требованиям по питательности. Этим требованиям могут соответствовать смеси различных видов кормов, или ингредиентов. В качестве ингредиентов рассмотрим три: известняк, зерно и соевые бобы. Требования к питательности рациона сформулируем, учитывая три вида питательных веществ: кальций, белок и клетчатку. Известны данные, характеризующие содержание (по весу) питательных веществ в каждом из ингредиентов и удельную стоимость каждого ингредиента:
| Ингредиент | Содержание питательных веществ, фунт/(фунт ингредиента) | Цена, долл./фунт | ||
| кальций | белок | клетчатка | ||
| Известняк | 0,38 | - | - | 0,04 |
| Зерно | 0,001 | 0,09 | 0,02 | 0,15 |
| Соевые бобы | 0,002 | 0,50 | 0,08 | 0,40 |
Смесь должна содержать:
– не менее 0,8%, но не более 1,2% кальция;
– не менее 22% белка;
– не более 5% клетчатки.
Требуется определить для птицеводческой фермы количество (в фунтах) каждого из трех ингредиентов, образующих смесь минимальной стоимости при соблюдении требований к общему расходу кормовой смеси и ее питательности.