问题情景
累进销售提成:
按照规定的销售额分级区间,以相应的提成率计算各区间中的提成额,最后进行汇总excel。
比如销售额为4750,则累进提成计算式为:=1000*1% 1000*1.2% 1000*1.4% 1000*1.6% 750*1.8%=65.5。
举例数据如下:
此示例的问题,只用IF的多重嵌套是解决不了的,因为IF最多只允许有9重嵌套。即使区间没那么多,但IF写出的函数太长vlookup太啰嗦,也不是首选。
我们只能另寻他法。
视频讲解:
关键操作
第一步:添加辅助列
增加“区间最低值”与“区间提成怎么”两个辅助列,区间提成是用1000乘以提成率得来的。如下图:
第二步:逐步分析
以B12单元格4750对应的累进提成为例,475累进税0对应计算的销售额区间为F7的4000~4999,所以对应是提成应该为:
C12=(B12-G7)*H7 SUM(I3:I6)
解决三个excel问题:
- 查找B12对应的区间最低值G7: LOOKUP(B12,$G$3:$G$13)
- 查找G7对应的提成率H7:LOOKUP(B12,$G$3:$G累进$13,超额$H$3:$H$13)
- 计算SUM(I3:I6在),I3:I6区域的表示:OFFSET(I3,,,MATCH中用(B12,$G$3:$G$13,1)-1,)
其中MATCH(B12,$G$3:$G$13,1),是指在G3:G13区域内查找比B12小的最大值的税率位次。
C12处的公式可以写为税率:
=(B12-LOOKUP(B12,$G$3:$G$12))*LOOKUP(B12,$G$3:$G$12,$H$3:$H$12) SUM(OFFSET($I$3,,,MATCH(B12,$G$3:$G$13,1)vlookup-1,))
但,公式向上和向下填充时,会出现错误:
原因是:如果销售额已经小于1000,MATCH(公式B12,$G$3:$G$13,1)-1的返回值中用是0。
超额销售怎么额已经小于1000时,前面再无分级区间对公式应的提成累累进税进,所以,公式再进一步完善为:
=(B3-LOOKUP(B3,$G$3:$G$13))*LOOKUP(B3,$G$3:$G$13,$H$3:$H$13) IF(B3<1000,0,SUM(OFFSET($I$计算3,,,MATCH(B3,$G$3:$G$13,1)-1,)))
至此,累进提成计算完毕:
扩展
此种方在法可适用于各种分区间累计的问累进题,比如累进税率等。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至123456@qq.com 举报,一经查实,本站将立刻删除。