5.7. Сводные таблицы

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

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

Эти возможности базируются на процедуре автоматического преобразования таблицы, в частности, перестановке строк и столбцов, а также сокрытия промежуточных данных и выполнения типичных функций обработки: суммирования, подсчета количества значений, вычисления среднего и т.д. Именно возможность изменения ориентации таблицы – транспонирование строк в столбцы и наоборот, дала сводной таблице ее название (в оригинале сводная таблица называется pivot table, где pivot означает «вращаться»). Следует иметь в виду, что автоматическое преобразование структуры таблицы возможно только при выполнении определенных требований, накладываемых на структуру таблицы, в частности, обязательное именование столбцов и строк таблицы.

Такая интерактивная таблица на рабочем листе позволяет подытожить большие объемы данных, выбрав подходящий метод вычислений. Осуществляют построение и обработку сводных таблиц с помощью Мастера сводных таблиц, который вызывается командой Данные – Сводная таблица. Для упрощения взаимодействия со сводной таблицей на экран можно вывести панель инструментов Сводные таблицы (рис. 5.34).

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

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

Для построения сводной таблицы возможно использовать следующие источники:

• список (базы данных MS Excel);

• внешний источник данных;

• указанные диапазоны консолидации;

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

В зависимости от вида источника изменяются последующие этапы работы по созданию сводной таблицы. Рассмотрим наиболее распространенный случай использования списков при построении сводных таблиц.

Второй шаг Мастера сводных таблиц (рис. 5.36) в этом случае предлагает задать диапазон исходных данных.

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

Полное имя диапазона ячеек записывается в виде:

[имя_книги]имя_листа!диапазон ячеек

Третий шаг Мастера сводных таблиц посвящен определению макета (структуры) сводной таблицы и является наиболее важным.

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

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

столбец – поля размещаются сле­ва направо, обеспечивая группировку данных сводной таблицы по иерархии полей. При условии существования области страницы или строки определять столбец необязательно;

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

данные – поля, по которым подводятся итоги, согласно выбранной функции.

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

Размещение полей выполняется путем их перетаскивания при нажатой левой кнопке мыши в определенную область макета. Каждое поле размещается только один раз в областях: страница, строка или столбец. По этим полям можно формировать группы и получать итоговые значения в области данные – группировочные поля. В области данные могут находиться поля произвольных т ипов, одно и то же поле может многократно размещаться в области данные. Для каждого такого поля задается вид функции и выполняется необходимая настройка. Эта настройка полей осуществляется с помощью диалогового окна Вычисление поля сводной таблицы (рис. 5.38).

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

Кнопка Дополнительно вызывает панель Дополнительные вычисления для выбора функций, список которых приведен в таблице 5.1.

Таблица 5.1.
Функция	Результат
Отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент
Доля	Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элемент
Приведенное отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент, нормированной к значе­нию этого элемента
С нарастающим итогом в поле	Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы кото­рого будут отображаться в нарастающем итоге
Доля от суммы по строке	Значения ячеек области данных отображаются в процентах от итога строки
Доля от суммы по столбцу	Значения ячеек области данных отображаются в процентах от итога столбца
Доля от общей суммы	Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы
Индекс	При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог)) / ((Итог строки) * (Итог столбца))

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