Формулы эксель если то: Функция ЕСЛИ — вложенные формулы и типовые ошибки

Заявление ЕСЛИ между двумя числами — пошаговый расчет

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

Предположим, что компании ИТ-поддержки необходимо эффективно отслеживать свою систему заявок. Статус заявки будет отображаться в столбце D как «Ожидание» или «Готово» в зависимости от параметров в столбце C.

Здесь у нас есть три разных значения, то есть «Выполняется», «Не назначено», «Решено» в столбце C. Если значение одно из первых двух, мы получим результат как «Ожидание», иначе статус будет «Готово».

Мы использовали формулу = ЕСЛИ (ИЛИ (C3 = «Решено», И (C3 = «Выполняется», C3 = «Не назначено»)), «Готово», «Ожидание») путем включения ИЛИ функция. Мы также использовали функцию AND, так как результат для любого из них означает, что заявка все еще находится на рассмотрении. Наш окончательный результат в электронной таблице выглядит следующим образом:

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

Вы можете добавить в общей сложности 255 условий с помощью функции ИЛИ в Excel версии 2007 и выше, а в версиях 2003 и ниже максимальное ограничение составляет 30 условий.

Оператор IF, использующий строки

Одним из преимуществ использования оператора If является то, что он по умолчанию не учитывает регистр символов. Это означает, что он будет определять «EXCEL» и «превосходить» как одни и те же значения.

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

Однако, когда вы используете формулу =IF(B3= «Заказано», «Оплачено», «») в столбце C, вы увидите, что Excel идентифицирует значения как нечувствительные к регистру, т. е. для каждого статуса либо как «Заказано»; или «ЗАКАЗАН», значение в столбце «Платеж выполнен» будет «Оплачено».

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

Если вы хотите, чтобы оператор If был чувствителен к регистру для вашего результата, вы должны использовать в своем операторе функцию EXACT. Например, используя формулу =IF(EXACT(B3, «Заказано»), «Оплачено»,»  «) , Excel отобразит результат «Оплачено» только для ячеек в столбце B, которые имеют значение «Заказано».

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

Практический пример № 1

Предположим, что учителю необходимо разделить результаты учащихся своего класса на три разные категории — «Отлично», «Хорошо» и «Удовлетворительно».

Каждый учащийся сдает в общей сложности 6 экзаменов на 600 баллов, из которых он должен набрать больше 430 баллов за «отлично», больше 380, но меньше 430 за «хорошо», а все, что меньше 380, попадает в категорию «удовлетворительно». категория. Набор данных выглядит следующим образом:

Здесь мы использовали комбинацию операторов If и функции Sum, которая проверяет общие оценки, набранные каждым учащимся, и сравнивает их с нашими критериями категоризации. Формула, используемая в ячейке I3, равна 9.0011 =ЕСЛИ(СУММ(C3:h4)>430, «Отлично», ЕСЛИ(СУММ(C3:h4)>380, «Хорошо», «Удовлетворительно»)) .

Итак, когда вы перетащите формулу в ячейку I13, вы увидите следующий результат:

Точно так же вы можете использовать функцию среднего вместе с операторами if, чтобы получить среднее значение баллов, полученных учащимися, если оно больше чем 65 приведет к получению результата «Отлично». Напротив, средний балл ниже 35 будет означать «хорошо». Используемая формула будет следующей: =ЕСЛИ(СРЗНАЧ(C3:h4)>65, «Отлично», ЕСЛИ(СРЗНАЧ(C3:h4)>35, «Хорошо», «Удовлетворительно»)) .

Вот как должна выглядеть наша электронная таблица:

Другая функция, которую можно использовать вместе с операторами IF, — это функции Min/Max. Функция Min позволяет вам найти наименьшее число из группы, а функция Max даст вам наибольшее число. Если вы правильно сформулируете свои формулы, любая из этих функций будет творить чудеса при работе с набором данных.

Если вам нужно найти студента, набравшего наибольшее и наименьшее количество баллов, вы можете использовать формулу =IF(I3=MAX($I$3:$I$12), «Лучшее», IF(I3=MIN($I$3:$I$12), «Средний результат»,»  «)) , что приведет к :

Практический пример #2

Итак, предположим, что вы согласовываете два разных значения в одной из задач, поставленных вашим руководителем. Для некоторых часто используемых терминов, таких как «Да», «Нет», «Ипотека с регулируемой процентной ставкой», сокращенные значения обычно обозначаются как «Y», «N» или «ARM» соответственно. Если вы сравните такие значения с их аналогом с помощью оператора If, это может не дать вам искомого значения.

Давайте рассмотрим пример, чтобы лучше понять его.

Как видно из приведенных выше данных, мы получаем данные из двух источников:

  1. Внутренний источник
  2. Кредитная компания

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

Все наши данные совпадают, кроме данных в столбцах «Предоплата». Мы видим, что во внутренних источниках это представлено как «Д» и «Н», если заемщики кредита произвели или не произвели какие-либо досрочные платежи по своим обязательствам. Однако в источнике обслуживания кредита его значения представлены как TRUE или FALSE.

Как и ожидалось, сравнение обеих ячеек с использованием =K4=L4 даст значение ЛОЖЬ. Итак, как победить эту проблему?

