Як використовувати кілька операторів IF в Microsoft Excel

Оператор IF в Excel запускає логічний тест, який повертає одне значення, якщо умова виконується, і інше значення, якщо це не так. Використання одного оператора IF Excel може виконувати лише основні операції, але якщо ви вкладете кілька операторів IF, ви можете виконувати складні завдання. Єдиним недоліком є ​​те, що при побудові формули потрібно бути обережним, щоб уникнути помилок. Альтернативні функції Excel можуть виконувати ту ж роботу з меншим ризиком помилок. Вивчіть основи вкладених операторів IF в Excel та альтернативи, щоб знайти найкращий підхід для ваших потреб.

Функція IF в Excel по суті говорить: «Якщо це правда, поверніть значення x, а якщо ні, поверніть значення y». Наприклад, якщо ви перевіряли, чи є у вашому бізнесі різні відділи були занижені або перевищені бюджетом, ви можете порівняти їхні витрати з їхніми бюджетами за допомогою функції та повернути її «Перебюджет», якщо відділ витратив занадто багато, або «У межах бюджету», якщо він не зробив. Загалом, ви використовуєте оператори порівняння, щоб сформувати свою заяву, наприклад:

• = (дорівнює)

• (більше, ніж, величніше ніж, крутіший за)

• = (більше або дорівнює)

• < (менше ніж)
• <= (менше або дорівнює)
• <> (не дорівнює)

Розуміння синтаксису оператора IF Excel є вирішальним кроком до навчання правильному його використанню. Формат функції IF(логічний_тест, значення_якщо_істина, [значення_якщо_хибне]) де квадратні дужки вказують, що цей останній аргумент необов'язковий. Їх легко інтерпретувати: логічний_тест означає твердження, яке ви хочете перевірити, value_if_true це місце для того, що ви хочете, щоб Excel показав, чи є тест правдивим, і [value_if_false] це додаткове місце для того, що ви хочете, щоб Excel відображав, якщо тест хибний.

Цього достатньо, щоб ви могли використовувати формулу Excel для операторів if-then, що можна продемонструвати за допомогою прикладу «Перевищений бюджет/в межах бюджету» з попереднього прикладу. Уявіть, що ви маєте суму, витрачену різними відділами в стовпці B, і суму, передбачену бюджетом у стовпці C, зі значеннями, які починаються з другого рядка. У клітинку D2 ви вводите текст =IF(B2>C2, "Понад бюджет", "У межах бюджету") щоб повернути результат, який ви шукаєте (зверніть увагу, що вам потрібно використовувати лапки, щоб повернути текст всередині). У першій частині написано "якщо значення в B2 більше, ніж значення в C2" (якщо витрати більші за бюджет), у другій частині - "то поверніть текст Занадто бюджет а третя частина говорить «якщо ні, то поверніться В рамках бюджету."

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

Щоб правильно використовувати кілька операторів IF, подумайте про логіку того, що ви просите зробити Excel. По суті, після вашої умови (першого аргументу у функції) ви можете додати ще один оператор IF до value_if_true або value_if_false пробіли, щоб сказати Excel, що робити далі. Подумайте про це як про те, що Excel перевірить клітинку за другим критерієм, якщо відповідна клітинка не відповідає відповідати першому критерію (або дійсно, якщо він відповідає першому), і ви можете продовжувати цей процес довго час. Іншими словами, ви перетворюєте «якщо це, то те» на «якщо це і це, то те» або «якщо не це, а це, то те».

Уявіть, що ви дали учням тест, де 85 відсотків або вище є оцінкою A, від 70 до 85 відсотків це B, від 55 до 70 відсотків - це C, від 40 до 55 відсотків - це D і все, що нижче, - це Не вдалося. Оцінки наведені в колонці B, з рядка 2 по рядок 11. Ви можете використовувати ретельно розроблену функцію Excel IF з кількома умовами, щоб класифікувати бали учнів.

Перша частина проста (для оцінки B2, у цьому прикладі): =IF(B2>=85, "A") наказує Excel повернути A, якщо учень набрав 85 або вище. Вкладеність дозволяє додавати інші оцінки, використовуючи третій аргумент (що робити, якщо умова хибна), щоб додати додаткові умови: =IF(B2>=85, "A", IF(B2>=70, "B")), який повідомляє Excel, щоб повернути A для балів 85 або вище, але якщо ні, повернути B, якщо оцінка перевищує 70. Продовжуйте таким чином, щоб завершити формулу, щоразу використовуючи третій аргумент для нового оператора IF: =IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Fail")) ))

Це виглядає складно, тому розбивка має закріпити ідею. Перші два аргументи говорять «якщо оцінка в B2 дорівнює 85 або більше, то студент отримує оцінку A», а третій говорить: «в іншому випадку перевірте наступну умову». The другий IF веде до цього твердження: "(для учнів, які набрали менше 85 балів), якщо оцінка в B2 дорівнює 70 або вище, то студент отримує B, а якщо ні, перевірте наступна умова." Третя IF говорить: "(для студентів, які набрали менше 70 балів), якщо оцінка в B2 становить 55 або вище, студент отримує C, а якщо ні, перевірте наступна умова» і четверта говорить «(для учнів, які набрали менше 55 балів), якщо оцінка в B2 вища за 40, студент отримує D, якщо ні, учень не вдається».

