Несколько условий ЕСЛИ в Excel

Функция ЕСЛИ в Excel позволяет оценивать ситуацию с двух точек зрения, например, значение больше 0 или меньше, и в зависимости от ответа на этот вопрос, произведи дальнейшие расчеты по той или иной формуле. Однако, не редки ситуации, когда вам приходится работать более, чем с двумя условиями. В сегодняшней статье мы рассмотрим примеры создания формул в Excel с несколькими условиями ЕСЛИ.

Прежде, чем начать изучать данный урок, рекомендую прочитать статью про функцию ЕСЛИ, где описаны основные приемы работы.

Принцип создания формул с несколькими условиями ЕСЛИ заключается в том, что в одном из аргументов формулы (значение_если_ИСТИНА или значение_если_ЛОЖЬ) находится еще одна формула ЕСЛИ.

Например: =ЕСЛИ(A5=0;»НОЛЬ»;ЕСЛИ(A5<0;»МЕНЬШЕ НОЛЯ»;»БОЛЬШЕ НОЛЯ»)), где функция оценивает значение ячейки A5 два раза, первый, проверяет, равняется ли значение нулю, и возвращает текст – НОЛЬ, если ИСТИНА. Если результат оценки вернул значение ЛОЖЬ, происходит вторая оценка, функция проверяет, является ли значение ячейки A5 меньше ноля, и возвращает текст МЕНЬШЕ НОЛЯ, если результат ИСТИНА, в противном случае возвращает текст БОЛЬШЕ НОЛЯ.

97-1-несолько условий Если в Excel

Таким образом, в примере выше, формула вернет значение МЕНЬШЕ НОЛЯ, так как при первой оценке, результат оказался ЛОЖЬ, а при второй оценке ИСТИНА.

Давайте рассмотрим пример посложнее. Предположим, вам необходимо рассчитать размер комиссии каждого продавца в зависимости от объема его продаж.

97-2-несолько условий Если в Excel пример 2

Ваш план таков:

  • Если продажи меньше или равны 500$, комиссия составляет 7%
  • Если продажи больше 500$, но меньше или равны 750%, комиссия составляет 10%
  • Если продажи больше 750$, но меньше или равны 1000%, комиссия составляет 12,5%
  • Если продажи больше 1000$, комиссия составляет 16%

Вместо того, чтобы рассчитывать размер комиссии для каждого работника, можно создать формулу с несколькими условиями ЕСЛИ. Логика формулы будет следующая:

  • Продажи меньше или равны 500$. Если ИСТИНА, рассчитываем комиссию.
  • Если ЛОЖЬ, то продажи меньше или равны 750$. Если ИСТИНА, рассчитываем комиссию.
  • Если ЛОЖЬ, то продажи меньше или равны 1000$. Если ИСТИНА, рассчитываем комиссию.
  • Если ЛОЖЬ, рассчитываем комиссию, так как это будет означать, что продажи больше 1000$ и больше логических тестов проводить не нужно.

Давайте создадим формулу следуя данной логике для продавца Сергея. (Я выделил жирным проверку логики для лучшего понимания).

=ЕСЛИ(B4<400;B4*7%;ЕСЛИ(B4<750;B4*10%;ЕСЛИ(B4<1000;B4*12.5%;B4*16%)))

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

Логическое выражение в первой формуле ЕСЛИ проверяет, является ли значение в ячейке B4 меньше 400, если ИСТИНА, формула умножает значение ячейки B4 на 7% и останавливает дальнейшие вычисления. Если значение ячейки B4 больше 400, мы переходим к следующей функции ЕСЛИ. Так будет продолжаться, пока мы не достигнем последнего значения, где значение ячейки умножается на 16%. Это значит, что ни одно из условий не удовлетворило требованиям, т.е. продажи составляют более 1000$.

Ниже вы видите, как будет выглядеть колонка Комиссия, когда все формулы будут введены. Также в колонке Формула отображены формулы для каждого продавца.

97-3-несколько условий если excel формула

