VBA-Leitfaden für Pivot-Tabellen

In diesem Tutorial wird gezeigt, wie Sie mit Pivot-Tabellen mithilfe von VBA arbeiten.

Pivot-Tabellen sind Tools zur Datenzusammenfassung, mit denen Sie wichtige Erkenntnisse und Zusammenfassungen aus Ihren Daten ziehen können. Schauen wir uns ein Beispiel an: Wir haben einen Quelldatensatz in den Zellen A1:D21, der die Details der verkauften Produkte enthält, wie unten gezeigt:

Verwenden von GetPivotData zum Abrufen eines Werts

Angenommen, Sie haben eine PivotTable namens PivotTable1 mit Sales im Feld Werte/Daten, Produkt als Feld Zeilen und Region als Feld Spalten. Sie können die PivotTable.GetPivotData-Methode verwenden, um Werte aus Pivot-Tabellen zurückzugeben.

Der folgende Code gibt 1.130,00 USD (den Gesamtumsatz für die Region Ost) aus der PivotTable zurück:

1	MsgBox ActiveCell.PivotTable.GetPivotData("Umsatz", "Region", "Ost")

In diesem Fall ist Sales das „DataField“, „Field1“ ist die Region und „Item1“ ist Ost.

Der folgende Code gibt 980 USD (der Gesamtumsatz für Produkt ABC in der Region Nord) aus der Pivot-Tabelle zurück:

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

In diesem Fall ist Sales das „DataField“, „Field1“ ist Produkt, „Item1“ ist ABC, „Field2“ ist die Region und „Item2“ ist Nord.

Sie können auch mehr als 2 Felder einschließen.

Die Syntax für GetPivotData lautet:

GetPivotData (Datenfeld, Feld1, Gegenstand 1, Feld2, Artikel2… ) wo:

Parameter	Beschreibung
Datenfeld	Datenfeld wie Umsatz, Menge usw., das Zahlen enthält.
Feld 1	Name eines Spalten- oder Zeilenfelds in der Tabelle.
Gegenstand 1	Name eines Elements in Feld 1 (optional).
Feld 2	Name eines Spalten- oder Zeilenfelds in der Tabelle (optional).
Artikel 2	Name eines Elements in Feld 2 (optional).

Erstellen einer Pivot-Tabelle auf einem Blatt

Um eine Pivot-Tabelle basierend auf dem obigen Datenbereich zu erstellen, würden wir in Zelle J2 auf Sheet1 der aktiven Arbeitsmappe den folgenden Code verwenden:

1234567891011

Worksheets("Sheet1").Cells(1, 1).SelectActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"Blatt1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _TableDestination:="Sheet1!R2C10", TableName:="PivotTable1", DefaultVersion _:=xlPivotTableVersion15Sheets("Sheet1").Select

Das Ergebnis ist:

Erstellen einer Pivot-Tabelle auf einem neuen Blatt

Um eine Pivot-Tabelle basierend auf dem obigen Datenbereich auf einem neuen Blatt der aktiven Arbeitsmappe zu erstellen, würden wir den folgenden Code verwenden:

12345678910111213

Worksheets("Sheet1").Cells(1, 1).SelectTabellen.HinzufügenActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"Blatt1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _:=xlPivotTableVersion15Sheets("Sheet2").Select

Hinzufügen von Feldern zur Pivot-Tabelle

Sie können Felder zu der neu erstellten Pivot-Tabelle namens PivotTable1 basierend auf dem obigen Datenbereich hinzufügen. Hinweis: Das Blatt, das Ihre Pivot-Tabelle enthält, muss das aktive Blatt sein.

Um Produkt zum Zeilenfeld hinzuzufügen, verwenden Sie den folgenden Code:

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

Um Region zum Spaltenfeld hinzuzufügen, verwenden Sie den folgenden Code:

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

Um Verkäufe zum Wertebereich mit dem Währungszahlenformat hinzuzufügen, verwenden Sie den folgenden Code:

123456789

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(_"PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSumMit ActiveSheet.PivotTables("PivotTable1").PivotFields("Umsatzsumme").NumberFormat = "$#,##0.00"Ende mit

Das Ergebnis ist:

Ändern des Berichtslayouts der Pivot-Tabelle

Sie können das Berichtslayout Ihrer Pivot-Tabelle ändern. Der folgende Code ändert das Berichtslayout Ihrer Pivot-Tabelle in eine tabellarische Form:

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

Löschen einer Pivot-Tabelle

Sie können eine Pivot-Tabelle mit VBA löschen. Der folgende Code löscht die Pivot-Tabelle namens PivotTable1 auf dem aktiven Blatt:

12	ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, TrueAuswahl.Inhalte löschen

Alle Pivot-Tabellen in einer Arbeitsmappe formatieren

Sie können alle Pivot-Tabellen in einer Arbeitsmappe mit VBA formatieren. Der folgende Code verwendet eine Schleifenstruktur, um alle Blätter einer Arbeitsmappe zu durchlaufen und alle Pivot-Tabellen in der Arbeitsmappe zu löschen:

12345678910111213

UnterformatierungAllThePivotTablesInAWorkbook()Dim wks als ArbeitsblattDim wb als ArbeitsmappeSet wb = ActiveWorkbookDim pt als PivotTableFür jede Woche in wb.SheetsFür jeden Punkt in wks.PivotTablespt.TableStyle2 = "PivotStyleLight15"Nächster PunktNächste WochenEnd Sub

Um mehr über die Verwendung von Loops in VBA zu erfahren, klicken Sie hier.

Entfernen von Feldern einer Pivot-Tabelle

Sie können Felder in einer Pivot-Tabelle mit VBA entfernen. Der folgende Code entfernt das Produktfeld im Abschnitt Zeilen aus einer Pivot-Tabelle namens PivotTable1 im aktiven Blatt:

12	ActiveSheet.PivotTables("PivotTable1").PivotFields("Produkt").Orientation = _xlVersteckt

Einen Filter erstellen

Eine Pivot-Tabelle mit dem Namen PivotTable1 wurde mit Produkt im Abschnitt Zeilen und Verkauf im Abschnitt Werte erstellt. Sie können auch mit VBA einen Filter für Ihre Pivot-Tabelle erstellen. Der folgende Code erstellt einen Filter basierend auf der Region im Abschnitt Filter:

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

Um Ihre Pivot-Tabelle basierend auf einem einzelnen Berichtselement in diesem Fall der Region Ost zu filtern, verwenden Sie den folgenden Code:

12345

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

Angenommen, Sie möchten Ihre Pivot-Tabelle nach mehreren Regionen filtern, in diesem Fall nach Osten und Norden, verwenden Sie den folgenden Code:

1234567891011121314

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1ActiveSheet.PivotTables("PivotTable1").PivotFields("Region"). _EnableMultiplePageItems = TrueMit ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").PivotItems("Süd").Visible = False.PivotItems("West").Visible = FalseEnde mit

Aktualisieren Ihrer Pivot-Tabelle

Sie können Ihre Pivot-Tabelle in VBA aktualisieren. Sie würden den folgenden Code verwenden, um eine bestimmte Tabelle namens PivotTable1 in VBA zu aktualisieren:

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