Чтобы сделать выпадающий список в Excel, выделите ячейки, откройте Данные → Проверка данных, в поле Тип данных выберите Список, а в поле Источник введите значения через точку с запятой или выделите ячейки, в которых они находятся. Нажмите OK, и в каждой ячейке появится стрелка, открывающая ваш список.
Самое важное здесь — откуда берутся значения. Список, введённый вручную, создаётся быстро, но его неудобно менять. Если список будет расти, храните значения в таблице Excel, и каждый выпадающий список будет автоматически подхватывать новые записи.
В русской версии Excel названия функций локализованы, а аргументы разделяются точкой с запятой. Формулы ниже приведены в русской форме, а английский вариант указан рядом для тех, кто работает в английской версии Excel.
| Источник значений | Пример в поле «Источник» | Обновляется автоматически? | Подходит для |
|---|---|---|---|
| Список вручную | Да;Нет;Возможно | Нет, правится вручную | Коротких списков, которые не меняются |
| Диапазон ячеек | =$F$2:$F$5 | Только если правите внутри диапазона | Фиксированных списков на листе |
| Таблица Excel | Выделить ячейки столбца таблицы | Да, по мере добавления строк | Списков, которые со временем растут |
| Диапазон разлива | =$H$2# | Да, из формулы вроде УНИК | Списков из уже имеющихся данных |
| Зависимый список | =ДВССЫЛ($A2) | Зависит от первого выбора | Категория, затем элемент |
Как создать выпадающий список по шагам
Эти шаги работают в Excel для Microsoft 365, Excel 2021 и 2019 на Windows и почти не отличаются на Mac и в Excel для Интернета (различия описаны ниже).
- Введите элементы списка в один столбец без пустых ячеек, например Не начато, В работе, Готово и Заблокировано в F2:F5. Для этого удобно создать отдельный лист с названием «Списки».
- Выделите ячейку или ячейки, в которых должен появиться выпадающий список, например C2:C50.
- Откройте Данные → Проверка данных (в группе «Работа с данными»). В Windows сочетание клавиш для английской версии Excel: Alt, A, V, V.
- На вкладке Параметры в поле Тип данных выберите Список.
- Щёлкните в поле Источник и выделите элементы на листе (F2:F5, без заголовка). Excel подставит
=$F$2:$F$5. - Убедитесь, что установлен флажок Список допустимых значений. Флажок Игнорировать пустые ячейки оставьте установленным, если пустые ячейки допустимы.
- Нажмите OK.
Выделите любую из этих ячеек, и рядом появится стрелка. Нажмите на неё или клавиши Alt+↓ (Option+↓ на Mac), чтобы открыть список.
Ввод значений вручную вместо выделения ячеек
Для короткого неизменного списка введите значения прямо в поле Источник: Да;Нет;Возможно. В русской версии Excel разделителем обычно служит точка с запятой, но если в региональных настройках компьютера список разделяется запятой, используйте запятую (Да,Нет,Возможно). Не ставьте пробелы после разделителей, иначе они станут частью каждого значения. В поле «Источник» помещается не более 255 символов, поэтому длинные списки лучше хранить на листе.
Excel для Mac и Excel для Интернета
- Mac: Данные → Проверка данных. В диалоговом окне те же три вкладки.
- Интернет: Данные → Проверка данных (в узком окне — в группе Работа с данными), затем те же шаги Тип данных → Список.
- Мобильные устройства: в приложениях для iPhone, iPad и Android можно выбирать значения из выпадающих списков, созданных на компьютере.
Как сделать список, который растёт автоматически, с помощью таблицы
Обычный диапазон вроде $F$2:$F$5 не включит элемент, который вы введёте в F6. Превратите список в таблицу, и он будет включать новые элементы:
- Щёлкните ячейку в списке и нажмите
Ctrl+T(Cmd+Tна Mac). Установите флажок Таблица с заголовками и нажмите OK. - Создайте выпадающий список, как описано выше, выделив в поле Источник ячейки с данными таблицы (без заголовка).
- Введите новый элемент в строку непосредственно под таблицей. Таблица расширится, и все выпадающие списки на её основе покажут новый элемент.
Ссылку на таблицу вида =Таблица1[Статус] нельзя ввести прямо в поле Источник: Excel её отклонит. Вместо этого выделите ячейки или используйте =ДВССЫЛ("Таблица1[Статус]") (в английской версии =INDIRECT("Table1[Status]")).
Подойдёт и именованный диапазон: выделите элементы, введите имя, например Статусы, в поле имени (слева от строки формул), нажмите Enter и используйте =Статусы в качестве источника.
Динамический список из имеющихся данных с помощью УНИК
Если значения должны браться из уже имеющихся данных, например из всех имён клиентов в столбце A, пусть список построит формула. В Excel для Microsoft 365, Excel 2021 и новее или Excel для Интернета:
- В пустой ячейке, скажем H2, введите
=СОРТ(УНИК(ФИЛЬТР(A2:A500;A2:A500<>"")))(в английской версии=SORT(UNIQUE(FILTER(A2:A500,A2:A500<>"")))). Отсортированный список без дубликатов разольётся вниз от H2 (частьФИЛЬТРисключает пустые ячейки, которые иначе отображались бы как 0). - Создайте выпадающий список, указав в поле Источник
=$H$2#.
Символ # сообщает Excel, что нужно использовать весь диапазон разлива, какой бы длины он ни стал, поэтому новые имена появляются в выпадающем списке автоматически. Саму функцию УНИК в поле «Источник» ввести нельзя, поэтому и нужна вспомогательная ячейка. Подробнее о функции УНИК читайте в статье Как удалить дубликаты в Excel.
Список с другого листа
Пока поле Источник активно, щёлкните ярлык другого листа и выделите ячейки: Excel запишет что-то вроде =Списки!$A$2:$A$10. После этого лист со списками можно скрыть (щёлкните его ярлык правой кнопкой мыши → Скрыть), чтобы никто не изменил его случайно. Источник в другой книге работает, только пока эта книга открыта, поэтому храните список в том же файле.
Зависимый (каскадный) выпадающий список с помощью ДВССЫЛ
Зависимый выпадающий список меняет свои значения в зависимости от другой ячейки: выберите Фрукты в столбце A, и в столбце B будут предложены только фрукты.
- На листе «Списки» разместите каждую категорию в заголовке, а под ним её элементы. Например, Фрукты в D1 и под ним Яблоко, Банан, Вишня, а Овощи в E1 и под ним Морковь, Лук-порей, Горох.
- Выделите D1:E4 и выберите Формулы → Создать из выделенного. Установите только флажок в строке выше и нажмите OK. Excel создаст именованный диапазон для каждого столбца с именем из его заголовка.
- В ячейке A2 основного листа создайте обычный выпадающий список, источником которого будут две ячейки с заголовками,
=Списки!$D$1:$E$1(названия категорий). - Выделите B2, откройте Данные → Проверка данных, выберите Список и введите в качестве источника
=ДВССЫЛ($A2)(в английской версии=INDIRECT($A2)). - Если Excel предупредит, что источник в данный момент вычисляется как ошибка, нажмите Да. Это происходит лишь потому, что A2 пока пуста.
Имена не могут содержать пробелов, поэтому команда Создать из выделенного превращает «Молочные продукты» в Молочные_продукты. Для категорий с пробелами используйте =ДВССЫЛ(ПОДСТАВИТЬ($A2;" ";"_")) (в английской версии =INDIRECT(SUBSTITUTE($A2," ","_"))).
Сообщение для ввода и сообщение об ошибке
- Сообщение для ввода: установите флажок Отображать подсказку, если ячейка является текущей на вкладке Сообщение для ввода и введите заголовок и короткое сообщение, например «Выберите статус задачи». Оно появляется всякий раз, когда кто-то выделяет ячейку.
- Сообщение об ошибке: на вкладке Сообщение об ошибке установите флажок Выводить сообщение об ошибке и выберите Вид. Вариант Останов блокирует всё, чего нет в списке, а Предупреждение и Сообщение показывают ваше сообщение, но позволяют продолжить ввод.
Учтите, что проверка срабатывает только на то, что вводят вручную. Если вставить значение поверх ячейки, вместе с содержимым исчезнет и выпадающий список.
Поиск в выпадающем списке при вводе
В актуальных версиях Excel для Microsoft 365 при вводе текста в ячейку с выпадающим списком список фильтруется по элементам, содержащим эти буквы, что значительно ускоряет работу с длинными списками. В Excel 2021 и более ранних версиях этого нет.
Как изменить, скопировать или удалить выпадающий список
Добавить или изменить элементы. Для таблицы введите новый элемент под ней. Для диапазона измените ячейки, затем снова откройте Данные → Проверка данных и скорректируйте Источник, если диапазон стал длиннее. Для списка, введённого вручную, отредактируйте текст в поле «Источник». Установите флажок Распространить изменения на другие ячейки с тем же условием, чтобы обновить сразу все такие выпадающие списки.
Скопировать выпадающий список в другие ячейки. Перетащите маркер заполнения вниз либо скопируйте ячейку, выделите места назначения и выберите Главная → Вставить → Специальная вставка → Условия на значения, чтобы скопировать только выпадающий список без значения.
Удалить выпадающий список. Выделите ячейки, откройте Данные → Проверка данных, нажмите Очистить все, затем OK. Значения, которые уже есть в ячейках, останутся. Чтобы найти все ячейки с выпадающими списками, выберите Главная → Найти и выделить → Проверка данных.
Почему выпадающий список не работает
- Стрелка не появляется. Откройте Данные → Проверка данных и установите флажок Список допустимых значений. Кроме того, стрелка видна, только когда ячейка выделена. Если стрелок нет нигде, проверьте Файл → Параметры → Дополнительно → Параметры отображения книги и для параметра Для объектов показывать выберите все.
- Новые элементы не появляются. Источником служит фиксированный диапазон. Расширьте его либо превратите список в таблицу или диапазон разлива, чтобы он рос сам.
- Команда «Проверка данных» неактивна. Лист защищён (Рецензирование → Снять защиту листа), в книге используется устаревшая функция общей книги, сгруппировано несколько листов (щёлкните ярлык одного листа) или вы ещё редактируете ячейку (нажмите Enter или Esc).
- В списке есть пустые строки. В диапазон источника попали пустые ячейки. Сократите диапазон или используйте таблицу.
- Кто-то ввёл значение, которого нет в списке. Для сообщения об ошибке выбран вид Предупреждение или Сообщение либо оповещение отключено. Переключите его на Останов.
Часто задаваемые вопросы
Как добавить выпадающий список на весь столбец? Перед открытием окна проверки данных выделите нужный диапазон столбца, например C2:C1000. Если данные находятся в таблице, выпадающий список, применённый к столбцу, автоматически переносится на новые строки.
Можно ли в выпадающем списке Excel выбрать несколько значений? Встроенными средствами проверки данных нельзя. В каждой ячейке хранится один вариант. Для выбора нескольких значений нужен макрос VBA или другая структура таблицы, например по одному столбцу на каждый вариант.
Сколько элементов может быть в выпадающем списке? Список из ячеек может содержать тысячи элементов, хотя длинные списки неудобно прокручивать. Список, введённый вручную, ограничен 255 символами в поле «Источник».
Как сделать выпадающий список с вариантами «Да» и «Нет»? В поле Тип данных выберите Список и введите в поле Источник Да;Нет (или Да,Нет, если в региональных настройках разделителем списка служит запятая).
Как удалить выпадающий список, но сохранить значения? Выделите ячейки, откройте Данные → Проверка данных → Очистить все и нажмите OK. Текущие значения останутся обычным текстом.
Держите данные для списков в порядке
Выпадающий список хорошо работает только тогда, когда данные, на которых он основан, единообразны. CoreGPT для Excel позволяет описать очистку простыми словами, например: «удали повторяющихся клиентов и исправь разные варианты написания каждой страны», и выполняет её прямо в вашей книге, так что списки, на основе которых вы создаёте выпадающие списки, остаются аккуратными. Начать можно бесплатно, а работает он в Excel для Интернета, Windows и Mac.
CoreGPTГотово. Я внёс такие изменения:
- Удалено 12 повторяющихся строк
- «US», «DE»… заменены полными названиями
- Номера телефонов приведены к единому формату
Попробуйте: CoreGPT для Excel
Читайте также:
