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

Функция ЕСЛИ в Excel: Объяснение с помощью 5 формул

Оператор ЕСЛИ обычно используется в качестве оператора для принятия решения – для принятия решения на основе определенных вариантов/условий. Если задано условие TRUE , выполните это, а для FALSE сделайте то.

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

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

С точки зрения функции ЕСЛИ:

  • Если оценки больше или равны 60, то Результатом будет Pass .
  • Иначе (для менее 60) результат Fail .

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

=ЕСЛИ(C2>=60,»Удачно»,»Неудачно»)

На этой основе рассмотрим общий синтаксис и параметры функции ЕСЛИ:

IF(logical_test, True_Value, [False_Value])

  • logical_test определяет условие, которое мы хотим проверить. В нашем примере формула C2>=60 является logical_test. Вы можете использовать «=», «<=», «<», «>» и другие операторы.
  • Если логическая_проверка оценивается как True , функция ЕСЛИ возвращает True_Value . В формуле примера «Pass» — это True_Value. Это тоже требуется .
  • Если logical_test имеет значение False, функция Excel IF возвращает значение False_Value . В примере «Fail» — это False_Value. Это необязательный .
  • Простая функция ЕСЛИ дает два результата (Истина/Ложь). Если вам нужны дополнительные результаты, используйте вложенный ЕСЛИ. Вложенные функции If могут иметь до 64 результатов.

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

Простой пример ЕСЛИ в Excel

В примере простой функции ЕСЛИ я буду использовать тот же сценарий, что и во вводном разделе. Если количество баллов больше или равно 60 , то в столбце «Результат» должно быть указано «Пройдено». Для менее чем 60 это должно быть Fail. Взгляните на формулу и демонстрационный лист Excel.

=ЕСЛИ(B5>=60,»Пройдено»,»Не пройдено»)

Пример проверки двух столбцов в функции IF «Еще»

В этом примере два значения столбца сравниваются в функции IF . Столбец A содержит Прибыль, а столбец B содержит Расходы.

Если расходы превышают доходы, то в столбце C (Статус) должно отображаться «Превышение бюджета» , в противном случае должно отображаться «Контролируется».

Формула для ячейки C2:

=ЕСЛИ(A2>B2, «Контролируется», «Превышение бюджета»)

Вы видели, мы использовали оператор «больше» в приведенной выше формуле, то есть «>». В качестве альтернативы вы также можете использовать «<=» для получения того же результата следующим образом:

=IF(B2<=A2, «Контролируется», «Превышение бюджета»)

Пример использования рассчитанного результата для значений True/False

В приведенных выше примерах мы использовали фиксированный текст для True или Fal се значения. Например, «Пройдено», «Контролируется», «Превышение бюджета» и т. д. В следующем примере, используя тот же лист Excel, мы рассчитаем, остался ли бюджет или он уже закончился.

Для этого мы будем использовать столбцы «AB» в функции ЕСЛИ следующим образом:

Формула функции ЕСЛИ:

=ЕСЛИ(B2<=A2,A2-B2,0)

Итак, функция ЕСЛИ проверенное значение ячейки B2 (Расходы) меньше или равно A2 (Бюджет). Если это True, т. е. сумма, оставшаяся на расходы, оставшаяся сумма отображается в соседнем столбце, т. е. D2. Если False, то будет просто отображаться 0. Та же формула применяется к другим ячейкам (от D2 до D4):

Пример функций ЕСЛИ и «И» Excel

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

Как и при использовании функции AND, если все условия оцениваются как True , будет возвращено значение True. Если какое-либо из условий имеет значение False, возвращается значение False.

Чтобы продемонстрировать это, функция И используется с функцией ЕСЛИ на том же листе Excel, что и в предыдущем примере, и к ячейкам D2, D3, D4 и D5 применяются следующие формулы: Ложь»)

Если вы внимательно посмотрите на график выше, A2 = 2500 долларов (правда) и B2 = 2000 (правда). Таким образом, конечный результат True.

Формула D3:

= ЕСЛИ (И (A3> 4000, B3 <= 3000), «Все верно», «Все или хотя бы одно неверно»)

В листе Excel A3 = 3500 (ложь) И B3 = 3700 (верно). Конечным результатом является «Все или по крайней мере одно неверно». Потому что одно условие ложно.

Формула D4:

=ЕСЛИ(И(A4>8000,B4>6000),»Все верно»,»Все или хотя бы одно неверно»)

Фактические значения в таблице Excel для мы проверили больше) и B4 = 6500 (Истина). Поскольку одно истинно, а другое ложно, результатом функции ЕСЛИ является «Все или хотя бы одно ложно».

Формула D5 ЕСЛИ И Excel:

