Выпадающие списки в Excel, включая зависимые

Простой список — через «Проверку данных», зависимый — через ДВССЫЛ по именованным диапазонам. Клики по шагам, разбор пустого списка и готовый файл с примером.

Коротко

Выпадающий список — это ячейка, в которой значение не набирают руками, а выбирают из готового перечня. Ставится через Данные → Проверка данных → «Список». Так убирают опечатки и разнобой в написании: не «шкаф», «Шкаф» и «шкаф » вперемешку, а одно значение из списка.

Зависимый список — это второй список, набор вариантов в котором меняется от выбора в первом. Выбрали в «Категории» Мебель — в «Подкатегории» доступны кресла, столы, шкафы и полки; выбрали Технику — ноутбуки, мониторы, принтеры. Держится это на функции ДВССЫЛ: она превращает текст выбранной категории в ссылку на именованный диапазон с тем же именем.

В примере форма заявки лежит на первом листе — столбцы «Категория» и «Подкатегория», — а варианты для списков вынесены на лист «Источники списков». Там каждая категория оформлена именованным диапазоном: диапазон «Мебель» — это ячейки с креслами, столами, шкафами и полками. Категория выбирается из простого списка имён, подкатегория — из =ДВССЫЛ(ПОДСТАВИТЬ(B7;" ";"_")), где B7 — ячейка с выбранной категорией.

То же самое формулой в Excel

Пошагово

1. Выпишите варианты. Соберите значения в отдельный столбец или на отдельный лист — по одному в ячейке, без пустых строк внутри перечня. Для зависимых списков варианты каждой категории держите отдельной колонкой.
2. Задайте имя диапазону. Выделите варианты одной категории и присвойте им имя: Формулы → Присвоить имя (или впишите имя в поле слева от строки формул). Для зависимых списков имя диапазона должно совпадать со значением категории ровно, буква в букву.
3. Список категорий. Встаньте в ячейку «Категория», откройте Данные → Проверка данных, тип — «Список», в поле «Источник» укажите имя диапазона категорий, например =Категории. В ячейке появится стрелка выбора.
4. Зависимый список. Встаньте в ячейку «Подкатегория», снова Данные → Проверка данных → «Список», а в «Источник» впишите =ДВССЫЛ(ПОДСТАВИТЬ(B7;" ";"_")), где B7 — ячейка с выбранной категорией той же строки.
5. Проверьте совпадение имён. Выберите каждую категорию по очереди и убедитесь, что во втором списке появились именно её варианты. Имена диапазонов должны совпадать со значениями в списке категорий — иначе зависимый список будет пуст.

Где обычно ломается

• В имени диапазона нельзя ставить пробел. Excel не разрешает пробелы в именах, поэтому категорию «Бытовая техника» именованным диапазоном назвать «Бытовая техника» не выйдет — только Бытовая_техника. Ради этого в формуле и стоит ПОДСТАВИТЬ(B7;" ";"_"): она на лету меняет пробел в выбранной категории на подчёркивание, чтобы текст совпал с именем диапазона. Диапазон надо назвать так же — с подчёркиванием.
• Заголовок столбца в диапазон не включайте. Если выделить варианты вместе со словом «Мебель» в шапке, оно попадёт в список отдельным пунктом. В именованный диапазон входят только сами значения, без заголовка.
• Категория обязана точь-в-точь совпадать с именем диапазона. Зависимый список пуст ровно потому, что ДВССЫЛ не нашла диапазон с таким именем: «Мебель.» с точкой, лишний пробел на конце или строчная «мебель» вместо «Мебель» — и ссылки нет. Значения в списке категорий и имена диапазонов должны быть одним и тем же текстом.
• При копировании ячейки переезжает и список. Копирование (Ctrl+C → Ctrl+V) переносит вместе со значением и правило проверки данных — выпадающий список появится там, куда вставили. Чтобы перенести только число или текст, вставляйте через «Специальная вставка → Значения», иначе список расползётся по соседним ячейкам. Убрать список из ячейки: Данные → Проверка данных → «Очистить всё».

Частые вопросы

Связанные страницы