- Bruk GetPivotData til å skaffe en verdi
- Opprette et pivottabell på et ark
- Opprette et pivottabell på et nytt ark
- Legge til felt i pivottabellen
- Endre rapportoppsettet for pivottabellen
- Slette et pivottabell
- Formater alle pivottabellene i en arbeidsbok
- Fjerne felt i et pivottabell
- Opprette et filter
- Oppdaterer pivottabellen din
Denne opplæringen vil demonstrere hvordan du arbeider med pivottabeller ved hjelp av VBA.
Pivottabeller er verktøy for datasammendrag som du kan bruke til å trekke sentral innsikt og sammendrag av dataene dine. La oss se på et eksempel: vi har et kildedatasett i cellene A1: D21 som inneholder detaljene for solgte produkter, vist nedenfor:
Bruk GetPivotData til å skaffe en verdi
Anta at du har en pivottabell som heter pivottabell1 med salg i verdier/datafeltet, produkt som rader -feltet og region som kolonner -feltet. Du kan bruke metoden PivotTable.GetPivotData til å returnere verdier fra pivottabeller.
Følgende kode returnerer $ 1130,00 (det totale salget for østregionen) fra pivottabellen:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Salg", "Region", "Øst") |
I dette tilfellet er salg "DataField", "Field1" er regionen og "Item1" er øst.
Følgende kode returnerer $ 980 (det totale salget for produkt -ABC i Nord -regionen) fra pivottabellen:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Salg", "Produkt", "ABC", "Region", "Nord") |
I dette tilfellet er salg "DataField", "Field1" er produkt, "Item1" er ABC, "Field2" er Region og "Item2" er nord.
Du kan også inkludere mer enn 2 felt.
Syntaksen for GetPivotData er:
GetPivotData (DataField, Felt 1, Vare 1, Felt 2, Vare 2… ) hvor:
| Parameter | Beskrivelse |
|---|---|
| Datafield | Datafelt som salg, mengde etc. som inneholder tall. |
| Felt 1 | Navnet på et kolonne- eller radfelt i tabellen. |
| Artikkel 1 | Navnet på et element i felt 1 (valgfritt). |
| Felt 2 | Navn på et kolonne- eller radfelt i tabellen (valgfritt). |
| Artikkel 2 | Navnet på et element i felt 2 (valgfritt). |
Opprette et pivottabell på et ark
For å lage en pivottabell basert på dataområdet ovenfor, i celle J2 på ark1 i den aktive arbeidsboken, ville vi bruke følgende kode:
| 1234567891011 | Regneark ("Ark1"). Celler (1, 1) .VelgActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Versjon: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Ark ("Ark1"). Velg |
Resultatet er:
Opprette et pivottabell på et nytt ark
For å lage en pivottabell basert på dataområdet ovenfor, på et nytt ark, i den aktive arbeidsboken, ville vi bruke følgende kode:
| 12345678910111213 | Regneark ("Ark1"). Celler (1, 1) .VelgArk. Legg tilActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Versjon: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Ark ("Ark2"). Velg |
Legge til felt i pivottabellen
Du kan legge til felt i den nyopprettede pivottabellen PivotTable1 basert på dataområdet ovenfor. Merk: Arket som inneholder pivottabellen, må være det aktive arket.
For å legge til produkt i radfeltet, bruker du følgende kode:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientation = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Position = 1 |
Hvis du vil legge til region i kolonnefeltet, bruker du følgende kode:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Posisjon = 1 |
Hvis du vil legge til salg i verdiseksjonen med valutanummerformatet, bruker du følgende kode:
| 123456789 | ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"Pivottabell1"). PivotFields ("salg"), "sum av salg", xlSumMed ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Sum av salg").NumberFormat = "$#, ## 0.00"Slutt med |
Resultatet er:
Endre rapportoppsettet for pivottabellen
Du kan endre rapportoppsettet i pivottabellen. Følgende kode vil endre rapportoppsettet i pivottabellen til tabellform:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18" |
Slette et pivottabell
Du kan slette et pivottabell med VBA. Følgende kode vil slette pivottabellen PivotTable1 på det aktive arket:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents |
Formater alle pivottabellene i en arbeidsbok
Du kan formatere alle pivottabellene i en arbeidsbok ved hjelp av VBA. Følgende kode bruker en sløyfestruktur for å gå gjennom alle arkene i en arbeidsbok og slette alle pivottabellene i arbeidsboken:
| 12345678910111213 | Sub FormattingAllThePivotTablesInAWorkbook ()Dim wks Som regnearkDim wb Som arbeidsbokSett wb = ActiveWorkbookDim pt som pivottabellFor hver uke i wb. ArkFor hver pkt In wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Neste ptNeste ukeSlutt Sub |
For å lære mer om hvordan du bruker Loops i VBA, klikk her.
Fjerne felt i et pivottabell
Du kan fjerne felt i en pivottabell ved hjelp av VBA. Følgende kode vil fjerne produktfeltet i rader -delen fra en pivottabell med navnet PivotTable1 i det aktive arket:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientering = _xlSkjult |
Opprette et filter
En pivottabell kalt PivotTable1 er opprettet med Produkt i rader -delen og Salg i verdiseksjonen. Du kan også lage et filter for pivottabellen din ved hjelp av VBA. Følgende kode vil opprette et filter basert på region i filtreringsdelen:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientering = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Posisjon = 1 |
For å filtrere pivottabellen din basert på et enkelt rapportelement i dette tilfellet øst -regionen, vil du bruke følgende kode:
| 12345 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). CurrentPage = _"Øst" |
La oss si at du ønsket å filtrere pivottabellen din basert på flere regioner, i dette tilfellet øst og nord, ville du bruke følgende kode:
| 1234567891011121314 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientering = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Posisjon = 1ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). _EnableMultiplePageItems = TrueMed ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region").PivotItems ("Sør"). Synlig = usant.PivotItems ("vest"). Synlig = usantSlutt med |
Oppdaterer pivottabellen din
Du kan oppdatere pivottabellen i VBA. Du vil bruke følgende kode for å oppdatere en bestemt tabell som heter PivotTable1 i VBA:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh |
