![如果 AND OR 嵌套函数有多个答案](https://linux22.com/image/1465684/%E5%A6%82%E6%9E%9C%20AND%20OR%20%E5%B5%8C%E5%A5%97%E5%87%BD%E6%95%B0%E6%9C%89%E5%A4%9A%E4%B8%AA%E7%AD%94%E6%A1%88.png)
我在使用相当复杂的嵌套 IF/AND/OR 公式来管理存在支付门槛和多个支付上限的销售激励时遇到了问题。
规则如下:
- 销售代表每售出一件产品可赚取 1 美元如果他的“比率”超过30%。
- 如果利率在 30-35% 之间,则赔付上限为 250 美元
- 如果利率在 35-40% 之间,则赔付上限为 350 美元
- 如果利率在 40-45% 之间,则支付上限为 500 美元,
- 如果利率超过 45%,则赔付上限为 750 美元
图片显示了 Excel 表的基本示例,以便您更清楚地了解我正在尝试做什么。我就是无法弄清楚用黄色填充 E2:E5 列的公式。
任何帮助都会非常有用。我尝试将公式分成几个额外的列,但就是无法得到正确的最终结果。非常感谢。
答案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 中并向下填充。INDEX
MATCH
=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
。它返回两个值中较小的一个,正如上限规则所规定的一样。
答案3
与 Excel 中的大多数内容一样,解决问题的方法不止一种。您专门询问如何使用 IF 逻辑来解决问题,jcbermu 的答案就是这样的。另一种方法是使用查找表来执行此操作:
它为您提供了解决此类问题的更简单的公式。E2 中的公式(您可以根据需要将其复制到该列中)是:
=MIN(B2,VLOOKUP(C2,G$2:H$6,2))
您无需定义每个范围的最小值和最大值,只需查看利率与每个范围的最小值的比较情况。低于最低合格 30% 的利率上限为 0 美元。
VLOOKUP 查找表中不超过 C 列值的最大利率并返回相关上限。MIN 函数返回原始支出计算值(因为该值乘以1
B 列值,所以我只使用 B 列值)或上限中的较小者。