Здесь вы можете использовать комбинацию оператора If и логической функции ИЛИ, чтобы получить любое значение на основе условного оператора. Мы используем формулу =ЕСЛИ(ИЛИ(K4&L4=»NFALSE», K4&L4=»YTRUE»), TRUE, FALSE) .

Объединив оба значения, а затем используя логическую функцию ИЛИ, чтобы получить значение ИСТИНА, наша электронная таблица выглядит следующим образом:

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

Другие важные функции, которые можно использовать в операторах IF

Функции с префиксом Is возвращают значение ИСТИНА или ЛОЖЬ в зависимости от значения, которое они оценивают. Он может либо проверять четные/нечетные значения, существует ли пустая ячейка в электронной таблице, либо возвращать условие как ИСТИНА, если в наборе данных есть ошибки.

Вместо того, чтобы просто помечать ячейки как ИСТИНА или ЛОЖЬ, вы можете включить их в свои условные операторы If, чтобы получить два разных результата на основе логических значений.

Можно использовать следующие функции Is:

Функции с префиксом Is, такие как ISNA, ISERR, ISERROR, можно использовать для обработки ошибок, т. е. для предоставления альтернативных значений в случае возникновения ошибок в Excel. Например, мы использовали формулу =ЕСЛИ(ЕОШИБКА(G6), «Вы нашли ошибку», «Ошибок не найдено»), чтобы проверить наличие ошибок в базе данных. Если ошибка существует, значение будет возвращено как «Вы нашли ошибку», иначе это будет «Ошибки не найдены»

 

Общие сведения о функциях ЕСЛИ, вложенных ЕСЛИ и ЕСЛИ

Excel: Общие сведения о функциях ЕСЛИ, вложенных ЕСЛИ и ЕСЛИ

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

Функция ЕСЛИ

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

=ЕСЛИ(проверка, значение-если-истина, значение-если-ложь)

Попробуем использовать это в примере. У нас есть возраст кого-то, и мы хотим отобразить «ребенок», если ему меньше 18 лет, или «взрослый», если он старше. Это можно сделать с помощью IF, например:

.

  • тест: ВОЗРАСТ<18 лет.
  • значение-если-истина: «ребенок».
  • значение-если-ложь: «взрослый».
  • Формула: =ЕСЛИ(ВОЗРАСТ<18, "ребенок", "взрослый") .

Просто замените ВОЗРАСТ на ячейку, содержащую фактический возраст. В таблице ниже это C2.

И когда мы редактируем возраст, текст обновляется правильно.

Это хорошо, но с вложенным IF мы можем делать еще больше возможностей.

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

Давайте продолжим наш предыдущий пример, но на этот раз мы хотим иметь 3 возрастные группы: «ребенок», если младше 18 лет, «взрослый», если меньше 80, и «старый», если нет. Вы не можете сделать это с одним ЕСЛИ, нужно 2:

=ЕСЛИ(ВОЗРАСТ<18, "ребенок", ЕСЛИ(ВОЗРАСТ<80, "взрослый", "старый"))

Первый ЕСЛИ проверяет, является ли человек ребенком, а если нет, то второй ЕСЛИ проверяет, взрослый он или пожилой человек.

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

Функция IFS

IFS — это совершенно новая функция, которая делает то же самое, что и вложенный IF, но более понятным способом. Выглядит так:

=ЕСЛИ(тест1, значение-если-истина, тест2, значение-если-истина)

И вы можете ставить сколько угодно тестов. Обратите внимание, что эта функция работает только в Excel 2016 и более поздних версиях. Если вы попытаетесь использовать IFS в более старой версии, вы увидите ошибку #ИМЯ?.

С IFS наш предыдущий пример можно переписать следующим образом:

  • Раньше: =ЕСЛИ(ВОЗРАСТ<18, "ребенок", ЕСЛИ(ВОЗРАСТ<80, "взрослый", "старый"))
  • После: =IFS(ВОЗРАСТ<18, "ребенок", ВОЗРАСТ<80, "взрослый", ВОЗРАСТ>=80, "старый")

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

Логические тесты

Вот список всех логических тестов, которые вы можете выполнить в IF, с примерами:

  • Равен: A1=A2.
  • Больше, чем: A1>A2.
  • Больше или равно чем: A1>=A2.
  • Меньше чем: A1
  • Меньше или равно: A1<=A2.
  • Не равно: A1<>A2.

Это довольно просто, за исключением «не равно», которое сочетает в себе «больше чем» и «меньше чем» одновременно.

Бонус

Я сделал небольшую игру, в которой игроки должны угадать мое любимое число. Как бы вы это создали?

Вот ответ ниже. Как видите, можно сделать простую игру в excel всего одной строкой кода 🙂

=IFS(C4<42, "слишком низко", C4>42, "слишком высоко", C4=42, "вы выиграли!")

Заключение

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

К вашему сведению, вот краткий обзор того, что мы рассмотрели:

  • =ЕСЛИ(проверка, истина, ложь)
  • =ЕСЛИ(проверка1, истина, ЕСЛИ(проверка2, истина, ложь))
  • =ЕСЛИ(тест1, истина, проверка2, истина, проверка3, истина)

Другие статьи, которые могут вам понравиться на ExcelFrog.

Читайте также: