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

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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

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

Щоб оператор 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% випадків. На жаль, шанси ви зараження 25%, тонкі.

  • Кілька операторів 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:B22. Якщо значення знайдено, потім повернутися відповідні значення в тому самому рядку у стовпці c.

Примітка : Обидва ці VLOOKUPs за допомогою аргумент TRUE в кінці формул, це означає, що ми хочемо, знайдіть повідомлення approxiate match. Іншими словами, він буде відповідати точне значення в таблиці підстановки, а також будь-якого значення, які перебувають між ними. У цьому випадку таблиці підстановки потрібно сортувати в порядку за зростанням, від найменшого до найбільшого.

Функції 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 (за категоріями)

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

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

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

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

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

×