3.7 Анализ данных на основе сводных таблиц
3.7.1 Общие сведения
Сводная таблица является мощным аналитическим инструменты, позволяющим суммировать данные полей списка табличного процессора. При создании сводной таблицы можно задать способ организации таблицы и тип выполняемых вычислений. После создания сводной таблицы можно менять ее расположение для удобства просмотра данных с других позиций анализа. Сводная таблица связана с данными исходного списка, при изменении этих данных она может обновляться вручную или автоматически.
3.7.2 Технология создания сводной таблицы
Включение инструмента Мастер сводных таблиц
Для создания сводной таблицы нужно выполнить операции:
установить курсор в любую ячейку диапазона, в котором размещен исходный список;
выполнить команду меню Данные/Сводная таблица - откроется диалоговое окно Мастер сводных таблиц (рис. 3.34).
В качестве источника данных для создания сводной таблицы Мастер сводных таблиц может использовать список или несколько списков, сформированных на рабочих листах активной рабочей книги, внешний источник данных, например из таблицы базы данных, другие сводные таблицы или сводные диаграммы.
Сводная таблица с помощью мастера сводных таблиц создается за три шага, включающих:
задание типа источника данных и вида создаваемого отчета;
указание расположения исходных данных в источнике данных;
указание места для размещения результирующей таблицы;
указание полей, размещаемых в таблице, способ организации таблицы и типы вычислений.
Рис 3.34 Диалоговое окно Мастер сводных таблиц
Задание типа источника данных и вида создаваемого отчета
Источник данных задается в диалоговом окне Мастер сводных таблиц (см. рис. 3.23). Для указания источника данных в группе Создать таблицу на основе имеющихся данных нужно установить соответствующий переключатель. Для указания вида отчета в группе Вид создаваемого отчета установить переключатель Сводная таблица или Сводная диаграмма. После выполнения приведенных операций можно перейти к следующему шагу, щелкнув на кнопке Далее.
Указание места расположения исходной таблицы
Если источник данных находится в списке или базе данных табличного процессора, то откроется окно диалога, приведенное на рис. 3.35. Место расположения исходной таблицы задается в этом диалоговом окне в поле Диапазон. Если источник данных находится в закрытой рабочей книге, то нужно воспользоваться кнопкой Обзор.
Рис. 3.35 Указание диапазона, содержащего исходные данные
При создании таблицы на основе внешнего источника данных откроется диалоговое окно, вид которого приведен на рис. 3.36.
Рис. 3.36 Создание таблицы на основе внешнего источника данных
Для определения внешнего источника нужно щелкнуть на кнопке Получить - откроется окно Получение источника данных (рис. 3.37). В этом диалоговом окне можно выбрать базу данных реляционного типа, выполнить сохраненный запрос или указать источник данных ОLAP.
Рис.
3.37 Диалоговое окно Выбор источника
данных
При создании сводной таблицы на основе нескольких диапазонов консолидации откроется диалоговое окно, вид которого приведен на рис. 3.38.
Рис. 3.38 Создание таблицы на основе нескольких диапазонов
Указание места расположения результирующей таблицы
В последнем диалоговом окне (рис. 3.39) Мастер сводных таблиц определяется место, где будет располагаться результирующая таблица. Чтобы поместить таблицу в новом рабочем листе, следует установить переключатель Новый лист.
Рис. 3.39 Указание места размещения таблицы
Перед тем как щелкнуть на кнопке Готово, если это необходимо, можно установить параметры, щелкнув на соответствующей кнопке диалогового окна, - откроется диалоговое окно Параметры сводной таблицы (рис. 3.40). К установке параметров можно вернуться и позднее, когда сводная таблица будет уже создана.
Рис. 3.40 Диалоговое окно Параметры сводной таблицы
Создание макета сводной таблицы
После выполнения операций с помощью Мастер сводных таблиц и щелчка на кнопке Готово в последнем диалоговом окне в рабочем листе будет отображен пустой макет таблицы, панель инструментов Сводные таблицы и диалоговое окно Список полей сводной таблицы (рис. 3.41).
Рис.3.41 Диалоговое окно Список полей сводной таблицы
Формирование макета выполняется путем перетаскивания кнопок с названиями столбцов исходного списка из диалогового окна Список полей сводной таблицы в области макета с надписью Перетащите сюда поля столбцов (строк). В область с надписью Перетащите сюда элементы данных следует перетащить кнопки с названиями по лей, над данными которых должны быть выполнены вычислительные операции.
Пример 3.9 Требуется создать сводную таблицу на основе списка, приводимого в предыдущих примерах, в которой должны быть отражены суммарные значения приходных и расходных операций для каждого клиента за текущий период.
Решение
Установим курсор в область исходного списка и включим инструмент Мастер сводных таблиц.
В окне Мастер сводных таблиц (шаг 1) установим переключатель В списке или базе данных Microsoft Office Excel и переключатель Сводная таблица.
В поле Диапазон окна Мастер сводных таблиц (шаг 2) укажем адрес диапазона, в котором расположен исходный список.
В окне Мастер сводных таблиц (шаг 3) установим переключатель Новый рабочий лист.
В появившемся на рабочем листе макете выполним операции:
перетащим кнопку Фамилия клиента в область с надписью Перетащите сюда названия строк;
в область с надписью Перетащите сюда элементы данных перетащим кнопки с названиями Приход и Расход. После выполнения этих операций на рабочем листе будет сформирована таблица, фрагмент которой приведен на рисунке. В поле Итог этой таблицы помещены числа, соответствующие количеству операций расхода и прихода, выполненных по счетам каждого клиента;
Рис. 3.42
поместим курсор в любое поле таблицы с надписью Количество по полю Приход и включим контекстное меню. В списке меню выберем Параметры поля;
в открывшемся окне диалога Вычисление поля сводной таблицы выберем из списка Операция элемент Сумма, повторим эту операцию для поля таблицы с надписью Количество по полю Расход (рис. 3.43).
Рис. 3.43
После выполнения всех операций будет получена результирующая таблица (рис. 3.44.)
Рис. 3.44