Связанные выпадающие списки

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

Способ 1 (именованные диапазоны + функция ДВССЫЛ)

Как видно из названия нам нужно проименовать диапазоны в нашей таблице.
А именно выделить все города страны Италия, и в поле Имя, которая находится слева от строки формул, ввести название страны — Италия.
Эту операцию повторим для соседних столбцов.

Выделим ячейку в которую мы хотим поместить выпадающий список с названием страны, в нашем примере это ячейка G2 «Страна».
Переходим во вкладку Данные, в группе Работа с данными выберем Проверка данных.

Во всплывающем окне во вкладке Параметры в поле Тип данных выберем Список.
В поле Источник укажем диапазон значений, который состоит из названий стран, в нашем случае формула выглядит так «=$A$1:$C$1».
Нажимаем ОК.

Выпадающий список со странами готов.

Нам осталось создать выпадающий список для выбора города, который будет соответствовать выбранной стране.
Для этого выделим ячейку, куда мы хотим поместить новый выпадающий список «Город», и создадим его по шагам выше.
В выпадающем окне Проверки вводимых значений в поле Тип данных так же выберем Список.
А в поле Источник нам нужно указать формулу с использованием функции ДВССЫЛ.
Эта функция возвращает ссылку, заданную строкой.
ДВССЫЛ расшифровывается как двойная ссылка.
На практике она работает следующим образом, ДВССЫЛ(А1) = А1, обе части эквивалентны.
Вместо значения А1 может быть и именованный диапазон, как в нашем случае, чем мы и воспользуемся.

В поле Источник запишем следующую функцию «=ДВССЫЛ($G$2), и нажимаем ОК.

Связанные выпадающие списки готовы.
Теперь в зависимости от страны, Excel нам будет предлагать на выбор города этой страны.

Способ 2 (умные таблицы + функция ДВССЫЛ)

Этот способ будет интересен тем, что мы сможем дополнять список городов прямо в таблице, и Excel будет автоматически подтягивать новые значения в выпадающий список.
Один раз настроили, и далее просто добавляем новые города.

Для начала необходимо превратить обычные таблицы в умные.
Выделите любое значение в столбце А.
На вкладке Главная в группе Стили выбираем Форматировать как таблицу.
Стиль таблицы выбираем любой, для нас это не принципиально.

Во всплывающем окне автоматически подтянется весь столбец.
Так как у нас есть заголовок с названием страны Италия, то ставим галочку Таблица с заголовками и нажимаем ОК.

Повторим эту операцию для всех столбцов.

Далее присвоим имя для каждой таблицы.
Особенность умных таблиц в том, что для присвоения имени достаточно выделить любой из элементов таблицы, и выбрать во вкладке Конструктор в группе Свойства, Имя таблицы.
Укажем название страны, в нашем примере для первого столбца будет страна Италия.

Повторим эту операцию для всех столбцов.

Подготовительная  работа завершена.

Создадим теперь первый выпадающий список.
Мы предварительно выписали названия всех стран, на них и будем ссылаться.

Выделяем ячейку I2, которая соответствует выпадающему списку для выбора страны.
Далее во вкладке Данные в группе Работа с данными выберем Проверка данных.
Во всплывающем окне в Параметрах в поле Тип данных выберем Список, в Источнике укажем подготовленный диапазон со списком стран.
Нажимаем ОК.

Первый выпадающий список готов.
Сразу создадим второй для выбора города.
Во всплывающем окне в поле Источник укажем только формулу, как и в Способе 1, с использованием функции ДВССЫЛ.
Формула имеет вид «=ДВССЫЛ($I$2)», и нажимаем ОК.

Готово!

Теперь мы можем дописывать в наши умные таблицы новые города, и они автоматически попадут в выпадающий список.

Так же, для Вашего удобства есть видеоурок.

Скачать пример

Больше полезных видеоуроков на нашем YouTube канале.
Если у Вас возникли вопросы или просто хотите поделиться мнением, напишите в комментариях к записи.

Add a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам: