用阶梯电价计算逻辑学习销售人员阶梯提成计算函数

作者刘伶谈薪论效 2020-04-27 15:33 457

在生活中有很多需要分段、阶梯式计算的情景，如阶梯电价、销售人员阶梯提成、个人所得税等。这些计算有一个共同点：需要分段计算，超过某个范围后需适用另外一个比例且该比例逐渐递增。

本文以阶梯电价的计算为例，充分利用Excel函数公式来介绍这种计算方法，供大家学习参考。

案例背景和数据介绍：

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

为解决上述问题提供下列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单元格的值）在各阶梯中的分布。

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，也就是下图中棕色、黄色和蓝色三个区域面积之和。

步骤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}代表各色块矩形的宽。

如果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})

本文仅代表作者个人观点，不代表三茅网立场。如因内容、版权等产生的一切纠纷，将由作者本人承担。

赞同

想薪资翻倍？先升级简历！

你的简历是否已过时，无法展示你真正的价值？即使暂时不找工作，也该为未来的机会做好准备。我们的AI简历优化服务，快速升级你的简历，让你的技能与经验焕发新光彩，助你在职场中脱颖而出，开启加薪之路！

2024-09-18 17:51

全部评论(0) 我参与的评论

关于作者

刘伶谈薪论效

+ 关注 已关注 取消关注

获得 打卡牛人 课程讲师 三茅认证称号

智帮咨询首席咨询师，专注薪酬绩效管理咨询！老师微信：13530571920

最新内容

用阶梯电价计算逻辑学习销售人员阶梯提成计算函数

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

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

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

调薪是个技术活儿

职修、业修解惑摘要2

为什么有的人做领导之后就变了性格？

末位淘汰制是否是一种有效的员工管理方式？

成长心路18—人情世故有时就是礼尚往来

专家最新作品更多>

用阶梯电价计算逻辑学习销售人员阶梯提成计算函数