Функція IF виконує логічне порівняння значення з результатом, який очікується. Для цього перевіряється умова й повертається результат True або False.
-
=IF(якась умова істинна; то виконати певну дію; інакше виконати іншу дію)
Отже, інструкція IF може мати два результати. Перший результат буде отримано, якщо результат порівняння – True, другий – якщо порівняння повертає False.
Інструкції IF надзвичайно надійні та виступають основою для багатьох моделей електронних таблиць, але можуть виявитися причиною багатьох проблем в електронних таблицях. Найкраще застосовувати інструкцію IF до умов із невеликою кількістю варіантів, наприклад "Чоловік" або "Жінка", "Так", "Ні" чи "Можливо" тощо, але іноді сценарії обчислень можуть бути складніші та може знадобитися вкласти* більше трьох функцій IF.
* "Вкладення" – це практика об'єднання кількох функцій в одній формулі.
Функція IF, яка відноситься до логічних функцій, повертає одне значення, якщо умова виконується, або інше, якщо вона не виконується.
Синтаксис
IF(логічний_вираз;значення_якщо_істина;[значення_якщо_хибність])
Наприклад:
-
=IF(A2>B2;"Бюджет перевищено";"OK")
-
=IF(A2=B2;B4-A4;"")
|
Ім’я аргументу |
Опис |
|
логічний_вираз (обов’язковий) |
Умова, яку потрібно перевірити. |
|
значення_якщо_істина (обов’язковий) |
Значення, яке повертається, якщо результат умови лог_вираз має значення TRUE. |
|
значення_якщо_хибність (необов’язковий) |
Значення, яке повертається, якщо результат умови лог_вираз має значення FALSE. |
Примітки
Хоча в Excel можна вкласти до 64 різних функцій IF, робити це зовсім не бажано. Чому?
-
Щоб належним чином поєднати кілька інструкцій IF, необхідно ретельно все продумати так, щоб за їхньою логікою правильно обчислювалася кожна умова, аж до останньої. Якщо не вкласти формулу точно на 100%, вона може працювати 75% часу, але повертати неочікувані результати 25% часу. На жаль, одразу виявити ці умови малоймовірно.
-
Оновлювати багато інструкцій IF може виявитися зовсім непросто, особливо коли ви повернетеся до формули через деякий час і намагатиметеся збагнути, що ви (або ще гірше – хтось інший) намагалися зробити.
Якщо ви опинитеся з інструкцією IF, яка просто, здається, продовжує зростати без кінця, настав час притиснути мишу і переосмислити свою стратегію.
Розгляньмо, як правильно створити складну вкладену інструкцію IF за допомогою кількох файлів IFs, а також коли розпізнавати, що настав час використовувати інший засіб в арсеналі Excel.
Приклади
Нижче наведено приклад відносно стандартної вкладеної інструкції IF, яка перетворює бали учнів за контрольну роботу на відповідні оцінки.
-
=IF(D2>89;"A";IF(D2>79;"B";IF(D2>69;"C";IF(D2>59;"D";"F"))))
Ця складна вкладена інструкція IF має просту логіку.
-
Якщо учень отримав за контрольну роботу понад 89 балів (у клітинці D2), його оцінка – A.
-
Якщо учень отримав за контрольну роботу понад 79 балів, його оцінка – B.
-
Якщо учень отримав за контрольну роботу понад 69 балів, його оцінка – C.
-
Якщо учень отримав за контрольну роботу понад 59 балів, його оцінка – D.
-
В іншому разі учень отримує оцінку F.
Цей конкретний приклад відносно безпечний, тому що не ймовірно, що кореляція між тестовими оцінками та оцінками листів зміниться, тому обслуговування не потребуватиме значного обслуговування. Але ось думка : що робити, якщо потрібно сегментувати оцінки між A+, A і A- (і так далі)? Тоді цю інструкцію IF із чотирма умовами знадобиться переписати так, щоб вона містила 12 умов. Тепер формула виглядатиме так:
-
=IF(B2>97;"A+";IF(B2>93;"A";IF(B2>89;"A–";IF(B2>87;"B+";IF(B2>83;"B";IF(B2>79;"B–";IF(B2>77;"C+";IF(B2>73;"C";IF(B2>69;"C–";IF(B2>57;"D+";IF(B2>53;"D";IF(B2>49;"D–";"F"))))))))))))
Вона працюватиме належним чином і працюватиме належним чином, але записування триватиме довше та триватиме довше, щоб перевірити, чи вона виконує потрібні дії. Ще одна яскрава проблема полягає в тому, що вам довелося вводити оцінки та еквівалентні оцінки букв вручну. Які шанси на те, що ви випадково матимете помилку? А тепер уявіть, що потрібно ввести 64 складніші умови. Звичайно, це можливо, але чи дійсно ви хочете піддати себе таким зусиллям і ймовірним помилкам, які буде дійсно важко помітити?
Порада.: Кожна функція в Excel береться в дужки (). Excel спробує з'ясувати, що відбувається, розфарбувавши різні частини формули під час її редагування. Наприклад, якщо змінити наведену вище формулу, коли курсор переміщається повз кожну з правих дужок ")", відповідна відкривна дужка матиме однаковий колір. Це може бути особливо корисно в складних вкладених формулах, коли ви намагаєтеся з'ясувати, чи достатньо відповідних дужок.
Додаткові приклади
Нижче наведено типовий приклад обчислень комісійних за збут залежно від рівня доходу.
-
=IF(C9>15000;20%;IF(C9>12500;17,5%;IF(C9>10000;15%;IF(C9>7500;12,5%;IF(C9>5000;10%;0)))))
Ця формула має такий зміст: ЯКЩО(C9 більше 15 000, то повернути 20%, ЯКЩО(C9 більше 12 500, то повернути 17,5% тощо...
Хоча ця формула дуже схожа на попередній приклад оцінок, ця формула – чудовий приклад того, наскільки важко зберігати великі інструкції IF– що потрібно зробити, якщо ваша організація вирішить додати нові рівні компенсації та, можливо, навіть змінити наявні значення в гривнях або відсотках? У вас було б багато роботи на руках!
Порада.: Щоб було легше читати довгі формули, у рядку формул можна вставляти розриви рядків. Просто натисніть клавіші Alt+Enter перед текстом, який потрібно перенести на наступний рядок.
Ось приклад сценарію обчислення комісійних із хибною логікою:
Чи можете ви побачити, що сталося? Порівняйте порядок порівнянь доходів із попереднім прикладом. Яким чином це відбувається? Це правильно, це йде знизу вгору ($ 5000 до $ 15000), а не навпаки. Але чому це має бути така велика справа? Це велика справа, тому що формула не може пройти перше оцінювання для будь-якого значення, що перевищує 5000 доларів США. Припустімо, ви отримали дохід у розмірі 12 500 доларів США – оператор IF поверне 10%, тому що він більший за 5 000 доларів США, і він зупиниться на цьому. Це може бути неймовірно проблематично, оскільки в багатьох ситуаціях ці типи помилок будуть непоміченими, поки вони не отримають негативний вплив. Отже, знаючи, що є серйозні підводні камені зі складними вкладеними операторами IF, що ви можете зробити? Здебільшого функцію VLOOKUP можна використовувати замість створення складної формули за допомогою функції IF. За допомогою функції VLOOKUP спочатку потрібно створити таблицю посилань:
-
=VLOOKUP(C2;C5:D17;2;TRUE)
Ця формула шукає значення з клітинки C2 в діапазоні C5:C17. Якщо значення знайдено, повертається відповідне значення з того ж рядка в стовпці D.
-
=VLOOKUP(B9;B2:C6;2;TRUE)
Подібно до попередньої, ця формула шукає значення з клітинки B9 у діапазоні B2:B6. Якщо значення знайдено, повертається відповідне значення з того ж рядка в стовпці C.
Примітка.: Обидва ці VLOOKUPs використовують аргумент TRUE в кінці формул, тобто ми хочемо, щоб вони шукали наближення збігу. Іншими словами, вона відповідатиме точним значенням у таблиці підстановки, а також будь-яким значенням, які потрапляють між ними. У цьому випадку таблиці підстановки потрібно відсортувати за зростанням від найменшого до найбільшого.
У цій статті про функцію VLOOKUP докладно описано набагато простіше , ніж 12-рівнева складна вкладена інструкція IF! Ця функція має й інші, менш очевидні переваги:
-
Таблиці підстановки VLOOKUP можна легко переглянути. Вони чіткі та зрозумілі.
-
Значення в таблицях можна легко оновити. Якщо зміняться умови, не доведеться чіпати формулу.
-
Якщо не потрібно, щоб користувачі бачили таблицю посилань або перешкоджали йому, просто розмістіть її на іншому аркуші.
Цікавий факт
Тепер є функція IFS, яка може замінити кілька вкладених інструкцій IF на одну функцію. Наш перший приклад про оцінки з 4 вкладеними функціями IF
-
=IF(D2>89;"A";IF(D2>79;"B";IF(D2>69;"C";IF(D2>59;"D";"F"))))
можна значно спростити, використовуючи єдину функцію IFS:
-
=IFS(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";TRUE;"F")
Функція IFS чудово працює, тому що вам не потрібно турбуватися про всі ці інструкції IF і дужки.
Примітка.: Ця функція доступна лише для власників передплати на Microsoft 365. Якщо ви маєте передплату Microsoft 365, переконайтеся, що у вас найновіша версія Office.Придбати або спробувати Microsoft 365
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
Пов’язані теми
IFS функція (Microsoft 365, Excel 2016 і пізніші версії) Функція COUNTIF підраховуватиме значення на основі однієї умови . Функція COUNTIFS підраховуватиме значення на основі кількох умов Функція SUMIF підсумує значення на основі однієї умови . SUMIFS функція підсумовує значення на основі кількох умов Функція AND Функція OR Функція VLOOKUP Огляд формул у програмі Excel Як уникнути недійсних формулВиявляти помилки у формулах ЛогічніфункціїExcel (за алфавітом)Функції Excel (за категоріями)
