Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Условный выпадающий список с оператором ЕСЛИ (5 примеров)

Author Siluvia Last modified

Если вам нужно создать выпадающий список, который изменяется в зависимости от того, что вы выберете в другой ячейке, добавление условия к выпадающему списку может стать полезным решением. При создании условного выпадающего списка использование оператора ЕСЛИ является интуитивно понятным методом, поскольку он всегда используется для проверки условий в Excel. В этом руководстве демонстрируются 5 методов, которые помогут вам пошагово создать условный выпадающий список в Excel.

Conditional Drop-Down List with IF Statement

Получить образец файла:

excel icon Нажмите, чтобы скачать образец файла

Используйте оператор ЕСЛИ или ЕСЛИМН для создания условного выпадающего списка

В этом разделе представлены две функции: ЕСЛИ и ЕСЛИМН, которые помогут вам создать условный выпадающий список на основе других ячеек в Excel с двумя примерами.

Добавьте одно условие, например две страны и их города

Как показано на GIF ниже, вы можете легко переключаться между городами двух стран «США и Франция» в выпадающем списке. Давайте посмотрим, как использовать функцию ЕСЛИ для выполнения этой задачи.

Шаг 1: Создайте основной выпадающий список

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

1. Выберите ячейку (в данном случае E2), куда вы хотите вставить основной выпадающий список. Перейдите на вкладку Данные, выберите Проверка данных.

Go to the Data tab, select Data Validation

2. В диалоговом окне Проверка данных выполните следующие шаги для настройки параметров.

1) Оставайтесь на вкладке Параметры;
2) Выберите Список в поле Разрешить;
3) В поле Источник выберите диапазон ячеек, содержащих значения, которые вы хотите отобразить в выпадающем списке (здесь я выбираю заголовки таблицы)
4) Нажмите кнопку ОК. Смотрите скриншот:

specify the options in the dialog box

Шаг 2: Создайте условный выпадающий список с помощью оператора ЕСЛИ

1. Выберите диапазон ячеек (в данном случае E3:E6), куда вы хотите вставить условный выпадающий список.

2. Перейдите на вкладку Данные, выберите Проверка данных.

3. В диалоговом окне Проверка данных вам необходимо настроить следующее.

1) Оставайтесь на вкладке Параметры;
2) Выберите Список в раскрывающемся списке Разрешить;
3) Введите следующую формулу в поле Источник;
=IF($E$2=$B$2,$B$3:$B$6,$C$3:$C$6)
4) Нажмите кнопку ОК. Смотрите скриншот:

specify the options in the dialog box with an IF statement

Примечание: Эта формула говорит Excel: Если значение в E2 равно значению в B2, отобразите все значения в диапазоне B3:B6. В противном случае отобразите значения в диапазоне C3:C6.
Где
1) E2 — это ячейка выпадающего списка, которую вы указали на шаге 1, содержащая заголовки.
2) B2 — это первая ячейка заголовка исходного диапазона.
3) B3:B6 содержит города в США.
4) C3:C6 содержит города во Франции.
Результат

Условный выпадающий список теперь завершен.

Как показано на GIF ниже, если вы хотите выбрать город в США, нажмите на E2, чтобы выбрать Города в США из выпадающего списка. Затем выберите любой город, принадлежащий США, в ячейках ниже E2. Чтобы выбрать город во Франции, выполните ту же операцию.

Примечание:
1) Указанный выше метод работает только для двух стран и их городов, потому что функция ЕСЛИ используется для проверки условия и возвращает одно значение, если условие выполнено, и другое значение, если оно не выполнено.
2) Если в этот случай добавить больше стран и городов, следующие вложенные функции ЕСЛИ и функции ЕСЛИМН могут помочь.

Добавьте несколько условий, например более двух стран и их городов

Как показано на GIF ниже, есть две таблицы. Одна колонка содержит разные страны, а многострочная таблица содержит города этих стран. Здесь нам нужно создать условный выпадающий список, который будет содержать города, меняющиеся в зависимости от выбранной вами страны в E10. Пожалуйста, следуйте приведенным ниже шагам для завершения.

Шаг 1: Создайте выпадающий список, содержащий все страны

1. Выберите ячейку (здесь я выбираю E10), где вы хотите отобразить страну, перейдите на вкладку Данные, нажмите Проверка данных.

2. В диалоговом окне Проверка данных вам необходимо:

1) Оставайтесь на вкладке Параметры;
2) Выберите Список в раскрывающемся списке Разрешить;
3) Выберите диапазон, содержащий страны, в поле Источник;
4) Нажмите кнопку ОК. Смотрите скриншот:

specify the options in the dialog box

Выпадающий список, содержащий все страны, теперь завершен.

Шаг 2: Присвойте имя диапазону ячеек для городов под каждой страной

1. Выберите весь диапазон таблицы городов, перейдите на вкладку Формулы, нажмите Создать из выделения.

Select the data range of the cities, go to the Formulas tab, click Create from Selection.

2. В диалоговом окне Создать имена из выделения отметьте только опцию Верхняя строка и нажмите кнопку ОК.

check the Top row option in the dialog box

Примечания:
1) Этот шаг позволяет вам одновременно создать несколько именованных диапазонов. Здесь заголовки строк используются как имена диапазонов.

