Перейти до основного матеріалу

Умовний розкривний список із оператором IF (5 прикладів)

Якщо вам потрібно створити розкривний список, який змінюватиметься залежно від того, що ви вибрали в іншій комірці, допоміжним рішенням може бути додавання умови до розкривного списку. Під час створення умовного розкривного списку використання оператора IF є інтуїтивно зрозумілим методом, оскільки він завжди використовується для перевірки умов у Excel. Цей підручник демонструє 5 методів, які допоможуть вам у покроковому створенні умовного розкривного списку в Excel.

Отримати файл зразка:

Клацніть, щоб завантажити зразок файлу

Використовуйте оператор IF або IFS, щоб створити умовний розкривний список

Цей розділ забезпечує дві функції: Функція IF і Функція IFS щоб допомогти вам створити умовний розкривний список на основі інших комірок у Excel із двома прикладами.

Додайте одну умову, наприклад дві країни та їхні міста

Як показано на зображенні нижче, ви можете легко перемикатися між містами в двох країнах "Сполучені Штати та Франція» у розкривному списку. Давайте подивимося, як використовувати функцію IF, щоб це зробити.

Крок 1: Створіть головний спадний список

По-перше, вам потрібно створити головний розкривний список, який буде служити основою для вашого умовного розкривного списку.

1. Виберіть комірку (у цьому випадку E2), куди потрібно вставити головний розкривний список. Перейти до дані вкладка, виберіть Перевірка достовірності даних.

2 В Перевірка достовірності даних діалоговому вікні, виконайте ці дії, щоб налаштувати параметри.

1) Залишайтеся в Налаштування вкладка;
2) Виберіть список в дозволяти коробка;
3) У полі «Джерело» виберіть діапазон клітинок, що містять значення, які ви хочете відобразити в розкривному списку (тут я вибираю заголовки таблиці)
4) Клацніть на OK кнопку. Дивіться знімок екрана:

Крок 2. Створіть умовний спадний список із оператором IF

1. Виберіть діапазон клітинок (у цьому випадку E3:E6), куди потрібно вставити умовний розкривний список.

2. До дані вкладка, виберіть Перевірка достовірності даних.

3 В Перевірка достовірності даних діалогове вікно, вам потрібно налаштувати наступним чином.

1) Залишайтеся в Налаштування вкладка;
2) Виберіть список в дозволяти випадаючий список;
3) Введіть наступну формулу в Source коробка;
=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) Наведений вище метод працює лише для двох країн та їхніх міст, оскільки функція IF використовується для перевірки умови та повернення одного значення, якщо умова виконується, і іншого значення, якщо вона не виконується.
2) Якщо до цього випадку додати більше країн і міст, можуть допомогти наступні вкладені функції IF і функції IFS.

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

Як показано на gif-зображенні нижче, є дві таблиці. Таблиця з одним стовпцем містить різні країни, тоді як таблиця з кількома стовпцями містить міста в цих країнах. Тут нам потрібно створити умовний розкривний список, який містить міста, які змінюватимуться відповідно до країни, яку ви виберете в E10. Для завершення виконайте наведені нижче дії.

Крок 1: Створіть розкривний список із усіма країнами

1. Виберіть клітинку (тут я вибираю E10), де потрібно відобразити країну, перейдіть до дані вкладка, клацніть Перевірка достовірності даних.

2. У Перевірка достовірності даних у діалоговому вікні потрібно:

1) Залишайтеся в Налаштування вкладка;
2) Виберіть список в дозволяти випадаючий список;
3) Виберіть діапазон, що містить країни в Source коробка;
4) Клацніть на OK кнопку. Дивіться знімок екрана:

Розкривний список містить усі країни.

Крок 2: назвіть діапазон клітинок для міст у кожній країні

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

2 В Створіть імена з виділення діалогове вікно, лише перевірити Верхній ряд та натисніть кнопку OK кнопки.

Примітки:
1) Цей крок дає змогу створити декілька іменованих діапазонів одночасно. Тут заголовки рядків використовуються як назви діапазонів.

2) За замовчуванням Менеджер імен не допускає пробілів під час визначення нових імен. Якщо в заголовку є пробіли, Excel перетворить їх на (_) замість цього. Наприклад, Сполучені Штати буде названо Сполучені Штати. Ці назви діапазонів використовуватимуться в наступній формулі.
Крок 3: Створіть умовний спадний список

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

2 В Перевірка достовірності даних у діалоговому вікні потрібно:

1) Залишайтеся в Налаштування вкладка;
2) Виберіть список в дозволяти випадаючий список;
3) Введіть наступну формулу в Source коробка;
=IF($E$10="Japan",Japan,IF(E10="Tunisia",Tunisia,IF(E10="United States",United_States, France)))
4) Клацніть на OK кнопки.

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

Лише кілька клацань, щоб створити умовний розкривний список за допомогою Kutools для Excel

Наведені вище методи можуть бути громіздкими для більшості користувачів Excel. Якщо вам потрібне більш ефективне та просте рішення, то Динамічний розкривний список особливість Kutools для Excel настійно рекомендується, щоб допомогти вам створити умовний спадний список лише кількома клацаннями.

Як бачите, всю операцію можна виконати всього за кілька кліків. Вам просто потрібно:

1. У діалоговому вікні виберіть Режим A: 2 рівні в режим розділ;
2. Виберіть стовпці, на основі яких потрібно створити умовний розкривний список;
3. Виберіть вихідний діапазон.
4. клацання OK.
примітки:
1) Kutools для Excel пропонує 30-денна безкоштовна пробна версія без обмежень, перейти до завантаження.
2) Окрім створення 2-рівневого розкривного списку, за допомогою цієї функції ви можете легко створити розкривний список від 3 до 5 рівнів. Подивіться на цей посібник: Швидко створіть розкривний список декількох рівнів у Excel.

Краща альтернатива функції IF: функція INDIRECT

Як альтернативу функціям IF та IFS можна використовувати комбінацію НЕПРАВИЛЬНО та ЗАМІННИК функції для створення умовного розкривного списку, який є простішим, ніж формули, наведені вище.

Візьмемо той самий приклад, який використовувався в кількох умовах вище (як показано на зображенні gif нижче). Тут я покажу вам, як використовувати комбінацію функцій INDIRECT і SUBSTITUTE для створення умовного спадного списку в Excel.

1. У комірці E10 створіть головний розкривний список із усіма країнами. Виконайте наведений вище крок 1.

2. Назвіть діапазон комірок для міст у кожній країні. Виконайте наведений вище крок 2.

3. Використовуйте функції INDIRECT і SUBSTITUTE, щоб створити умовний розкривний список.

Виберіть клітинку (у цьому випадку E11), щоб вивести умовний розкривний список, перейдіть до дані вкладка, виберіть Перевірка достовірності даних, в Перевірка достовірності даних у діалоговому вікні потрібно:

1) Залишайтеся в Налаштування вкладка;
2) Виберіть список в дозволяти випадаючий список;
3) Введіть наступну формулу в Source коробка;
=INDIRECT(SUBSTITUTE(E10," ","_"))
4) Клацніть на OK кнопки.

Тепер ви успішно створили умовний розкривний список за допомогою функцій INDIRECT і SUBSTITUTE.

Найкращі інструменти продуктивності офісу

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

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

Опис

Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

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