VBA -guide til pivottabeller

Denne vejledning viser, hvordan du arbejder med pivottabeller ved hjælp af VBA.

Pivottabeller er værktøjer til opsummering af data, som du kan bruge til at trække nøgleindsigt og resuméer fra dine data. Lad os se på et eksempel: vi har et kildedatasæt i cellerne A1: D21, der indeholder detaljerne for solgte produkter, vist nedenfor:

Brug af GetPivotData til at opnå en værdi

Antag at du har en pivottabel kaldet pivottabel1 med salg i værdier/datafeltet, produkt som feltet rækker og område som feltet kolonner. Du kan bruge metoden PivotTable.GetPivotData til at returnere værdier fra pivottabeller.

Følgende kode returnerer $ 1.130,00 (det samlede salg for østregionen) fra pivottabellen:

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Salg", "Region", "Øst")

I dette tilfælde er salg "DataField", "Field1" er regionen og "Item1" er øst.

Følgende kode returnerer $ 980 (det samlede salg for produkt -ABC i region Nord) fra pivottabellen:

1	MsgBox ActiveCell.PivotTable.GetPivotData ("Salg", "Produkt", "ABC", "Region", "Nord")

I dette tilfælde er salg "DataField", "Field1" er produkt, "Item1" er ABC, "Field2" er Region og "Item2" er nord.

Du kan også inkludere mere end 2 felter.

Syntaksen for GetPivotData er:

GetPivotData (DataField, Felt 1, Vare 1, Felt 2, Vare 2… ) hvor:

Parameter	Beskrivelse
Datafield	Datafelt som salg, mængde osv., Der indeholder tal.
Felt 1	Navn på en kolonne eller rækkefelt i tabellen.
Punkt 1	Navn på et element i felt 1 (valgfrit).
Felt 2	Navn på en kolonne eller rækkefelt i tabellen (valgfrit).
Punkt 2	Navn på et element i felt 2 (valgfrit).

Oprettelse af et pivottabel på et ark

For at oprette en pivottabel baseret på dataområdet ovenfor på celle J2 på Sheet1 i den aktive projektmappe, ville vi bruge følgende kode:

1234567891011

Regneark ("Ark1"). Celler (1, 1) .VælgActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Version: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Ark ("Ark1"). Vælg

Resultatet er:

Oprettelse af et pivottabel på et nyt ark

For at oprette en pivottabel baseret på dataområdet ovenfor, på et nyt ark i den aktive projektmappe, ville vi bruge følgende kode:

12345678910111213

Regneark ("Ark1"). Celler (1, 1) .VælgArk. TilføjActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Version: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Ark ("Ark2"). Vælg

Tilføjelse af felter til pivottabellen

Du kan tilføje felter til den nyoprettede pivottabel kaldet Pivottabel1 baseret på dataområdet ovenfor. Bemærk: Arket, der indeholder dit pivottabel, skal være det aktive ark.

For at tilføje produkt til rækkefeltet skal du bruge følgende kode:

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

Hvis du vil føje region til kolonnefeltet, skal du bruge følgende kode:

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

Hvis du vil føje salg til værdisektionen med valutanummerformatet, skal du bruge følgende kode:

123456789

ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"Pivottabel1"). PivotFields ("salg"), "sum af salg", xlSumMed ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Sum af salg").NumberFormat = "$#, ## 0.00"Afslut med

Resultatet er:

Ændring af rapportlayoutet i pivottabellen

Du kan ændre rapportlayoutet i din pivottabel. Følgende kode ændrer rapportlayoutet i din pivottabel til tabelform:

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

Sletning af en pivottabel

Du kan slette en pivottabel ved hjælp af VBA. Følgende kode vil slette pivottabellen kaldet PivotTable1 på det aktive ark:

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

Formater alle pivottabellerne i en projektmappe

Du kan formatere alle pivottabellerne i en projektmappe ved hjælp af VBA. Den følgende kode bruger en loop -struktur for at gennemgå alle arkene i en projektmappe og slette alle pivottabellerne i projektmappen:

12345678910111213

UnderformateringAllThePivotTablesInAWorkbook ()Dim wks Som regnearkDim wb Som projektmappeIndstil wb = ActiveWorkbookDim pt som pivottabelFor hver uge i wb. ArkFor hver pt In wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Næste ptNæste ugeAfslut Sub

For at lære mere om, hvordan du bruger Loops i VBA, klik her.

Fjernelse af felter i en pivottabel

Du kan fjerne felter i en pivottabel ved hjælp af VBA. Følgende kode vil fjerne produktfeltet i afsnittet Rækker fra en pivottabel med navnet PivotTable1 i det aktive ark:

12	ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Produkt"). Orientering = _xlSkjult

Oprettelse af et filter

Der er oprettet en pivottabel kaldet pivottabel1 med produkt i sektionen Rækker og salg i værdiafsnittet. Du kan også oprette et filter til din pivottabel ved hjælp af VBA. Følgende kode vil oprette et filter baseret på Region i sektionen Filtre:

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

For at filtrere din pivottabel baseret på et enkelt rapportelement i dette tilfælde østregionen, vil du bruge følgende kode:

12345

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

Lad os sige, at du ønskede at filtrere din pivottabel baseret på flere regioner, i dette tilfælde øst og nord, ville du bruge følgende kode:

1234567891011121314

ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = TrueMed ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems ("Syd"). Synlig = falsk.PivotItems ("vest"). Synlig = falskAfslut med

Opfriskning af din pivottabel

Du kan opdatere din pivottabel i VBA. Du ville bruge følgende kode for at opdatere en bestemt tabel kaldet PivotTable1 i VBA:

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