☰

如果 AND OR 嵌套函数有多个答案

tag-icon • tag-icon worksheet-function microsoft-excel-2013
如果 AND OR 嵌套函数有多个答案

我在使用相当复杂的嵌套 IF/AND/OR 公式来管理存在支付门槛和多个支付上限的销售激励时遇到了问题。

规则如下：

  • 销售代表每售出一件产品可赚取 1 美元如果他的“比率”超过30%。
  • 如果利率在 30-35% 之间，则赔付上限为 250 美元
  • 如果利率在 35-40% 之间，则赔付上限为 350 美元
  • 如果利率在 40-45% 之间，则支付上限为 500 美元，
  • 如果利率超过 45%，则赔付上限为 750 美元

图片显示了 Excel 表的基本示例，以便您更清楚地了解我正在尝试做什么。我就是无法弄清楚用黄色填充 E2:E5 列的公式。

Excel 图像

任何帮助都会非常有用。我尝试将公式分成几个额外的列，但就是无法得到正确的最终结果。非常感谢。

答案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)这就是最终结果。

答案2

您可以使用、和来完成此操作MIN，而无需进行大量复杂的嵌套。将以下内容放在 E2 中并向下填充。INDEXMATCH

=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结合起来，就像一个查找表。MATCHINDEX

最后一步是MIN函数，它将未设上限的值D2与函数返回的上限值进行比较INDEX。它返回两个值中较小的一个，正如上限规则所规定的一样。

答案3

与 Excel 中的大多数内容一样，解决问题的方法不止一种。您专门询问如何使用 IF 逻辑来解决问题，jcbermu 的答案就是这样的。另一种方法是使用查找表来执行此操作：

![![在此处输入图片描述

它为您提供了解决此类问题的更简单的公式。E2 中的公式（您可以根据需要将其复制到该列中）是：

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

您无需定义每个范围的最小值和最大值，只需查看利率与每个范围的最小值的比较情况。低于最低合格 30% 的利率上限为 0 美元。

VLOOKUP 查找表中不超过 C 列值的最大利率并返回相关上限。MIN 函数返回原始支出计算值（因为该值乘以1B 列值，所以我只使用 B 列值）或上限中的较小者。

相关内容