比if短多了！text按阶梯分段求和，你会吗？

分段求订单总提成

下图左表记录的是员工的月完成订单量，现在要计算每个员工的提成。提成是根据右表来计算的。

当月完成单量小于等于200单时，每单提成4元；超出200单但又小于等于500单的部分，每单提成7元；超出500单的部分，每单提成8元。

以300单为例说明，前200单按每单提成4元计算，后100单(超出200的部分)按每单提成7元计算，也就是200*4+100*7。

这个题目如果让你做，你会怎么做呢？你可能想到用if来完成。

如果月完成单量小于等于200，那么就是月完成单量*4。

如果月完成单量大于200且小于等于500，那么就是(月完成单量-200)*7+200*4。

如果月完成单量大于500，那么就是(月完成单量-500)*8+300*7+200*4。

所以，最后的公式为：

=IF(B3<=200,B3*4,IF(B3<=500,(B3-200)*7+200*4,(B3-500)*8+200*4+300*7))

你会发现这个公式很长，如果分段数更多的话，那么公式就会更长。

下面我们用text来完成一下，在D3单元格输入下面的公式，向下填充，公式很短。

=SUM(TEXT(B3-{0;200;500},"[<]!0")*{4;3;1})

这个公式的思路是前一阶段的提成包含了后一阶段的部分提成，只需补上后一阶段剩余部分的提成就可以了。这么说你可能还是理解不了。下面举例说明一下。

比如月完成单量是300，300-{0;200;500}的结果是{300;100;-200}。TEXT({300;100;-200},"[<]!0")的结果是{"300";"100";"0"}。text第2参数的意思是如果第1参数小于0，强制显示为0。所以-200就变为0。

接下来用text的结果{"300";"100";"0"}*{4;3;1}得到{1200;300;0}，最后用sum求和得到1500。和200*4+100*7的结果是一样的。

其中{4;3;1}是怎么得到的呢？第1元素4是由4-0得到的，第2元素3是由7-4得到的，第3个元素1是由8-7得到的。也就是用下一个提成减上一个提成。

下面画了个图方便大家理解，用if来算的话就是200*4+100*7。用text来算的话最后就是300*4+100*3。其实它们两者是相等的。text的第1段300*4其实包含了第2段的100*4的部分，所以它的第2段是100*3，也就是100*(7-4)。

链接：

https://pan.baidu.com/s/1gtMq8EAmdSFi65xXF0h4uA

提取码：bs83