Jak filtrować tabele przestawne za pomocą języka vba (z przykładami)

Do filtrowania tabel przestawnych w programie Excel przy użyciu języka VBA możesz użyć następujących metod:

Metoda 1: Przefiltruj tabelę przestawną na podstawie wartości

 SubFilterPivotTable ()
   Dim pf As PivotField
   Dim myFilter As String
   Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
   myFilter = ActiveWorkbook.Sheets(" Sheet1 ").Range(" J2 ").Value
   pf.PivotFilters.Add2 xlCaptionEquals, , myFilter
End Sub

To konkretne makro będzie filtrować tabelę przestawną o nazwie Tabela przestawna 1 , aby wyświetlić tylko te wiersze, w których wartość w kolumnie Pozycja tabeli przestawnej jest równa wartości w komórce J2 Arkusza 1 .

Metoda 2: Filtruj tabelę przestawną na podstawie wielu wartości

 Sub FilterPivotTableMultiple()
  Dim v Ace Variant
  Dim i As Integer, j As Integer
  Dim pf As PivotField
  Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
  
  'specify range with values to filter on
  v = Range(" J2:J3 ")
  
  'clear existing filters
  pf.ClearAllFilters
  
  'apply filter to pivot table
  With pf
    For i = 1 TB pf.PivotItems.Count
      j = 1
      Do While j <= UBound(v, 1) - LBound(v, 1) + 1
         If pf.PivotItems(i).Name = v(j, 1) Then
           pf.PivotItems(pf.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pf.PivotItems(pf.PivotItems(i).Name).Visible = False
       End If
        j = j + 1
      Loop
    Next i
 End With
End Sub

To konkretne makro będzie filtrować tabelę przestawną o nazwie PivotTable1 , aby wyświetlić tylko te wiersze, w których wartość w kolumnie Pozycja tabeli przestawnej jest równa jednej z wartości z zakresu komórek J2:J3 .

Metoda 3: Usuń filtry tabeli przestawnej

 SubClearPivotTableFilter ()
   Dim pt As PivotTable
   Set pt = ActiveSheet.PivotTables(" PivotTable1 ")
   pt.ClearAllFilters
End Sub

To konkretne makro usunie wszystkie filtry z tabeli przestawnej o nazwie PivotTable1 .

Poniższe przykłady pokazują, jak zastosować każdą z tych metod w praktyce.

Przykład 1: Filtruj tabelę przestawną na podstawie wartości

Załóżmy, że utworzyliśmy tabelę przestawną na podstawie zbioru danych w Excelu, aby podsumować punkty zdobyte przez koszykarzy z różnych drużyn i na różnych pozycjach:

Załóżmy, że chcemy przefiltrować tabelę przestawną, aby wyświetlić tylko wiersze, których wartość w kolumnie Pozycja to Ochrona.

W tym celu możemy utworzyć następujące makro:

 SubFilterPivotTable ()
   Dim pf As PivotField
   Dim myFilter As String
   Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
   myFilter = ActiveWorkbook.Sheets(" Sheet1 ").Range(" J2 ").Value
   pf.PivotFilters.Add2 xlCaptionEquals, , myFilter
End Sub

Po uruchomieniu tego makra tabela przestawna jest automatycznie filtrowana, aby wyświetlić tylko wiersze, których wartość w kolumnie Pozycja to Straż:

Tabela przestawna została przefiltrowana, aby wyświetlać tylko wiersze, których wartość w kolumnie Pozycja to Ochrona.

Przykład 2: Filtruj tabelę przestawną na podstawie wielu wartości

Załóżmy, że zamiast tego chcemy przefiltrować tabelę przestawną, aby wyświetlić tylko wiersze, których wartość w kolumnie Pozycja to Ochrona lub Środek.

W tym celu możemy utworzyć następujące makro:

 Sub FilterPivotTableMultiple()
  Dim v Ace Variant
  Dim i As Integer, j As Integer
  Dim pf As PivotField
  Set pf = ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Position ")
  
  'specify range with values to filter on
  v = Range(" J2:J3 ")
  
  'clear existing filters
  pf.ClearAllFilters
  
  'apply filter to pivot table
  With pf
    For i = 1 TB pf.PivotItems.Count
      j = 1
      Do While j <= UBound(v, 1) - LBound(v, 1) + 1
         If pf.PivotItems(i).Name = v(j, 1) Then
           pf.PivotItems(pf.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pf.PivotItems(pf.PivotItems(i).Name).Visible = False
       End If
        j = j + 1
      Loop
    Next i
 End With
End Sub

Po uruchomieniu tego makra tabela przestawna jest automatycznie filtrowana, aby wyświetlić tylko wiersze, których wartość w kolumnie Pozycja to Osłona lub Środek:

Tabela przestawna została przefiltrowana, aby wyświetlać tylko wiersze, których wartość w kolumnie Pozycja to Osłona lub Środek.

Dodatkowe zasoby

Poniższe samouczki wyjaśniają, jak wykonywać inne typowe zadania w VBA:

VBA: jak odświeżyć tabele przestawne
VBA: jak usunąć zduplikowane wartości
VBA: Jak policzyć liczbę wierszy w zakresie