Как сделать выпадающий список в Excel: простой, зависимый, из другого листа

Как сделать выпадающий список в Excel? Нужна проверка данных: выделяете ячейки, открываете Данные → Проверка данных, выбираете тип «Список» и указываете, откуда брать значения. После этого в ячейке появляется стрелка, а вместо ручного ввода выбирается готовый вариант. Ошибок вида «Выполнен», «выполнено» и «Выполн.» в одном столбце больше не будет, а сводная таблица и фильтры начнут считать правильно.

Ниже разберём, как сделать выпадающий список в Excel по шагам: простой список через точку с запятой, список из диапазона и с другого листа, список, который пополняется сам, зависимый список через ДВССЫЛ, подсказки и сообщения об ошибке, а также как скопировать, найти и удалить список. В конце коротко про Google Таблицы. Все примеры я собрал и проверил в Microsoft Excel 2021 (сборка 16.0.17002.20000) с русским интерфейсом. Картинки сделаны из этого Excel, а раскрытый список на них дорисован по содержимому настоящего источника: в обычный снимок диапазона Excel его не включает. Учебный файл со всеми списками можно скачать и посмотреть, как они устроены.

Как сделать выпадающий список в Excel: проверка данных

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

Простой список через точку с запятой

Самый быстрый вариант подходит, когда вариантов немного и они редко меняются: статусы, «Да» и «Нет», способы доставки. В учебном файле это столбец «Статус» на листе «Заказы».

1. Выделите ячейки, где нужен список. Удобно выделить сразу запас строк, в примере F2:F101.
2. Откройте вкладку Данные (Data) → группа «Работа с данными» → Проверка данных (Data Validation). Откроется окно «Проверка вводимых значений».
3. На вкладке «Параметры» в поле «Тип данных» выберите Список (List).
4. В поле «Источник» (Source) введите значения через точку с запятой без пробелов вокруг: Новый;В работе;Выполнен;Отменён.
5. Проверьте, что стоят флажки «Список допустимых значений» (без него стрелки не будет) и «Игнорировать пустые ячейки». Нажмите ОК.

Разделитель зависит от версии. В классическом Excel с русскими настройками это точка с запятой, в Excel в браузере, по справке Microsoft, разделителем служит запятая. Я проверил на своём файле: правило со строкой Новый;В работе;Выполнен;Отменён даёт ровно четыре пункта, и «В работе» с пробелом внутри считается одним значением.

Регистр букв важен. Значение «выполнен» с маленькой буквы Excel счёл неверным, хотя «Выполнен» в списке есть. Это ещё один довод выбирать из списка, а не печатать вручную.

Список из диапазона ячеек

Если вариантов больше пяти-шести или их нужно иногда править, держите их в ячейках. Впишите значения столбиком без пустых строк, а в поле «Источник» вместо текста укажите диапазон: поставьте курсор в поле и выделите ячейки мышью. Excel подставит адрес со знаками доллара, например =$J$2:$J$5. Заголовок столбца в диапазон не включайте, иначе он станет пунктом списка.

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

Выпадающий список из другого листа

Значения для списков удобно держать на отдельном листе, чтобы они не мешали в рабочей таблице. В учебном файле это лист «Списки»: категории товаров, товары по категориям, менеджеры и способы оплаты.

Сослаться на этот лист можно двумя способами.

Прямой ссылкой

В поле «Источник» откройте нужный лист и выделите диапазон. Получится ссылка вида =Списки!$J$2:$J$5. Так в примере сделан столбец «Оплата». Способ рабочий, но адрес неудобно читать, а при добавлении пятого способа оплаты в J6 список его не увидит: я дописал способ «Рассрочка» под диапазоном, и Excel не принял её в ячейке с проверкой. Диапазон придётся расширять вручную: Проверка данных → поле «Источник», а чтобы правка дошла до всех ячеек столбца, поставьте флажок «Распространить изменения на другие ячейки с тем же условием».

Через имя диапазона

Удобнее дать диапазону имя и сослаться на него:

1. Выделите значения без заголовка, в примере A2:A5 на листе «Списки».
2. Щёлкните в поле имени слева от строки формул, введите Категории и нажмите Enter. Другой путь: Формулы (Formulas) → Присвоить имя (Define Name).
3. В проверке данных в поле «Источник» напишите =Категории.

Такую запись проще читать, а если список вырастет, диапазон достаточно поправить один раз в имени, и изменение дойдёт до всех ячеек с этим списком. Все имена книги собраны в Формулы → Диспетчер имён (Name Manager): там же диапазон имени меняют, когда список вырос. Имя не может начинаться с цифры и не должно содержать пробелов.

Выпадающий список, который пополняется сам

Обычная ссылка на диапазон фиксирована: строки, добавленные ниже, в список не попадут. Есть два способа это исправить.

Через умную таблицу

Умная таблица сама расширяется, когда под ней дописывают строку. Как её сделать и чем она удобна, подробно разобрано в статье про сводные таблицы в Excel. Для списка порядок такой:

