Если планируется преобразование сводной таблицы в значения, потребуется скопировать всю сводную таблицу. Но в этом случае довольно сложно заранее предсказать размер сводной таблицы. Если вы каждый день выводите отчет по данным транзакции, то можете иметь (или не иметь!) в наличии данные по объемам продаж, к примеру, для западного региона. Тогда сводная таблица может включать либо семь, либо шесть столбцов.

Для установки ссылок на сводную таблицу в Excel применяются два свойства диапазонов. Специальное свойство .TableRange2 включает все строки сводной таблицы, в том числе все раскрывающиеся списки полей страниц, которые отображаются в верхней части сводной таблицы. Область действия свойства .TableRange1 находится ниже области расположения полей фильтра. Это свойство зачастую включает такие необязательные строки, как Сумма по полю Доход (Sum of Revenue), которые отображаются в верхней части сводной таблицы.

Если нужно преобразовать сводную таблицу в значения, не перемещая при этом таблицу в новое местоположение, воспользуйтесь следующим кодом.

PT.TableRange2.Сору
PT.TableRange2.PasteSpecial xlPasteValues

Если в новое местоположение нужно скопировать лишь область данных сводной таблицы, для смещения на одну строку ниже места, выбираемого с помощью свойства .TableRange2, выбирается свойство .Offset.

PT.TableRange2.Offset(1,0).Copy

Эта ссылка копирует область данных и дополнительно еще одну строку для заголовков.

Обратите внимание на рис. 12.7, где использование свойства .Offset без свойства .Resize приводит к копированию дополнительной строки. Но поскольку эта строка всегда пуста, нет необходимости в применении свойства .Resize для копирования большего числа дополнительных строк.

В коде не только копируется свойство РТ.TableRange2, но и выполняется метод PasteSpecial ячейки, расположенной на шесть строк ниже сводной таблицы. На этом этапе рабочий лист должен выглядеть так, как показано на рис. 12.7. Таблица в ячейке N2 представляет реальную сводную таблицу, а таблица в ячейке N58 содержит всего лишь скопированные итоговые сведения. В дальнейшем можете полностью очистить сводную таблицу, применив ко всей таблице метод Clear. Перед дальнейшей настройкой новой таблицы вам следует удалить кеш старой сводной таблицы из памяти, назначив свойству PTCache значение Nothing.

В следующем коде (листинг 12.2) для получения итоговых значений на основе исходных данных применяется сводная таблица. Более 80000 строк исходных данных сжимаются до 50 строк итоговых значений. Результирующие данные корректно отформатированы благодаря применению дополнительной фильтрации, сортировки и т.д. В конце программного кода сводная таблица копируется в виде статических значений, а сама сводная таблица очищается.

'Листинг 12.2. Создание статических итогов на основе сводной таблицы

Sub UsePivotToCreateValues()

	Dim WSD As Worksheet
	Dim PTCache As PivotCache
	Dim PT As PivotTable
	Dim PRange As Range
	Dim FinalRow As Long
Set WSD = Worksheets("Data")

'Удаление определенных ранее сводных таблиц
	For Each PT In WSD.PivotTables
		PT.TableRange2.Clear
	Next PT
	WSD.Range("Nl:AZ1").EntireColumn.Clear

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

'Создание сводной таблицы на основе кеша Pivot Cache
	Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
		Cells(2, FinalCol + 2), TableName:="PivotTable1")

'Отключение обновления при создании таблицы
	PT.ManualUpdate = True

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

'Настройка полей данных
	With PT.PivotFields("Доход")
		.Orientation = xlDataField
		.Function = xlSum
		.Position = 1
		.NumberFormat =   "# ##0"
		.Name = "Доход"
	End With

	With PT.PivotFields("Стоимость оборудования")
		.Orientation = xlDataField
		.Function = xlSum
		.Position = 2
		.NumberFormat - "# ##0"
		.Name = "КПС"
	End With

'Настройки, применяемые для создания сплошного массива данных
	With PT
		.NullString = О
		.RepeatAllLabels Repeat:=xlRepeatLabels
		.ColumnGrand = False
		.RowGrand = 0
	.PivotFields("Регион").Subtotals(1)  = True
	.PivotFields("PerHOH").Subtotals(l)  = False
	End With

'Вычисление сводной таблицы
	PT.ManualUpdate = False
	PT.ManualUpdate = True

'Копирование сводной таблицы в виде значений ниже сводной таблицы
	PT.TableRange2.0ffset(1,  0).Сору
	PT.TableRange1.Celle(1, 1) .Offset( _
		PT.TableRangel.Rows.Count + 4,  0).PasteSpecial _
		xlPasteValuesAndNumberFormats
	StartRow = PT.TableRangel.Cells(1, 1) _
		.Offset(PT.TableRangel.Rows-Count + 5, 0).Row
        PT.TableRangel.Clear
		Set PTCache = Nothing
		
        WSD.Activate
		Cells(StartRow,   FinalCol + 2).Select
	End Sub

Этот код создает сводную таблицу. Затем он копирует результаты в виде значений и вставляет их ниже исходной сводной таблицы. Порой отчет сводной таблицы размещается на другом листе либо даже в другой книге. Соответствующие примеры приводятся далее.

До сих пор мы создавали только простые отчеты сводных таблиц. Но сводные таблицы обеспечивают намного большую гибкость. В следующих статьях мы рассмотрим несколько более сложных примеров отчетов.