VBA-Leitfaden für Pivot-Tabellen

In diesem Tutorial wird gezeigt, wie Sie unter Verwendung von VBA mit Pivot-Tabellen arbeiten können.

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

Verwenden von GetPivotData zum Ermitteln eines Wertes

Angenommen, Sie haben eine PivotTable mit dem Namen PivotTable1, die „Sales“ im Feld Werte/Daten, „Product“ im Feld Zeilen und „Region“ im Feld Spalten enthält. Sie können die Methode PivotTable.GetPivotData verwenden, um Werte aus Pivot-Tabellen zurückgeben zu lassen.

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

MsgBox ActiveCell.PivotTable.GetPivotData("Sales", "Region", "East")

In diesem Fall ist „Sales“ das Wertefeld, „Feld1“ ist „Region“ und „Element1“ ist „East“.

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

MsgBox ActiveCell.PivotTable.GetPivotData("Sales", "Product", "ABC", "Region", "North")

In diesem Fall ist „Sales“ das „Wertefeld“, „Feld1“ ist „Product“, „Element1“ ist „ABC“, „Feld2“ ist „Region“ und „Element2“ ist „North“.

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

Die Syntax für GetPivotData lautet:

GetPivotData(DatenFeld, Feld1, Element1, Feld2, Element2…) wobei:

Parameter	Beschreibung
Wertefeld	Datenfeld wie z. B. Umsatz, Menge usw., das Zahlen enthält.
Feld 1	Name eines Spalten- oder Zeilenfeldes in der Tabelle.
Element 1	Name eines Artikels in Feld 1 (Optional).
Feld 2	Name eines Spalten- oder Zeilenfeldes in der Tabelle (Optional).
Element 1	Name eines Elements in Feld 2 (Optional).

Erstellen einer Pivot-Tabelle auf einem Blatt

Um eine Pivot-Tabelle auf der Basis des obigen Datenbereichs in Zelle J2 auf „Sheet1“ der aktuellen Arbeitsmappe zu erstellen, würden wir den folgenden Code verwenden:

Worksheets("Sheet1").Cells(1, 1).Select

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

 "Sheet1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _

  TableDestination:="Sheet1!R2C10", TableName:="PivotTable1", DefaultVersion _

  :=xlPivotTableVersion15

  Sheets("Sheet1").Select

Das Ergebnis ist:

Erstellen einer Pivot-Tabelle auf einem neuen Blatt

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

Worksheets("Sheet1").Cells(1, 1).Select

Sheets.Add

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

 "Sheet1!R1C1:R21C4", Version:=xlPivotTableVersion15).CreatePivotTable _

  TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _

  :=xlPivotTableVersion15

  Sheets("Sheet2").Select

Hinzufügen von Feldern zur Pivot-Tabelle

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

Um „Product“ zum Zeilenfeld hinzuzufügen, würden Sie den folgenden Code verwenden:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = xlRowField

ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Position = 1

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

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlColumnField

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

Um „Sales“ in den Abschnitt Werte mit dem Währungszahlenformat hinzuzufügen, würden Sie den folgenden Code verwenden:

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _

        "PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSum

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales")

        .NumberFormat = "$#,##0.00"

End With

Das Ergebnis ist:

Ändern des Berichtslayouts der Pivot-Tabelle

Sie können das Berichtslayout Ihrer Pivot-Tabelle ändern. Der folgende Code ändert das Berichtslayout der Pivot-Tabelle in Tabellenform:

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 aus dem aktuellen Arbeitsblatt:

ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Selection.ClearContents

Formatieren aller Pivot-Tabellen in einer Arbeitsmappe

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 formatieren:

Sub FormatierenAllerPivotTabellenInArbeitsmappe()
Dim wks As Worksheet
Dim wb As Arbeitsmappe
Set wb = ActiveWorkbook
Dim pt As PivotTable

For Each wks In wb.Sheets
For Each pt In wks.PivotTables
pt.TableStyle2 = "PivotStyleLight15"
Next pt
Next wks

End Sub

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

Entfernen von Feldern aus einer Pivot-Tabelle

Sie können Felder in einer Pivot-Tabelle mit VBA entfernen. Der folgende Code entfernt das Feld „Product“ im Abschnitt „Zeilen“ aus einer Pivot-Tabelle mit dem Namen „PivotTable1“ im aktuellen Arbeitsblatt:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = _
xlHidden

Erstellen eines Filters

Eine Pivot-Tabelle namens PivotTable1 wurde mit „Product“ im Abschnitt Zeilen und „Sales“ im Abschnitt Werte erstellt. Sie können auch einen Filter für Ihre Pivot-Tabelle mit VBA erstellen. Der folgende Code erstellt einen Filter basierend auf „Region“ im Abschnitt Filter:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageField

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

Um Ihre Pivot-Tabelle auf der Basis eines einzelnen Berichtselements zu filtern, in diesem Fall die Region „East“, würden Sie den folgenden Code verwenden:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").ClearAllFilters

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

"East"

Angenommen, Sie möchten Ihre Pivot-Tabelle nach mehreren Regionen filtern, in diesem Fall nach „East“ und „North“, dann würden Sie den folgenden Code verwenden:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageField

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

ActiveSheet.PivotTables("PivotTable1").PivotFields("Region"). _
EnableMultiplePageItems = True

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")

.PivotItems("South").Visible = False

.PivotItems("West").Visible = False

End With

Aktualisieren der 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:

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