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

Функции 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, а также ее более современным вариантом для множества условий ЕСЛИМН. 

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

42 комментария к “Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий”

  1. Аноним

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

      1. =ЕСЛИ(B25=»Реализация*»;СЖПРОБЕЛЫ(ПСТР(B25;28;11));ЕСЛИ(B25=»Перемещение*»;СЖПРОБЕЛЫ(ПСТР(B25;21;11));СЖПРОБЕЛЫ(ПСТР(B25;28;12))))

        не считается формула, прошу помочь

  2. Аноним

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

    1. Аноним

      Для начала, приведите в порядок вашу формулу, столбец С:С должен быть или «Продажа» или «Аренда», далее, допустим что В:В «Магнит» И С:С «Продажа», вызывает сомнение, что продажа Магнит в сумме, меньше 100 (100-SUM(F:F)) непонятно чего, то ли КВт, то ли км.
      P.S. Ваш вопрос, вам правильнее задать на нашем Форуме 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

  3. Аноним

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

    1. Аноним

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

  4. Аноним

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

    1. Аноним

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

  5. Аноним

    Новая Функция ЕСЛИМН впервые появилась 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

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

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

  6. Аноним

    Дмитрий, нужно указывать не только 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

  7. Аноним

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

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

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

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

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

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

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

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

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

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

  10. Аноним

    Доброго времени суток! помогите пожалуйста с формулой. Сам походу не соображу как доработать

  11. Svetlana Vladi

    Здравствуйте! Подскажите, пожалуйста, какую формулу использовать, если нужно посчитать два разных условия (с содержанием трех ЕСЛИ в каждом) и сложить между собой. Считаю з/п менеджеров. Есть два показателя, из которых состоит сумма. И в каждом показателе по 3 условия. Какую формулу использовать?

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

      Здравствуйте. Возможно, внутри ECЛИ для выполнения нескольких условий (любого или одновременно всех) следует использовать функции ИЛИ и И. Для решения задачи нужно видеть данные и конкретное условие. По такому описанию трудно дать точный совет.

  12. Анна Колтакова

    Здравствуйте! подскажите, пожалуйста, какую формулу использовать в следующем случае: есть 4 сотрудника, они получают определенный балл, который считается в зависимости от результатов работы за месяц и необходимо к самому лучшему результату присвоить 30% премии, ко второму по величине результату -20%, третьему — 10%, и самому худшему — 0%.

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

      Здравствуйте. Наверное, нужна комбинация из функций ЕСЛИ и НАИБОЛЬШИЙ. Но конкретную формулу лучше написать исходя из данных и их расположения. Можете прислать файл Excel с примером, я помогу с формулой.

      1. Анна Колтакова

        Здравствуйте, прикрепляю изображение. Помогите, пожалуйста, прописать формулу для колонки Bonus (сейчас там прописаны значения вручную, без формулы), в зависимости от результатов в колонке Score (чем выше значение в Score, тем выше бонус)

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

          Здравствуйте. Комментарий не вижу полностью. Пришлите лучше файл с примером на ящик admin_собака_statanaliz_точка_info

  13. Аноним

    Очень круто все рассказано. Особенно про функцию ЕСЛИ в примере расчета премий.Прям очень помогло решение. Спасибо.

  14. Аноним

    Здравствуйте. Подскажите пожалуйста. Как сделать так, чтобы в зависимости от условия значение ячейки становилось бы равно значению другой ячейки?

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

      Здравствуйте. =ЕСЛИ(проверяемое условие;ссылка на значение в другой ячейке; альтернативное значение или формула)

  15. Аноним

    Добрый день, подскажите, пожалуйста, какую формулу использовать, если нужно посчитать количество значений «ДА» в отдельно указанных ячейках? Пробовала эту формулу =СЧЁТЕСЛИ(N2;AJ2;X2;AT2;BF2;BP2;CB2;CL2;»Да») , не работает.

  16. Аноним

    Добрый день подскажите.
    Помогите прописать формулу со след. условиями.
    Максимальная премия 5000 рублей
    если сотрудник обработал >4500 позиций его премия 5000 рублей
    если 4000

  17. Аноним

    С помощью формул заполнить пустые колонки с условием, что Столбец «Стоимость» вычисляется по условию: от 1 до 10 суток – 100% стоимости, от 11 до 20 суток –80% стоимости, а более 20 – 60% общей стоимости номера за это количество дней. Не могу разобраться с функцией если. Подскажите формулу. Спасибо

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

        Нет. Условие f1<20 сработает раньше чем f1<10. Поэтому, для числа 5 будет выведено 0,8. Нужно поменять местами 2-е и 3-е условия.

  18. Аноним

    Можно спросить ,а как найти эту функцию ЕСЛИМН просто у меня не выходит

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

      Функция появилась в Excel 2016, причем не в первом релизе. Обновите Excel до последней версии.

  19. Андрей

    добрый день. формула не считает почему =ЕСЛИ(B11=»Реализация*»;СЖПРОБЕЛЫ(ПСТР(B11;28;11));ЕСЛИ(B11=»Перемещение*»;СЖПРОБЕЛЫ(ПСТР(B11;21;11));СЖПРОБЕЛЫ(ПСТР(B11;28;12))))

Оставьте комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Пролистать наверх

Электронная книга трюков MS Excel

Секреты эффективной работы

Конфиденциальность данных гарантирована.
Никакого спама.