Хоча в Excel функція IF необхідна для базових логічних тестів, складні умови часто вимагають вкладених операторів IF для покращеної обробки даних. У цьому вичерпному посібнику ми детально розглянемо основи вкладеного IF, від синтаксису до практичних застосувань, включаючи комбінації вкладеного IF з умовами AND/OR. Крім того, ми поділимося тим, як покращити читабельність вкладених функцій IF, а також порадами щодо вкладених IF і дослідимо такі потужні альтернативи, як VLOOKUP, IFS тощо, щоб зробити складні логічні операції простішими у використанні та ефективнішими.

• Синтаксис вкладеного IF
• Практичні приклади вкладених IF
• Вкладено, якщо з умовою І / АБО

• Зробити вкладений IF зручним для читання
• Порядок вкладених функцій IF
• Цифри та текст слід трактувати по-різному
• Обмеження вкладеного IF

• Використання VLOOKUP
• Використання IFS
• Використання ВИБРАТИ та СПІВПРАЦІ

Функція Excel IF проти вкладених операторів IF

Функція IF і вкладені оператори IF в Excel служать подібним цілям, але значно відрізняються за складністю та застосуванням.

Використання вкладеного IF

У цьому розділі демонструється базове використання вкладених операторів IF у Excel, зокрема синтаксис, практичні приклади та способи їх використання з умовами І чи АБО.

Синтаксис вкладеного IF

Розуміння синтаксису функції є основою для її правильного та ефективного застосування в Excel. Почнемо з синтаксису вкладених операторів if.

синтаксис:

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

Аргументи:

• Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
• Result1: This is the value returned if Condition1 is TRUE.
• Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
• Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
• Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
  
  In short, this expression can be interpreted as follows:
  Test condition1, if TRUE, return result1, if FALSE,
  test condition2, if TRUE, return result2, if FALSE,
  test condition3, if TRUE, return result3, if FALSE,
  return result4

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

Практичні приклади вкладених IF

Тепер давайте зануримося у використання вкладеного IF на двох практичних прикладах.

Приклад 1: Система оцінювання

Як показано на знімку екрана нижче, припустімо, що у вас є список балів студентів і ви хочете виставити оцінки на основі цих балів. Ви можете використовувати вкладений IF для виконання цього завдання.

Виберіть порожню клітинку (у цьому випадку C2), введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат. Потім перетягніть Ручка заповнення вниз, щоб отримати решту результатів.

=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))

Приклад 2: Розрахунок комісії з продажу

Уявіть собі сценарій, коли торгові представники отримують різні ставки комісійної винагороди залежно від їхніх продажів. Як показано на знімку екрана нижче, ви хочете обчислити комісію продавця на основі цих різних порогових значень продажів, і вкладені оператори IF можуть допомогти вам у цьому.

Виберіть порожню клітинку (у цьому випадку C2), введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат. Потім перетягніть Ручка заповнення вниз, щоб отримати решту результатів.

=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))

Вкладено, якщо з умовою І / АБО

У цьому розділі я зміню наведений вище перший приклад «системи оцінювання», щоб продемонструвати, як поєднати вкладене ЯКЩО з умовою І або АБО в Excel. У переглянутому прикладі оцінювання я ввів додаткову умову на основі «Рівень відвідуваності».

Використання вкладеного if з умовою AND

Якщо студент задовольняє як критерії оцінки, так і критерії відвідування, він отримає підвищення оцінки. Наприклад, студент, чий бал становить 60 або вище, а рівень відвідуваності становить 95% або вище, отримає оцінку на один рівень, наприклад, з A до A+, B до B+ тощо. Однак, якщо рівень відвідуваності нижче 95%, оцінювання буде відповідати оригінальним критеріям на основі балів. У таких випадках нам потрібно використовувати вкладений оператор IF з умовою AND.

Виберіть порожню комірку (у цьому випадку D2), введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат. Потім перетягніть Ручка заповнення вниз, щоб отримати решту результатів.

=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Використання вкладеного if з умовою OR

У цьому випадку оцінка студента буде підвищена на один рівень, якщо його бал 95 або вище, або якщо рівень відвідуваності становить 95% або більше. Ось як ми можемо це зробити за допомогою вкладених умов IF та OR.

Виберіть порожню комірку (у цьому випадку D2), введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат. Потім перетягніть Ручка заповнення вниз, щоб отримати решту результатів.

=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Поради та підказки для вкладеного IF

Цей розділ охоплює чотири корисні поради та підказки щодо вкладеного IF.

Зробити вкладений IF зручним для читання

