Сводные таблицы являются одним из наиболее мощных средств Excel для анализа данных, помещенных в таблицы или списки. Сводные таблицы позволяют группировать данные и производить их анализ. Создавая сводные таблицы, пользователь оперирует именами полей, которые должны помещаться в ее строках и столбцах. Возможно также задание поля страницы, превращающего сводную таблицу в подшивку из нескольких страниц. Иерархически сводная таблица входит в рабочий лист. Все сводные таблицы рабочей книги образуют семейство pivotTabies (сводные таблицы), которое содержит в себе семейство pivotFieids (поля сводной таблицы) всех полей, входящих в сводную таблицу. Объект Pivotitem (элемент сводной таблицы) является конкретным элементом объекта pivotFieid. Все объекты Pivotitem образуют семейство Pivotitems. На рис. 6.1 показана иерархия этих семейств.
Рис. 6.1. Иерархия семейства объектов PivotTabies, PivotFieids и Pivotitems
Семейство PivotTabies имеет единственный метод item, возвращающий элемент этого семейства, т. е. конкретную сводную таблицу.
Синтаксис:
Item(Index)
Index — имя или номер возвращаемого элемента семейства PivotFieids
Из свойств семейства PivotTabies отметим только свойство Count, возвращающее число элементов этого семейства.
Метод PivotTableWizard
Программно сводная таблица создается методом PivotTableWizard. Вручную на рабочем листе сводная таблица конструируется с помощью команды Данные, Сводная таблица (Data, Pivot Table and Pivot Chart Report).
Объект 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 или семейство сводных таблиц, размещенных на рабочем листе. Этот метод имеет два синтаксиса.
Синтаксис 1:
Объект.PivotTabies
Возвращает семейство сводных таблиц. Здесь и во втором синтаксисе объект -рабочий лист.
Синтаксис 2:
Объект.PivotTables(Index)
Возвращает сводную таблицу из семейства сводных таблиц с именем или номером, указанным в аргументе 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
Добавляет строки, столбцы и страницы в сводную таблицу.
RowFields — специфицирует имя или массив имен полей, которые будут играть роль строк сводной таблицы
ColumnFields — специфицирует имя или массив имен полей, которые будут играть роль столбцов сводной таблицы
FageFields — специфицирует имя или массив имен полей, которые будут играть роль страниц сводной таблицы
AddToTabie — допустимые значения: True (добавляет поля в сводную таблицу) и False (заменяет существующие поля)
Объект pivotTabie имеет следующие наиболее часто используемые свойства.
ColumnFields, RowFields, DataFields
И
PageFields
Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей (синтаксис 2), который является столбцом (строкой, данными или страницей) сводной таблицы.
Возвращает объект, являющийся либо единичным полем (синтаксис 1), либо семейством полей (синтаксис 2), который в данный момент отображается (скрыт) в сводной таблице.
Синтаксис 1:
VisibleFields (Index) HiddenFields (Index)
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) устанавливает создание сводной таблицы на основе списка данных, расположенных на рабочем листе.
Переключатель во внешнем источнике данных (External Data Source) устанавливает создание сводной таблицы на основе файлов или таблиц, созданных другими программами.
Переключатель в нескольких диапазонах консолидации (Multiple Consolidation Ranges) устанавливает создание сводной таблицы на основе нескольких списков.
Переключатель в другой сводной таблице (Another Pivot Table) устанавливает создание сводной таблицы на основе другой существующей сводной таблицы.
Установите переключатель в списке или базе данных Microsoft
Excel (Microsoft Excel List or Database), т. к. сводная таблица будет создаваться на основе одного списка активного рабочего листа. Нажмите кнопку Далее > (Next >)
Рис. 6.3. Первое диалоговое окно мастера сводных таблиц
Шаг 2
На экране появится второе диалоговое окно мастера сводных таблиц (рис. 6.4). В поле Диапазон (Range) введите ссылку, например AI :Е1б, на диапазон, по которому будет строиться сводная таблица. Нажмите кнопку Далее > (Next >).
Рис. 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:
Если в данных, по которым строилась сводная таблица, произошли изменения, для перерасчета сводной таблицы надо ее выделить и выбрать команду Данные, Обновить данные (Data, Refresh Data). Программно перерасчет сводной таблицы осуществляется следующими инструкциями:
Рис. 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 .
Поля сводной таблицы можно группировать. Например, объединим магазины Альфа и Бета в одну группу. Для этого надо выделить поля этих магазинов и выбрать команду Данные, Группа и структура, Группировать (Data, Group and Outline, Group) (рис. 6.11). Программно тот жерезультат получается при помощи следующих двух инструкций:
Скрыть детали элементов группы можно командой Данные, Группа и структура, Скрыть детали (Data, Group and Outline, Hide Details) (рис. 6.12), предварительно выделив поле этой группы. Программно тот же результат получается при помощи следующих двух инструкций:
Показать детали элементов группы можно, выполнив команду Данные, Группа и структура, Отобразить детали (Data, Group and Outline, Show Details). Программно это осуществляется инструкциями:
Гостей: 6
Скрытых: 0
Видимых: 0
Всего:
