VBA útmutató a kimutatástáblákhoz

Ez az oktatóanyag bemutatja, hogyan kell dolgozni a kimutatástáblákkal a VBA használatával.

A kimutatástáblák olyan adatösszesítő eszközök, amelyekkel kulcsfontosságú betekintést és összefoglalókat készíthet az adatokból. Nézzünk egy példát: az A1: D21 cellákban van egy forrásadatkészletünk, amely tartalmazza az értékesített termékek adatait, az alábbiakban látható:

A GetPivotData használata érték megszerzéséhez

Tegyük fel, hogy rendelkezik egy PivotTable1 nevű kimutatással, amelynek Értékei/adatmezője Értékesítés, a Sorok mezőként a Termék, az Oszlopok mező pedig. A PivotTable.GetPivotData metódussal adhat vissza értékeket a kimutatástáblákból.

A következő kód 1 130,00 USD -t (a keleti régió teljes értékesítése) ad vissza a kimutatásból:

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Értékesítés", "Régió", "Kelet")

Ebben az esetben az Értékesítés az „Adatmező”, a „Mező1” a Régió és a „Tétel1” a Kelet.

A következő kód 980 dollárt ad vissza (az ABC termék teljes értékesítése az északi régióban) a kimutatástáblából:

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Értékesítés", "Termék", "ABC", "Régió", "Észak")

Ebben az esetben az Értékesítés az „Adatmező”, a „Mező1” a Termék, a „1. tétel” az ABC, a „Mező2” a Régió és a „2. cikk” az Észak.

Több mint 2 mezőt is megadhat.

A GetPivotData szintaxisa a következő:

GetPivotData (Adatmező, 1. mező, 1. tétel, 2. mező, 2. tétel… ) ahol:

Paraméter	Leírás
Adatmező	Adatmező, például értékesítés, mennyiség stb., Amely számokat tartalmaz.
1. mező	Oszlop vagy sor mező neve a táblázatban.
1. tétel	Egy elem neve az 1. mezőben (nem kötelező).
2. mező	A táblázat oszlop- vagy sormezőjének neve (nem kötelező).
2. tétel	Egy elem neve a 2. mezőben (nem kötelező).

Pivot táblázat létrehozása egy lapon

Pivot tábla létrehozásához a fenti adattartomány alapján, az Active munkafüzet 1. lapjának J2 cellájában a következő kódot használnánk:

1234567891011

Munkalapok ("Sheet1"). Cellák (1, 1). Válassza kiActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Verzió: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Táblázatok ("Sheet1"). Válassza ki

Az eredmény:

Pivot táblázat létrehozása új lapon

Pivot tábla létrehozásához a fenti adattartomány alapján, az aktív munkafüzet új lapján a következő kódot használnánk:

12345678910111213

Munkalapok ("Sheet1"). Cellák (1, 1). Válassza kiLapok. HozzáadásActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Verzió: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Táblázatok ("Sheet2"). Válassza a lehetőséget

Mezők hozzáadása a pivot táblához

A fenti adattartomány alapján mezőket adhat hozzá az újonnan létrehozott PivotTable1 nevű kimutatáshoz. Megjegyzés: A Pivot táblázatot tartalmazó lapnak aktív lapnak kell lennie.

Ha a terméket a Sorok mezőhöz kívánja hozzáadni, használja a következő kódot:

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

A Régió hozzáadásához az Oszlopok mezőhöz a következő kódot kell használnia:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Pozíció = 1

Az Értékesítés hozzáadásához az Értékek szakaszhoz a pénznemszám formátummal a következő kódot kell használnia:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"PivotTable1"). PivotFields ("Sales"), "Sales of Sum", xlSumActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Értékesítési összeg").NumberFormat = "$#, ## 0.00"Vége ezzel

Az eredmény:

A kimutatástábla jelentéselrendezésének módosítása

Módosíthatja a kimutatástábla jelentéselrendezését. A következő kód a Pivot tábla jelentéselrendezését táblázatos formára változtatja:

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

Pivot tábla törlése

Pivot táblát törölhet a VBA használatával. A következő kód törli a PivotTable1 nevű kimutatástáblát az aktív lapról:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueKiválasztás. Tiszta tartalom

Formázza meg a munkafüzet összes kimutatástábláját

A munkafüzet összes kimutatástábláját formázhatja a VBA használatával. A következő kód egy ciklusszerkezetet használ a munkafüzet összes lapjának áthurkolására, és a munkafüzet összes kimutatási táblázatának törlésére:

12345678910111213

AlformázásAllThePivotTablesInAWorkbook ()Dim wks munkalapkéntDim wb Mint munkafüzetWb = ActiveWorkbook beállításaDim pt PivotTable -kéntMinden hétre wb.SheetsMinden pt In wks.PivotTablept.TableStyle2 = "PivotStyleLight15"Következő ptKövetkező hétEnd Sub

Ha többet szeretne megtudni a hurkok használatáról a VBA -ban, kattintson ide.

Pivot tábla mezőinek eltávolítása

A kimutatástáblákat a VBA segítségével távolíthatja el. A következő kód eltávolítja a Sorok szakasz Termék mezőjét az aktív munkalap PivotTable1 nevű kimutatásából:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Tájolás = _xlRejtett

Szűrő létrehozása

A PivotTable1 nevű pivot tábla létrehozásra került a Sorok szakasz Termék, az Értékesítés pedig az Értékek részben. Szintén létrehozhat szűrőt a kimutatástáblához a VBA használatával. A következő kód létrehoz egy szűrőt a Régiók alapján a Szűrők részben:

123	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Pozíció = 1

Ha a kimutatástáblát egyetlen jelentési elem alapján szeretné szűrni ebben az esetben a keleti régióban, akkor a következő kódot használja:

12345

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). CurrentPage = _"Keleti"

Tegyük fel, hogy a kimutatástáblát több régió, ebben az esetben kelet és észak alapján szeretné szűrni: a következő kódot használja:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Pozíció = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = IgazActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems ("Dél"). Látható = Hamis.PivotItems ("West"). Visible = FalseVége ezzel

Pivot táblázat frissítése

Frissítheti a kimutatástáblát a VBA -ban. A következő kód használatával frissítheti a VBA PivotTable1 nevű táblázatát:

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