Сводные таблицы обычно строятся вручную специальным инструментом - Мастером сводных таблиц.
Для примера используются данные по продажам торговой фирмы, хранящиеся в формате MS Access в файле ITS.mdb. При построении сводной таблицы в нее будут включены данные, хранящиеся в разных таблицах базы данных. При анализе деятельности могут интересовать самые разные вопросы:
· Как шли продажи в стоимостном и количественном исчислении за те или иные периоды времени?
· Какие группы товаров продавались наиболее успешно?
· Кто из сотрудников офиса оформлял наибольшее число заказов?
· С кем из заказчиков шла наиболее успешная работа?
· С какими городами шло наиболее успешное сотрудничество?
Сводная таблица должна позволять менеджеру, проводящему анализ, отвечать на все эти вопросы.
В документе Excel выберем рабочий лист, на котором предполагаем поместить сводную таблицу, выбраем ячейку, задающую ее начало. Теперь можно начать работу с Мастером сводных таблиц, для чего в главном меню выбраем пункт "Данные" и в нем пункт "Сводная таблица. Вот как выглядит первое окно, открываемое Мастером:
Рис. 3.1. Первое окно Мастера сводных таблиц и диаграмм
Заметим, из четырех возможностей задания разных типов источников данных - списков Excel, внешних источников, нескольких диапазонов, другой сводной таблицы - выбран внешний источник данных, поскольку, мы будем строить сводную таблицу, используя базу данных Access. Данные храним в Access, т.к. в Excel допустимое количество строк таблиц чуть больше 64000, а у нас есть таблицы на сотни тысяч строк. Вторая группа переключателей позволяет задать желаемый вид отчета - сводную таблицу или сводную диаграмму, построенную на основе сводной таблицы. Пример с диаграммой приведем чуть позже, а сейчас рассмотрим чисто сводные таблицы. Сделав выбор, остается нажать кнопку "Далее", что заставляет Мастера сделать очередной шаг. Вот окно, открываемое на втором шаге:
Рис. 3.2. Окно второго шага Мастера сводных таблиц
Получать данные внешних источников сам Мастер сводных таблиц не умеет, а посему он предлагает на этом шаге обратиться к другому инструментальному средству - Microsoft Query, предназначенному для работы с запросами баз данных, широко используемому в Access и всюду в Office 2003, где приходится работать с базами данных. Нажатие кнопки "Получить данные" запускает этот инструментарий, и первое появившееся окно позволяет выбрать тип источника данных:
Рис. 3.3. Выбор источника данных при запуске Microsoft Query
Здесь тоже идет речь о выборе типа источника данных, но уже на другом уровне. Три вкладки: "Базы данных", "Запросы", "Кубы OLAP" позволяют сделать выбор на этом уровне. В данном случае выбаем вкладку "Базы данных" и в открывшемся списке указываем тип базы данных - MS Access Database. Заметьте, включен флажок "Использовать мастер запросов". По нажатию кнопки "OK" открывается окно, где можно задать путь к базе данных:
Рис. 3.4. Задание пути к базе данных
В следующем окне Мастера запросов начинается собственно формирование запроса на основании таблиц и запросов базы данных и содержащихся в них полей, называемых здесь столбцами. В открывающемся списке показаны все таблицы и все запросы базы данных "ITS". Каждый элемент списка можно раскрыть, нажав значок "плюс", и перенести нужные поля таблицы в запрос, на основании которого строится сводная таблица.
В результате получим:
Рис. 3.5. Выбор полей базы данных для включения их в сводную таблицу
После выбора всех нужных полей нажатие кнопки "Далее" заставляет Мастера запросов перейти к очередному шагу. Что будет выполняться на следующем шаге, зависит от того, сумеет ли Мастер запросов извлечь требуемые данные из таблиц базы данных. Если проблем у него не возникает, то Мастер запросов предложит создать фильтр для отбираемых данных. Вот как выглядит соответствующее окно при задании фильтра по полю Дата (3 и 4 кварталы 2007 года)
Рис. 3.6. Запрос на построение фильтров
Окно, следующее за построением фильтров, позволяет задать требуемый порядок сортировки данных. Для нас сортировка не нужна, поэтому ее не используем. Так выглядит следующее окно, завершающее построение запроса для рассматриваемой ситуации:
Рис. 3.7. Завершающий шаг построения запроса
На этом шаге можно, как видите, сохранить запрос, нажав соответствующую командную кнопку для использования в будущем. Здесь также следует сделать выбор одной из трех возможностей:
· Вернуть данные в Microsoft Office Excel,
· Просмотр или изменение данных Microsoft Query,
· Создание куба OLAP из данного запроса.
Обычная практика состоит в том, что выбирается первый пункт, и данные возвращаются в Excel. Мы тоже вернемся в Excel, но чуть попозже, а пока выберем второй вариант, чтобы вручную добавить в запрос Справочник по товару из базы.
Рис. 3.8. Результат после нажатия на кнопку Готово.
Нажимаем на кнопку в меню Добавить таблицу, выбираем из списка таблиц базы Справочник по товару, как показано на рисунке 
Рис. 3.9. Добавление в запрос таблицы Справочник по товару
В результате получаем возможность связать две выбранные таблицы по полю PID и добавить в запрос два новых поля из Справочника по товару, как показано на рисунке 3.10.
Рис. 3.10. Установление связей между таблицами
Выполнив всю требуемую работу, просто закрываем это окно, что возвращает нас ко второму шагу Мастера сводных таблиц и диаграмм, но уже в новом состоянии, когда данные для построения сводной таблицы получены:
Рис. 3.11. Новое состояние окна Мастера сводных таблиц на втором шаге
Заметьте, теперь, в отличие от рисунка 3.2, наряду с уведомлением о получении данных стала доступной кнопка "Далее", которую и нажимаем для перехода к последнему шагу работы Мастера:
Рис. 3.12. Заключительный шаг работы Мастера сводных таблиц
На заключительном шаге работы можно указать рабочий лист и ячейку, начиная с которой будет располагаться сводная таблица. Заметьте, наряду с кнопкой "Готово", нажатие которой завершает работу Мастера, в нашем распоряжении есть и другие кнопки, в частности, кнопка "Макет". Вот как выглядит окно макета сводной таблицы:
Рис. 3.13. Макет сводной таблицы
На макете представлена схема сводной таблицы, - четыре области таблицы, озаглавленные соответственно "Страница", "Строка", "Столбец" и "Данные". На макете также представлены поля, отобранные для построения сводной таблицы. Каждое из полей может быть перетащено в одну из областей таблицы. Заметьте, вовсе не обязательно перетаскивать сразу все поля. Как уже говорилось, одно из достоинств сводных таблиц состоит в том, что их структуру можно легко перестраивать в зависимости от целей, которые менеджер, работающий с таблицей, ставит при анализе данных.
Мы не будем работать с макетом таблицы, предпочтя окончательную работу по формированию таблицы сделать чуть позже. Поэтому вместо кнопки "Макет" в окне, показанном на рис. 3.12, нажимаем кнопку "Готово". В результате Мастер сводных таблиц разместил на выбранном рабочем листе по существу макет сводной таблицы, открыл инструментальную панель с именем "Сводные таблицы", окно с полями из запроса и на этом завершил свою работу. Вот как выглядит рабочий лист Excel по окончании работы Мастера:
Рис. 3.14. Рабочий лист с макетом сводной таблицы и инструментальной панелью
Теперь пришла пора заключительного этапа формирования структуры сводной таблицы, - необходимо разумным образом переместить доступные поля в четыре области таблицы. Заметьте, не обязательно перемещать все поля и не обязательно заполнять область страниц. Разумность задания той или иной структуры сводной таблицы определяется целями проводимого анализа, опытом и привычкой.
В данном примере выбран следующий вариант размещения полей:
· В область данных поместили (перетащили мышкой) поле Сумма СС (себестоимость). Это естественный выбор, когда речь идет об анализе продаж какого либо товара.
· В область столбцов вставили поле Менеджер, чтобы видеть результаты работы каждого менеджера.
· В область строк - поле Дата.
· В область страниц поместили поле вида операции Transaction_NAME.
В результате этих действий получилось:
Рис. 3.15. Сформированная сводная таблица
В таком виде таблица содержит полную информацию о продажах, ее единственный недостаток - в ней слишком много подробностей. Чаще всего, для понимания сути явления нужно уметь скрывать детали.
Прежде всего, рассмотрим возможность группирования данных. По настоящему, группирование следует предусматривать еще при проектировании базы данных, например, указывать страну, регион, город при задании местоположения заказчика, вводить классификацию продаваемых товаров и так далее. Наша база данных слишком проста и не содержит такого группирования. Однако возможность группирования данных хотя бы одному измерению у нас имеется. Дело в том, что на датах автоматически задано естественное группирование - по годам, кварталам, месяцам, дням. Более того, можно ввести группирование по часам, минутам, секундам, можно также ввести группировку по количеству дней, а значит по неделям или по декадам. Выделив в сводной таблице поле "Дата" и нажав правую кнопку мыши, из появившегося контекстного меню выбираем пункт "Группа и структура" и подпункт "Группировать", где и задается группировка дат по кварталам, месяцам и дням:
Рис. 3.16. Группировка дат
В результате добавились новые поля и новая возможность - скрыть детали продаж в отдельные дни и проанализировать итоговые объемы продаж, например, по годам, кварталам, месяцам. Вот как выглядит сводная таблица в результате добавления группировки полей:
Рис. 3.17. Сводная таблица в результате группировки дат
Теперь, когда таблица построена, и начинается самый важный для пользователя этап содержательного анализа данных. Как уже говорилось, сводная таблица очень мощное и вместе с тем очень простое в использовании средство анализа данных. Вот лишь некоторые ее возможности:
· Изменение структуры таблицы. Поля таблицы можно добавлять и удалять, путем перетаскивания их с инструментальной панели "Сводные таблицы". Можно изменять ориентацию измерений, меняя местами поля строк, столбцов и страниц.
· Фильтрация данных. Практически по каждому полю можно вводить фильтры. Так, например, выбрав в поле "Город" значение "Тверь", получаем фильтр для всех заказчиков из города Тверь. Выбрав для поля "Сотрудник" фамилию интересующего нас сотрудника, получаем соответствующий фильтр.
· Вычисляемые поля. Некоторые поля таблицы можно объявить вычисляемыми и задать различные функции, подводящие итоги. Для числовых полей это может быть сумма значений поля, максимум, минимум, среднее, среднеквадратическое отклонение или другая подобная функция из некоторого фиксированного набора.
· Визуализация данных. По сводной таблице можно построить сводную диаграмму, которая перестраивается в ответ на изменения, происходящие со сводной таблицей. Верно и обратное, можно перестраивать диаграмму, что отражается в перестроении сводной таблицы.
· Другие изменения. Их можно производить, используя контекстное меню, появляющееся при нажатии правой кнопки мыши, или используя возможности инструментальной панели "Сводные таблицы".
Инструментальная панель "Сводные таблицы"
В процессе создания сводной таблицы по умолчанию включается инструментальная панель "Сводные таблицы". Ее, конечно, при желании можно отключить, но, как правило, она всегда включена, поскольку позволяет выполнять различные операции над сводной таблицей.
Вид инструментальной панели показан на предшествующих рисунках, например, на рис. 3.17. Рассмотрим более подробно, что можно делать с помощью кнопок инструментальной панели:
· 
- Эта кнопка позволяет задать форматирование сводной таблицы. Можно выбрать формат из списка, содержащего два десятка возможных форматов.
· 
- Задает построение сводной диаграммы.
· 
- Вызывает Мастера сводных таблиц, позволяя вернуться к процессу построения таблицы с использованием Мастера.
· 
- две кнопки, позволяющие скрыть и показать детали. Особенно полезны при работе с полями, допускающими группировку. Но могут применяться и к большинству других полей.
· 
- позволяет обновить данные сводной таблицы, получив их из источника данных.
· 
- позволяет задать параметры поля. Для вычислимых полей можно задать соответствующую функцию вычислений.
· 
- кнопка для скрытия полей.
· 
- первая кнопка, расположенная на панели. При ее нажатии открывается меню, пункты которого выполняют практически все задачи, что и перечисленные выше кнопки, но кроме этого в меню есть и дополнительные возможности, например, можно установить дополнительные свойства сводной таблицы, задав те или иные ее параметры.
Сводные диаграммы
При работе со сводными таблицами визуализация данных способствует правильному их восприятию. Поэтому чаще всего наряду со сводной таблицей строится и соответствующая ей сводная диаграмма. Задать построение диаграммы можно еще на первом шаге работы Мастера сводных таблиц, что показано на рис. 3.1. Однако сделать это не поздно и потом, когда таблица уже построена. Среди кнопок инструментальной панели есть и кнопка, нажатие которой приводит к построению сводной диаграммы на отдельном листе, как показано на рисунке:
Рис. 3.18. Лист со сводной диаграммой
Заметьте, на лист диаграмм вынесены и поля таблицы, что позволяет задавать фильтры, мгновенно изменяя диаграмму. При работе с листом диаграммы доступны и кнопки инструментальной панели.
Еще раз отметим, диаграмма и сводная таблица жестко связаны, - любые изменения в диаграмме и сводной таблице взаимосвязаны.
Построение OLAP-куба
Excel позволяет по данным внешних источников построить не только сводную таблицу, но и многомерный куб данных - OLAP куб. Цель этого построения - использовать в дальнейшем этот куб, как источник данных для сводной таблицы. При больших объемах данных, используемых при построении сводной таблицы, эффективность работы может быть существенно повышена, если сводная таблица использует OLAP куб.
Работа по построению OLAP куба начинается с построения сводной таблицы. Но в тот момент, когда построен запрос, извлекающий данные из базы данных, можно перейти к построению OLAP куба. Взгляните на рис. 3.7, где показан завершающий шаг построения запроса, - здесь можно включить соответствующий переключатель и перейти к созданию OLAP куба. В примере, где это окно не появлялось, и где запрос создавался непосредственно в Microsoft Query, что показано на рис. 3.10, для построения OLAP куба можно выбрать соответствующий пункт из меню Файл.
Итак, построение OLAP куба в Excel начинается так же, как и построение сводной таблицы вплоть до момента завершения построения запроса. В этот момент появляется возможность построить OLAP куб по запросу. Давайте продолжим рассмотрение, начиная с этого момента. Предположим, что в окне, показанном на рис. 3.7, был выбран третий переключатель, после чего запускается на исполнение новый Мастер - Мастер построения OLAP куба. Так выглядит первое окно, открываемое этим Мастером:
Рис. 3.19. Первое информационное окно Мастера построения OLAP куба
В этом информационном окне перечислены достоинства OLAP кубов, есть возможность перейти к подробной справке, которую рекомендую внимательно прочесть на досуге. Пойдем дальше, следуя за Мастером. На первом шаге своей работы он предлагает задать вычисляемые поля и определить функцию, используемую при вычислениях. Вот как выглядит это окно:
Рис. 3.20. Окно первого шага Мастера построения OLAP куба
Обычно, по умолчанию все численные поля являются вычисляемыми, а в качестве функции применяется функция "Сумма". Оставим предлагаемые установки только для поля Сумма СС и перейдем к следующему шагу работы. На этом шаге работы Мастер предлагает создать измерения нашего куба:
Рис. 3.21. Создание измерений OLAP куба
Заметьте также, вычисляемые поля стали полями данных. Все оставшиеся поля перетаскиваем в правую часть, и они становятся измерениями, т.е. осями нашего куба. Поле "Дата", имеющее тип даты, автоматически породило иерархию на соответствующем измерении.
Рис. 3.22. Результат создания измерений OLAP куба
На заключительном шаге работы Мастер предлагает сохранить OLAP куб. Здесь можно выбрать, сохранить ли только определение куба - файл с расширением "oqy" или сам куб с данными - файл с расширением "cub". Сохранение самого куба хотя и требует времени, но обеспечивает более быструю работу со сводной таблицей в дальнейшем при использовании куба.
Рис. 3.23. Сохранение OLAP куба
Выберем сохранение самого куба. На этом построение куба завершено. Сводная таблица – это как окно, через которое можно просматривать данные OLAP куба, приведем окончательный результат:
Рис. 3.24. Макет сводной таблицы, созданной на основе OLAP куба
Далее поступаем также, как строили сводную таблицу, только используем другие поля для конструирования куба и получаем:
Рис. 3.25. Сводная таблица, созданная на основе OLAP куба
Заметьте, есть некоторые отличия в сводных таблицах, построенных на основе OLAP куба. В частности, удобнее работать с измерениями, имеющими иерархию. В нашем примере это можно заметить при работе с датами, где иерархия была построена автоматически, а выбор интересующего диапазона дат задается, как показано на рис. 3.26.
Рис. 3.26. Выбор диапазона дат для построения сводной таблицы.
Таким образом, мы рассмотрели все основные вопросы, связанные с построением сводных таблиц и OLAP-кубов для проведения анализа данных.
Задания к работе.