- Използване на GetPivotData за получаване на стойност
- Създаване на обобщена таблица на лист
- Създаване на обобщена таблица на нов лист
- Добавяне на полета към обобщена таблица
- Промяна на оформлението на отчета на обобщената таблица
- Изтриване на обобщена таблица
- Форматирайте всички обобщени таблици в работна книга
- Премахване на полета на обобщена таблица
- Създаване на филтър
- Обновяване на вашата обобщена таблица
Този урок ще покаже как да работите с обобщени таблици, използвайки 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 ("Продажби", "Продукт", "ABC", "Регион", "Север") |
В този случай Sales е „DataField“, „Field1“ е продукт, „Item1“ е ABC, „Field2“ е Region и „Item2“ е North.
Можете също да включите повече от 2 полета.
Синтаксисът за GetPivotData е:
GetPivotData (DataField, Поле 1, Позиция1, Поле 2, Точка 2… ) където:
| Параметър | Описание |
|---|---|
| Поле от данни | Поле с данни като продажби, количество и т.н., което съдържа числа. |
| Поле 1 | Име на колона или поле в таблицата. |
| Точка 1 | Име на елемент в поле 1 (Незадължително). |
| Поле 2 | Име на колона или поле в таблицата (Незадължително). |
| Точка 2 | Име на елемент в поле 2 (Незадължително). |
Създаване на обобщена таблица на лист
За да създадем обобщена таблица въз основа на горния диапазон от данни, в клетка J2 на лист 1 на активната работна книга, бихме използвали следния код:
| 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 ("Сума от продажби").NumberFormat = "$#, ## 0.00"Край с |
Резултатът е:
Промяна на оформлението на отчета на обобщената таблица
Можете да промените оформлението на отчета на своята обобщена таблица. Следният код ще промени оформлението на отчета на вашата обобщена таблица в таблична форма:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18" |
Изтриване на обобщена таблица
Можете да изтриете обобщена таблица с помощта на VBA. Следният код ще изтрие обобщената таблица, наречена PivotTable1 в активния лист:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents |
Форматирайте всички обобщени таблици в работна книга
Можете да форматирате всички обобщени таблици в работна книга с помощта на VBA. Следният код използва циклична структура, за да премине през всички листове на работна книга и да изтрие всички обобщени таблици в работната книга:
| 12345678910111213 | Подформатиране AllThePivotTablesInAWorkbook ()Dim wks As WorksheetDim wb Като работна книгаЗадайте wb = ActiveWorkbookDim pt като обобщена таблицаЗа всяка седмица в wb.SheetsЗа всеки pt В wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Следваща точкаСледващите седмициEnd Sub |
За да научите повече за това как да използвате Loops във VBA, щракнете тук.
Премахване на полета на обобщена таблица
Можете да премахнете полета в обобщена таблица с помощта на VBA. Следният код ще премахне полето Продукт в секцията Редове от обобщена таблица, наречена PivotTable1 в активния лист:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientation = _xlСкрит |
Създаване на филтър
Създадена е обобщена таблица, наречена PivotTable1 с Продукт в раздела Редове и Продажби в Раздел Стойности. Можете също така да създадете филтър за вашата обобщена таблица, използвайки 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 ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = ВярноС ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems ("Юг"). Visible = False.PivotItems ("West"). Visible = FalseКрай с |
Обновяване на вашата обобщена таблица
Можете да опресните сводната таблица във VBA. Бихте използвали следния код, за да опресните конкретна таблица, наречена PivotTable1 във VBA:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh |
