KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

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

АвторSiluviaДата изменения

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

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

Пример файла:

значок ExcelНажмите, чтобы скачать пример файла

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

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

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

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

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

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

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

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

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

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

укажите параметры в диалоговом окне

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

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

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

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

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

укажите параметры в диалоговом окне с использованием оператора ЕСЛИ

Примечание: Эта формула указывает 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) Нажмите кнопку ОК. См. снимок экрана:

укажите параметры в диалоговом окне

Теперь готов раскрывающийся список со всеми странами.

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

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

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

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

в диалоговом окне установите флажок «Верхняя строка»

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

создайте несколько именованных диапазонов этим действием

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

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

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

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

укажите параметры в диалоговом окне для создания условного раскрывающегося списка

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

Более удобная альтернатива функции ЕСЛИ: функция ДВССЫЛ

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

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

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

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

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

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

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

укажите параметры в диалоговом окне с помощью функции ДВССЫЛ

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

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

🤖KUTOOLS AI Помощник: Революционизируйте Анализ данных на основе:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  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.

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