Логическая функция ЕСЛИ в Excel является одной из самых популярных функций. Он возвращает результат (значение или другую формулу) в зависимости от условия.
Функция ЕСЛИ в Excel
Функция имеет следующий синтаксис.
IF(log_expression; value_if_true; [value_if_false])
Выражение log_expression — это условие для проверки. Например, A2
value_if_true — значение или формула, возвращаемая в случае наступления события, указанного в первом параметре.
value_if_false — альтернативное значение или формула, которая будет возвращена, если условие не выполняется. Это поле заполнять не нужно. В этом случае, если альтернативное событие произойдет, функция вернет значение FALSE.
Очень простой пример. Нам нужно проверить, превышают ли продажи отдельного товара 30 единиц или нет. Если да, то формула должна вернуть «OK», иначе — «Delete». Расчет и результат показаны ниже.
Продажи первого элемента равны 75, т.е. условие, что они больше 30, выполняется. Поэтому функция возвращает то, что указано в следующем поле — «Ok». Продажи второго элемента меньше 30, поэтому условие (>30) не выполняется, и возвращается альтернативное значение, указанное в третьем поле. В этом и заключается весь смысл функции IF. Потянув расчет вниз, мы получим результат для каждого продукта.
Однако это был демонстрационный пример. Чаще всего формула Excel IF используется для более сложных проверок. Предположим, что в любой момент времени у нас есть средние недельные продажи товаров и их остатки. Покупателю необходимо сделать прогноз по запасам в течение 2 недель. Для этого из текущих запасов вычтите удвоенный средний недельный объем продаж.
Пока все это имеет смысл, но обратная сторона сбивает с толку. Есть ли отрицательные остатки? Нет, конечно, нет. Запасы не могут быть меньше нуля. Отрицательные значения должны быть заменены нулями, чтобы прогноз был верным. Здесь очень помогает формула IF. Он проверит полученное значение по предсказанию и, если оно окажется меньше нуля, то обязательно выдаст в качестве ответа 0, иначе результат вычисления будет тем же, то есть некоторым положительным числом. В принципе, та же логика, только вместо значения мы используем формулу в качестве условия.
В прогнозе акций больше нет отрицательных значений, что в целом является очень хорошим результатом.
Формулы IF в Excel также широко используются в формулах массивов. Мы не будем вдаваться в подробности. Для тех, кому интересно, рекомендую статью о том, как рассчитать максимальное и минимальное значение по условию. Однако расчеты из той статьи больше не актуальны, поскольку в Excel 2016 появились функции МИНИМАЛЬНО и МАКСИМАЛЬНО. Но посмотреть на пример стоит — он пригодится в другой ситуации.
Формула ЕСЛИ в Excel – примеры нескольких условий
Довольно часто количество возможных условий не 2 (проверенное и альтернативное), а 3, 4 и более. В этом случае вы также можете использовать функцию IF, но теперь вам придется вложить их друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.
Несколько менеджеров по продажам должны получать премию в зависимости от выполнения плана продаж. Система поощрения выглядит следующим образом. Если план выполнен менее чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20%, а если план выполнен более чем достаточно — премия 30%. Как вы можете видеть, есть четыре варианта. Чтобы задать их в одной формуле, необходима следующая логическая структура. Если выполняется первое условие, то выполняется первый вариант, иначе, если выполняется второе условие, то выполняется второй вариант, иначе, если … и т.д. Количество условий может быть довольно большим. В конце формулы приводится последний альтернативный вариант, для которого не действует ни одно из вышеперечисленных условий (как третье поле в обычной формуле IF). В результате формула имеет следующий вид.
Комбинация функций IF работает таким образом, что если условие выполнено, то последующие условия больше не проверяются. Поэтому важно изложить их в правильном порядке. Если бы мы начали с B2
При написании формулы легко запутаться, поэтому стоит посмотреть на всплывающую подсказку.
Не забудьте закрыть все круглые скобки в конце, иначе Excel выдаст ошибку
Функция Excel ЕСЛИМН
Функция IF в Excel в целом хорошо справляется со своей задачей. Но вариант, когда нужно написать длинную строку условий, не очень приятен, потому что, во-первых, написать с первого раза не всегда получается (то условие указано неправильно, то скобка не закрывается); во-вторых, разобраться, при необходимости, в такой формуле бывает сложно, особенно когда условий много, а вычисления сложные.
В MS Excel 2016 появилась функция ELIMIN, для которой и написана эта статья. Это то же самое, что и IF, только специально заточенное для проверки нескольких условий. Теперь вам не нужно писать IF сто раз и считать открытые скобки. Вам просто нужно вычислить условия и закрыть одну скобку в конце.
Это работает следующим образом. Давайте рассмотрим приведенный выше пример и воспользуемся новой формулой Excel — ECLIMN.
Как видите, формульная нотация выглядит намного проще и понятнее.
Стоит обратить внимание на следующие моменты. Условия по-прежнему перечисляются в правильном порядке, чтобы не было ненужного наложения диапазонов. Последнее альтернативное условие, в отличие от обычного условия IF, также должно быть указано. В IF указывается только альтернативное значение, которое возникает, когда ни одно из условий не выполняется. Здесь необходимо указать само условие, которое в нашем случае будет B2>=1. Однако этого можно избежать, если в поле условия ввести TRUE, указывая, что если вышеуказанные условия не выполняются, то это TRUE и возвращается последнее альтернативное значение.
Теперь вы знаете, как использовать функцию IF в Excel, а также ее более современную версию для множественных условий ELIMIN.