Свойства и методы объекта PivotTable
Объект PivotTable, задающий отчет сводной таблицы - его внешнее представление устроен, естественно, более сложно. У него значительно больше свойств, чем у объекта PivotCache, - их 54, да и методов в четыре раза больше. Замечу, что для программного создания сводной таблицы достаточно использовать лишь малую часть из этого набора. Большая часть этих свойств и методов необходима, если Вы хотите программно поддерживать работу пользователя со сводной таблицей.
Давайте рассмотрим основные свойства этого объекта:
- ColumnFields([Index]) As Object, DataFields([Index]) As Object, PageFields([Index]) As Object, RowFields([Index]) As Object. Все эти свойства имеют статус "только для чтения" и возвращают коллекцию или отдельный элемент коллекции, если указан индекс. Возвращаемые объекты задают поля сводной таблицы по соответствующему измерению - поля столбцов, данных, страниц или строк. Вне зависимости от измерения все возвращаемые объекты принадлежат единому классу PivotField или PivotFields для коллекций.
- ColumnRange, DataLabelRange, DataBodyRange, PageRange, RowRange - возвращают объект Range, задающий соответствующую область. Вот простенькая процедура, поочередно выделяющая указанные области сводной таблицы:
Public Sub SelectRange() ThisWorkbook.Worksheets("Лист1").Activate Range("A3").Select ActiveCell.PivotTable.ColumnRange.Select ActiveCell.PivotTable.DataLabelRange.Select ActiveCell.PivotTable.DataBodyRange.Select ActiveCell.PivotTable.PageRange.Select ActiveCell.PivotTable.RowRange.Select End Sub
- ColumnGrand, RowGrand - булевы свойства, имеющие значение True, если сводная таблица подводит итоги по столбцам и строкам.
- CubeFields - для сводных таблиц, основанных на OLAP кубе, возвращает одноименную коллекцию, задающую поля куба. Каждый объект этой коллекции содержит свойства поля.
- HiddenFields([Index]) As Object, VisibleFields([Index]) As Object - коллекции спрятанных и видимых полей. Для сводных таблиц, основанных на OLAP кубах спрятанных полей нет - все поля являются видимыми.
- ErrorString As String, DisplayErrorString As Boolean. Первое из свойств позволяет задать строку, представляющую сообщение об ошибке, второе - позволяет включить или отключить появление этой строки в вычисляемых полях, где возникает ошибка.
- PivotFormulas As PivotFormulas - возвращает одноименную коллекцию объектов. Каждый элемент этой коллекции является объектом класса PivotFormula и представляет формулу, используемую в вычисляемых полях.
На этом я закончу рассмотрение свойств и перейду к рассмотрению методов:
- Function AddFields([RowFields], [ColumnFields], [PageFields], [AddToTable]). Позволяет добавить поля к соответствующему измерению. Последний булев параметр позволяет указать, будут ли поля добавляться или заменять существующий набор полей. В предыдущей версии
- Function CalculatedFields() As CalculatedFields. Возвращает одноименную коллекцию вычисляемых полей.
- Sub Format(Format As xlPivotFormatType). Производит форматирование сводной таблицы. Аргумент Format задает один из возможных типов форматирования.
- Function GetData(Name As String) As Double. Позволяет получить данные из отдельной ячейки сводной таблицы. Аргумент Name задает поля таблицы, однозначно определяющие ячейку. Он имеет достаточно сложный синтаксис, на деталях которого останавливаться не буду.
- Function PivotCache() As PivotCache - возвращает объект PivotCache, связанный с отчетом.
- Function PivotFields([Index]) As Object - возвращает одноименную коллекцию, а при указании индекса элемент этой коллекции, задающий поле сводной таблицы. В качестве индекса можно использовать имя поля. Возвращаемые объекты принадлежат классу PivotField. Позже в примере я продемонстрирую работу с этими объектами при программном формировании структуры сводной таблицы.
- Sub PivotTableWizard([SourceType], [SourceData], [TableDestination], [TableName], [RowGrand], [ColumnGrand], [SaveData], [HasAutoFormat], [AutoPage], [Reserved], [BackgroundQuery], [OptimizeCache], [PageFieldOrder], [PageFieldWrapCount], [ReadData], [Connection]). Этим методом, но не в виде процедуры, а в виде функции обладает и объект Worksheet. Вызванный этим объектом метод позволяет создать объект PivotTable. В предыдущих версиях Office этот способ был основным для создания подобных объектов. Теперь надобность в нем практически отпала. Метод моделирует работу Мастера сводных таблиц и имеет многочисленные аргументы, позволяющие определить сводную таблицу. Поскольку, как я сказал, теперь не следует пользоваться этим методом, то я не буду останавливаться на деталях его описания.
- Function RefreshTable() As Boolean - обновляет данные сводной таблицы и возвращает значение True, если обновление прошло удачно.
- Function ShowPages([PageField]) - создает новый отчет для каждого элемента в поле страниц. Каждый отчет создается на отдельной странице.