Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.

значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.

значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.

Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.

Функция Excel ЕСЛИ с одним условием

Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

Прогноз остатков

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

Функция ЕСЛИ для задания условия в формуле

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Несколько условий в функции ЕСЛИ

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

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

Подсказка функции

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

Ошибка из-за нехватки скобки

Функция Excel ЕСЛИМН 

Функция Эксель ЕСЛИ в целом хорошо справляется со своими задачами. Но вариант, когда нужно записывать длинную цепочку условий не очень приятный, т.к., во-первых, написать с первого раза не всегда получается (то условие укажешь неверно, то скобку не закроешь); во-вторых, разобраться при необходимости в такой формуле может быть непросто, особенно, когда условий много, а сами расчеты сложные.

В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.

Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.

Функция Excel ЕСЛИМН

Как видно, запись формулы выглядит гораздо проще и понятнее.

Стоит обратить внимание на следующее. Условия по-прежнему перечисляем в правильном порядке, чтобы не произошло ненужного перекрытия диапазонов. Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

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

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  • Ольга

    Подскажите как правильно задать формулу… к разнице двух ячеек прибавлять единицу когда в третей ячейке буквенное выражение ВКЛ

    • Езепов Дмитрий

      Надо на данные смотреть. Пришлите на почту пример, помогу.

  • Дмитрий

    Здравствуйте!
    Не могу понять, почему функция работает неверно.
    =IF(AND(B:B=»Магнит»; C:C=»Продажа»);100-SUM(F:F); IF(AND(B:B=»Магнит»; C:C=»Аренда»);M3))

  • ольга

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

    • Жук

      Если у вас в столбце например А отображается «Устранено» или «Не устранено», то воспользуйтесь в формуле например Функцией СЧЁТЕСЛИ, =СЧЁТЕСЛИ(A5:A10;»Не устранено») или Функцией СЧЁТЕСЛИМН, если условий больше одного.

  • Тати

    Доброй ночи,подскажите,где ошибка в формуле =ЕСЛИ(F13=»+»;E13*C13;ЕСЛИ(F13=»0″;C13;ЕСЛИ(F13=»-«;0;0)))
    Первый и третий «если» работают,а вот второй не идет никак.

    • Жук

      У вас ошибка в написании формулы, формула должна выглядеть так: =ЕСЛИ(A2=»+»;B2*B2;ЕСЛИ(A2=0;0;ЕСЛИ(A2=»-«;B2))) таким образом F13, должно в вашей формуле, быть равно 0 без кавычек.

  • Жук

    Новая Функция ЕСЛИМН впервые появилась 16 февраля 2016 года в версии 1601 (сборка 6568.2025), точно имеется в версии 1803 (сборка 9126.2259) Office 2016 из состава пакета Office 365.
    К сожалению, Автор статьи указав Excel 2016, не указал номер версии и в какой сборке, когда появилась новая Функция ЕСЛИМН.
    P.S. Все вопрос по работе с Функциями Excel, по ошибкам с формулами, правильнее задавать на нашем Форуме Answers:
    https://answers.microsoft.com/ru-ru/msoffice/forum?sort=LastReplyDate&dir=Desc&tab=Threads&status=all&mod=&modAge=&advFil=&postedAfter=&postedBefore=&threadType=Questions&isFilterExpanded=true&page=1

    • Езепов Дмитрий

      Здравствуйте. Благодарю за уточнение — полезная информация для других пользователей.

  • Жук

    Дмитрий, нужно указывать не только Excel 2016, но и полное её название с версией и сборкой.
    На дворе уже заканчивает свой поход, 2018 год, а проблема с Функцией ЕСЛИМН в Excel 2016 всё равно возникает:
    https://answers.microsoft.com/ru-ru/msoffice/forum/msoffice_excel-mso_other-mso_2016/%d1%84%d1%83%d0%bd%d0%ba%d1%86%d0%b8%d0%b8/04786341-de5e-4792-8551-907077984b14

  • Никита

    Господа, помогите пожалуйста. =16000+(ЕСЛИ(C4<="4";C4*1500; (ЕСЛИ(C4="4";С4*2000; ЕСЛИ(C4<="6"; С4*2500;)))))
    Работает только первое условие, всегда умножается на 1500

    • Езепов Дмитрий

      Измените порядок условий, начните с большого и идите к меньшему. Excel останавливает проверку при выполнении первого условия. Т.е. если число меньше 1500, то дальше не проверяется, хотя, естественно, оно меньше и 2000, и 2500.

  • Шохаббос Эркинов

    рисование списка по категориям в отдельную таблицу (помогите пожалуйста)

  • Шохаббос Эркинов

    например Аббос——————————15000 руб.
    Александр————————15000 руб.
    Настя——————————-12000 руб.
    Вася———————————12000 руб.
    1. таблица (15000) руб.
    Аббос——15000 руб.
    Александр——-15000 руб.

    2. таблица (12000) руб.
    Настя——12000 руб.
    Вася———12000 руб.

    • Езепов Дмитрий

      Так в чем вопрос?

  • Шохаббос Эркинов

    помоги уважаемый админстратор!!!!!

    • Езепов Дмитрий

      Задача совершенно непонятна. Скопируйте нужные строки и сделайте таблицы. Зачем здесь формула?

  • Шохаббос Эркинов

    так что расписание должно быть сделано

    • Езепов Дмитрий

      Извините, но при такой постановке задачи я не могу помочь.