In diesem Tutorial wird gezeigt, wie Sie den Pivot-Tabellenfilter in VBA verwenden.
Pivot-Tabellen sind ein außergewöhnlich leistungsfähiges Datenwerkzeug von Excel. Pivot-Tabellen ermöglichen es uns, große Datenmengen zu analysieren und zu interpretieren, indem wir Felder und Zeilen gruppieren und zusammenfassen. Wir können Filter auf unsere Pivot-Tabellen anwenden, damit wir schnell die für uns relevanten Daten sehen können.
Zuerst müssen wir eine Pivot-Tabelle für unsere Daten erstellen. (Klicken Sie hier, um unseren VBA-Pivot-Tabellenleitfaden anzuzeigen).
Erstellen eines Filters basierend auf einem Zellenwert
Sie können in einer Pivot-Tabelle mithilfe von VBA basierend auf Daten filtern, die in einem Zellenwert enthalten sind - wir können entweder nach dem Seitenfeld oder einem Zeilenfeld filtern (zum Beispiel nach dem Feld Lieferant oben oder dem Feld Oper, das sich in der Spalte Zeilenbeschriftungen befindet.) ).
Erstellen Sie in einer leeren Zelle rechts neben der Pivot-Tabelle eine Zelle, um den Filter aufzunehmen, und geben Sie dann die Daten in die Zelle ein, nach der Sie die Pivot-Tabelle filtern möchten.
Erstellen Sie das folgende VBA-Makro:
| 1234567 | UnterfilterSeitenwert()Dim pvFld As PivotFieldDim strFilter als StringSet pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Lieferant")strFilter = ActiveWorkbook.Sheets("Sheet1").Range("M4").ValuepvFld.CurrentPage = strFilterEnd Sub |
Führen Sie das Makro aus, um den Filter anzuwenden.
Um den Filter zu löschen, erstellen Sie das folgende Makro:
| 12345 | Unter ClearFilter()pTbl als PivotTable dimmenSet pTbl = ActiveSheet.PivotTables("PivotTable1")pTbl.ClearAllFiltersEnd Sub |
Der Filter wird dann entfernt.
Wir können dann die Filterkriterien ändern, um nach einer Zeile in der Pivot-Tabelle statt nach der aktuellen Seite zu filtern.
Die Eingabe des folgenden Makros ermöglicht es uns dann, nach der Zeile zu filtern (beachten Sie, dass das Pivot-Feld, nach dem gefiltert werden soll, jetzt der Oper und nicht der Lieferant ist).
| 1234567 | UnterfilterRowValue()Dim pvFld As PivotFieldDim strFilter als StringSet pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")strFilter = ActiveWorkbook.Sheets("Sheet1").Range("M4").ValuepvFld.PivotFilters.Add2 xlCaptionEquals, , strFilterEnd Sub |
Führen Sie das Makro aus, um den Filter anzuwenden.
Verwenden mehrerer Kriterien in einem Pivot-Filter
Wir können den Zeilenwertfilter oben hinzufügen, indem wir zusätzliche Kriterien hinzufügen.
Da der Standardfilter jedoch die nicht benötigten Zeilen ausblendet, müssen wir die Kriterien durchlaufen und die angeforderten anzeigen, während die nicht benötigten Zeilen ausgeblendet werden. Dies geschieht durch Erstellen einer Array-Variablen und Verwenden einiger Schleifen im Code.
| 1234567891011121314151617181920212223 | UnterfilterMultipleRowItems()Dim vArray als VarianteDim i As Integer, j As IntegerDim pvFld As PivotFieldSet pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")vArray = Bereich("M4:M5")pvFld.ClearAllFiltersMit pvFldFür i = 1 Zu pvFld.PivotItems.Countj = 1Do While j <= UBound(vArray, 1) - LBound(vArray, 1) + 1Wenn pvFld.PivotItems(i).Name = vArray(j, 1) ThenpvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = TrueBeendenAnderspvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = FalseEnde Wennj = j + 1SchleifeAls nächstesEnde mitEnd Sub |
Erstellen eines Filters basierend auf einer Variablen
Wir können die gleichen Konzepte verwenden, um Filter basierend auf Variablen in unserem Code anstelle des Werts in einer Zelle zu erstellen. Diesmal wird die Filtervariable (strFilter) in den Code selbst gefüllt (zB: Hartcodiert in das Makro).
| 1234567 | UnterfilterTextValue()Dim pvFld As PivotFieldDim strFilter als StringSet pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Lieferant")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterEnd Sub |
