用上这个Excel公式,再也不用加班算提成了

个人微信号 | (ID:LiRuiExcel520)

微信服务号 | 跟李锐学Excel(ID:LiRuiExcel)

微信公众号 | Excel函数与公式(ID:ExcelLiRui)

经学员反馈,由于单位的销售提成采用了分区间对应不同提成率的算法,所以每个月初都要为上千业务员的提成加班,他虽然学过一些Excel函数但一点也用不上,到底该怎么办?

本教程教你借助Excel分分钟搞定千人提成的复杂计算。而且提供多种解法供你选择,让你再也不用为提成计算发愁了。

在讲具体算法之前,我们先来看一下提成计算的规则要求。

从上图可见提成计算规则,以及举例拆分算法。当然表中只给出16名业务员是为了举例,不可能把上千人罗列完整。

请大家先自己独立思考两分钟,再看下面的解析和算法答案,印象会更深刻,具体算法下面会分别按步骤介绍。

本教程内容担心记不全的话,可以分享到朋友圈给自己备份一份。

算法一

这个问题无非就是面对不同多条件判断采取不同算法,绝大多数人第一直觉都是用IF函数,没问题,可以解决,虽然不是最优算法,但是这是最简单的。

用IF函数从大到小按照每个区间依次计算,Excel公式如下

=IF(B2>50000,(B2-50000)*5%+30000*3%+15000*2%+5000*1%,IF(B2>20000,(B2-20000)*3%+15000*2%+5000*1%,IF(B2>5000,(B2-5000)*2%+5000*1%,B2*1%)))

场景效果如下图所示。

这种公式写法虽然简单,但是不容易扩展,当计算规则的区间很多时,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})

不同解法都可以解决问题,喜欢哪种用哪种即可。这些公式都不难,很多同学反馈学了很久却写不出,是因为火候还不到,这需要一个过程是很正常的,慢慢积累+思考总结,总会达到的。

希望这篇文章能帮到你!怕记不住可以发到朋友圈自己标记。

>>推荐阅读 <<

(点击蓝字可直接跳转)

史上最全VLOOKUP函数套路大全

Excel万能函数SUMPRODUCT

IF函数强大却不为人知的实战应用技术

SUM函数到底有多强大,你真的不知道!

史上最全条件求和函数SUMIF教程

最具价值日期函数DATEDIF套路大全

Excel高手必备函数INDIRECT的神应用

飞檐走壁的函数里数她轻功最好!她就是...

COUNTIF,堪比统计函数中的VLOOKUP,你会用吗?

这个函数堪称统计之王,会用的都是高手!

(0)

相关推荐