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

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

Поделиться:
Отправить
21 Комментарий
  • Мария says:

    Добрый день! Помогите пожалуйста! Есть две таблицы, необходимо чтобы при вводе в одну таблицу буквы К, в другой таблице в определенной ячейке появлялся раскрывающийся список из двух значений: 3,5 и 7. При этом в первой таблице вводятся и другие значения кроме К, например 8, А, Б и т.д. необходимо чтобы при других значениях во второй таблице была просто пустая ячейка. Спасибо)

    • excel says:

      Так наверное только макросами. Можно вводить к1 и к2 в зависимости от того что нужно 3,5 или 7. Либо опишите суть задачи (на живом примере что хотите сделать) — может предложим другое решение

  • Елена says:

    Вложение  .xlsx

    Помогите, пожалуйста. Нужно написать формулу. У меня не получается.

    Условие если ячейка А1 на листе 1 равна ячейке А1 на другом листе, то ячейка В2 на листе 1 принимает значение равное ячейке В2 на другом листе

    • excel says:

      =ЕСЛИ(A1=Лист2!A1;Лист2!B2; "")

      если не равно, то будет пусто "", можно поменять на что нужно.

  • Sonik says:

    добрый день, не могу вбить формулой значение если есть сумма 8811 р. и известный процент 2,513 %, необходимо что бы итоговая сумма выходила но в что бы не получалась сумма процента (8811*2,513/100) так уже делала.Спасибо за помощь

    • excel says:

      Вам надо формат ячейки поменять с процентов на обычный числовой. А так вы все правильно считаете

  • Dragonfly-NT says:

    Огромное спасибо за помощь! Для «чайников» еще хотелось бы добавить примечание, что значения в таблице по премии должны быть отсортированы от меньшего к большему, иначе формула не сработает.

  • Константин 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 не будет опубликован. Все поля обязательны для заполнения.