Выпадающие списки – неотъемлемая часть работы с данными в Excel.
Благодаря широкому выбору подходов пользователю доступно множество способов к созданию таких списков в зависимости от уникальных требований задачи.
Один из примеров, иллюстрирующий простой и эффективный метод создания зависимого выпадающего списка при помощи VBA рассмотрен данном обзоре.
📢 Файл с примером размещен в конце статьи 🔽
Рассмотренный пример будет полезен в случае если требуется упорядочить параметры выбора зависимых данных.
Для целей данного рассмотрим ситуацию когда требуется настроить зависимый выпадающий список в зависимости от выбранной категории товара.
▶️ Создаем выпадающий список
В качестве категорий товара установим следующие:
- Овощи
- Фрукты
- Ягоды
Выпадающий список для данных категорий определим по столбцу В.
Для решения поставленной задачи безусловно можно воспользоваться применением проверки данных к ячейкам:
Но создать аналогичный выпадающий список можно и при помощи VBA:
⚙️ Краткое описание:
- устанавливаем переменные;
- определяем перечисления, которые будут использоваться в выпадающем списке;
- проходим по каждой ячейке в столбце B, начиная с B2. Если ячейка столбца А пустая или не внесена ни одна запись, то список не создается.
- перед созданием нового выпадающего списка удаляем существующие правила проверки данных;
- добавляем новые настроек валидации с типом "список" и перечисленными значениями. Тип проверки устанавливается равным списку (xlValidateList). А параметр AlertStyle имеет значение xlValidAlertStop, которое запрещает пользователю вводить неверные данные в любую из ячеек столбца B.
Примечание автора: параметр Operator: =xlBetween, в данном случае не используется, т.к тип равен xlValidateList.
Теперь при при наличии данных с столбце А в столбце B будет создан выпадающий список содержащий элементы: "Овощи", "Фрукты" и "Ягоды".
▶️ Создаем зависимый выпадающий список
Предварительно для установленных категорий создадим три справочника и определим для каждого свой именованный диапазон:
Чтобы создать зависимый выпадающий список применим макрос:
⚙️ Краткое описание:
- перебираем каждую ячейку в столбце B активного листа и считываем ее содержимое.
- на основе содержимого ячейки выбираем, какую формулу использовать для проверки данных. Проверка выполняется в ячейке на один столбец правее (смещение (0, 1)) текущей ячейки.
- перед созданием выпадающего списка удаляем из ячейки все существующие проверки данных. Если ни одна из категорий не совпадает (Овощи, Фрукты или Ягоды), список в ячейку добавляться не будет.
▶️ Управление выбором
Чтобы рассмотренные выше макросы выполнялись автоматически следует в модуле листа разместить код:
Напомним, что, Worksheet_SelectionChange срабатывает при выборе ячейки, в то время как Worksheet_Change срабатывает после изменения содержимого.
Поэтому соответствующие макросы (DropDownList и DependentDropDownList) были назначены на Worksheet_SelectionChange, а очистка содержимого ячеек при изменении значений в столбцах A и B на Worksheet_Change.
📝 Примечание автора: очистка содержимого ячеек добавлена для обеспечения согласованности и корректности данных. Например, значения столбца A предназначены для управления содержимым столбцов B и C ячейки которых будут очищены при удалении данных из соответствующей ячейки в столбце A.
▶️ Тестируем результат
Чтобы удостовериться, что выпадающие списки создаются корректно, зависимости между ними устанавливаются правильно проверим, как код реагирует на изменения в данных:
А в заключении отметим, что это ни единственный подход решения поставленной задачи. И конечно же нет предела совершенству 😉.
А потому, предлагаем поделиться своими идеями и предложениями по улучшению данного подхода. Возможно, вы увидите дополнительные возможности оптимизации, которые могут значительно улучшить функционал предложенного решения.
🙌 Оставайся с нами на связи и узнавай больше о других методах и приемах работы в Excel:
✅ СКАЧАТЬ ПРИМЕР 🔽
