Функция ЕСЛИ в Excel с несколькими условиями. Примеры использования вложенных функций ЕСЛИ
Мы уже рассматривали пример с функцией ЕСЛИ, но часто требуется использовать несколько условий ЕСЛИ, то есть вкладывать несколько ЕСЛИ в одну формулу. В этом случае у многих возникают вопросы как это реализовать. Задача возникает очень часто и давно напрашивалась к написанию. На самом деле ничего сложного нет, в этом примере мы рассмотрим пример Функции ЕСЛИ с несколькими условиями подробно, чтобы у вас было четкое понимание как это использовать для других примеров.
Пример задачи с использование нескольких функцией ЕСЛИ: У нас есть отчет по продажам продавцов. План продаж считается по количеству проданных единиц товара и составляет от 18 до 20шт. Каждый сотрудник получает оплату 2000 рублей. За каждую дополнительно проданную единицу товара сверх плана сотрудник получает 25 рублей, а за невыполнение плана – штрафуется по 50 рублей за единицу (например, продал 16 шт, недобрав 2 шт до минимального плана 18 шт, следовательно он получает 2000 рублей минус 2*50 рублей то есть 1900 рублей). Нам необходимо рассчитать оплату для каждого сотрудника.
Для удобства вынесем все условия в отдельную таблицу, чтобы при необходимости мы могли быстро их менять если, например, поменяется оплата или план
Нам требуется заполнить столбец “Оплата”. У нас несколько условий, поэтому одним если обойтись не получится. Вот как мы будет рассматривать эти условия
- если продаж больше 20, то считаем оплату за перевыполнение, иначе проверяем
- если продаж меньше 18, то считаем оплату за невыполнение, иначе было от 18 до 20 продаж и сумма выплаты составляет 2000 рублей
Для удобство понимания, в первое время лучше использовать диалоговые окна. В дальнейшем вы сможете писать функцию сразу в строке формул. Для начала мы посчитаем оплату для первого сотрудника Алексей, но сразу учтем и пропишем формулу таким образом, чтобы можно было применить эту формулу и для других сотрудников, протянув ее вниз.
Итак, вставьте курсор в ячейку C3 и нажмите на значок выбора функций, перейдите в категорию “Логические” и выберите функцию ЕСЛИ и нажмите “ОК” (см. на рисунок)
Откроется диалоговое окно функции если.
Лог_выражение – это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 20 (то есть больше плана) или нет. Продажи Алексея у нас в ячейке B3 – кликните мышкой на B3 и это выражение появится в этом поле (можно просто прописать B3, но удобнее выбирать). Далее нам необходимо проверить эти продажи Алексея с планом. Напишем B3> и выберем ячейку с планом G2, У нас должно получиться выражение B3>G2
Для того, чтобы в дальнейшем применить данную формулу для других сотрудников, протянув ее вниз, нам необходимо закрепить ссылку в ячейке G2, чтобы она стало абсолютной (то есть не менялась, когда мы будет протягивать формулу). Для этого необходимо прописать знаки доллара перед буквой и цифрой или только перед цифрой, так как мы не будет протягивать формулу влево или вправо. Удобнее всего зафиксировать ячейку просто выделив в формуле нужную ячейку и нажав клавишу F4
Должно получиться вот так B3>$G$2 или так B3>G$2
Обратите внимание, 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
Переходим к следующему полю.
Значение_если_ложь. Итак, выше мы рассчитали оплату сотруднику, если план продаж выполнен (то есть если наше выражение в первом поле верно – возвращает истину). Если нет, то осталось два варианта: либо сотрудник точно выполнил план продаж (от 18 до 20шт), либо недовыполнил. Снова два условия, потребуется вложенная ЕСЛИ. Будет легче, если мы сначала проверим условие невыполнение плана, а оставшийся вариант будет выполнение плана.
В этом поле можно написать вложенную функцию ЕСЛИ вручную, но это не очень удобно, поэтому мы снова воспользуемся диалоговыми окнами. Перейдите в ячейку Значение_если_ложь откройте раскрывающийся список последних использованных функции в правом углу и выберите ЕСЛИ
После этого у вас снова появится новое диалоговое окно функции ЕСЛИ. Не надо паниковать предыдущая информация не стерлась, а просто свернулась. Вы можете это видеть в строке формул. Это сделано для удобство.
Заполняем поле лог_выражение, как вы помните мы проверяем условие о невыполнение плана сотрудником. То есть прописываем (выбираем) B3<F2 (нижняя граница выполнения плана). Снова закрепляем ссылку на F2 с помощью клавиши F4 и получаем выражение: B3<$F$2
Далее поле Значение_если_истина. То есть если действительно B3<F2 (то есть если Алексей не выполнил план), мы рассчитывает ему оплату как сотруднику, который не выполнил план. Она рассчитываться так: оплата 2000 рублей (F4) минус 50 рублей за каждую единицу невыполнения плана. Чтобы посчитать количество единиц, которое не хватило сотруднику, нужно отнять от нижний границы плана F2 итоговый результат по продажам B3 и умножить на 50 рублей (F6).
Получаем следующую формулу: $F$4-($F$2-B3)*$F$6
Отлично. Если план перевыполнен, то мы посчитали оплату, если не выполнен, мы проверяем сотрудника на невыполнения плана. Если он не выполнил план, то мы считаем ему оплату. Если же он и не перевыполнил план и не недовыполнил план, то значил от сделал ровно план от 18 до 20 шт.
Поэтому в следующем поле мы рассчитываем оплату сотруднику, когда он выполнил план, то есть он получает 2000 рублей (ячейка F4). Переходим в поле Значение_если_ложь и прописываем (выбираем) F4. Не забываем закрепить ссылку – $F$4
Вот так будет выглядеть вложенное ЕСЛИ
Нажимаем “Ок” – формула готова. Вот так выглядит полная формула:
=ЕСЛИ(B3>$G$2;$F$4+(B3-$G$2)*$F$5;ЕСЛИ(B3<$F$2;$F$4+($F$2-B3)*$F$6;$F$4))
С помощью диалоговых окон очень просто пользоваться функцией ЕСЛИ и при необходимости не запутаться с вложенными ЕСЛИ, главное изначально подумать о последовательности проверки ЕСЛИ.
Вложение
Приветствую. Тщетно пытаюсь победить следующую задачу.
Задача заключается в том, чтобы построить калькулятор автоматического расчёта суточной дозировки для ряда препаратов для животных. Дозировка у каждого препарата своя и зависит она напрямую от веса животного. Есть колонка где прописан возможный вес животного (от 0,5 кг. до 30 кг.), так же есть колонка где напротив каждой ячейки веса стоит своя дозировка. Создана ячейка с выпадающим списком в которой нужного выбрать вес, в соседней ячейке автоматически должна быть выведена соответствующая значению выбранного веса дозировка.
Возможно сделать так, чтобы с помощью выбора в выпадающем списке определённого товара в расчётах использовалась соответствующая ему таблица дозировок?
Пример на картинке. Подскажите пожалуйста на какие формулы обратить внимание.
За ответ буду очень признателен.
как написать формулу (ЕСЛИ, СЛУЧМЕЖДУ), если >15, случмежду 0;15
Вложение
Добрый день! В вашей формуле не хватает еще одного условия проверки: это B3 не равно 0, если протянуть формулу ниже таблицы в столбце С получается результат 1100 рублей.
По условиям там все правильно. Если план меньше 18, то берется 2000 рублей оклад и минус сколько не добрал до плана.
Например 8 продаж. Значит 2000+(8-18)*50=2000-500=1500 рублей и так далее
Вложение -Данные-2-Утренние-вечерние-исполнители.xlsx
Здравствуйте! Можете подсказать по задаче.
Проблема в том, что в задаче в двух таблицах указаны не все id курьеров из-за чего ВПР выдает результат N/A и дальше по задаче продвинуться никак не могу, либо там какая-то хитрость.
Заранее спасибо!
Вложение .xlsx
Добрый день! Подскажите пожалуйста! В ячейке обычная формула умножения, можно ли ее совместить с формулой “если” или с какой любой другой? Во вложении файл, задача в том что бы в ячейке “С3”, автоматически было значение “о”, если товар продает Петя, ну а если Саша продает, то формула считает его процент. Спасибо большое!
Добрый день! помогите прописать формулу, есть ячейка, в которой введена формула подсчета возраста гражданина (г.м.д.), как прописать формулу, чтоб по достижению определенного возраста, с учетом формата возраста г.м.д. подсвечивалась фамилия гражданина?
Смотри условное форматирование. =)
Здравствуйте! Подскажите, пожалуйста, как прописать в формуле ЕСЛИ: в ячейке находится любой текст или данные. То есть если в ячейке есть информация то присваивается истина, если там нет ничего, то ложь
=ЕСЛИ(A1=””;если_истина;если_ложь)
Только тут наоборот. Если пусто то истина, если нет, то ложь
ну или так
=ЕСЛИ(A1<>“”;если_истина;если_ложь)
Помогите, пожалуйста!
формула должна быть такой как ее правильно прописать?
65/34,4-1*100
Вопрос не понятен. Вы пишите, что формула должна быть такой. Потом пишите саму формулу, а потом спрашиваете как ее прописать.
Добрый день! Есть 3 бригадира, по которым ведется ежемесячная статистика по браку допущенному в их работе. В конце каждого месяца данные анализируются (сейчас вручную) и допущенный брак отминусовывает у них премиальную часть, которая без брака равна 10000 рублей.
Вопрос – как задать условие, которое бы считало следующим образом: от 0% до 5% брака – 100% премии, от 5% до 20% брака -80% премии, от 20% до 40% брака – 60% премии, свыше 40% брака – 0% премии.
Помогите пожалуйста. Пример в этой теме близок к моему решению, но я не могу никак домучить…
Вот тут как раз ваша ситуация рассмотрена: https://sirexcel.ru/priemi-excel/funkcii/alternativa-bolshomu-kolichestvu-vlozhennyx-esli-v-excel-s-primerom/
Как записать формулу, чтобы если в одной ячейке появляется любой текст, другой присваивалось значение “1”?
Функция СЧЁТЗ – считает количество заполненных ячеек.
То есть можно указать большой диапазон сразу и если в этом диапазоне появится заполненная ячейка. Кол-во в ячейке где СЧЁТЗ – увеличится
добрый день. Помогите написать формулу – которая будет находить студента(ов) с максимальным балом и писать премия напротив их фамилии. написала вот такую формулу но везде выдает прочерк =IF($A10=MAXA($G$10:$G$26); “Премия”;”-“)
Спасибо!
=IF ($A10>=MAXA ($G$10:$G$26); «Премия»; «-»)
Полагаю больше или равно Максимума там $A10>=MAXA =) В твоем случае т.к. значение не равно, то и выдает прочерк «-», как ложь.
Подскажите как записать функцию: Если в ячейке А1=2, то сравниваем значения B1 и С1 и в ячейку D1 заносим большее значение, а если А1=3, то сравниваем значения B2 и C2 и в ячейку D1 заносим большее значение и т.д.
Вложение
Добрый день.Помогите пожалуйста с формулой..Если возраст сотрудника младше 30 присваивается 1 категория, от 30 до 35 – 2 категория, старше 35 – 3 категория.
Посмотрите на комментарии ниже, точно такая же задача
Добрый день. Помогите пожалуйста с формулой
Начисление бонусов за выполнение начинается от 80% (меньше 80% = 0) и до 120% (свыше 120% не оплачивается)
Вот статья по вашей задаче https://sirexcel.ru/priemi-excel/funkcii/alternativa-bolshomu-kolichestvu-vlozhennyx-esli-v-excel-s-primerom/
ВПР там не обязательно
там условие меньше 80% не оплачивается
80%
90%
100%
110%
120% это максимум
просто свыше 120% не оплачивается и я не знаю как зафиксировать этот максимум
Не обязательно, но можно решить. Вы правы в этом случае лучше использовать ЕСЛИ.
Например так
=ЕСЛИ(И(A1>80%;A1<120%);"если_оплачивается";"если_не_оплачивается")не работает, а что если сделать несколько вложенных если? как эту формулу написать?
Здесь не нужно несколько вложенных если. Должно работать. Значит что-то не так делаете.
А1 – вам нужно заменить на ячейку, где идет проверка
“если_оплачивается” – вместо это нужно написать, что делать, если А1 одновременно удовлетворяет условию и больше 80% и меньше 120%.
“если_не_оплачивается” – тут надо прописать, что делать, во всех остальных случаях, то есть когда меньше 80% и больше 120%
все таки нужна формула со вложенными если, потому что с функцией и я не могу задать условие
выходит так что если меньше 80% то не оплачивается вообще, а если больше 120% то тоже не оплачивается вообще. Тогда как свыше 120% идет оплата как 120%
Если(A1<80%;0;Если(A1>120%;оплата120;оплата100))Вот, например можно так (вложение)
Если план продаж от 80 до 120% то выплачивается премия, если меньше или больше указанного диапазона, то 0
https://yadi.sk/i/WbvxK68a3EEmEw
вы не читаете что я пишу. свыше 120% оплачивается также как и за 120%
а вы пишете что свыше 120% = 0 это не так
вот вложение
Добрый день. Как задать условие выполнения плана продаж больше 80% и не больше 120%
К примеру план составляет 100 единиц продукции, а выполнение факт 300 единиц
Заранее спасибо.
Есть значения:
до 1800 = 0.5
от 1810 до 3600=1
от 3610 до 5400=1.5
от 5410 до 7300=2
Условие: ставишь значение 600 выдавать должно число 0.5, если ставишь значение 6000 выдавать должно число 2.
Получается только 1 формула и последняя, в промежутке пишет ЛОЖЬ. Умоляю, ПОМОГИТЕ
Добрый день! Эта задача рассматривается в этой статье https://sirexcel.ru/priemi-excel/funkcii/alternativa-bolshomu-kolichestvu-vlozhennyx-esli-v-excel-s-primerom/
Добрый день!
Помогите пожалуйста составить формулу, а то прям запуталась)
Суть такая: есть минимальный план 500, если производится до 700, нужно к разнице между 500 и 700 прибавить 10%, т.е. к примеру, если сделали 600, то 10% прибавить к 100. Но если меньше 500 или больше 700, должно стоять 0
=ЕСЛИ(И(A1<700;A1>500);(700-A1)*1.1;0)А вас не затруднит расписать принцип построения формулы (для особо “умных”) чтобы понять как самой составить?
Спасибо)
сначала в ЕСЛИ проверяется условие
И(A1<700;A1>500) оно должно возвращать истину или ложь
Функция И означает, что истина будет возвращаться только если оба условия выполняются. И меньше 700 и обязательно больше 500 одновременно. То есть если выполнение плана от 500 до 700
Если оба условия выполняются, то идет вычисление (700-A1)*1.1 – вычитаем из 700 выполнение плана и прибавляем 10% я это сделал умножив на 1.1. Это из пропорции. Оно вычисляется так:
(700-A1) – 100%
x – 110%
x=(700-A1)*110%/100%=(700-A1)*1.1
Ну а во всех остальных случаях, когда выполнение плана не входит в диапазон от 500 до 700 мы возвращаем 0
Спасибо большое!
Ещё один вопрос. А, если выполнение плана выходит за рамки 500-700, но не нужно ставить 0, а просто высчитать разницу, а перевыполнение игнорировать?
так если выходит за рамки 500-700 нужно просто посчитать разницу или игнорировать?
800 – выходить за рамки, перевыполнение – игнорировать или считать разницу.
Во-вторых тут все должно быть понятно, все расписано. Вместо 0 указываете нужное вам действие
Если меньше 500, ставить 0, в пределах 500-700, считать разницу, а перевыполнение-игнорировать
А напишу вам, мне не сложно. Но какой смысл? вы сейчас находитесь на странице где как раз все это расписано. Лучше один раз прочитать, вникнуть и использовать все время. А если что-то не понятно спрашивайте – отвечу, дополню.
Если под игнорировать вы имеете ввиду ничего не ставить в ячейке, то так
=ЕСЛИ(И(A1<700;A1>500);700-A1;ЕСЛИ(A1<500;0;""))Здравствуйте! Помогите пож-та с формулой для такой задачи. Если значение в определенной ячейке больше например 12, тогда разницу от 12 нужно умножить на 2. Спасибо!
А в чем сложность? у вас обычная функция ЕСЛИ (даже не вложенная): https://sirexcel.ru/priemi-excel/funkciya-esli-v-excel-s-primerami/
Вложение
В очередной раз прошу извинения. Но мои знания не позволяют мне сделать правильную формулу.
Есть столбец “O” с большим списком, необходим отбор по ИСТИНЕ, что бы столбец “N” это показывал.
Я попробовал, у меня получилась реализовать работоспособность формулы только в раздельном виде. На рисунке это верхняя формула “N 2 и 3”, и нижняя “N 4 и 5”.
Соответственно если я протяну формулу вниз от “N 2”, то подобные значения как в ячейках О4 и О5 будут показывать ЛОЖЬ.
Подскажите пожалуйста каким образом совместить обе формулы, что бы они работали и на значение >1 и <-1 одновременно?
Я не очень понимаю, что вам нужно сделать.
Правильно я понимаю, что вы хотите сделать проверку. Если O2 больше либо равно 1, то нужно возвратить ИСТИНУ, если Меньше 1, то нужно вернуть ЛОЖЬ? и эту формулу нужно протянуть. Так?
Здравствуйте! Мне нужно чтобы в тех ячейках в которых значение от 1 и более, а также от -1 и менее была ИСТИНА. В ячейках со значениями от 0,99 до -0.99 была ЛОЖЬ. Это первое основное. Если возможно также, в ячейках со значениями 100 и -100 тоже была ЛОЖЬ.
Вложение -Истина.xlsx
В конце статьи есть форма для поддержки проекта =)
В данном случае фильтр нужно будет обновлять постоянно. То есть если данные поменялись, нужно будет снова нажать на список и данные обновятся.
Можно сделать чтобы список с ИСТИНА формировался автоматически. Приложил файл с примером. в столбце K и L формируется список Истина, если данные в C будут меняться на Ложь или Истина, то и список автоматически будет перестраиваться
Вложение
=ЕСЛИ(ИЛИ(B1=100;B1=-100);"ЛОЖЬ";ЕСЛИ(ИЛИ(B1>=1;B1<=-1);"ИСТИНА";"ЛОЖЬ"))Суперски!!! Отлично работает. Спасибо)
Если это возможно, скажите номер тел. или Киви, я отблагодарю за Ваше добро.
Скажите ещё пожалуйста, если я поставлю фильтр на ИСТИНА, и у меня идёт потоковое обновление данных (некоторые ячейки меняют значения уже после установившихся, на другое). Можно ли как то при фильтре ИСТИНА сразу отсеивать появившуюся ЛОЖЬ, и также при появлении ещё ИСТИНА сразу пополняли фильтр по ИСТИНА? Или в Excel фильтр так и реализован?
Можно ли в значении ложь написать функцию впр, если ДА, то как? Спасибо!
Конечно можно, для простоты, напиши впр отдельно с соседней ячейке, а потом можете эту формулу просто вставить в месте если ЛОЖЬ
Так обнаружил ошибку в Вашей итоговой формуле: ЕСЛИ(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 внести без знака доллар…
О да, вы правы. Исправил. Спасибо за внимательность