Если И ИЛИ вложенная функция с несколькими ответами

Question

У меня возникли проблемы с довольно сложной вложенной формулой IF/AND/OR для управления стимулом продаж, когда есть предел выплат и несколько ограничений на выплату.

Правила таковы:

• Salesrep зарабатывает 1 доллар за каждый проданный продукт, если его / ее ставка превышает 30%.
• Выплата ограничена 250 $, если ставка находится между 30-35%
• Выплата ограничена 350 $, если ставка находится между 35-40%
• Выплата ограничена в размере 500 $, если ставка находится в пределах 40-45%,
• Выплата ограничена 750 $, если ставка превышает 45%

На рисунке показан базовый пример листа Excel, чтобы дать вам более четкое представление о том, что я пытаюсь сделать. Я просто не могу понять формулу, чтобы заполнить столбцы E2:E5 желтым.

Любая помощь будет более чем полезной. Я попытался разделить формулы на несколько дополнительных столбцов, но просто не могу получить правильный конечный результат. Большое спасибо.

Answer 1

Вы должны поместить это в ячейку E2:

=IF(C2>0.3,IF(C2<=0.35,250,IF(C2<=0.4,350,IF(C2<=0.45,500,IF(C2>0.45,750,0)))),0)

Объяснение:

If C2>0.3 then
    If C2<=0.35 then
          250
    Else
          If C2<=0.4 then
               350
          Else
               If C2 <=0.45 then
                     500
               Else
                     If C2>0.45 then
                          750
                     Else
                           0
                     End If
               End If
          End If
    End If
Else
    0
End If

В ячейку F2 поместите: =IF(D2>E2,E2,D2) и это конечный результат.

Answer 2

Вы можете сделать это без большого количества сложного вложения, используя MIN , INDEX и MATCH . Поместите следующее в E2 и заполните.

=MIN(D2,INDEX({0,250,350,500,750},MATCH(C2,{0,0.3,0.35,0.4,0.45},1)))

Как это работает:

MATCH(C2,{0,0.3,0.35,0.4,0.45},1) сравнивает процент в C2 с массивом нижних границ для диапазонов пределов выплат. Функция MATCH вернет, где в массиве C2 падает. Например, если C2 равен 10%, функция обнаружит, что 10% находится между 0% и 30%, поэтому она вернет 1 потому что она попадает в первый диапазон. Если бы C2 был 33%, он вернул бы 2 потому что 33% попадают во второй диапазон, между 30% и 35%.

Значение, возвращаемое функцией MATCH затем используется функцией INDEX для возврата значения из той же позиции в массиве ограничений выплат. Так, например, если MATCH возвращает 1 , то INDEX вернет первое число из массива, 0 . Если MATCH возвращает 4 , INDEX вернет четвертое число из массива, 500 . Вместе MATCH и INDEX работают как справочная таблица.

Последний шаг - это функция MIN , которая сравнивает значение без ограничения в D2 с ограничением, возвращаемым функцией INDEX . Он возвращает меньшее из двух значений, как диктует правило ограничения.

Answer 3

Как и большинство вещей в Excel, есть несколько способов решения проблемы. Вы спрашивали конкретно о решении с помощью логики IF, и ответ jcbermu делает это. Другой метод - сделать это с помощью таблицы поиска:

Это дает вам более простую формулу для такого рода проблемы. Формула в E2, которую можно при необходимости скопировать в столбец:

=MIN(B2,VLOOKUP(C2,G$2:H$6,2))

Вместо определения минимума и максимума каждого диапазона, вы увидите, как скорость сравнивается с минимумом каждого диапазона. Ставки ниже минимальных квалификационных 30% имеют ограничение в 0 долларов США.

VLOOKUP находит наибольшую скорость в таблице, которая не превышает значение в столбце C, и возвращает соответствующий предел. Функция MIN возвращает меньшее из необработанного расчета выплат (так как это в 1 раз больше значения столбца B, я просто использую значение столбца B), либо ограничение.