Сводные таблицы и диаграммы

Сводные таблицы – это интерактивная таблица, в которой приведены итоговые значения, полученные на основе консолидации больших массивов данных. Отличиями сводной таблицы от таблицы консолидации является легкость перестройки ее макета, наличие фильтров по большинству полей, структурированность, т.е. возможность сворачивать и разворачивать определенные группы данных, а также возможность мгновенно генерировать новые листы в книге Microsoft Excel с развернутым представлением исходных данных для конкретного значения определенного поля.

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

Сводная таблица Microsoft Excel является трехмерной таблицей за счет наличия у нее области страниц. В этой области можно выбрать любое значение определенного поля, и вся сводная страница будет автоматически перестроена с учетом фильтрации по этому полю. Например, если с областью страниц сопоставить поле «Год», то сводная таблица сможет показать результаты сразу за все годы ведения (в консолидированном виде) либо по каждому конкретному году.

Исходными данными для сводной таблицы может являться любой диапазон ячеек, где есть возможность группировки данных. Он должен быть оформлен как таблица или база данных, т.е. иметь имена полей и записей.

Рис. 14.18 Пример сводной таблицы:
1 – таблица исходных данных, отображающая продажи сотовых телефонов в магазинах сети; 2 – строка заголовка, выделенная форматированием, благодаря чему названия столбцов были распознаны в качестве имен полей; 3 – область строк, здесь каждая строка соответствует продажам телефонов в определенный день; 4 – название для ячеек с данными (отредактировано вручную, оригинальное название – «Сумма по полю Сумма»); 5 – область страниц, где можно выбрать суммирование по каждому магазину отдельно или по всем магазинам; 6 – кнопка фильтрации по строкам; 7 – кнопка выбора страницы; 8 – кнопка фильтрации по столбцам (значок показывает, что фильтрация задействована); 9 – ячейки с данными, содержащие сумму выручки за каждую модель телефона в каждый день;
10 – ячейки, содержащие сумму выручки за каждый день; 11 – область столбцов, здесь каждый столбец соответствует модели телефона; 12 – ячейки, содержащие сумму выручки от продажи каждой модели телефона; 13 – итоговая сумма выручки за все модели телефонов по всем дням

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

Для создания сводной таблицы служит кнопка « Сводная таблица» в группе Таблицы вкладки Вставка ленты инструментов. Будет открыто диалоговое окно рис. 14.19.

Рис. 14.19 Диалоговое окно вставки сводной таблицы

В диалоговом окне (рис. 14.19) указывается источник данных для построения сводной таблицы. Поскольку использование внешних источников данных в пособии не рассматривается, следует оставить вариант «Выбрать таблицу или диапазон». Если ячейки могут быть представлены как база данных или уже оформлены как таблица, поле «Таблица или диапазон» будет заполнена автоматически.

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

После вставки сводной таблицы при ее выделении на экране будет отображена новая группа вкладок «Работа со сводными таблицами», состоящая из двух вкладок, показанных на рис. 14.20: Конструктор (далее – «Конструктор сводных таблиц») и Параметры (далее – «Параметры сводной таблицы»).

Рис. 14.20 Вкладки «Конструктор» и «Параметры» для работы со сводной таблицей

Во многих случаях для анализа данных в дополнение к сводной таблице удобно иметь еще и диаграмму. В Microsoft Excel существует возможность строить также интерактивные сводные диаграммы. Совокупность сводной таблицы и построенной на ее основе сводной диаграммы принято называть сводным отчетом.

Сводная диаграмма является дополнением сводной таблицы и не может существовать отдельно от нее.

Создание сводной диаграммы запускается командой Сводная диаграмма из выпадающего меню кнопки « Сводная таблица» в группе Таблицы на вкладке Вставка. Окно создания сводной диаграммы идентично окну рис. 14.19.

Если сводная таблица уже создана, добавить к ней сводную диаграмму позволяет кнопка « Сводная диаграмма» в группе Сервис вкладки Конструктор сводных таблиц. В этом случае создание сводной диаграммы начинается с выбора ее типа (см. Лабораторную работу №13; в случае использования кнопки « Сводная таблица» на вкладке Вставка будет использован тип диаграммы по умолчанию). Сама диаграмма будет размещена рядом со сводной таблицей.

При выделении сводной диаграммы отображается группа вкладок «Работа со сводными диаграммами». Особенности работы со сводными диаграммами не будут рассматриваться в данном пособии. Отметим лишь, что большинство параметров сводной диаграммы настраивается так же, как и у простых диаграмм.