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

Содержание

Функция ЕСЛИ (IF) в 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 values 

В приведенных выше примерах мы использовали фиксированный текст для значений True или False. Например, «Пройдено», «Контролируется», «Превышение бюджета» и т. д. В следующем примере, используя тот же лист 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>2000,B2<=2000), «Все верно», «Все или хотя бы одно неверно»)

2000 г. (правда). Таким образом, конечный результат True.

Формула D3:

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

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

Формула D4:

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

Фактические значения в таблице Excel для формата A4 = 8000 долларов (ложь, так как мы проверили больше) и 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)», «Все или хотя бы одна Истина», «Все ложь»)

Формула D3:

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

D4 Формула:

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

Формула D5:

=ЕСЛИ(ИЛИ(A5=6000,B5<=5700,C5=”Контролируется”),”Все или по крайней мере одно верно”,”Все Ложь”)

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

Только для формул ячейки 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 используется следующая формула ЕСЛИ:

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

Формула C3:

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

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

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

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

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

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

Как использовать функцию ЕСЛИ с вложенными формулами в Excel

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

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

Обзор функции ЕСЛИ в Excel

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

Логический тест: Он имеет дело с условием, которое вы оцениваете как истинное или ложное.
Value_if_true: Этот аргумент содержит текст/информацию, которую вы хотите, чтобы функция возвращала, если данные соответствуют проверяемым критериям условия.
Value_if_flase: Как и предыдущий аргумент, он также возвращает информацию, которую вы хотите, чтобы функция возвращала, если условие ложно.

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

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

Пример функции ЕСЛИ в Excel

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

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

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

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

В выделенной формуле B4>=4 — тестовый аргумент, « Допустимый» — аргумент Value_if_true, а аргумент Value_if_false намеренно оставлен пустым.

В случаях, когда аргумент пуст, всегда заключайте его в двойные кавычки («»); в противном случае результат выдаст ошибку или отобразит ноль в ячейке, соответствующей условию.

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

Нажмите Введите клавишу , чтобы применить формулу. Поскольку количество продаж работника 1 равно шести, что больше четырех, выход функции для первой ячейки будет Eligible .

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

При этом функция будет применена к другим ячейкам ниже по строке.

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

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

Связано: Как использовать проверку данных в Excel

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

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

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

 =ЕСЛИ(И(B4>=4,C4>2500),"Допустимо","Недопустимо")

Здесь, в приведенной выше формуле, оператор И используется в качестве проверочного аргумента, так как вы должны проверить два параметра.

Как и в предыдущем случае, если входные данные (Количество продаж и Доход) соответствуют критериям, функция вернет в качестве выходных данных «Подходящий» , иначе «Неподходящий».

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

Как видите, только работники 1, 2 и 4 произвели больше или равно четырем продажам с доходом более 2500. Таким образом, они имеют право на получение бонуса.

Хотя работник 7 совершил четыре продажи, соответствующие первому критерию, его доход меньше 2200. Таким образом, он не имеет права на получение бонуса из-за несоответствия второму условию.

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

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

Таким образом, работники, продавшие четыре или более единиц товара или получившие доход более 2500, будут претендовать на получение бонуса.

Формула будет выглядеть следующим образом:

 =ЕСЛИ(ИЛИ(B4>=4,C4>2500), "Допустимо", "Недопустимо")

Нажмите Введите , чтобы выполнить формулу, и перетащите ее вниз строку, вы получите этот результат.

Как видите, в этом случае Работник 7 также имеет право на получение бонуса, поскольку, хотя он и не достиг порога дохода, но совершил четыре продажи.

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

Функция ЕСЛИ (IF) в Excel примеры

Популярное

Добавить комментарий

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

Как использовать функцию ЕСЛИ с вложенными формулами в Excel

Обзор функции ЕСЛИ в Excel

Пример функции ЕСЛИ в Excel

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

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

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

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

Функция ЕСЛИ (IF) в Excel примеры

Популярное

Добавить комментарий

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

Как использовать функцию ЕСЛИ с вложенными формулами в Excel

Обзор функции ЕСЛИ в Excel

Пример функции ЕСЛИ в Excel

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

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

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

Ssd файл подкачки windows 7: Файл подкачки Windows 7 и ssd: оптимальный размер, как включить

Поиск значения по нескольким условиям в excel: Поиск в excel по нескольким условиям в excel

Цены разгон: ЦЕНЫ В БАТУТНОМ ПАРКЕ «RAZGON»