В этом руководстве будет показано, как использовать фильтр сводной таблицы в VBA.
Сводные таблицы - это исключительно мощный инструмент для работы с данными в Excel. Сводные таблицы позволяют нам анализировать и интерпретировать большие объемы данных путем группировки и суммирования полей и строк. Мы можем применять фильтры к нашим сводным таблицам, чтобы мы могли быстро увидеть релевантные нам данные.
Во-первых, нам нужно создать сводную таблицу для наших данных. (Щелкните здесь, чтобы просмотреть руководство по сводной таблице VBA).
Создание фильтра на основе значения ячейки
Вы можете фильтровать сводную таблицу с помощью VBA на основе данных, содержащихся в значении ячейки - мы можем фильтровать либо по полю страницы, либо по полю строки (например, по полю «Поставщик» выше или по полю «Операция», которое находится в столбце «Ярлыки строк»). ).
В пустой ячейке справа от сводной таблицы создайте ячейку для хранения фильтра, а затем введите данные в ячейку, по которой вы хотите отфильтровать сводную таблицу.
Создайте следующий макрос VBA:
| 1234567 | Sub FilterPageValue ()Dim pvFld As PivotFieldDim strFilter как строкаУстановите pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Supplier")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). ValuepvFld.CurrentPage = strFilterКонец подписки |
Запустите макрос, чтобы применить фильтр.
Чтобы очистить фильтр, создайте следующий макрос:
| 12345 | Sub ClearFilter ()Dim pTbl As PivotTableУстановите pTbl = ActiveSheet.PivotTables ("PivotTable1")pTbl.ClearAllFiltersКонец подписки |
После этого фильтр будет удален.
Затем мы можем изменить критерии фильтрации, чтобы фильтровать по строке в сводной таблице, а не по текущей странице.
После ввода следующего макроса мы сможем выполнить фильтрацию по строке (обратите внимание, что поле Pivot для фильтрации теперь является оператором, а не поставщиком).
| 1234567 | Sub FilterRowValue ()Dim pvFld As PivotFieldDim strFilter как строкаУстановите pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")strFilter = ActiveWorkbook.Sheets ("Sheet1"). Range ("M4"). ValuepvFld.PivotFilters.Add2 xlCaptionEquals`` strFilterКонец подписки |
Запустите макрос, чтобы применить фильтр.
Использование нескольких критериев в сводном фильтре
Мы можем добавить к фильтру значений строки выше, добавив дополнительные критерии.
Однако, поскольку стандартный фильтр скрывает строки, которые не требуются, нам нужно перебрать критерии и показать те, которые требуются, и скрыть те, которые не требуются. Это делается путем создания переменной Array и использования пары циклов в коде.
| 1234567891011121314151617181920212223 | Sub FilterMultipleRowItems ()Dim vArray как вариантDim i как целое число, j как целое числоDim pvFld As PivotFieldУстановите pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Oper")vArray = Диапазон ("M4: M5")pvFld.ClearAllFiltersС pvFldДля i = 1 в pvFld.PivotItems.Countj = 1Выполнить Пока j <= UBound (vArray, 1) - LBound (vArray, 1) + 1Если pvFld.PivotItems (i) .Name = vArray (j, 1) ТогдаpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = TrueВыход СделатьЕщеpvFld.PivotItems (pvFld.PivotItems (i) .Name) .Visible = FalseКонец, еслиj = j + 1ПетляДалее яКонец сКонец подписки |
Создание фильтра на основе переменной
Мы можем использовать те же концепции для создания фильтров на основе переменных в нашем коде, а не значения в ячейке. На этот раз переменная фильтра (strFilter) заполняется в самом коде (например: жестко запрограммирована в макрос).
| 1234567 | Sub FilterTextValue ()Dim pvFld As PivotFieldDim strFilter как строкаУстановите pvFld = ActiveSheet.PivotTables ("PivotTable1"). PivotFields ("Supplier")strFilter = "THOMAS S"pvFld.CurrentPage = strFilterКонец подписки |