create multiple named ranges by this step

2) По умолчанию Диспетчер имен не позволяет использовать пробелы при определении новых имен. Если в заголовке есть пробелы, Excel преобразует их в (_). Например, Соединенные Штаты будут названы United_States. Эти имена диапазонов будут использоваться в следующей формуле.
Шаг 3: Создайте условный выпадающий список

1. Выберите ячейку (здесь я выбираю E11) для вывода условного выпадающего списка, перейдите на вкладку Данные, выберите Проверка данных.

2. В диалоговом окне Проверка данных вам необходимо:

1) Оставайтесь на вкладке Параметры;
2) Выберите Список в раскрывающемся списке Разрешить;
3) Введите следующую формулу в поле Источник;
=IF($E$10="Japan",Japan,IF(E10="Tunisia",Tunisia,IF(E10="United States",United_States, France)))
4) Нажмите кнопку ОК.

specify the options in the dialog box to create a conditional drop-down list

Примечание:
Если вы используете Excel 2019 или более поздние версии, вы можете применить функцию ЕСЛИМН для оценки нескольких условий, которая делает то же самое, что и вложенные ЕСЛИ, но более четким способом. В этом случае вы можете попробовать следующую формулу ЕСЛИМН, чтобы получить тот же результат.
=IFS(E10="Japan",Japan,E10="Tunisia",Tunisia,E10="United States",United_States,E10="France", France)
В приведенных выше двух формулах,
1) E10 — это ячейка выпадающего списка, содержащая страны, которые вы указали на шаге 1;
2) Тексты в двойных кавычках обозначают значения, которые вы выберете в E10, а тексты без двойных кавычек — это имена диапазонов, которые вы указали на шаге 2;
3) Первый оператор ЕСЛИ ЕСЛИ($E$10="Япония",Япония) говорит Excel:
Если E10 равно «Япония», тогда отображаются только значения в именованном диапазоне «Япония» в этом выпадающем списке. Второй и третий операторы ЕСЛИ означают то же самое.
4) Последний оператор ЕСЛИ ЕСЛИ(E10="США",United_States, France) говорит Excel:
Если E10 равно «США», тогда отображаются только значения в именованном диапазоне «United_States» в этом выпадающем списке. В противном случае отображаются значения в именованном диапазоне «France».
5) Вы можете добавить больше операторов ЕСЛИ в формулу, если это необходимо.
6) Нажмите, чтобы узнать больше о функциях ЕСЛИ и ЕСЛИМН в Excel.
Результат

Всего несколько кликов для создания условного выпадающего списка с помощью Kutools для Excel

Указанные выше методы могут быть громоздкими для большинства пользователей Excel. Если вы хотите получить более эффективное и простое решение, настоятельно рекомендуется использовать функцию Динамический выпадающий список  Kutools для Excel, чтобы помочь вам создать условный выпадающий список всего за несколько кликов.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

Как видите, всю операцию можно выполнить всего за несколько кликов. Вам просто нужно:

1. В диалоговом окне выберите Режим A: 2 уровня в разделе Режим;
2. Выберите столбцы, на основе которых вы хотите создать условный выпадающий список;
3. Выберите диапазон вывода.
4. Нажмите ОК.
Примечание:
1) Kutools для Excel предлагает 30-дневную бесплатную пробную версию без ограничений, перейдите для скачивания.
2) Кроме создания двухуровневого выпадающего списка, вы также можете легко создать трех-, четырех- или пятиуровневый выпадающий список с помощью этой функции. Посмотрите это руководство: Быстро создать многоуровневый выпадающий список в Excel.

Лучшая альтернатива функции ЕСЛИ: функция ДВССЫЛ

Как альтернатива функциям ЕСЛИ и ЕСЛИМН, вы можете использовать комбинацию функций ДВССЫЛ и ПОДСТАВИТЬ для создания условного выпадающего списка, что проще, чем формулы, которые мы предоставили выше.

Возьмем тот же пример, что был использован в случае с несколькими условиями выше (как показано на GIF ниже). Здесь я покажу вам, как использовать комбинацию функций ДВССЫЛ и ПОДСТАВИТЬ для создания условного выпадающего списка в Excel.

1. В ячейке E10 создайте основной выпадающий список, содержащий все страны. Следуйте указанному выше шагу 1.

2. Присвойте имя диапазону ячеек для городов под каждой страной. Следуйте указанному выше шагу 2.

3. Используйте функции ДВССЫЛ и ПОДСТАВИТЬ для создания условного выпадающего списка.

Выберите ячейку (в данном случае E11) для вывода условного выпадающего списка, перейдите на вкладку Данные, выберите Проверка данных. В диалоговом окне Проверка данных вам необходимо:

1) Оставайтесь на вкладке Параметры;
2) Выберите Список в раскрывающемся списке Разрешить;
3) Введите следующую формулу в поле Источник;
=INDIRECT(SUBSTITUTE(E10," ","_"))
4) Нажмите кнопку ОК.

specify the options in the dialog box by INDIRECT function

Теперь вы успешно создали условный выпадающий список с помощью функций ДВССЫЛ и ПОДСТАВИТЬ.

Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions…
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...

Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
  • Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!

Все надстройки Kutools. Один установщик

Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.

Excel Word Outlook Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек