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