Построение сводной таблицы вручную
Сводные таблицы обычно строятся вручную специальным инструментом - Мастером сводных таблиц. Сравнительно просто построить эти таблицы и программно. Программист, конечно, должен уметь пользоваться таким инструментом, как Мастер сводных таблиц, хотя главное для него - знание объектной модели сводной таблицы. Причина понятна, - в разрабатываемом программистом офисном приложении не всегда можно требовать от пользователя умение строить самому сводную таблицу и часто эту таблицу надо строить программно, выяснив в диалоге требования пользователя. По сути, надо уметь строить собственного Мастера, который вел бы диалог с пользователем на более понятном языке, подходящем к данной конкретной ситуации.
Программная реализация предполагает знакомство с соответствующими классами объектов. Сама таблица и все ее части являются объектами, классы которых содержат большое количество свойств и методов. Об объектной модели сводной таблицы поговорим чуть позже, а сейчас рассмотрим на конкретном примере построение таблицы с использованием Мастера сводных таблиц. В примере я использую в качестве источника данных уже созданную базу данных офиса "Родная Речь", с которой шла работа в предыдущей главе. Замечу, что эта база данных в офисе используется уже давно, и потому там хранятся сведения о продажах за несколько лет.
При построении сводной таблицы я включу в нее данные, хранящиеся в разных таблицах базы данных. При анализе деятельности меня могут интересовать самые разные вопросы:
- Как шли продажи в стоимостном и количественном исчислении за те или иные периоды времени?
- Какие книги продавались наиболее успешно?
- Кто из сотрудников офиса оформлял наибольшее число заказов?
- С кем из заказчиков шла наиболее успешная работа?
- С какими городами шло наиболее успешное сотрудничество?
Наша сводная таблица должна позволять менеджеру, проводящему анализ отвечать на все эти вопросы. Вся необходимая информация хранится в таблицах: "Заказы", "Заказано", "Заказчики". Поля из этих таблиц и будут использоваться при построении сводной таблицы.
Итак, цель ясна - приступим?
В документе Excel я выбрал рабочий лист, на котором предполагаю поместить сводную таблицу, выбрал ячейку, задающую ее начало. Теперь можно начать работу с Мастером сводных таблиц, для чего в главном меню я выбрал пункт "Данные" и в нем пункт "Сводная таблица. Вот как выглядит первое окно, открываемое Мастером:
Рис. 8.1. Первое окно Мастера сводных таблиц и диаграмм
Заметьте, из четырех возможностей задания разных типов источников данных - списков Excel, внешних источников, нескольких диапазонов, другой сводной таблицы - я выбрал внешний источник данных, поскольку, как я уже говорил, буду строить сводную таблицу, используя базу данных Access. Вторая группа переключателей позволяет задать желаемый вид отчета - сводную таблицу или сводную диаграмму, построенную на основе сводной таблицы. Пример с диаграммой приведем чуть позже, а сейчас займемся чисто сводными таблицами. Сделав выбор, остается нажать кнопку "Далее", что заставляет Мастера сделать очередной шаг. Вот окно, открываемое на втором шаге:
Рис. 8.2. Окно второго шага Мастера сводных таблиц
Взгляните на результат моей работы:
Рис. 8.5. Выбор полей базы данных для включения их в сводную таблицу
Нажатие кнопки "Next" заставляет Мастера запросов перейти к очередному шагу. Что будет выполняться на следующем шаге, зависит от того, сумеет ли Мастер запросов извлечь требуемые данные из таблиц базы данных. Если проблем у него не возникает, то Мастер запросов предложит создать фильтр для отбираемых данных. Вот как выглядит соответствующее окно в несколько более простой ситуации, когда не включено поле "Город" в число полей, запрашиваемых для построения сводной таблицы.
Рис. 8.6. Запрос на построение фильтров
Окно, следующее за построением фильтров, позволяет задать требуемый порядок сортировки данных. Я не буду приводить его. Взгляните, как выглядит следующее окно, завершающее построение запроса для рассматриваемой ситуации:
Рис. 8.7. Завершающий шаг построения запроса
На этом шаге можно, как видите, сохранить запрос, нажав соответствующую командную кнопку. Здесь также следует сделать выбор одной из трех возможностей:
- вернуть данные в Excel и возвратиться к очередному шагу работы Мастера сводных таблиц и диаграмм,
- перейти в Microsoft Query и там продолжить работу над запросом,
- перейти к построению OLAP куба.
Обычная практика состоит в том, что выбирается первый пункт, и данные возвращаются в Excel. Мы тоже вернемся в Excel, но чуть попозже, а пока рассмотрим исходную ситуацию, когда поле "Город" включено в запрос. Эта ситуация при построении запроса оказалась чуть более сложной, и у Мастера построения запросов возникли некоторые трудности, - он оказался не в состоянии разобраться в связях между таблицами базы данных, и попросил выполнить эту работу вручную, перейдя в Microsoft Query. Вот появляющееся сообщение о возникших у него трудностях:
Рис. 8.8. Сообщение о возникших трудностях у мастера запросов
Заметьте, при работе с моей базой данных я установил все необходимые связи, что видно из рисунка, отображающего схему данных:
Рис. 8.9. Схема данных базы "dbPP2000"
Тем не менее, я не отказываюсь помочь Мастеру и выполняю требуемую им работу. Путем перетаскивания полей я добавляю отсутствующую связь между таблицами "Заказчики" и "Заказы". Таблицы связаны общим полем - "Заказчик" в таблице "Заказы", "Название" в таблице "Заказчики". Вот как выглядит окно Мастера запросов, в котором я уже выполнил необходимую работу:
Рис. 8.10. Установление связей между таблицами
Открывшееся окно Microsoft Query имеет главное меню из многих пунктов и панель с набором инструментальных кнопок. Но я не буду описывать весь спектр возможностей этого инструмента. Нам еще придется работать с ним в дальнейшем. Сейчас же, установив связи между таблицами, и, тем самым, выполнив всю требуемую работу, я просто закрыл это окно, что возвращает нас ко второму шагу Мастера сводных таблиц и диаграмм, но уже в новом состоянии, когда данные для построения сводной таблицы получены:
Рис. 8.11. Новое состояние окна Мастера сводных таблиц на втором шаге
Заметьте, теперь, в отличие от рисунка 8.2, наряду с уведомлением о получении данных стала доступной кнопка "Далее", которую я и нажал для перехода к последнему шагу работы Мастера:
Рис. 8.12. Заключительный шаг работы Мастера сводных таблиц
На заключительном шаге работы можно указать рабочий лист и ячейку, начиная с которой будет располагаться сводная таблица. Заметьте, наряду с кнопкой "Готово", нажатие которой завершает работу Мастера, в нашем распоряжении есть и другие кнопки, в частности, кнопка "Макет". Вот как выглядит окно макета сводной таблицы:
Рис. 8.13. Макет сводной таблицы
На макете представлена схема сводной таблицы, - четыре области таблицы, озаглавленные соответственно "Страница", "Строка", "Столбец" и "Данные". На макете также представлены поля, отобранные для построения сводной таблицы. Каждое из полей может быть перетащено в одну из областей таблицы. Заметьте, вовсе не обязательно перетаскивать сразу все поля. Как я уже говорил, одно из достоинств сводных таблиц состоит в том, что их структуру можно легко перестраивать в зависимости от целей, которые менеджер, работающий с таблицей, ставит при анализе данных.
Я не стал работать с макетом таблицы, предпочтя окончательную работу по формированию таблицы сделать чуть позже. Поэтому вместо кнопки "Макет" в окне, показанном на рис. 8.10, я нажал кнопку "Готово". В результате Мастер сводных таблиц разместил на выбранном рабочем листе по существу макет сводной таблицы, открыл инструментальную панель с именем "Сводные таблицы", и на этом завершил свою работу. Вот как выглядит рабочий лист Excel по окончании работы Мастера:
Рис. 8.14. Рабочий лист с макетом сводной таблицы и инструментальной панелью
Теперь пришла пора заключительного этапа формирования структуры сводной таблицы, - необходимо разумным образом переместить доступные поля в четыре области таблицы. Заметьте, не обязательно перемещать все поля и не обязательно заполнять область страниц. Разумность задания той или иной структуры сводной таблицы определяется целями проводимого анализа, опытом и привычкой.
Я в данном примере размещу все поля и приведу некоторые аргументы в пользу выбранного мной варианта размещения полей:
- В область данных я поместил поля "Стоимость" и "Количество". Это, наверное, совершенно естественный выбор, когда речь идет об анализе продаж какого либо товара. Область данных в этом случае отображает данные о продажах в количественном и стоимостном выражении.
- В область столбцов я поместил поле с названиями книг. По сути, это названия продаваемых товаров.
- В область строк я поместил два поля - "ДатаЗаказа" и "Сотрудники".
- В область страниц я также поместил два поля - "Заказчики" и "Город", задающий расположение заказчиков.
