И или если в excel примеры: Использование ЕСЛИ с функциями И, ИЛИ и НЕ

Содержание

Использование операторов Excel If с несколькими диапазонами условий (и/или)


|

Цель использования сложных и обширных инструкций Excel Если операторы с несколькими диапазонами условий важны, когда речь идет об анализе данных, который требует проверки определенных условий в определенном диапазоне в один момент времени.

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

Операторы If на самом деле являются наиболее часто используемой формулой для тех, кто ежедневно работает с анализом данных и знаком с этой функцией.

Вот различные операторы If, которые вы можете использовать в зависимости от условий, для которых они вам нужны:

Оператор If Excel

Вложенный оператор If

Предположим, что у нас есть следующие условия:

  • Если оценка находится между 0 до 50, марка F
  • Если сумма баллов находится в диапазоне от 51 до 60, оценка D
  • Если оценка находится в диапазоне от 61 до 75, оценка C
  • Если оценка находится в диапазоне от 76 до 90, оценка B
  • Если оценка находится в диапазоне от 91 до 100, оценка A

Синтаксис формулы, основанный на приведенном выше условии, будет таким:

=ЕСЛИ(B2<50, «F»,IF(B2<61, «D», IF(B2<76»,C»,IF(B2<91»,B»,»A»)))))

Excel, если с логическим тестом

При использовании операторов Excel If существует два разных условия, которые можно использовать для выполнения логического теста — И и ИЛИ.

Функция И

Используя эту функцию, результат, который Excel выдаст при выполнении логического теста, будет True в каждом условии, в противном случае он будет False.

Функция ИЛИ

Результатом будет True, если логический тест выполняется при совпадении любой из ситуаций, в противном случае результат будет False.

Когда также используются функции И и ИЛИ, обычно необходимо выполнить несколько проверок.

Со временем вы научитесь использовать эти две функции с оператором If.

