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

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

Comments (72)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hey Friends,

Donno if this Forum works now:

I am trying to figure out a formula for the following:
I have 3 Columns : viz : A1,D1, L1 with text contents. And output expected in M1

So if any of the cells A,D,L has Faulty mentioned, then M1 should result "Faulty", Else if all 3 are blank , need "Spare", and if any cell has any data then it shoud show "Mapped"
This comment was minimized by the moderator on the site
Kan iemand mij helpen met onderstaande voor het maken van een formule?
als B2 de tekst factuur staat moet in cel D8 de tekst factuurdatum komen te staan. Als in cel B2 de tekst offerte staat moet in cel D8 offertedatum komen te staan.

Alvast hartelijk dank
This comment was minimized by the moderator on the site
Hi there,

Can you show us the look of the text invoice, text quotation and text quotation date?
This comment was minimized by the moderator on the site
I am trying to write a formula to populate scores based off a range of values. This is what I have so far:

=IF(C2>=104.5%,"5", IF(C2<=104.49%,"4", IF(C2>=95.5%,"4", IF(C2<=95.49%,"3", IF(C2>=79.5%,"3", IF(C2<=79.49%,"2", IF(C2>=59.5%,"2", IF(C2<=59.49%,"1"""))))))))

The formula is working and I am not receiving any error messages. However, it is not populating the lower range values correctly. Here is my range:

>105% = 5
96%-105% = 4
80%-95% = 3
60%-79% = 2
<60% = 1

Any help is greatly appreciated.
This comment was minimized by the moderator on the site
Hi there,

I've fixed the formula as shown below:
=IF(C2>105%,"5",IF(C2>=96%,"4",IF(C2>=80%,"3",IF(C2>=60%,"2","1"))))

Hope this is what you want.

Amanda
This comment was minimized by the moderator on the site
Never mind, I figured it out. Thanks!
This comment was minimized by the moderator on the site
Kan iemand mij helpen aan onderstaande formule?
Alvast bedankt!

als Q groter is dan C dan Q en als Q kleiner is dan H dan H tenzij H 0 is dan is Q
This comment was minimized by the moderator on the site
Hi there,

Suppose, Q=A1, C=A2, H=A3. Please use the formula below: =IF(B1>B2,B1,IF(B1<B3,IF(B3=0,B1,B3),B3))

Hope this is what you want.

Amanda
This comment was minimized by the moderator on the site
I am trying to find a formula when realignment is in column A then add the text conflict when transition begins date (B) is >= the solution start date in column D OR if activation in column A then add the text conflict when transition begins date is <= solution start date in column D OR if deactivation then add the text conflict when transition begins date is >= the solution start date
A B C D E
Transition Type Transition Begins Transition Ends Solution Start Date Solution End Date
Realignment 11/1/2022 11/15/2022 1/15/2022 3/6/2022
Realignment 11/1/2022 11/15/2022 1/15/2022 3/16/2022
Realignment 11/1/2022 11/15/2022 6/1/2022 7/16/2022
Realignment 11/1/2022 11/15/2022 6/1/2022 7/16/2022
Realignment 11/1/2022 11/15/2022 6/1/2022 7/16/2022
Activation 1/1/2022 1/31/2022 1/15/2022 3/6/2022
Deactivation 12/1/2021 12/15/2021 1/15/2022 3/6/2022
Reorganization 2/6/2022 2/12/2022 1/15/2022 3/6/2022
Activation 12/1/2021 12/31/2021 11/1/2029 12/31/2029
Activation 12/1/2021 12/31/2021 2/1/2025 7/31/2025
Activation 12/1/2021 12/31/2021 4/1/2024 6/29/2024
Activation 12/1/2021 12/31/2021 2/1/2028 3/2/2028
Activation 12/1/2021 12/31/2021 2/1/2022 5/20/2025
Activation 12/1/2021 12/31/2021 9/6/2022 3/16/2023
Activation 12/1/2021 12/31/2021 6/1/2024 11/28/2024
Activation 12/1/2021 12/31/2021 9/1/2022 9/7/2022
Deactivation 10/1/2021 10/30/2021 11/1/2029 12/31/2029
Deactivation 10/1/2021 10/30/2021 2/1/2025 7/31/2025
Deactivation 10/1/2021 10/30/2021 4/1/2024 6/29/2024
Deactivation 10/1/2021 10/30/2021 2/1/2028 3/2/2028
Deactivation 10/1/2021 10/30/2021 2/1/2022 5/20/2025
Deactivation 10/1/2021 10/30/2021 9/6/2022 3/16/2023
Deactivation 10/1/2021 10/30/2021 6/1/2024 11/28/2024
Deactivation 10/1/2021 10/30/2021 9/1/2022 9/7/2022
Reorganization 2/1/2022 2/28/2022 11/1/2029 12/31/2029
Reorganization 2/1/2022 2/28/2022 2/1/2025 7/31/2025
This comment was minimized by the moderator on the site
Hi there,

What do you mean by adding the text conflict? Can you show me the result you want?

Amanda
This comment was minimized by the moderator on the site
=IF(AND(AY7>60,AY7>30),"0.02","0.04"),if(and(ay7<=30,ay<az),"0.06"),if(and(ay7<=15,ay<az),"0.08")
This comment was minimized by the moderator on the site
I am trying use If formula for one oridinary file, lets say I have number from 1 to 31 in a perticular cell. I need to show the result in another cell as if number in that perticular cell is less than 26 they it will zero, if the number in that perticular cell is from 26 to 30 then it will show the same number but if the number is abobe 30 then it will show the 30 only....Can anyone advise me how can I formulate this formula?
This comment was minimized by the moderator on the site
Hi there,

Please try the formula below: =IF(A1<26,0,IF(A1<=30,A1,30))

Hope this could help you.

Amanda
This comment was minimized by the moderator on the site
Ciao,
mi potete aiutare perfavore...
ho tre celle:
1-data di pagamento
2-totale
3-totale se pagato

vorrei che quando inserisco la data di pagamento(1), la casella 3(che è vuota) si riempisse automaticamente come la casella 2
come posso fare?
This comment was minimized by the moderator on the site
Hi there,

Do you want to fill the value of the cell 2 in the cell 3?
If so, you can enter this IF formula in the casella 3: =IF(casella 1<>"",casella 2,"")

Amanda
This comment was minimized by the moderator on the site
=IF(D4<=30000, and =>20000,than D4a-5000,and if(d4<=40000, and >30000, than d4-6000) convert into formula
This comment was minimized by the moderator on the site
Try the formula. Since I don't quite understand you, please check and change the part "DA4-5000" and "D4-6000", "FALSE" to the results you want.
=IF(AND(D4<=30000,D4>20000),"DA4-5000",IF(AND(D4<=40000,D4>30000),"D4-6000","FALSE"))
Amanda
This comment was minimized by the moderator on the site
HI EVERY ONE I NEED HELP IN THIS SYNTEX=IF(AND(AJ=1250,AJ*2.5%),IF(AND(AJ>1250,AJ<=2500),AJ*10%,IF(AND(AJ>2500,AJ<=3750),AJ*15%,IF(AND(AJ>3750,AJ<=11666),AJ*20%))))
THIS FOURMAIL GIVE ME #NAME WHERE THE EROO
This comment was minimized by the moderator on the site
Hi ahmed.dba,
Can you send the file to ? And if you have private information in the file, please delete them.
Amanda
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations