Все мы знаем, что зависимый выпадающий список достаточно удобная вещь.
Способов создания выпадающего списка существует несколько.
Как лучше создать зависимый выпадающий список (какой способ выбрать) безусловно зависит от ваших знаний Excel, конечной задачи и того, как представлены исходные данные.
В этом обзоре представляем один из примеров, как создать зависимый выпадающий список для решения одной и той же задачи формулой и макросом.
А какой способ лучше - решать вам! 😉
📢 Файл с исходным кодом размещен в конце статьи 🔽
▶️ Исходные данные
Для целей данного примера рассмотрим пример оформления заявки на доставку товара:
Источник данных (ассортимент товара) размещен на отдельном листе и имеет вид:
А сами данные представлены в форме таблиц:
Так же заданы:
- именованная ячейка "Start"
- именованный диапазон "Ассортимент":
🎯 Задача: для автоматизации процесса оформления заявки сформировать выпадающий список в столбце В и зависимый ему выпадающий список в столбце С.
▶️ Способ 1. Зависимый выпадающий список формулой
Первым шагом установим выпадающий список в столбце В.
Для этого, в столбце В укажем тип данных "Список", а в качестве источника - именованный диапазон "Ассортимент":
Как указывалось выше способов создания выпадающих списков существует несколько.
Ранее мы рассматривали возможность создать зависимый выпадающий список при помощи функции If (ЕСЛИ):
Еще одним из способов является совместное использование встроенных функций: СМЕЩ, ПОИСКПОЗ и СЧЁТЗ.
Напомним:
- функция СМЕЩ:
- функция ПОИСКПОЗ:
- функция СЧЁТЗ:
Теперь, понимая синтаксис каждой из приведенных функций, составим формулу для рассматриваемого примера:
- в качестве ссылки задаем именованную ячейку "Start" относительно которой будет выполнено смещение:
- определяем смещение по строкам равное 1:
- смещение по столбцам определим формулой: ПОИСКПОЗ(B4;Ассортимент;0)-1
- высоту диапазона (число элементов выпадающего списка) определим формулой: СЧЁТЗ(СМЕЩ(Start;1;ПОИСКПОЗ(B4;Ассортимент;0)-1;10;1))
- т.к. каждой категории должен соответствовать свой товар ширина в столбцах так же принимается равной 1:
🔘 Результирующая формула:
Данную формулу следует указать в качестве источника для выпадающего списка столбца С:
🔻 Тестируем результат:
Если вы все выполнили верно, то для каждой категории товара будет задан свой выпадающий список:
▶️ Способ 2. Зависимый выпадающий список макросом
Альтернативным способом решения поставленной задачи будет следующий макрос:
📝 Краткое описание:
Валидация данных представляет собой выпадающий список, который строится на основе найденного диапазона данных на листе wsSource.
Таким образом, макрос позволяет создавать выпадающие списки в столбце C листа wsDestination на основе данных из листа wsSource, соответствующих значениям в столбце B листа wsDestination:
Чтобы автоматизировать работу макроса и автоматически создавать не только зависимый, но и исходный выпадающий список применим событие Worksheet.SelectionChange, и добавим в модуль листа следующий макрос:
📝 Краткое описание:
Теперь мы имеем возможность динамически создавать выпадающие списки в столбце B на основе данных из листа wsSource и автоматически обновлять их при изменении выбранной ячейки:
▶️ Дополнения
И все хорошо, если бы ни одно "НО" 😉.
Сейчас, если пользователь очищает ячейку или меняет её значение 🔽
предыдущий выпадающий список остается, что безусловно доставляет неудобство и может путать пользователя 🤔.
Исправить данную ситуацию поможет макрос заданный на событие Worksheet.Change:
📝 Краткое описание:
Такой подход позволяет автоматически очищать ячейки в столбце C, если значения в столбце B были изменены или удалены:
А на этом сегодня все! 😉
Полагаем наше решение поможет вам в создании собственных зависимых выпадающих списков.
📍 Рекомендуемые статьи 🔽