=ЕСЛИ(И(A5=5500,B5<=5700,C5=«Превышение бюджета»), «Все верно», «Все или хотя бы одно неверно»)

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

  • A5 = 5000 (Истина)
  • В5 = 5700 (Истина)
  • C5 = «Превышение бюджета» (правда)

Таким образом, все условия верны, и возвращаемое значение  Все верно .

Пример функций ЕСЛИ/ИЛИ для нескольких условий

Вы также можете использовать функцию ИЛИ с ЕСЛИ для проверки нескольких условий. Разница между функциями Excel ИЛИ и «И» заключается в том, что ЕСЛИ возвращает True_Value, если какое-либо из условий истинно. В то время как в случае AND, как мы видели, все условия должны быть True.

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

Следующие формулы ЕСЛИ используются с ИЛИ для ячеек от D2 до D5:

Формула D2 с ЕСЛИ/ИЛИ:

=ЕСЛИ(ИЛИ(A2>2000,B2<=2000), «Все или хотя бы одно верно», «Все ложь»)

900 37

Формула D3:

=ЕСЛИ(ИЛИ(A3>4000,B3<=3000),"Все или хотя бы одно верно","Все ложь")

Формула D4:

900 34

=ЕСЛИ(ИЛИ(A4>8000,B4>6000),»Все или хотя бы одно верно»,»Все неверно»)

Формула D5:

= ЕСЛИ (ИЛИ (A5 = 6000, B5 <= 5700, C5 = «Контролируется»), «Все или хотя бы одно верно», «Все неверно»)

понять, как функция ИЛИ работает с ЕСЛИ для оценки нескольких условий:

Только для формул ячеек D3, все условия ложны. Для других ячеек по крайней мере одно условие имеет значение True, поэтому оно вернуло True_Value.

Вложенный ЕСЛИ Пример

Вложенный ЕСЛИ означает использование нескольких функций ЕСЛИ в одной формуле. Простая функция ЕСЛИ дает две возможности. То есть значение True или False. Бывают ситуации, когда вам может потребоваться иметь дело с большим количеством возможностей. Например, возьмем сценарий Студенческие оценки (как в первой демонстрации). Там у нас было только два результата: Pass или Fail.

Позвольте мне расширить этот пример, чтобы продемонстрировать, как работает вложенный ЕСЛИ (IF..elseif) в Excel. Возьмем такую ​​ситуацию:

  • Если оценки находятся между от 1 до 40 , тогда результат должен быть E
  • Если оценки находятся между 41 и 50 , то результат должен быть D
  • от 51 до 60 = С
  • от 61 до 70 = В
  • от 71 до 80 = А
  • от 81 до 100 = А+

Для этого столбцы с B2 по B7 заполняются отметками. Столбцы с C2 по C7 отображают результат от E до A+ с использованием формулы вложенного ЕСЛИ.

В ячейке C2 используется следующая формула ЕСЛИ:

