Как фильтровать сводные таблицы с помощью 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, чтобы отображать только те строки, в которых значение в столбце «Позиция» сводной таблицы равно значению в ячейке 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 для отображения только тех строк, в которых значение в столбце «Позиция» сводной таблицы равно одному из значений в диапазоне ячеек J2:J3 .

Способ 3. Удаление фильтров сводной таблицы

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

Этот конкретный макрос очистит все фильтры из сводной таблицы PivotTable1 .

Следующие примеры показывают, как использовать каждый из этих методов на практике.

Пример 1. Фильтрация сводной таблицы по значению

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

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

Для этого мы можем создать следующий макрос:

 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

Когда мы запускаем этот макрос, сводная таблица автоматически фильтруется, чтобы отображать только строки, значение которых в столбце «Позиция» — «Защита»:

Сводная таблица была отфильтрована для отображения только тех строк, значение которых в столбце «Позиция» — «Охрана».

Пример 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

Когда мы запускаем этот макрос, сводная таблица автоматически фильтруется, чтобы отображать только строки, значение которых в столбце «Позиция» — «Защита» или «Центр»:

Сводная таблица была отфильтрована так, чтобы отображались только строки, значение которых в столбце «Позиция» — «Защита» или «Центр».

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в VBA:

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