Функция ЕСЛИ в Excel с несколькими условиями. Примеры использования вложенных функций ЕСЛИ

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

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

Пример задачи с использование нескольких функцией ЕСЛИ: У нас есть отчет по продажам продавцов. План продаж считается по количеству проданных единиц товара и составляет от 18 до 20шт. Каждый сотрудник получает оплату 2000 рублей. За каждую дополнительно проданную единицу товара сверх плана сотрудник получает 25 рублей, а за невыполнение плана — штрафуется по 50 рублей за единицу (например, продал 16 шт, недобрав 2 шт до минимального плана 18 шт, следовательно он получает 2000 рублей минус 2*50 рублей то есть 1900 рублей). Нам необходимо рассчитать оплату для каждого сотрудника.

Для удобства вынесем все условия в отдельную таблицу, чтобы при необходимости мы могли быстро их менять если,  например, поменяется оплата или план

funkciya-excel-primery-neskolkimi-usloviyami

Нам требуется заполнить столбец «Оплата». У нас несколько условий, поэтому одним если обойтись не получится. Вот как мы будет рассматривать эти условия

  • если продаж больше 20, то считаем оплату за перевыполнение, иначе проверяем
  • если продаж меньше 18, то считаем оплату за невыполнение,  иначе было от 18 до 20 продаж и сумма выплаты составляет 2000 рублей

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

Итак, вставьте курсор в ячейку C3 и нажмите на значок выбора функций, перейдите в категорию «Логические» и выберите функцию ЕСЛИ и нажмите «ОК» (см. на рисунок)

otkryvaem-dialogovoe-okno-funkcii-esli

Откроется диалоговое окно функции если.

Лог_выражение — это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 20 (то есть больше плана) или нет. Продажи Алексея у нас в ячейке B3 — кликните мышкой на B3 и это выражение появится в этом поле (можно просто прописать B3, но удобнее выбирать). Далее нам необходимо проверить эти продажи Алексея с планом. Напишем B3> и выберем ячейку с планом G2, У нас должно получиться выражение B3>G2

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

Должно получиться вот так B3>$G$2 или так B3>G$2

log-vyrazheniya-esli

Обратите внимание, Excel автоматически вычисляет выражение и отображает его справа. В нашем случае Excel вычислил, что выражение — ИСТИНА, то есть действительно Алексей сделал продаж в количестве 35 шт, что больше верхней границе плана 20 шт. Обратите внимание, что если бы у Алексея было бы недовыполнение плана, то формулу мы бы все равно писали точно так же — гипотетически рассуждая, как если бы у Алексея был бы выполнен план.

Поехали дальше, переходим ко второму аргументу функции.

Значение_если_истина. Переведите курс в это поле. Тут нам необходимо рассчитать оплату сотруднику, если он выполнил план. Стандартная оплата 2000 (F4) плюс так как мы рассматриваем ситуацию когда план перевыполнен, нужно прибавить переработку. Для этого вычтем из всего продаж план и получим количество продаж сверх нормы (B3-G2) и умножим их на оплату за перевыполнение 25 рублей (F5). В итоге получаем следующий расчет 2000+(35-20)*25 то есть F4+(B3-G2)*F5

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

Получаем итоговый расчет: $F$4+(B3-$G$2)*$F$5

esli-istina-v-funkcii-esli

Переходим к следующему полю.

Значение_если_ложь. Итак, выше мы рассчитали оплату сотруднику, если план продаж выполнен (то есть если наше выражение в первом поле верно — возвращает истину). Если нет, то осталось два варианта: либо сотрудник точно выполнил план продаж (от 18 до 20шт), либо недовыполнил. Снова два условия, потребуется вложенная ЕСЛИ. Будет легче, если мы сначала проверим условие невыполнение плана, а оставшийся вариант будет выполнение плана.

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

dobavlyaem-vlozhennoe-esli

После этого у вас снова появится новое диалоговое окно функции ЕСЛИ. Не надо паниковать предыдущая информация не стерлась, а просто свернулась. Вы можете это видеть в строке формул. Это сделано для удобство.

vstavka-vstroennoj-funkcii-esli

Заполняем поле лог_выражение, как вы помните мы проверяем условие о невыполнение плана сотрудником. То есть прописываем (выбираем) B3<F2 (нижняя граница выполнения плана). Снова закрепляем ссылку на F2 с помощью клавиши F4 и получаем выражение: B3<$F$2

proveryaem-varazhenie-vo-vlozhennom-esli

Далее поле Значение_если_истина. То есть если действительно B3<F2 (то есть если Алексей не выполнил план), мы рассчитывает ему оплату как сотруднику, который не выполнил план. Она рассчитываться так: оплата 2000 рублей (F4) минус 50 рублей за каждую единицу невыполнения плана. Чтобы посчитать количество единиц, которое не хватило сотруднику, нужно отнять от нижний границы плана F2 итоговый результат по продажам B3 и умножить на 50 рублей (F6).

Получаем следующую формулу: $F$4-($F$2-B3)*$F$6

vlozhennaya-funkciya-esli-esli-istina

Отлично. Если план перевыполнен, то мы посчитали оплату, если не выполнен, мы проверяем сотрудника на невыполнения плана. Если он не выполнил план, то мы считаем ему оплату. Если же он и не перевыполнил план и не недовыполнил план, то значил от сделал ровно план от 18 до 20 шт.

Поэтому в следующем поле мы рассчитываем оплату сотруднику, когда он выполнил план, то есть он получает 2000 рублей (ячейка F4). Переходим в поле Значение_если_ложь и прописываем (выбираем) F4. Не забываем закрепить ссылку - $F$4

Вот так будет выглядеть вложенное ЕСЛИ

vlozhennoe-esli-v-excel

Нажимаем «Ок» — формула готова. Вот так выглядит полная формула:

=ЕСЛИ(B3>$G$2;$F$4+(B3-$G$2)*$F$5;ЕСЛИ(B3<$F$2;$F$4+($F$2-B3)*$F$6;$F$4))

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

Поделиться:
10 Комментариев
  • Рустам says:

    вложение

    В очередной раз прошу извинения. Но мои знания не позволяют мне сделать правильную формулу.

    Есть столбец «O» с большим списком, необходим отбор по ИСТИНЕ, что бы столбец «N» это показывал.

    Я попробовал, у меня получилась реализовать работоспособность формулы только в раздельном виде. На рисунке это верхняя формула «N 2 и 3», и нижняя «N 4 и 5».

    Соответственно если я протяну формулу вниз от «N 2», то подобные значения как в ячейках О4 и О5 будут показывать ЛОЖЬ.

    Подскажите пожалуйста каким образом совместить обе формулы, что бы они работали и на значение >1 и <-1 одновременно?

    • excel says:

      Я не очень понимаю, что вам нужно сделать.

      Правильно я понимаю, что вы хотите сделать проверку. Если O2 больше либо равно 1, то нужно возвратить ИСТИНУ, если Меньше 1, то нужно вернуть ЛОЖЬ? и эту формулу нужно протянуть. Так?

      • Рустам says:

        Здравствуйте! Мне нужно чтобы в тех ячейках в которых значение от 1 и более, а также от -1 и менее была ИСТИНА. В ячейках со значениями от 0,99 до -0.99 была ЛОЖЬ. Это первое основное. Если возможно также, в ячейках со значениями 100 и -100 тоже была ЛОЖЬ.

        • excel says:

          вложение  -Истина.xlsx

          В конце статьи есть форма для поддержки проекта =)

          В данном случае фильтр нужно будет обновлять постоянно. То есть если данные поменялись, нужно будет снова нажать на список и данные обновятся.

          Можно сделать чтобы список с ИСТИНА формировался автоматически. Приложил файл с примером. в столбце K и L формируется список Истина, если данные в C будут меняться на Ложь или Истина, то и список автоматически будет перестраиваться

        • excel says:

          вложение

          =ЕСЛИ(ИЛИ(B1=100;B1=-100);"ЛОЖЬ";ЕСЛИ(ИЛИ(B1>=1;B1<=-1);"ИСТИНА";"ЛОЖЬ"))

          • Рустам says:

            Суперски!!! Отлично работает. Спасибо)

            Если это возможно, скажите номер тел. или Киви, я отблагодарю за Ваше добро.

            Скажите ещё пожалуйста, если я поставлю фильтр на ИСТИНА, и у меня идёт потоковое обновление данных (некоторые ячейки меняют значения уже после установившихся, на другое). Можно ли как то при фильтре ИСТИНА сразу отсеивать появившуюся ЛОЖЬ, и также при появлении ещё ИСТИНА сразу пополняли фильтр по ИСТИНА? Или в Excel фильтр так и реализован?

  • Колл says:

    Можно ли в значении ложь написать функцию впр, если ДА, то как? Спасибо!

    • excel says:

      Конечно можно, для простоты, напиши впр отдельно с соседней ячейке, а потом можете эту формулу просто вставить в месте если ЛОЖЬ

  • Андрей says:

    Так обнаружил ошибку в Вашей итоговой формуле: ЕСЛИ(B3>$G$2;$F$4+(B3-$G$2)*$F$5;ЕСЛИ(B3$G$2;$F$4+(B3-$G$2)*$F$5;ЕСЛИ(B3<$F$2;$F$4+($F$2-B3)*$F$6;$F$4)). То есть последнюю запись B3 внести без знака доллар...

    • excel says:

      О да, вы правы. Исправил. Спасибо за внимательность

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

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