Tutorials

Excel VBA: Pivot-Tabellen automatisch erstellen – Ein Schritt-für-Schritt-Tutorial

Melden

Hier ist ein ausführliches Tutorial, das dir zeigt, wie du mit Excel VBA eine Pivot-Tabelle automatisch erstellst.

Die Automatisierung von Pivot-Tabellen mit VBA ist besonders hilfreich, wenn du regelmäßig Berichte aus ähnlich strukturierten Datensätzen erstellen musst. In diesem Guide lernst du, wie du den Quellbereich definierst, einen Pivot-Cache erstellst und die Felder anordnest.

1. Die Vorbereitung der Daten

Damit das Makro reibungslos funktioniert, sollten deine Daten:

  • In der ersten Zeile Überschriften haben.
  • Keine komplett leeren Zeilen oder Spalten innerhalb des Datensatzes enthalten.

2. Der vollständige VBA-Code

Kopiere diesen Code in ein neues Modul in deinem VBA-Editor (Öffnen mit ALT + F11 -> Einfügen -> Modul).

Sub ErstellePivotTabelle()
    Dim wsQuelle As Worksheet
    Dim wsZiel As Worksheet
    Dim PCache As PivotCache
    Dim PTabelle As PivotTable
    Dim PRange As Range
    Dim LetzteZeile As Long
    Dim LetzteSpalte As Long

    ' 1. Arbeitsblätter festlegen
    Set wsQuelle = ActiveSheet

    ' Prüfen, ob das Blatt "Pivot_Bericht" schon existiert, falls ja: löschen
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Pivot_Bericht").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' Neues Zielblatt erstellen
    Set wsZiel = Sheets.Add(After:=wsQuelle)
    wsZiel.Name = "Pivot_Bericht"

    ' 2. Datenbereich dynamisch ermitteln
    LetzteZeile = wsQuelle.Cells(wsQuelle.Rows.Count, 1).End(xlUp).Row
    LetzteSpalte = wsQuelle.Cells(1, wsQuelle.Columns.Count).End(xlToLeft).Column
    Set PRange = wsQuelle.Cells(1, 1).Resize(LetzteZeile, LetzteSpalte)

    ' 3. Pivot Cache erstellen (Speicherort der Daten für die Pivot)
    Set PCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=PRange)

    ' 4. Pivot-Tabelle im Zielblatt erstellen
    Set PTabelle = PCache.CreatePivotTable( _
        TableDestination:=wsZiel.Cells(3, 1), _
        TableName:="UmsatzAnalyse")

    ' 5. Felder anordnen
    With PTabelle
        ' Zeilenfeld hinzufügen (Beispiel: "Region")
        With .PivotFields("Region")
            .Orientation = xlRowField
            .Position = 1
        End With

        ' Spaltenfeld hinzufügen (Beispiel: "Jahr")
        With .PivotFields("Jahr")
            .Orientation = xlColumnField
            .Position = 1
        End With

        ' Filter hinzufügen (Beispiel: "Kategorie")
        With .PivotFields("Kategorie")
            .Orientation = xlPageField
            .Position = 1
        End With

        ' Wertefeld hinzufügen (Beispiel: "Umsatz")
        .AddDataField .PivotFields("Umsatz"), "Summe von Umsatz", xlSum

        ' Zahlenformat im Wertefeld anpassen
        .PivotFields("Summe von Umsatz").NumberFormat = "#.##0,00 €"
    End With

    MsgBox "Pivot-Tabelle wurde erfolgreich erstellt!", vbInformation
End Sub

3. Erläuterung der wichtigsten Schritte

A. Der Pivot Cache (PivotCache)

Bevor Excel eine Pivot-Tabelle zeichnet, lädt es die Daten in einen internen Speicher, den sogenannten Cache. Dies macht die Tabelle performant. Im Code geschieht dies über PivotCaches.Create.

B. Dynamischer Datenbereich

Anstatt einen festen Bereich wie A1:D100 zu nutzen, ermitteln wir die letzte Zeile und Spalte:

LetzteZeile = wsQuelle.Cells(wsQuelle.Rows.Count, 1).End(xlUp).Row
Set PRange = wsQuelle.Cells(1, 1).Resize(LetzteZeile, LetzteSpalte)

Dadurch passt sich das Makro automatisch an, wenn dein Datensatz wächst.

C. Die Orientierung (Orientation)

Hier bestimmst du, wo die Felder landen:

  • xlRowField: Zeilen
  • xlColumnField: Spalten
  • xlPageField: Filter (Berichtsfilter)
  • xlDataField: Werte (Berechnungen wie Summe, Anzahl etc.)

4. Anpassung an deine Daten

Damit das Makro bei dir funktioniert, musst du die Namen der Felder in den Anführungszeichen an deine Spaltenüberschriften anpassen:

  • Ändere "Region" in den Namen deiner Zeilenbeschriftung.
  • Ändere "Umsatz" in den Namen deiner Zahlenspalte.
  • Ändere "Summe von Umsatz" in einen beliebigen Anzeigenamen.

5. So führst du das Makro aus

  1. Gehe zurück in dein Excel-Fenster.
  2. Drücke ALT + F8.
  3. Wähle ErstellePivotTabelle aus und klicke auf Ausführen.

Profi-Tipp: Fehler vermeiden

Wenn du das Makro mehrmals hintereinander ausführst, würde Excel versuchen, ein Blatt zu erstellen, das bereits existiert. Der Code-Block unter Punkt 1 (On Error Resume Next ... Delete) sorgt dafür, dass das alte Berichtsblatt vorher gelöscht wird, um Fehler zu vermeiden.

0