Раскрывающийся список в Excel¶
Раскрывающийся список (выпадающий список, data validation list) — это элемент интерфейса Microsoft Excel, позволяющий пользователю выбрать значение из заранее заданного набора вариантов из выпадающего меню в ячейке. Такие списки применяются для стандартизации ввода данных, исключения опечаток и упрощения работы с большими таблицами. В русскоязычной версии Excel функция реализована средствами проверки данных (вкладка «Данные» → «Проверка данных»).
¶Способы создания
¶Через проверку данных
Основной способ создания раскрывающегося списка:
- Выделить ячейку или диапазон ячеек, в которых будет список.
- На вкладке «Данные» нажать кнопку «Проверка данных».
- В диалоговом окне на вкладке «Параметры» в поле «Допустимое значение» выбрать «Список».
- В поле «Источник» указать значения через запятую (например:
Январь,Февраль,Март) или ссылку на диапазон ячеек (например:=$A$1:$A$12). - Нажать «ОК».
После этого в ячейке появится стрелка, открывающая список вариантов. При вводе значения, отсутствующего в списке, Excel по умолчанию выдаёт предупреждение и запрещает ввод.
¶Через ссылку на диапазон
Если вариантов много, их удобнее разместить в отдельном диапазоне ячеек (обычно на другом листе) и указать этот диапазон как источник. Преимущества:
- легко редактировать и дополнять набор значений;
- список автоматически обновляется при изменении содержимого диапазона;
- не нужно переносить длинные перечни в поле «Источник».
Для ссылки на другой лист используется синтаксис вида =Лист2!$A$1:$A$10. В старых версиях Excel прямая ссылка на другой лист в проверке данных работала с ограничениями, поэтому использовали именованные диапазоны.
¶Именованные диапазоны
Именованный диапазон — это пользовательское имя, присвоенное ячейкам или диапазону. Создание:
- Выделить диапазон со значениями.
- В поле слева от строки формул ввести имя (например,
Месяцы). - Нажать Enter.
В поле «Источник» проверки данных указывается =Месяцы. Такой подход делает формулы читаемыми и позволяет использовать один список в нескольких местах книги.
¶Динамические списки
Статический список не меняется при добавлении новых значений. Динамический список подстраивается под размер источника автоматически. Основные инструменты:
| Инструмент | Версия Excel | Принцип |
|---|---|---|
ССЫЛКА() (OFFSET) | с 2007 | Возвращает диапазон заданного размера относительно якорной ячейки |
ИНДЕКС() (INDEX) | с 2007 | Определяет последнюю заполненную ячейку диапазона |
ДИАПАЗОН() (INDEX, старый синтаксис) | 2007–2010 | Устаревший вариант |
ФИЛЬТР() (FILTER) | Microsoft 365, Excel 2021 | Фильтрует значения по условию |
| Таблицы Excel (Ctrl+T) | с 2007 | Расширяются автоматически при добавлении строк |
Пример формулы динамического источника через ССЫЛКА и ИНДЕКС:
`` =ССЫЛКА($A$1;1;1;СЧЁТЗ($A:$A);1) ``
Формула возвращает диапазон высотой, равной числу заполненных ячеек в столбце A.
¶Вложенные (иерархические) списки
Раскрывающийся список может зависеть от значения в соседней ячейке. Например, в первом списке выбирается регион, во втором — только города этого региона. Реализация:
- Создать отдельные именованные диапазоны для каждой категории (имена должны совпадать с элементами родительского списка).
- В поле «Источник» дочернего списка указать формулу
=ВЫБОР(...)или=ПРОСМОТР(...)с подстановкой имени родительского значения. - Использовать функцию
ДВССЫЛ(INDIRECT), которая преобразует текстовое значение родительской ячейки в имя диапазона:
`` =ДВССЫЛ($A$1) ``
Такой приём применяется в каскадных формах, каталогах товаров, справочниках организаций.
¶Ограничения и настройки
В диалоге «Проверка данных» доступны три вкладки:
- Параметры — тип проверки и источник списка;
- Сообщение об ошибке — текст предупреждения при недопустимом вводе (можно отключить);
- Ввод данных — всплывающая подсказка при выделении ячейки.
Полезные свойства:
- Флажок «Выбор из списка» — при вводе первой буквы Excel предлагает совпадения;
- Игнорирование пустых ячеек — позволяет не показывать стрелку в незаполненных ячейках;
- Удаление проверки — выделить диапазон → «Проверка данных» → «Удалить всё».
¶Особенности совместимости
- Раскрывающиеся списки корректно работают в Excel для Windows, Excel для macOS, Excel Online и мобильных версиях, однако редактирование проверки данных в мобильной версии ограничено.
- При копировании ячейки с проверкой данных она переносится вместе с ней; при вставке в другую книгу ссылки на источники могут потребовать корректировки.
- Защита листа с включённым разрешением на выделение ячеек позволяет пользователю работать только с ячейками, содержащими списки и данные.
¶Источники
- Документация Microsoft Support: «Добавление проверки данных в ячейки»
- Microsoft Excel — справка по функции ПРОВЕРКА.ДАННЫХ
- Книга Дж. Уокена «Microsoft Excel 2019 Bible»