Руководство VBA по сводным таблицам

В этом руководстве будет показано, как работать со сводными таблицами с помощью VBA.

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

Использование GetPivotData для получения значения

Предположим, у вас есть сводная таблица под названием PivotTable1 с продажами в поле значений / данных, продуктом в поле строк и регионом в поле столбцов. Вы можете использовать метод PivotTable.GetPivotData для возврата значений из сводных таблиц.

Следующий код вернет из сводной таблицы 1 130,00 долларов США (общий объем продаж для Восточного региона):

1	MsgBox ActiveCell.PivotTable.GetPivotData («Продажи», «Регион», «Восток»)

В этом случае Sales - это «DataField», «Field1» - это регион, а «Item1» - это восток.

Следующий код вернет 980 долларов США (общий объем продаж продукта ABC в Северном регионе) из сводной таблицы:

1	MsgBox ActiveCell.PivotTable.GetPivotData («Продажи», «Продукт», «Азбука», «Регион», «Север»)

В этом случае Sales - это «DataField», «Field1» - это Product, «Item1» - это ABC, «Field2» - это регион, а «Item2» - это север.

Вы также можете включить более двух полей.

Синтаксис GetPivotData:

GetPivotData (Поле данных, Поле1, Item1, Поле2, Item2… ) куда:

Параметр	Описание
Поле данных	Поле данных, такое как продажи, количество и т. Д., Которое содержит числа.
Поле 1	Имя поля столбца или строки в таблице.
Пункт 1	Имя элемента в поле 1 (необязательно).
Поле 2	Имя поля столбца или строки в таблице (необязательно).
Пункт 2	Название элемента в Поле 2 (необязательно).

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

Чтобы создать сводную таблицу на основе диапазона данных выше, в ячейке J2 на листе Sheet1 активной книги мы должны использовать следующий код:

1234567891011

Листы ("Лист1"). Ячейки (1, 1). ВыбратьActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _«Sheet1! R1C1: R21C4», версия: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Таблицы ("Лист1"). Выберите

Результат:

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

Чтобы создать сводную таблицу на основе указанного выше диапазона данных на новом листе активной книги, мы должны использовать следующий код:

12345678910111213

Листы ("Лист1"). Ячейки (1, 1). ВыбратьТаблицы.ДобавитьActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _«Sheet1! R1C1: R21C4», версия: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Таблицы ("Лист2"). Выберите

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

Вы можете добавить поля во вновь созданную сводную таблицу под названием PivotTable1 на основе диапазона данных выше. Примечание: лист, содержащий вашу сводную таблицу, должен быть активным листом.

Чтобы добавить продукт в поле строк, вы должны использовать следующий код:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientation = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Position = 1

Чтобы добавить регион в поле столбцов, вы должны использовать следующий код:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1

Чтобы добавить продажи в раздел значений в формате числа валюты, вы должны использовать следующий код:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_«Сводная таблица1»). Сводные поля («Продажи»), «Сумма продаж», xlSumС ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Sum of Sales").NumberFormat = "$ #, ## 0.00"Конец с

Результат:

Изменение макета отчета сводной таблицы

Вы можете изменить макет отчета своей сводной таблицы. Следующий код изменит макет отчета сводной таблицы на табличную форму:

1	ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18"

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

Вы можете удалить сводную таблицу с помощью VBA. Следующий код удалит сводную таблицу под названием PivotTable1 на активном листе:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents

Отформатируйте все сводные таблицы в книге

Вы можете отформатировать все сводные таблицы в книге с помощью VBA. В следующем коде используется структура цикла, чтобы перебрать все листы книги и удалить все сводные таблицы в книге:

12345678910111213

Дополнительное форматированиеDim wks как рабочий листDim wb As WorkbookУстановить wb = ActiveWorkbookDim pt As PivotTableЗа каждую неделю в wb.SheetsДля каждой точки в нед.pt.TableStyle2 = "PivotStyleLight15"Следующая птСледующие неделиКонец подписки

Чтобы узнать больше о том, как использовать циклы в VBA, нажмите здесь.

Удаление полей сводной таблицы

Вы можете удалить поля в сводной таблице с помощью VBA. Следующий код удалит поле Product в разделе Rows из сводной таблицы с именем PivotTable1 на активном листе:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientation = _xlHidden

Создание фильтра

Сводная таблица под названием PivotTable1 была создана с Product в разделе Rows и Sales в разделе Values. Вы также можете создать фильтр для своей сводной таблицы с помощью VBA. Следующий код создаст фильтр на основе региона в разделе фильтров:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1

Чтобы отфильтровать сводную таблицу на основе одного элемента отчета, в данном случае Восточного региона, вы должны использовать следующий код:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). CurrentPage = _"Восток"

Допустим, вы хотите отфильтровать сводную таблицу по нескольким регионам, в данном случае Восток и Север, вы должны использовать следующий код:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1ActiveSheet.PivotTables («Сводная таблица1»). Сводные поля («Регион»). _EnableMultiplePageItems = TrueС ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems ("Юг"). Visible = False.PivotItems ("Запад"). Visible = FalseКонец с

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

Вы можете обновить сводную таблицу в VBA. Вы должны использовать следующий код, чтобы обновить определенную таблицу под названием PivotTable1 в VBA:

1	ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh