Функція IF – вкладені формули й уникання пасток

Функція IF – вкладені формули й уникання пасток

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

Функція IF виконує логічне порівняння значення з результатом, який очікується. Для цього перевіряється умова й повертається результат True або False.

  • =ЯКЩО(це значення – істина, то виконати певну дію, інакше – іншу дію)

Отже, інструкція IF може мати два результати. Перший отримаємо, якщо умова виявиться істинною, другий – якщо вона буде хибна.

Інструкції IF надзвичайно надійні та виступають основою для багатьох моделей електронних таблиць, але можуть виявитися причиною багатьох проблем в електронних таблицях. Найкраще застосовувати інструкцію IF до умов із невеликою кількістю варіантів, наприклад "Чоловік" або "Жінка", "Так", "Ні" чи "Можливо" тощо, але іноді сценарії обчислень можуть бути складніші та може знадобитися вкласти* більше трьох функцій IF.

* "Вкласти" – це об’єднати кілька функцій в одній формулі.

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

Синтаксис

IF(логічний_вираз;значення_якщо_істина;[значення_якщо_хибність])

Наприклад:

  • =IF(A2>B2;"Бюджет перевищено";"OK")

  • =IF(A2=B2;B4-A4;"")

Ім’я аргументу

Опис

логічний_вираз   

(обов’язковий)

Умова, яку потрібно перевірити.

значення_якщо_істина   

(обов’язковий)

Значення, яке повертається, якщо результат умови лог_вираз має значення TRUE.

значення_якщо_хибність   

(необов’язковий)

Значення, яке повертається, якщо результат умови лог_вираз має значення FALSE.

Примітки

В Excel можна вкласти до 64 різних функцій IF, але робити цього не варто. Чому?

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

  • Оновлювати багато інструкцій IF може виявитися зовсім непросто, особливо коли ви повернетеся до формули через деякий час і намагатиметеся збагнути, що ви (або ще гірше – хтось інший) намагалися зробити.

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

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

Приклади

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

Складна вкладена інструкція IF – у клітинці E2 введено формулу =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"))))))))))))
  • =IF(D2>89;"A";IF(D2>79;"B";IF(D2>69;"C";IF(D2>59;"D";"F"))))

    Ця складна вкладена інструкція IF має просту логіку.

  1. Якщо учень отримав за контрольну роботу понад 89 балів (у клітинці D2), його оцінка – A.

  2. Якщо учень отримав за контрольну роботу понад 79 балів, його оцінка – B.

  3. Якщо учень отримав за контрольну роботу понад 69 балів, його оцінка – C.

  4. Якщо учень отримав за контрольну роботу понад 59 балів, його оцінка – D.

  5. В іншому разі учень отримує оцінку 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, різні її частини виділяються кольором, щоб її було простіше зрозуміти. Наприклад, якщо, редагуючи наведену вище формулу, перемістити курсор за праву дужку ")", відповідна ліва дужка стане такого ж кольору. Це може бути особливо корисно для складних вкладених формул, коли потрібно з’ясувати, чи кожна дужка має пару.

Додаткові приклади

Нижче наведено типовий приклад обчислень комісійних за збут залежно від рівня доходу.

У клітинці D9 введено формулу IF(C9>15000;20%;IF(C9>12500;17,5%;IF(C9>10000;15%;IF(C9>7500;12,5%;IF(C9>5000;10%;0)))))
  • =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 перед текстом, який потрібно перенести на наступний рядок.

Ось приклад сценарію обчислення комісійних із хибною логікою:

У клітинці D9 введено неправильну формулу =IF(C9>5000;10%;IF(C9>7500;12,5%;IF(C9>10000;15%;IF(C9>12500;17,5%;IF(C9>15000;20%;0)))))

Ви можете бачити, що не так? Порівняння порядку в попередньому прикладі, порівняння прибутку. Яким чином це один буде? Це правильно, його буде знизу вверх ($5000 до $ 15 000), не навпаки. Але чому, має бути таку велику річ? Це нічого складного, тому що формулу не можна передавати першого оцінки для будь-яке значення більше $5000. Припустімо, ви отримали $12,500 у прибуток – оператор IF поверне 10%, оскільки це більше $5000, і він припинить там. Це може бути надзвичайно проблематично, оскільки у багатьох випадках такі типи помилок непоміченими, доки вони негативно впливає. Щоб знаючи, що там є кілька серйозних помилок зі складними вкладені операторів IF, що можна зробити? У більшості випадків замість створення складних формула з функцією IF можна використовувати функцію VLOOKUP. Використання функції VLOOKUP, спочатку потрібно створити посилання таблиці:

У клітинці D2 введено формулу =VLOOKUP(C2;C5:D17;2;TRUE)
  • =VLOOKUP(C2;C5:D17;2;TRUE)

У цій формулі вказано, що шукати значення у клітинці C2 в діапазоні C5:C17. Якщо значення знайдено, потім повернутися відповідні значення в тому самому рядку у стовпці d.

У клітинці C9 введено формулу =VLOOKUP(B9;B2:C6;2;TRUE)
  • =VLOOKUP(B9;B2:C6;2;TRUE)

Подібно до попередньої, ця формула шукає значення з клітинки B9 у діапазоні B2:B6. Якщо значення знайдено, повертається відповідне значення з того ж рядка в стовпці C.

Примітка.: В обох цих функціях VLOOKUP використано аргумент TRUE в кінці формули. Це означає, що вони шукатимуть приблизні збіги. Іншими словами, формула шукатиме точні збіги в таблиці підстановки та значення між ними. У цьому випадку таблиці підстановки має бути відсортовано за зростанням (від найменшого до найбільшого значення).

Функцію VLOOKUP докладніше описано тут, але вона безперечно значно простіша, ніж 12-рівнева складна вкладена інструкція IF. Ця функція має й інші, менш очевидні переваги:

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

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

  • Щоб інші користувачі не могли переглядати або редагувати таблицю підстановки, просто розмістіть її на іншому аркуші.

Цікавий факт

Існує зараз Якщо функції , які можна замінити кілька, вкладені операторів 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 і дужки.

Примітка.: Ця функція доступна тільки для власників передплати на Office 365. Якщо ви передплатник Office 365, переконайтеся, що маєте найновішу версію Office.

Спробуйте Office 365 або найновішу версію Excel

Потрібна додаткова довідка?

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

Пов’язані теми

Відео: Розширені функції IF
Якщо функція (Office 365, програмі Excel 2016 і пізніші)
функції The COUNTIF буде обчислення значень на основі одного умови
функції The COUNTIFS буде обчислення кількості значень за кількома умови
функція SUMIF буде обчислення суми значень на основі одного умови
функції SUMIFS у буде обчислення суми значень на основі кількох умов
функції AND
функції OR
Функції VLOOKUP
Огляд формул у програмі Excel
способи уникнення недійсних формул
виявлення помилок у формулах
логічних функцій
Функції (за алфавітом) у програмі Excel
функції Excel (за категоріями)

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×