Як фільтрувати зведені таблиці за допомогою vba (з прикладами)

Щоб фільтрувати зведені таблиці в Excel за допомогою VBA, можна використовувати такі методи:

Спосіб 1. Фільтруйте зведену таблицю на основі значення

 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

Цей конкретний макрос фільтруватиме зведену таблицю під назвою PivotTable1 , щоб відображати лише ті рядки, де значення в стовпці Position зведеної таблиці дорівнює значенню в клітинці J2 аркуша Sheet1 .

Спосіб 2. Відфільтруйте зведену таблицю на основі кількох значень

 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

Цей конкретний макрос фільтруватиме зведену таблицю під назвою PivotTable1 , щоб відображати лише ті рядки, де значення в стовпці Position зведеної таблиці дорівнює одному зі значень у діапазоні клітинок J2:J3 .

Спосіб 3. Видалення фільтрів зведеної таблиці

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

Цей конкретний макрос очистить усі фільтри зі зведеної таблиці під назвою PivotTable1 .

Наступні приклади показують, як використовувати кожен із цих методів на практиці.

Приклад 1: фільтрування зведеної таблиці на основі значення

Припустімо, ми створили зведену таблицю з набору даних у Excel, щоб підсумувати очки, набрані баскетболістами з різних команд і позицій:

Припустімо, ми хочемо відфільтрувати зведену таблицю, щоб відображати лише рядки, значення яких у стовпці Position дорівнює Guard.

Для цього ми можемо створити такий макрос:

 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

Коли ми запускаємо цей макрос, зведена таблиця автоматично фільтрується, щоб відображати лише рядки, значення яких у стовпці Position дорівнює Guard:

Зведену таблицю було відфільтровано, щоб відображати лише рядки, значення яких у стовпці «Позиція» — Guard.

Приклад 2: фільтрування зведеної таблиці на основі кількох значень

Припустімо, замість цього ми хочемо відфільтрувати зведену таблицю, щоб відобразити лише рядки, значення яких у стовпці Position є Guard або Center.

Для цього ми можемо створити такий макрос:

 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

Коли ми запускаємо цей макрос, зведена таблиця автоматично фільтрується, щоб відображати лише рядки, значення яких у стовпці Position є Guard або Center:

Зведену таблицю було відфільтровано, щоб відображати лише рядки, значення яких у стовпці «Позиція» — Guard або Center.

Додаткові ресурси

У наступних посібниках пояснюється, як виконувати інші типові завдання у VBA:

VBA: як оновити зведені таблиці
VBA: як видалити повторювані значення
VBA: як підрахувати кількість рядків у діапазоні