Альтернатива большому количеству однотипных вложенных ЕСЛИ в 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.

Буду очень благодарен за лайки и репосты в социальных сетях.

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

    здравствуйте!

    подскажите на примере вашей же таблицы...

    что если перевыполнение плана будет, скажем, равняться 101?

    как поставить максимальное значение в колонке «ДО» равное не 100, а бесконечности?

    спасибо!

    • excel says:

      Поставьте заведомо большое число, которое у вас не может быть, например 100000

  • Borg says:

    Добрый день и спасибо за статью, мне было интересно.

    Но, с учетом «равномерности» условий начисления премии, я пошел другим путем, математическим. В ячейке E2 поместил ...

    =ОКРУГЛВВЕРХ(((C2-B2)/5);0)*2

    ... и протянул. Ну и, конечно, при нулевом перевыполнении — нулевая премия.

    С уважением,

    Borg

  • Алик says:

    А как ЕСЛИ одновременно указать «больше/меньше или равно» или «равно» это по отдельности делается? Больше/меньше одновременно делал, но еще «равно» к ним не добавлял никогда.

    • excel says:

      «больше/меньше или равно» — это все варианты, поэтому если тут не надо

  • Ирина says:

    Спасибо огромное! Сделала формулу с 15-ю ЕСЛИ, и решила все-таки поискать что-либо более рациональное и сразу нашла у Вас, все быстро получилось!! Мое Вам уважение и благодарность!

  • Ксю says:

    вложение  .xls

    =ЕСЛИ(G2=12; «Среднее значение»;ЕСЛИ(И(G2>12;G215;G219; «Яркий экстраверт»;ЕСЛИ(И(G29; «Склонность к интроверсии»;ЕСЛИ(И(G25; «Интроверт»;ЕСЛИ(G2<5;"Глубокий интроверт")))))))))))

    В чем ошибка в этой формуле?

    Смысл такой, что при ответе на вопросы теста, людям выдавался результат! Подскажите может как то по другому сделать?

    • excel says:

      В итоговой формуле не разбирался, но вот тут

      ЕСЛИ(И(G2>12;G215;G219

      У вас используется функция И — тут должно быть перечислены условия, которые возвращают ложь или истину. Первое G2>12 у вас есть условие, а вот второе G215 — это не условие.

  • Катерина says:

    Добрый день,

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

  • роман says:

    удобно да но мне например по условиям задания надо именно команда ЕСЛИ с 5-7 условиями...ни кто не подскажет?

  • Владимир says:

    Второй файл

  • Владимир says:

    вложение  -.xls

    Прошу Вас помочь с написанием формулы, сам осилить не могу, слишком много условий.

    Спасибо.

    +79129462724

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

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