Якщо ви правильно ввели формулу для оператора IF, він поверне один із параметрів, які ви надали в Excel. Однак, якщо ви допустите помилку, формула може повернути a 0 або #NAME? у клітинку, що містить вашу формулу.

А 0 помилка означає, що або value_if_true або value_if_false аргумент порожній. Виправте цю проблему, переконавшись, що значення введено в обидва аргументи або принаймні в value_if_true аргумент. The value_if_false аргумент можна повністю видалити, і він поверне FALSE, якщо ви не поставите другу кому після value_if_true поле.

А #NAME? Помилка повідомляє про те, що ви, імовірно, написали неправильно або щось неправильно ввели у формулі. Це може бути трохи важче виправити, оскільки це може бути будь-яка частина формули, але найкраща порада – перевірити все ще раз або написати формулу ще раз.

У попередньому прикладі формула вкладеної функції IF стала досить довгою, що є одним із основних недоліків використання кількох операторів IF в Excel. Вони можуть стати складними, і легко зробити помилку, тому що ви повинні ретельно слідувати логіці, щоб переконатися, що все працює так, як ви плануєте. Microsoft зазначає, що неправильно вкладена формула може навіть повертати правильний результат більшу частину часу, але неправильні результати в рідкісних випадках. Їх важко помітити.

Неправильне розташування умов є поширеною помилкою. У прикладі оціночних тестів, якщо ви почали з нижчого бала =IF(B2>=40, "D", …" і перейшов до списку інших у порядку зростання, навіть 100 балів дасть D. Це тому, що перша умова перевіряється першою, тож якщо ви почали з "якщо клітинка B2 дорівнює 40 або вище, поверніть D" він оцінить будь-який бал 40 або більше як D, не перевіряючи нічого іншого, оскільки він відповідає першому хвороба. Це чудовий приклад того, чому вам потрібно до кінця слідувати логіці вашої формули.

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

Технічно ви можете вкладати до 64 функцій IF в одну формулу, але Microsoft не рекомендує використовувати таку функцію з наведених вище причин. Все швидко ускладнюється, і, швидше за все, якщо ви намагаєтеся вкласти більше 10 операторів IF, ймовірно, є кращий інструмент для цієї роботи, який уже включено в Excel.

Найзручнішою альтернативою функції IF є функція IFS, яка є версією функція, яка виконує те саме завдання, що і вкладення в одну функцію, і може обробляти до 127 умови. Функція IFS має простіший синтаксис: =IFS(логічний_тест1, значення_якщо_правда1, [логічний_тест2, значення_якщо_істина2], [логічний_тест3, значення_якщо_правда3]…) і так далі. По суті, це працює як вкладені оператори IF, за винятком того, що наступні оператори IF займають простір після "значення, якщо істина", без необхідності знову викликати функцію.

Формулу в прикладі тестової оцінки можна записати набагато простіше за допомогою функції IFS. Замість:

=IF(B2>=85, "A", IF(B2>=70, "B",IF(B2>=55, "C", IF(B2>=40, "D", "Fail")) ))

просто напиши

=IFS(B2>=85, "A", B2>=70, "B", B2>=55, "C", B2>=40, "D", TRUE, "Fail")

The ПРАВДА на передостанньому місці каже "якщо жодне з інших значень не виконано, то поверніть наступне значення".

Ця функція доступна лише в Excel 2019 або Excel для Office 365.

Функція VLOOKUP в Excel часто є більш простою альтернативою кільком операторам IF і доступна в будь-якій версії Excel з 2007 року. Він не ідеально підходить для всіх ситуацій, оскільки повертає результати для точного збігу або для найближчий число, тому його не можна використовувати для оцінок, як у попередньому прикладі. Однак у багатьох ситуаціях це може заощадити вам багато роботи з написанням формули, хоча вам доведеться побудувати таблицю з можливими значеннями в одному у стовпці, наприклад, продукти, які продає ваш магазин, і те, що ви хочете, щоб функція повертала, наприклад ціна продукту, в іншому стовпці, для приклад.

Синтаксис для VLOOKUP такий =VLOOKUP(значення_шукання, масив_таблиці, номер_індексу стовпця, [діапазон_пошуку]) і вивчення кожного з компонентів покаже вам, як ним користуватися. The пошукове_значення Аргумент — це значення, яке ви хочете перевірити, яке має бути клітинкою в тому самому стовпці, що й початок таблиці, щодо якої ви хочете перевірити, над або під нею. The table_array – це діапазон клітинок, що містить вашу таблицю інформації, наприклад продукти та відповідні ціни та номер_індексу стовпця – це стовпець, у якому Excel має шукати значення для повернення 1 вказуючи крайній лівий стовпець, 2 із зазначенням наступного і так далі. нарешті, [range_lookup] є необов’язковим, але введіть «TRUE», якщо ви хочете, щоб Excel базував відповідь на найближчому значенні, або «FALSE», якщо вам потрібна лише точна відповідність.