使用Excel函数计算阶梯式提成的两种方

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

1

案例描述

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

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

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

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

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

2

MAX函数

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

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

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

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

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

3

SUMPRODUCT+TEXT函数

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

在G2单元格输入公式：

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

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

公式解析：

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

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

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