电脑教程
位置：首页>> 电脑教程>> office教程>> 累进提成，IF望尘莫及，可以用LOOKUP、SUM、OFFSET、MATCH来解决

累进提成，IF望尘莫及，可以用LOOKUP、SUM、OFFSET、MATCH来解决

　　发布时间：2022-05-15 04:42:44　

标签：Excel函数,excel函数公式,excel表格制作,Excel教程

按照规定的销售额分级区间，以相应的提成率计算各区间中的提成额，最后进行汇总。

比如销售额为4750，则累进提成计算式为：=1000*1％+1000*1.2％+1000*1.4％+1000*1.6％+750*1.8％=65.5。

举例数据如下：

累进提成，IF望尘莫及，可以用LOOKUP、SUM、OFFSET、MATCH来解决

此示例的问题，只用IF的多重嵌套是解决不了的，因为IF最多只允许有9重嵌套。即使区间没那么多，但IF写出的函数太长太啰嗦，也不是首选。

我们只能另寻他法。

关键操作添加辅助列

增加“区间最低值”与“区间提成”两个辅助列，区间提成是用1000乘以提成率得来的。如下图：

累进提成，IF望尘莫及，可以用LOOKUP、SUM、OFFSET、MATCH来解决

逐步分析

累进提成，IF望尘莫及，可以用LOOKUP、SUM、OFFSET、MATCH来解决

以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,))

但，公式向上和向下填充时，会出现错误：

累进提成，IF望尘莫及，可以用LOOKUP、SUM、OFFSET、MATCH来解决

原因是：如果销售额已经小于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,)))

至此，累进提成计算完毕：

累进提成，IF望尘莫及，可以用LOOKUP、SUM、OFFSET、MATCH来解决

扩展

此种方法可适用于各种分区间累计的问题，比如累进税率等。

0
投稿

猜你喜欢

手机版 电脑教程 asp之家 www.aspxhome.com