excel分段累计提成公式(使用Excel函数计算阶梯式提成的两种方法) | 您所在的位置:网站首页 › 阶梯电价计算公式excel › excel分段累计提成公式(使用Excel函数计算阶梯式提成的两种方法) |
excel分段累计提成公式(使用Excel函数计算阶梯式提成的两种方法)
圉畐小新 2023-01-25 04:59:24 收藏 赞 分享 分享到:阶梯式计算问题,想必大家都不陌生。常见的情形,如计算综合所得的个人所得税、阶梯式电价、水价等,都是比较常见的阶梯式计算问题。本文以计算销售提成为例,分享阶梯式计算的两种方法。 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函数返回的结果与每个阶梯的提成相乘并求和。 , 展开全文免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:[email protected] |
CopyRight 2018-2019 实验室设备网 版权所有 |