Макросы для фильтра сводной таблицы в Excel

Используя макросы, можно быстро и автоматически управлять структурой сводных таблиц. В данном примере будут описаны процессы фильтрации и скрытия столбцов значений в сводных отчетах Excel.

Как сделать фильтр в сводной таблице макросом

Чтобы получить данные для одного из 4-х магазинов фирмы, достаточно лишь открыть выпадающий список со всеми магазинами и выбрать один из них. Пример результата этих действий изображено на рисунке:

Изменение настроек, которые доступны в интерактивных инструментах сводной таблице доступны так же на уровне программирования макросов из редактора VBA. Выбор магазина, который является элементом поля СТРАИЦЫ реализуется с помощью свойства CurrentPage.

Просто как параметр для этого свойства следует указать название поля. Например, напишем простой код макроса, который сам выберет «Магазин3» как критерий для фильтрования данных по оборотам в сводной таблице:

Sub Magazin3()
ActiveSheet.PivotTables("ТаблицаМ").PivotFields("Магазины").CurrentPage = "Магазин 3"
End Sub


Чтобы создать такой макрос сначала откройте редактор VisualBasic (ALT+F11), а потом создайте новый модуль в редакторе: «Insert»-«Module» и введете в него выше указанный VBA-код:

Результат действия этого простого макроса такой же как выше рисунке, но без использования пользователем интерактивных инструментов сводной таблицы.

В методе PivotTables указан аргумент «ТаблицаМ» – это всего лишь ссылка на внутренне имя, которое было присвоено для сводной таблицы еще на этапе ее создания. Читайте пример создания где он детально описан: Макрос для создания сводной таблицы в Excel. Далее рассмотрим, как можно скрывать часть данных из области видимости значений.



Как скрыть столбец в сводном отчете макросом

Во многих ситуациях необходимо скрытие части данных, чтобы сосредоточить внимание аналитика на конкретно выбранной задачи или поставленной цели. Естественно сводная таблица обладает такой функцией без потери данных, а только лишь их визуальным скрытием. Чтобы скрыть в таблице данные за 2017 год с помощью макроса, введите в модуль следующий код:

Sub Hidden2017()
ActiveSheet.PivotTables("ТаблицаМ").PivotFields("Год").PivotItems("2017").Visible = False
End Sub


Пример VBA-макроса в действии:

Чтобы снова включить (или сделать второй режим для переключателя скрыть/показать) в таблицу данные за 2017 год достаточно лишь в этом коде поменять параметр Fale на True.

Читайте также: Макрос позволяет рассчитать процент в сводной таблице Excel.

Безусловно после применения любых из выше описанных макросов, сводная таблица полностью сохраняет свою функциональность. Все также доступны все ее элементы управления и инструменты для настройки полей, фильтров, а также других опций.