Чтобы сделать выпадающий список в Excel, выделите ячейки, выберите Данные > Проверка данных, в поле Тип данных выберите Список, в поле Источник введите элементы через разделитель или выделите диапазон, где они стоят, и нажмите «ОК». Теперь в каждой ячейке есть стрелка с этими вариантами, а другие значения не принимаются. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой, например =СУММЕСЛИ(B2:B6;E2;C2:C6).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Region | Sales | |
| 2 | Ana | North | 120 | North | 360 | |
| 3 | Ben | South | 85 | |||
| 4 | Cara | North | 240 | |||
| 5 | Dan | East | 60 | |||
| 6 | Eve | South | 150 |
F2 показывает 360, итог North. Выберите North в B3, и F2 вырастет на 85 продаж Ben. В E2 тоже есть выпадающий список: выберите там South, и F2 покажет итог South. Список для ввода и формула, которая его читает, это самое частое применение выпадающего списка.
Как сделать выпадающий список по шагам
- Выделите ячейки, в которых должен быть список, например B2:B6.
- Выберите Данные > Проверка данных (группа «Работа с данными»). В английской версии Excel для Windows последовательность клавиш Alt, A, V, V.
- На вкладке Параметры в поле Тип данных выберите Список.
- В поле Источник либо введите элементы через разделитель, либо щёлкните в поле и выделите на листе диапазон с элементами, и тогда запишется
=$F$2:$F$5. - Оставьте отмеченным Список допустимых значений (без него стрелки нет, остаётся только проверка).
- Нажмите ОК.
Чтобы открыть список с клавиатуры, выделите ячейку и нажмите Alt+Стрелка вниз (Windows) или Option+Стрелка вниз (Mac). В Excel для Microsoft 365 ввод первых букв в ячейке сужает список до подходящих элементов.
В том же окне есть две необязательные вкладки: Сообщение для ввода показывает подсказку, когда ячейка выделена, а Сообщение об ошибке задаёт, что происходит, когда кто-то вводит значение не из списка. С видом Останов (по умолчанию) ввод отклоняется; с видом Предупреждение или Сообщение он разрешается после вопроса. Снимите флажок Выводить сообщение об ошибке, чтобы можно было вводить что угодно, но список всё равно предлагался.
Введённые элементы разделяются разделителем списков из региональных настроек компьютера. В русском Excel, как и в других странах с десятичной запятой, это точка с запятой: North;South;East;West.
Выпадающий список из диапазона ячеек
Список, введённый в окне, скрыт от глаз и правится только там. Список в ячейках проще поддерживать: измените ячейку, и изменится каждый выпадающий список, который её использует. Здесь регионы стоят в E2:E5, а выпадающий список в B2:B6 использует этот диапазон как источник.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | |
| 2 | Ana | North | 120 | North | |
| 3 | Ben | South | 85 | South | |
| 4 | Cara | North | 240 | East | |
| 5 | Dan | East | 60 | West | |
| 6 | Eve | South | 150 |
Замените E5 с West на Central, затем откройте любую стрелку в столбце B: список предложит Central вместо West. Уже выбранные значения в столбце B не изменятся.
Чтобы использовать диапазон на другом листе, а так списки обычно и прячут, введите в поле «Источник» имя листа: =Lists!$A$2:$A$5. Чтобы список рос, когда вы добавляете элемент внизу, сначала превратите элементы в таблицу (выделите их, Вставка > Таблица), а затем выделите столбец таблицы как источник: ссылка будет расширяться вместе с таблицей.
Динамический выпадающий список с УНИК
Когда элементы должны браться из самих данных (каждый регион, который встречается в столбце, по одному разу), постройте список формулой и направьте выпадающий список на результат. =СОРТ(УНИК(B2:B8)) в G2 разливает различные регионы по алфавиту.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Pick | Sales | Regions | |
| 2 | Ana | North | 120 | South | 235 | East | |
| 3 | Ben | South | 85 | North | |||
| 4 | Cara | North | 240 | South | |||
| 5 | Dan | East | 60 | West | |||
| 6 | Eve | South | 150 | ||||
| 7 | Fay | West | 95 | ||||
| 8 | Gus | East | 110 |
G2 разливает East, North, South, West, и выпадающий список в E2 предлагает эти четыре. Замените B7 на Central, и Central появится и в результате, и в списке.
В Excel задайте источником выпадающего списка =$G$2#. # после ячейки означает «весь разлитый результат этой формулы», поэтому список всегда ровно такой длины, как результат, без пустых строк в конце. Ссылка на разлитый диапазон требует Excel 365 или 2021; исходная ячейка может быть на другом листе (=Lists!$A$2#). Если в столбце данных есть пустые ячейки, УНИК возвращает для них 0; уберите их через =СОРТ(УНИК(ФИЛЬТР(B2:B100;B2:B100<>""))). Подробно функция описана на странице УНИК.
Зависимые выпадающие списки
Зависимый список меняется вместе с выбором в другой ячейке: выберите Fruit в A2, и B2 предложит только фрукты. В Excel 365 и 2021 второй список строит формула ФИЛЬТР: =ФИЛЬТР(E2:E8;D2:D8=A2) возвращает элементы, категория которых совпадает с A2, а выпадающий список в B2 использует этот разлитый результат как источник.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Category | Item | Category | Item | Items | ||
| 2 | Fruit | Pear | Fruit | Apple | Apple | ||
| 3 | Fruit | Pear | Pear | ||||
| 4 | Vegetable | Carrot | Kiwi | ||||
| 5 | Vegetable | Leek | |||||
| 6 | Bakery | Bread | |||||
| 7 | Fruit | Kiwi | |||||
| 8 | Bakery | Bagel |
При Fruit в A2 G2 разливает Apple, Pear и Kiwi, и это варианты в B2. Выберите Bakery в A2: G2 сменится на Bread и Bagel. В B2 по-прежнему стоит Pear, пока вы не выберете снова, потому что выпадающий список никогда не меняет значение, уже стоящее в ячейке. В Excel источник для B2 это =$G$2#.
В старых версиях Excel классический способ использует ДВССЫЛ и именованные диапазоны:
- Поместите элементы каждой категории в отдельный столбец с названием категории в заголовке: Fruit в одном столбце, Vegetable в следующем.
- Выделите каждый столбец с элементами и назовите его по категории в поле имени (слева от строки формул):
Fruit,Vegetable,Bakery. - Сделайте в A2 выпадающий список с источником
Fruit;Vegetable;Bakery. - Сделайте в B2 выпадающий список с источником
=ДВССЫЛ(A2). ДВССЫЛ превращает текст из A2 в ссылку на диапазон с таким именем.
Имена должны в точности совпадать с текстом категории и не могут содержать пробелов (используйте Dairy_Products или =ДВССЫЛ(ПОДСТАВИТЬ(A2;" ";"_")) в источнике). Подробнее о ДВССЫЛ на странице ДВССЫЛ.
Найти значение выбранного элемента
Выпадающий список часто служит вводом в бланке заказа или расчёте: пользователь выбирает товар, а поиск подставляет его цену.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Product | Price | ||
| 2 | Order | Pear | Apple | $1.20 | ||
| 3 | Pear | $1.50 | ||||
| 4 | Carrot | $0.80 | ||||
| 5 | Bread | $2.40 | ||||
| 6 | Milk | $1.10 |
Ваша очередь: В C2 верните цену товара, выбранного в B2, из таблицы в E:F.
При выбранном Pear ответ $1.50. Выберите другой товар в B2, и цена изменится следом. =ПРОСМОТРX(B2;E2:E6;F2:F6) тоже работает; аргументы описаны на странице ВПР.
Закрасить ячейку по выбранному элементу
Чтобы закрашивать ячейку по выбранному значению (зелёным для Done, красным для Late), добавьте к тем же ячейкам правило условного форматирования: выделите B2:B6, выберите Главная > Условное форматирование > Правила выделения ячеек > Равно, введите Late и выберите формат. Чтобы закрашивать всю строку, выделите A2:B6 и используйте Создать правило > Использовать формулу для определения форматируемых ячеек с =$B2="Late".
| A | B | |
|---|---|---|
| 1 | Task | Status |
| 2 | Quote | Done |
| 3 | Invoice | Late |
| 4 | Order | Open |
| 5 | Report | Late |
| 6 | Survey | Done |
B3 и B5 выделены. Выберите Late в B4, и она тоже выделится; выберите Done в B3, и выделение пропадёт. Правила подробно описаны на странице условное форматирование.
Почему выпадающий список не работает
- В Данные > Проверка данных снят флажок Список допустимых значений. Список по-прежнему ограничивает ввод, но стрелки нет.
- Стрелка видна только у выделенной ячейки. На сетке другие ячейки со списком никак не отмечены; чтобы их найти, используйте Главная > Найти и выделить > Проверка данных.
- В диапазоне-источнике есть пустые ячейки, поэтому в списке пустые строки. Выделяйте только заполненные ячейки или используйте разлитый источник (
=$G$2#), в котором пустых нет. - Элементы введены не с тем разделителем:
North,Southв Excel, где разделитель точка с запятой, превращается в один элементNorth,South. - В выпадающем списке одно значение. Выбор второго элемента заменяет первый; для выбора нескольких элементов в одной ячейке нужен макрос VBA.
Чтобы скопировать выпадающий список в другие ячейки без значения, скопируйте ячейку, затем выберите Главная > Вставить > Специальная вставка > условия на значения. Чтобы удалить список, выделите ячейки и выберите Данные > Проверка данных > Очистить все.
Часто задаваемые вопросы
Как сделать выпадающий список в Excel?
Выделите ячейки, выберите Данные > Проверка данных, в поле «Тип данных» выберите «Список», в поле «Источник» введите элементы через точку с запятой (North;South;East) или выделите диапазон, где они стоят (=$F$2:$F$5), и нажмите «ОК».
Как изменить выпадающий список в Excel?
Выделите ячейку со списком, откройте Данные > Проверка данных и измените поле «Источник». Отметьте Распространить изменения на другие ячейки с тем же условием, чтобы обновить все копии. Если источник это диапазон, изменение его ячеек меняет список без открытия окна.
Как удалить выпадающий список в Excel?
Выделите ячейки, выберите Данные > Проверка данных и нажмите Очистить все, затем «ОК». Уже выбранные значения останутся в ячейках; исчезнут только стрелка и ограничение.
Как сделать выпадающий список с другого листа?
Введите в поле «Источник» ссылку с именем листа: =Lists!$A$2:$A$6 или, пока поле «Источник» активно, щёлкните другой лист и выделите диапазон. Подойдёт и именованный диапазон (Формулы > Присвоить имя): =Regions.
Как сделать выпадающий список, который обновляется автоматически?
Направьте его на разлитую формулу: поставьте =СОРТ(УНИК(ФИЛЬТР(B2:B100;B2:B100<>""))) во вспомогательную ячейку, например H2, и укажите =$H$2# как источник. Новые значения в столбце B сразу появятся в списке, а ФИЛЬТР не пустит в него пустые строки. Для этого нужен Excel 365 или 2021.
