Мир объектов Excel 2000

         

Построение сводной таблицы вручную


Сводные таблицы обычно строятся вручную специальным инструментом - Мастером сводных таблиц. Сравнительно просто построить эти таблицы и программно. Программист, конечно, должен уметь пользоваться таким инструментом, как Мастер сводных таблиц, хотя главное для него - знание объектной модели сводной таблицы. Причина понятна, - в разрабатываемом программистом офисном приложении не всегда можно требовать от пользователя умение строить самому сводную таблицу и часто эту таблицу надо строить программно, выяснив в диалоге требования пользователя. По сути, надо уметь строить собственного Мастера, который вел бы диалог с пользователем на более понятном языке, подходящем к данной конкретной ситуации.

Программная реализация предполагает знакомство с соответствующими классами объектов. Сама таблица и все ее части являются объектами, классы которых содержат большое количество свойств и методов. Об объектной модели сводной таблицы поговорим чуть позже, а сейчас рассмотрим на конкретном примере построение таблицы с использованием Мастера сводных таблиц. В примере я использую в качестве источника данных уже созданную базу данных офиса "Родная Речь", с которой шла работа в предыдущей главе. Замечу, что эта база данных в офисе используется уже давно, и потому там хранятся сведения о продажах за несколько лет.

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

  • Как шли продажи в стоимостном и количественном исчислении за те или иные периоды времени?
  • Какие книги продавались наиболее успешно?
  • Кто из сотрудников офиса оформлял наибольшее число заказов?
  • С кем из заказчиков шла наиболее успешная работа?
  • С какими городами шло наиболее успешное сотрудничество?

Наша сводная таблица должна позволять менеджеру, проводящему анализ отвечать на все эти вопросы. Вся необходимая информация хранится в таблицах: "Заказы", "Заказано", "Заказчики". Поля из этих таблиц и будут использоваться при построении сводной таблицы.

Итак, цель ясна - приступим?

В документе 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.  Рабочий лист с макетом сводной таблицы и инструментальной панелью

Теперь пришла пора заключительного этапа формирования структуры сводной таблицы, - необходимо разумным образом переместить доступные поля в четыре области таблицы. Заметьте, не обязательно перемещать все поля и не обязательно заполнять область страниц. Разумность задания той или иной структуры сводной таблицы определяется целями проводимого анализа, опытом и привычкой.

Я в данном примере размещу все поля и приведу некоторые аргументы в пользу выбранного мной варианта размещения полей:

  • В область данных я поместил поля "Стоимость" и "Количество". Это, наверное, совершенно естественный выбор, когда речь идет об анализе продаж какого либо товара. Область данных в этом случае отображает данные о продажах в количественном и стоимостном выражении.
  • В область столбцов я поместил поле с названиями книг. По сути, это названия продаваемых товаров.
  • В область строк я поместил два поля - "ДатаЗаказа" и "Сотрудники".
  • В область страниц я также поместил два поля - "Заказчики" и "Город", задающий расположение заказчиков.



Содержание раздела