Результат выполнения операции может выглядеть так, как показано на рис. 17.
Рис. 17. Результат вычисления промежуточных итогов по Продуктам
3.2. Работа со сводными таблицами
Сводная таблица – таблица итоговых данных, извлеченных или рассчитанных на основе информации, содержащейся в списках.
Сводные таблицы могут использоваться:
1) для обобщения большого количества однотипных данных;
2) реорганизации данных (с помощью перетаскивания);
3) отбора и группировки данных;
4) построения диаграмм.
Методика создания сводной таблицы.
1. Выбрать место для сводной таблицы, т. е. установить указатель в необходимое место на рабочем листе.
2. Выполнить команду Данные – Сводная таблица.
3. В диалоговом окне Мастер сводных таблиц и диаграмм на первом шаге определить источник данных и вид создаваемого отчета (рис. 19) и нажать кнопку Далее.
Рис. 19. Диалоговое окно Мастер сводных таблиц и диаграмм
1. На втором шаге определить исходный диапазон (рис. 20) и нажать кнопку Далее.
Рис. 20. Окно определения исходного диапазона
2. На третьем шаге Мастера сводных таблиц и диаграмм необходимо нажать кнопку Макет (рис. 21), чтобы сформировать макет сводной таблицы (т.е. задать страницу, строки, столбцы и вычисляемые поля сводной таблицы).
Рис. 21. Окно размещения сводной таблицы
3. В диалоговом окне Мастер сводных таблиц – Макет поля списка представлены в виде кнопок (рис. 22), которые нужно просто перетащить в соответствующие области макета таблицы и нажать кнопку ОК.
Рис. 22. Диалоговое окно Мастер сводных таблиц – макет
Страница – значения поля, помещенного в эту область, используются в качестве заголовков страниц сводной таблицы.
Строка – значение поля, помещенного в эту область, используются в качестве заголовков строк сводной таблицы.
Данные – поле (поля), для которого подводится итог в сводной таблице.
Столбец – значения поля, помещенного в эту область, используются в качестве заголовков столбцов сводной таблицы.
По умолчанию при перетаскивании кнопки в область Данные, будет применена функция Сумм, если поле содержит числовые значения, и функция Счет – в случае нечисловых значений. Если дважды щелкнуть по полю в области данных, функцию можно изменить.
7. После проведения всех подготовительных операций нажать кнопку ОК и затем Готово (рис. 21).
Пример 5. По исходной базе данных (рис. 1)
1. Построить сводную таблицу с макетом, приведенным на рис. 23. Сводную таблицу расположить на том же листе, что и исходная таблица.
2. В созданной сводной таблице поле Оборудование переместить в строки, поле Цена добавить в столбец.
3. Сгруппировать продукты в две группы: Комплектующее оборудование (кулер, видеокарта, блок питания) и Периферийное оборудование (принтер, сканер, колонки, камера) и переименовать их.
Рис. 23. Макет сводной таблицы для исходной базы данных
Для построения сводной таблицы для примера необходимо:
установить курсор в любую ячейку исходной таблицы;
выполнить команду Данные – Сводная таблица;
в диалоговом окне Мастер сводных таблиц и диаграмм пошагово указать необходимые параметры для сводной таблицы.
В результате выполнения всех этапов построения сводных таблиц, она будет иметь вид, как на рис. 24.
Рис. 24. Пример сводной таблицы
1- Исходная таблица
2- Сводная таблица
В созданной сводной таблице поле Оборудование переместить в строки, поле Цена добавить в столбец. Для этого на созданной сводной таблице в контекстном меню выбрать пункт Мастер сводных таблиц в диалоговом окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 нажать кнопку Макет… и поле Оборудование переместить в строки, поле Цена добавить в столбец (рис. 25).
Рис. 25. Изменение макета сводной таблицы
В результате измененная сводная таблица примет вид, представленный на рис. 26.
Рис. 26. Вид измененной сводной таблицы
Комплектующее оборудование (кулер, видеокарта, блок питания) и Периферийное оборудование (принтер, сканер, колонки, камера) и переименовать их. Для этого в сводной таблице, удерживая нажатой клавишу Ctrl, необходимо выделить те продукты, которые должны входить в одну группу. Затем в контекстном меню выбрать Группа и структура – Группировать.
Появившуюся структуру Группа 1 нужно переименовать. Для этого необходимо перевести курсор на ячейку с текстом Группа 1 и ввести новый текст в строке формул. В данном случае нужно ввести следующее: Комплектующее оборудование.
Аналогично переименовывается Группа 2, для которой вводится новое название Периферийное оборудование
В результате выполнения операции группировки будет сформирована таблица, показанная на рис. 27.
Рис. 27. Создание группы с сводной таблице