Программное формирование структуры сводной таблицы
Теперь, когда объекты PivotCache и PivotTable уже созданы, можно приступить к завершающему этапу - формированию структуры отчета сводной таблицы. Это означает, что нужно поля таблицы, содержащиеся в коллекции PivotFields распределить по измерениям. И здесь существует несколько способов выполнения этой работы. В предыдущих версиях я применял метод AddFields, который, правда, имел ряд ограничений. Теперь всю эту работу удобнее выполнять, работая непосредственно с объектами PivotField. Полного описания этих объектов давать не буду, но поясню, какими свойствами я пользовался, на примере формирования отчета сводной таблицы. Процедура, которую я сейчас приведу, полностью решает вопрос программного создания сводной таблицы, начиная от этапа связывания с источником данных, кончая этапом формирования структуры таблицы и группирования ее данных. Вот ее текст:
Public Sub MyCreatePT() 'Создание отчета сводной таблицы 'Создание кэша и отчета сводной таблицы - объектов PivotCache, PivotTable 'CreatePivotCacheAndTable 'Другой вариант создания кэша - через ADO CreatePivotCacheAndTableWithADO 'Формирование отчета сводной таблицы With ThisWorkbook.Worksheets("Лист1").PivotTables("Анализ продаж") With .PivotFields("ДатаЗаказа") .Orientation = xlRowField .Position = 1 End With With .PivotFields("Сотрудник") .Orientation = xlRowField .Position = 2 End With With .PivotFields("НазваниеКниги") .Orientation = xlColumnField .Position = 1 End With With .PivotFields("Заказчик") .Orientation = xlPageField .Position = 1 End With With .PivotFields("Стоимость") .Orientation = xlDataField .Position = 1 End With With .PivotFields("Количество") .Orientation = xlDataField .Position = 2 End With End With Range("A5").Select Selection.Group Start:=True, End:=True, By:=7, Periods:=Array(False, _ False, False, True, False, False, False) End Sub
Я приведу несколько комментариев:
- На первом этапе работы создаются объекты PivotCache и PivotTable, для чего вызываются уже рассмотренные нами процедуры. Реально вызывается одна из этих процедур, вызов другой закомментирован. Какой вариант предпочесть - дело вкуса. О достоинствах этих вариантов я говорил.
- После создания указанных объектов формируется структура отчета сводной таблицы. Для каждого из полей сводной таблицы задается соответствующее измерение и порядок расположения. Для этого используются свойства объектов PivotField - Orientation и Position. Первое из них задает измерение, второе - порядок в измерении. Добраться до нужного поля позволяет коллекция PivotFields, где в качестве индекса используется имя поля.
- На заключительном шаге производится группирование данных по полю "Дата заказа". В данном случае я группирую данные по неделям. Скажу несколько слов о методе группирования данных - Group, производящем эту операцию. Он является методом класса Range и, следовательно, может вызываться объектом Selection. Здесь применяется его форма, специально созданная для группирования данных сводной таблицы. При группировании дат булев массив Periods указывает одну из 7 возможных единиц группирования (секунду, минуту, час, день, месяц, квартал, год), а параметр BY задает количество единиц в группе.
Единственное, что осталось сделать для завершения рассказа о программном создании отчета сводной таблицы, - это посмотреть на результаты работы нашей процедуры:
увеличить изображение
Рис. 8.25. Программно построенная сводная таблица
