Связанные выпадающие списки и формула массива в Excel
Итак, как сделать два связанных списка в Excel: категория, подкатегория и категория более нижнего уровня. Своими словами в данном случае нижний уровень - это "подподкатегория" если она вообще существует... Но для лучшего понимания данного обучающего материала, предположим, что существует.
Два связанных выпадающих списка с формулой массива
В любом случае, с самого начала напишем, что этот учебный материал является продолжением материала: Как сделать зависимые выпадающие списки в ячейках Excel, в котором подробно описали логику и способ создания одного из таких списков. Рекомендуем вам ознакомиться с ним, потому что здесь подробно описывается только то, как сделать тот другой связанный выпадающий список :-) А это то, что мы хотим получить:

Итак, мы имеем:
- тип автомобиля: Легковой, Фургон и Внедорожник (Категория)
- производитель: Fiat, Volkswagen i Suzuki (Подкатегория) и
- модель: ... немножечко их есть :-) (Подподкатегория)
В то же время мы имеем следующие данные:

Этот список должен быть отсортирован в следующей очередности:
- Тип.
- Производитель.
- Модель.
Он может быть любой длины. Что еще важно: стоит добавить к нему еще два меньших списка, необходимых для Типа и Производителя, то есть к категории (первый список) и подкатегории (второй список). Эти дополнительные списки списки выглядят следующим образом:

Дело в том, что эти списки не должны иметь дубликатов записей по Типу и Производителю, находящихся в списке Моделей. Вы можете создать их с помощью инструмента «Удалить дубликаты» (например, это показано в этом видео продолжительностью около 2 минут). Когда мы это сделали, тогда ...
Первый и второй связанный выпадающий список: Тип и Производитель
Для ячеек, которые должны стать раскрывающимися списками в меню "Данные" выбираем "Проверка данных" и как тип данных выбираем "Список".
Для Типа как источник данных мы просто указываем диапазон B7:B9.
Для Производителя мы уже используем формулу, которая подробно описана здесь. Она выглядит так:


Модель - описание для этой записи сделаем таким же самым образом.
Третий связывающий выпадающий список: Модель
Теперь рассмотрим, как связать выпадающий список в Excel. Поскольку Модель зависит как от Типа, так и от Производителя - значит мы будем использовать сложную формулу. После поместим ее не в проверку данных, а в именной диапазон. Соответственно проверка данных будет содержать ссылку на это имя. Предположим, мы хотим отобразить в нем легковые модели Fiat. В первом списке мы выбрали Легковой, во втором - Fiat.
Мы будем перемещать ячейку H4 на столько строк, пока не найдем позицию первого легкового Fiatа. Поэтому в колонке Тип мы должны иметь значение Легковой, а в колонке Производитель должен быть Fiat. Если бы мы использовали промежуточный столбец (это было бы отличным решением, но хотели бы показать вам что-то более крутое ;-), то мы бы искали комбинацию этих данных: Легковой Fiat. Однако у нас нет такого столбца, но мы можем создать его «на лету», другими словами, используя формулу. Набирая эту формулу, вы можете себе представить, что такой промежуточный столбец существует, и вы увидите, что будет проще ;-)
Для определения положения Легковой Fiat, мы, конечно, будем использовать функцию ПОИСКПОЗ. Смотрите:
ПОИСКПОЗ(B4&C4;F5:F39&G5:G39;0)
Вышеописанное означает, что мы хотим знать позицию Легкового Fiatа (отсюда и связь B4&C4). Где? В нашем воображаемом вспомогательном столбце, то есть: F5:F39&G5:G39. И здесь самая большая сложность всей формулы.
Остальное уже проще, а наибольшего внимания требует функция СЧЁТЕСЛИМН, которая проверяет, сколько есть Легковых Fiatов. В частности, она проверяет, сколько раз в списке встречаются такие записи, которые в столбце F5:F39 имеют значение Легковой, а в столбце G5:G39 - Fiat. Функция выглядит так:
СЧЁТЕСЛИМН(F5:F39;B4;G5:G39;C4)
А вся формула для именного диапазона раскрывающегося списка это:
Если вы планируете использовать эту формулу в нескольких ячейках - не забудьте обозначить ячейки как абсолютные ссылки!
Теперь чтобы правильно использовать данную формулу по назначению в третьем выпадающем списке нам необходимо выполнить рад последовательных действий:
- Создаем новое имя. Для этого выберите инструмент: «ФОРМУЛЫ»-«Определенные имена»-«Диспетчер имен»-«Создать».
- При создании имени в поле «Имя:» вводим слово – модель, а в поле «Диапазон:» вводим выше указанную формулу и нажимаем на всех открытых диалоговых окнах ОК:
- Перейдите на ячейку D4 чтобы там создать выпадающий список, в котором на этот раз в поле ввода «Источник:» следует указать ссылку на выше созданное имя с формулой =модель.



Скачать зависимые выпадающие списки в Excel
Когда вы перейдете в меню "Данные", "Проверка данных" и выберите как Тип данных "список", а в поле "Источник" вставьте не саму формулу, а ссылку на имя «=модель» именного диапазона с этой формулой. Такой подход обеспечит стабильность работы третьего выпадающего списка.