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

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

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

    Добрый день! Помогите, пжл, с формулой. Вс. голову сломала)))
    Условия следующие: если в ячейке стоит 1 или 2, то считает +х%, в строке таких ячеек 24. По итогу, если во всех ячейках не будет стоять «0», то формула выдает 100%, если хотя бы в одной ячейке стоит «0», то формула выдает 98% или 84% в зависимости от того, что это за ячейка(каждой ячейке соответствует определенный %)
    Заранее благодарю

  • Роман says:

    Подскажите, пожалуйста, нужно сделать проверку ячеек, если в столбце А в какой-либо ячейке есть определенное число, например 12, то нужно, проверить ячейку столбца В в соответствующей строке и если в ней есть буква Z, то написать 1. Если нет, то и не надо.

  • Ольга says:

    Здравствуйте! Подскажите пожалуйста, вроде все просто, а не получается формула. Если А1 меньше или равно 22, то А1. Если А1 больше 22, то А1-22. Я пишу =ЕСЛИ(А122;A1-22))) Огромнейшее спасибо заранее!!!

    • excel says:

      =ЕСЛИ(А1<=22;А1;A1-22) получается если A1 меньше или равно 22, то A1. Во всех остальных случаях, то есть иначе, то есть когда А1 больше 22 будет A1-22

  • Сания says:

    Здравствуйте, помогите пожалуйста в написании формулы: (Если значение в ячейке А1 от 1 до 4 (ячейки С1-D1) то значение в ячейке В2 должно умножиться на значение в ячейке E 1) и/или А1 от 5 до 10 (ячейки С2-D2) то значение в ячейке В2 должно умножиться на значение в ячейке E 2) и т.д.

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

×
Рекомендуем посмотреть