Сводные таблицы
являются одним из наиболее мощных средств Excel для анализа данных, помещенных
в таблицы или списки. Сводные таблицы позволяют группировать данные и производить
их анализ. Создавая сводные таблицы, пользователь оперирует именами полей, которые
должны помещаться в ее строках и столбцах. Возможно также задание поля страницы,
превращающего сводную таблицу в подшивку из нескольких страниц. Иерархически
сводная таблица входит в рабочий лист. Все сводные таблицы рабочей книги образуют
семейство pivotTabies (сводные таблицы), которое содержит в себе семейство pivotFieids
(поля сводной таблицы) всех полей, входящих в сводную таблицу. Объект Pivotitem
(элемент сводной таблицы) является конкретным элементом объекта pivotFieid.
Все объекты Pivotitem образуют семейство Pivotitems. На рис. 6.1 показана иерархия
этих семейств.
Рис.
6.1. Иерархия семейства объектов PivotTabies, PivotFieids и Pivotitems
Семейство
PivotTabies имеет единственный метод item, возвращающий элемент этого семейства,
т. е. конкретную сводную таблицу.
Синтаксис:
Item(Index)
Из свойств
семейства PivotTabies отметим только свойство Count, возвращающее число элементов
этого семейства.
Программно
сводная таблица создается методом PivotTableWizard. Вручную на рабочем листе
сводная таблица конструируется с помощью команды Данные, Сводная таблица
(Data, Pivot Table and Pivot Chart Report).
Синтаксис:
Объект.PivotTableWizard(SourceType,
SourceData, TableDestination,
TableName, RowGrand,
ColumnGrand, SaveData, HasAutoFormat, AutoPage, Reserved, BackgroundQuery, OptimizeCache,
PageFieldOrder, PageFieldWrapCount, ReadData, Connection)
Аргументы
Объект |
Объект Worksheet
(рабочий лист ) или PivotTable (сводная таблица) |
||
SourceType |
Тип источника данных. Допустимые значения:
|
||
SourceData |
Определяет вид
источника данных в зависимости от значения аргумента SourceType:
|
||
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 или семейство сводных таблиц,
размещенных на рабочем листе. Этот метод имеет два синтаксиса.
Синтаксис
1:
Объект.PivotTabies
Возвращает
семейство сводных таблиц. Здесь и во втором синтаксисе объект -рабочий лист.
Синтаксис
2:
Объект.PivotTables(Index)
Возвращает
сводную таблицу из семейства сводных таблиц с именем или номером, указанным
в аргументе index .
Объект PivotTable
имеет следующие наиболее часто используемые методы.
PivotFields |
Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей. Синтаксис 1: PivotFields (Index)
PivotFields |
||
PivotSelect |
Выбирает элементы сводной таблицы. Синтаксис: PivotSelect (Name,
Mode) Аргументы: G Name — строковое выражение, идентифицирующее выбранный элемент
xlBlanks,
xiButton,
xlDataAndLabel,
xlDataOnly, xlLabelOnly или xlOrigin |
||
RefreshTable |
Обновляет данные.
Дело в том, что в сводной таблице не происходит автоматического перерасчета
при изменении исходных данных. Для перерасчета сводной таблицы вручную
надо ее выделить и выбрать команду Данные, Обновить данные (Data,
Refresh Data). Программно перерасчет сводной таблицы производится
методом RefreshTable |
||
AddFields |
Добавляет строки,
столбцы и страницы в сводную таблицу. Синтаксис: AddFields (RowFields,
ColumnFields, PageFields, AddToTable) Аргументы:
|
||
Объект pivotTabie
имеет следующие наиболее часто используемые свойства.
ColumnFields,
RowFields, DataFields И PageFields |
Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей (синтаксис 2), который является столбцом (строкой, данными или страницей) сводной таблицы. Синтаксис 1: ColumnFields (Index) RowFields (Index) DataFields (Index) PageFields (Index)
Синтаксис 2: ColumnFields RowFields DataFields PageFields |
||
VisibleFields
и HiddenFields |
Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей (синтаксис 2), который в данный момент отображается (скрыт) в сводной таблице. Синтаксис 1: VisibleFields (Index) HiddenFields (Index)
Синтаксис 2: VisibleFields HiddenFields |
||
Объект PivotField имеет следующие наиболее часто используемые свойства.
Orientation
Возвращает местоположение поля в сводной таблице.
Допустимые значения:
xlColumnField,
xlDataField,
xlHidden, xlPageField
или xlRowField
Возвращает
позицию поля (первая, вторая и т. д.) среди полей того же местоположения
Рассмотрим
соответствие между аргументами метода 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), т. к. сводная таблица будет создаваться
на основе одного списка активного рабочего листа. Нажмите кнопку Далее
> (Next >)
|
||
Рис.
6.3. Первое диалоговое окно мастера сводных таблиц
Шаг 2 |
На экране появится
второе диалоговое окно мастера сводных таблиц (рис. 6.4). В поле Диапазон
(Range) введите ссылку, например AI :Е1б, на диапазон, по которому
будет строиться сводная таблица. Нажмите кнопку Далее > (Next >). |
||
Рис.
6.4. Второе диалоговое окно мастера сводных таблиц
ШагЗ |
На экране появится третье диалоговое окно мастера сводных таблиц (рис. 6.5), в котором создается структура сводной таблицы.
|
||
Рис.
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.8. Сводная таблица
Преобразования
свободной таблицы
Рассмотрим
наиболее часто используемые операции преобразования сводной таблицы.
Удаление
поля |
Для удаления
поля сводной таблицы достаточно расположить на нем указатель мыши
и перетащить поле за пределы сводной таблицы. Например, удалим поле
месяц (рис. 6.9). Программно тот же результат получается при ** помощи
следующих двух инструкций: ActiveSheet . PivotTables (СводнаяТаблица!" ) . _ PivotSelect "Месяц", xlButton ActiveSheet. PivotTables ("СводнаяТаблица!") . _ PivotFields ("Месяц")
.Orientation = xlHidden |
||
Рис.
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. Сводная таблица со скрытой детализацией элементов группы магазинов
Альфа и Бета
|
![]() |