Типовий вкладений оператор IF може виглядати компактним, але його важко розшифрувати.

У наведеній нижче формулі складно швидко визначити, де закінчується одна умова і починається інша, особливо в міру того, як зростає складність.

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Рішення: додавання розривів рядків і відступів

Щоб полегшити читання вкладеного IF, ви можете розбити формулу на кілька рядків з кожним вкладеним IF у новому рядку. У формулі просто помістіть курсор перед IF і натисніть клавіші Alt + Enter.

Після порушення наведеної вище формули вона буде показана таким чином:

=IF(A2>=90, "A",
      IF(A2>=80, "B",
          IF(A2>=70, "C",
              IF(A2>=60, "D", "F")))
)

Цей формат робить більш зрозумілим, де знаходяться кожна умова та відповідний вихід, покращуючи читабельність формули.

Порядок вкладених функцій IF

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

Правильна формула

У прикладі системи оцінювання ми використовуємо наступну формулу для призначення оцінок на основі балів.

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

Excel послідовно обчислює умови у вкладеній формулі IF, від першої умови до останньої. Ця формула спочатку перевіряє найвищий поріг (>=90 для «A»), а потім переходить до нижчих порогів. Це гарантує, що оцінка порівнюється з найвищою оцінкою, на яку вона відповідає. Якщо перша умова вірна (A2>=90), повертається «A» і не оцінюються подальші умови.

Неправильна впорядкована формула

Якби порядок умов був протилежним, починаючи з найнижчого порогу, він повернув би неправильні результати.

=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))

У цій неправильній формулі оцінка 95 негайно відповідатиме першій умові B2>=60 і їй буде неправильно присвоєно оцінку «D».

Цифри та текст слід трактувати по-різному

У цьому розділі буде показано, як по-різному обробляються числа та текст у вкладених операторах IF.

Номери

Числа використовуються для арифметичних порівнянь і обчислень. У вкладених операторах IF ви можете безпосередньо порівнювати числа за допомогою таких операторів, як >, <, =, >= і <=.

текст

У вкладених операторах IF текст має бути взяті в подвійні лапки. Дивіться A, B, C, D і F у такій формулі:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Обмеження вкладеного IF

У цьому розділі наведено кілька обмежень і недоліків вкладеного IF.

Складність і читабельність:

Хоча Excel дозволяє вкладати до 64 різних функцій IF, робити це зовсім не бажано. Чим більше рівнів вкладеності, тим складнішою стає формула. Це може призвести до формул, які важко читати, розуміти та підтримувати.

Схильний помилятися:

Крім того, складні вкладені оператори IF можуть стати схильними до помилок, і їх буде складно налагодити або змінити.

Важко розширити або масштабувати:

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

Розуміння цих обмежень є ключовим для ефективного використання вкладених операторів IF в Excel. Часто поєднання вкладених IF з іншими функціями або пошук альтернативних підходів може призвести до більш ефективних і придатних для обслуговування рішень.

Альтернативи Nested IF

У цьому розділі наведено кілька функцій у Excel, які можна використовувати як альтернативу вкладеним операторам IF.

Використання VLOOKUP

Ви можете використовувати функцію VLOOKUP замість вкладених операторів IF, щоб виконати наведені вище два практичні приклади. Ось як ви можете це зробити:

Приклад 1: Система оцінювання за допомогою VLOOKUP

Тут я покажу, як використовувати VLOOKUP для призначення оцінок на основі балів.

Крок 1. Створіть таблицю пошуку для оцінок

По-перше, вам потрібно створити таблицю пошуку (наприклад, E1:F6 у цьому випадку) для діапазону балів і відповідних оцінок. примітки: Оцінки в першому стовпчику таблиці повинні бути відсортовані в порядку зростання.

Крок 2. Застосуйте функцію VLOOKUP для призначення оцінок

Виберіть порожню комірку (у цьому випадку C2), введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у ключ до першого класу. Виберіть цю клітинку формули та перетягніть її Ручка заповнення вниз, щоб отримати решту оцінок.

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

Приклад 2: Розрахунок комісії з продажу за допомогою VLOOKUP

Ви також можете використовувати VLOOKUP для виконання розрахунку комісії з продажу в Excel. Будь ласка, зробіть наступне.

Крок 1. Створіть таблицю пошуку для оцінок

По-перше, вам потрібно створити таблицю пошуку для продажів і відповідної ставки комісії, наприклад E2:F4 у цьому випадку. примітки: Продажі в першому стовпці таблиці повинні бути відсортовані в порядку зростання.