Можно проверить на примере Натальи правильность работы формулы. Продажи Натальи составили 844$, т.е. больше, чем 750$, но меньше чем 1000$. Соответственно, коэффициент комиссии будет равняться 12,5%, а сама комиссия составит 105,5$. Также важно отметить, о работе формулы с пограничными значениями. Предположим, что сумма продаж Натальи составила 750$, какой коэффициент должна применить формула? Коэффициент будет 12,5%, так как для коэффициента 10% сумма продаж должна равняться меньше 750. Это важное замечание, поэтому будьте аккуратны при составлении логики формулы.

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


74 комментария

  1. Нужно вернуть определенное значение из ячейки и посчитать балл, т.е. например в ячейке D3 может быть значение А, Б, В, Г, надо в ячейку D4 вернуть значение в зависимости от буквы, например А=1, Б=2, В=3 и так далее. Как сделать? Можно ли через формулу ЕСЛИ?

  2. Статья хорошая, спасибо.
    Но.. вначале статьи планы ставят из минимального расчета 500$, а все дальнейшие расчеты исходят из 400$.
    Как бы надо стараться следовать тем планам, что ставите.

  3. Данный метод хорош, если у нас немного критериев (2-3), но когда их 10, то в такой формуле потом трудно разобратся «что и откуда». В таком случае можно (и нужно) обойтись без ЕСЛИ.
    Для этого создаем маленькую табличку с нашими критериями: в первой строке по возрастанию заполняем критерии (в приведенном примере это будут 0, 500, 750, 1000); во второй строчке под каждым критерием заполняем соответствующий процент (7, 10, 12,5, 16). Допустим, в диапазоне A1:D1 у нас заполнены критерии, а в диапазоне A2:D2 — соответствующие проценты. В ячейке А5 имеем цифру продаж; для рассчета комиссии используем следующую формулу: =A5*ИНДЕКС($A$2:$D$2;ПОИСКПОЗ(A5;$A$1:$D$1;1)).
    ПОИСКПОЗ ищет расположение критерия, который меньше продаж, но наибольший в списке, а ИНДЕКС по полученному номеру выдает нам необходимый процент.

    Файл:  .xlsx

  4. Добрый день, прописал =ЕСЛИ(C5>D5;3;ЕСЛИ(C5=D5;1;ЕСЛИ(C5<D5;0;))), и даже при пустых ячейках ставить единицу, как сделать так что бы если ячейка пуста — функция не работала и оставляла там пустое место либо =0.

    • Здравствуйте, Виктор.
      Для решения Вашей проблемы пропишите еще одной встроенной функцией ЕСЛИ проверку ячеек на пустоты:
      =ЕСЛИ(ИЛИ(C5=»»;D5=»»);»»;ЕСЛИ(C5>D5;3;ЕСЛИ(C5=D5;1;ЕСЛИ(C5<D5;0;))))

  5. А если все тоже самое только исходить от даты ? Например если продавец 1.1.2016 в этот день продает до 500 то 7% если до 750 то 10% если до 1000 то 12.5% ? Ну и соответственно что бы формула работала и по следующим дням? Проще говоря что бы % ставка была зависима от объёма продаж в день. Заранее спасибо если поможете )

    • Дмитрий, формула будет работать и по дням тоже. Только в таком случае вам необходимо будет переформатировать саму таблицу с продавцами, чтобы она отражала результаты по дням, а не консолидированно по продавцам

  6. Добрый день!
    Когда-то давно делала такую формулу с примерно 8 условиями, как-то справилась. Сейчас же на три условия формула никак не хочет работать. Вот такая формула:

    ЕСЛИ(L34>3999;»12%»;ЕСЛИ(L34>7999;»15%»;ЕСЛИ(L34>11999;»20%»;»0%»)))

    Задача прям как у вас в примере. Если продавец за день продал меньше, чем на 4000, тогда ему полагается 0%, если от 4000 до 7999 — 12%, и далее ниже:
    4000-7999 12%
    8000-11999 15%
    >12000 20%
    Формула, вроде, рабочая, но при изменении суммы продажи меняется на ноль или не меняется вовсе, как-то так. Что нужно исправить в формуле?

    • То есть не «не меняется вовсе», а меняется неправильно, не соответствуя формуле. Сейчас у меня в таблице выручка за день 10 000 р. Должно начисляться 15%. А формула показывает 12%.

      • Марина, логика программы следующая. Формула читается слева направо, и как только формула видит первое истинное значение, она останавливается и выдает результат. В вашем случае, формула проверяет значение 10000 руб, оно выдает ИСТИНА, при первой проверке L34>3999, выводит результат 12% и останавливается. Чтобы обойти данный финт, попробуйте сформулировать формулу начиная с максимального значения.

      • сраный комментарий сожрал пол текста. 1 выриант был через формулу И. 2 вариант необходимо использовать значения от большего к меньшему. сначала 11999 потом > 7999 …. >3999 … 0

  7. Ренат, добрый день. Я работаю в мебельной компании, мы закупаем ткани у многих производителей. У производителя цена на ткани в долларах, мы продаем в рублях, в связи с нестабильность курса, цены меняются очень часто. Для продажи диванов в этих тканях, мы устанавливаем цены в зависимости от категории ткани. Категория зависит от цены. Мне нужно сделать формулу, чтобы при попадании цены ткани под условия той или иной категории, значение этой категории проставлялось автоматически. Я загрузила файл с данными. Очень надеюсь на помощь, спасибо.

  8. А я так не люблю эти вложенные ЕСЛИ… Пока разберешься в их количестве скобок, глаза повыпучиваешь.
    я объединяю несколько формул ЕСЛИ подряд

    =ЕСЛИ(И([@[Время занятия]]>ВРЕМЯ(7;0;0);[@[Время занятия]]=ВРЕМЯ(14;0;0);[@[Время занятия]]=ВРЕМЯ(18;0;0);[@[Время занятия]]<ВРЕМЯ(23;59;0));"Вечер";"")
    Даже, если мне нужно получить числовое значение, этот прием работает.(просто обернем формулу в "ЗНАЧ() )
    Главное внимательно следить, чтобы диапазоны условий не пересекались, и тогда не будет противоречий..

    • К сожалению разметка сайта съела весь код.. Смысл в том, что мы объединяем несколько формул если через символ &. Каждая формула дает либо пустую строку («»), либо нужное значение, в результате получаем то что нам нужно, выглядит очень красиво ( по формуле на строку)

  9. Добрый день! помогите, пожалуйста, с формулой.
    Условия такие:
    если 98,5<Х<101,5, то 100%
    если Х101,5, то 101,5%
    Пробовала разные варианты: то выдает ошибку, то максимум выполняются последние 2 условия, а первое не отображается.

  10. Добрый день!
    помогите, пожалуйста, найти ошибку в формуле:
    =СЧЁТЕСЛИМН(‘[Отгрузки.xlsx]MAIN LIST’!$T:$T,»09.05.2016:13.05.2016″,'[Отгрузки.xlsx]MAIN LIST’!$R:$R,»фамилия1, фамилия2″)
    смысл: считать количество ячеек(строк) при выполнении 2 условий: в заданном диапазоне времени (календарная неделя) и по фамилии ответственного сотрудника.

    спасибо.

  11. опечаталась:
    =СЧЁТЕСЛИМН(‘[Отгрузки.xlsx]MAIN LIST’!$T:$T,»09.05.2016:13.05.2016″,'[Отгрузки.xlsx]MAIN LIST’!$R:$R,»фамилия1, фамилия2″)
    с кавычками всё нормально, всё равно не считает.

  12. Добрый день !
    Помогите, пожалуйста, как правильно составить формулу:
    есть 2 значения, например первая цифра 50 другая 100 разница — 50
    другая 100 и 50 разница 50.
    как сделать так, чтоб где разница была положительной — писалась эта цифра, а там где отрицательное значение был прочерк «-«, заранее спасибо !

  13. А как будет выглядить логическая функция, если мне надо скажем поизвести деление, но если хотя бы в одной из ячеек 0 (то есть пусто) функция не работала и игнорировала их?

  14. Добрый день! Подскажите как из ячейки с текстом вытянуть определенные данные используя формулу «если». Например в ячейке: код банка 0000 код магазина PR221, сумма 45.60 . Нужно вытянуть в отдельные ячейки код магазина и сумму. Спасибо

  15. Здравствуйте. Бьюсь уже час. Помогите!
    А задача проста.
    если выполнение плана меньше 100%-найти каков % выполнения, если больше 100% =100.
    Моя формула =ЕСЛИ(C7*(100);C7/C6;ЕСЛИ(C7>100;100%)) не работает.

  16. Подскажите формулу (нужно составить таблицу а в формулах я очень слаб)

    Если числовые значения (возраст в моём случае) с 0 до 3 то это одна категория, с 3 до 6 вторая, с 6 до 14 третья, с 14 до 18 четрёртая и более 18 пятая. Большое человеческое спасибо.
    Нужно срочно для статистики летнего семейного лагеря.

  17. Подскажите пожалуйста формулу.
    У меня несколько значений если.
    Задача: Если выполнение плана по продаже всех продуктов менее 50%, при этом выполнении плана по продаже спец продукта менее 50% -0%; менее 80% — 2%; более 80%-4%. Если выполнение плана по продаже всех продуктов менее 80% при этом выполнение плана по продаже спец продукта менее 50% -2%; менее 80% — 4%; более 80%-7% и если выполнение плана по продаже всех продуктов более 80%, а выполнение плана по продаже спец продукта менее 50% -4%; менее 80% — 7%; более 80%-10%.
    Подскажите пожалуйста кто знает, всю голову сломала. Может есть какая-то специальная функция?

  18. Как округлить в формуле где есть ЕСЛИ несколько условий

    Здравствуйте уважаемый Ренат!
    Скажите пожалуйста как написать формулу чтобы в Excelе «Несколько условий ЕСЛИ» округлить. Округлить например до сотых. Например, как написать формулу=ЕСЛИ(B4<400;B4*7%;ЕСЛИ(B4<750;B4*10%;ЕСЛИ(B4<1000;B4*12.5%;B4*16%)))
    чтобы в результате было Округление, например до сотых (то есть после запятой было две цифры.
    Я пробовал, но у меня не получается. Вот например, как округлить просто число в ячейке я знаю: =ОКРУГЛ(G5;2).

  19. Здравствуйте
    Как записать правильно форумулу!
    Если значение в ячейке лист2 В1 равно значению из лист1 А1:А100, то копируем значение с этой строки лист1 В1:В100 в ячейку лист2 С1. Если нет,+25.

  20. Как записать правильно форумулу!
    Если значение в ячейке лист2 В1 равно значению из лист1 А1:А100, то копируем значение с этой строки лист1 В1:В100 в ячейку лист2 С1. Если нет,+25.

    добавил фаил

    Файл:  ..xlsx

  21. Добрый день.
    Использую Excel 2003. И столкнулся с ограничением применения условия ЕСЛИ(), т.е. уровень вложенности не должен превышать больше 7.
    Вот самой условие:
    //*****************************//
    =ЕСЛИ(/*условие на последние дни месяца*/
    ИЛИ(И(МЕСЯЦ($R$4)=2;ДЕНЬ(D13)>29);
    И(МЕСЯЦ($R$4)=4;ДЕНЬ(D13)>30);
    И(МЕСЯЦ($R$4)=9;ДЕНЬ(D13)>30);
    И(МЕСЯЦ($R$4)=9;ДЕНЬ(D13)>30);
    И(МЕСЯЦ($R$4)=11;ДЕНЬ(D13)>30)
    );»-«;
    ЕСЛИ(/*условие на введенные значения в ячейках. Условие проверяется на интервал*/
    ИЛИ(И(D10>=$AK$30;D10=$AL$30;D10=$AM$30;D10=$AK$31;D10=$AL$31;D10=$AM$31;D10=$AK$20;D10=$AK$21;D10=$AK$24;D10=$AL$24;D10=$AM$24;D10=$AK$25;D10=$AL$25;D10=$AM$25;D10<=$AM$25))
    );"вых";
    ЕСЛИ(ДЕНЬНЕД($R$4-2+D10)=1;"пн";/*условие на день недели*/
    ЕСЛИ(ДЕНЬНЕД($R$4-2+D10)=2;"вт";
    ЕСЛИ(ДЕНЬНЕД($R$4-2+D10)=3;"ср";
    ЕСЛИ(ДЕНЬНЕД($R$4-2+D10)=4;"чт";
    ЕСЛИ(ДЕНЬНЕД($R$4-2+D10)=5;"пт";"вых")
    )
    )
    )
    )
    )
    )
    )
    //*****************************//
    Буду Вам благодарен, если подскажите как граматно обойти ограничение условие ЕСЛИ(), только без переустановки Excel.

  22. Подскажите, пожалуйста, как правильно составить формулу в этом случае: есть два файла, данные одного
    2015 203 сумма 0
    2015 204 сумма 100
    2015 205 сумма 200
    данные второго
    2015 сумма 260
    Нужно из второго файла подтянуть к второму файлу при этом учесть большее по сумме условие, те чтобы 260 подтянулась к 200? При этом желательно чтобы разница между 260 и 200 относилась к сумме 100, а при ее отсутствии в пустую ячейку рядом с 260. Заранее спасибо.

  23. Здравствуйте. Как правильно составить формулу: оплата за обучение 31000. Если у студента одни «5»(оценка) по предметам то скидка 75%, если ни одной «3» — 20% скидка за обучение. как можно решить задачу?
    Напишите пожалуйста на почту j.keneshbekova.a@mail.ru
    ЗАРАНЕЕ СПАСИБО!

  24. Доброй ночи!
    У меня по сравнению с остальными совсем все просто, но разобраться у меня не получается…
    =ЕСЛИ(K3=»х;2″;»+»;»-«)
    Мне нужно, если в ячейке я ставлю значение х или 2 выдавало +, в противном случае -. Что я делаю не так?

  25. Здравствуйте! Помогите пожалуйста разобраться с формулой. Ситуация такова, что если число в ячейке H3 положительное, то расчет производится верно, но если число отрицательное, то расчет неверный. Как прописать условие для отрицательного числа?=ЕСЛИ(I54=»»;»»;(H54-I54-$H$3)*0,0001)

  26. Добрый день!
    Подскажите, пожалуйста, решение вопроса. Есть два переменных значения А (количество по длине), В (количество по высоте), при которых А и В изменяются в зависимости от размера, но при этом, если размер меньше, то должно быть одно условие, если больше то другое и таких условий кк минимум пять. какую формулу использовать и как это можно сделать. =ЕСЛИ(И(C4>=6;C5=6;C5=3);G4+15;G4) вот эта формула не работает 🙁 пишет #ЗНАЧ!

  27. Присвойте ячейкам A1, B1, C1,D1 имена q,r,s,t. В этих ячейках содержатся числа. Создайте формулы, возвращающие значение ИСТИНА когда:
    -только одно число является отрицательным
    -какое-либо из чисел равно 0
    -числа в ячейках q и r равны между собой.

  28. =ЕСЛИ(D5179=10;30;ЕСЛИ(D5179=15;42;ЕСЛИ(D5179=20;55;ЕСЛИ(D5179=22;60;ЕСЛИ(D5179=25;68;ЕСЛИ(D5179=30;80;ЕСЛИ(D5179=35;95;ЕСЛИ(D5179=40;110;ЕСЛИ(D5179=45;120;ЕСЛИ(D5179=50;135;ЕСЛИ(D5179=55;150;ЕСЛИ(D5179=60;165;ЕСЛИ(D5179=65;180;ЕСЛИ(D5179=70;190;ЕСЛИ(D5179=75;205;ЕСЛИ(D5179=80;220;ЕСЛИ(D5179=85;245;ЕСЛИ(D5179=90;260;ЕСЛИ(D5179=95;265;ЕСЛИ(D5179=100;275))) Подскажите, пожалуйста , как упростить эту формулу. очень много символов и не пропускает

  29. Добрый день! Помогите пожалуйста, нужно посчитать прибыль, с разницы цен покупки и продажи категорий товара, с условием, что объем проданного товара может превышать его остаток перед продажей на складе и в этом случае происходит высортировка недостающего количества товара из другой заведомо обозначаемой позиции. Пример: На складе было 10 кг (А1) яблок сорт «Ромашка» по цене 5 руб/кг (B1) на сумму 50 руб (С1) и 20 кг (А2) яблок сорт «Улыбка» по цене 7 руб/кг (В2) на сумму 140 руб (С2). Продали 15 кг. яблок улыбка (D1) по цене 10 руб/кг (I1), на сумму 150 руб. (F1). Как правильно составить формулу? Заранее спасибо!

  30. Подскажите, пожалуйста (я полный ноль в экселе).
    Нужно составить формулу, чтобы ставила оценки в зависимости от количества набранных баллов в тесте.
    20-29 баллов = 1
    30-49 = 2
    50-69 = 3
    70-79 = 4
    80-100 = 5
    Буду очень благодарен за помощь

  31. Замутили с ребятами турнир по прогнозам на матчи.
    Хотелось бы чтобы баллы автоматически считались, после того как ввели результаты матчей.

    Правила такие: 3 очка — угадан результат, 2 очка — угадана разница, 1 очко — угадан победитель, 0 очков — ничего не угадал

  32. Здравствуйте, извините меня, что задаю наверно такой глупый вопрос, но почему в условии число 500, а в формуле присутствует число 400, когда вроде как должно быть 500, объясните пожалуйста

  33. Здравствуйте, пытаюсь создать функцию например: если (или(и(условие1;условие2);(условие3;условие4);(условие5;условие6)… В общем оба условия в скобках должны выполняться одновременно и если хотя бы одни скобки (из 3-х в данном случае) выполняются, то ИСТИНА. никак не получается правильная формула, помогите пожалуйста

  34. Спасибо за отличное решение, в самый раз подошло.
    Немного модернизировал и обработка 20000 ячеек стала простой и понятной.
    =ЕСЛИ(L2=M2;1;ЕСЛИ(L2=N2;2;ЕСЛИ(L2=O2;3;ЕСЛИ(L2=P2;4;ЕСЛИ(L2=Q2;5;ЕСЛИ(L2=R2;6;ЕСЛИ(L2=S2;7;0))))))) — это последовательное сравнение 1й ячейки с 7ю в одной строке для поиска дублей, с выдачей подсказки по месту совпадения.
    Разложив на 7 столбцов с корректировкой количества ячеек получаем в формуле — получаем построчную проверку на дубли во всех ячейках для 8 столбцов.

  35. Помогите разобраться!!!
    На листе Images в колонке «Images» необходимо проставить картинки из списка на листе «Images Data».
    Обратите внимание на то, что имя картинки содержит Make и Model, а значит, что для одного и того же SKU но с разными Make, Model картинки будут разные.

  36. Два дня мучаюсь, пожалуйста помогите с формулой
    Если платеж совершен до 01.07.2018, то необходимо заплатить 200 руб., если платеж совершен после 01.07.2018, но до 01.10.2018, то необходимо заплатить 250 руб., если платеж совершен после 01.10.2018, то необходимо заплатить 300 руб.
    =ЕСЛИ(RC[-7]>»01.07.2018″;»200″) ;ЕСЛИ(RC[-7]<"01.07.2018";"250"); ЕСЛИ(RC[-7]<"01.10.2018";"300")
    Где я ошибся?

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

    Файл:  -1.xlsx

  38. Добрый день! А если нужно вывести сумму отвечающую двум условиям? Например из диапазона мне нужно выбрать суммы равные определенному коду и соответствующие определенному текстовому условию? Пробовала использовать «ЕСЛИ» и «И» не получилось 🙁 Подскажите пожалуйста формулу (файл прицепила)

    Файл:  .xlsx

Добавить комментарий

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