Используя макросы, можно быстро и автоматически управлять структурой сводных таблиц. В данном примере будут описаны процессы фильтрации и скрытия столбцов значений в сводных отчетах 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.
Безусловно после применения любых из выше описанных макросов, сводная таблица полностью сохраняет свою функциональность. Все также доступны все ее элементы управления и инструменты для настройки полей, фильтров, а также других опций.
Я пытаюсь автоматизировать ежедневный отчет, и поэтому я хочу создать две кнопки, которые изменяют фильтры из трех сводных таблиц. В деталях кнопки должны изменить день, который показан. Первые фильтры вчера второй — кнопка сброса очищают все фильтры и показывают все дни. "Resest" -Button работает, но "вчера" -Button нет.
На данный момент макрос выглядит так:
Я также попробовал PivotFilters.Add _, Type:=xlDateYesterday но это тоже не работает.
Попробуйте приведенный ниже код, он должен работать, если только ваша "дата" не форматируется между источником данных Pivot и Range("B1") .
Примечание: старайтесь избегать использования ActiveSheet , вместо этого используйте ссылочные объекты. В нижеследующем случае замените Worksheets("Sheet1") на ваше имя листа.
March 2019
46 раз
У меня есть набор данных, где я хочу, чтобы нажать на ячейку в столбце А и фильтрует сводную таблицу на другом листе. У меня есть этот код до сих пор:
Он делает то, что я хочу, но только для одной ячейки. Я хочу быть в состоянии нажать на любую ячейку в столбце А и имеют значение ячейки фильтра сводной таблицы. Как я указать диапазон, а не только одну ячейку?