Сводные таблицы. Язык визуального программирования приложений Visual Basic for Application

Сводные таблицы являются одним из наиболее мощных средств Excel для анализа данных, помещенных в таблицы или списки. Сводные таблицы позволяют группировать данные и производить их анализ. Создавая сводные таблицы, пользователь оперирует именами полей, которые должны помещаться в ее строках и столбцах. Возможно также задание поля страницы, превращающего сводную таблицу в подшивку из нескольких страниц. Иерархически сводная таблица входит в рабочий лист. Все сводные таблицы рабочей книги образуют семейство pivotTabies (сводные таблицы), которое содержит в себе семейство pivotFieids (поля сводной таблицы) всех полей, входящих в сводную таблицу. Объект Pivotitem (элемент сводной таблицы) является конкретным элементом объекта pivotFieid. Все объекты Pivotitem образуют семейство Pivotitems. На рис. 6.1 показана иерархия этих семейств.

Рис. 6.1. Иерархия семейства объектов PivotTabies, PivotFieids и Pivotitems

Семейство PivotTabies имеет единственный метод item, возвращающий элемент этого семейства, т. е. конкретную сводную таблицу.

Из свойств семейства PivotTabies отметим только свойство Count, возвращающее число элементов этого семейства.

Программно сводная таблица создается методом PivotTableWizard. Вручную на рабочем листе сводная таблица конструируется с помощью команды Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report).

TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache, PageFieldOrder, PageFieldWrapCount, ReadData, Connection)


	Объект	Объект Worksheet (рабочий лист ) или PivotTable (сводная таблица)
	SourceType	Тип источника данных. Допустимые значения: xlConsolidation (консолидация нескольких диапазонов рабочих листов Excel) xlDatabase (список или база данных Excel) xlExternal (внешняя база данных) xlPivotTabie (сводная таблица)
	SourceData	Определяет вид источника данных в зависимости от значения аргумента SourceType: Диапазон, если значением аргумента является xlDatabase Массив строк, содержащий строку связи ODBC и SQL-оператор, если — xlExternal Массив диапазонов, если — xlConsolidation Имя существующей сводной таблицы, если -xlPivotTable
	TableDestination	Диапазон, где будет размещена сводная таблица
	TableName	Имя создаваемой сводной таблицы
	RowGrand	Допустимые значения: True (отображается суммарный итог по строкам сводной таблицы) и False (итог не отображается)
	ColumnGrand	Допустимые значения: True (отображается суммарный итог по столбцам сводной таблицы) и False (итог не отображается)


	SaveData	Допустимые значения: True (сохраняются данные вместе со сводной таблицей) и False (сохраняется только сводная таблица)
	HasAuto Format	Допустимые значения: True (автоматическое пере-форматирование сводной таблицы при изменении данных) и False (в противном случае)
	AutoPage	Применим только при аргументе sourceType, равным xlConsolidation. Допустимые значения: True (Excel создает поле страницы) и False (пользователь должен создать поле)
	Reserved	Не используется
	BackgroundQuery	Допустимые значения: True (Excel выполняет запрос в фоновом режиме) и False (в последовательном)
	OptimizeCache	Допустимые значения: True (создается сводная таблица в режиме оптимизации, применяется для сводных таблиц, обрабатывающих большие базы данных) и False (оптимизация выключена, что убыстряет создание сводной таблицы)
	PagePieldOrder	Задает ориентацию поля страницы. Допустимые значения: xlDownThenOver (поле страницы располагается вертикально) и xlOverThenDown (поле страницы располагается горизонтально)
	PageFieldWrapCount	Задает номер поля, с которого начинается новая страница. По умолчанию 0, т. е. отменена разбивка на страницы
	ReadData	Допустимые значения: True (данные сразу считываются в кэш) и False (данные считываются в кэш по мере необходимости)
	Connection	Используется для указания источника данных ODBC, источника данных URL и имени файла, содержащего запрос

С методом pivotTableWizard тесно связан метод PivotTables, применяемый к рабочему листу. Метод PivotTabies возвращает объект PivotTable или семейство сводных таблиц, размещенных на рабочем листе. Этот метод имеет два синтаксиса.

Возвращает семейство сводных таблиц. Здесь и во втором синтаксисе объект -рабочий лист.

Возвращает сводную таблицу из семейства сводных таблиц с именем или номером, указанным в аргументе index .

