Сводную таблицу можно создать «вручную» с помощью команды Сводная таблица из меню Данные. Программный способ создания сводной таблицы основан на использовании метода PivotTableWizard объекта Worksheet. Объект PivotTable используется только для импорта данных.
Данные в сводной таблице редактировать нельзя, но можно скопировать эти данные из сводной таблицы в диапазон рабочего листа, сделать нужные изменения, экспортировать отредактированные данные в исходную базу данных и выполнить метод RefreshTable сводной таблицы.
Для создания сводной таблицы средствами VBA, как уже было сказано, необходимо вызвать метод PivotTableWizard рабочего листа (объекта Worksheet). Этот метод имеет следующие обязательные аргументы:
– SourceType – признак источника данных: xlDatabase – список или база данных Excel, xlExternal – внешняя база данных, xlConsolidation – консолидация нескольких диапазонов рабочих листов, xlPivotTable – существующая сводная таблица;
– SourceData – источник данных, зависящий от первого аргумента: или диапазон, или массив строк, содержащих строку связи ODBC и оператор SQL, или массив диапазонов, или имя существующей сводной таблицы;
– TableDestination – диапазон, в который будет помещена сводная таблица;
– TableName – имя, которое будет присвоено сводной таблице;
– RowGrand – управляет отображением суммарного итога всех строк таблицы (если True – итог отображается, если False – нет);
– ColumnGrand – управляет отображением суммарного итога всех столбцов таблицы (если True – итог отображается, если False – нет);
– SaveData – управление сохранением внутреннего кэша (если True – содержащий данные сводной таблицы внутренний кэш сохраняется вместе с таблицей в файле рабочей книги, если False – нет);
– HasAutoFormat – если этот аргумент равен True, то при изменении данных Excel автоматически переформатирует таблицу;
– AutoPage – если этот аргумент имеет значение True, Excel автоматически создает поле страницы в сводной таблице (используется только при консолидации диапазонов) ;
– Reserved – зарезервирован, остается пустым;
– OptimizeCache – используется для оптимизации создания сводных таблиц с большими и сложными данными;
– PageFieldOrder – используется для физической ориентации поля страницы на рабочем листе;
– PageFieldWrapCount – задает номер поля страницы, с которого начинается новая строка или столбец;
– ReadData – если равен True, то данные сразу считываются в сводный кэш из внешней базы данных, если False – данные считываются по мере необходимости;
– Connection – используется для указания источника данных ODBC, источника данных URL или имени файла, содержащего запрос.
При создании сводной таблицы с помощью описанного метода Excel только считывает данные во внутренний кэш и резервирует место для сводной таблицы на рабочем листе. У Excel пока нет информации о том, какие поля нужно помещать в каждую область сводной таблицы (в таблице используются области «Строка», «Столбец», «Данные» и «Страница»), поэтому данные не отображаются на рабочем листе. Таким образом, запросы сводной таблицы действуют на двух уровнях: запрос первого уровня действует для перемещения данных из основного источника данных в кэш сводной таблицы; запросы второго уровня используются для вывода данных из внутреннего кэша в том виде, как они отображаются в таблице. Запросы второго уровня выполняются путем использования свойств и методов объектов PivotTable, PivotField и PivotItem.
Структура сводной таблицы показана на рис. 22.
Поле базы данных можно помещать в любую область сводной таблицы. Для этого можно использовать метод AddFields объекта PivotTables или присвоить свойству Orientation объекта PivotField (поля, включенного в сводную таблицу) одно из приведенных ниже значений констант:
xlColumnField – поместить поле в область «Столбец» сводной таблицы;
xlDataField – поместить поле в область «Данные»;
xlHidden – скрыть поле (поле не отображается ни в каких областях);
xlPageField – поместить поле в область «Страница»;
xlRowField – поместить поле в область «Строка».
Доступ к отдельному значению можно получить через объект PivotItem.
Ниже приведен пример оператора, создающего на рабочем листе сводную таблицу на основе базы данных Excel:
Для определения структуры сводной таблицы следует работать уже с созданным объектом PivotTable. Этот объект содержит семейство PivotFields в котором находятся все поля базы данных, к которым возможен доступ либо по имени поля, либо по его номеру. Используя это семейство, можно помещать различные поля в различные области сводной таблицы. Например:
Объект PivotTable имеет несколько свойств и методов, которые можно использовать для управления структурой таблицы и отображением данных в сводной таблице.
Свойства объекта PivotTable:
– ColumnRange – диапазон ячеек, включающий область «Столбец» (в том числе столбец заголовков) в сводной таблице;
– RowRange – диапазон ячеек, включающий область «Строка» (в том числе строку заголовков) в сводной таблице;
– PageRange – диапазон ячеек, включающий область «Страница» в сводной таблице;
– DataBodyRange – диапазон ячеек, включающий область «Данные» в сводной таблице;
– DataLabelRange – диапазон, содержащий имя (имена) поля (полей) сводной таблицы в области «Данные»;
– TableRange1 – диапазон, включающий области «Строка», «Столбец» и «Данные» (но не область «Страница»);
– TableRange2 – диапазон, включающий все области, составляющие сводную таблицу;
– ColumnGrand – если это свойство установлено в True, отображается итог по столбцам;
– RowGrand – если это свойство установлено в True, отображается итог по строкам;
– HasAutoFormat – если это свойство установлено в True, Excel автоматически переформатирует сводную таблицу при ее изменении;
– Name – имя сводной таблицы;
– RefreshDate – дата и время последнего обновления данных сводной таблицы в кэше (только для чтения);
– RefreshName – имя пользователя, который произвел последнее обновление сводной таблицы (только для чтения);
– SaveData– если это свойство имеет значение True, содержимое внутреннего кэша сохраняется вместе со сводной таблицей;
– SourceData – источник данных для сводной таблицы (только для чтения);
– DisplayErrorString – если это свойство установлено в True, сводная таблица выводит значение свойства ErrorString вместо значений ошибок в ячейках, содержащих ошибки;
– ErrorString – пользовательская строка с текстом описания ошибки;
– DisplayNullString – если это свойство установлено в True, сводная таблица выводит значение свойства NullString в ячейках, содержащих пустые значения;
– NullString – строка, отображаемая в ячейках, содержащих пустые значения;
– ManualUpdate – если свойство имеет значение True, изображение сводной таблицы не обновляется при изменениях, что позволяет выполнить последовательность операторов VBA без обновления изображений на экране (можно отобразить только результат всех изменений);
– EnableDrillDown – разрешает или запрещает возможность выполнения отображения детальных данных;
– EnableFieldDialog – разрешает или запрещает пользователю доступ к диалоговому окну «Поле сводной таблицы»;
– EnableWizard – разрешает или запрещает пользователю доступ к Мастеру сводных таблиц.
Для выполнения некоторых операций над сводной таблицей необходимо выделение диапазонов ее ячеек, что можно выполнить с помощью перечисленных методов. Например, для удаления таблицы нужно выделить весь диапазон содержащих ее ячеек и вызвать метод Clear для полученного объекта Range. Кроме того, выделенные диапазоны можно отформатировать и т.д.
Последние свойства обеспечивают защиту таблицы.
Методы объекта PivotTable:
– AddFields – используется для добавления полей в области «Строка», «Столбец» и «Страница» сводной таблицы; для указания области используются аргументы RowFields, ColumnFields и PageFields соответственно, их значения могут быть строкой или массивами строк; аргумент AddToTable принимает значение True, если новые поля нужно добавить к уже существующим в таблице, и False, если указанные поля замещают существующие;
– ColumnFields – возвращает все поля сводной таблицы в области «Столбец» (в том числе метки столбцов);
– RowFields – возвращает все поля сводной таблицы в области «Строка» (в том числе метки строк);
– DataFields – возвращает все поля сводной таблицы в области «Данные»;
– PageFields – возвращает все поля сводной таблицы в области «Страница»;
– PivotFields – возвращает все поля сводной таблицы;
– VisibleFields– возвращает все отображаемые поля сводной таблицы;
– HiddenFields– возвращает все поля, которые не отображаются в сводной таблице;
– CalculatedFields– возвращает все вычисляемые поля сводной таблицы;
– RefreshTable – используется для обновления данных во внутреннем кэше таблицы;
– ShowPage – используется для создания отдельной сводной таблицы на новом рабочем листе для каждого элемента данных определенного поля из области «Страница»; имя поля указывается с помощью аргумента PageField.
Объект PivotField применяется для представления полей, содержащих данные, которыми заполняется сводная таблица. Используя свойства и методы этого объекта, можно управлять визуализацией данных в различных областях сводной таблицы.
Свойства полей сводной таблицы в областях «Строка», «Столбец» и «Страница»:
– CurrentPage – соответствует элементу, отображаемому в поле раскрывающегося списка в области «Страница», применяется только для полей в области «Страница»;
– Subtotals – предоставляет возможность вычисления промежуточных итогов в сводной таблице; промежуточные итоги могут быть установлены для полей в областях «Строка», «Столбец» и «Страница», но отображаться могут только в областях «Строка» и «Столбец»; это свойство представляет собой массив значений типа Boolean, каждый элемент которого представляет тип используемого промежуточного итога в следующем порядке: «Всего», «Сумма», «Количество значений», «Среднее», «Максимум», «Минимум», «Произведение», «Количество числовых значений»; «Смещенное отклонение», «Несмещенное отклонение», «Смещенная дисперсия», «Несмещенная дисперсия»; таким образом, установив нужные флажки в массиве, можно вычислить интересующие значения промежуточных итогов.
Вычисление промежуточных итогов возможно с помощью свойства Function метода Subtotals.
Вручную вычисление промежуточных итогов можно задать с помощью двойного щелчка на метке поля сводной таблицы в диалоговом окне «Вычисление поля сводной таблицы» при работе с мастером сводных таблиц.
Сгруппированные поля имеют некоторые дополнительные свойства. Поля могут группироваться только в областях «Строка», «Столбец» и «Страница».
Данные в поле сводной таблицы можно группировать автоматически или вручную, но оба способа используют метод Group. Метод Group является методом объекта Range. Поэтому при вызове этого метода для группировки полей нужно указать диапазон, используя свойство DataRange, применимое ко всем полям сводной таблицы. При автоматической группировке необходимые значения указываются в аргументах метода. Такая группировка может выполняться только с полями сводной таблицы, содержащими данные типа дата/время или числовые данные. Метод Group имеет следующие аргументы:
– Start– значение, с которого начинается группа (если установлено в True, то группа начинается с первого значения в поле);
– End – значение, которым заканчивается группа (значение True означает, что группа заканчивается на последнем значении в поле);
– By – единица измерения группы;
– Periods – состоящий из 7 значений логический массив, с помощью которого производится выбор из семи встроенных интервалов даты/времени («Секунды», «Минуты» и т.д.).
Свойства объекта PivotField для работы с группами в сводных таблицах (свойства сгруппированных полей):
– ChildField – возвращает поле, соответствующее прямому подмножеству группы (используется только при ручной группировке);
– ParentField – возвращает поле, которое содержит в себе указанное поле, т.е. родительское поле (используется только при ручной группировке);
– GroupLevel – номер уровня поля в группе (существует только у полей, сгруппированных вручную);
– TotalLevels – общее количество полей в группе.
– Свойства полей сводной таблицы в области «Данные»:
– NumberFormat–задает строку, которая используется для установки формата чисел поля (все числовые форматы можно увидеть на вкладке «Число» диалогового окна «Формат ячейки»; можно использовать пользовательский формат);
– Calculation – представляет способ визуализации данных (используется для разбиения значений поля в области «Данные»); возможны следующие варианты:
– xlDifferenceFrom – «отличие» – отображается модуль разности между значением в поле и заданным значением (BaseItem) в заданном поле (BaseField);
- xlIndex – «индекс» – вычисляется для каждого значения по формуле:
- ((значение в ячейке)*(общий итог))/((итог в строке)*(итог в столбце))
- xlNormal – данные отображаются в обычном виде, без дополнительных вычислений;
– xlPercentDifferenceFrom – «приведенное отличие» – подобна первому варианту, но отображается процент различия между значением BaseItem и значениями в поле BaseField;
- xlPercentOf – «доля» – отображается процентное соотношение между значением BaseItem и значениями в поле BaseField;
- xlPercentOfColumn – «доля от суммы по столбцу» – значения ячеек области данных отображается в процентах от итога по столбцу;
- xlPercentOfRow – «доля от суммы по строке» – значения ячеек области данных отображается в процентах от итога по строке;
- xlPercentOfTotal – «доля от общей суммы» – вычисляется процент каждого элемента по отношению к сумме всех значений, взятой за 100%;
- xlRunningTotal – «с нарастающим итогом» – значения ячеек области данных отображаются в виде нарастающего итога по строкам;
– BaseItem – элемент поля сводной таблицы, используемый в свойстве Calculation с некоторыми установками;
– BaseField – элемент поля сводной таблицы, используемый в свойстве Calculation с некоторыми установками;
– Function – через это свойство Excel предоставляет несколько функций, которые могут применяться к основным данным полей сводной таблицы области «Данные»; может принимать следующие значения:
- xlAverage – среднее арифметическое значение анализируемых данных;
- xlCount – количество записей;
- xlCountNum – количество числовых записей;
- xlMax – максимальное значение;
- xlMin – минимальное значение;
- xlProduct – произведение;
- xlStDev – несмещенное стандартное отклонение (по выборке данных);
– xlStDevP – смещенное стандартное отклонение (по всей генеральной совокупности);
- xlVarP – смещенная оценка дисперсии (по всей генеральной совокупности).
Применение какой-либо функции к полю приводит к изменению свойства Name. Например: применение функции вычисления среднего к полю “Доход” изменит имя на “Среднее по полю Доход”.
Свойства всех полей сводной таблицы:
– DataRange – диапазон рабочего листа, занятый изображением данных поля сводной таблицы;
– LabelRange – диапазон листа, занятый изображением метки имени поля сводной таблицы;
– Name – имя поля в том виде, как оно изображено в сводной таблице;
– SourceName – первоначальное имя поля (из источника данных);
– Orientation – используется для размещения поля в различных областях таблицы; может принимать значения xlColumnField, xlDataField, xlHidden, xlPageField, xlRowField;
– Position – номер позиции поля в занимаемой им области таблицы;
– Value – синоним свойства Name;
– DataType – тип данных поля сводной таблицы; может принимать значения xlText (текстовые данные), xlNumber (числовые данные), xlDate (данные типа дата/время).
Следующие свойства объекта PivotField используются для защиты:
– DragToColumn – если значение False, поле не может быть перемещено в область «Столбец»;
– DragToHide – если значение False, поле не может быть скрыто;
– DragToPage – если значение False, поле не может быть перемещено в область «Страница»;
– DragToRow – если значение False, поле не может быть перемещено в область «Строка».
Методы объекта PivotField применяются только к полям, расположенным в областях «Строка», «Столбец» и «Страница»:
– PivotItems – возвращает имена всех уникальных элементов в поле сводной таблицы;
– VisibleItems – возвращает элементы, которые видны в данный момент;
– HiddenItems – возвращает элементы, которые являются скрытыми в данный момент;
– ChildItems – элементы в поле сводной таблицы, для которых была применена группировка (только для областей «Строка», «Столбец» и «Страница», для полей, сгруппированных вручную);
– ParentItems – новая группа элементов, которая была сформирована с помощью группировки дочерних элементов (только для областей «Строка», «Столбец» и «Страница», для полей, сгруппированных вручную).
Объект PivotItem используется для представления уникальных элементов поля сводной таблицы.
Свойства объекта PivotItem:
– DataRange – объект Range, представляющий диапазон области «Данные», содержащей данный элемент (применяется только для полей в областях «Строка», «Столбец» и «Страница»);
– LabelRange – объект Range, содержащий метки данного элемента в сводной таблице (применяется только для полей в областях «Строка» и «Столбец»);
– Name – полное имя элемента поля сводной таблицы или текущее значение, связанное с элементом;
– ParetnItem – имя группы, к которой принадлежит дочерний элемент (применимо только к элементам, являющимся дочерними в группе; созданной вручную из полей в областях «Строка», «Столбец» и «Страница»);
– ShowDetail – если имеет значение True, группа отображается полностью (для родительских элементов, созданных при ручной группировке полей в областях «Строка», «Столбец» и «Страница»);
– ParentShowDetail – если имеет значение True, отображается дополнительная информация о предках элемента, т.е. отображаются все дочерние элементы в соответствующей группе (это свойство дочерних элементов, созданных при ручной группировке полей в областях «Строка», «Столбец» и «Страница»);
– Position – порядковые номер элемента в указанном поле сводной таблицы (применимо к элементам из областей «Строка», «Столбец», «Страница»);
– SourceName или Value – имя элемента в том виде, как оно указано в первоначальном источнике данных;
– Visible – управляет отображение элемента, позволяет его спрятать без удаления из таблицы (применимо к элементам в областях «Строка», «Столбец» и «Страница»; может принимать значения True или False).
МетодChildItems объекта PivotItem возвращает элемент или семейство элементов, которые являются дочерними элементами указанного элемента-предка или сгруппированного поля сводной таблицы.
Вычисляемые поля и элементы являются новыми средствами Excel 97. Это объекты PivotFields, определенные с помощью какой-либо формулы. Например:
Вычисляемые элементы представляются объектом CalculatedItem. Вычисляемые элементы имеют имя и формулу. Они вводятся с помощью вызова метода Add объекта CalculatedFields, а обрабатываются так же, как и другие объекты PivotItems. Для определения, является ли элемент вычисляемым, можно использовать свойство IsCalculated.
Внутренний кэш сводной таблицы содержит необработанные данные, загруженные из источника данных в оперативную память, а сводная таблица отображает данные кэша. Кэш в программе представляется объектом PivotCache. Он доступен как свойство объекта PivotTable. Этот объект имеет следующие основные свойства:
– BackgroundQuery – если это свойство установлено в True, Excel выполняет запрос к источнику данных в фоновом режиме, если False – пользователь должен ждать завершения выполнения запроса перед продолжением работы (в частности, при создании сводной таблицы до размещения полей нужно дождаться завершения запроса);
– RecordCount – количество записей, находящихся в данный момент в кэше (только чтение);
– RefreshDate – содержит дату и время последнего обновления кэша;
– RefreshName – имя пользователя, который произвел последнее обновление кэша;
– EnabledRefresh – разрешает или запрещает пользователю обновление сводной таблицы.
Далее приведены примеры кода создания и модификации сводной таблицы.
На рис. 23 приведена исходная таблица, на основании которой формируется сводная таблица.
