时间:2020-02-10  来源:百度经验  作者:shaowu459  阅读:1

在生活中有很多需要分段、阶梯式计算的情景,如阶梯电价、销售人员提成、个人所得税等。这些计算有一个共同点:需要分段计算,超过某个范围后需适用另外一个比例且该比例逐渐递增。本经验以阶梯电价的计算为例,利用Excel函数公式来介绍这种计算方法。

如下图所示,A1单元格存储本月所用电量数(单元格实际输入的数据是633,通过自定义单元格格式显示成如图效果),需根据阶梯价格表计算本月应交电费金额。

【Excel技巧】阶梯电价、提成和个税怎么算

为解决上述问题提供下列3个公式,本次讲解第一个公式:=SUMPRODUCT(IF(A1-{0,260,600}>0,A1-{0,260,600},)*{0.68,0.05,0.25})=SUMPRODUCT(TEXT(A1-{0,260,600},"0;\0")*{0.68,0.05,0.25})=SUMPRODUCT(TEXT(A1%-{0,2.6,6},"0%;\0")*{68,5,25})

为解决上述问题提供下列3个公式,本次讲解第一个公式:=SUMPRODUCT(IF(A1-{0,260,600}>0,A1-{0,260,600},)*{0.68,0.05,0.25})=SUMPRODUCT(TEXT(A1-{0,260,600},"0;\0")*{0.68,0.05,0.25})=SUMPRODUCT(TEXT(A1%-{0,2.6,6},"0%;\0")*{68,5,25})

步骤1:将电价标准转化成阶梯图形

如下图,蓝色区域表示的是每一档标准的电费单价(C23,E22和G21)。其中酒红色单元格表示的是每一档电费单价与上一档单价之差(E23和G22)。中间部分表示的是用电度数(A1单元格的值)在各阶梯中的分布。

【Excel技巧】阶梯电价、提成和个税怎么算

1)假设当月用电量低于或等于260度,那么该月电费为A1*0.68。2)假设当月用电量大于260度且小于等于600度,那么该月电费计算为:260度*0.68+(A1-260)度*0.73。整理得到:A1*0.68+(A1-260度)*0.05。理解起来实际意义是这样的:当月所用电量每度先支付0.68元,超出260度的每度再支付0.05元。3)和步骤2推断类似,当月用电量大于600度时电费计算结果为:A1度*0.68+(A1-260)度*0.05+(A1-600)度*0.25。实际意义为:所有的电量每度先支付0.68元,超过260度的每度先支付0.05元,最后超过600度的部分每度再额外支付0.25元。

1)假设当月用电量低于或等于260度,那么该月电费为A1*0.68。2)假设当月用电量大于260度且小于等于600度,那么该月电费计算为:260度*0.68+(A1-260)度*0.73。整理得到:A1*0.68+(A1-260度)*0.05。理解起来实际意义是这样的:当月所用电量每度先支付0.68元,超出260度的每度再支付0.05元。3)和步骤2推断类似,当月用电量大于600度时电费计算结果为:A1度*0.68+(A1-260)度*0.05+(A1-600)度*0.25。实际意义为:所有的电量每度先支付0.68元,超过260度的每度先支付0.05元,最后超过600度的部分每度再额外支付0.25元。

步骤2:用面积图的方法解释一个例子

假设当月用电量为678度,那么总电费金额=678度*0.68+(678-260)度*0.05+(678-600)度*0.25,也就是下图中棕色、黄色和蓝色三个区域面积之和。

【Excel技巧】阶梯电价、提成和个税怎么算

步骤3:将上述计算方法用数组方式表达

选中C70:C72,输入=(A1-{0;260;600})*{0.68;0.05;0.25},按Ctrl+Shift+Enter运行公式即可直观在单元格中看到步骤2中三个颜色块代表的计算结果。上述公式参数在步骤二面积图中的意义如下:A1-{0;260;600}代表各色块矩形的长,{0.68;0.05;0.25}代表各色块矩形的宽。

【Excel技巧】阶梯电价、提成和个税怎么算

如果A1的值小于分段点,比如说是A1=576度,那么A1-{0;260;600}={576;316;-24},其中的负数说明该分段点所在的颜色块面积不应算在结果之内。因此外层嵌套个IF函数,如果返回值小于0则返回0,也就是:IF(A1-{0,260,600}>0,A1-{0,260,600},)。上述返回结果再乘以{0.68,0.05,0.25}并求和就得到了应交电费总数,完整公式为:=SUMPRODUCT(IF(A1-{0,260,600}>0,A1-{0,260,600},)*{0.68,0.05,0.25})

如果A1的值小于分段点,比如说是A1=576度,那么A1-{0;260;600}={576;316;-24},其中的负数说明该分段点所在的颜色块面积不应算在结果之内。因此外层嵌套个IF函数,如果返回值小于0则返回0,也就是:IF(A1-{0,260,600}>0,A1-{0,260,600},)。上述返回结果再乘以{0.68,0.05,0.25}并求和就得到了应交电费总数,完整公式为:=SUMPRODUCT(IF(A1-{0,260,600}>0,A1-{0,260,600},)*{0.68,0.05,0.25})

Top