=IF(B2<=40,"E",IF(B2<=50",D",IF(B2<=60",C",IF(B2<=70",B",IF(B2<=80",A",IF(B2<=100,A+"))))))

C3 Формула:

=ЕСЛИ(B3<=40,"E",IF(B3<=50",D",IF(B3<=60",C",IF(B3<=70,B",IF(B3<=80,A",IF(B3<=100,A+"))))))

И так далее. Просто замените B2 на B3, B4 и так далее.

Результирующий лист показан ниже:

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

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

Примечание: Чтобы запустить эту формулу в вашей системе, вы должны ввести число от 1 до 100. Или соответствующим образом изменить диапазон чисел в формуле ЕСЛИ.

ЕСЛИ: объяснение формул Excel

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

Синтаксис ЕСЛИ

Синтаксис функции ЕСЛИ следующий:

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

Где:

  • логическая_проверка состояние, которое вы хотите оценить. Это может быть сравнение двух значений, ссылка на ячейку или комбинация нескольких условий с использованием функций И, ИЛИ или НЕ.
  • value_if_true — это значение или формула, которая будет возвращена, если logical_test имеет значение TRUE.
  • value_if_false — это значение или формула, которая будет возвращена, если logical_test имеет значение FALSE. Этот аргумент является необязательным, и если его не указать, Excel по умолчанию вернет FALSE.

Примеры ЕСЛИ

Рассмотрим несколько примеров использования функции ЕСЛИ в Excel:

  1. Базовая функция ЕСЛИ: Предположим, у вас есть список учащихся и их оценки за тест. Вы хотите определить, сдал или не сдал учащийся, основываясь на проходном балле 60. Формула будет следующей: =ЕСЛИ(A1>=60, «сдал», «не сдал»), где A1 содержит балл учащегося. Если оценка больше или равна 60, формула вернет «Пройдено»; в противном случае он вернет «Fail».
  2. Вложенная функция ЕСЛИ: Вы можете использовать несколько функций ЕСЛИ в одной формуле для обработки более сложных условий. Например, вы хотите классифицировать успеваемость учащихся по трем уровням: «Отлично», «Хорошо» и «Требуется улучшение» на основе их оценок. Формула будет следующей: =ЕСЛИ(A1>=90, «Отлично», ЕСЛИ(A1>=70, «Хорошо», «Требуется улучшение»)). Эта формула сначала проверяет, больше или равно значение 90; если TRUE, возвращается «Отлично». Если FALSE, он переходит к следующей функции ЕСЛИ, которая проверяет, больше или равно 70 баллов; если TRUE, возвращается «Хорошо». Если оба условия FALSE, возвращается «Требуется улучшение».
  3. ЕСЛИ с функцией И: Вы можете использовать функцию И для объединения нескольких условий в одну функцию ЕСЛИ. Например, вы хотите определить, имеет ли студент право на получение стипендии, основываясь на его баллах и посещаемости. Студент должен иметь не менее 80 баллов и посещаемость не менее 90%. Формула будет следующей: = ЕСЛИ (И (A1> = 80, B1> = 0,9), «Допущено», «Не соответствует»), где A1 содержит балл учащегося, а B1 — его посещаемость. Если оба условия соблюдены, формула вернет «Доступно»; в противном случае он вернет «Не соответствует требованиям».

Советы и рекомендации по ЕСЛИ

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

  • При использовании вложенных функций ЕСЛИ старайтесь свести уровни вложенности к минимуму, чтобы формула не стала слишком сложной и трудной для чтения. Если вы обнаружите, что используете несколько вложенных функций ЕСЛИ, рассмотрите возможность использования других функций, таких как ВПР, ГПР или ИНДЕКС-ПОИСКПОЗ, чтобы упростить формулу.
  • Используйте функцию ЕСЛИОШИБКА для обработки ошибок в формулах ЕСЛИ. Например, если у вас есть формула, которая делит два числа, и вы хотите отобразить «Н/Д», если есть ошибка деления на ноль, вы можете использовать следующую формулу: =ЕСЛИОШИБКА(A1/B1, «Н/Д»).
  • При работе с текстовыми значениями обязательно заключайте их в двойные кавычки («») в формулах ЕСЛИ. Например, =IF(A1=»Да», «Утверждено», «Отказано»).

Распространенные ошибки при использовании функции ЕСЛИ

Вот некоторые распространенные ошибки, которых следует избегать при использовании функции ЕСЛИ:

  • Забыть закрыть круглые скобки в конце формулы. Каждая функция ЕСЛИ требует закрывающей скобки, и если у вас есть вложенные функции ЕСЛИ, вам нужно закрыть каждую из них. Excel обычно выделяет отсутствующие скобки, но важно дважды проверить формулу.
  • Использование неправильного оператора сравнения. Обязательно используйте правильный оператор для логического_теста, например «=», «<>«, «>», «<", ">=» или «<=".
  • Не использовать двойные кавычки для текстовых значений. Не забывайте заключать текстовые значения в двойные кавычки при использовании их в формулах ЕСЛИ.

Почему моя функция ЕСЛИ не работает?

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

  • Ошибка в логическом_тесте: Дважды проверьте свой logical_test, чтобы убедиться, что он оценивает правильное условие. Убедитесь, что вы используете правильный оператор сравнения и ссылаетесь на правильные ячейки.
  • Отсутствует или неверное значение_если_истина или значение_если_ложь: Убедитесь, что вы указали правильные значения или формулы для аргументов значение_если_истина и значение_если_ложь. Если вы используете формулу, убедитесь, что она действительна и не содержит ошибок.
  • Проблемы вложения: Если вы используете вложенные функции ЕСЛИ, убедитесь, что у вас правильное количество открывающих и закрывающих скобок и что функции правильно вложены.
  • Проблемы с форматированием: Если функция ЕСЛИ возвращает неожиданные результаты, это может быть связано с форматированием ячеек. Проверьте формат ячеек, на которые вы ссылаетесь в своей формуле, и внесите необходимые изменения.

ЕСЛИ: связанные формулы

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

  1. ЕСЛИОШИБКА: Используйте эту функцию для обработки ошибок в формулах, возвращая указанное значение в случае ошибки. Например: =ЕСЛИОШИБКА(A1/B1, «Ошибка»).
  2. IFS: Эта функция позволяет проверить несколько условий и вернуть значение для первого условия, которое является ИСТИННЫМ, без необходимости использования вложенных функций ЕСЛИ. Например: =IFS(A1>=90, «Отлично», A1>=70, «Хорошо», A1<70, "Требуется улучшение").
  3. И, ИЛИ, НЕ: Используйте эти логические функции для объединения нескольких условий в формулах ЕСЛИ.