Pivotdetails auslesen in Excel per VBA
Schritt-für-Schritt-Anleitung
Um die Detaildaten einer Pivot-Tabelle in Excel per VBA auszulesen, kannst du das folgende Makro verwenden. Dieses Beispiel wurde unter Excel 2003 erstellt, sollte jedoch auch in neueren Versionen funktionieren.
- Öffne Excel und lade die Arbeitsmappe mit der Pivot-Tabelle.
- Drücke
ALT + F11, um den VBA-Editor zu öffnen.
- Klicke auf
Einfügen > Modul, um ein neues Modul zu erstellen.
- Kopiere und füge den folgenden Code in das Modul ein:
Sub PivotAllesAnzeigen()
Dim pvTab As PivotTable, pvField As PivotField, pvItem As PivotItem
Dim StatusCalc As Long
On Error GoTo Fehler
StatusCalc = Application.Calculation
Set pvTab = ActiveSheet.PivotTables(1)
If StatusCalc <> xlCalculationAutomatic Then _
Application.Calculation = xlCalculationAutomatic
For Each pvField In pvTab.PageFields
pvField.CurrentPage = "(Alle)"
Next
For Each pvField In pvTab.RowFields
For Each pvItem In pvField.PivotItems
If pvItem.Visible = False Then pvItem.Visible = True
Next
Next
For Each pvField In pvTab.ColumnFields
For Each pvItem In pvField.PivotItems
If pvItem.Visible = False Then pvItem.Visible = True
Next
Next
pvTab.RefreshTable
Fehler:
With Err
Select Case .Number
Case 0 ' kein Fehler
Case 1004
If Not pvItem Is Nothing Then pvItem.Delete
Resume Next
Case Else
MsgBox "Fehler-nr.: " & .Number & vbLf & .Description
End Select
End With
If StatusCalc <> Application.Calculation Then Application.Calculation = StatusCalc
End Sub
- Schließe den VBA-Editor und kehre zu Excel zurück.
- Führe das Makro über
Entwicklertools > Makros aus.
Mit diesem Makro werden alle Filter in der Pivot-Tabelle aufgehoben, sodass du Zugriff auf alle Detaildaten hast.
Häufige Fehler und Lösungen
-
Fehler: "Fehler-nr.: 1004"
Dieser Fehler tritt auf, wenn ein Pivot-Item nicht sichtbar gemacht werden kann, weil es keine Daten gibt. Um dies zu beheben, wird das Item in der Fehlerbehandlung gelöscht.
-
Fehler beim Löschen von Items
Achte darauf, dass die Zeile If Not pvItem Is Nothing Then pvItem.Delete korrekt ist. Das "Not" sollte nicht weggelassen werden, da es sonst zu einem Fehler kommt.
Alternative Methoden
Falls du keine VBA-Lösung verwenden möchtest, kannst du auch die Pivot-Tabelle manuell bearbeiten:
- Klicke auf die Pivot-Tabelle.
- Verwende die Filteroptionen in den Pivot-Feldern, um die Sichtbarkeit der Elemente anzupassen.
- Setze alle Filter zurück, um die vollständigen Daten anzuzeigen.
Praktische Beispiele
Angenommen, du hast eine Pivot-Tabelle mit Verkaufsdaten. Mit dem oben genannten Makro kannst du alle Filter entfernen und auf die vollständige Liste aller Verkäufe zugreifen.
Beispiel:
- Ursprüngliche Filter: Nur Verkäufe aus dem Jahr 2022 sind sichtbar.
- Nach Ausführen des Makros: Alle Verkaufsdaten aus allen Jahren sind sichtbar.
Tipps für Profis
- Verwende
Option Explicit am Anfang jedes Moduls, um sicherzustellen, dass alle Variablen deklariert sind. Dies hilft, Fehler zu vermeiden.
- Füge Kommentare zu deinem Code hinzu, um ihn für andere (oder dich selbst in der Zukunft) verständlicher zu machen.
- Teste das Makro zunächst in einer Kopie deiner Arbeitsmappe, um Datenverlust zu vermeiden.
FAQ: Häufige Fragen
1. Wie kann ich das Makro anpassen?
Du kannst das Makro anpassen, indem du die Filterbedingungen in den For Each-Schleifen änderst.
2. Funktioniert das Makro in allen Excel-Versionen?
Das Beispiel wurde in Excel 2003 erstellt, sollte aber auch in neueren Versionen funktionieren. Achte jedoch darauf, dass sich die Benutzeroberfläche geändert haben könnte.