Сводная таблица является, по существу, динамическим итоговым отчетом, генерируемым на основе базы данных. База данных может находиться как на рабочем листе, так и во внешнем файле. Сводные таблицы помогут создать доступное для понимания представление огромного количества строк и столбцов.
С помощью данного средства можно, например, создавать частотные распределения и таблицы массивов данных разных размерностей. Кроме того, существует возможность отображать промежуточные итоги с любой необходимой степенью детализации. Пожалуй, самой важной особенностью сводных таблиц является их интерактивность. В сводной таблице можно переупорядочить информацию практически любым образом; можно даже вставить специальные формулы, которые будут выполнять новые вычисления. Или, к примеру, скомбинировать итоговые данные северного и южного регионов. Кроме того, с помощью всего нескольких щелчков мыши вы можете превратить сводную таблицу в готовый к презентации аналитический отчет.
Сводные таблицы появились в версии Excel 97. К сожалению, многие пользователи проигнорировали этот инструмент, поскольку считали создание сводных таблиц уделом исключительно профессионалов. В версии Excel 2007 сводные таблицы были в значительной мере улучшены; теперь их создание и форматирование на порядок проще, чем когда бы то ни было. Единственный небольшой недостаток использования сводных таблиц заключается в том, что они не обновляются автоматически при изменении исходных данных, как это происходит в случае создания итогового отчета с использованием формул. Однако это не представляет серьезную проблему: для того, чтобы сводная таблица использовала последние измененные данные, необходим всего один щелчок на кнопке.
Пример сводной таблицы
Лучше всего понять концепцию сводной таблицы – это взглянуть на нее. На рисунке отображены данные, которые будут использоваться для создания сводных таблиц в этом разделе.
Эта таблица состоит из ежемесячно обновляемой информации о счетах банка, имеющего три отделения. Таблица состоит из 712 строк, каждая строка содержит информацию об отдельном счете. Таблица включает следующие столбцы:
• дата открытия счета;
• размер открытого счета;
• тип счета (начальный, накопительный, внутренний или международный);
• кто открыл счет: кассир или менеджер;
• отделение, в котором открыт счет (центральный, западный или северный филиал);
• тип клиента (старый или новый).
База данных банковских счетов содержит довольно много информации. Однако из данных, представленных в такой форме, понять можно немного. Чтобы из отображенных данных можно было извлечь больше полезной информации, их следует определенным образом обобщить. Такое обобщение данных является, по существу, процессом выбора ответов на ряд вопросов. Некоторые вопросы, которые обычно интересуют банковских работников, представлены ниже.
• Какова общая сумма депозита в каждом отделении и какова доля каждого типа счета?
• Сколько счетов было открыто в каждом отделении и какова доля, каждого типа счета?
• Каково распределение различных типов счетов?
• Какие типы счетов открывают чаще всего кассиры?
• Как соотносятся результаты работы центрального отделения банка со всеми остальными?
• В каком отделении открыто наибольшее количество счетов для новых клиентов?
Безусловно, для получения ответов на все эти вопросы можно написать соответствующие формулы. Однако во многих случаях использование сводных таблиц представляется более удачным решением. Создать сводную таблицу можно за считанные секунды, не написав при этом ни единой формулы и получив презентабельный отчет. К тому же сводные таблицы в меньшей степени подвержены потенциальным ошибкам, чем формулы.
На рисунке ниже показана сводная таблица, которая создана из данных банка, представленных на рисунке выше. В этом примере показана величина депозитов, разбитых по отделениям и типам счетов. Это одно из десятков возможных обобщений имеющихся данных.
На следующем рисунке показана еще одна сводная таблица, созданная из той же базы банковских данных. В ней используется раскрывающийся список фильтрации по полю типа клиента. В таблице отображены данные лишь для старых клиентов (можно также выбрать из списка поля варианты Новый или Все). Обратите внимание на изменение ориентации таблицы: отделения указываются уже в строках, а типы счетов – в столбцах. Этот пример демонстрирует гибкость сводных таблиц.
Данные, пригодные для создания сводных таблиц
Для сводной таблицы нужно, чтобы исходные данные были организованы в виде прямоугольной базы данных. Эти данные могут находиться либо в диапазоне базы данных (обычном или организованном в форме таблицы с помощью команды Вставка→Таблицы→Таблица), либо во внешнем файле базы данных. Несмотря на то, что программа Excel способна генерировать сводные таблицы на основе любой базы данных, не все базы данных могут извлечь из этого пользу.
Поля в таблице базы данных могут быть двух типов.
• Данные. Содержат значения или данные, которые должны быть обобщены. На первом рисунке поле Сумма является полем данных.
• Категории. Они описывают данные. На первом рисунке поля Дата, Тип, Отделение и Клиент являются полями категорий, так как они описывают данные, содержащиеся в поле Сумма.
Таблица базы данных может иметь любое количество полей данных и категорий. При создании сводной таблицы обычно ставится задача подведения итогов для одного или нескольких полей данных. И наоборот, значения полей категорий в сводной таблице могут появляться в качестве категорий, полей столбцов или строк, а также фильтров.
Однако существуют и исключения. Средства создания сводных таблиц Excel могут оказаться полезными, даже если вы работаете с базами данных, не содержащими настоящих числовых полей данных. В таком случае сводная таблица выполняет подсчет полей, а не их суммирование.
На следующем рисунке показан диапазон данных Excel, вообще не подходящий для создания сводной таблицы. В этом диапазоне содержится описательная информация о каждом значении, однако он не организован в форму таблицы. На самом деле этот диапазон сам очень похож на сводную таблицу.
На рисунке ниже показаны эти же данные, но упорядоченные определенным образом. Теперь данные нормализованы. Нормализованный диапазон состоит из столбцов и строк, причем каждая строка содержит информацию об одном элементе данных и каждый столбец имеет заголовок.
В рассматриваемом примере нормализованный диапазон состоит из 78 строк данных и одной строки заголовков. Обратите внимание на то, что каждая строка содержит информацию о категориях каждой продажи – штат, регион, месяц и квартал. Этот диапазон – идеальный “кандидат” для создания сводной таблицы, поскольку он содержит всю информацию, необходимую для обобщения данных по категориям.
На следующем рисунке показана сводная таблица, созданная на основе нормализованного диапазона (см. рис. выше). Как видите, она весьма похожа на ненормализованную таблицу.
Обращение сводной таблицы
Инструменты Excel создают итоговую сводную таблицу на основе списка. Однако иногда нужно выполнить противоположную операцию – преобразовать двустороннюю итоговую таблицу в нормализованный список.
Ниже приведен рисунок, на котором диапазон A1:Е13 содержит итоговую таблицу с 48 точками данных. Обратите внимание на то, что она очень похожа на сводную таблицу. Столбцы G:I содержат таблицу из 48 строк, сгенерированную на основе таблицы, показанной слева. Каждое значение итоговой таблицы слева преобразовано в строку нормализованной таблицы. Полученная таким образом нормализованная таблица может быть полезной, когда нужно заново отсортировать или отфильтровать данные (или выполнить над ними какие-либо иные операции), а затем снова создать на ее основе сводную таблицу.
Преобразование выполняется с помощью макроса.
Терминология, используемая при работе со сводными таблицами
Понимание терминологии, ассоциированной со сводными таблицами, является первым шагом в освоении этого инструмента. Упростить изложение материала поможет показанная ниже сводная таблица.
• Поле столбца – это поле (т.е. столбец) исходной таблицы, имеющее в сводной таблице ориентацию столбца. Каждый элемент в сводной таблице занимает столбец. На приведенном рисунке Клиент является полем столбца с двумя элементами (Старый и Новый). В сводной таблице можно использовать вложенные поля.
• Общий итог – это строка или столбец, отображающий итоги для всех ячеек в строке или столбце. При создании сводной таблицы можно определить отображение итогов для строк, столбцов, для строк и столбцов или отсутствие итогов.
• Группа – это набор элементов, рассматриваемых как один элемент. Элементы можно группировать как вручную, так и автоматически (например, можно сгруппировать даты в месяцы). Приведенная выше в качестве примера сводная таблица не содержит определенных групп.
• Элемент – это элемент поля, выступающий в сводной таблице в качестве заголовка строки или столбца. На приведенном рисунке Старый и Новый являются элементами поля Клиент. Поле Отделение имеет три элемента, поле Тип – четыре.
• Обновление – это пересчет сводной таблицы после внесения изменений в исходные данные или структуру.
• Поле строки – это поле (т.е. столбец) исходной таблицы, имеющее в сводной таблице ориентацию строки. Каждый элемент в поле занимает строку. Поля строки можно объединять. На рисунке отображены два поля строки: Отделение и Тип.
• Исходные данные – это данные, используемые для создания сводной таблицы. Они могут находиться как на рабочем листе, так и во внешней базе данных.
• Промежуточные итоги – это строка или столбец, отображающий промежуточные суммы для части ячеек в строке или столбце сводной таблицы. В представленной таблице показаны промежуточные суммы для каждого отделения.
• Фильтр таблицы – это поле, имеющее ориентацию страницы сводной таблицы. Оно похоже на срез трехмерного куба. В поле фильтра (страницы) может одновременно содержаться любое количество элементов. На рисунке поле Открыт фильтра отображает значение Кассир.
• Область данных – это ячейки сводной таблицы, содержащие обобщенные данные. Excel предлагает возможность обобщения данных несколькими способами (суммирование, среднее значение, подсчет и т.д.).