Перейти до основного матеріалу

Як використовувати функцію IF, якщо з І, АБО, А НЕ в Excel?

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

=IF(condition, value_if_true, value_if_false)

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

Вкладена формула ЯКЩО І АБО НІ

ЯКЩО І формула

Щоб оцінити кілька умов і отримати конкретний результат коли виконано всі умови (ІСТИНА)і інший результат, коли будь-яка умова не виконується (ХИБНІСТЬ), ви можете включити функцію І в логічну перевірку оператора ЯКЩО. Структура для цього:

=IF(AND(condition1, condition2, …), value_if_all_true, value_if_any_false)

Наприклад, уявіть, що ви вчитель, який аналізує оцінки учнів. Ви хочете визначити, чи успішний учень, на основі двох критеріїв: оцінка вище 70 І явка понад 80%.

  1. Почніть з вивчення даних першого студента, з його балом у клітинці B2 та відвідуваністю у клітинці C2. Для цього студента застосуйте наведену нижче формулу в D2: 
    =IF(AND(B2>70, C2>80%), "Pass", "Fail")
    Порада: Ця формула перевіряє, чи оцінка в B2 перевищує 70, а відвідуваність у C2 перевищує 80%. Якщо обидві умови виконуються, повертається «Pass»; інакше він повертає "Помилка".
  2. Перетягніть формулу вниз через стовпець, щоб оцінити оцінку та відвідуваність кожного студента.

    ЯКЩО І формула

Формула ЯКЩО АБО

Щоб оцінити кілька умов і повернути певний результат коли виконується будь-яка з умов (ІСТИНА), і інший результат, коли жодна з умов не виконується (ХИБНІСТЬ), функцію АБО можна використовувати в логічній перевірці оператора ЯКЩО. Формула має таку структуру:

=IF(OR(condition1, condition2, …), value_if_any_true, value_if_all_false)

Наприклад, в освітньому контексті розгляньте більш гнучкий критерій проходження студентом. Тут студент вважається успішним, якщо він будь-яка оцінка вище 90 OR мати рівень відвідуваності вище 95%.

  1. Почніть з оцінки успішності першого учня, вказавши їхній бал у клітинку B2, а присутність – у клітинку C2. Застосуйте формулу до сусідньої клітинки, наприклад D2, щоб оцінити: 
    =IF(OR(B2>90, C2>95%), "Pass", "Fail")
    Порада: Ця формула оцінює, чи студент набирає більше 90 балів у B2, або має рівень відвідуваності понад 95% у C2. Якщо будь-яка умова виконується, повертається «Pass»; якщо ні, «Невдача».
  2. Скопіюйте цю формулу вниз у стовпець, щоб застосувати її до кожного студента у вашому списку, дозволяючи швидко оцінити придатність кожного студента для проходження на основі цих критеріїв.

    ЯКЩО АБО формула

ЯКЩО НІ Формула

Щоб оцінити умову та повернути певний результат якщо умова НЕ виконується (ЛОЖЬ), і інший результат, якщо умова виконана (ІСТИНА), функція NOT в операторі IF є вашим рішенням. Структура цієї формули така:

=IF(NOT(condition), value_if_false, value_if_true)

Для практичного прикладу розглянемо сценарій на робочому місці, де бонуси співробітників визначаються на основі їхньої відвідуваності. Працівники мають право на премію, якщо вони НЕ були відсутні більше 3 днів.

  1. Щоб оцінити це для першого працівника, дні відсутності якого вказані в клітинці B2, використовуйте формулу: 
    =IF(NOT(B2>3), "Eligible", "Not Eligible")
    Порада: Ця формула перевіряє кількість днів відсутності в B2. Якщо значення НЕ більше 3, повертається «Відповідно»; інакше "Не відповідає вимогам".
  2. Скопіюйте цю формулу вниз у стовпець, щоб застосувати її для кожного працівника.

    ЯКЩО НЕ формула

Розширені сценарії з IF і логічними функціями

У цьому розділі ми розглянемо складне використання функції IF Excel із такими логічними операторами, як І, АБО та НІ. Цей розділ охоплює все, від чутливих до регістру оцінок до вкладених операторів IF, демонструючи універсальність Excel у складному аналізі даних.

Якщо ваша умова виконується, то розрахуйте

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

Уявіть, що ви керуєте відділом продажів і хочете розрахувати бонуси. Ви вирішуєте, що працівник отримує 10% бонус від продажів якщо вони обсяг продажів перевищує 100 доларів США І працювали більше 30 годин на тиждень.

  1. Для початкової оцінки перегляньте дані Аліси з її продажами в клітинці B2 і відпрацьованими годинами в клітинці C2. Застосуйте цю формулу до D2: 
    =IF(AND(B2>100, C2>30), B2*0.1, 0)
    Порада: Ця формула розраховує бонус у розмірі 10% від продажів Аліси, якщо її продажі перевищують 100 доларів США, а кількість відпрацьованих годин перевищує 30. Якщо виконуються обидві умови, вона розраховує бонус; інакше повертає 0.
  2. Поширте цю формулу на решту вашої команди, скопіювавши її в стовпець. Такий підхід гарантує, що премія кожного працівника розраховується на основі однакових критеріїв.

    ЯКЩО Тоді обчисліть

Примітка: У цьому розділі ми зосередимося на використанні функції ЯКЩО з І для обчислень на основі конкретних умов. Цю концепцію також можна розширити, включивши АБО та НІ, а також вкладені логічні функції, що дозволяє виконувати різні умовні обчислення в Excel.

Оператори І, АБО та НЕ з урахуванням регістру

Хоча в Excel такі логічні функції, як І, АБО та НЕ, зазвичай не враховують регістр, існують сценарії, коли чутливість до регістру в текстових даних є вирішальною. Шляхом інтеграції ТОЧНА функція за допомогою цих логічних операторів ви можете ефективно обробляти такі чутливі до регістру умови. У цьому розділі ми демонструємо використання функцій IF і OR з урахуванням регістру як приклад.

Уявіть собі сценарій роздрібної торгівлі, де продукт має право на просування, якщо це так обсяг продажів перевищує 100 доларів США OR його код точно відповідає "ABC" у перевірці з урахуванням регістру.

  1. Для першого продукту, зазначеного в рядку 2, з його продажами в клітинці B2 і кодом продукту в клітинці C2, використовуйте цю формулу в D2: 
    =IF(OR(B2>100, EXACT(C2,"ABC")), "Promotion Eligible", "Not Eligible")
    Порада: Ця формула визначає, чи показник продажів у B2 перевищує 100 доларів США, або код продукту в C2 точно відповідає «ABC». Виконання будь-якої з цих умов робить продукт придатним для рекламної акції; невдача обох робить його непридатним.
  2. Повторіть цю формулу в стовпці для всіх продуктів, щоб однаково оцінити їхню придатність до рекламної акції на основі продажів і критеріїв коду продукту з урахуванням регістру.

    Формула IF OR з урахуванням регістру

Примітка: У цьому розділі ми проілюстрували використання функцій ЯКЩО та АБО з функцією EXACT для оцінок з урахуванням регістру. Так само можна застосувати функцію EXACT у своїх формулах IF у поєднанні з AND, OR, NOT або вкладеними логічними функціями, щоб відповідати різноманітним вимогам до регістру в Excel.

Інтеграція IF із вкладеними операторами AND, OR, NOT

Функція ЯКЩО в Excel, вкладена в І, АБО та НІ, пропонує спрощений підхід для обробки більш багатошарових умов. У цьому розділі наведено приклад застосування цих вкладених функцій у роздрібній торгівлі.

