VBA-gids voor draaitabellen

Deze zelfstudie laat zien hoe u met draaitabellen kunt werken met VBA.

Draaitabellen zijn hulpmiddelen voor het samenvatten van gegevens die u kunt gebruiken om belangrijke inzichten en samenvattingen uit uw gegevens te halen. Laten we een voorbeeld bekijken: we hebben een brongegevensset in cellen A1:D21 met de details van verkochte producten, hieronder weergegeven:

GetPivotData gebruiken om een ​​waarde te verkrijgen

Stel dat u een draaitabel hebt met de naam Draaitabel1 met Verkoop in het veld Waarden/gegevens, Product als het veld Rijen en Regio als het veld Kolommen. U kunt de methode PivotTable.GetPivotData gebruiken om waarden uit draaitabellen te retourneren.

De volgende code retourneert $ 1.130,00 (de totale verkoop voor de regio Oost) uit de draaitabel:

1	MsgBox ActiveCell.PivotTable.GetPivotData("Verkoop", "Regio", "Oost")

In dit geval is Verkoop het "Gegevensveld", "Veld1" is de Regio en "Artikel1" is Oost.

De volgende code retourneert $ 980 (de totale verkoop voor product ABC in de regio Noord) uit de draaitabel:

1	MsgBox ActiveCell.PivotTable.GetPivotData("Verkoop", "Product", "ABC", "Regio", "Noord")

In dit geval is Verkoop het "Gegevensveld", "Veld1" is Product, "Artikel1" is ABC, "Veld2" is Regio en "Artikel2" is Noord.

U kunt ook meer dan 2 velden opnemen.

De syntaxis voor GetPivotData is:

GetPivotData (Dataveld, Veld1, Item 1, Veld2, Artikel2… ) waar:

Parameter	Beschrijving
Dataveld	Gegevensveld zoals verkoop, hoeveelheid etc. dat getallen bevat.
Veld 1	Naam van een kolom- of rijveld in de tabel.
Item 1	Naam van een item in veld 1 (optioneel).
Veld 2	Naam van een kolom- of rijveld in de tabel (optioneel).
Artikel 2	Naam van een item in veld 2 (optioneel).

Een draaitabel op een blad maken

Om een ​​draaitabel te maken op basis van het bovenstaande gegevensbereik, zouden we in cel J2 op Blad1 van de actieve werkmap de volgende code gebruiken:

1234567891011

Werkbladen ("Blad1"). Cellen (1, 1). SelecteerActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"Blad1!R1C1:R21C4", Versie:=xlPivotTableVersion15).CreatePivotTable _TableDestination:="Blad1!R2C10", TableName:="PivotTable1", DefaultVersion _:=xlPivotTableVersion15Bladen ("Blad1"). Selecteer

Het resultaat is:

Een draaitabel maken op een nieuw blad

Om een ​​draaitabel te maken op basis van het bovenstaande gegevensbereik, op een nieuw blad, van de actieve werkmap, zouden we de volgende code gebruiken:

12345678910111213

Werkbladen ("Blad1"). Cellen (1, 1). SelecteerLakens.ToevoegenActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"Blad1!R1C1:R21C4", Versie:=xlPivotTableVersion15).CreatePivotTable _TableDestination:="Blad2!R3C1", TableName:="PivotTable1", DefaultVersion _:=xlPivotTableVersion15Bladen ("Blad2"). Selecteer

Velden toevoegen aan de draaitabel

U kunt velden toevoegen aan de nieuw gemaakte draaitabel met de naam PivotTable1 op basis van het bovenstaande gegevensbereik. Opmerking: het blad met uw draaitabel moet het actieve blad zijn.

Om Product aan het veld Rijen toe te voegen, gebruikt u de volgende code:

123	ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Oriëntatie = xlRowFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Position = 1

Om een ​​regio toe te voegen aan het veld Kolommen, gebruikt u de volgende code:

123	ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Oriëntatie = xlColumnFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1

Om Verkoop toe te voegen aan de sectie Waarden met de valuta-getalnotatie, gebruikt u de volgende code:

123456789

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _"PivotTable1"). Draaivelden ("Sales"), "Sum of Sales", xlSumMet ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales").NumberFormat = "$#,##0.00"Eindigt met

Het resultaat is:

De rapportindeling van de draaitabel wijzigen

U kunt de rapportindeling van uw draaitabel wijzigen. De volgende code verandert de rapportindeling van uw draaitabel in tabelvorm:

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

Een draaitabel verwijderen

U kunt een draaitabel verwijderen met VBA. Met de volgende code wordt de draaitabel met de naam PivotTable1 op het actieve blad verwijderd:

12	ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, TrueSelectie.ClearInhoud

Alle draaitabellen in een werkmap opmaken

U kunt alle draaitabellen in een werkmap opmaken met VBA. De volgende code gebruikt een lusstructuur om alle bladen van een werkmap te doorlopen en alle draaitabellen in de werkmap te verwijderen:

12345678910111213

Sub-opmaakAllThePivotTablesInAWorkbook()Dim wks als werkbladDim wb als werkboekStel wb = ActiveWorkbook inDim pt als draaitabelVoor elke wkn in wb.SheetsVoor elke pt In wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Volgende puntVolgende wekenEinde sub

Klik hier voor meer informatie over het gebruik van Loops in VBA.

Velden van een draaitabel verwijderen

U kunt velden in een draaitabel verwijderen met VBA. Met de volgende code wordt het veld Product in de sectie Rijen verwijderd uit een draaitabel met de naam Draaitabel1 in het actieve blad:

12	ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Oriëntatie = _xlVerborgen

Een filter maken

Er is een draaitabel gemaakt met de naam Draaitabel1 met Product in het gedeelte Rijen en Verkoop in het gedeelte Waarden. U kunt ook een filter voor uw draaitabel maken met VBA. De volgende code maakt een filter op basis van regio in de sectie Filters:

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

Als u uw draaitabel wilt filteren op basis van één rapportitem, in dit geval de regio Oost, gebruikt u de volgende code:

12345

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

Stel dat u uw draaitabel wilt filteren op basis van meerdere regio's, in dit geval Oost en Noord, zou u de volgende code gebruiken:

1234567891011121314

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1ActiveSheet.PivotTables("PivotTable1").PivotFields("Regio"). _EnableMultiplePageItems = TrueMet ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").PivotItems("Zuid").Zichtbaar = False.PivotItems("West").Zichtbaar = FalseEindigt met

Uw draaitabel vernieuwen

U kunt uw draaitabel in VBA vernieuwen. U zou de volgende code gebruiken om een ​​specifieke tabel met de naam PivotTable1 in VBA te vernieuwen:

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