累进提成,IF望尘莫及,可以用LOOKUP、SUM、OFFSET、MATCH来解决
发布时间:2022-05-15 04:42:44
按照规定的销售额分级区间,以相应的提成率计算各区间中的提成额,最后进行汇总。
比如销售额为4750,则累进提成计算式为:=1000*1%+1000*1.2%+1000*1.4%+1000*1.6%+750*1.8%=65.5。
举例数据如下:
此示例的问题,只用IF的多重嵌套是解决不了的,因为IF最多只允许有9重嵌套。即使区间没那么多,但IF写出的函数太长太啰嗦,也不是首选。
我们只能另寻他法。
关键操作添加辅助列
增加“区间最低值”与“区间提成”两个辅助列,区间提成是用1000乘以提成率得来的。如下图:
逐步分析
以B12单元格4750对应的累进提成为例,4750对应的销售额区间为F7的4000~4999,所以对应是提成应该为: C12=(B12-G7)*H7+SUM(I3:I6)
解决三个问题:
查找B12对应的区间最低值G7: LOOKUP(B12,$G$3:$G$13)
查找G7对应的提成率H7:LOOKUP(B12,$G$3:$G$13,$H$3:$H$13)
计算SUM(I3:I6),I3:I6区域的表示:OFFSET(I3,,,MATCH(B12,$G$3:$G$13,1)-1,)
其中MATCH(B12,$G$3:$G$13,1),是指在G3:G13区域内查找比B12小的最大值的位次。
C12处的公式可以写为:
=(B12-LOOKUP(B12,$G$3:$G$12))*LOOKUP(B12,$G$3:$G$12,$H$3:$H$12)+SUM(OFFSET($I$3,,,MATCH(B12,$G$3:$G$13,1)-1,))
但,公式向上和向下填充时,会出现错误:
原因是:如果销售额已经小于1000,MATCH(B12,$G$3:$G$13,1)-1的返回值是0。
销售额已经小于1000时,前面再无分级区间对应的提成累进,所以,公式再进一步完善为:
=(B3-LOOKUP(B3,$G$3:$G$13))*LOOKUP(B3,$G$3:$G$13,$H$3:$H$13)+IF(B3<1000,0,SUM(OFFSET($I$3,,,MATCH(B3,$G$3:$G$13,1)-1,)))
至此,累进提成计算完毕:
扩展
此种方法可适用于各种分区间累计的问题,比如累进税率等。
猜你喜欢
- 最近很多朋友问小编word按空格键删除后面的文字怎么办?下面小编给大家带来word按空格键删除的解决方法,需要的朋友可以参考下我们在操作wo
- 文件加密可以在文件保护工作表保存的时候设置一下访问密码,excel2019怎么加密文件?这篇文章主要介绍了excel2019文件加密教程,需
- 1、用word2010打开需要替换内容的文档。 2、将光标定位在文档的起始处,然后点击“开始”选项卡。  
- 一元二次方程求根公式是高中数学最重要的几个公式之一,在教学方式越来越趋向于电子信息化的今天,如何在Word中打出一元二次方程求根公式呢?如何
- 有些用户的电脑设置了密码,就是为了保护电脑的安全,但是电脑如果只是自己在家使用的话,就不需要密码保护了,因为开机要输入密码很麻烦,每次锁屏也
- 在Word中设置字体通常是到字体下拉框中选择,如果你每次选择的都是那几种常用的字体,如黑体、宋体、仿宋体的话,不如将它们直接请到工具栏上,这
- 书名号怎么打出来?书名号相信大家都不会陌生了,正常情况下,我们会将书名、歌曲名、作品名等用书名号框起来,这样就可以让读者一目了然。然而很多用
- 记事本是一项非常好用的功能,可以记录用户的待办事项,最近有些win11用户想要对记事本的默认字体进行修改,具体要如何操作呢?下面小编就给大家
- 最近有不少win10用户反映说电脑开机之后,显示器显示没有信号,重新开机也是这种情况,完全无法进入系统,这该怎么办?不要着急,下面小编就给大
- Win10 KB5000802更新后电脑打印机崩溃蓝屏怎么办?最近微软更新的最新补丁又一次为用户带来了一堆bug,而此次bug对于用户影响非
- Excel核对数据,用小技巧可以轻松很多,不需要花费大量的时间一个个查看数据的不同。下面给大家分享多种核对数据方法。1、Ctrl+\这个方法
- 从2007取消了2003那样的自动编号后,页码就不是很好增加了,不过我们可以自己来完成,也不是很复杂,今天,小编就教大家在Word中2007
- 首先,打开Word 2010,“文件”-“选项”-“语言”,检查一下编辑语言,看看中文是否启用。如果没有,安装启用中文。 如果已安装,则打开
- 今天跟大家分享如何为Word文档中的文本添加超链接。为文本添加超链接以后,可以快速跳转到链接的网页。比如在下图文档中,我们按住ctrl键单击
- 关于最新版本的office 2013,其实所有的使用教程在软件中就有,就是“帮助”选项,只不过我们使用的少而已。这就是一个天然的使用offi
- word2010怎么去除图片背景呢?很多用户对此还不是很清楚,小编这里就给大家带来有关word2010怎么去除图片背景的回答,希望能够帮助大
- 我们在Word编辑文字的时候,为了美观起见,可以适当使用艺术字的方式,在文档中插入艺术字体,类似于插入图片的形式,对文档进行编辑。那么wor
- word怎么调出节日庆祝类的图片?word中想要插入圣诞节的圣诞帽和圣诞树,该怎么插入呢?其实word中有很多有趣的图片,下面我们就来看看调
- 用自己的电脑来共享,还要输入网络凭据。这不就像要证明钱包里的钱是我的道理?让人觉得有点麻烦又不爽,这其实是可以解决的,毕竟是自己在用的电脑,
- 我们可能熟悉使用INDEX、SMALL等在给定单列或单行数组的情况下,返回满足一个或多个条件的值的列表。这是一项标准的公式技术。我们述了一种