ПРЕДСТАВЬТЕ СИТУАЦИЮ: Мы хотим создать в Excel небольшую табличку, где можно выбрать страну и соответствующий ей город. При этом с помощью выпадающих списков, необходимо ограничить доступные пользователям варианты стран и городов, из которых они могут выбирать. В первой ячейке мы сделаем выбор страны, а во второй будут доступны только принадлежащие выбранной стране города. Думаю, это понятно?
Для начала нужно создать базу данных. На втором листе я занес список стран, которые хочу дать пользователям на выбор в первом раскрывающемся списке, а в соседнем столбце указал числовой индекс, который соответствует одному из списков городов. Списки городов располагаются правее в столбцах D, F и H. Так, например, рядом с France стоит индекс 2, который соответствует списку городов 2. Позже Вы увидите, как этот индекс будет использован.
Если Вы работаете в Excel 2010, то можете создать лист-источник в отдельной рабочей книге. Если же у Вас версия Excel 2003 года, и Вы планируете использовать именованный диапазон, то значения должны находиться в той же книге, можно на другом листе.
Нажмите кнопку New (Создать), чтобы добавить новый именованный диапазон. Откроется диалоговое окно New Name (Создание имени).
В поле Name (Имя) введите имя Country для нашего первого именованного диапазона, а в поле Refers to (Диапазон) выберите тот, в котором хранится список стран:
=Sheet3!$A$3:$A$5
Имена диапазонам, содержащим города, можно присвоить точно таким же образом.
Откроется диалоговое окно Data Validation (Проверка вводимых значений).
Мы хотим дать пользователю на выбор список вариантов, поэтому в поле Allow (Тип данных) выберите List (Список). Это активирует поле Source (Источник), где необходимо указать имя диапазона со странами. Введите в этом поле «=Country» и жмите ОК. Теперь нам нужно сделать второй раскрывающийся список, чтобы пользователи могли выбрать город. Мы поместим этот раскрывающийся список в ячейку B2. А теперь внимание — фокус! Нам нужно проверить содержимое ячейки с названием страны (ячейка B1), чтобы получить индекс соответствующий базе данных с городами. Если пользователь выберет Portugal, то мы должны обратиться к базе с индексом 3, в которой хранятся названия городов Португалии. Мы воспользуемся функцией ВПР (VLOOKUP) для поиска значения из ячейки B1 в таблице с названиями стран. После того как индекс будет известен, мы выберем список, который станет источником данных для нашего второго выпадающего списка. Для этого напишем такую формулу:
=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal) =ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)
Вот так будет выглядеть наш второй раскрывающийся список:
В результате мы получим два связанных (или зависимых) выпадающих списка. Если мы выбираем страну France, в связанном списке у нас будут города только из Франции.
Из этой статьи Вы узнали, как можно сделать простейшие связанные выпадающие списки в Microsoft Excel. Вы можете взять этот простой пример и использовать его для решения реальных задач.
Урок подготовлен для Вас командой сайта office-guru.ru Источник: https://www.excel-user.com/2011/02/cascading-validation-lists.html Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel
