Выпадающий список в Excel: обычный и зависимый
Выпадающий список ограничивает ввод в ячейку заранее заданным перечнем значений — это ускоряет заполнение таблицы и защищает от опечаток и разнобоя вроде «Минск» и «г. Минск» в одном столбце. Разберём обычный список через Проверку данных и более сложный сценарий — зависимый список, где перечень во втором столбце меняется в зависимости от выбора в первом.
Опубликовано
Как сделать обычный выпадающий список
Выделите ячейку или диапазон ячеек, в которых нужен список, и откройте вкладку «Данные» → «Проверка данных». В поле «Тип данных» выберите «Список» — появится поле «Источник», куда можно указать перечень значений одним из двух способов.
Способ 1. Ввести значения вручную
В поле «Источник» впишите значения через точку с запятой,
например: Новый;В работе;Выполнено;Отменён.
Подходит для короткого и редко меняющегося перечня — при
любом изменении список придётся редактировать через это же
окно.
Способ 2. Сослаться на диапазон ячеек
Заранее выпишите значения списка в отдельные ячейки (удобнее
всего — на отдельном листе), а в поле «Источник» укажите
ссылку на этот диапазон, например =$A$2:$A$10.
Такой список проще редактировать: значения меняются прямо
в ячейках-источниках, а не в окне проверки данных.
Как сделать список, который сам растёт вместе с источником
У ссылки на обычный диапазон ($A$2:$A$10) есть
ограничение: новая строка, добавленная ниже границы диапазона,
в список не попадёт. Чтобы список расширялся автоматически,
оформите источник как «умную» таблицу — выделите диапазон
и нажмите Ctrl+T, а в поле «Источник» проверки
данных сошлитесь на столбец этой таблицы. Тогда и добавление,
и удаление строк в источнике сразу отражается в списке.
Что такое зависимый (каскадный) список
Зависимый список — это два связанных списка, где перечень во втором зависит от значения, выбранного в первом. Типичный пример: в первом столбце выбирается категория товара («Канцтовары», «Оргтехника»), а во втором должны появляться только позиции этой категории, а не общий список всех товаров сразу.
Как сделать зависимый список через ДВССЫЛ
Стандартный способ — именованные диапазоны для каждой
категории и функция ДВССЫЛ в источнике второго
списка.
- Разместите значения каждой категории в своём столбце: например, столбец с товарами категории «Канцтовары», рядом — столбец категории «Оргтехника».
- Выделите значения одной категории и присвойте диапазону имя, совпадающее с названием категории (Формулы → Диспетчер имён → Создать, или поле имени слева от строки формул). Если в названии категории есть пробел, замените его на нижнее подчёркивание — например, «Офисная_техника»: пробелы в именах диапазонов недопустимы.
- Сделайте первый список обычным способом — Проверка данных → Список — со ссылкой на перечень категорий.
- Для второго списка в поле «Источник» укажите
=ДВССЫЛ(A2), где A2 — ячейка с уже выбранной категорией. Функция превращает текст в этой ячейке в имя диапазона и подставляет его значения в качестве источника.
Частые проблемы
- Второй список пустой, пока не выбрано значение в первом. Это нормальное поведение — ДВССЫЛ ссылается на ещё не заданное имя диапазона, пока ячейка первого списка пуста.
- При смене категории в первом списке старое значение во втором столбце остаётся, хотя уже не подходит. Excel не очищает связанную ячейку автоматически — это можно сделать только макросом на событие изменения листа; штатными средствами проверки данных такая очистка не предусмотрена.
- ДВССЫЛ не работает после сохранения файла в другом формате. Функция чувствительна к стилю ссылок и работает нестабильно при передаче файла между разными региональными настройками Excel — если список ломается только у части пользователей, в первую очередь проверьте этот момент.
Часто задаваемые вопросы
Почему выпадающий список не подхватывает новые значения, добавленные в источник?
Если список ссылается на обычный диапазон ячеек (например, $A$1:$A$5), новые строки, добавленные ниже этой границы, в список не попадут — диапазон нужно расширять вручную через Проверку данных. Чтобы список обновлялся сам, оформите источник как «умную» таблицу (Ctrl+T) — тогда ссылка на неё в проверке данных растёт вместе с таблицей автоматически.
Почему зависимый список через ДВССЫЛ выдаёт ошибку или показывает пустой список?
Чаще всего причина в том, что имя именованного диапазона не совпадает с текстом, который выбран в первом списке — например, в категории есть пробел («Офисная техника»), а у диапазона пробелы недопустимы. Проверьте, что все имена диапазонов совпадают со значениями первого списка один в один, с учётом замены пробелов на подчёркивания в обеих частях.
Можно ли запретить ввод значений, которых нет в выпадающем списке?
Да, это поведение по умолчанию: если в окне Проверки данных на вкладке «Сообщение об ошибке» выбран тип «Остановка» (стоит по умолчанию), Excel не даст сохранить в ячейке значение, которого нет в списке источника. Тип «Предупреждение» или «Сообщение» позволяет ввести произвольное значение, проигнорировав список.
Как изменить сам перечень значений в уже готовом выпадающем списке?
Если список сделан вручную (перечень через запятую), достаточно снова открыть Данные → Проверка данных и отредактировать текст в поле «Источник». Если список ссылается на диапазон или умную таблицу, проще всего отредактировать сами значения в этом диапазоне — список обновится автоматически.
Что дальше
ДВССЫЛ — не единственная функция, которая превращает текст в ссылку на диапазон или на именованный объект. Разбор похожих по логике функций поиска — в статье «Функция ВПР (VLOOKUP) в Excel: примеры и разбор ошибок».
Готовые таблицы с настроенными списками
В шаблонах на сайте поля ввода уже защищены выпадающими списками — меньше риска опечатки и разнобоя в данных без ручной настройки проверки данных.