Объект PivotTable имеет следующие наиболее часто используемые методы.


	PivotFields	Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей. Синтаксис 1: PivotFields (Index) Index — имя или номер поля сводной таблицы Синтаксис 2: PivotFields
	PivotSelect	Выбирает элементы сводной таблицы. Синтаксис: PivotSelect (Name, Mode) Аргументы: G Name — строковое выражение, идентифицирующее выбранный элемент Mode — специфицирует структуры выбранного элемента. Допустимые значения: xlBlanks, xiButton, xlDataAndLabel, xlDataOnly, xlLabelOnly или xlOrigin
	RefreshTable	Обновляет данные. Дело в том, что в сводной таблице не происходит автоматического перерасчета при изменении исходных данных. Для перерасчета сводной таблицы вручную надо ее выделить и выбрать команду Данные, Обновить данные (Data, Refresh Data). Программно перерасчет сводной таблицы производится методом RefreshTable
	AddFields	Добавляет строки, столбцы и страницы в сводную таблицу. Синтаксис: AddFields (RowFields, ColumnFields, PageFields, AddToTable) Аргументы: RowFields — специфицирует имя или массив имен полей, которые будут играть роль строк сводной таблицы ColumnFields — специфицирует имя или массив имен полей, которые будут играть роль столбцов сводной таблицы FageFields — специфицирует имя или массив имен полей, которые будут играть роль страниц сводной таблицы AddToTabie — допустимые значения: True (добавляет поля в сводную таблицу) и False (заменяет существующие поля)

Объект pivotTabie имеет следующие наиболее часто используемые свойства.


	ColumnFields, RowFields, DataFields И PageFields	Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей (синтаксис 2), который является столбцом (строкой, данными или страницей) сводной таблицы. Синтаксис 1: ColumnFields (Index) RowFields (Index) DataFields (Index) PageFields (Index) Index — имя или номер поля сводной таблицы Синтаксис 2: ColumnFields RowFields DataFields PageFields
	VisibleFields и HiddenFields	Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей (синтаксис 2), который в данный момент отображается (скрыт) в сводной таблице. Синтаксис 1: VisibleFields (Index) HiddenFields (Index) Index — имя или номер поля сводной таблицы Синтаксис 2: VisibleFields HiddenFields

Объект PivotField имеет следующие наиболее часто используемые свойства.

Orientation Возвращает местоположение поля в сводной таблице.

Возвращает позицию поля (первая, вторая и т. д.) среди полей того же местоположения

Рассмотрим соответствие между аргументами метода pivotTabiewizard и созданием сводной таблицы вручную на рабочем листе с помощью команды Данные, Сводная таблица (Data, Pivot Table). Сводную таблицу будем создавать для отчета по продажам компьютеров сети из трех магазинов (рис. 6.2).

Рис. 6.2. Отчет о продаже компьютеров сети из трех магазинов


	Шаг 1	Выберите команду Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report). Появится первое диалоговое окно мастера сводных таблиц (рис. 6.3). Выбор переключателей, расположенных под заголовком Создать таблицу на основе данных, находящихся: (Where is the data that you want to analyze?) позволяет установить источник данных для сводной таблицы. Переключатель в списке или базе данных Microsoft Excel (Microsoft Excel List or Database) устанавливает создание сводной таблицы на основе списка данных, расположенных на рабочем листе. Переключатель во внешнем источнике данных (External Data Source) устанавливает создание сводной таблицы на основе файлов или таблиц, созданных другими программами. Переключатель в нескольких диапазонах консолидации (Multiple Consolidation Ranges) устанавливает создание сводной таблицы на основе нескольких списков. Переключатель в другой сводной таблице (Another Pivot Table) устанавливает создание сводной таблицы на основе другой существующей сводной таблицы. Установите переключатель в списке или базе данных Microsoft Excel (Microsoft Excel List or Database), т. к. сводная таблица будет создаваться на основе одного списка активного рабочего листа. Нажмите кнопку Далее > (Next >)

Рис. 6.3. Первое диалоговое окно мастера сводных таблиц

Рис. 6.4. Второе диалоговое окно мастера сводных таблиц


	ШагЗ	На экране появится третье диалоговое окно мастера сводных таблиц (рис. 6.5), в котором создается структура сводной таблицы. Выберите поле, по которому будут подводиться итоги сводных таблиц, и перетащите кнопку, соответствующую этому полю в область Данные (Data). В данном случае перетащим кнопку Стоимость в область Данные (Data) (рис. 6.6). Двойной щелчок на кнопке Стоимость в области Данные (Data) приводит к отображению диалогового окна Вычисления поля сводной таблицы (PivotTable Field), позволяющее установить операцию, на основе которой будут подводиться итоги. Выберите операцию Сумма (Sum). Выберите поля, которые будут образовывать строки сводной таблицы и перетащите кнопки, соответствующие этим полям в область Строка (Row). В данном случае строки сводной таблицы будут соответствовать магазинам. Поэтому перетащим кнопку магазин в область Строка (Row). Выберите поля, которые будут образовывать столбцы сводной таблицы и перетащите кнопки, соответствующие этим полям в область Столбец (Column). В данном случае строки сводной таблицы будут соответствовать месяцам. Поэтому перетащим кнопку месяц в область Столбец (Column). Если вы хотите создать сводную таблицу в виде подшивки страниц, то перетащите кнопку, соответствующую полю, по которому будут строиться страницы в область Страница (Page). В данном случае сводная таблица, состоящая из страниц, не строится и поэтому не будем перетаскивать никакую кнопку в область Страница (Page). Нажмите кнопку Далее > (Next >).

Рис. 6.5. Третье диалоговое окно мастера сводных таблиц

