Cara memfilter pivottable menggunakan vba (dengan contoh)

Anda dapat menggunakan metode berikut untuk memfilter tabel pivot di Excel menggunakan VBA:

Metode 1: Filter tabel pivot berdasarkan nilai

 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

Makro khusus ini akan memfilter tabel pivot yang disebut PivotTable1 untuk memperlihatkan hanya baris-baris yang nilai di kolom Posisi tabel pivot sama dengan nilai di sel J2 pada Sheet1 .

Metode 2: Filter PivotTable berdasarkan beberapa nilai

 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

Makro khusus ini akan memfilter tabel pivot yang disebut PivotTable1 untuk hanya menampilkan baris-baris yang nilai dalam kolom Posisi tabel pivot sama dengan salah satu nilai dalam rentang sel J2:J3 .

Metode 3: Hapus Filter PivotTable

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

Makro khusus ini akan menghapus semua filter dari tabel pivot yang disebut PivotTable1 .

Contoh berikut menunjukkan cara menggunakan masing-masing metode ini dalam praktik.

Contoh 1: Filter tabel pivot berdasarkan nilai

Katakanlah kita membuat tabel pivot dari kumpulan data di Excel untuk merangkum poin yang dicetak oleh pemain bola basket dari tim dan posisi berbeda:

Katakanlah kita ingin memfilter tabel pivot untuk hanya menampilkan baris yang nilainya di kolom Posisi adalah Penjaga.

Kita dapat membuat makro berikut untuk melakukan ini:

 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

Saat kita menjalankan makro ini, tabel pivot secara otomatis difilter untuk memperlihatkan hanya baris yang nilainya di kolom Posisi adalah Penjaga:

Tabel pivot telah difilter untuk hanya menampilkan baris yang nilainya di kolom Posisi adalah Penjaga.

Contoh 2: Filter tabel pivot berdasarkan beberapa nilai

Misalkan kita ingin memfilter tabel pivot agar hanya menampilkan baris yang nilainya di kolom Posisi adalah Penjaga atau Tengah.

Kita dapat membuat makro berikut untuk melakukan ini:

 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

Saat kita menjalankan makro ini, tabel pivot secara otomatis difilter untuk memperlihatkan hanya baris yang nilainya di kolom Posisi adalah Penjaga atau Tengah:

Tabel pivot telah difilter untuk memperlihatkan hanya baris yang nilainya di kolom Posisi adalah Penjaga atau Tengah.

Sumber daya tambahan

Tutorial berikut menjelaskan cara melakukan tugas umum lainnya di VBA:

VBA: cara menyegarkan tabel pivot
VBA: cara menghapus nilai duplikat
VBA: Cara menghitung jumlah baris dalam jangkauan