Логічна функція IF в Excel (якщо) – одна з найбільш популярних. Вона перевіряє чи виконується умова, і повертає один результат (значення чи іншу формулу) якщо умова виконалася, або інший результат – якщо ні.
Оскільки спектр застосування функції IF досить широкий, то представленому матеріалі буде розібрано кілька прикладів. Показано як функція працює для однієї умови та розглянуто роботу IF Excel на прикладах з кількома умовами.
Функція IF у Excel
Логічна функція IF містить 3 аргументи та має наступний синтаксис.
IF (лог_вираз ; значення_якщо_істина ; значення_якщо_хибність)
лог_вираз – це умова, яка перевіряється. Наприклад, B5<75. Якщо значення в клітинці B5 дійсно менше 75, то в пам’яті Excel формується відповідь TRUE (істина) і функція повертає те, що вказано у наступному аргументі. В іншому випадку, в пам’яті формується відповідь FALSE (хибне) і вказується значення з останнього поля.
значення_якщо_істина – вираз, число або формула, що повертається під час виконання умови, вказаної в першому аргументі.
значення_якщо_хибність – це альтернативне значення, текст або вираз, які повертаються у тому випадку, коли умова не виконана. Якщо значення цього аргументу не заповнювати, то функція Ексель поверне значення false.
Розглянемо нескладний приклад використання функції IF в Excel. Потрібно перевірити, у кого зі студентів коледжу середній бал більший або дорівнює 7. Якщо більше, то формула має повернути значення «Отримує», інакше – «Немає». Нижче показано алгоритм розрахунку із результатом.
Встановлюємо курсор у клітинку, де потрібно створити формулу, наприклад, C2 і викликаємо Майстер функцій. Знаходимо функцію IF та натискаємо на неї.
Відкривається діалогове вікно, де для розв’язання цієї задачі потрібно заповнити три поля:
- У полі «Лог_вираз» вказати умову, у нашому випадку: B2>=7. Адресу клітинку можна вказати з допомогою мишки.
- У полі «Значення_якщо_істина» вказуємо «Отримує». Якщо не поставити лапки, вони потім самі з’являться.
- У полі «Значення_якщо_хибність» вказуємо «Немає», тобто дані, якщо умова НЕ виконана.
- Заповнивши всі поля, натисніть OK.
Отримати потрібний результат із застосуванням функції IF можна і без використання майстра функцій. Для цього в клітинці С2 потрібно написати формулу: =IF(B2>=7;”Отримує”;”Немає”)
Середній бал першого студента (ки) дорівнює 9.5, тобто умова про те, що він більше 7, виконується. Отже, функція повертає те значення, що вказано у наступному аргументі – «Отримує». Щоб отримати результат для інших рядків, потрібно встановити курсор на клітинці C2, затиснути ЛКМ та потягнути вниз.
Якщо середній бал буде менше 7, умова не виконається і у клітинці з’явиться значення, що вказане у третьому аргументі – «Немає». Саме так працює функція IF.
Ми розглянули простий приклад. На практиці формулу Excel IF можна застосовувати у більш складних перевірках. Розглянемо приклад, коли є середнє значення продажів товарів за тиждень та їх залишки на поточний день. Власнику потрібно дізнатись, скільки товару залишиться через 2 тижні. Зробити це можна шляхом віднімання «Залишків на сьогодні» від подвійного середнього значення продажів за тиждень.
Все нібито добре, проте наявність від’ємних залишків виглядає зовсім не логічно, оскільки кількість товарів не може бути менше 0. Щоб виправити цю ситуацію, потрібно від’ємні значення замінити на «0».
Тут як раз допоможе формула IF Ексель. Вона перевірятиме отримане за формулою значення, і якщо це значення буде менше 0, то у клітинці з’явиться нуль, в іншому випадку – з’явиться деяке додатне число, отримане в результаті розрахунку. Власне маємо той самий логічний ланцюжок, тільки цього разу, у якості умови, використовується формула.
Формула IF в Excel з кількома умовами
У розрахунках Ексель часто доводиться працювати не з двома умовами, а 3, 4 тощо. В таких випадках також можна застосувати функцію IF (якщо), але тепер її потрібно вкладати одну в одну, встановлюючи всі умови по черзі. Розглянемо наступний приклад.
Торгових агентів потрібно преміювати в залежності від об’єму виконаного плану продажів. Умови для нарахування надбавки, наступні:
- При виконанні плану менш ніж на 90% – премія відсутня;
- Від 95% до 100% – премія 15%;
- Якщо план перевиконано, премія 30%.
З умови задачі видно, що потрібно використати три варіанти для преміювання. Для того, щоб всіх їх задіяти у формулі, потрібно зробити таку логічну структуру. Якщо перша умова виконається – то формула поверне перший варіант (тобто без премії), інакше, якщо буде виконана 2 умова, то повернеться другий варіант (15% премії), в іншому випадку якщо… і т.д.
Насправді можна задавати будь-яку кількість умов. Головне розуміти, що в кінці формули завжди залишається альтернативний варіант, який не відповідає жодній із вказаних умов (як третій аргумент у звичайній формулі IF). У підсумку отримаємо таку формулу і результат.
Комбінація функцій IF працює таким чином, що коли якась умова вже виконана, то інші не перевіряються. Саме тому вони повинні вказуватись у правильній послідовності. Якщо б в нашій задачі перевірка почалася з умови B2<1, тоді б умову B2<0.9, Excel би просто «не помітив», оскільки ті числа входять і до інтервалу B2<1, який перевірився б першим. У підсумку результат міг би бути невірним.
При використанні вкладених функцій IF, легко заплутатися, тому в таких випадках бажано дивитися на підказку або скористатися майстром функцій.
Функція IFS в Excel 2016
Функція Ексель IF дуже добре виконує поставлені перед нею задачі. Проте, як вже помітили вище, записувати довгий ланцюжок умов дуже втомливо, до того ж це підвищує ймовірність допущення помилки в самій умові та робить складним перевірку такої формули. Особливо це актуально в тих випадках, коли у формулі застосовується багато складних розрахунків.
У MS Excel 2016 з’явилася нова функція IFS. По суті, це та сама функція IF (якщо), тільки створена спеціально для тих випадків, коли потрібно перевіряти безліч умов. З цією функцією немає потреби кілька разів писати IF і рахувати всі відкриті дужки. Тепер потрібне лише вказати всі умови та наприкінці закрити одну дужку.
Принцип роботи IFS наступний. Візьмемо приклад з розрахунком премій і застосуємо нову формулу Excel IFS.
На скриншоті вище бачимо, що тепер формула для підрахунку премій виглядає набагато простіше та зрозуміліше. Щоб зручніше було використовувати функцію IFS, можна використовувати майстер функцій.
Умови, як і раніше, потрібно перераховувати в правильному порядку, інакше може відбутися перекриття діапазонів. Варто відзначити наступну особливість цієї функції.
На відміну від звичайної функції IF, остання альтернативна умова має бути задана обов’язково.
У IF вказується тільки альтернативне значення, яке виконається в тому випадку, якщо жодна з вказаних умов не виконається. А от в IFS необхідно вказати безпосередньо саму умову, яка в нашому прикладі була б B2>= 1, інакше може бути помилка N/A. Проте, щоб цього не робити, у полі з останньою умовою можна написати TRUE, вказуючи таким чином програмі Excel, що якщо всі перелічені умови хибні, то настає TRUE і потрібно вказати останнє альтернативне значення.
Тепер ви знаєте, як користуватися функцією IF в Excel, а також її більш сучасним варіантом для багатьох умов IFS. Якщо у вас залишилися питання, то залишайте їх у коментарях або оцініть матеріал, для нас це також важливо.
Круто. Зрозуміло