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

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

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

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

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

      • андро

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

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

        • statanaliz

          »Реализация*» — странная запись. Пришлите файл с примером на почту

  • Аноним

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

  • Аноним

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

    • Аноним

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

      • http://t.me/sohibovs Сайриддин

        Всем добрый день, хотел написат формулу но не получается, как сделать если в ячейке А1 цифра от 0 до 100 плохой, от 101 до 200 слабый, от 201 до 300 средний, от 301 до 400 хороший, от 401 до 500 отличий

  • Аноним

    Доброй ночи,подскажите,где ошибка в формуле =ЕСЛИ(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 руб.

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

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

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

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

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

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

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

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

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

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

  • Аноним

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

    • Аноним

      Дико извиняюсь, коряво нарисовал. Если условный знак в С4,

  • Svetlana Vladi

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

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

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

  • Анна Колтакова

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

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

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

      • Анна Колтакова

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

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

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

  • Аноним

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

  • Аноним

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

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

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

      • Аноним

        спасибо за ответ!!!!

  • Аноним

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

  • Аноним

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

  • Аноним

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

    • Андрей Титов

      =еслимн(f1>20;0,6;f1<20;0,8;f1<10;1)
      Так пойдет?

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

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

  • Аноним

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

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

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

  • Андрей

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

  • Аноним

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

  • Максим

    День добрый.
    Есть расчёт оплаты за пройденный километраж авто. Е4-километраж, G3; G4; G5-руб./км. Но в формулу нужно добавить условие, что при использовании G3 сумма должна быть в пределе D7-мин. значение D8-мах. значение, при использовании G4 сумма должна быть в пределе D8-мин. значение D9-мах. значение, при использовании G5 сумма должна быть в пределе D9-мин. значение D10-мах. значение
    Куда и как воткнуть в формулу D7,D8,D9,D10, чтобы срабатывало условие?
    =ЕСЛИ(И(E4>=0;E4=201;E4=401;E4<=600);E4*F5)

    • Максим

      День добрый.
      Есть расчёт оплаты за пройденный километраж авто. Е4-километраж, G3; G4; G5-руб./км. Но в формулу нужно добавить условие, что при использовании G3 сумма должна быть в пределе D7-мин. значение D8-мах. значение, при использовании G4 сумма должна быть в пределе D8-мин. значение D9-мах. значение, при использовании G5 сумма должна быть в пределе D9-мин. значение D10-мах. значение
      Куда и как воткнуть в формулу D7,D8,D9,D10, чтобы срабатывало условие?
      =ЕСЛИ(E4>=0==401<=600;E4*G5)))

  • Виктор

    Доброго времени суток, помогите пожалуйста. Как заставить Эксель проверить две ячейки а разных листах и при верном условии прибавить к текущему времени 12 часов, иначе НИЧЕГО НЕ ДЕЛАЙ ОСТАВЬ КАК ЕСТЬ.
    Ячейки динамические выгружаются с сайта постоянно меняются.
    =ЕСЛИ(И(‘Лист1′!D31=»Жив»;’Лист2’!D31=»Мертв»);ТДАТА()+ВРЕМЯ(;720;))

    первая часть работает отлично, при выполнении условия накидывает к текущему времени 12 часов, но позже данные изменятся и появится ЛОЖЬ. Как сделать что бы при условии ЛОЖЬ ничего не изменялось в ячейке?

  • Kseniia

    Добрый день.
    Подскажите пожалуйста как сделать правильно:
    =ЕСЛИ(ИЛИ(D6=»e-auctionable»;D7=»e-auctionable»);»OK»;(D6=»non-e-auctionable»;D7=»non-e-auctionable»);»OK»;(D6=»non-e-auctionable»;D7=»e-auctionable»);»NOK»))

    • Kseniia

      =ЕСЛИ(ИЛИ(D6=»e-auctionable»;D7=»e-auctionable»);»OK»;(D6=»non-e-auctionable»;D7=»non-e-auctionable»);»OK»;(D6=»non-e-auctionable»;D7=»e-auctionable»);»NOK»))

  • Елена

    Добрый день! Два дня изучаю функцию если. Не получается. Необходимо если в ячейках C12, D12, E12 есть число (хоть в одной) рассчитать по формуле 1-G12/10.5 Excel пишет «формула в этой ячейке ссылается на диапазон, к которому прилегают другие значения». Подскажите что не так? и как правильно сделать

    =ЕСЛИ(ЕЧИСЛО(C12:E12);»=(1-G12/10,5)»;»»)

  • Ксения

    Здравствуйте, есть ли такая функция, которая подставляла бы в ячейку B2 слово «ОПЛАТИТЬ», если ячейка А2 и(или) C2 заполнены, или слово «ОПЛАЧЕНО», если заполнена ячейка D2?:

    плановая дата прихода предупреждение об оплате дата прихода ГП дата оплаты
    01.01.2020 ОПЛАТИТЬ 02.01.2020
    02.01.2020 ОПЛАЧЕНО 03.01.2020 05.01.2020
    ОПЛАЧЕНО 05.01.2020
    При моей формуле срабатывает только «ОПЛАТИТЬ»:
    =ЕСЛИ(ИЛИ(I30>0;K30>0;);»ОПЛАТИТЬ!!!»;ЕСЛИ(L30>0;»оплачено»;»»))

  • Ксения

    пл.дата пр.___предупр._____дата прих.___дата оплаты
    01.01.2020___ОПЛАТИТЬ___02.01.2020
    02.01.2020___ОПЛАЧЕНО___03.01.2020___05.01.2020
    ______________ОПЛАЧЕНО_________________05.01.2020

  • Аноним

    Добрый день, подскажите, пожалуйста. Стоит задача, чтобы из соседнего листа взять текстовую информацию из ячейки при условии, что в соседней ячейке информация идентичная. Допустим, у меня есть нумерация актов (1,2,3,4…и тд) рядом столбец с названием работ, так вот в другой таблице мне нужно, название работ перенести в рядом стоящий столбец, если я указываю номер акта. (на этом листе может нумерация актов быть не по порядку и даже повторяться) Хочу видеть, если акт номер 25, то название он подтягивает с предыдущего листа формулой

Комментарии для сайта Cackle
Пролистать наверх

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

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

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