Note: The other languages of the website are Google-translated. Back to English
Увійти Реєстрація

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

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

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

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

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

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

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

Як показано на зображенні нижче, ви можете легко перемикатися між містами в двох країнах "Сполучені Штати і France» у розкривному списку. Давайте подивимося, як використовувати функцію 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 містить міста в France.
Результат

Умовний спадний список завершено.

Як показано на 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 дорівнює “Сполучені Штати", то лише значення в іменованому діапазоні "Сполучені Штати» відображаються в цьому спадному списку. В іншому випадку він відображає значення в іменованому діапазоні "France".
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 для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL