Понятие сводной таблицы и ее создание

Программа работы

1. Изучение понятия сводной таблицы.

2. Приобретение навыков работы по созданию сводной таблицы.

3. Приобретение навыков работы по использованию панели инструментов Запрос и сводная таблица.

4. Приобретение навыков работы с полями данных сводной таблицы.

5. Приобретение навыков работы с полями страниц сводной таблицы.

Понятие сводной таблицы и ее создание

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

Под сводной таблицей понимается такая таблица, которая получена из первоначальной таблицы в соответствии с заданными критериями и содержит обобщенные данные. Пусть имеется ведомость расхода материала по складу строительных материалов (рис. 11.1).

Рис. 11.1. Расходная ведомость по складу строительных материалов

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

Рис. 11.2. Сводная таблица по фамилиям, материалам и кварталам

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

Как же построить сводную таблицу более легким способом?

Для построения сводной таблицы необходимо выделить исходную таблицу и вызвать Мастера сводных таблиц, выполнив команду Данные-Сводная таблица (Data-Pivot Table). В результате откроется первое диалоговое окно Мастера сводных таблиц (Pivot Table Wizard) (рис. 11.3).

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

Рис. 11.3. Первое диалоговое окно Мастера сводных таблиц

Эти данные можно взять:

· из текущей или любой другой рабочей книги (Microsoft Excel List or Database);

· из внешнего источника данных (External Data Source), например, данные в формате dBase, Microsoft, Access и некоторых других;

· из нескольких диапазонов консолидации данных;

· из другой сводной таблицы.

В нижней части окна находятся знакомые нам кнопки. Нажав кнопку Далее (Next), мы переходим во второе диалоговое окно (рис. 11.4).

Рис. 11.4. Второе диалоговое окно Мастера сводных таблиц

В данном окне можно уточнить диапазон. Кнопка Обзор...(Browse...) позволяет выбрать файл, с которого можно взять данные для создания сводной таблицы. Следующий шаг приводит к открытию третьего окна (рис. 11.5).

Рис. 11.5. Третье диалоговое окно Мастера сводных таблиц

В данном окне справа находятся кнопки с названиями столбцов исходной таблицы. Эти названия с помощью мыши можно перетаскивать в поля Строка (Row), Столбец (Column), Данные (Data), Страница (Раде), создавая необходимый макет сводной таблицы (рис. 11.6).

Рис. 11.6. Создание макета сводной таблицы

Перетащим в поле Строка имена "ФИО" и "Название", в поле Столбец - "Дата", в поле Данные - "Сумма".

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

На следующем шаге открывается четвертое диалоговое окно Мастера сводных таблиц, в котором указываются адрес ячейки, с которой начинается сводная таблица, и ее имя (рис. 11.7).

Рис. 11.7. Четвертое диалоговое окно Мастера сводных таблиц

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

Рис. 11.8. Сводная таблица по конкретным датам

Для получения сводной таблицы по кварталам необходимо поставить табличный курсор в сводной таблице на любую ячейку с датой и вызвать щелчком правой клавиши контекстно-зависимое меню, в котором необходимо активизировать команды Структура-Сгруппировать (Group).

В результате откроется диалоговое окно Группировка (Grouping), в котором указываются параметры группировки (начальная и конечная даты, по годам, кварталам, месяцам и так далее) (рис. 11.9). Мы выбираем группировку по кварталам и получаем требуемую сводную таблицу (см. рис. 11.2).

Рис. 11. 9. Диалоговое окно Группировка

Панель инструментов Запрос и сводная таблица

Панель инструментов. Запрос и сводная таблица (Query and Pivot) можно вызвать на экран с помощью команд меню Вид-Панели инструментов (View- Toolbars) (рис. 11.10).

Рис. 11.10. Панель инструментов Запрос и сводная таблица

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

Вторая кнопка вызывает диалоговое окно Вычисление поля сводной таблицы (рис. 11.11).

Рис. 11.11. Диалоговое окно Вычисление поля сводной таблицы

В данном диалоге можно выбрать соответствующую функцию. Нажав кнопку Дополнительно (Options), можно задать тип дополнительных вычислений (рис. 11.12).

Третья и четвертые кнопки предназначены для разгруппировки и группировки данных.

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

Седьмая кнопка открывает диалог Показать страницы.

Восьмая кнопка предназначена для обновления данных.

Рис. 11.12. Установка дополнительных вычислений