- Använda GetPivotData för att erhålla ett värde
- Skapa ett pivottabell på ett ark
- Skapa ett pivottabell på ett nytt ark
- Lägga till fält i pivottabellen
- Ändra rapportlayout för pivottabellen
- Ta bort ett pivottabell
- Formatera alla pivottabeller i en arbetsbok
- Ta bort fält i ett pivottabell
- Skapa ett filter
- Uppdaterar ditt pivottabell
Denna handledning visar hur du arbetar med pivottabeller med VBA.
Pivottabeller är verktyg för datasammanfattning som du kan använda för att dra viktiga insikter och sammanfattningar från dina data. Låt oss titta på ett exempel: vi har en källdataset i cellerna A1: D21 som innehåller detaljerna om sålda produkter, som visas nedan:
Använda GetPivotData för att erhålla ett värde
Anta att du har en pivottabell som heter Pivottabell1 med försäljning i fältet Värden/data, Produkt som rader och Region som fältet Kolumner. Du kan använda metoden PivotTable.GetPivotData för att returnera värden från pivottabeller.
Följande kod returnerar $ 1130,00 (den totala försäljningen för East Region) från pivottabellen:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("Försäljning", "Region", "Öst") |
I det här fallet är försäljning "DataField", "Field1" är regionen och "Item1" är öst.
Följande kod returnerar $ 980 (den totala försäljningen för produkt -ABC i norra regionen) från pivottabellen:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("försäljning", "produkt", "ABC", "region", "norr") |
I det här fallet är försäljning "DataField", "Field1" är produkt, "Item1" är ABC, "Field2" är Region och "Item2" är norr.
Du kan också inkludera mer än 2 fält.
Syntaxen för GetPivotData är:
GetPivotData (Data fält, Fält 1, Artikel 1, Fält 2, Artikel 2… ) var:
| Parameter | Beskrivning |
|---|---|
| Data fält | Datafält som försäljning, kvantitet etc. som innehåller siffror. |
| Fält 1 | Namn på en kolumn eller radfält i tabellen. |
| Artikel 1 | Namn på ett objekt i fält 1 (valfritt). |
| Fält 2 | Namn på en kolumn eller radfält i tabellen (valfritt). |
| Punkt 2 | Namn på ett objekt i fält 2 (valfritt). |
Skapa ett pivottabell på ett ark
För att skapa en pivottabell baserat på dataintervallet ovan, i cell J2 på Sheet1 i den aktiva arbetsboken, skulle vi använda följande kod:
| 1234567891011 | Arbetsblad ("Ark1"). Celler (1, 1) .VäljActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Version: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet1! R2C10", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Ark ("Ark1"). Välj |
Resultatet är:
Skapa ett pivottabell på ett nytt ark
För att skapa en pivottabell baserat på dataområdet ovan, på ett nytt blad i den aktiva arbetsboken, skulle vi använda följande kod:
| 12345678910111213 | Arbetsblad ("Ark1"). Celler (1, 1) .VäljArk. Lägg tillActiveWorkbook.PivotCaches.Create (SourceType: = xlDatabase, SourceData: = _"Sheet1! R1C1: R21C4", Version: = xlPivotTableVersion15) .CreatePivotTable _TableDestination: = "Sheet2! R3C1", TableName: = "PivotTable1", DefaultVersion _: = xlPivotTableVersion15Ark ("Ark2"). Välj |
Lägga till fält i pivottabellen
Du kan lägga till fält i den nyskapade pivottabellen PivotTable1 baserat på dataområdet ovan. Obs! Arket som innehåller ditt pivottabell måste vara det aktiva bladet.
Om du vill lägga till en produkt i raderna använder du följande kod:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientation = xlRowFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Position = 1 |
Om du vill lägga till region i kolumnfältet använder du följande kod:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlColumnFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1 |
Om du vill lägga till försäljning i värdesektionen med valutanummerformatet använder du följande kod:
| 123456789 | ActiveSheet.PivotTables ("PivotTable1"). AddDataField ActiveSheet.PivotTables (_"PivotTable1"). PivotFields ("Sales"), "Summa försäljning", xlSumMed ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Summa försäljning").NumberFormat = "$#, ## 0.00"Sluta med |
Resultatet är:
Ändra rapportlayout för pivottabellen
Du kan ändra rapportlayouten för din pivottabell. Följande kod ändrar rapportlayouten för din pivottabell till tabellform:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). TableStyle2 = "PivotStyleLight18" |
Ta bort ett pivottabell
Du kan ta bort en pivottabell med VBA. Följande kod tar bort pivottabellen PivotTable1 på det aktiva bladet:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotSelect "", xlDataAndLabel, TrueSelection.ClearContents |
Formatera alla pivottabeller i en arbetsbok
Du kan formatera alla pivottabeller i en arbetsbok med VBA. Följande kod använder en loopstruktur för att bläddra igenom alla ark i en arbetsbok och ta bort alla pivottabeller i arbetsboken:
| 12345678910111213 | SubformateringAllThePivotTablesInAWorkbook ()Dimma veckor som arbetsbladDim wb Som arbetsbokAnge wb = ActiveWorkbookDim pt som pivottabellFör varje vecka i wb.SheetsFör varje pt In wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Nästa ptNästa veckaAvsluta Sub |
För att lära dig mer om hur du använder Loops i VBA klicka här.
Ta bort fält i ett pivottabell
Du kan ta bort fält i en pivottabell med VBA. Följande kod tar bort produktfältet i avsnittet Rader från en pivottabell med namnet PivotTable1 i det aktiva bladet:
| 12 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Product"). Orientering = _xlGömt |
Skapa ett filter
En pivottabell med namnet PivotTable1 har skapats med Produkt i avsnittet Rader och Försäljning i värdesektionen. Du kan också skapa ett filter för ditt pivottabell med VBA. Följande kod skapar ett filter baserat på region i avsnittet Filter:
| 123 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Orientation = xlPageFieldActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). Position = 1 |
För att filtrera din pivottabell baserat på ett enda rapportobjekt i det här fallet östra regionen, använder du följande kod:
| 12345 | ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). ClearAllFiltersActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Region"). CurrentPage = _"Öst" |
Låt oss säga att du ville filtrera din pivottabell baserat på flera regioner, i det här fallet öst och norr, skulle du använda följande kod:
| 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 ("söder"). Synligt = falskt.PivotItems ("väst"). Synligt = falsktSluta med |
Uppdaterar ditt pivottabell
Du kan uppdatera ditt pivottabell i VBA. Du skulle använda följande kod för att uppdatera en specifik tabell som heter PivotTable1 i VBA:
| 1 | ActiveSheet.PivotTables ("PivotTable1"). PivotCache.Refresh |
