Мини-курс "5 чудес Excel"
Интервальная фильтрация в области фильтра сводной таблицы
Интервальная фильтрация в области фильтра - удивительно, но эта простая и нужная настройка отсутствует в сводных таблицах. Поэтому поделюсь вариантом решения, который получился у меня и будет по силам и Вам.
Скачайте файл с решением у меня на сайте (это бесплатно и без регистрации). При открытии нажмите «Включить макросы», иначе не получится протестировать. Файл можно сохранить под любым удобным именем, важно оставить ему расширение .xlsm
Тестируем мой файл
На листе со сводной таблицей в жёлтых ячейках формулы МИН и МАКС рассчитывают минимальное и максимальное значение кода (артикула и т.п.) – такой столбец есть в исходных данных и содержит данные числового типа.
В розовых ячейках B1 и B2 впишите начальное и конечное значение диапазона номеров для фильтрации и нажмите кнопку «Фильтровать», немножко подождите и макрос всё сделает.
Вы можете работать в моём файле, разместив на листе с исходными данными свою таблицу и просто обновляя сводную (обратите внимание, что исходник оформлен как умная таблица через Главная – Форматировать как таблицу).
Только нужно будет обязательно скорректировать имя поля фильтрации – ниже я пишу, как заглянуть в программный код и в том числе забрать мой макрос в свой файл.
В розовых ячейках B1 и B2 впишите начальное и конечное значение диапазона номеров для фильтрации и нажмите кнопку «Фильтровать», немножко подождите и макрос всё сделает.
Вы можете работать в моём файле, разместив на листе с исходными данными свою таблицу и просто обновляя сводную (обратите внимание, что исходник оформлен как умная таблица через Главная – Форматировать как таблицу).
Только нужно будет обязательно скорректировать имя поля фильтрации – ниже я пишу, как заглянуть в программный код и в том числе забрать мой макрос в свой файл.
Корректируем код макроса
Нажмите ALT+F11 и откроется окно редактора VBA. Слева щёлкните дважды по модулю Module1 и справа увидите текст макроса.
В самом начале кода внутри кавычек проверьте и впишите имя своей сводной таблицы и название поля фильтрации. В моём примере это поле «Код».
Чтобы забрать макрос в другую книгу, откройте её и в редакторе VBA просто перетащите мышкой модуль с макросом Module1 на название своей книги. Как только её название будет подсвечено синим, отпустите мышь. Осталось сохранить свой файл с расширением .xlsm и можно пользоваться.
Код макроса
Sub MyPivotFilter()
'впишите имя своей сводной таблицы (см. на вкладке Анализ слева)
pv_name = "Сводная таблица1"
'впишите своё название фильтруемого поля
pv_filter = "Код"
With Excel.Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = Excel.xlCalculationManual
End With
'сброс всех фильтров в сводной
ActiveSheet.PivotTables(pv_name).ClearAllFilters
'цикл фильтрации данных по заданному интервалу
For i = Range("A1") To Range("A2")
If i < Range("B1") Or i > Range("B2") Then
ActiveSheet.PivotTables(pv_name).PivotFields(pv_filter).PivotItems(CStr(i)).Visible = False
End If
Next
With Excel.Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = Excel.xlCalculationAutomatic
End With
End Sub
'впишите имя своей сводной таблицы (см. на вкладке Анализ слева)
pv_name = "Сводная таблица1"
'впишите своё название фильтруемого поля
pv_filter = "Код"
With Excel.Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = Excel.xlCalculationManual
End With
'сброс всех фильтров в сводной
ActiveSheet.PivotTables(pv_name).ClearAllFilters
'цикл фильтрации данных по заданному интервалу
For i = Range("A1") To Range("A2")
If i < Range("B1") Or i > Range("B2") Then
ActiveSheet.PivotTables(pv_name).PivotFields(pv_filter).PivotItems(CStr(i)).Visible = False
End If
Next
With Excel.Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = Excel.xlCalculationAutomatic
End With
End Sub
Уровень сложности макроса - учебный. такие, и даже более сложные коды, мы пишем с моими учениками на курсе "Макросы на VBA с нуля". Если функции ВПР и ЕСЛИ Вам хорошо знакомы и понятны, то и макросы будут по силам.