Перейти к основному содержанию

Условный раскрывающийся список с оператором IF (5 примеров)

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


Используйте оператор IF или IFS для создания условного раскрывающегося списка.

Этот раздел выполняет две функции: Функция IF и функция ЕСЛИ чтобы помочь вам создать условный раскрывающийся список на основе других ячеек в Excel с двумя примерами.

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

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

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

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

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

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

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

Шаг 2. Создайте условный раскрывающийся список с оператором IF.

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

2. К Данные вкладка, выберите проверка достоверности данных.

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

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

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

Условный выпадающий список готов.

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

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

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

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

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

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

2. В проверка достоверности данных диалоговое окно, вам необходимо:

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

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

Шаг 2. Назовите диапазон ячеек для городов каждой страны.

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

2. в Создать имена из выделенного диалоговое окно, только отметьте Верхний ряд и нажмите OK .

Ноты:
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) Нажмите OK .

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


Всего несколько кликов, чтобы создать условный раскрывающийся список с помощью Kutools for Excel

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

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

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

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

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

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

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

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

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

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

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

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

Лучшие инструменты для офисной работы

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

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

Описание


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

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