Как использовать функцию ЕСЛИ с вложенными формулами в Excel
Большинство людей знакомы с использованием функций ЕСЛИ для проверки условия на большом наборе данных. Однако они могут не знать о преимуществах их использования в сочетании с операторами OR, AND или другими операторами и функциями.
Прежде чем двигаться дальше, давайте посмотрим, как выглядит простая функция ЕСЛИ и как правильно использовать аргументы.
Обзор функции ЕСЛИ в Excel
Как показано выше, функция ЕСЛИ использует три аргумента, описанных ниже:
- Логический тест: он имеет дело с условием, которое вы оцениваете как истинное или ложное.
- Value_if_true: этот аргумент содержит текст / информацию, которую функция должна возвращать, если данные соответствуют критериям проверенного условия.
- Value_if_flase: как и приведенный выше аргумент, он также возвращает информацию, которую функция должна возвращать, если условие ложно.
Первый аргумент необходим для выполнения функции ЕСЛИ; два других необязательны. Вы можете добавить любой текст к последним двум аргументам или оставить их пустыми. Если вы оставите один или оба последних аргумента пустыми, результатом также будет пустая ячейка.
Теперь давайте посмотрим, как можно использовать функцию ЕСЛИ для анализа нескольких условий в одной формуле. Кроме того, вы также узнаете, как использовать его во вложенной формуле с операторами OR и AND.
Пример функции ЕСЛИ в Excel
Предположим, вы работаете в ювелирном магазине, где вы регистрируете продажи и доходы каждого из семи рабочих, работающих в магазине. В конце каждой недели компания выплачивает еженедельный бонус только тем сотрудникам, которые достигли определенного порогового значения.
Ниже вы видите количество продаж и доходов, полученных каждым работником за одну неделю.
Предположим, что порог бонуса на этой неделе – это количество продаж, равное или превышающее 4. Чтобы проверить, кто из сотрудников получит бонус на этой неделе, вы воспользуетесь простой функцией ЕСЛИ.
Итак, вы будете использовать количество продаж в качестве аргумента тестирования в функции ЕСЛИ, как показано ниже.
В выделенной формуле B4> = 4 – это тестовый аргумент, « Допустимый» – это аргумент Value_if_true, а аргумент Value_if_false намеренно оставлен пустым.
В случаях, когда аргумент пуст, всегда заключайте его в двойные кавычки (""); в противном случае результат выдаст ошибку или отобразит ноль в ячейке, удовлетворяющей условию.
После проверки количества продаж каждого работника функция ЕСЛИ должна возвращать результаты как Допустимые, если количество продаж больше или равно четырем; в противном случае оставьте ячейки пустыми.
Нажмите клавишу Enter, чтобы ввести формулу. По мере того как количество продаж работника 1 составляет шесть, которая больше , чем четыре, выход функции для первой ячейки будет иметь право.
Вам не придется вводить формулу для всех ячеек по отдельности. Вместо этого, используя функцию последовательности автозаполнения, переместите курсор в нижний левый угол выбранного блока и перетащите его вниз.
В результате функция будет реализована для других ячеек в строке.
Посмотрите, как рабочие 1, 2, 4 и 7 достигают порога чуть меньше четырех продаж и, таким образом, имеют право на премию, тогда как остальные ячейки остаются пустыми, поскольку эти рабочие не достигают порога.
Скажем; вместо того, чтобы оставить второй аргумент пустым, вы поместили его в поле «Не соответствует требованиям». В этом случае окончательный результат будет таким, как показано ниже.
Использование оператора И с функцией ЕСЛИ
В течение следующей недели компания изменила свою политику предоставления бонусов и добавленной выручки в установленном пороге с количеством продаж. Таким образом, вы должны анализировать одни и те же данные, но с двумя условиями тестирования вместо одного.
Компания выплачивает премию работникам, которые производят не менее четырех продаж с выручкой более 2500. В этом случае вы будете использовать операцию И, и формула будет иметь следующий вид:
=IF(AND(B4>=4,C4>2500),"Eligible","Ineligible")Здесь, в приведенной выше формуле, оператор AND используется в качестве тестового аргумента, поскольку вам нужно проверить два параметра.
Как и в предыдущем случае, если входные данные (количество продаж и доход) удовлетворяют критериям, функция вернет в качестве выходных данных «Допущено» , в противном случае – «Не допустимо» .
Нажмите клавишу Enter, чтобы выполнить функцию, а затем перетащите ее вниз, чтобы применить ту же формулу к остальной части набора данных. Вы увидите окончательные результаты следующим образом.
Как видите, только рабочие 1, 2 и 4 – это те, кто произвел больше или равный четырем продажам с доходом более 2500. Таким образом, они имеют право на бонус.
Хотя работник 7 произвел четыре продажи, которые соответствовали первому критерию, его доход меньше 2200. Таким образом, он не имеет права на получение бонуса из-за несоблюдения второго условия.
Использование оператора OR с функцией IF
Уже третью неделю компания получила хорошую прибыль и выплачивает бонусы работникам, выполнившим одно из двух условий. В этом случае вы можете использовать оператор OR в качестве тестового аргумента для оператора IF, чтобы отфильтровать точное количество рабочих.
Таким образом, работники, которые продали четыре или более предметов или получили доход более 2500, будут иметь право на получение бонуса.
Формула будет выглядеть так:
=IF(OR(B4>=4,C4>2500), "Eligible", "Ineligible")Нажмите Enter, чтобы выполнить формулу, и, перетащив ее вниз по строке, вы получите этот результат.
Вы можете видеть, что Рабочий 7 также имеет право на получение бонуса в этом случае, поскольку, хотя он не достиг порога дохода, но сделал четыре продажи. Он выполняет одно условие, что дает ему право на получение бонуса.
Точно так же вы можете использовать функцию IF с операторами AND и OR, а также с другими функциями для фильтрации результатов из большого набора данных.
Упростите вычисления с помощью функции ЕСЛИ во вложенных формулах
Когда вы объединяете функцию ЕСЛИ с другими функциями, вы можете одновременно тестировать несколько условий на большом наборе данных.
Это избавляет от необходимости вручную тестировать несколько условий по отдельности, экономя ваше время и усилия. Автоматизация основных функций также позволит вам работать более продуктивно.