1. Щёлкните в любую ячейку со значениями и нажмите Ctrl+T (или Главная → Форматировать как таблицу). Отметьте «Таблица с заголовками».
2. На вкладке Конструктор таблиц задайте понятное имя таблицы, в примере ТаблМенеджеры.
3. Создайте имя через Формулы → Присвоить имя: имя Менеджеры, в поле «Диапазон» формула =ТаблМенеджеры[Менеджер].
4. В проверке данных укажите источник =Менеджеры.

Ссылку на столбец таблицы вида =ТаблМенеджеры[Менеджер] Excel прямо в поле «Источник» не принимает (у меня правило с такой ссылкой не создалось), поэтому её заворачивают в имя. Второй рабочий вариант без имени: =ДВССЫЛ("ТаблМенеджеры[Менеджер]"). Я проверил, как это работает: до добавления имя «Ольга» в ячейке с проверкой считалось неверным. После того как я вписал Ольгу в первую пустую строку под таблицей, таблица выросла до H1:H6, имя Менеджеры само стало указывать на H2:H6, и Ольга появилась в списке.

Через динамический массив

В Excel 2021, 2024 и Microsoft 365 есть функции динамических массивов. Ими удобно собрать список без повторов из уже введённых данных, например города клиентов. На листе «Клиенты» в ячейке D2 стоит формула:

=СОРТ(УНИК(ФИЛЬТР(B2:B200;B2:B200<>"")))

ФИЛЬТР (FILTER) убирает пустые ячейки, УНИК (UNIQUE) оставляет каждый город один раз, СОРТ (SORT) расставляет их по алфавиту. Результат сам «разливается» вниз на столько ячеек, сколько городов. В английском Excel формула записывается так: =SORT(UNIQUE(FILTER(B2:B200,B2:B200<>""))).

В проверке данных источником указана ссылка на весь разлив: =$D$2#. Знак # после адреса значит «весь диапазон, который вернула формула в D2», сколько бы в нём ни было строк.

Я проверил: до добавления клиента из Казани значение «Казань» в столбце «Город доставки» было неверным. После новой строки разлив вырос с D2:D5 до D2:D6, и «Казань» стала допустимой. Ссылка на разлив работает и с другого листа: источник =Клиенты!$D$2# в моём файле принимал «Пермь» и отклонял «Сочи». В Excel 2019 и более ранних версиях функций УНИК и СОРТ нет: там используйте умную таблицу.

Зависимый выпадающий список через ДВССЫЛ

Зависимый выпадающий список меняет варианты в зависимости от соседней ячейки. В примере сначала выбирается категория, а потом в столбце «Товар» показываются только товары этой категории. Строится он на именах диапазонов и функции ДВССЫЛ (INDIRECT).

Шаг 1. Разложите товары по столбцам

На листе «Списки» у каждой категории свой столбец, а в первой строке стоит название категории, точно такое же, как в списке категорий: «Сеть», «Хранение», «Периферия», «Умный дом».

Шаг 2. Создайте имена из заголовков

1. Выделите первый столбец с заголовком, в примере C1:C5 (от «Сеть» до последнего товара).
2. Формулы → Создать из выделенного (Create from Selection).
3. В окне «Создание имён из выделенного диапазона» оставьте только флажок «в строке выше» и нажмите ОК.
4. Повторите для каждого столбца.

Можно выделить сразу весь блок C1:F5, но тогда в короткие столбцы попадут пустые ячейки, а в списках появятся пустые строки. Поэтому в учебном файле я создавал имена по одному столбцу. Проверить результат можно в Диспетчере имён:

Обратите внимание на «Умный_дом». Имя не может содержать пробел, и Excel сам заменил его подчёркиванием.

Шаг 3. Настройте второй список

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

=ДВССЫЛ(ПОДСТАВИТЬ($D2;" ";"_"))

ДВССЫЛ превращает текст «Хранение» из ячейки D2 в ссылку на одноимённый диапазон. ПОДСТАВИТЬ (SUBSTITUTE) меняет пробел на подчёркивание, чтобы «Умный дом» превратился в имя «Умныйдом». Если пробелов в названиях нет, хватит =ДВССЫЛ($D2). В английском Excel: `=INDIRECT(SUBSTITUTE($D2," ",""))`.

Знак доллара стоит только перед буквой столбца, как в условном форматировании: каждая строка смотрит на свою категорию. Формула пишется для первой строки выделения.

Что я проверил на этом примере:

• Нужный товар проходит, чужой - нет. При категории «Хранение» значение NAS допустимо, а «Роутер» - нет.
• Без ПОДСТАВИТЬ ломается категория с пробелом. С формулой =ДВССЫЛ($D2) товар «Датчик протечки» для «Умного дома» оказался неверным: имени «Умный дом» не существует.
• Пустая категория даёт ошибку. Если в момент создания правила ячейка D2 пуста, формула в источнике возвращает ошибку. Я настраивал списки из скрипта, и в таком состоянии Excel правило не создал. Надёжнее сначала выбрать категорию в первой строке, а потом настраивать список товаров.

Когда товар выбран, цену к нему удобно подтянуть из прайса функцией ВПР: как это сделать, разобрано в статье Функция ВПР в Excel.

Подсказка и сообщение об ошибке

В том же окне «Проверка вводимых значений» есть ещё две вкладки.

Сообщение для ввода. Включите флажок «Отображать подсказку, если ячейка является текущей», заполните «Заголовок» и «Подсказку по вводу». Когда пользователь встанет на ячейку, рядом появится жёлтая записка, например «Статус заказа: выберите статус из списка». Справка Microsoft называет предел подсказки в 225 символов, в моём Excel 2021 через COM поместилось 254, а заголовок ограничен 32 символами. Длинную инструкцию лучше вынести в отдельную ячейку.

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

В справке Microsoft эти виды названы «Остановить», «Предупреждение» и «Информация». В учебном файле статус и товар настроены на «Останов», а способ оплаты - на «Предупреждение» с текстом «Такого способа оплаты нет в списке. Оставить?». «Предупреждение» удобно, когда список почти полный, но редкие исключения бывают.

Если заголовок и текст не заполнить, Excel покажет своё стандартное сообщение. Своё лучше: в нём можно подсказать, что делать, например «Сначала выберите категорию, затем товар из списка».

Как скопировать, найти и удалить выпадающий список

Скопировать список в другие ячейки

Проще всего протянуть ячейку со списком за маркер заполнения или скопировать её и вставить: правило переносится вместе с ячейкой. Если нужно перенести только правило без значения и оформления:

1. Скопируйте ячейку со списком (Ctrl+C).
2. Выделите ячейки, куда нужен список.
3. Правый щелчок → Специальная вставка (Paste Special) → условия на значения (Validation) → ОК.

Я проверил: после такой вставки в пустой ячейке появилось то же правило со списком статусов, а сама ячейка осталась пустой.

Найти ячейки с выпадающими списками

Главная → Найти и выделить (Find & Select) → Проверка данных (Data Validation). Excel выделит все ячейки листа, где есть любые правила проверки. На моём листе «Заказы» он выделил C2:G101, все 500 ячеек пяти столбцов.

Если нужны только ячейки с таким же правилом, как у текущей, встаньте на неё и откройте Найти и выделить → Выделить группу ячеек (Go To Special) → «проверка данных» → «этих же». Для ячейки F2 Excel выделил ровно F2:F101.

Обвести неверные данные

Значения, которые попали в ячейки в обход списка (вставкой, до появления правила или после смены категории), показывает команда Данные → стрелка у кнопки Проверка данных → Обвести неверные данные (Circle Invalid Data). Excel нарисует вокруг таких ячеек красные овалы.

Овалы видны, пока вы не исправите значение или не выберете Удалить обводку неверных данных в том же меню.

Удалить выпадающий список

1. Выделите ячейки со списком. Удобно сделать это через Найти и выделить → Выделить группу ячеек → «этих же».
2. Данные → Проверка данных.
3. Нажмите Очистить все (Clear All) и ОК.

Значения в ячейках останутся, исчезнут только стрелка и само правило. Обратное тоже верно: клавиша Delete стирает значение, но не список. Я проверил: после очистки содержимого правило в ячейке осталось на месте. Если нужно убрать вообще всё, используйте Главная → Очистить → Очистить все.

Выпадающий список в Google Таблицах

В Google Таблицах выпадающий список делается похоже, а вариантов создания даже больше. Раздел написан по справке Google, сам я его в Таблицах не проверял.

1. Выделите ячейки.
2. Откройте Вставка → Раскрывающийся список. То же самое есть в меню Данные → Настроить проверку данных, в контекстном меню ячейки и по символу @ в ячейке.
3. Справа откроется панель правил. В поле условия выберите:
   • Раскрывающийся список: значения вводятся вручную по одному, кнопкой «Добавить объект» добавляется следующий;
   • Раскрывающийся список из диапазона: значения берутся из ячеек, как в Excel.

Отличия от Excel, которые стоит знать:

• Неверные значения по умолчанию отклоняются. Чтобы разрешить их, в «Расширенных настройках» в поле «Для неверных данных» выберите «Показывать предупреждение».
• Стиль показа выбирается там же: «Чип», «Стрелка» или «Обычный текст». Каждому пункту можно назначить свой цвет.
• Несколько значений в одной ячейке можно разрешить флажком «Разрешить выбор нескольких объектов», но только для стиля «Чип». В Excel такой настройки нет.
• Удалить список можно в той же панели кнопкой «Удалить правило».

Зависимый список с ДВССЫЛ в поле источника я в Google Таблицах не проверял, поэтому описывать этот способ для них не буду. Учебный файл можно загрузить в Google Таблицы, но как перенесутся имена и ссылки на умную таблицу, тоже не проверял.

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