- Використання GetPivotData для отримання значення
- Створення зведеної таблиці на аркуші
- Створення зведеної таблиці на новому аркуші
- Додавання полів до зведеної таблиці
- Зміна макета звіту зведеної таблиці
- Видалення зведеної таблиці
- Відформатуйте всі зведені таблиці у книзі
- Видалення полів зведеної таблиці
- Створення фільтра
- Оновлення зведеної таблиці
Цей підручник покаже, як працювати з зведеними таблицями за допомогою VBA.
Зведені таблиці - це інструменти узагальнення даних, які можна використовувати для отримання ключових уявлень та резюме з ваших даних. Давайте розглянемо приклад: у комірках A1: D21 є набір вихідних даних, що містить деталі проданих продуктів, показані нижче:
Використання GetPivotData для отримання значення
Припустимо, у вас є зведена таблиця під назвою Зведена таблиця1 із продажами у полі «Значення/дані», «Продукт» у полі «Рядки» та «Регіон» у полі «Стовпці». Щоб повернути значення зі зведених таблиць, можна використовувати метод PivotTable.GetPivotData.
Наведений нижче код поверне 1130,00 доларів США (загальний обсяг продажів для Східного регіону) зі зведеної таблиці:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Продажі", "Регіон", "Схід") |
У цьому випадку Продаж - це «Поле даних», «Поле1» - це Регіон, а «Елемент1» - Схід.
Наступний код поверне $ 980 (загальний обсяг продажів продукту ABC у Північному регіоні) зі зведеної таблиці:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Продажі", "Продукт", "ABC", "Регіон", "Північ") |
У цьому випадку Продаж - це «Поле даних», «Поле1» - це продукт, «Елемент1» - ABC, «Поле2» - Регіон, а «Елемент2» - північ.
Ви також можете включити більше 2 полів.
Синтаксис GetPivotData такий:
GetPivotData (DataField, Поле1, Пункт 1, Поле 2, Пункт 2… ) де:
| Параметр | Опис |
|---|---|
| Поле даних | Поле даних, таке як продажі, кількість тощо, яке містить цифри. |
| Поле 1 | Назва стовпця або поля рядка в таблиці. |
| Пункт 1 | Назва елемента в полі 1 (необов’язково). |
| Поле 2 | Назва стовпця або поля рядка в таблиці (необов’язково). |
| Пункт 2 | Назва елемента у полі 2 (необов’язково). |
Створення зведеної таблиці на аркуші
Для того, щоб створити зведену таблицю на основі діапазону даних вище, у клітинці J2 на аркуші 1 активної книги, ми б використали такий код:
| 1234567891011 | Робочі аркуші ("Аркуш1"). Клітинки (1, 1) .ВиберітьActiveWorkbook.PivotCaches.Create (Тип джерела: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Version: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "Зведена таблиця1", DefaultVersion _: = xlPivotTableVersion15Аркуші ("Аркуш1"). Виберіть |
Результат такий:
Створення зведеної таблиці на новому аркуші
Щоб створити зведену таблицю на основі діапазону даних вище, на новому аркуші активної книги, ми б використали такий код:
| 12345678910111213 | Робочі аркуші ("Аркуш1"). Клітинки (1, 1) .ВиберітьАркуші. ДодActiveWorkbook.PivotCaches.Create (Тип джерела: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Version: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "Зведена таблиця1", DefaultVersion _: = xlPivotTableVersion15Аркуші ("Аркуш2"). Виберіть |
Додавання полів до зведеної таблиці
Ви можете додавати поля до щойно створеної зведеної таблиці під назвою Зведена таблиця1, виходячи з наведеного вище діапазону даних. Примітка. Аркуш, що містить зведену таблицю, має бути активним аркушем.
Щоб додати продукт до поля рядків, потрібно використати такий код:
| 123 | ActiveSheet.PivotTables ("Зведена таблиця1"). Зворотні поля ("Продукт"). Орієнтація = xlRowFieldActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Продукт"). Положення = 1 |
Щоб додати регіон до поля стовпців, потрібно використати такий код:
| 123 | ActiveSheet.PivotTables ("Зведена таблиця1"). Зворотні поля ("Регіон"). Орієнтація = xlColumnFieldActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Регіон"). Положення = 1 |
Щоб додати Продажі до Розділу цінностей у форматі номера валюти, слід використати такий код:
| 123456789 | ActiveSheet.PivotTables ("Зведена таблиця1"). AddDataField ActiveSheet.PivotTables (_"Зведена таблиця1"). Зведені поля ("Продажі"), "Сума продажів", xlSumЗ ActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Сума продажів").NumberFormat = "$#, ## 0.00"Закінчити з |
Результат такий:
Зміна макета звіту зведеної таблиці
Ви можете змінити макет звіту зведеної таблиці. Наступний код змінить макет звіту зведеної таблиці на табличну форму:
| 1 | ActiveSheet.PivotTables ("Зведена таблиця1"). TableStyle2 = "Зведений стильLight18" |
Видалення зведеної таблиці
Ви можете видалити зведену таблицю за допомогою VBA. Наступний код видалить зведену таблицю під назвою Зведена таблиця1 в активному аркуші:
| 12 | ActiveSheet.PivotTables ("Зведена таблиця1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents |
Відформатуйте всі зведені таблиці у книзі
Ви можете відформатувати всі зведені таблиці у книзі за допомогою VBA. У наведеному нижче коді використовується структура циклу для перегляду всіх аркушів книги та видалення всіх зведених таблиць у книзі:
| 12345678910111213 | ПідформатуванняAllThePivotTablesInAWorkbook ()Затемнити робочі дні як робочий аркушDim wb Як робочий зошитВстановити wb = ActiveWorkbookDim pt як зведена таблицяДля кожного тижня в таблицях веб -сторінокДля кожного пункту В тижpt.TableStyle2 = "PivotStyleLight15"Наступний пунктНаступні тижніEnd Sub |
Щоб дізнатися більше про те, як використовувати цикли у VBA, натисніть тут.
Видалення полів зведеної таблиці
Ви можете видалити поля зведеної таблиці за допомогою VBA. Наступний код видалить поле Product у розділі Rows зі зведеної таблиці з назвою PivotTable1 в активному аркуші:
| 12 | ActiveSheet.PivotTables ("Зведена таблиця1"). Зворотні поля ("Продукт"). Орієнтація = _xlHidden |
Створення фільтра
Зведена таблиця під назвою Зведена таблиця1 була створена з розділом «Продукт» у розділі «Рядки» та «Продажі» у розділі «Значення». Ви також можете створити фільтр для зведеної таблиці за допомогою VBA. Наступний код створить фільтр на основі регіону в розділі Фільтри:
| 123 | ActiveSheet.PivotTables ("Зведена таблиця1"). Зворотні поля ("Регіон"). Орієнтація = xlPageFieldActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Регіон"). Положення = 1 |
Щоб відфільтрувати зведену таблицю на основі єдиного елемента звіту, в даному випадку в східному регіоні, слід використати такий код:
| 12345 | ActiveSheet.PivotTables ("Зведена таблиця1"). Зворотні поля ("Регіон"). ClearAllFiltersActiveSheet.PivotTables ("зведена таблиця1"). Зведені поля ("регіон"). CurrentPage = _"Схід" |
Скажімо, ви хотіли відфільтрувати зведену таблицю на основі кількох регіонів, у цьому випадку на Сході та Півночі, ви б використали такий код:
| 1234567891011121314 | ActiveSheet.PivotTables ("Зведена таблиця1"). Зворотні поля ("Регіон"). Орієнтація = xlPageFieldActiveSheet.PivotTables ("Зведена таблиця1"). Зведені поля ("Регіон"). Положення = 1ActiveSheet.PivotTables ("зведена таблиця1"). Зведені поля ("регіон"). _EnableMultiplePageItems = ІстинаЗ ActiveSheet.PivotTables ("зведена таблиця1"). Зведені поля ("регіон").PivotItems ("Південь"). Visible = False.PivotItems ("Захід"). Видимий = НеправдивийЗакінчити з |
Оновлення зведеної таблиці
Ви можете оновити зведену таблицю у VBA. Щоб оновити певну таблицю під назвою Зведена таблиця1 у VBA, ви б використали такий код:
| 1 | ActiveSheet.PivotTables ("Зведена таблиця1"). PivotCache.Refresh |