Рис. 6.6. Третье диалоговое окно мастера сводных таблиц после создания структуры сводной таблицы


	ШагЗ	Появится последнее четвертое диалоговое окно мастера сводных таблиц (рис. 6.7). В этом окне устанавливается местоположение сводной таблицы. П Выбор переключателя новый лист (New worksheets) приводит к созданию нового рабочего листа и размещения на нем сводной таблицы. Выберите переключатель новый лист. П Выбор переключателя существующий лист* (Existing worksheets) позволяет разместить сводную таблицу в любом месте уже существующего листа. Для этого в поле Поместить таблицу в (Pivot Table Starting Cell) надо указать ссылку на ячейку, в которой будет располагаться левый верхний угол сводной таблицы. О Нажав кнопку Параметры (Options) можно вызвать диалоговое окно Параметры сводной таблицы (Pivot Table Options), где задаются имя сводной таблицы и ее формат. Нажмите кнопку Готово (Finish).
	Шаг 4	Сводная таблица построена (рис. 6.8). Такую же сводную таблицу можно построить при помощи следующей последовательности инструкций VBA: ActiveSheet. PivotTableWizard SourceType:=xlDatabase, SourceData:= "Лист1 !R1C1:R16C5", TableDestination:="", TableName : ="СводнаяТаблица1 " ActiveSheet . PivotTables ( "СводнаяТаблица! " ) . AddFields RowFields : ="Магазин" , ColumnFields : ="Месяц" ActiveSheet . PivotTables ( "СводнаяТаблица! " } . PivotFields ("Стоимость") .Orientation = xlDataField Если в данных, по которым строилась сводная таблица, произошли изменения, для перерасчета сводной таблицы надо ее выделить и выбрать команду Данные, Обновить данные (Data, Refresh Data). Программно перерасчет сводной таблицы осуществляется следующими инструкциями: ActiveSheet . PivotTables ( "СводнаяТаблица! " ) . PivotSelect "", xlDataAndLabel ActiveSheet . PivotTables ("СводнаяТаблица!") . Ref reshTable

Рис. 6.7. Четвертое диалоговое окно мастера сводных таблиц

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

Рис. 6.9. Сводная таблица после удаления поля Месяц


	Перемещение поля	Местоположение полей сводной таблицы легко изменяется. Для этого достаточно расположить указатель мыши на требуемом поле и перетащить его на новое место. Например, переместим в первоначальной сводной таблице поле месяц из столбцов в строки (рис. 6.10). Программно тот же результат получается при помощи следующих двух инструкций: ActiveSheet . PivotTables ("СводнаяТаблица!") . _ PivotSelect "Месяц [Все] ", xlLabelOnly With ActiveSheet . PivotTables ( "СводнаяТаблица!"). PivotFields ("Месяц") .Orientation = xlRowField .Position = 1 End With
	Группировка полей	Поля сводной таблицы можно группировать. Например, объединим магазины Альфа и Бета в одну группу. Для этого надо выделить поля этих магазинов и выбрать команду Данные, Группа и структура, Группировать (Data, Group and Outline, Group) (рис. 6.11). Программно тот жерезультат получается при помощи следующих двух инструкций: ActiveSheet . PivotTables ( "СводнаяТаблица1 " ) . PivotSelect "Альфа: Бета", xlDataAndLabel Selection . Group Скрыть детали элементов группы можно командой Данные, Группа и структура, Скрыть детали (Data, Group and Outline, Hide Details) (рис. 6.12), предварительно выделив поле этой группы. Программно тот же результат получается при помощи следующих двух инструкций: ActiveSheet . PivotTables ( "СводнаяТаблица1 " ) . PivotSelect "Группа1 Январь", xlDataAndLabel Selection. ShowDetail = False Показать детали элементов группы можно, выполнив команду Данные, Группа и структура, Отобразить детали (Data, Group and Outline, Show Details). Программно это осуществляется инструкциями: Active Sheet . PivotTables ("СводнаяТаблица! " ). PivotSelect "Группа! Январь", xlDataAndLabel Selection. ShowDetail = True

Рис. 6.10. Сводная таблица после перемещения поля Месяц из столбцов в строки

Рис. 6.11. Сводная таблица после группировки магазинов Альфа и Бета

Рис. 6.12. Сводная таблица со скрытой детализацией элементов группы магазинов Альфа и Бета


	Шаг 2	На экране появится второе диалоговое окно мастера сводных таблиц (рис. 6.4). В поле Диапазон (Range) введите ссылку, например AI :Е1б, на диапазон, по которому будет строиться сводная таблица. Нажмите кнопку Далее > (Next >).


	Удаление поля	Для удаления поля сводной таблицы достаточно расположить на нем указатель мыши и перетащить поле за пределы сводной таблицы. Например, удалим поле месяц (рис. 6.9). Программно тот же результат получается при ** помощи следующих двух инструкций: ActiveSheet . PivotTables (СводнаяТаблица!" ) . _ PivotSelect "Месяц", xlButton ActiveSheet. PivotTables ("СводнаяТаблица!") . _ PivotFields ("Месяц") .Orientation = xlHidden