Office

Выпадающий список в ячейке Excel: создание, изменение, зависимые списки

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

Выпадающий список в ячейке Excel: создание, изменение, зависимые списки
Фото: W.carter, CC BY-SA 4.0, Wikimedia Commons
Содержание
  1. Как сделать выпадающий список из диапазона ячеек
  2. Список вручную: значения через точку с запятой
  3. Список, который обновляется сам при добавлении значений
  4. Подсказка и сообщение об ошибке при вводе
  5. Как изменить выпадающий список
  6. Как убрать или удалить выпадающий список
  7. Зависимые выпадающие списки
  8. Частые проблемы и их решение

Как сделать выпадающий список из диапазона ячеек

Это основной способ. Сначала запишите допустимые значения в столбец или строку, например отделы компании: Продажи, Склад, Бухгалтерия, Доставка. Лучше разместить их на отдельном листе, чтобы случайно не удалить.

  1. Выделите ячейку или диапазон, где должен появиться список.
  2. Откройте вкладку "Данные" и нажмите "Проверка данных".
  3. На вкладке "Параметры" в поле "Тип данных" выберите "Список".
  4. В поле "Источник" выделите мышью диапазон со значениями или впишите его адрес, например =$F$2:$F$5.
  5. Оставьте отмеченными "Игнорировать пустые ячейки" и "Список допустимых значений", нажмите "ОК".

Справа от ячейки появится стрелка. Нажмите её или клавиши Alt+Стрелка вниз, и значения раскроются. Выбранный пункт попадает в ячейку. Тот же список можно сразу назначить целому столбцу: выделите ячейки заранее.

Макет окна Проверка данных в Excel с типом Список
Окно проверки данных: тип Список и источник значений

Список вручную: значения через точку с запятой

Если вариантов мало и они не меняются, например "Да", "Нет", "Не знаю", таблицу на листе делать необязательно. В поле "Источник" впишите значения через точку с запятой: Да;Нет;Не знаю. Разделитель зависит от настроек Windows: в русской региональной настройке это точка с запятой, в англоязычной - запятая.

Минус способа в том, что изменить значения можно только в окне проверки данных, причём у каждой ячейки отдельно. Для часто меняющихся перечней используйте диапазон на листе.

Список, который обновляется сам при добавлении значений

Если обычный диапазон $F$2:$F$5 дополнить новой строкой F6, в списке её не будет. Есть два надёжных решения.

  • Превратите исходные значения в таблицу: выделите их, нажмите Ctrl+T. В источнике укажите диапазон таблицы, а лучше именованный диапазон на её столбец.
  • Задайте источник в виде формулы с запасом пустых строк и включите "Игнорировать пустые ячейки": пустые строки в конце списка просто не будут мешать.

Самый простой вариант - именованный диапазон. Выделите значения, в поле имени слева от строки формул впишите, например, Отделы и нажмите Enter. В поле "Источник" запишите =Отделы. Новые значения, добавленные внутрь диапазона, подтянутся автоматически. Чтобы они подтягивались и при добавлении в конец, держите значения в умной таблице.

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

У окна "Проверка данных" есть ещё две вкладки. На вкладке "Подсказка по вводу" можно задать текст, который появится при выборе ячейки, например "Выберите отдел из списка". На вкладке "Сообщение об ошибке" настраивается реакция на значение вне списка.

Вид сообщенияЧто происходит при вводе чужого значения
ОстановExcel не принимает значение, нужно ввести разрешённое
ПредупреждениеExcel спрашивает, оставить ли введённое значение
СообщениеExcel сообщает о несоответствии, но значение остаётся

Для строгого учёта оставьте "Останов". Если список служит подсказкой, а не ограничением, выберите "Сообщение" или "Предупреждение".

Схема из четырёх шагов создания выпадающего списка в Excel
Порядок создания выпадающего списка по шагам

Как изменить выпадающий список

