一、速算扣除数是怎么来的？

二、速算扣除数的数组公式原理。

二、分段递减的速算扣除数及数组公式。

已知业绩金额为8万，需要分段计算提成金额，然后进行加总，各段比例如下表：

由于8万跨过了0-3万，3-6万，6-9万三个区间，需要分三段计算后再加总：

所以提成金额=3*5.5% 3*4.5% 2*3.5%=0.37万。这是设计激励方案时比较科学的方法。

以上是最基本的原理，我们来看一个实际工作中的例子，还是老规矩，我们先看效果，再说原理。

这是一个真实的案例，我们有大货，中货，小货三种货品，每种货品都设置了不同的分段提成比例，由于有淡旺季，淡季工作量不饱和，但必须保证员工基本收入，所以基础工作量的提成比例较高，超额工作量的提成比例降低，提成比例呈现下降的特点。

我们的需求是，大、中、小货的数量可以预测，但可能分为乐观、一般、保守三种数据方案。每种货的分段提成比例也需要根据测算结果进行调整。怎么能快速地计算出每种方案下的测算结果？

熟悉个人所得税的朋友，可以看到，这个问题跟“超额累进”计算类似。不一样的是，个税是金额越大，比例也越大；这里是金额越大，比例反而降低（适用于有规模经济的业务，比如返利，非饱和工作量下提成等）。

方法一：分段计算再求和

第4行是业绩值，5-10行是提成区间表，以及根据区间表计算的提成额，第11行是把5-10算出的分段提成额相加。

要点：我们来以D6单元格的公式为例：

=IF(D$4<$B5,"",IF($B6<D$4,($B6-$B5)*$C6,(D$4-$B5)*$C6))

可以看到，只有两层IF函数判断，以及一些绝对引用和相对引用，主要是方便拖动向右向下填充公式，本例中D5：O10单元格中的公式都是可以用D6的公式直接填充就可以了，在设计公式时，一定要能填充，否则公式单元格多的时候更改会非常麻烦，出错检查也能要人老命。（对绝对引用不熟悉的朋友，可以看一下我前面一篇小文函数篇之一：基础知识两条）

分段计算的基本原理就是判断当前单元格是否完全覆盖本区间上限，如果是，用本区间上限减上一区间上限的差乘以本区间的提成率，如果否，则用业绩值减上一区间的差乘以本区间的提成率。

优点：1、原理直观易懂。

2、方便检查，同一行的计算结果应该相同，除非本区间刚好是业绩值的最大区间。

不足：

不方便计算业绩金额有多个的情况，比如大货和中货也有数量，那么需要复制插入2段5-11行的内容，分别计算大货和中货。如果段数不一样，还得修改公式，比较臃肿和繁琐。

方法二、用match choose函数计算

我们可以看到D12单元格的公式：

=D4* CHOOSE(MATCH(D4,{0,30000,60000,90000,120000,150000},1),0.055,0.045,0.035,0.025,0.015,0.005) CHOOSE(MATCH(D4,{0,30000,60000,90000,120000,150000},1),0,300,900,1800,3000,4500)

这种方式已不需要中间单元格，直接就可以计算出小货的结果。我们可以看上述公式，第2行表示查找D4的值与{0,30000,60000,90000,120000,150000}中的第几个值接近，并取得对应的提成比率；第3行也是通过类似方法，不过取得的是“速算扣除数”，不过这里的提成比例是递减的，所以速算扣除数应加上去。

对于match和choose不熟悉的朋友，请看前作8个函数——尽得EXCEL查找引用之精髓（上），这是我觉得最应该看的一篇文章，但阅读量确奇低，看来愿意独立思考的确实是少数[流泪]

对于速算扣除数计算不明白的，详见本文尾部

需要提醒的是，本例中，用到了{}，但这并不是数组公式，而是引用常数，所以正常录入，不要使用数组公式的ctr shit enter录入。

点评：已经能达到目的，分别查找比率和速算扣除数，容易理解，但是公式略显冗长。

方法三、min函数

我们可以看一下D14的公式：

=MIN(D4*{0.055,0.045,0.035,0.025,0.015,0.005} {0,300,900,1800,3000,4500})

看懂了方法二，再看方法三就简单了，将业绩值与每一个比率相乘并加上“速算扣除数”，然后从这6个（分了6段）结果中取最小的一个，就是正确的结果。

我第一次看到MIN函数这种用法时，也禁不住深深感叹，这大概是“重剑无锋、大巧不工”的境界了。

这里的{}也不是数组公式，正常录入即可。

点评：截图可以看到，以上3种方法的结果是一样的，方法三非常简洁高效地实现了分段递减比例计算；业绩值、比率、速算扣除数都非常容易更改，可以方便快捷地得到各种方案的测算结果。

速算扣除数：

速算扣除数可以从简单的模型开始理解，比如只有两段:

0--30000， 5.5%;

30001--60000, 4.5%

比如业绩值是40000，我全部按4.5%计算，那其中有30000是不是少算了1%，所以这时就要加上个30000*1%=300，这个300就是“速算扣除数”，分段更多的时候，也是同样的原理。

速算扣除数示例

F列是和G列是已知条件

H20是直接用F20*G20，但H21=(F21-F20)*G21 H20，H22及以后用H21填充。

I列是F*G

J列是H列-I列，J列即为比率递减下的“速算扣除数”

测试结果=测试数*G J,可见最小的值正是测试数对应的档次比率及“速算扣除数”计算