VBA – Filter in Pivot-Tabelle

In diesem Tutorial zeigen wir Ihnen, wie Sie den Pivot-Tabellenfilter in VBA verwenden können.

Pivot-Tabellen sind ein äußerst leistungsfähiges Datenwerkzeug in Excel. Mit Pivot-Tabellen können wir große Datenmengen analysieren und interpretieren, indem wir Felder und Zeilen gruppieren und zusammenfassen. Wir können Filter auf unsere Pivot-Tabellen anwenden, um die für uns relevanten Daten schnell sehen zu können.

Als Erstes müssen wir eine Pivot-Tabelle für unsere Daten erstellen. (Klicken Sie hier für unseren VBA-Leitfaden zu Pivot-Tabellen)

Einen Filter auf Grundlage eines Zellenwertes erstellen

In diesem Beispiel werden wir eine Pivot-Tabelle auf Grundlage eines Zellenwertes filtern.

Erstellen Sie in einer leeren Zelle rechts von der Pivot-Tabelle eine Zelle, die den Filter enthält und geben Sie dann die Daten, wonach Sie die Pivot-Tabelle filtern möchten, in diese Zelle ein.

Erstellen Sie das folgende VBA-Makro:

Sub SeitenWertFiltern()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTabelle1").PivotFields("Lieferant")
   strFilter = ActiveWorkbook.Sheets("Tabelle1").Range("M4").Value
   pvFld.CurrentPage = strFilter
End Sub

Führen Sie das Makro aus, um den Filter anzuwenden.

Um den Filter zu löschen, erstellen Sie das folgende Makro:

Sub FilterLoeschen()
   Dim pTbl As PivotTable
   Set pTbl = ActiveSheet.PivotTables("PivotTabelle1")
   pTbl.ClearAllFilters
End Sub

Der Filter wird dann entfernt.

Wir können dann die Filterkriterien so ändern, dass wir nach einer Zeile in der Pivot-Tabelle und nicht nach der aktuellen Seite filtern.

Durch Eingabe des folgenden Makros können wir dann nach Zeile filtern (beachten Sie, dass das zu filternde Pivot-Feld jetzt der Betreiber und nicht mehr der Lieferant ist).

Sub ZeilenWertFiltern()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTabelle1").PivotFields("Betreiber")
   strFilter = ActiveWorkbook.Sheets("Tabelle1").Range("M4").Value
   pvFld.PivotFilters.Add2 xlCaptionEquals, , strFilter
End Sub

Führen Sie das Makro aus, um den Filter anzuwenden.

 

Mehrere Kriterien in einem Pivot-Filter verwenden

Wir können den obigen Zeilenwertfilter durch Hinzufügen zusätzlicher Kriterien ergänzen.

Da der Standardfilter jedoch die nicht benötigten Zeilen ausblendet, müssen wir die Kriterien in einer Schleife durchlaufen und die benötigten anzeigen, während wir die nicht benötigten ausblenden. Dazu wird eine Array-Variable erstellt und eine Reihe von Schleifen im Code verwendet.

Sub MehrereZeilenElementeFiltern()
  Dim vArray As Variant
  Dim i As Integer, j As Integer
  Dim pvFld As PivotField
  Set pvFld = ActiveSheet.PivotTables("PivotTabelle1").PivotFields("Betreiber")
  vArray = Range("M4:M5")
  pvFld.ClearAllFilters

  With pvFld
    For i = 1 To pvFld.PivotItems.Count
      j = 1
       Do While j <= UBound(vArray, 1) - LBound(vArray, 1) + 1
         If pvFld.PivotItems(i).Name = vArray(j, 1) Then
           pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = False
        End If
        j = j + 1
      Loop
    Next i
  End With
End Sub

Einen Filter auf Grundlage einer Variablen erstellen

Wir können dieselben Konzepte verwenden, um Filter, die auf Variablen in unserem Code und nicht auf dem Wert in einer Zelle basieren, zu erstellen. Diesmal wird die Filtervariable (strFilter) in den Code selbst eingefügt (z. B. fest in das Makro codiert).

Sub TextWertFiltern()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTabelle1").PivotFields("Lieferant")
   strFilter = "THOMAS S"
   pvFld.CurrentPage = strFilter
End Sub