Создание раскрывающегося списка в Excel, набор элементов которого зависит от выбора значения в другом выпадающем списке. Динамические диапазоны и условия.
Именованные динамические диапазоны
Для создания раскрывающихся списков будем использовать именованные динамические диапазоны, которые позволяют добавлять и удалять элементы в исходных наборах без внесения изменений в формулы.
Для динамических диапазонов создаем новый рабочий лист Excel и переименовываем его в «Списки». Заполняем столбцы наборами элементов:
На вкладке «Формулы» ленты инструментов нажимаем ссылку «Присвоить имя»:
В поле «Имя» пишем «Группа», в поле «Диапазон» вставляем формулу =СМЕЩ(Списки!$A$2;0;0;СЧЁТЗ(Списки!$A:$A)-1;1) и нажимаем кнопку «OK». Первый именованный динамический диапазон создан.
Повторяем эти действия для создания остальных именованных диапазонов, формулы для которых перечислены в таблице:
| Имя | Диапазон |
|---|---|
| Группа | =СМЕЩ(Списки!$A$2;0;0;СЧЁТЗ(Списки!$A:$A)-1;1) |
| Грибы | =СМЕЩ(Списки!$B$2;0;0;СЧЁТЗ(Списки!$B:$B)-1;1) |
| Насекомые | =СМЕЩ(Списки!$C$2;0;0;СЧЁТЗ(Списки!$C:$C)-1;1) |
| Рыбы | =СМЕЩ(Списки!$D$2;0;0;СЧЁТЗ(Списки!$D:$D)-1;1) |
Все имена диапазонов можно просмотреть в диспетчере имен («Формулы» — «Диспетчер имен»):
В диспетчере имен доступно создание, изменение и удаление имен диапазонов.
Создание раскрывающегося списка
Создаем первый раскрывающийся список, от выбора значения в котором будет зависеть набор элементов второго выпадающего списка. Привяжем его к динамическому диапазону «Группа».
На листе Excel, где будет таблица с раскрывающимися списками, называем первые два столбца «Группа» и «Вид». Выбираем ячейку под названием столбца «Группа» и нажимаем ссылку «Проверка данных» на вкладке «Данные» ленты инструментов.
В открывшемся окне «Проверка вводимых значений» выбираем тип данных «Список», указываем источник «=Группа» и нажимаем кнопку «OK»:
Первый выпадающий список создан. Скопировать его в другие ячейки столбца «Группа» можно простым протягиванием. Также, при присвоении источника данных диапазону ячеек, раскрывающийся список будет добавлен во все ячейки.
Раскрывающийся список по условию
Для создания выпадающего списка по условию выбираем ячейку под названием столбца «Вид» и нажимаем ссылку «Проверка данных» на вкладке «Данные» ленты инструментов. В открывшемся окне «Проверка вводимых значений» выбираем тип данных «Список», указываем источник =ЕСЛИ(A2="Грибы";Грибы;ЕСЛИ(A2="Насекомые";Насекомые;ЕСЛИ(A2="Рыбы";Рыбы;Списки!$E$1))) и нажимаем кнопку «OK».
Если в первом раскрывающемся списке (Группа) не будет выбрана ни одна позиция, во втором выпадающем списке (Вид) будет отображаться значение ячейки по адресу Списки!$E$1 (Выберите группу).
Смотрите, как для раскрывающегося списка можно использовать динамический диапазон на основе умной таблицы.