Продолжая тему поиска и выбора данных на основе заданного критерия рассмотрим пример решения позволяющий определить месяц по дате и установить фильтр на основе заданного критерия.
Рассмотренный подход будет полезен, когда требуется выполнить определенные действия в ответ на изменения в определенных ячейках листа, что обеспечивает автоматизацию и управление данными в Excel.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Постановка задачи и пример исходных данных
В качестве демонстрационного примера рассмотрим задачу в которой требуется:
- автоматически, при вводе даты определить соответствующий месяц
- установить фильтр данных при вводе в целевую ячейку (В2)
Исходные данные представлены в виде таблицы и определены соответствующие именованные диапазоны:
▶️ Как определить месяц по дате
Для автоматического формирования месяца на основании представленной даты назначим макрос на событие Worksheet_SelectionChange, которое будет отслеживать изменения выделенной ячейки и выполнять соответствующие действия:
🔘 Используемые переменные:
- rngDateOfBirth: диапазон, содержащий даты рождения.
- rngMonth: диапазон, куда будут записываться месяцы.
- cell: итерируемая переменная для перебора ячеек в диапазоне дат рождения.
- monthValue: переменная для хранения числового значения месяца.
🔘 Параметр Target: представляет собой диапазон ячеек, которые были выделены пользователем в результате изменения.
🔘 Обработка события:
- проверяем, находится ли измененная ячейка в диапазоне определяющем дату рождения (rngDateOfBirth)
- если изменения произошли в назначенном диапазоне, обработка событий временно отключается (Application.EnableEvents = False), чтобы избежать бесконечного цикла событий
- для каждой ячейки, содержащей дату рождения, определяем месяц (Month(cell.Value))
- полученное числовое значение месяца проставляется в соответствующую ячейку столбца "Месяц" (rngMonth), с учетом соответствия номера строки (cell.row - 4, так как диапазон начинается с 5 строки)
- после обработки событий снова включается (Application.EnableEvents = True) обработка событий.
➡️ Результат:
Когда пользователь выбирает (выделяет) ячейку в столбце именованного диапазона определяющего дату рождения автоматически проставляется соответствующий месяц.
▶️ Как установить фильтр на основе заданного критерия
Если данные отсортированы, то можно воспользоваться рассмотренным ранее примером изменив тип данных переменной:
Но, в том случае, если вы не уверены в "правильном" порядке данных, следует воспользоваться следующим подходом:
Представленный код код обрабатывает ситуацию, когда данные не отсортированы, проходя по каждой ячейке в столбце "Месяц" и ищет совпадения с критерием.
⚙️ Как это работает:
- Определение переменных: вначале определяются переменные, включая диапазон данных ("colA"), диапазон найденных ячеек ("found") и счетчик количества найденных совпадений ("cnt").
- Выключение обновления экрана: это помогает ускорить выполнение макроса, предотвращая мерцание экрана при изменении данных.
- Проверка критерия: если критерий не пустой, выполняем назначенные действия
- Включение обновления экрана: после завершения операций восстанавливается обновление экрана для пользователя.
📝 Примечание автора:
Напомним, что макрос следует назначить на событие Worksheet_Change, которое срабатывает при ручном взаимодействии с данными, включая ввод новых данных, удаление или изменение существующих.
🔘 Параметр Target: представляет собой диапазон ячеек, который был изменен.
🔘 Условие проверки:
- If Target.CountLarge = 1: проверяет, была ли изменена только одна ячейка. Это делается для оптимизации, чтобы избежать лишних действий при массовом изменении данных.
- If Target.Address = "$B$2": проверяет, была ли изменена конкретная ячейка B2. Это делается для ограничения действий только на указанной ячейке.
🔘 Действие:
Если условия выполняются, то вызывается процедура showIf, передавая ей значение ячейки B2 в качестве аргумента. Это позволяет установить фильтр в зависимости от значения ячейки B2.
➡️ Результат
- если значение ячейки соответствует критерию, оно добавляется к диапазону "found", а счетчик увеличивается. Строки, не соответствующие критерию, скрываются:
- если совпадений нет, выводится сообщение об ошибке:
🔷 Подводя итог
Рассмотренное решение позволяет автоматически определять месяц по дате рождения и устанавливать фильтр в соответствии с заданным критерием, что безусловно обеспечивает удобство и эффективность работы с данными в Excel. В зависимости от условий конкретной задачи может быть доработано и изменено.
🔶 Узнать о других способах и приемах автоматизации работы в Excel можно найти в наших предыдущих обзорах 🔽
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽
