Альтернатива большому количеству однотипных вложенных ЕСЛИ в Excel с примером
По данному вопросу также приходит очень много вопросов. Вот пример задачи, где на первый взгляд нужно использовать функцию ЕСЛИ в Excel, причем не один раз.
За перевыполнение плана от 0 до 5 штук (включительно), работнику выплачивается премия 2%
от 6 до 10 шт. – 4%
от 11 до 15 шт. – 6%
от 16 до 20 шт. – 8%
и так далее
от 95 до 100 шт. – 40%У нас есть данные по выполнению планов работников и нам необходимо рассчитать премию.
Если бы диапазонов расчета премии было бы несколько, то мы могли бы использовать функцию ЕСЛИ, то есть указываем, если перевыполнение больше 0 и меньше или равно 5, то поставить премию 2%, если нет, то снова открываем ЕСЛИ и проверяем если перевыполнение больше 5 и меньше или равно 10, то поставить премию 4%, если нет, то снова открываем ЕСЛИ и так далее.
В нашем примере 20 условий и сделать 20 вложенных ЕСЛИ можно, но не рационально. Предлагаю вам решение данной задачи с использованием функции ВПР с интервальным просмотром равным 1 (приблизительный поиск).
Итак, давайте начнем рассматривать решение данной задачи. Сначала создадим таблицу с условиями по выплате премии. Причем значения ОТ и ДО должны быть в разных столбцах и указываться в качестве цифр.
Далее прописываем функцию ВПР, которая будет искать нужный диапазон и возвращать соответствующую ему премию.
Пишем:
=ВПР(H3;$A$2:$C$22;3;1), где
H3 – это искомое значение, в нашем случае это выполнение плана определенным сотрудником, для Иванова – это 13 шт.
$A$2:$C$22 – это таблица с условиями по премии. Важно, чтобы первым столбцом данной таблицы были значения ОТ
3 – это третий столбец таблицы с условиями, в котором находятся % премии, который выплачивается сотруднику
1 – это интервальный просмотр, обычно всегда используется цифра 0, для поиска точного значения. Мы же указываем 1, так как нам нужно найти не точное, а ближайшее значение. Допустим, если перевыполнение плана составляет 8 шт., то при интервальном просмотре равным 1, функция будет искать ближайшее число меньше или равное 8, в нашем случае это будет 6, которому соответствует 4% премии
Далее протягиваем формулу на всех сотрудников и меняем формат премии на проценты
Это очень простой и эффективный способ в подобных задачах, которые встречаются очень часто. Многие сразу задумываются о вложенных ЕСЛИ, но как вы видите есть более простой, хоть и не очень очевидный способ с использованием функции ВПР.
Скачать пример файла – https://yadi.sk/i/Qnum6_IXdezhV
Надеюсь, это статья поможет вам решить задачу с большим количеством вложенных ЕСЛИ в Excel.
Буду очень благодарен за лайки и репосты в социальных сетях.
Добрый день! Помогите, пжл, с формулой. Вс. голову сломала)))
Условия следующие: если в ячейке стоит 1 или 2, то считает +х%, в строке таких ячеек 24. По итогу, если во всех ячейках не будет стоять “0”, то формула выдает 100%, если хотя бы в одной ячейке стоит “0”, то формула выдает 98% или 84% в зависимости от того, что это за ячейка(каждой ячейке соответствует определенный %)
Заранее благодарю
Подскажите, пожалуйста, нужно сделать проверку ячеек, если в столбце А в какой-либо ячейке есть определенное число, например 12, то нужно, проверить ячейку столбца В в соответствующей строке и если в ней есть буква Z, то написать 1. Если нет, то и не надо.
Здравствуйте! Подскажите пожалуйста, вроде все просто, а не получается формула. Если А1 меньше или равно 22, то А1. Если А1 больше 22, то А1-22. Я пишу =ЕСЛИ(А122;A1-22))) Огромнейшее спасибо заранее!!!
=ЕСЛИ(А1<=22;А1;A1-22) получается если A1 меньше или равно 22, то A1. Во всех остальных случаях, то есть иначе, то есть когда А1 больше 22 будет A1-22
Здравствуйте, помогите пожалуйста в написании формулы: (Если значение в ячейке А1 от 1 до 4 (ячейки С1-D1) то значение в ячейке В2 должно умножиться на значение в ячейке E 1) и/или А1 от 5 до 10 (ячейки С2-D2) то значение в ячейке В2 должно умножиться на значение в ячейке E 2) и т.д.
Добрый день! Помогите пожалуйста! Есть две таблицы, необходимо чтобы при вводе в одну таблицу буквы К, в другой таблице в определенной ячейке появлялся раскрывающийся список из двух значений: 3,5 и 7. При этом в первой таблице вводятся и другие значения кроме К, например 8, А, Б и т.д. необходимо чтобы при других значениях во второй таблице была просто пустая ячейка. Спасибо)
Так наверное только макросами. Можно вводить к1 и к2 в зависимости от того что нужно 3,5 или 7. Либо опишите суть задачи (на живом примере что хотите сделать) – может предложим другое решение
Вложение .xlsx
Помогите, пожалуйста. Нужно написать формулу. У меня не получается.
Условие если ячейка А1 на листе 1 равна ячейке А1 на другом листе, то ячейка В2 на листе 1 принимает значение равное ячейке В2 на другом листе
=ЕСЛИ(A1=Лист2!A1;Лист2!B2;””)
если не равно, то будет пусто “”, можно поменять на что нужно.
добрый день, не могу вбить формулой значение если есть сумма 8811 р. и известный процент 2,513 %, необходимо что бы итоговая сумма выходила но в что бы не получалась сумма процента (8811*2,513/100) так уже делала.Спасибо за помощь
Вам надо формат ячейки поменять с процентов на обычный числовой. А так вы все правильно считаете
Огромное спасибо за помощь! Для “чайников” еще хотелось бы добавить примечание, что значения в таблице по премии должны быть отсортированы от меньшего к большему, иначе формула не сработает.
здравствуйте!
подскажите на примере вашей же таблицы..
что если перевыполнение плана будет, скажем, равняться 101?
как поставить максимальное значение в колонке “ДО” равное не 100, а бесконечности?
спасибо!
Поставьте заведомо большое число, которое у вас не может быть, например 100000
Добрый день и спасибо за статью, мне было интересно.
Но, с учетом “равномерности” условий начисления премии, я пошел другим путем, математическим. В ячейке E2 поместил …
=ОКРУГЛВВЕРХ(((C2-B2)/5);0)*2
… и протянул. Ну и, конечно, при нулевом перевыполнении – нулевая премия.
С уважением,
Borg
А как ЕСЛИ одновременно указать “больше/меньше или равно” или “равно” это по отдельности делается? Больше/меньше одновременно делал, но еще “равно” к ним не добавлял никогда.
«больше/меньше или равно» – это все варианты, поэтому если тут не надо
Спасибо огромное! Сделала формулу с 15-ю ЕСЛИ, и решила все-таки поискать что-либо более рациональное и сразу нашла у Вас, все быстро получилось!! Мое Вам уважение и благодарность!
Пожалуйста!
Вложение .xls
=ЕСЛИ(G2=12;”Среднее значение”;ЕСЛИ(И(G2>12;G215;G219;”Яркий экстраверт”;ЕСЛИ(И(G29;”Склонность к интроверсии”;ЕСЛИ(И(G25;”Интроверт”;ЕСЛИ(G2<5;"Глубокий интроверт")))))))))))
В чем ошибка в этой формуле?
Смысл такой, что при ответе на вопросы теста, людям выдавался результат!Подскажите может как то по другому сделать?
В итоговой формуле не разбирался, но вот тут
ЕСЛИ(И(G2>12;G215;G219
У вас используется функция И – тут должно быть перечислены условия, которые возвращают ложь или истину. Первое G2>12 у вас есть условие, а вот второе G215 – это не условие.
Добрый день,
помогите с выбором функции. Нужно задать условие и из предложенного перечня выбрать заданный параметр, чтобы по этому параметру, перемножить другие значения, значения расположены между 2мя разными таблицами. Спасибо.
Тут основная должна быть функция ЕСЛИ https://sirexcel.ru/priemi-excel/funkciya-esli-v-excel-s-primerami/
удобно да но мне например по условиям задания надо именно команда ЕСЛИ с 5-7 условиями…ни кто не подскажет?
Вложение -рабочего-времени.xlsx
Второй файл
Вложение -.xls
Прошу Вас помочь с написанием формулы, сам осилить не могу, слишком много условий.
Спасибо.
+79129462724