Способ зависит от того, как создан список. Если источник - диапазон на листе, просто исправьте значения в ячейках: список обновится сразу. Если в диапазон нужно добавить строку, вставьте её внутри диапазона (щёлкните правой кнопкой по строке, "Вставить"), чтобы адрес расширился сам.

Если список введён вручную, выделите ячейку, откройте "Данные", "Проверка данных" и измените текст в поле "Источник". Чтобы изменение коснулось всех ячеек с таким же списком, отметьте внизу окна "Распространить изменения на другие ячейки с тем же условием".

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

Выделите ячейку со списком, откройте "Данные", "Проверка данных" и нажмите кнопку "Очистить все", затем "ОК". Стрелка исчезнет, а значение, уже выбранное в ячейке, останется как обычный текст.

Если не помните, где на листе есть списки, откройте вкладку "Главная", "Найти и выделить", "Выделить группу ячеек" и отметьте "Проверка данных". Excel выделит все ячейки с проверкой, и их можно очистить за один раз.

Зависимые выпадающие списки

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

  1. Создайте на листе столбцы со значениями второго уровня: под заголовком "Россия" города России, под заголовком "Беларусь" города Беларуси.
  2. Выделите значения каждого столбца и присвойте имя, совпадающее с названием первого уровня: Россия, Беларусь. Пробелов и дефисов в имени быть не должно, подчёркивание допустимо.
  3. В ячейке A2 создайте обычный список стран.
  4. В ячейке B2 создайте список и в "Источник" впишите формулу =ДВССЫЛ($A2).

Теперь выбор страны в A2 меняет перечень в B2. Если названия первого уровня содержат пробелы, используйте имена с подчёркиванием и подставляйте их формулой ПОДСТАВИТЬ. Приёмы с функциями удобно подбирать в конструкторе формул Excel, а для поиска значения по выбору из списка пригодится функция ВПР.

Частые проблемы и их решение

  • Список не раскрывается: на вкладке "Параметры" должен быть включён флажок "Список допустимых значений" (в некоторых версиях - "Раскрывающийся список в ячейке").
  • В списке пустые строки: источник захватил пустые ячейки. Уменьшите диапазон или оформите значения таблицей.
  • После вставки из другой ячейки список пропал: обычная вставка копирует и правило проверки. Для вставки значений используйте "Специальная вставка" - "Значения".
  • Выпадает слишком мелкий шрифт: размер шрифта в списке зависит от масштаба листа. Увеличьте масштаб в правом нижнем углу окна.
  • Список не ведёт себя как нужно в Excel Online при сложных источниках: настройте проверку данных в классическом приложении.

Выпадающие списки хорошо сочетаются с условным форматированием: например, ячейка со статусом "Выполнено" автоматически закрашивается зелёным, а "Просрочено" - красным. Для учёта заказов, отпусков и задач связка работает как простая форма без макросов.

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

Как сделать выпадающий список в ячейке Excel?

Выделите ячейку, откройте "Данные", затем "Проверка данных". В поле "Тип данных" выберите "Список", в поле "Источник" укажите диапазон со значениями или перечислите их через точку с запятой и нажмите "ОК".

Как добавить значение в выпадающий список?

Если источник - диапазон, вставьте новую строку внутри диапазона и впишите значение. Если список введён вручную, добавьте текст в поле "Источник" через точку с запятой. Для автоматического расширения оформите значения таблицей Ctrl+T.

Как удалить выпадающий список в Excel?

Выделите ячейки, откройте "Данные", "Проверка данных", нажмите "Очистить все" и "ОК". Уже выбранные значения останутся в ячейках как обычный текст.

Как сделать зависимый выпадающий список?

Назовите диапазоны значений второго уровня так же, как пункты первого списка. Во втором списке в поле "Источник" напишите =ДВССЫЛ(ячейка_с_первым_списком), например =ДВССЫЛ($A2).

Как выбрать несколько значений из выпадающего списка?

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

Нашли ошибку или устаревший пункт меню? Напишите на admin@programistik.top с адресом страницы. Перед правкой реестра, системных файлов и разделов диска делайте резервную копию.

Читайте по теме