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

Мы не предлагаем рядовым пользователям использовать VBA для создания сводных таблиц. Цель этой статьи заключается в том, чтобы напомнить вам о том, что сводные таблицы можно (и нужно) использовать как средство получения окончательных результатов. Можете применить сводную таблицу для подведения итогов по набору данных, а затем использовать эти итоги в других расчетах.

Начиная с версии Excel 2007 пользовательский интерфейс управления сводными таблицами претерпел значительные изменения, но VBA-код, который используется для управления им, остается прежним. Разработчики сделали правильный выбор, иначе миллионы приложений, разработанных на VBA, попросту перестали бы выполняться в Excel 2007. Несмотря на изменение названий областей сводной таблицы в Excel, в VBA они продолжают носить старые имена: «область страницы» (Page Fields), «область столбцов» (Column Fields), «область строк» (Row Fields) и «область данных» (Data Fields).

В Excel 2000 и более поздних версиях, прежде чем создавать сводную таблицу, нужно сформировать объект кеша сводной таблицы, применяемый для описания области ввода данных.

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim Prange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD - Worksheets("PivotTable")

	'Удаление всех предыдущих сводных таблиц
For Each PT In WSD.PivotTables
	PT.TableRange2.Clear
Next PT

	'Описание области ввода данных и определение хеша
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Celled, Application.Columns.Count) . _
	End (xlToLeft).Column
Set PRange = WSD.Cells(1,  1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add _
	(SourceType:-xlDatabase, SourceData:-PRange

После определения кеша сводной таблицы используйте метод СrеatePivotTable для создания пустой сводной таблицы на основе выделенного ранее кеша.

	Set PT = PTCache.CreatePivotTable(TableDestination:= _
		WSD.Cells(2,  FinalCol + 2), TableName:="PivotTablel")

В методе CreatePivotTable вы указываете расположение выходных данных и (не обязательно) определяете имя таблицы с несколько странным видом. После выполнения этой строки кода вы получите пустую сводную таблицу. Если в области списка полей сводной таблицы был установлен флажок Отложить обновление макета (Defer Layout Update), то Excel не станет вновь вычислять сводную таблицу после перетаскивания в таблицу каждого поля. По умолчанию программа вычисляет сводную таблицу после выполнения каждого этапа построения макета таблицы. Таким образом, сводная таблица будет вычисляться много раз, пока не будет получен итоговый результат.

Чтобы ускорить выполнение кода, временно отключите вычисление сводной таблицы с помошью свойства ManualUpdate.

PT.ManualUpdate = True

Теперь можно выполнять все операции по компоновке сводной таблицы.

В методе .AddFields указывается одно или несколько полей, которые должны находиться в области строк, столбцов или страниц сводной таблицы. Параметр RowFields позволяет определять поля, которые добавлены в область заголовков строк списка полей сводной таблицы. Параметр ColumnFields соответствует области заголовков столбцов, а параметр PageFields — области фильтров отчета.

Следующий программный код представляет сводную таблицу с двумя полями в области заголовков строк и одним полем в области заголовков столбцов.

	' Установка полей строк и столбцов
PT.AddFields RowFieldss=Array("Категория оборудования", _
	"Название оборудования"), ColumnFields:="Регион"

Если в одну из областей сводной таблицы было добавлено единственное поле, например поле Регион в области столбцов сводной таблицы, имя поля заключается в кавычки. Если добавляются два либо больше полей, список полей включают в состав функции массива. Несмотря на то что поля строк, столбцов и страниц в сводной таблице могут обрабатываться с помощью метода .AddFields, для добавления полей в область данных лучше воспользоваться кодом, который будет описан в следующей статье.

Top