用上这个Excel公式,再也不用加班算提成了
个人微信号 | (ID:LiRuiExcel520)
微信服务号 | 跟李锐学Excel(ID:LiRuiExcel)
微信公众号 | Excel函数与公式(ID:ExcelLiRui)
经学员反馈,由于单位的销售提成采用了分区间对应不同提成率的算法,所以每个月初都要为上千业务员的提成加班,他虽然学过一些Excel函数但一点也用不上,到底该怎么办?
本教程教你借助Excel分分钟搞定千人提成的复杂计算。而且提供多种解法供你选择,让你再也不用为提成计算发愁了。
在讲具体算法之前,我们先来看一下提成计算的规则要求。
从上图可见提成计算规则,以及举例拆分算法。当然表中只给出16名业务员是为了举例,不可能把上千人罗列完整。
请大家先自己独立思考两分钟,再看下面的解析和算法答案,印象会更深刻,具体算法下面会分别按步骤介绍。
本教程内容担心记不全的话,可以分享到朋友圈给自己备份一份。
算法一
这个问题无非就是面对不同多条件判断采取不同算法,绝大多数人第一直觉都是用IF函数,没问题,可以解决,虽然不是最优算法,但是这是最简单的。
用IF函数从大到小按照每个区间依次计算,Excel公式如下
场景效果如下图所示。
这种公式写法虽然简单,但是不容易扩展,当计算规则的区间很多时,IF的嵌套层级也随之增加,公式会越来越长。
所以给出算法二,改善这种不足。
算法二
算法二的思路是借助LOOKUP函数实现IF多层级判断,前提是计算好临界点和速算扣除数。
临界点很好确定,直接看下图,速算扣除数是为了简化公式计算而设定的,计算方法公式如下
=(G3-G2)*H3+I2
做好以后,效果如下图所示。
做好数据准备以后,剩下的就很简单了,用LOOKUP函数最基础用法即可搞定。
=LOOKUP(B2,{0;5000;20000;50000},B2*{1;2;3;5}%-{0;50;250;1250})
来上一张场景示意图,帮助大家更好理解,黄色区域都是公式自动生成的。
这个公式已经大幅简化IF公式了,但还可以进一步简化,继续看算法三。
算法三
算法三是观察LOOKUP函数计算原理之后,发现提取结果应该是数组各元素中的最大值,所以采用MAX函数进一步简化LOOKUP公式。
公式如下所示
=MAX(B2*{1;2;3;5}%-{0;50;250;1250})
不同解法都可以解决问题,喜欢哪种用哪种即可。这些公式都不难,很多同学反馈学了很久却写不出,是因为火候还不到,这需要一个过程是很正常的,慢慢积累+思考总结,总会达到的。
希望这篇文章能帮到你!怕记不住可以发到朋友圈自己标记。
>>推荐阅读 <<
(点击蓝字可直接跳转)