excel-vba
Сводные таблицы

замечания

Создание сводной таблицы

Одной из самых мощных возможностей в Excel является использование сводных таблиц для сортировки и анализа данных. Использование VBA для создания и управления Pivots проще, если вы понимаете взаимосвязь Pivot Tables с Pivot Caches и как ссылаться и использовать разные части таблиц.

В самом основном, ваши исходные данные представляют собой область Range данных на Worksheet . Эта область данных ДОЛЖНА идентифицировать столбцы данных с строкой заголовка в качестве первой строки в диапазоне. После создания сводной таблицы пользователь может просматривать и изменять исходные данные в любое время. Однако изменения не могут быть автоматически или немедленно отражены в самой сводной таблице, поскольку существует промежуточная структура хранения данных, называемая сводным кэшем, которая напрямую связана с самой сводной таблицей.

Если требуется несколько сводных таблиц, основанных на одних и тех же исходных данных, сводный кэш можно повторно использовать в качестве внутреннего хранилища данных для каждого из сводных таблиц. Это хорошая практика, поскольку она экономит память и уменьшает размер файла Excel для хранения.

В качестве примера, чтобы создать сводную таблицу на основе исходных данных, показанных на приведенных выше рисунках:

Sub test()
    Dim pt As PivotTable
    Set pt = CreatePivotTable(ThisWorkbook.Sheets("Sheet1").Range("A1:E15"))
End Sub

Function CreatePivotTable(ByRef srcData As Range) As PivotTable
    '--- creates a Pivot Table from the given source data and
    '    assumes that the first row contains valid header data
    '    for the columns
    Dim thisPivot As PivotTable
    Dim dataSheet As Worksheet
    Dim ptSheet As Worksheet
    Dim ptCache As PivotCache
    
    '--- the Pivot Cache must be created first...
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:=srcData)
    '--- ... then use the Pivot Cache to create the Table
    Set ptSheet = ThisWorkbook.Sheets.Add
    Set thisPivot = ptCache.CreatePivotTable(TableDestination:=ptSheet.Range("A3"))
    Set CreatePivotTable = thisPivot
End Function

Ссылки Объект Pivot Table MSDN

Сводные диапазоны таблиц

Эти превосходные справочные источники предоставляют описания и иллюстрации различных диапазонов в сводных таблицах.

Рекомендации

• Ссылка на диапазоны Pivot Table в VBA - из Технического блога Джона Пельтье
• Ссылка на таблицу сводных таблиц Excel с использованием VBA - из globaliconnect Excel VBA

Добавление полей в сводную таблицу

Две важные вещи, которые следует учитывать при добавлении полей в сводную таблицу, - это ориентация и позиция. Иногда разработчик может предположить, где находится поле, поэтому всегда яснее явно определять эти параметры. Эти действия влияют только на данную сводную таблицу, а не на сводный кеш.

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    Set ptField = .PivotFields("Gender")
    ptField.Orientation = xlRowField
    ptField.Position = 1
    Set ptField = .PivotFields("LastName")
    ptField.Orientation = xlRowField
    ptField.Position = 2
    Set ptField = .PivotFields("ShirtSize")
    ptField.Orientation = xlColumnField
    ptField.Position = 1
    Set ptField = .AddDataField(.PivotFields("Cost"), "Sum of Cost", xlSum)
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

Форматирование данных сводной таблицы

В этом примере изменяется / DataBodyRange несколько форматов в области диапазона данных ( DataBodyRange ) данной сводной таблицы. Доступны все форматируемые параметры в стандартном Range . Форматирование данных влияет только на сводную таблицу, а не на сводный кеш.

ПРИМЕЧАНИЕ: свойство называется TableStyle2 потому что свойство TableStyle не является членом свойств объекта PivotTable .

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    .DataBodyRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"
    .DataBodyRange.HorizontalAlignment = xlRight
    .ColumnRange.HorizontalAlignment = xlCenter
    .TableStyle2 = "PivotStyleMedium9"
End With