Wie filtert man eine Pivot-Tabelle basierend auf einem bestimmten Zellwert in Excel?
In Excel werden Pivot-Tabellen häufig zur effizienten Zusammenfassung, Analyse und Exploration von Daten verwendet. Standardmäßig erfolgt die Filterung innerhalb einer Pivot-Tabelle normalerweise durch Auswahl der gewünschten Elemente aus dem Filter-Dropdown-Menü. Während dieser Ansatz Flexibilität bietet, gibt es bestimmte Szenarien, in denen eine dynamischere Filtermethode benötigt wird – zum Beispiel möchten Sie vielleicht, dass sich die Ergebnisse der Pivot-Tabelle automatisch ändern, basierend auf dem Wert, der in einer bestimmten Arbeitsblattzelle eingegeben wird. Dies ist besonders hilfreich bei der Vorbereitung von Dashboards, Automatisierung von Workflows oder beim Erstellen interaktiver Berichte für Endbenutzer, die möglicherweise nicht mit manueller Filterung vertraut sind.
Excel bietet keine Standardfunktion, die einen Zellenwert nativ mit einem Pivot-Tabellenfilter verknüpft (ohne Code). Es gibt jedoch mehrere praktische Techniken, um diese Anforderung zu erfüllen, jede mit ihren eigenen Vorteilen und Aspekten, die zu beachten sind. Dieses Tutorial stellt zunächst eine einfache VBA-Methode vor, um eine Zelle direkt mit einem Pivot-Tabellenfilter zu verbinden, sodass die Pivot-Tabelle sofort aktualisiert wird, wenn sich der Zellenwert ändert. Darüber hinaus behandeln wir alternative Methoden, wie die Verwendung von Excel-Formeln (z.B., GETPIVOTDATA, FILTER) zur Anzeige gefilterter Ergebnisse und die Verwendung von Slicer als grafische Filtersteuerungen. Das Verständnis dieser Optionen hilft Ihnen, die beste Methode für Ihren Excel-Workflow und Benutzererfahrung auszuwählen.
➤ Filtern der Pivot-Tabelle basierend auf einem bestimmten Zellwert mit VBA-Code
➤ Excel-Formel - Anzeigen gefilterter Pivot-Tabellen-Ergebnisse basierend auf einem Zellwert
➤ Andere integrierte Excel-Methoden - Verwenden von Slicern als interaktive PivotTable-Filter
Filtern der Pivot-Tabelle basierend auf einem bestimmten Zellwert mit VBA-Code
Wenn Sie echte dynamische Interaktivität wünschen – das heißt, wenn Sie einen Wert in eine Zelle eingeben und der Pivot-Tabellenfilter automatisch auf die Änderung reagiert – bietet VBA eine direkte Lösung. Dies ist besonders nützlich in Dashboards, Vorlagen für Kollegen oder Situationen, in denen schnelle Filteranpassungen durch Änderung einer einzigen Zelle erforderlich sind. Diese Methode erfordert jedoch grundlegende Kenntnisse im VBA-Editor, und wie bei allen Makros muss Ihr Arbeitsblatt im makrofähigen Format (.xlsm) gespeichert werden.
Der folgende VBA-Code ermöglicht es Ihnen, eine Arbeitsblattzelle dynamisch mit einem Pivot-Tabellenfilter zu verknüpfen. Folgen Sie diesen Schritten sorgfältig und passen Sie den Arbeitsblattnamen, den Namen der Pivot-Tabelle und den Feldbezug bei Bedarf in Ihrem Arbeitsblatt an:
Schritt 1: Geben Sie den Wert, nach dem Sie Ihre Pivot-Tabelle filtern möchten, in eine Arbeitsblattzelle ein (z.B., geben Sie den Filterwert in Zelle H6 ein oder wählen Sie ihn aus).
Schritt 2: Öffnen Sie das Arbeitsblatt, das Ihre Ziel-Pivot-Tabelle enthält. Klicken Sie mit der rechten Maustaste auf die Blattregisterkarte am unteren Rand von Excel und wählen Sie „Code anzeigen“ aus dem Kontextmenü aus. Dadurch öffnet sich das VBA-Editor-Fenster für das Arbeitsblatt.
Schritt 3: Fügen Sie im geöffneten Microsoft Visual Basic for Applications (VBA)-Fenster den folgenden Code in das Code-Modul des Arbeitsblatts ein (nicht in ein Standardmodul):
VBA-Code: Filtern der Pivot-Tabelle basierend auf einem Zellwert
Private Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180702
Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String
On Error Resume Next
If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
Set xPFile = xPTable.PivotFields("Category")
xStr = Target.Text
xPFile.ClearAllFilters
xPFile.CurrentPage = xStr
Application.ScreenUpdating = True
End Sub📝 Hinweise:
- „Sheet1“ ist das Arbeitsblatt, das die Pivot-Tabelle enthält. Passen Sie es bei Bedarf an.
- „PivotTable2“ ist der Name Ihrer Pivot-Tabelle. Sie können ihn im Register „PivotTable Analyze“ finden.
- „Category“ ist das Feld, das Sie filtern möchten. Es muss genau mit dem Feldnamen übereinstimmen.
- H6 ist die Filterzelle. Stellen Sie sicher, dass der Wert mit einem Element in der Filterliste übereinstimmt.
- Filterwerte müssen Zeichen für Zeichen übereinstimmen. Zusätzliche Leerzeichen oder Tippfehler können Fehler oder leere Ergebnisse verursachen.
Schritt 4: Drücken Sie Alt + Q, um den VBA-Editor zu schließen und zu Excel zurückzukehren.
Jetzt sollte Ihre Pivot-Tabelle automatisch so gefiltert werden, dass nur noch die Daten angezeigt werden, die mit dem in Zelle H6 eingegebenen Wert übereinstimmen. Dieses Makro wird jedes Mal ausgeführt, wenn sich der Wert in H6 ändert, was es einfach macht, Ihre Datenaufbereitung dynamisch anzupassen.
Sie können den Wert in der Filterzelle jederzeit ändern – die Pivot-Tabelle wird sofort aktualisiert, sobald sich der Zelleninhalt ändert oder ersetzt wird.
Problembehebung:
- Stellen Sie sicher, dass Makros in Ihrem Arbeitsblatt aktiviert sind.
- Überprüfen Sie doppelt, dass die Arbeitsblatt-, Pivot-Tabelle- und Feldnamen mit Ihrer tatsächlichen Einrichtung übereinstimmen.
- Stellen Sie sicher, dass der Filterwert in H6 genau mit den Werten der Pivot-Tabelle übereinstimmt.
- Dieser VBA-Ansatz funktioniert für Einfachfeldfilter. Für mehrere Felder ist zusätzliches Skripting erforderlich.
Excel-Formel – Anzeige gefilterter Pivot-Tabellen-Ergebnisse basierend auf einem Zellwert
Für Benutzer, die keine Makros aktivieren möchten, bietet Excel formelbasierte Ansätze zur Anzeige von Pivot-Tabellen-Ergebnissen basierend auf einem bestimmten Zellwert. Funktionen wie GETPIVOTDATA und FILTER ändern zwar nicht die Filtereinstellungen der Pivot-Tabelle selbst, sie können aber dynamisch auf zusammenfassende Ergebnisse verweisen und präsentieren, die auf Benutzereingaben reagieren.
Diese Lösung ist besonders nützlich beim Erstellen benutzerdefinierter Zusammenfassungstabellen, Dashboards oder Berichte, die sich ändernde Kriterien widerspiegeln, die vom Benutzer eingegeben wurden – ohne die ursprüngliche Pivot-Tabellenansicht zu verändern.
Verwendung von GETPIVOTDATA:
Angenommen, Ihre Pivot-Tabelle (mit dem Namen "PivotTable2") fasst Umsätze nach Kategorie zusammen, und der Filterwert wird in Zelle H6 eingegeben. Sie können GETPIVOTDATA verwenden, um den Gesamtumsatz für die in H6 angegebene Kategorie anzuzeigen:
1. Wählen Sie die Zelle aus, in der Sie das Zusammenfassungsergebnis anzeigen möchten (z.B., I6):
=GETPIVOTDATA("Sum of Sales", $A$4, "Category", $H$6)2. Drücken Sie Enter. Wenn Sie den Wert in H6 ändern, wird das Ergebnis in I6 automatisch aktualisiert, um die entsprechende Zusammenfassung aus der Pivot-Tabelle widerzuspiegeln.
Wenn Ihre Pivot-Tabelle unterschiedliche Feldnamen oder Layouts verwendet, passen Sie die Formel entsprechend an. Um eine GETPIVOTDATA-Formel automatisch zu generieren, geben Sie = in einer Zelle ein und klicken dann auf eine Wertezelle innerhalb Ihrer Pivot-Tabelle. Excel fügt die entsprechende Formel ein, die Sie dann bei Bedarf bearbeiten können.
Verwendung von FILTER mit einer Hilfstabelle:
Wenn Sie detaillierte Datensätze aus Ihrem Originaldatensatz extrahieren möchten (anstatt nur Pivot-Tabellen-Zusammenfassungen) und Sie Excel 365 oder Excel 2019 verwenden, ermöglicht die FILTER-Funktion dynamisches Filtern basierend auf einem Zellenwert:
Angenommen, Ihre Quelldaten befinden sich im Bereich A1:C100 und die Kategorie steht in Spalte A.
1. Wählen Sie die Startzelle aus, in der die gefilterten Datensätze erscheinen sollen (z.B., J6):
=FILTER(A2:C100, A2:A100 = H6, "No data")2. Drücken Sie Enter. Die übereinstimmenden Zeilen werden in angrenzende Zellen übertragen und alle Datensätze aufgelistet, bei denen die Kategorie mit dem Wert in H6 übereinstimmt. Durch Aktualisieren von H6 werden die Ergebnisse sofort aktualisiert.
Um Pivot-Tabellen-Gruppierungen abzugleichen oder nach mehreren Kriterien zu filtern, sollten Sie GETPIVOTDATA und FILTER kombinieren oder die Formel mit zusätzlichen logischen Bedingungen erweitern.
📝 Tipps & Warnungen:
- Diese Formeln ändern nicht den eigentlichen Pivot-Tabellenfilter. Sie bieten nur eine separate, dynamische Ansicht basierend auf Zellenwerten.
- Um Pivot-Tabellenfilter direkt zu ändern, ist VBA erforderlich.
- Stellen Sie sicher, dass die in
GETPIVOTDATAverwendeten Feldnamen genau mit denen in der Pivot-Tabelle übereinstimmen (Groß-/Kleinschreibung und Leerzeichen). - Wenn Sie
#REF!-Fehler sehen, überprüfen Sie, ob Ihre Referenzen gültig sind und die Struktur der Pivot-Tabelle sich nicht geändert hat.
Andere integrierte Excel-Methoden – Verwenden von Slicern als interaktive Pivot-Tabellenfilter
Wenn VBA- oder formelbasierte Lösungen nicht vollständig zu Ihrem Workflow passen, bieten Excel-Slicer eine weitere interaktive Methode zum Filtern von Pivot-Tabellen. Slicer sind visuelle Filtersteuerungen, die es Benutzern ermöglichen, Daten mit einer einfachen Punkte-und-Klick-Oberfläche zu filtern. Obwohl sie nicht direkt mit Zellenwerten verknüpft werden können – das bedeutet, Sie können keine Zelle ändern, um einen Slicer zu steuern – sind sie intuitiv und sehr effektiv für Dashboards und Berichte, die von technisch nicht versierten Benutzern genutzt werden.
So fügen Sie einen Slicer hinzu und verwenden ihn:
- Wählen Sie eine beliebige Zelle innerhalb Ihrer Pivot-Tabelle aus.
- Gehen Sie zum Register „PivotTable Analyze“ (oder „Analyze“ in älteren Versionen) und klicken Sie auf „Slicer einfügen“.
- Aktivieren Sie im Dialogfeld „Slicer einfügen“ das Feld, nach dem Sie filtern möchten (z.B., Kategorie), und klicken Sie dann auf OK.
- Der Slicer wird auf Ihrem Arbeitsblatt erscheinen. Klicken Sie auf eine Schaltfläche, um die Pivot-Tabelle nach diesem Wert zu filtern. Halten Sie Strg gedrückt, um mehrere Elemente auszuwählen.
Slicer können formatiert, in der Größe angepasst und mit mehreren Pivot-Tabellen verknüpft werden, um synchronisierte Filterung über verschiedene Berichte hinweg zu ermöglichen. Sie sind besonders nützlich in Dashboards oder geteilten Arbeitsmappen, wo Benutzer möglicherweise nicht mit Dropdown-Filtern vertraut sind, aber trotzdem Daten leicht filtern müssen, ohne VBA zu verwenden oder Formeln zu bearbeiten.
Einschränkungen: Slicer unterstützen keine native Verknüpfung mit Zellenwerten. Wenn Ihr Workflow dynamisches Filtern erfordert, das durch Zelleingaben gesteuert wird, sollten Slicer als ergänzendes Werkzeug und nicht als Ersatz für VBA- oder formelbasierte Methoden betrachtet werden.
Darüber hinaus können Sie, wenn Ihre Daten in einer Excel-Tabelle (nicht in einer Pivot-Tabelle) gespeichert sind, Slicer weiterhin verwenden, indem Sie die Tabelle auswählen und zum Register „Tabellenentwurf“ > „Slicer einfügen“ gehen.
Problembehebung: Wenn der Slicer nicht zu filtern scheint, überprüfen Sie die Berichtsverbindungen (unter dem Register „Slicer“ oder „Analyze“), um sicherzustellen, dass er ordnungsgemäß mit der vorgesehenen Pivot-Tabelle(n) verbunden ist.
Jede der oben genannten Methoden dient einem anderen Zweck: VBA ermöglicht direkte zellgebundene Filterung, Formeln bieten dynamische Anzeige von Ergebnissen und Slicer bieten benutzerfreundliches grafisches Filtern. Wählen Sie den Ansatz, der am besten zu Ihren Anforderungen an Automatisierung, Flexibilität und Benutzerfreundlichkeit passt. Herkömmliche Pivot-Tabellen-Dropdown-Filter bleiben als grundlegende Sicherheitslösung verfügbar.
Verwandte Artikel:
- Wie kombiniert man mehrere Blätter in einer Pivot-Tabelle in Excel?
- Wie erstellt man eine Pivot-Tabelle aus einer Textdatei in Excel?
- Wie verknüpft man einen Pivot-Tabellenfilter mit einer bestimmten Zelle in Excel?
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins