Создаем связанные выпадающие списки в Excel – самый простой способ!

ПРЕДСТАВЬТЕ СИТУАЦИЮ: Мы хотим создать в Excel небольшую табличку, где можно выбрать страну и соответствующий ей город. При этом с помощью выпадающих списков, необходимо ограничить доступные пользователям варианты стран и городов, из которых они могут выбирать. В первой ячейке мы сделаем выбор страны, а во второй будут доступны только принадлежащие выбранной стране города. Думаю, это понятно?

Итак, давайте начнём наш простой пример с того, как можно создать связанный (или зависимый) выпадающий список в Excel? В ячейке B1 мы будем выбирать страну, а в ячейке B2 – принадлежащий ей город, как на примере:

Связанный выпадающий список в Excel

Для начала нужно создать базу данных. На втором листе я занес список стран, которые хочу дать пользователям на выбор в первом раскрывающемся списке, а в соседнем столбце указал числовой индекс, который соответствует одному из списков городов. Списки городов располагаются правее в столбцах D, F и H. Так, например, рядом с France стоит индекс 2, который соответствует списку городов 2. Позже Вы увидите, как этот индекс будет использован.

Связанный выпадающий список в Excel

Если Вы работаете в Excel 2010, то можете создать лист-источник в отдельной рабочей книге. Если же у Вас версия Excel 2003 года, и Вы планируете использовать именованный диапазон, то значения должны находиться в той же книге, можно на другом листе.

Мы будем использовать именованные диапазоны и сделаем так, чтобы эти связанные выпадающие списки работали во всех версиях Excel. Следующий шаг – создать именованные диапазоны для наших списков. На вкладке Formulas (Формулы) есть команда Name Manager (Диспетчер имён). Нажав на нее, откроется диалоговое окно Name Manager (Диспетчер имён).

Связанный выпадающий список в Excel

Нажмите кнопку New (Создать), чтобы добавить новый именованный диапазон. Откроется диалоговое окно New Name (Создание имени).

Связанный выпадающий список в Excel

В поле Name (Имя) введите имя Country для нашего первого именованного диапазона, а в поле Refers to (Диапазон) выберите тот, в котором хранится список стран:

=Sheet3!$A$3:$A$5

Нажмите ОК, чтобы сохранить и закрыть диалоговое окно.

Связанный выпадающий список в Excel

Имена диапазонам, содержащим города, можно присвоить точно таким же образом.

Теперь мы можем создать выпадающие списки в тех ячейках, где планировали выбирать данные. Выделите ячейку B1 (в ней мы будем выбирать страну), откройте вкладку Data (Данные), нажмите Data Validation (Проверка данных), а затем в выпадающем меню выберите Data Validation (Проверка данных).

Связанный выпадающий список в Excel

Откроется диалоговое окно Data Validation (Проверка вводимых значений).

Связанный выпадающий список в Excel

Мы хотим дать пользователю на выбор список вариантов, поэтому в поле 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)

Что же делает эта формула? Она ищет значение из ячейки B1 в списке стран и возвращает соответствующий индекс, который затем использует функция CHOOSE (ВЫБОР), чтобы выбрать 1-й, 2-й или 3-й именованный диапазон.

Вот так будет выглядеть наш второй раскрывающийся список:

Связанный выпадающий список в Excel

В результате мы получим два связанных (или зависимых) выпадающих списка. Если мы выбираем страну France, в связанном списке у нас будут города только из Франции.

Связанный выпадающий список в Excel

Из этой статьи Вы узнали, как можно сделать простейшие связанные выпадающие списки в Microsoft Excel. Вы можете взять этот простой пример и использовать его для решения реальных задач.

01.04.2015 14:58
13514

Комментарии

Нет комментариев. Ваш будет первым!