Перейти к основному содержанию

Как использовать функцию ЕСЛИ с И, ИЛИ и НЕ в Excel?

Функция ЕСЛИ в Excel является свидетельством мощности и универсальности логических операций при обработке данных. Суть функции ЕСЛИ заключается в ее способности оценивать условия и возвращать конкретные результаты на основе этих оценок. Он действует по фундаментальной логике:

=IF(condition, value_if_true, value_if_false)

В сочетании с логическими операторами, такими как И, ИЛИ и НЕ, возможности функции ЕСЛИ значительно расширяются. Сила этой комбинации заключается в их способности одновременно обрабатывать несколько условий, обеспечивая результаты, которые можно адаптировать к различным и сложным сценариям. В этом руководстве мы рассмотрим, как эффективно использовать эти мощные функции Excel, чтобы открыть новые аспекты анализа данных и улучшить процесс принятия решений. Давайте углубимся и узнаем о практическом применении этих потрясающих функций Excel!

Вложенная формула ЕСЛИ И ИЛИ НЕ


формула ЕСЛИ И

Для оценки нескольких условий и достижения конкретного результата когда все условия выполнены (ИСТИНА)и другой результат, если какое-либо условие не выполнено (FALSE), вы можете включить функцию AND в логическую проверку оператора IF. Структура для этого:

=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%. Если оба условия выполнены, возвращается «Прошел»; в противном случае возвращается «Fail».
  2. Перетащите формулу вниз по столбцу, чтобы оценить балл и посещаемость каждого учащегося.

    формула ЕСЛИ И


Формула ЕСЛИ ИЛИ

Чтобы оценить несколько условий и вернуть конкретный результат когда любое из условий выполнено (ИСТИНА)и другой результат, когда ни одно из условий не выполнено (FALSE), функцию OR можно использовать в рамках логической проверки оператора IF. Формула имеет следующую структуру:

=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. Если любое из условий выполнено, возвращается «Пройдено»; если нет, «Не удалось».
  2. Скопируйте эту формулу в столбец, чтобы применить ее к каждому учащемуся в вашем списке, что позволит быстро оценить право каждого учащегося на сдачу экзамена на основе этих критериев.

    Формула ЕСЛИ ИЛИ


ЕСЛИ НЕ Формула

Чтобы оценить условие и вернуть конкретный результат если условие НЕ выполнено (FALSE)и другой результат, если условие выполнено (ИСТИНА), вашим решением является функция НЕ в операторе ЕСЛИ. Структура этой формулы следующая:

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

В качестве практического примера рассмотрим сценарий на рабочем месте, где бонусы сотрудников определяются на основе их посещаемости. Сотрудники имеют право на премию, если они НЕ отсутствовали более 3 дней.

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

    ЕСЛИ НЕ формула


Расширенные сценарии с ЕСЛИ и логическими функциями

В этом разделе мы рассмотрим сложное использование функции ЕСЛИ в Excel с логическими операторами, такими как И, ИЛИ и НЕ. В этом разделе рассматривается все: от оценок с учетом регистра до вложенных операторов IF, демонстрируя универсальность Excel в сложном анализе данных.


Если ваше условие выполнено, то рассчитайте

Помимо предоставления заранее определенных результатов, функция Excel ЕСЛИ в сочетании с логическими операторами, такими как И, ИЛИ и НЕ, может выполнять различные вычисления в зависимости от того, являются ли заданные условия истинными или ложными. Здесь мы будем использовать комбинацию ЕСЛИ И в качестве примера, чтобы продемонстрировать эту функциональность.

Представьте, что вы управляете отделом продаж и хотите рассчитывать бонусы. Вы решаете, что сотрудник получает 10% бонус от своих продаж если они объем продаж превысит 100 долларов США И работали более 30 часов в неделю.

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

    ЕСЛИ Тогда вычислить

Примечание: В этом разделе мы сосредоточимся на использовании функции ЕСЛИ с И для вычислений, основанных на конкретных условиях. Эту концепцию также можно расширить, включив в нее ИЛИ и НЕ, а также вложенные логические функции, позволяющие выполнять различные условные вычисления в Excel.


Операторы AND, OR и NOT с учетом регистра

Хотя в Excel логические функции, такие как И, ИЛИ и НЕ, обычно нечувствительны к регистру, существуют сценарии, в которых чувствительность к регистру в текстовых данных имеет решающее значение. Путем интеграции ТОЧНАЯ функция с помощью этих логических операторов вы можете эффективно обрабатывать такие условия, чувствительные к регистру. В этом разделе мы демонстрируем использование функций ЕСЛИ и ИЛИ с учетом регистра в качестве примера.

Представьте себе сценарий розничной торговли, в котором продукт подлежит продвижению, если он либо объем продаж превышает 100 долларов США OR его код точно соответствует «ABC» при проверке с учетом регистра.

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

    Формула ЕСЛИ ИЛИ с учетом регистра

Примечание: В этом разделе мы проиллюстрировали использование функций ЕСЛИ и ИЛИ с функцией ТОЧНО для вычислений с учетом регистра. Аналогичным образом вы можете применить функцию ТОЧНО в формулах ЕСЛИ в сочетании с И, ИЛИ, НЕ или вложенными логическими функциями для удовлетворения различных требований 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. Скопируйте эту формулу в столбец для каждого сотрудника, чтобы единообразно оценить право на получение бонуса с учетом его продаж, отработанных часов и отдела.

    Вложенная формула ЕСЛИ И ИЛИ НЕ


Вложенные функции ЕСЛИ с И, ИЛИ, НЕ

Если ваш анализ данных включает в себя несколько условных проверок, вложенные функции ЕСЛИ в Excel предлагают мощное решение. Этот метод предполагает создание отдельных операторов ЕСЛИ для различных условий, включая логику И, ИЛИ и НЕ, а затем интеграцию их в одну упрощенную формулу.

Рассмотрим рабочее место, где работа сотрудников оценивается на «отлично», «хорошо» или «удовлетворительно». на основании продажи, отработанное время и соблюдение политики:

  • «Отлично» для продаж на сумму более 150 долларов США И отработанных более 35 часов.
  • В противном случае «Хорошо» для продаж на сумму более 100 долларов США ИЛИ нарушения политики НЕ более 1.
  • «Справедливо», если ни одно из этих условий не выполнено.

Для оценки эффективности работы каждого сотрудника по вышеуказанным условиям необходимо выполнить следующие действия:

  1. Начните с оценки Анны, чьи продажи указаны в ячейке B2, отработанные часы – в ячейке C2, а нарушения правил – в ячейке D2. Вложенная формула ЕСЛИ в E2:
    =IF(AND(B2>150, C2>35), "Excellent", IF(OR(B2>100, NOT(D2>1)), "Good", "Fair"))
    Наконечник: Эта формула сначала проверяет, соответствуют ли продажи и часы Анны критерию «Отлично». Если нет, он оценивает, соответствует ли она критериям «хорошо». Если ни одно из условий не выполняется, ей присваивается категория «Удовлетворительно».
  2. Распространите эту вложенную формулу ЕСЛИ на каждого сотрудника, чтобы последовательно оценивать его эффективность по множеству критериев.

    Вложенные функции ЕСЛИ


Использование ЕСЛИ с И ИЛИ НЕ: Часто задаваемые вопросы

В этом разделе рассматриваются часто задаваемые вопросы по использованию ЕСЛИ с И, ИЛИ и НЕ в Microsoft Excel.

Сколько условий могут поддерживать функции И, ИЛИ и НЕ?
  • Функции И и ИЛИ могут поддерживать до 255 отдельных условий. Однако рекомендуется использовать только несколько из них, чтобы избежать слишком сложных формул, которые трудно поддерживать.
  • Функция НЕ принимает только одно условие.
Могу ли я использовать в этих функциях такие операторы, как , =?

Конечно, в функциях И, ИЛИ и НЕ Excel вы можете использовать такие операторы, как меньше (), равно (=), больше или равно (>=) и больше, чтобы установить условия.

Почему в этих функциях возникает ошибка #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