Как применять функции ЕСЛИ и И

  1. Во-первых, нам нужно взять данные о баллах каждого учащегося по естественным наукам и английскому языку.
  2. Оценка по естественным наукам хранится в данных B, тогда как оценка по английскому языку хранится в данных C.
  3. В качестве примера предположим, что учащийся сможет пройти, если его или ее балл больше или равен 50 по естественным наукам и больше 75 по английскому языку.
  4. Чтобы получить результат для всех учащихся всего за несколько секунд, оператор If будет объединен с функцией AND.
  5. Чтобы запустить формулу, можно ввести следующее: =ЕСЛИ(Excel отобразит логическую подсказку под ячейкой F2. Он покажет параметры этой функции: логическая_проверка, значение_если_истина, значение_если_ложь.
  6. Первый параметр указывает условие, которое необходимо выполнить. Этот логический тест также допускает комбинацию функций ЕСЛИ и И.
  7. Второй параметр покажет значение, если условие истинно.
  8. Наконец, третий параметр покажет, что значение условия ложно.
  9. Применяя формулы ЕСЛИ и И, вы получите =ЕСЛИ(И(B2>=75,C2>=75), «Пройдено», «Не пройдено»)
  10. Добавьте столбец «годен/не годен». Добавьте формулу в ячейку и скопируйте и вставьте ее в остальные ячейки ниже. Затем вы увидите результат для каждого ученика.

Как использовать ЕСЛИ с функцией ИЛИ в Excel

Использование этой функции аналогично использованию функций ЕСЛИ и И. Единственная разница в том, что если условие истинно, оно показывает только истинность.

Если принять приведенный выше пример, то формула будет =ЕСЛИ(ИЛИ(B2>=50,C2>=75), «Удачно», «Не пройдено»)

Как использовать ЕСЛИ с функциями И и ИЛИ

Это применимо при тестировании нескольких условий на основе разных данных в один момент времени.

Наиболее логичной формулой является использование ЕСЛИ с функциями И и ИЛИ.

Ниже приведены примеры:

  • 1-я ситуация: если столбец B>=70 и столбец C>=85
  • 2-я ситуация: если столбец B>=80 и столбец C>=85

При соблюдении всех вышеперечисленных условий учащийся считается зачисленным.

В противном случае студент терпит неудачу.

Используемая формула:

=ЕСЛИ(ИЛИ(И(B2>=70,C2>=85), AND(B2>=80,C2>=85))», «Выполнено», «Не выполнено»)

Ниже приведены примеры использования оператора IF с другие функции:

Оператор ЕСЛИ с функциями суммы, среднего, минимума и максимума

Предположим, что на основе оценки учащегося вы хотите отметить его как «хорошо», «удовлетворительно» или «плохо».

Если у вас есть предопределенная формула, такая как показанная ниже, и изменение данных не разрешено:

=If((B2+C2)>=150, «Хорошо», If((B2+C2)=>100, «Удовлетворительно», «Плохо»))

Использование функции СУММ

=If(СУММ(B2:C2)>=150, «Хорошо», If(SUM(B2+C2)=>100 , «Удовлетворительно», «Плохо»))

Использование функции СРЗНАЧ

=Если(СРЗНАЧ(B2:C2)>=150, «Хорошо», Если(СРЗНАЧ(B2+C2)=>100, «Удовлетворительно», «Плохо»))

Использование функции МАКС/МИН

9000 2 Если вы хотите найти, кто набрал наибольшее количество баллов из группы студентов, вы можете использовать функцию МАКС.

Таким же образом вы можете использовать функцию MIN, чтобы узнать, кто получил наименьшее количество очков.

Формула для получения наивысшего балла будет следующей:

=If(B2=Max($B$2:$B$10), «Лучший результат»», «)

Формула для получения самого низкого балла также будет:

=If(B2=M in($B$2:$B$10), «Худший результат», «)

Объединение этих двух формул: 

=If(B2=Max($B$2:$B$10), «Лучший результат», If(B2=Min($B$2:$B$10), «Худший результат»,», «))

Теперь вы можете начать использовать четыре различных типа операторов Excel If.

В зависимости от того, какой анализ данных вам нужен с имеющейся информацией, вы сможете начать применять эти функции.

Множественные операторы If в Excel (вложенные операторы IF, AND/OR) с примерами

Мы используем оператор IF в Excel для проверки одного условия и возврата одного значения, если условие выполняется, и другого, если условие не выполняется.

Однако мы используем несколько или вложенных операторов IF при оценке многочисленных условий в определенном порядке, чтобы получить разные результаты.

В этом руководстве показаны четыре примера использования вложенных операторов IF в Excel и пять альтернатив использованию нескольких операторов IF в Excel .

Содержание

Переключатель

Общий синтаксис вложенных операторов IF (несколько операторов IF)

Общий синтаксис для вложенных операторов IF следующий:

 =IF(Condition1, Value_if_true1, IF(Con условие2, Значение_если_истина2, ЕСЛИ(Условие3, Значение_если_истина3, Значение_если_ложь)))

Эта формула проверяет первое условие; если true, возвращает первое значение.

Если первое условие ложно, формула переходит ко второму условию и возвращает второе значение, если оно истинно.

Каждая последующая функция ЕСЛИ включается в аргумент value_if_false предыдущей функции ЕСЛИ.

Этот процесс продолжается до тех пор, пока не будут оценены все условия, и формула возвращает окончательное значение, если ни одно из условий не выполняется.

Максимальное количество вложенных операторов IF, разрешенных в Excel, равно 64.

Теперь посмотрите на следующие четыре примера использования вложенных операторов IF в Excel.

Пример № 1. Использование нескольких операторов ЕСЛИ для выставления буквенных оценок на основе числовых оценок

Рассмотрим следующий набор данных, показывающий баллы некоторых учащихся за тест по математике.

Мы хотим использовать вложенные операторы ЕСЛИ для присвоения учащимся буквенных оценок на основе их баллов.

Используем следующие шаги:

  1. Выберите ячейку C2 и введите следующую формулу:
 =ЕСЛИ(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))
  1. Нажмите Enter в ячейке, чтобы получить результат формулы в ячейке.
  1. Скопируйте формулу для остальных ячеек столбца

Присвоенные буквенные оценки отображаются в столбце C.

Объяснение формулы

=IF(B2>=90,”A”,IF(B2>=80,B”,IF(B2>=70,C”,IF(B2>=60,D”,»,F”))))

Эта формула оценивает значение в ячейке B2 и присваивает «A», если значение равно 90 или больше, «B», если значение находится в диапазоне от 80 до 89, «C», если значение находится в диапазоне от 70 до 79, «D», если значение находится в диапазоне от 60 до 69, и «F», если значение меньше 60.

Обратите внимание, что это может быть сложно отслеживать, какие скобки идут с какими аргументами во вложенных функциях ЕСЛИ.

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

  Читайте также  : Как использовать оператор Excel с несколькими условиями в диапазоне

Пример 2.

Использование нескольких операторов ЕСЛИ для расчета комиссионных на основе объема продаж

Вот набор данных, показывающий продажи конкретных продавцов в определенном месяце.

Мы хотим использовать несколько операторов IF для расчета многоуровневой комиссии для продавцов на основе их объема продаж.

Действуем следующим образом:

  1. Выберите ячейку C2 и введите следующую формулу:
 =IF(B2>=40000, B2*0,14,IF(B2>=20000,B2*0,12,IF(B2>=10000,B2*0,105,IF(B2>0,B2*0,08,0))))
  1. Нажмите клавишу Enter, чтобы получить результат формулы.
  2. Дважды щелкните или перетащите маркер заполнения, чтобы скопировать формулу вниз по столбцу.

Комиссия каждого продавца отображается в столбце D.

Пояснение к формуле

=ЕСЛИ(В2>=40000, В2*0,14,ЕСЛИ(В2>=20000,В2*0,12,ЕСЛИ(В2>=10000,В2*0,105,ЕСЛИ(В2>0) ,В2*0,08,0))))

Эта формула оценивает значение в ячейке B2, а затем выполняет следующие действия:

  • Если значение в ячейке B2 больше или равно 40 000, это число умножается на 14% (0,14).
  • Если число в ячейке B2 меньше 40 000, но больше или равно 20 000, значение умножается на 12 % (0,12).
  •  Если число в ячейке B2 меньше 20 000, но больше или равно 10 000, это число умножается на 10,5 % (0,105).
  • Если значение в ячейке B2 меньше 10 000, но больше 0 (ноль), число умножается на 8% (0,08).
  • Если значение в ячейке B2 равно 0 (ноль), возвращается 0 (ноль).

Пример №3. Использование нескольких операторов IF для присвоения рейтинга эффективности продаж на основе достижения цели продаж

Ниже приведен набор данных, показывающий данные о региональных продажах конкретной технологической компании за конкретный год.

Мы хотим использовать несколько операторов IF, чтобы присвоить рейтинг эффективности продаж каждому региону на основе достижения их целевых показателей продаж.

Используем следующие шаги:

  1. Выберите ячейку C2 и введите следующую формулу:
 =ЕСЛИ(В2>500000, "Отлично", ЕСЛИ(В2>400000, "Хорошо", ЕСЛИ(В2>275000, "Средне", "Плохо")))
  1. Нажмите Enter в строке формул.
  2. Перетащите или дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу.

Рейтинги регионов приведены в столбце С.

Пояснение к формуле

=ЕСЛИ(В2>500000, «Отлично», ЕСЛИ(В2>400000, «Хорошо», ЕСЛИ(В2>275000, «Средне», «Плохо»)))

В этой формуле, если цель продаж в ячейке B2 больше 500 000, формула возвращает «Отлично».

Если значение находится в диапазоне от 400 000 до 500 000, формула возвращает «Хорошо».

Если значение находится в диапазоне от 275 000 до 400 000, формула возвращает значение «Среднее». А если оно ниже 275 000, формула возвращает «Плохо».

Пример № 4. Использование нескольких операторов IF в Excel для проверки ошибок и возврата сообщений об ошибках

Предположим, у нас есть следующий набор данных о результатах тестов учащихся по английскому языку. Некоторые баллы меньше 0 или больше 100, а в некоторых случаях баллы отсутствуют.

Мы хотим использовать вложенные операторы IF для проверки оценок в столбце B и отображать сообщения об ошибках в столбце C, если оценки отсутствуют или оценки меньше 0 или больше 100.

Если оценка в столбце B действительна, мы хотим, чтобы формула возвращала пустую строку в столбце C.

Вот шаги, которые нужно выполнить:

9006 0

  • Выберите ячейку C2 и введите следующую формулу:
    •  =ЕСЛИ(ИЛИ(B2<0,B2>100),"Оценка вне допустимого диапазона",ЕСЛИ(ПУСТО(B2),"Неверная оценка",""))
    1. Нажмите Enter в строке формул.
    2. Перетащите маркер заполнения, чтобы скопировать формулу вниз по столбцу.

    Сообщения об ошибках показаны в столбце C.

    Объяснение формулы 2 меньше 0 или больше 100, и если это так, он возвращает сообщение об ошибке «Оценка вне диапазона».

    Формула также использует функцию ЕПУСТО, чтобы проверить, пуста ли ячейка B2, и, если это так, она возвращает сообщение об ошибке «Недопустимая оценка».

    Если ошибки нет, формула возвращает пустую строку, что означает, что в столбце B не отображается сообщение.

    Альтернативы использованию нескольких операторов IF в Excel

    Формулы, использующие вложенные операторы IF, могут стать трудными для чтения и управления, если у нас есть несколько условий для проверки.

    Кроме того, если мы превысим максимально допустимый лимит в 64 вложенных оператора IF, мы получим сообщение об ошибке.

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

    В этом уроке мы представляем альтернативные способы.

    Альтернатива #1: использование функции IFS

    Функция IFS проверяет, выполняются ли одно или несколько условий, и возвращает значение, соответствующее первому ИСТИННОМУ условию.

    До выпуска функции IFS в 2018 году в рамках обновления Excel 365 единственным способом проверить несколько условий и вернуть соответствующее значение в Excel было использование вложенных операторов IF.

    Однако недостатком нескольких операторов IF является громоздкость формул, которые трудно читать и поддерживать.

    В некоторых ситуациях функция IFS предназначена для замены нескольких функций IF.

    Синтаксис функции IFS более прямолинеен и удобен для чтения, чем вложенные операторы IF, и она может обрабатывать до 127 условий.

    Вот пример:

    Давайте рассмотрим следующий набор данных, показывающий результаты некоторых учащихся на тесте по математике.

    Мы хотим использовать функцию IFS для выставления буквенных оценок учащимся на основе их баллов.

    Мы используем следующие шаги:

    1. Выберите ячейку C2 и введите следующую формулу:
     =ЕСЛИ(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", B2<60, "F")
    1. Нажмите Enter в строке формул.
    2. Перетащите или дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу.

    Буквенные оценки учащегося показаны в столбце C.

    Объяснение формулы

    =IFS(B2>=90, «A», B2>=80, «B», B2>=70, «C», B2>=60, «D», B2<60, «F»)

    Эта формула проверяет балл в ячейке B2 по каждому условию и возвращает соответствующую букву оценки, если условие истинно.

    Ограничение функции IFS

    Функция IFS в Excel предназначена для упрощения сложных вложенных операторов ЕСЛИ.

    Однако бывают ситуации, когда функция IFS не может полностью заменить вложенные функции IF.

    Одной из таких ситуаций является необходимость вычислений или действий на основе условия или набора условий.

    Хотя функция IFS может возвращать значение или текстовую строку на основе условия, она не может выполнять вычисления или операции с этим значением, как вложенные операторы IF.

    Еще одна ситуация, когда функция IFS может оказаться менее полезной, — это когда вам нужно протестировать диапазон условий, а не только определенный набор.

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

    Функция IFS — это мощный инструмент для упрощения сложных логических тестов в Excel.

    Однако могут быть ситуации, когда вложенные операторы IF больше подходят для ваших нужд.

    Рекомендуем рассмотреть оба варианта и выбрать тот, который лучше всего соответствует конкретным требованиям вашей задачи.

    Альтернатива № 2: использование вложенных функций ЕСЛИ

    Мы можем использовать несколько функций ЕСЛИ в формуле, если у нас есть более одного условия для проверки.

    Например, предположим, что у нас есть следующий набор данных с именами учащихся и оценками за тест по физике в столбцах A и B.

    Мы хотим присвоить буквенную оценку каждой оценке и включить обозначение «пройдено» или «не пройдено» в зависимости от того, выше или ниже 75 баллов.

    Вот шаги для использования:

    1. Выберите ячейку C2 и введите по формуле
     =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")&"  "&IFS(B2>=75,"Pass",B2<75,"Fail")
    1. Нажмите Enter в строке формул.
    2. Перетащите или дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу.

    Буквенная оценка и обозначение баллов учащихся отображаются в столбце C.

    Пояснение к формуле Pass», B2

    <75, «Fail»)

    В этой формуле первая функция IFS используется для присвоения буквенной оценки на основе оценки в столбце A, а вторая функция IFS — для обозначения прохождения/непрохождения на основе оценки в столбце A.

    Две функции IFS объединяются с помощью оператора амперсанда (&) для создания единой текстовой строки, которая отображает буквенную оценку каждой оценки и обозначение «пройдено/не пройдено».

    Альтернатива № 3: использование комбинации функций ВЫБОР и XMATCH

    Функция ВЫБОР выбирает значение или действие из списка значений на основе порядкового номера.

    Функция XMATCH находит и возвращает относительное положение элемента в массиве. Мы можем комбинировать эти функции в формуле вместо вложенных функций ЕСЛИ.

    Вот пример:

    Предположим, у нас есть следующий набор данных, показывающий баллы некоторых учащихся и буквенные оценки за тест по биологии.

    Мы хотим использовать формулу, объединяющую функции ВЫБОР и СРАВНЕНИЕ, чтобы присвоить соответствующие баллы в столбце D каждой буквенной оценке.

    Мы используем следующие шаги:

    1. Выберите ячейку D2 и введите следующую формулу:
     =ВЫБОР(XMATCH(C2,{"F","E","D","C","B","A"},0),0,1,2,3,4,5)
    1. Нажмите Enter в строке формул.
    2. Перетащите или дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу.

    Оценки каждого учащегося отображаются в столбце D.

    Пояснение к формуле

    =ВЫБОР(XMATCH(C2,{"F","E","D","C","B","A"},0),0,1,2,3,4,5)

    Эта формула применяет функцию XMATCH к найдите позицию буквенной оценки в массиве {"F", "E", "D", "C", "B", "A"}, а затем используйте функцию ВЫБОР, чтобы вернуть соответствующие баллы оценки.

    Альтернатива #4: Использование функции ВПР

    Функция ВПР ищет значение в крайнем левом столбце таблицы, а затем возвращает значение в той же строке из указанного столбца.

    Мы можем использовать функцию ВПР вместо вложенных функций ЕСЛИ в Excel.

    Ниже приведен пример использования функции ВПР вместо вложенных функций ЕСЛИ в Excel:

    Предположим, у нас есть следующий набор данных, показывающий баллы некоторых учащихся и буквенные оценки за тест по биологии.

    Мы хотим использовать функцию ВПР для присвоения баллов буквенной оценке каждого учащегося в столбце D.

    Мы используем следующие шаги:

    1. Создайте таблицу со списком оценок и соответствующих им баллов в диапазоне ячеек F1:G7.
    1. В ячейке D2 введите следующую формулу:
     =ВПР(C2,$F$2:$G$7,2,ЛОЖЬ)

    Примечание: Используйте знак доллара, чтобы заблокировать диапазон ячеек F2:G7.

    1. Нажмите Enter в строке формул.
    2. Перетащите или дважды щелкните маркер заполнения, чтобы скопировать формулу вниз по столбцу.

    Оценки каждого учащегося отображаются в столбце D.

    Объяснение формулы

    =ВПР(C2,$F$2:$G$7,2,ЛОЖЬ)

    Эта формула использует функцию ВПР для поиска оценки в ячейке C2 в таблице F2:G7 и возврата соответствующей оценки точку во втором столбце (т. е. столбце G).

    Аргумент «ЛОЖЬ» гарантирует, что требуется точное совпадение.

    Альтернатива № 5: использование определяемой пользователем функции

    Если вам нужно протестировать несколько условий, рассмотрите возможность создания определяемой пользователем функции в VBA, которая может обрабатывать многие условия.

    Вот пример использования кода VBA для замены вложенных функций ЕСЛИ в Excel:

    Предположим, у нас есть следующий набор данных, показывающий продажи конкретных продавцов в определенном месяце.

    Мы хотим использовать определяемую пользователем функцию для расчета комиссии для каждого продавца на основе следующих ставок:

    • Если общая сумма продаж составляет менее 10 000 долларов США, комиссия составляет 8%.
    • Если общий объем продаж равен или превышает 10 000 долларов США, но меньше 20 000 долларов США, ставка комиссии составляет 10,5%.
    • Если общий объем продаж равен или превышает 20 000 долларов США, но меньше 40 000 долларов США, ставка комиссии составляет 12%.
    • Если объем продаж равен или превышает 40 000 долларов США, ставка комиссии составляет 14%

    Мы используем следующие шаги:

    1. Откройте рабочий лист, содержащий набор данных о продажах.
    2. Нажмите Alt + F11 для запуска редактора Visual Basic .
    3. Щелкните Вставить в строке меню и выберите Модуль , чтобы вставить новый модуль.
    1. Введите следующий код VBA.
     'Код, разработанный Стивом Скоттом с https://spreadsheetplanet.com
Функция КОМИССИЯ(Продажи в двойном размере) В двойном размере
    Постоянная скорость1 = 0,08
    Постоянная скорость2 = 0,105
    Постоянная скорость3 = 0,12
    Постоянная скорость4 = 0,14
'Рассчитать комиссию с продаж
    Выберите «Продажи кейсов»
        Случай от 0 до 9999,99: КОМИССИЯ = Продажи * Ставка1
        Вариант с 10000 по 19999,99: КОМИССИЯ = Продажи * Ставка2
        Вариант с 20000 по 39999,99: КОМИССИЯ = Продажи * Ставка3
        Дело >= 40000: КОМИССИЯ = Продажи * Ставка4
    Конец выбора
Конечная функция
    1. Сохраните процедуру функции и рабочую книгу как рабочую книгу с поддержкой макросов .