Создание выпадающего списка в Excel¶
Выпадающий список — это элемент проверки данных в Microsoft Excel, позволяющий выбрать значение из заранее заданного перечня вместо ручного ввода. Он обеспечивает корректность вводимых данных, ускоряет заполнение таблиц и минимизирует ошибки, возникающие при опечатках. В Excel выпадающие списки реализуются средствами инструмента «Проверка данных» и могут ссылаться на диапазон ячеек, именованный диапазон или создаваться вручную.
¶Основной способ: проверка данных
Самый распространённый метод создания выпадающего списка предполагает использование встроенного инструмента проверки данных.
¶Подготовка источника данных
Для начала необходимо определить перечень допустимых значений. Их можно разместить в отдельном столбце или на отдельном листе книги. Например, в ячейках листа «Справочник» в столбце A со строки 1 по строку 5 вводятся значения: «Яблоко», «Груша`, «Слива», «Вишня», «Абрикос».
¶Настройка проверки данных
Порядок действий в Excel (версии 2016, 2019, 2021 и Microsoft 365) следующий:
- Выделить ячейку или диапазон ячеек, в которых будет выпадающий список.
- Перейти на вкладку «Данные» и нажать кнопку «Проверка данных» в группе «Работа с данными».
- В открывшемся диалоговом окне на вкладке «Параметры» в поле «Допустимое значение» выбрать вариант «Список».
- В поле «Источник» указать ссылку на диапазон со значениями, например
=Справочник!$A$1:$A$5. Если значения вводятся вручную, их можно перечислить через запятую:Яблоко,Груша,Слива. - Нажать «ОК».
После выполнения этих действий в ячейке появляется стрелка, при нажатии на которую отображается список доступных значений.
¶Именованный диапазон
Для удобства управления источником данных часто применяют именованные диапазоны. Для их создания выделяют диапазон со значениями и вводят имя в поле «Имя» слева от строки формул, например Фрукты. В поле «Источник» проверки данных указывают =Фрукты. Преимущество такого подхода — возможность расширять или сокращать список, изменяя границы именованного диапазона, не перенастраивая каждую ячейку проверки данных.
¶Динамический выпадающий список
Статический список не подстраивается под изменения справочника. Динамический вариант автоматически обновляется при добавлении или удалении значений.
¶Функция ДИНСПИСОК
В современных версиях Excel (Microsoft 365, Excel 2021) для построения динамического источника используется функция ДИНСПИСОК (в англоязычной версии — OFFSET в связке с СЧЁТЗ, либо нативный ДИНСПИСОК). Пример формулы, возвращающей динамический диапазон из столбца A листа «Справочник»:
`` =ДИНСПИСОК(Справочник!$A$2;1;1;СЧЁТЗ(Справочник!$A:$A)-1;1) ``
Эта формула подсчитывает количество заполненных ячеек и возвращает диапазон соответствующего размера. Результат формулы оформляется как именованный диапазон и используется в поле «Источник» проверки данных.
¶Таблица Excel
Альтернативный способ — преобразование справочника в форматированную таблицу (Ctrl+T). При ссылке на столбец такой таблицы в качестве источника проверки данных список автоматически расширяется при добавлении новых строк в таблицу.
¶Вложенные (иерархические) списки
Иногда требуется связать несколько списков, чтобы значения второго зависели от выбранного значения первого. Например, при выборе категории «Фрукты» второй список предлагает только фрукты, а при выборе «Овощи» — только овощи.
Такие списки строятся с помощью функции ВПР (или ПРОСМОТР), которая подставляет в поле «Источник» проверки данных ссылку на нужный столбец справочника. Для именования диапазонов используется функция ДВССЫЛ, формирующая ссылку по имени категории. Настройка вложенных списков требует аккуратной организации справочника: каждая категория должна быть оформлена именованным диапазоном, а заголовок категории — совпадать с именем диапазона.
¶Ограничения и настройка сообщений
В диалоговом окне проверки данных доступна вкладка «Сообщение об ошибке». Если пользователь вводит значение, отсутствующее в списке, Excel может блокировать ввод и показывать предупреждение с поясняющим текстом. Существует три режима поведения:
- Стоп — запрещает ввод любого значения, кроме перечисленных в списке.
- Предупреждение — предлагает подтвердить ввод, но допускает нестандартное значение.
- Информация — лишь уведомляет, не блокируя ввод.
Режим выбирается на вкладке «Сообщение об ошибке» при создании проверки данных.
¶Особенности и ограничения
Выпадающий список, созданный через проверку данных, имеет ряд ограничений:
- Количество отображаемых в выпадающем окне строк ограничено (примерно 8–10 строк видно без прокрутки).
- Список не поддерживает форматирование отдельных элементов (цвета, шрифты).
- Сортировка и фильтрация внутри выпадающего окна недоступны.
- При удалении листа-источника ссылки становятся некорректными.
Для обхода части ограничений используются элементы управления ActiveX и формы, а также надстройки и пользовательские функции VBA, однако базовый инструмент проверки данных покрывает большинство типовых задач.
¶Применение
Выпадающие списки широко применяются в шаблонах документооборота, анкетах, расчётных моделях и базах данных, создаваемых на основе Excel. Они используются для стандартизации ввода наименований, кодов, статусов и категорий, что критично при совместной работе нескольких пользователей над одной книгой.
Источники:
- Microsoft Support. Проверка данных в Excel.
- Microsoft Support. Создание выпадающего списка.
- Кун Е. Microsoft Excel 2019. Полный курс самоучитель.