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