Динамические выпадающие списки с «зависимостью» (без макросов)

Привет, дорогой читатель! 👋

Вы уже умеете делать обычные выпадающие списки через «Проверку данных». Но что, если я скажу, что можно создать цепочку списков, где выбор в первом определяет содержимое второго? Например: выбрали страну → во втором списке появляются только её города. Выбрали город → в третьем списке — только улицы.

Это называется зависимые (каскадные) выпадающие списки. И сегодня я покажу, как сделать их в Excel без макросов, без VBA, только с помощью формул и именованных диапазонов. Звучит как магия? Давайте разбираться! 🧙‍♂️

🧠 Как это работает (общая идея)

У нас будет три ингредиента:

1. Исходные данные — таблица со связями: страна → город → улица.
2. Именованные диапазоны — каждой стране дадим имя, равное её названию, и внутри этого имени будут её города.
3. Функция ДВССЫЛ (INDIRECT) — она превращает текст в ссылку. Именно она позволит первому списку «подставить» нужный именованный диапазон во второй список.

Всё просто, но выглядит как настоящий программинг! 🤯

📝 Пошаговый пример: выбор страны → города → улицы

Шаг 1. Готовим исходные данные

Создадим на листе три столбца (можно на отдельном листе-справочнике):

Важно: данные должны быть отсортированы по странам и городам (но не обязательно, просто для удобства).

Шаг 2. Создаём уникальные списки стран и городов

Нам нужны два вспомогательных списка для выпадающих меню.

• Список стран (уникальные значения): выделите столбец «Страна» → скопируйте → вставьте в другое место → «Удалить дубликаты». Получим: Россия, США.

• Список городов нам не нужен целиком, мы сделаем его динамическим.

Шаг 3. Создаём именованные диапазоны для городов каждой страны

Это ключевой момент. Нам нужно, чтобы Excel запомнил: «Россия» — это список {Москва; Санкт-Петербург, Томск}, а «США» — {Вашингтон, Нью-Йорк; Лос-Анджелес}.

Делаем так:

Создайте отдельную таблицу уникальных пар «Страна-Город». Далее выделите всю таблицу и нажмите "Данные" - "Удаление дубликатов". Должна получить такая таблица:

ВНИМАНИЕ! В названиях городов не должно быть пробелов и тире. Их можно заменить на знак подчеркивания.

• Затем для каждой страны вручную создайте именованный диапазон:
• Диапазон: выделите ячейки с уникальными городами России (например, Москва, Санкт-Петербург и Томск).
• Имя: В разделе "Формулы" - "Присвоить имя" создайте имя "Россия". Имя должно быть без пробелов!

Также создайте имя для городов США.

• Имя: США
• Диапазон: Нью-Йорк, Лос-Анджелес, Вашингтон

В диспетчере имен должны появиться два новых имени:

Шаг 4. Создаём первый выпадающий список (страны)

1. Выделите ячейку, где будет выбор страны (например, J1).
2. «Данные» → «Проверка данных» → тип «Список».
3. Источник: укажите диапазон с уникальными названиями стран (например, =E1:E2, если вы их туда вынесли).
4. Нажмите ОК.

Теперь в J1 можно выбрать «Россия» или «США».

Шаг 5. Создаём зависимый выпадающий список (города)

1. Выделите ячейку для города (например, L1).
2. «Данные» → «Проверка данных» → тип «Список».
3. В поле «Источник» вводим формулу:

=ДВССЫЛ(J1)

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

Важно: Функция ДВССЫЛ превращает текст из ячейки A1 в ссылку. Если в A1 написано «Россия», то =ДВССЫЛ(A1) превращается в =Россия, а это именованный диапазон с городами.

Теперь, если в A1 выбрана «Россия», то в B1 выпадающий список покажет города: Москва, Санкт-Петербург. Если выбрать «США» — появятся Нью-Йорк и Лос-Анджелес. 🎉

Шаг 6. Третий уровень: зависимость улиц от города

Теперь сделаем так, чтобы в третьем списке (улицы) появлялись только улицы выбранного города.

Для этого нам нужно создать именованные диапазоны для каждого города: Москва, Санкт-Петербург, Томск, Нью-Йорк, Лос-Анджелес, Вашингтон. В каждом диапазоне — соответствующие улицы.

создание имен для Городов

• Имя Москва → улицы: Тверская, Арбат.
• Имя Санкт_Петербург (внимание! Нельзя использовать в имени пробелы и тире) → Невский пр., Лиговский пр.
• Имя Томск → Тверская, Ленина пр..
• Имя Нью_Йорк → 5-я авеню, Бродвей.
• Имя Лос_Анджелес → Сансет-бульвар, Голливуд-бульвар.
• Имя Вашингтон → Индепенденс-авеню, Массачусетс-авеню.

Создаём эти имена через Диспетчер имён (вкладка «Формулы» → «Диспетчер имён» → «Создать»). Или через выделение диапазона и ввод имени в поле слева от строки формул.

Затем в ячейке для улицы (N1) настраиваем проверку данных:

• Источник: =ДВССЫЛ(L1)

Вуаля! Теперь выбор в трёх списках полностью синхронизирован. 🌍 → 🏙️ → 🏠

⚠️ Важные нюансы и ограничения

1. Имена не могут начинаться с цифры или содержать пробелы. Если у вас в названии страны есть пробел (например, «Южная Корея»), используйте подчёркивание: Южная_Корея. Тогда и в ячейке с выбором должно быть точно такое же написание, иначе ДВССЫЛ не сработает.
2. Имена чувствительны к регистру? Нет, Excel не различает заглавные и строчные буквы в именах. Но для надёжности лучше писать одинаково.
3. Что делать, если список городов большой? Создавать имена вручную — муторно. Есть способ автоматического создания имён через формулы или Power Query, но это уже тема для продвинутой статьи. Для начала хватит и ручного способа.
4. При изменении выбора в первом списке второй список не очищается автоматически. Он просто показывает новые значения, а старая ячейка может остаться с неактуальным городом. Чтобы избежать путаницы, можно добавить условное форматирование или использовать макрос для очистки (но это уже VBA, а мы сегодня без него).

💡 Где это пригодится в жизни?

• Формы заказа (выбор категории → подкатегория → товар)
• Анкеты (страна → регион → город)
• Биллинг (клиент → его проекты → задачи по проекту)
• Учёт товаров (бренд → модель → комплектация)

Любая ситуация, когда последующий выбор зависит от предыдущего.

🎯 Заключение

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

• обычную проверку данных,
• функцию ДВССЫЛ,
• именованные диапазоны.

Это мощный приём, который превращает ваши таблицы в полноценные интерактивные формы.

🔥 Ставьте лайк, если хотите продолжения: «Как создавать зависимые списки автоматически для сотен значений без ручного ввода имён». А в комментариях поделитесь, где бы вы применили такую связку? 👇

Подписывайтесь, чтобы не пропустить новые трюки с формулами. Успешной работы! 🚀

⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇

Материалы по Эксель. Содержание данного канала:

https://dzen.ru/a/ZhpQXTxmQDShWlXf

⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆

На сегодня все!

Спасибо за внимание!