Pivot Table: Filter auslesen in Excel
Schritt-für-Schritt-Anleitung
Um die Filter einer Pivot-Tabelle auszulesen und diese in einer Textbox oder Zelle anzuzeigen, kannst Du die folgende VBA-Lösung verwenden. Der Code wurde für Excel 2003 erstellt, sollte jedoch auch in neueren Versionen funktionieren, wobei einige Anpassungen notwendig sind.
-
Öffne den VBA-Editor:
-
Füge ein neues Modul hinzu:
- Klicke im Projektfenster mit der rechten Maustaste auf dein Projekt und wähle
Einfügen > Modul.
-
Füge den folgenden Code ein:
Option Explicit
Sub PivotInfosActiveSheet()
Dim objShape As Shape, wks As Worksheet, Diagramm As Chart, _
strTextFeld As String, varPvTab
On Error GoTo Fehler
Select Case ActiveSheet.Name
Case "Diagramm1", "Pivot2"
Set wks = Worksheets("Pivot2")
Set Diagramm = Charts("Diagramm1")
strTextFeld = "Textfeld 1"
varPvTab = 1
Case Else
Set wks = Nothing
Set Diagramm = Nothing
End Select
If Not Diagramm Is Nothing And strTextFeld <> "" Then
MsgBox PivotInfos(pvTable:=wks.PivotTables(varPvTab))
Set objShape = Diagramm.Shapes(strTextFeld)
objShape.TextFrame.Characters.Text = PivotInfos(pvTable:=wks.PivotTables(varPvTab))
Else
MsgBox "Für die PivotTabelle in diesem Blatt gibt es noch " & _
"keine Diagramm/Textfeld-Zuordnung"
End If
Fehler:
With Err
If .Number <> 0 Then
MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description & vbLf & _
vbLf & "Makro funktioniert nur in Tabelle mit Pivottabelle!!"
End If
End With
End Sub
Function PivotInfos(pvTable As PivotTable) As String
Dim pvField As PivotField, pvItem As PivotItem, bolField1 As Boolean
bolField1 = False
For Each pvField In pvTable.PageFields
If bolField1 = False Then
PivotInfos = PivotInfos & "Seitenfeld(er)"
bolField1 = True
End If
PivotInfos = PivotInfos & vbLf & pvField.Name & ": " & pvField.CurrentPage
Next
' Zeilenfeld(er) auslesen
bolField1 = False
For Each pvField In pvTable.RowFields
If bolField1 = False Then
PivotInfos = PivotInfos & IIf(PivotInfos = "", "", vbLf) & "Zeilenfelder(er)"
bolField1 = True
End If
PivotInfos = PivotInfos & vbLf & pvField.Name & ": "
If pvField.VisibleItems.Count <> pvField.PivotItems.Count Then
PivotInfos = PivotInfos & "Item(s)"
For Each pvItem In pvField.VisibleItems
PivotInfos = PivotInfos & " - " & pvItem.Name
Next
Else
PivotInfos = PivotInfos & "Alle"
End If
Next
' Spaltenfeld(er) auslesen
bolField1 = False
For Each pvField In pvTable.ColumnFields
If bolField1 = False Then
PivotInfos = PivotInfos & IIf(PivotInfos = "", "", vbLf) & "Spaltenfelder(er)"
bolField1 = True
End If
PivotInfos = PivotInfos & vbLf & pvField.Name & ": "
If pvField.VisibleItems.Count <> pvField.PivotItems.Count Then
PivotInfos = PivotInfos & "Item(s)"
For Each pvItem In pvField.VisibleItems
PivotInfos = PivotInfos & " - " & pvItem.Name
Next
Else
PivotInfos = PivotInfos & "Alle"
End If
Next
End Function
- Füge im Tabellenmodul den folgenden Code hinzu:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call PivotInfosActiveSheet
End Sub
Häufige Fehler und Lösungen
-
Fehler 9: Index außerhalb des gültigen Bereichs:
Dieser Fehler tritt häufig auf, wenn Du den Namen des Diagramms oder der Pivot-Tabelle nicht korrekt angegeben hast. Stelle sicher, dass die Namen exakt mit den Namen im Excel übereinstimmen.
-
"Für diesen Bericht kann keine Zeitachse erstellt werden":
Dies kann auftreten, wenn die Datenquelle für die Pivot-Tabelle nicht korrekt formatiert ist. Überprüfe die Datenquelle auf Datumsformate.
-
Pivot-Tabelle Filter funktioniert nicht:
Stelle sicher, dass die Filter korrekt gesetzt sind. Manchmal kann es an der Version von Excel liegen, insbesondere bei Mehrfachauswahlen in Seitenfeldern.
Alternative Methoden
Wenn Du eine einfache Lösung ohne VBA bevorzugst, kannst Du die Filter direkt in eine Zelle schreiben, indem Du die GETPIVOTDATA-Funktion verwendest. Diese Funktion ermöglicht es Dir, spezifische Daten aus einer Pivot-Tabelle zu extrahieren, ohne dass Du VBA verwenden musst.
Ein Beispiel für die Verwendung von GETPIVOTDATA:
=GETPIVOTDATA("Wert", "PivotTable1", "Feldname", "Kriterium")
Praktische Beispiele
-
Anzeige der Filter in Zelle:
-
Filter für Diagramme:
- Um die Pivot-Filterinformationen in ein Diagramm zu integrieren, stelle sicher, dass Du die Textfeldnamen korrekt angegeben hast.
Tipps für Profis
-
VBA Debugging:
Nutze die Debug.Print-Anweisung, um Variablen während der Ausführung des Codes zu überwachen. Dies kann helfen, Fehlerquellen schnell zu identifizieren.
-
Excel Version beachten:
Sei dir bewusst, dass einige Funktionen, wie die Mehrfachauswahl in Seitenfeldern, neu in Excel 2007 eingeführt wurden. Teste Deinen Code in der entsprechenden Excel-Version.
-
Pivot Filter VBA:
Wenn Du regelmäßig mit Pivot-Tabellen arbeitest, könnte es sich lohnen, eigene Makros zu schreiben, um die Effizienz zu steigern.
FAQ: Häufige Fragen
1. Wie kann ich die Filter einer Pivot-Tabelle in Excel auslesen?
Verwende das VBA-Makro, um die Filterinformationen auszulesen und in eine Zelle oder ein Textfeld zu schreiben.
2. Warum werden bei Mehrfachauswahl nur "Mehrere Elemente" angezeigt?
Das Verhalten ist abhängig von der Excel-Version. In Excel 2007 und neuer ist diese Funktionalität anders implementiert.
3. Was tun, wenn der Pivot-Filter nicht funktioniert?
Überprüfe die Filtereinstellungen und die Datenquelle. Manchmal kann auch ein einfaches Schließen und erneutes Öffnen der Datei helfen.