Припустімо, що ви керуєте командою, яка відповідає за різні категорії продуктів, і хочете визначити їх право на бонуси. Працівник має право на премію, якщо він: досягти продажів понад 100 доларів США, І будь-яке працювати більше 30 годин на тиждень OR НЕ у відділі електроніки.

  1. По-перше, оцініть продуктивність Енн з її продажами в клітинці B2, відпрацьованими годинами в клітинці C2 та відділом у клітинці D2. Формула в E2 буде такою: 
    =IF(AND(B2>100, OR(C2>30, NOT(D2="Electronics"))), "Eligible", "Not Eligible")
    Порада: Ця формула перевіряє, чи Анна має продажі, що перевищують 100 доларів США, і вона працює більше 30 годин або не працює з електронікою. Якщо вона відповідає цим критеріям, вона вважається «Відповідною»; якщо ні, "Не відповідає вимогам".
  2. Скопіюйте цю формулу вниз у стовпець для кожного працівника, щоб рівномірно оцінити право на бонуси, враховуючи їхні продажі, відпрацьовані години та відділ.

    Вкладена формула ЯКЩО І АБО НІ

Вкладені функції IF з І, АБО, НІ

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

Розгляньте робоче місце, де продуктивність співробітників оцінюється як «Відмінно», «Добре» або «Задовільно» на основі продажі, відпрацьовані години та дотримання політики:

  • «Відмінно» для продажів на суму понад 150 доларів США І понад 35 годин роботи.
  • В іншому випадку «Добре» для продажів понад 100 доларів США АБО порушення політики НЕ більше ніж 1 раз.
  • «Справедливий», якщо жодна з цих умов не виконується.

Щоб оцінити продуктивність кожного працівника відповідно до наведених вище умов, виконайте наступне:

  1. Почніть з оцінки Енн, продажі якої вказано в клітинці B2, відпрацьовані години — у клітинці C2, а порушення правил — у клітинці D2. Вкладена формула IF в E2 виглядає так: 
    =IF(AND(B2>150, C2>35), "Excellent", IF(OR(B2>100, NOT(D2>1)), "Good", "Fair"))
    Порада: Ця формула спочатку перевіряє, чи продажі та години роботи Анни відповідають критеріям «Відмінно». Якщо ні, він оцінює, чи відповідає вона критеріям «Добре». Якщо жодна умова не виконується, вона класифікується як "Справедлива".
  2. Поширте цю вкладену формулу IF на кожного працівника, щоб узгоджено оцінювати їх ефективність за кількома критеріями.

    Вкладені функції IF

Використання IF з AND OR NOT: запитання й відповіді

Цей розділ має на меті відповісти на поширені запитання щодо використання IF з І, АБО та НІ в Microsoft Excel.

Скільки умов можуть підтримувати функції AND, OR і NOT?
  • Функції І та АБО можуть підтримувати до 255 окремих умов. Однак бажано використовувати лише кілька, щоб уникнути надто складних формул, які важко підтримувати.
  • Функція NOT виконує лише одну умову.
Чи можна використовувати такі оператори, як , = у цих функціях?

Звичайно, у функціях І, АБО та НЕ Excel ви можете використовувати такі оператори, як менше (), дорівнює (=), більше або дорівнює (>=) тощо для встановлення умов.

Чому в цих функціях виникає помилка #VALUE?

Помилка #VALUE у функціях І, АБО та НЕ Excel часто виникає, якщо формула не відповідає жодній заданій умові або якщо є проблема зі структурою формули. Це означає, що Excel не може правильно інтерпретувати вхідні дані або умови у формулі.

Вище наведено весь відповідний вміст, пов’язаний із використанням ЯКЩО з функціями І, АБО та НЕ в Excel. Сподіваюся, підручник буде для вас корисним. Якщо ви хочете ознайомитися з іншими порадами та підказками щодо Excel, Будь ласка, натисніть тут щоб отримати доступ до нашої великої колекції з понад тисячі посібників.