函数界也搞裁员？我们恐将告别IF函数！

Excel函数 2023-01-13

问题1：如下图所示，当实际销售量大于销售量目标时，奖励1000元。

通常遇到这类问题，首先想到的一定是IF函数，公式为：=IF(C2>B2,1000,0)

大家都能理解这个公式，而且这个问题也相当简单，简单到甚至都不需要用函数就能解决：

在公式“=(C2>B2)*1000”中，利用了逻辑值直接参与计算，当C2>B2成立时，得到TRUE，反之得到FALSE。逻辑值在与数字计算时，TRUE等同于1，FALSE等同于0，因此公式“=(C2>B2)*1000”同样可以得到所需的结果。问题2：还是计算奖励的问题，这次对奖励规则做了调整，当实际销量大于目标销量时，每超过一个销量奖励50元，1000元封顶。这时候如果还用IF函数解决，公式就变成了“=IF(C2B2,0,IF((C2-B2)*50<1000,(C2-B2)*50,1000))”。

这个公式进行了两次判断，首先判断是否达到奖励标准，也就是C2

B2时，不发奖励；如果达到奖励标准，还要进一步判断奖励是否达到1000元，也就是(C2-B2)*50<1000，如果不到1000，按实际奖励计算，超过了仍按1000计算。在这个问题中，要用好IF已经需要一点功力才行了，公式明显比第一个问题复杂了很多，这时候，IF函数的新对手出现了，而且一下子就来了两个：=MIN(MAX((C2-B2)*50,0),1000)

MIN函数用于得到几个数字中最小的一个，MAX函数用于得到几个数字中最大的一个，这两个函数配合了一下，竟然把一个原本该是IF函数的活给轻松解决了。这个公式需要分成两部分来理解，首先MAX((C2-B2)*50,0)得到理论奖励和0中的较大者，如果不够奖励标准，(C2-B2)*50就是一个负数，较大者为0，反之就是超额销量*50；接下来再将MAX得到的结果和1000放在一起，通过MIN函数来得到较小者，如果奖励金额超过1000，则返回1000。这样就可以把一个比较复杂的IF公式变得简洁。问题3：按超额数量计算阶梯奖励，规则如图所示。

如果还想用IF来解决这个问题，可以自己试试，确实太长了。下面分享几个不用IF的公式供大家参考：公式1：=MIN(MAX(INT((C2-B2)/10+1)*300,),1000)

这就完全是一种数学思路了，按照阶梯奖励的规则，每一档相差300元，1000元封顶，所以先把超额数量除以10再加1，乘上300就是奖励金额：

但是会出现负数和超过1000的情况，再用问题2的思路，结合MAX和MIN就能得到最终结果。公式2：=MIN(MAX(CEILING(C2-B2+1,10)*30,),1000)

这个公式可以看作是公式1的改版，还是利用了奖励规则中的一些规律性，用CEILING(C2-B2+1,10)*30取代了INT((C2-B2)/10+1)*300。CEILING函数是将数字按照指定的倍数向上舍入，看看下图示例或许就明白了。

公式3：=LOOKUP(C2-B2,$F$2:$H$6)

公式3完全是利用了LOOKUP可以进行区间匹配的功能，需要说明的是，本例中使用了一个辅助区域，这对于初学者来说是非常有用的，注意辅助区域的首列一定要用下限值。如果不想用辅助区域，可以按f9键把公式里的区域变成数组就行了： =LOOKUP(C2-B2,{-999,0;0,300;10,600;20,900;30,1000})

如果奖励标准发生变化时，自己修改数组中的数据即可。结论：以上案例中，分别使用了逻辑值、MIN、MAX、INT、CEILING和LOOKUP等函数来取代IF，实际上能取代IF的函数还有一些，例如CHOOSE，TEXT等都可以，篇幅所限不再一一列举。

当问题的判断条件是基于数字的时候，IF往往不是唯一可以选择的途径，换个思路或许可以得到更多方法，但是IF函数的确也有自身的优势，对于一些非数字性的判断，就非它不可了。

上一篇：IF函数嵌套，看完这篇也简单下一篇：关于IF函数公式，多条件判断，你一定踩过的坑

函数界也搞裁员？我们恐将告别IF函数！

热门文章

最新文章

标签列表