Теория Excel_0. Решение задач оптимизации исследование влияния разных факторов на данные
 Скачать 474.43 Kb.
|
Консолидация данныхПроцедура консолидации (объединения) позволяет создать новую таблицу – сводку на основе данных опорной таблицы по заданной категории данных. При выполнении консолидации пользователь должен задать также тип функции, по которой будут вычисляться итоговые значения. Рассмотрим консолидацию данных на примере таблицы на рис. 3.3. В качестве категории выберем количество каждого вида продукции, реализованного через все торговые точки. Выделить положение итоговой таблицы: например, ячейка B22. Выполнить команду меню: Данные→Консолидация. В окне Консолидацияв списке Функцияуказать функцию «Сумма». Кроме суммирования, в окне функций присутствует еще более 10 функций. Установить курсор в строку Ссылка. Выделить первую исходную область. В нашем примере она находится в диапазоне B5:E8. Рамка выделения – пунктир. Нажать в окне Консолидациякнопку Добавить. Повторите действия, описанные в пп. 4, 5 и 6, для диапазонов B10:E13 и B16:E19. Установите флажок «в левом столбце» и нажмите кнопку ОК. Полученная в результате выполнения консолидации итоговая таблица приведена на рис. 3.5 (названия столбцов скопированы вручную). Обратите внимание, что строки «Итого» из исходной таблицы также могут участвовать в консолидации.  Рис. 3.5. Результат консолидации Примечание. В этой таблице ошибочно суммирована цена каждого изделия. Чтобы избежать этого, требовалось проводить консолидацию только для столбцов Наименованиеи К-во, столбец Ценаорганизовать вводом, столбец Сумма– вычислением. В этом случае таблица примет вид, показанный на рис. 3.6.  Рис. 3.6. Общая реализация продукции Сводные таблицыМастер сводных таблиц позволяет использовать еще один способ обобщения табличных данных. Одна из особенностей этого мастера заключается в том, что он удовлетворительно работает только с однородными табличными данными. Таблица, показанная на рис. 3.3, уже достаточно сложна для мастера сводных таблиц: мешают подзаголовки с названиями торговых точек. Поэтому для обработки этих данных мастером их надо преобразовать к виду, представленному на рис. 3.7. В дальнейшем эта таблица называется однородной. Для создания однородной таблицы необходимо перекопировать исходную таблицу (рис. 3.3) на новый рабочий лист, удалить названия всех торговых точек и строк итогов, вручную добавить столбец «Запр. станции» и заполнить его записями.  Рис. 3.7. Исходные данные для построения сводной таблицы Данная таблица пригодна для создания сводной таблицы. Мастер сводных таблиц создает таблицу за 4 шага: Вызвать мастер сводных таблиц командой меню Вставка → Своднаятаблица.На первом шаге переключатель должен указывать на источник данных «В списке или базе данных Microsoft Excel». Указать диапазон, в котором содержатся исходные данные. В нашем случае это B3:F13. Если ячейки выделены заранее, то мастер сам определит. Указать в поле «Поместить таблицу в адрес ячейки», которая будет соответствовать левому верхнему углу таблицы. Если эта ячейка видна на листе, то достаточно щелкнуть по ней мышью. Определить, как будет выглядеть новая сводная таблица. На рис. 3.8 показано окно мастера на третьем шаге. Требуется создать макет будущей таблицы, используя известные мастеру поля. Переместить мышью Фирмапроизводитель в область Фильтр отчета, кнопку Марка топлива – в область Строка, кнопку К-во – в область Столбец, а кнопку Сумма – в область Значения. Поле Цена не вошло в элементы сводной таблицы.  Рис. 3.8. Третий шаг мастера сводных таблиц Созданная сводная таблица (рис. 3.9) имеет страничную организацию. Это видно потому, что поле «Фирма производитель» содержит список, который программа создала автоматически. Выбирая элементы этого списка, можно видеть в таблице данные по одной фирме или по всем сразу. «Наименование» и «К-во» также являются полями сводной таблицы. При формировании строк и столбцов программа упорядочила их элементы и устранила повторения. Вместо нулей в таблице возможны пустые ячейки.  Рис. 3.9. Сводная таблица |