Подробный ответ
Зависимый выпадающий список — это список, содержимое которого меняется в зависимости от выбора в другом списке.
Пошаговая инструкция
Шаг 1. Подготовьте данные
На вспомогательном листе создайте таблицу:
- A1 — заголовок «Категории», A2:A4 — Фрукты, Овощи, Ягоды
- Столбец B (Фрукты): Яблоко, Груша, Апельсин
- Столбец C (Овощи): Морковь, Картофель, Лук
- Столбец D (Ягоды): Клубника, Малина, Черника
Шаг 2. Создайте именованные диапазоны
- Выделите список фруктов (B2:B4), Формулы → Присвоить имя → имя «Фрукты» с областью «Книга» и абсолютной ссылкой на этот диапазон.
- Аналогично для «Овощи» и «Ягоды».
- Выделите все категории (A2:A4), имя «Категории».
Шаг 3. Создайте первый список (категория)
В ячейке A2 основного листа: Данные → Проверка данных → Список → Источник: =Категории.
Шаг 4. Создайте зависимый список
В ячейке B2: Данные → Проверка данных → Список → Источник: =ДВССЫЛ(A2).
Функция ДВССЫЛ преобразует текст из A2 («Фрукты») в ссылку на именованный диапазон.
Важные нюансы
- Имена диапазонов не должны содержать пробелов (используйте подчёркивание).
- Если категория содержит пробел, используйте имя диапазона с подчёркиванием, а в источнике списка —
=ДВССЫЛ(ПОДСТАВИТЬ(A2;" ";"_")). После замены название должно точно соответствовать существующему имени. - При смене значения в A2 старый выбор в B2 не сбрасывается автоматически — используйте VBA для очистки.
Если A2 пустая или такого имени диапазона нет, ДВССЫЛ вернёт #ССЫЛ!. Сначала выберите категорию. При смене категории проверьте зависимое значение вручную, если автоматическая очистка не настроена.
Альтернатива в Microsoft 365, Excel 2021 и 2024 — ФИЛЬТР: выведите список формулой в отдельную свободную область того же листа, затем используйте ссылку на разлившийся диапазон как источник проверки данных. Прямая подстановка массива ФИЛЬТР в поле «Источник» не заменяет ссылку на диапазон.
Практическая деталь
После смены категории старое значение зависимого списка само по себе не очищается. Проверьте его вручную или настройте отдельную проверку соответствия выбранной категории.
| A | B | |
|---|---|---|
| 1 | Фрукты | Напитки |
| 2 | Яблоко | Вода |
| 3 | Груша | Чай |
Назовите A2:A3 «Фрукты», B2:B3 «Напитки». В D2 выберите категорию, а для E2 задайте источник =ДВССЫЛ(D2). При выборе «Напитки» E2 предложит «Вода» и «Чай».