Всем привет! 👋
А вы знали, что зависимый выпадающий список - это не только удобный инструмент поиска на основе определенного критерия, но и отличная возможность установить автоматическую фильтрацию данных таблицы.
Сегодня в обзоре мы научим вас как реализовать данную возможность.
📢 Скачать исходник с примером кода вы можете в конце статьи 🔽
Как вы уже могли догадаться пользоваться стандартным инструментом "Фильтр" ⬇️ расположенным на вкладке "Редактирование" не будем 😊
А дочитав статью до конца, полагаем вы оцените все преимущества описанного способа и согласитесь, что прием использования метода AutoFilter VBA для целей фильтрации данных гораздо удобнее, чем выбор вручную 😉.
▶️ Исходные данные
Для целей данного примера рассмотрим следующий источник данных:
С заданным именованным диапазоном:
Задача: при выборе зависимых критериев (ячейки В2 и С2) установить фильтр в таблице и выполнить фильтрацию в соответствии с критерием.
▶️ Создание выпадающего списка
Первым шагом сформируем выпадающий список основе значений из четвёртой строки применив событие Worksheet.SelectionChange:
➡️ Теперь, при клике на ячейку B2, будет создан выпадающий список в соответствии с непустыми значениями из четвёртой строки:
▶️ Создание зависимого выпадающего списка
Для создания зависимого выпадающего списка воспользуемся событием Worksheet_Change:
➡️ Если в B2 выбирается значение, то в C2 будет создан выпадающий список со значениями, начиная со строки 5 и до последней заполненной ячейки в столбце, соответствующем выбранному значению из B2:
➡️ Если выбор в B2 изменяется на другое значение, то список в C2 будет соответственно обновлен:
В том случае если диапазон зависимых данных содержит повторяющиеся значения, можно использовать индексный доступ к элементам коллекции.
Вот как это можно сделать:
➡️ Этот код будет формировать список уникальных значений из столбца данных, соответствующего выбранному значению в ячейке B2:
▶️ Фильтрация данных средствами VBA
Чтобы перейти непосредственно к фильтрации данных поясним механизм работы Метода AutoFilter.
Чтобы вся строка получила раскрывающийся список фильтров следует задать нужный диапазон (ячейку) на листе применить Метод AutoFilter:
➡️ После запуска макроса теперь можно фильтровать данные, аналогично тому, как бы это делали, применив фильтр вручную:
Но, для условия задачи это полдела. И это далеко не автоматизация.
Чтобы установить автофильтр по определенному критерию следует определить:
- нужный диапазон - Range;
- столбец по которому будет установлен поиск - Field;
- критерий поиска - Criteria1
Теперь макрос будет имеет вид, например:
где
- Range("A4:C13") - диапазон таблицы
- Field:=2 - номер столбца
- Criteria1:="Текст2-4" - критерий, по которому установлен фильтр
Результат, полагаем, вас порадует:
Но и это еще не все...
Согласитесь, задавать непосредственно в коде критерии поиска, и постоянно их менять - это тоже не автоматизация.
А потому, применим макрос, в котором будем автоматически определять и номер столбца поиска и критерий:
🔔 Обратите внимание!
- номер столбца записан в переменную;
- в качестве критерия - считываем значение ячейки выбранного из зависимого выпадающего списка (Range("$C$2"));
- а диапазон данных таблицы задан ранее определенным именованным диапазоном (Range("Тест"))
В результате, макрос отфильтрует данные в указанном столбце на листе в зависимости от выбранного значения в ячейке B2.
Если в ячейке C2 есть значение, то данные будут отфильтрованы по выбранному значению из B2:
Если ячейка C2 пуста, то фильтр будет удален, и все данные будут показаны:
В том случае если имеется дополнительный критерий, например:
Для установки фильтра по двум (и более столбцам) можно использовать оператор xlGreaterEqual вместе с значением из ячейки:
➡️ Это условие установит фильтр так, чтобы отображались только те строки, в которых значение в четвертом столбце больше или равно значению из ячейки $D$2:
Что равносильно тому, если бы вы задавали фильтр вручную выбрав "числовые фильтры" ->> "больше или равно":
▶️ Автоматическая установка фильтра
Чтобы фильтр в таблице был установлен автоматически добавим вызов макроса VBA_Filter() внутри Private Sub Worksheet_Change(ByVal Target As Range) после обработки изменения значения в ячейке B2:
➡️ Теперь, после изменения значения в B2, макрос VBA_Filter() будет автоматически вызван для применения фильтра на основе выбранных значений:
▶️ Автоматическое снятие фильтра
Как было сказано выше, чтобы снять фильтр достаточно сделать ячейку В2 пустой.
Но, если в ручную ничего "очищать" не хочется, ускорить данный процесс поможет обработка события клика на ячейку в макросе.
Добавьте к событию Private Sub Worksheet_Change(ByVal Target As Range) строку:
➡️ Это позволит очистить критерии выбора и вернуть таблицу к первоначальному виду:
Чтобы вовсе убрать раскрывающийся список фильтров со всех столбцов следует добавить строку:
➡️ Это позволит не только очистить критерии поиска, но и убрать убрать раскрывающийся список фильтров, если пользователь кликает на пустую ячейку:
При повторном клике на пустую ячейку раскрывающийся список фильтров будет показан при отсутствии фильтров на листе и скрыт при их наличии.
📍 Рекомендуемые статьи 🔽