excel分段累计提成公式（使用Excel函数计算阶梯式提成的两种方法）

阶梯式计算问题，想必大家都不陌生。常见的情形，如计算综合所得的个人所得税、阶梯式电价、水价等，都是比较常见的阶梯式计算问题。本文以计算销售提成为例，分享阶梯式计算的两种方法。

1

案例描述

如下图所示，A1:C4区域为销售提成表。当销售额在1万元以下时，按1%计算提成；当销售额超过1万元，不超过3万元时，超过部分按2%计算提成；当销售额超过3万元时，超过部分按3%计算提成。

以计算“皮卡球”的提成为例，销售额为38000，计算提成的第一种方法为：提成=10000*1% 20000*2% 8000*3%=740。这是计算销售提成最直观的方式。

计算提成的第二种方法为：提成=38000*1% (38000-10000)*(2%-1%) (38000-30000)*(3%-2%)=740。

第二个计算公式的逻辑是，首先38000全部按照第一阶梯的提成比例1%计算提成；然后超过第一阶梯销售额部分（即38000-10000），按照第二阶梯和第一阶梯的提成比例差异（2%-1%），补计提销售提成；最后超过第二阶梯销售额部分（即38000-30000），按照第三阶梯和第二阶梯的提成比例差异（3%-2%），补计提销售提成。

理解第二个计算公式的逻辑，对于接下来理解使用Excel函数批量计算销售提成非常重要。接下来分享的两种方法，都是由第二个计算公式的计算逻辑构造。

2

MAX函数

计算阶梯式提成的第一种方法是使用MAX函数。

如下图所示，在G2单元格输入公式：

=F2*1% MAX((F2-10000)*(2%-1%),0) MAX((F2-30000)*(3%-2%),0)

拖动G2单元格填充柄向下复制公式。

MAX函数用于获取一组数值的最大值。以MAX((F2-10000)*(2%-1%),0)为例，当销售额没有超过第一阶梯的销售额上限10000时，(F2-10000)*(2%-1%)为负值。MAX((F2-10000)*(2%-1%),0)返回0。当销售额超过第一阶梯的销售额上限10000时，(F2-10000)*(2%-1%)为整数，MAX((F2-10000)*(2%-1%),0)返回值为(F2-10000)*(2%-1%)。

3

SUMPRODUCT Text函数

计算阶梯式提成的第一种方法是使用SUMPRODUCT TEXT函数。

在G2单元格输入公式：

=SUMPRODUCT(TEXT(F2-{0,10000,30000},"0;!0")*{0.01,0.01,0.01})

拖动G2单元格填充柄，向下复制公式。

公式解析：

（1）F2-{0,10000,30000}，指F2单元格的销售额依次减去第一阶梯、第二阶梯、第三阶梯的销售额下限分界点。当销售额为38000时，返回的结果为{38000,28000,8000}；当销售额为5000时，返回的结果为{5000,-5000,-25000}。

（2）TEXT(F2-{0,10000,30000},"0;!0")用于将F2-{0,10000,30000}返回的结果中复制设置为0。代码“0;!0”，指当数值为正值时，返回数值本身，当数值小于0时，返回0。当销售额为38000时，Text函数返回的结果为{38000,28000,8000}；当销售额为5000时，Text函数返回的结果为{5000,0,0}。

（3）SUMPRODUCT函数则将Text函数返回的结果与每个阶梯的提成相乘并求和。