Крок 2. Застосуйте функцію VLOOKUP для призначення оцінок

Виберіть порожню комірку (у цьому випадку C2), введіть наступну формулу та натисніть клавішу Enter, щоб отримати першу комісію. Виберіть цю комірку формули та перетягніть її маркер заповнення вниз, щоб отримати решту результатів.

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

Використання IFS

Команда Функція IFS спрощує процес, усуваючи потребу у вкладеності та полегшуючи читання та керування формулами. Це покращує читабельність і спрощує обробку кількох умовних перевірок. Щоб скористатися функцією IFS, переконайтеся, що ви використовуєте Excel 2019 або новішу версію або маєте підписку на Office 365. Давайте подивимося, як це можна застосувати на практичних прикладах.

Приклад 1: Система оцінювання за допомогою IFS

Припускаючи ті самі критерії оцінювання, що й раніше, функцію IFS можна використовувати таким чином:

Виберіть порожню комірку, наприклад C2, введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати перший результат. Виберіть цю клітинку результату та перетягніть її Ручка заповнення вниз, щоб отримати решту результатів.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")

Приклад 2: Розрахунок комісії з продажу за допомогою IFS

Для сценарію розрахунку комісії з продажу функція IFS застосовується таким чином:

Виберіть порожню комірку, наприклад C2, введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати перший результат. Виберіть цю клітинку результату та перетягніть її Ручка заповнення вниз, щоб отримати решту результатів.

=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)

Використання ВИБРАТИ та СПІВПРАЦІ

Підхід CHOOSE і MATCH може бути ефективнішим і легшим у управлінні порівняно з вкладеними операторами IF. Цей метод спрощує формулу та робить оновлення або зміни більш простими. Нижче я покажу, як використовувати комбінацію функцій CHOOSE і MATCH для обробки двох практичних прикладів у цій статті.

Приклад 1: Система оцінювання з ВИБЕРІ та СПІВПРАЦІ

Ви можете використовувати комбінацію функцій CHOOSE і MATCH для призначення оцінок на основі різних балів.

Крок 1. Створіть масив пошуку зі значеннями пошуку

По-перше, вам потрібно створити діапазон комірок, що містить порогові значення, які шукатиме MATCH, наприклад $E$2:$E$6 у цьому випадку. примітки: Числа в цьому діапазоні мають бути відсортовані в порядку зростання, щоб функція MATCH працювала правильно під час використання приблизного типу відповідності.

Крок 2. Застосуйте CHOOSE та MATCH для призначення оцінок

Виберіть порожню комірку (у цьому випадку C2), введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у ключ до першого класу. Виберіть цю клітинку формули та перетягніть її Ручка заповнення вниз, щоб отримати решту результатів.

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

Приклад 2: Розрахунок комісії з продажу за допомогою IFS

Використання комбінації CHOOSE і MATCH для розрахунку комісії з продажу також може бути ефективним, особливо якщо ставки комісії базуються на визначених порогових значеннях продажів. Давайте подивимося, як ми можемо зробити.

Крок 1. Створіть масив пошуку зі значеннями пошуку

По-перше, вам потрібно створити діапазон комірок, що містить порогові значення, які шукатиме MATCH, наприклад $E$2:$E$4 у цьому випадку. примітки: Числа в цьому діапазоні мають бути відсортовані в порядку зростання, щоб функція MATCH працювала правильно під час використання приблизного типу відповідності.

Крок 2: застосуйте CHOOSE та MATCH, щоб отримати результати

Виберіть порожню комірку (у цьому випадку C2), введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у ключ до першого класу. Виберіть цю клітинку формули та перетягніть її Ручка заповнення вниз, щоб отримати решту результатів.

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

Підсумовуючи, оволодіння вкладеними операторами IF у Excel є цінною навичкою, яка покращує вашу здатність обробляти складні логічні сценарії під час аналізу даних і процесів прийняття рішень. Хоча вкладені IF є потужними для складних логічних операцій, важливо пам’ятати про їхні обмеження. Простіші альтернативи, такі як VLOOKUP, IFS і CHOOSE with MATCH, можуть забезпечити більш оптимізовані рішення в певних сценаріях. Озброївшись цією інформацією, тепер ви можете з упевненістю застосовувати найбільш прийнятні методи Excel для завдань аналізу даних, забезпечуючи ясність, точність і ефективність ваших електронних таблиць. Для тих, хто прагне глибше заглибитися в можливості Excel, наш веб-сайт може похвалитися великою кількістю посібників. Дізнайтеся більше порад і підказок щодо Excel тут.

Статті по темі