Несколько условий ЕСЛИ в 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. Это важное замечание, поэтому будьте аккуратны при составлении логики формулы.

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


58 комментариев

  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 очков — ничего не угадал

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

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