作者:www.jczbzx.com   发表时间:2020-04-30 14:49:28

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

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

本文以阶梯电价的计算为例,充分利用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})

需要示例源文件,请关注刘伶老师公众号,从公众号获取源文件:

网站用图片.jpg

相关新闻

  • 采用结构化思维提升表达力

    采用结构化思维提升表达力

    作者:www.jczbzx.com   发表时间:2020-04-30 14:48:59 麦肯锡咨询顾问芭芭拉·明托这位神人,在《金字塔原理》这本书中,除了提出MECE法则之外,还提出了一个“结构化表达”工具:SCQA架构。 一、SCQA表达法 SCQA是四个英文单词的缩写: S——Situation,即背境; C——Complication,即复杂性,常意译为冲突; Q——Question,即问题; A——Answer,即答案。 比如: “刚刚入职一家互联网公司不…

    专业研究 2021年7月1日
  • 智帮咨询薪酬管理系列 构建科学薪酬体系的天龙八步

    智帮咨询薪酬管理系列 构建科学薪酬体系的天龙八步

    作者:www.jczbzx.com   发表时间:2020-04-30 14:55:26  在很多人眼里,薪酬体系设计是件很难的事。其实, HR只要把握薪酬体系设计和优化的“天龙八步”,就等于掌握了薪酬体系设计的法宝,设计出符合企业实际情况的薪酬体系也就并非难事了。 第一步:职位体系梳理 职位体系梳理是薪酬体系设计的首要环节,在企业的实际管理过程中不仅存在职位命名不规范,职位职责、权限不清,任职资格模糊,导致各岗位对企业的结果产生无法进行相对的量化评估,员…

    专业研究 2021年7月1日
  • 智帮咨询采购系列 做好采购管理,其实只需读透这张图

    智帮咨询采购系列 做好采购管理,其实只需读透这张图

    深圳市精诚智帮企业管理咨询有限公司专注于企业绩效薪酬领域的研究与落地应用的专业咨询公司,咨询服务范围:绩效设计咨询、薪酬设计咨询、股权设计咨询、年度经营计划辅导等。

    专业研究 2021年7月1日
  • 智帮咨询绩效管理系列 构建三位一体的绩效指标体系

    智帮咨询绩效管理系列 构建三位一体的绩效指标体系

    作者:www.jczbzx.com   发表时间:2020-04-30 14:59:40   绩效指标体系可谓是绩效管理的重中之重,往往企业推行绩效考核失败的主要原因就是没有构建科学的绩效指标体系,考核指标与企业的战略目标脱节,考核指标与员工的岗位职责关联度不大,最终导到绩效考核以失败告终。   “员工不会做你希望的,只会做你检查”,考核指标就是一个方向标,指挥棒,什么样的指标决定员工采取相应的行为,所以构建科学的绩效指标将是绩效管理系统构建的核…

    专业研究 2021年7月1日
  • 智帮咨询绩效管理系列 从战略到绩效的逻辑思考

    智帮咨询绩效管理系列 从战略到绩效的逻辑思考

    作者:www.jczbzx.com   发表时间:2020-04-30 14:51:22 绩效管理体系是一个系统工程,上至企业发展战略,下至各岗位的具体执行,如何从企业的战略到绩效,可以通过成型的工具和模型来实现,本文从绩效管理体系设计角度分析,提出组织绩效管理和个人绩效管理的基本架构,借鉴华为公司的绩效实践进行逻辑分析,供笔者借鉴和参考。 一、绩效管理体系 企业的绩效管理是如何从老板的层面,就是从企业的战略层面落到企业的管理层面,也就是落到我们一些核心部门,或者说…

    专业研究 2021年7月1日
  • 帮咨询 股权激励之超额利润分红法不可不知的操作要领

    帮咨询 股权激励之超额利润分红法不可不知的操作要领

    作者:www.jczbzx.com   发表时间:2020-05-01 11:02:58 很多老板都这样说:“我非常乐意将企业的股份拿一些出来,与核心高管进行分享,但是核心高管其实对股份分配并不太感兴趣,该怎么办呢?”老板想给高管股份,高管还不愿意要。其实,那些高管表面上是对股份分配不感兴趣,实际上是看不到公司的希望,也就是说对老板不太感兴趣。 近几年,一些老板像独行侠一样,到处学习,到处寻师论道,有理念、有高度,也很有境界;但是他们的团队成长很慢,跟老板脱节很严重…

    专业研究 2021年7月1日
  • 智帮咨询 绩效管理系列 基于BLM模型的战略绩效体系分析

    智帮咨询 绩效管理系列 基于BLM模型的战略绩效体系分析

    作者:www.jczbzx.com   发表时间:2020-04-30 14:54:02 一、初识BLM模型 BLM模型是IBM的“业务领先模型”(Business Leadership Model),它是IBM完整的战略规划方法论,我们可以先从两个角度了理解BLM模型:一是BLM的方法论,二是如何用BLM进行战略规划。 1、BLM的方法论 所谓的方法论实际上是将方法进行归纳、整理之后形成的一套可以重复使用的框架和逻辑,BLM模型就是IBM公司提供给我们的一个标准的…

    专业研究 2021年7月1日
  • 智帮咨询 战略地图就是寻找堵点

    智帮咨询 战略地图就是寻找堵点

    作者:www.jczbzx.com   发表时间:2020-04-30 14:58:24 战略地图是构建战略绩效常用的工具之一,其中的逻辑关系说明企业的盈利逻辑,是必要的路径选择。无论是选择哪条路径,当战略地图被制定出来以后,我们能够根据这个基础来寻找堵点,并制定关键战略。在原来的模式中,我们通过使命-愿景-远期目标-近期目标-关键战略领域的分解步骤,得到的堵点可能不够全面。原因很简单,原来是“感性地”从远期目标分解到近期目标再到关键战略,更多地是依靠经验而不是模型…

    专业研究 2021年7月1日
  • 智帮咨询绩效管理系列 绩效管理之道_构建绩效体系的核心思路

    智帮咨询绩效管理系列 绩效管理之道_构建绩效体系的核心思路

    作者:www.jczbzx.com   发表时间:2020-04-30 15:05:00     绩效管理一直是企业人力资源管理者们的一个心病,怎么操作感觉总是存在不同程度的问题,要么员工积极性不高,参与度不够,要么老板认为落地操作性不强,总是受到诟病。经智帮咨询对珠三角500余家企业推行绩效管理的情况进行调查,发现85%的企业推行绩效管理以失败告终,执行效果不理想。     经分析,我们发现林林总总的问题有很多,具…

    专业研究 2021年7月1日
  • 如果公司的战略不落地,是因为没有规划好部门作战地图

    如果公司的战略不落地,是因为没有规划好部门作战地图

    深圳市精诚智帮企业管理咨询有限公司专注于企业绩效薪酬领域的研究与落地应用的专业咨询公司,咨询服务范围:绩效设计咨询、薪酬设计咨询、股权设计咨询、年度经营计划辅导等。

    专业研究 2021年7月1日
联系我们

联系我们

0755-83826678

在线咨询:点击这里给我发消息

邮件:13530571920@163.com

9:00-18:30,节假日休息

关注微信
关注微信
分享本页
返回顶部