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 单元格填充柄向下复制公式。
【excel算阶梯式提成公式最简单的阶梯式提成计算公式】

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 函数返回的结果与每个阶梯的提成相乘并求和。