【辅助列解决实际问题_系列3】分级差求累进费率

cleverzhzhf

分级差累进税率，是很多公司常用的提成方式，最经典的使用地方莫过于税率，这个让大家“痛恨”的家伙。
今天就来讨论一下这个东西的实际使用方式。

三国公司有这样的一个销售提成政策：
销售业绩在0-10万之间，提成5%
销售业绩在10-20万之间，超过10万的部分提成8%
销售业绩在20-40万之间，超过20万的部分提成15%
销售业绩在40-80万之间，超过40万的部分提成25%
销售业绩超过80万的，超过80万的部分提成40%

题目描述参考如下：


当我们只需要处理一个值的时候，这时候多做两个辅助列，看着既清晰又简单：


当需要处理值为多个时，一个个来这样手动，简直是累死活人哦~~
这时候最常用的就是采用速减系数的方式：

分级差求累进费率.rar (9.95 KB, 下载次数: 467)

2015-2-3 00:13 上传

点击文件名下载附件

---

一个公式解决问题，J24数组公式：

1. =SUM(TEXT(I24-$B$12:$B$16,"0;\0")*($C$12:$C$16-N(IF({1},$C$11:$C$15))))

复制代码

cleverzhzhf

方法一：
诸葛亮销售业绩100万提成：(10-0)*5%+(20-10)*8%+(40-20)*15%+(80-40)*25%+(100-80)*40%=22.3
曹操销售业绩为30万提成：(10-0)*5%+(20-10)*8%+(30-20)*15%=2.8
由此可以看出来，当不在的那一档提成时候，就直接用这个级差乘以相应的提成比例；
当业绩处于某一档的时候，用这个业绩减去当前所处的级别金额，如(30-20).

D12：=IF(B13="","",B13-B12)
公式上很好理解，这样为了算出来每两个级差之间的差值。
当B17-B16时候，由于B17没有值，如果硬减，则会出现负数，对整体结果有影响，所有用“空”来处理。
（也可以在B17放一个很大很大的数字，只要是销售业绩达不到的数值即可，如9999）

E12：=MAX(MIN($I$12-B12,D12),0)
首先是MIN($I$12-B12,D12)，这个是要做决定，究竟是使用级差，还是用目标值减去级别金额
其次是MAX(MIN($I$12-B12,D12),0)，对于(30-40)这种得到负值的，明显不是我们不需要的数字，所以就是把小于0的值全部放弃。

J12：=SUMPRODUCT(E12:E16,C12:C16)
这就是一个常见的对应项乘积的和，便能够得到最终的提成结果了

cleverzhzhf

方法二：与方法一的思想不同，这个是要先按照目前达到的最高比例算提成，然后再把之前的级别多提的部分减去。而这个减去的值就是速减系数。


速减系数：
<方法一>D24：=IFERROR(SUM(($B24:B$25-$B23:B$24)*(C24-$C23:C$24)),0)
这是一个数组公式，得到了速减系数。其实真正有意义是从D25开始的：
=IFERROR(SUM(($B$25:B25-$B$24:B24)*(C25-$C$24:C24)),0)

分别用每一个级差去乘以，当前级别费率与相应级差的费率的差值，然后再把这些数字求和，就是相应的多算出来的提成。
如果读不懂，无所谓，只需要背下来即可，记得这是求得速减系数的方式。

<方法二>E24：=B24*(C24-N(C23))+E23
当被上面第一个复杂的数组公式打败的时候，其实沉下心来，速减系数算的就是相邻两级之间的差值。
其实正常写法是=B24*(C24-C23)+D23，只不过因为C23一般来讲是标题，直接减会报错。而N函数可以将所有文本转化为数字0，方便做减法。


提成：
J24：=I24*LOOKUP(I24,$B$24:$C$28)-LOOKUP(I24,$B$24:$D$28)
K24：=MAX(I24*$C$24:$C$28-$D$24:$D$28)

这两个公式无需多讲，都是较为基本的用法。根据个人理解状况和习惯记忆。

cleverzhzhf

方法三：
反正这个速减数是一个一劳永逸的事情，只要第一次把这些数字确定好，以后用的时候都会方便，所以完全不介意第一次做的时候稍微麻烦一点。
结合以上方法一和方法二的思想：

1、先使用以上方法一，分别求得临界点的实际提成，得到一组数字（或者直接用草稿纸加计算器算，一般来讲也不会很麻烦）：
    0、0.5、1.3、4.3、14.3

2、使用方法二的思想，用销售提成的级别分别乘以相应的提成比例，如：0*5%、10*8%、20*15%……得到一组数字：
    0、0.8、3、10、32

3、继续思考以上方法二的思想，用两者相减，便得到一组速减数：
    0、0.3、1.7、5.7、17.7

4、把这一组数填写到D24:D28，然后使用方法二的J4公式：
    J24：=I24*LOOKUP(I24,$B$24:$C$28)-LOOKUP(I24,$B$24:$D$28)
    或者：=MAX(I24*$C$24:$C$28-$D$24:$D$28)

满坛皆为吾师

感谢老师分享，内容对我有帮助！

minren118

早发现此帖，少走许多弯路，在做2019年个税的自定义函数，发现网上没找到那个扣除数，自己来做了下，找了好多资料才做出来，呜呜

57wk

学习了。感谢老师分享

sucx007

谢谢老师分享，原来这个早就了啊！

mvphp

感谢分享！！！！！

忘情的城市

比例提成 通俗易懂

感谢老师分享