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


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

Правила:

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

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

Excel image

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


2
2017-09-11 14:09


Источник


Почему это получает downvotes? Вопрос ясен, был приведен пример, и ожидаемый результат достаточно ясен. Пожалуйста, будьте добры, укажите причину, по которой этот вопрос бесполезен, если вы проголосуете таким образом. - Ramhound


Ответы:


Вы должны положить это на ячейку 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) и это окончательный результат.


2
2017-09-11 14:33



=IF(D2>E2,E2,D2) может быть выражено более кратко =MIN(D2,E2), - Scott
Это то, что я пытался сделать, и больше соответствует моей собственной логике (и компетенции), чем другие 2 ответа. Моя проблема заключалась в том, что я пытался усложнить работу с AND / OR. - james


Вы можете сделать это без большого сложного вложения, используя 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 функция. Он возвращает меньшее из двух значений, точно так же, как правило ограничения.


2
2017-09-11 15:15



Вы отправили это, пока я работал над своим ответом, и я это заметил. Его нужно вводить как формулу массива? - fixer1234
Да, я вижу, что у нас была такая же идея, но разные подходы к поиску. Это фактически работает без ввода массива. MATCH а также INDEX будет принимать массивы в качестве аргументов без специальной записи. - Excellll
Спасибо Excellll. Работает отлично. Никогда раньше не использовали функции соответствия или индекса. Мне нужно потратить еще немного времени, чтобы понять, как использовать их лучше, поскольку я часто создаю тупые правила стимулирования для своих торговых представителей, и они могут пригодиться снова. - james


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

![![enter image description here

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

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

Вместо определения min и max каждого диапазона вы увидите, как скорость сравнивается с минимумом каждого диапазона. Ставки ниже минимальной квалификации 30% имеют кол-во $ 0.

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


2
2017-09-11 15:26



Ничего себе, так просто .. никогда бы не подумал об использовании vlookup в одном листе. Фактический расчет, который я должен сделать, более сложный, но ваша формула работает. Бесконечно благодарен.. - james