累进提成,IF望尘莫及,可以用LOOKUP、SUM、OFFSET、MATCH来解决
时间:2022-05-15 04:42:44
按照规定的销售额分级区间,以相应的提成率计算各区间中的提成额,最后进行汇总。
比如销售额为4750,则累进提成计算式为:=1000*1%+1000*1.2%+1000*1.4%+1000*1.6%+750*1.8%=65.5。
举例数据如下:
此示例的问题,只用IF的多重嵌套是解决不了的,因为IF最多只允许有9重嵌套。即使区间没那么多,但IF写出的函数太长太啰嗦,也不是首选。
我们只能另寻他法。
关键操作添加辅助列
增加“区间最低值”与“区间提成”两个辅助列,区间提成是用1000乘以提成率得来的。如下图:
逐步分析
以B12单元格4750对应的累进提成为例,4750对应的销售额区间为F7的4000~4999,所以对应是提成应该为: C12=(B12-G7)*H7+SUM(I3:I6)
解决三个问题:
查找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)-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,)))
至此,累进提成计算完毕:
扩展
此种方法可适用于各种分区间累计的问题,比如累进税率等。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
在word文档中如何快速编号、取消编号?
![](https://img.aspxhome.com/file/2023/3/24913_0s.jpg)
wps打开以后页面很小怎么调
![](https://img.aspxhome.com/file/2023/1/53611_0s.jpg)
苹果系统10.11.1 Office2016激活失败怎么办?
![](https://img.aspxhome.com/file/2023/8/16098_0s.png)
Win10系统广告关不掉怎么办?Win关闭系统广告教程
![](https://img.aspxhome.com/file/2023/8/52108_0s.jpg)
word标点符号与文字过于紧密,是怎么回事?
![](https://img.aspxhome.com/file/2023/7/35537_0s.png)
Excel表格如何进行跨工作表计算
![](https://img.aspxhome.com/file/2023/3/36323_0s.jpg)
低配电脑装win10会怎么样?低配电脑装win10教程
![](https://img.aspxhome.com/file/2023/7/48547_0s.jpg)
excel vba 指令对象
excel如何快速查询出电话号码位数不对的方法,也适用于身份证
![](https://img.aspxhome.com/file/2023/6/a140196_0s.jpg)
如何在 word 文档中插入交叉引用
![](https://img.aspxhome.com/file/2023/3/33203_0s.jpg)
Word2007制作精美的心形相册边框
![](https://img.aspxhome.com/file/2023/3/22093_0s.jpg)
在Excel中怎么快速制作表单?Excel表单制作方法介绍
![](https://img.aspxhome.com/file/2023/6/42236_0s.jpg)
怎样用excel进行大量数据的求和,排序?
![](https://img.aspxhome.com/file/2023/8/42538_0s.png)
Excel表格中如何设置姓名左右对齐操作方法分享
![](https://img.aspxhome.com/file/2023/2/16052_0s.png)
Word如何调整行距
office提示不是正版怎么解决?office提示不是正版的解决方法
![](https://img.aspxhome.com/file/2023/5/16025_0s.png)
excel中时分秒怎么转换成分钟数?
![](https://img.aspxhome.com/file/2023/6/41406_0s.jpg)
word目录域怎么使用? word目录项域设置目录的教程
![](https://img.aspxhome.com/file/2023/8/30648_0s.jpg)
iOS 14 新变化:更聪明的 Siri
![](https://img.aspxhome.com/file/2023/9/a194889_0s.png)
win10usb驱动怎么安装?win10usb驱动安装方法介绍?
![](https://img.aspxhome.com/file/2023/8/47368_0s.jpg)