Wie verknüpft man einen Pivot-Tabellenfilter mit einer bestimmten Zelle in Excel?
In Excel möchten Sie möglicherweise oft interaktive Berichte erstellen, in denen Ihr Pivot-Tabellenfilter den Wert in einer bestimmten Zelle widerspiegelt. Dies ermöglicht es Benutzern, einen Filterwert an einer Stelle auszuwählen oder einzugeben, und die Pivot-Tabelle aktualisiert sich dynamisch basierend auf dieser Eingabe. Diese Methode ist besonders nützlich beim Entwerfen von Dashboards oder benutzerdefinierten Filterschnittstellen zur Datenexploration.
Dieser Artikel bietet mehrere praktische Lösungen, einschließlich eines VBA-basierten Ansatzes und anderer integrierter Excel-Methoden, um Ihnen zu helfen, einen Pivot-Tabellenfilter mit einem Zellenwert zu verknüpfen oder ähnliche dynamische Berichtseffekte zu erzielen.
- Pivot-Tabellenfilter mit VBA-Code mit einer bestimmten Zelle verknüpfen
- Excel-Formel - Verwenden Sie Formeln (z. B. GETPIVOTDATA) in Kombination mit Slicer oder Berichtsfilterreferenzen
- Andere integrierte Excel-Methoden - Verbinden Sie Pivot-Tabellen-Slicer und Dashboards für interaktives Filtern
Pivot-Tabellenfilter mit VBA-Code mit einer bestimmten Zelle verknüpfen
Wenn Sie die direkteste Verknüpfung zwischen einer Zelle und einem Pivot-Tabellenfilter benötigen – sodass das Ändern des Zellenwerts den Pivot-Tabellenfilter automatisch aktualisiert – bietet VBA eine praktische Möglichkeit, dies zu erreichen. Dieser Ansatz eignet sich für interaktive Dashboards oder Berichte, bei denen Benutzer Datenabschnitte schnell über eine einzelne Zelle steuern möchten.
Damit diese Technik funktioniert, muss Ihre Pivot-Tabelle ein Filterfeld enthalten. Der Name des Filterfelds ist entscheidend für die korrekte Konfiguration des VBA-Codes.
Betrachten Sie das folgende Beispiel: Die Pivot-Tabelle hat ein Filterfeld namens Kategorie mit zwei Filterwerten: „Ausgaben“ und „Umsätze“. Durch das Verknüpfen einer Zelle mit dem Pivot-Tabellenfilter können Sie die angezeigten Daten steuern, indem Sie entweder „Ausgaben“ oder „Umsätze“ in Ihrer gewählten Zelle eingeben.
Um dies zu implementieren:
- Wählen Sie die Zelle aus, die Sie als Filtercontroller verwenden möchten (z. B. Zelle H6), und geben Sie im Voraus einen Ihrer Filterwerte ein. Stellen Sie sicher, dass der Wert genau mit den in dem Pivot-Tabellenfilterfeld verfügbaren übereinstimmt.
- Gehen Sie zum Arbeitsblatt mit Ihrer Pivot-Tabelle. Klicken Sie mit der rechten Maustaste auf die Blattregisterkarte und wählen Sie Code anzeigen aus dem Menü. Dies öffnet das Visual Basic for Applications-Fenster.
Fügen Sie im Microsoft Visual Basic for Applications-Fenster den folgenden VBA-Code in den Codebereich ein.
VBA-Code: Pivot-Tabellenfilter mit einer bestimmten Zelle verknüpfen
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")) 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 SubHinweise:
Nachdem Sie den Code eingefügt haben, drücken Sie Alt + Q, um das VBA-Editorfenster zu schließen und zu Excel zurückzukehren.
Jetzt wird der Filterstatus Ihrer Pivot-Tabelle durch den Inhalt der Zelle H6 gesteuert. Das einfache Ändern des Werts in Zelle H6 (auf "Umsätze" oder "Ausgaben") aktualisiert sofort die Anzeige der Pivot-Tabelle. Wenn Sie Probleme feststellen, überprüfen Sie bitte, ob der referenzierte Zellenwert genau mit einem Filterwert in der Pivot-Tabelle übereinstimmt und ob die Namen in Ihrem Code korrekt zugewiesen sind.
Immer wenn Sie den Inhalt der Zelle ändern, aktualisiert die Pivot-Tabelle ihre gefilterten Daten entsprechend.
Tipps und Fehlerbehebung: Wenn der Filterfeldwert in der Zelle nicht genau mit den verfügbaren Elementen übereinstimmt (einschließlich Groß-/Kleinschreibung und Leerzeichen), kann der Code den Filter möglicherweise nicht wie erwartet anwenden. Überprüfen Sie immer, dass Ihre Feld- und Tabellennamen im VBA-Code richtig geschrieben sind. Wenn Sie diese Einrichtung über mehrere Pivot-Tabellen hinweg verwenden möchten, können Sie den Code weiter anpassen oder mithilfe von Schleifen erweitern.
Entfesseln Sie die Magie von Excel mit Kutools AI
- Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
- VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
- Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.
Excel-Formel - Verwenden Sie Formeln (z. B. GETPIVOTDATA) in Kombination mit Slicer oder Berichtsfilterreferenzen
Obwohl Excel keine rein native formelmäßige Methode bietet, um einen Pivot-Tabellenfilter direkt mit einer Zelle zu verbinden, können Sie mit Formeln wie GETPIVOTDATA in Kombination mit Slicern oder Berichtsfiltern dynamische Berichte erstellen und relevante Werte anzeigen. Diese Lösung ist nützlich, wenn Sie Dashboards erstellen möchten, in denen Zusammenfassungswerte basierend auf einer Filterauswahl oder einer anderen Zelleneingabe sofort aktualisiert werden, was die Datenanalyse interaktiver macht.
Anwendbare Szenarien umfassen dynamische Berichtspanels, Dashboards oder vergleichende Zusammenfassungen, in denen Sie möchten, dass das angezeigte Ergebnis den Slicer-Auswahlen folgt oder Daten widerspiegelt, die mit dem Inhalt einer Zelle zusammenhängen. Der Hauptvorteil ist, dass diese Methode gut funktioniert, um aktualisierte Zusammenfassungsdaten anzuzeigen. Der eigentliche Filterstatus der Pivot-Tabelle kann jedoch nicht allein durch eine Zellenformel programmgesteuert festgelegt werden.
Beispiel: Anzeigen einer Pivot-Tabellenzusammenfassung basierend auf einem Zellenwert
Angenommen, Sie haben eine Pivot-Tabelle, die Umsätze nach Kategorie zusammenfasst (z. B. „Umsätze“, „Ausgaben“). Sie können GETPIVOTDATA verwenden, um den relevanten Wert für eine in einer Zelle angegebene Kategorie zu extrahieren.
1. Angenommen, Zelle H6 enthält die Kategorie, die Sie anzeigen möchten (z. B. „Umsätze“). Platzieren Sie die folgende Formel in Ihrer Zusammenfassungszelle (z. B. I6):
=GETPIVOTDATA("Sum of Amount",$B$4,"Category",H6)2. Nachdem Sie die Formel in I6 eingegeben haben, drücken Sie Enter. Nun wird jedes Mal, wenn Sie H6 in eine gültige Kategorie ändern (wie „Ausgaben“ oder „Umsätze“), I6 sofort aktualisiert, um die Summe für diese Kategorie gemäß der aktuellen Pivot-Tabelle anzuzeigen.
- Das erste Argument „Summe von Betrag“ sollte durch den tatsächlichen Namen des Werte-Felds in Ihrer Pivot-Tabelle ersetzt werden (z. B. "Gesamtumsätze" oder welches Label Ihre Werte verwenden). Ebenso sollte $B$4 durch die Referenz auf eine beliebige spezifische Zelle innerhalb Ihrer Pivot-Tabelle ersetzt werden – Excel erkennt diese Referenz automatisch und ordnet sie der richtigen Pivot-Tabelle zu, damit die GETPIVOTDATA-Funktion ordnungsgemäß funktioniert.
- Um Ihre genaue GETPIVOTDATA-Syntax zu erhalten, klicken Sie in eine Zelle Ihrer Pivot-Tabelle und versuchen Sie, einen Wert zu referenzieren – Excel generiert automatisch die richtige Syntax. Stellen Sie sicher, dass H6 mit einer der verfügbaren Kategorien in der Tabelle übereinstimmt, um genaue Ergebnisse zu erhalten.
Tipp: Während diese Methode den Filter innerhalb der Pivot-Tabelle selbst nicht ändert, zeigt sie effektiv Ergebnisdaten an, als wären sie durch die Zelle gefiltert, und bietet eine dynamische Anzeige, die mit Ihrer Zielzelleingabe verknüpft ist. Sie können diese Methode auch verwenden, um Diagramme, Zusammenfassungstabellen oder Dashboards zu unterstützen.
Fehlerbehebung: Wenn die Formel einen #BEZUG!- oder #WERT!-Fehler zurückgibt, überprüfen Sie, ob Ihre Zellreferenzen korrekt sind, die eingegebene Kategorie in Ihrer Pivot-Tabelle existiert und der Feld-/Summenname genau übereinstimmt.
Andere integrierte Excel-Methoden - Verbinden Sie Pivot-Tabellen-Slicer und Dashboards für interaktives Filtern
Excels Slicer- und Berichtsfilter-Tools bieten benutzerfreundliche, integrierte Optionen für interaktives Filtern ohne das Schreiben von VBA-Code. Sie können diese Methoden verwenden, um einen Dashboard-ähnlichen Effekt zu erzielen, indem Sie mehrere Pivot-Tabellen oder Anzeigen mit einem oder mehreren Slicern verbinden.
Ein gängiger Ansatz besteht darin, einen Slicer einzufügen, der mit Ihrem Pivot-Tabellenfeld verknüpft ist (z. B. „Kategorie“). Benutzer klicken einfach auf gewünschte Elemente im Slicer, und die Pivot-Tabellen(n) aktualisieren sich entsprechend. Wenn Sie mehrere Pivot-Tabellen basierend auf derselben Datenquelle haben, können Sie einen einzigen Slicer mit allen Tabellen verbinden, um synchronisierte Filterung zu ermöglichen, was Ihre Berichtsschnittstelle intuitiver und konsistenter macht.
So erstellen Sie einen Slicer und verknüpfen ihn:
- Klicken Sie in Ihre Pivot-Tabelle und gehen Sie zu PivotTable Analyse (oder Registerkarte Optionen, je nach Excel-Version) > Slicer einfügen.
- Aktivieren Sie das gewünschte Feld (z. B. Kategorie) und klicken Sie auf OK. Der Slicer erscheint auf dem Blatt und ermöglicht es Benutzern, visuell zu filtern.
- Um einen Slicer mit mehreren Pivot-Tabellen zu verknüpfen, klicken Sie mit der rechten Maustaste auf den Slicer und wählen Sie Berichtsverbindungen (oder Pivot-Tabellenverbindungen) und aktivieren Sie alle Pivot-Tabellen, die Sie synchronisieren möchten.
Dies ist besonders leistungsstark für Dashboard-Szenarien, in denen verschiedene Visualisierungen gemeinsam auf Benutzerfilter reagieren.
Vorteile: Sehr einfach zu verwenden für die meisten interaktiven Filterbedürfnisse und erfordert keine Makros oder benutzerdefinierten Code. Ideal für Dashboards oder geteilte Berichte, bei denen Einfachheit und Zuverlässigkeit entscheidend sind. Die Einschränkung ist, dass absolute Zell-zu-Filter-Automatisierung (Zell-zu-Filter-Bindung) nicht nativ unterstützt wird – direkte Wert-zu-Filter-Zuweisung erfordert VBA oder externe Tools.
Fehlerbehebung: Wenn ein Slicer sich nicht mit mehreren Pivot-Tabellen verbindet, stellen Sie sicher, dass alle Tabellen aus demselben Cache/Datenquelle erstellt wurden. Die Option Berichtsverbindungen erscheint nur, wenn die Tabellen kompatibel sind.
Zusammenfassender Vorschlag: Wenn Sie die optimale Methode zum Verknüpfen von Pivot-Tabellenfiltern mit Zellenwerten oder zum Erstellen interaktiver Dashboards auswählen, berücksichtigen Sie Ihr erforderliches Automatisierungsniveau, Excel-Versionsgrenzen und ob VBA/Makros in Ihrer Umgebung zulässig sind. Für grundlegende Anforderungen bieten Slicer und Formeln (GETPIVOTDATA) schnelle, robuste Ergebnisse. Für fortgeschrittene Automatisierung bietet die VBA-Lösung größere Kontrolle. Überprüfen Sie immer, dass Feldnamen und Filterelemente konsistent verwendet werden, um genaue Ergebnisse zu erzielen. Wenn Fehler auftreten, überprüfen Sie die Zelleingabewerte und stellen Sie sicher, dass alle Namen exakt zwischen Code, Formeln und Datensatz übereinstimmen.
Verwandte Artikel:
- Wie kombiniert man mehrere Tabellenblätter in einer Pivot-Tabelle in Excel?
- Wie erstellt man eine Pivot-Tabelle aus einer Textdatei in Excel?
- Wie filtert man eine Pivot-Tabelle basierend auf einem bestimmten Zellenwert 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