函数界也搞裁员?我们恐将告别IF函数!
发布时间:2022-12-05 01:38:05
问题1:如下图所示,当实际销售量大于销售量目标时,奖励1000元。
通常遇到这类问题,首先想到的一定是IF函数,公式为:=IF(C2>B2,1000,0)
大家都能理解这个公式,而且这个问题也相当简单,简单到甚至都不需要用函数就能解决:
在公式“=(C2>B2)*1000”中,利用了逻辑值直接参与计算,当C2>B2成立时,得到TRUE,反之得到FALSE。逻辑值在与数字计算时,TRUE等同于1,FALSE等同于0,因此公式“=(C2>B2)*1000”同样可以得到所需的结果。 问题2:还是计算奖励的问题,这次对奖励规则做了调整,当实际销量大于目标销量时,每超过一个销量奖励50元,1000元封顶。 这时候如果还用IF函数解决,公式就变成了“=IF(C2B2,0,IF((C2-B2)*50<1000,(C2-B2)*50,1000))”。
这个公式进行了两次判断,首先判断是否达到奖励标准,也就是C2
B2时,不发奖励;如果达到奖励标准,还要进一步判断奖励是否达到1000元,也就是(C2-B2)*50<1000,如果不到1000,按实际奖励计算,超过了仍按1000计算。 在这个问题中,要用好IF已经需要一点功力才行了,公式明显比第一个问题复杂了很多,这时候,IF函数的新对手出现了,而且一下子就来了两个:=MIN(MAX((C2-B2)*50,0),1000)
MIN函数用于得到几个数字中最小的一个,MAX函数用于得到几个数字中最大的一个,这两个函数配合了一下,竟然把一个原本该是IF函数的活给轻松解决了。 这个公式需要分成两部分来理解,首先MAX((C2-B2)*50,0)得到理论奖励和0中的较大者,如果不够奖励标准,(C2-B2)*50就是一个负数,较大者为0,反之就是超额销量*50;接下来再将MAX得到的结果和1000放在一起,通过MIN函数来得到较小者,如果奖励金额超过1000,则返回1000。这样就可以把一个比较复杂的IF公式变得简洁。 问题3:按超额数量计算阶梯奖励,规则如图所示。
如果还想用IF来解决这个问题,可以自己试试,确实太长了。下面分享几个不用IF的公式供大家参考: 公式1:=MIN(MAX(INT((C2-B2)/10+1)*300,),1000)
这就完全是一种数学思路了,按照阶梯奖励的规则,每一档相差300元,1000元封顶,所以先把超额数量除以10再加1,乘上300就是奖励金额:
但是会出现负数和超过1000的情况,再用问题2的思路,结合MAX和MIN就能得到最终结果。 公式2:=MIN(MAX(CEILING(C2-B2+1,10)*30,),1000)
这个公式可以看作是公式1的改版,还是利用了奖励规则中的一些规律性,用CEILING(C2-B2+1,10)*30取代了INT((C2-B2)/10+1)*300。CEILING函数是将数字按照指定的倍数向上舍入,看看下图示例或许就明白了。
公式3:=LOOKUP(C2-B2,$F$2:$H$6)
公式3完全是利用了LOOKUP可以进行区间匹配的功能,需要说明的是,本例中使用了一个辅助区域,这对于初学者来说是非常有用的,注意辅助区域的首列一定要用下限值。 如果不想用辅助区域,可以按f9键把公式里的区域变成数组就行了: =LOOKUP(C2-B2,{-999,0;0,300;10,600;20,900;30,1000})
如果奖励标准发生变化时,自己修改数组中的数据即可。 结论:以上案例中,分别使用了逻辑值、MIN、MAX、INT、CEILING和LOOKUP等函数来取代IF,实际上能取代IF的函数还有一些,例如CHOOSE,TEXT等都可以,篇幅所限不再一一列举。
当问题的判断条件是基于数字的时候,IF往往不是唯一可以选择的途径,换个思路或许可以得到更多方法,但是IF函数的确也有自身的优势,对于一些非数字性的判断,就非它不可了。
猜你喜欢
- 在行政工作中,处理日期数据的时候,是不是尤其苦恼呢,因为有的人将日期填写为:1998.12.13,19981213,1998 12 13……
- 在我们使用电脑的过程中,如果知道电脑使用的快捷键就能够方便快捷地使用电脑,而且还能够提高工作效率。所以大家平时可以多看看电脑快捷键的教程,学
- 工作中一些重要excel文件需要设密码进行保护,那怎样设置呢?下面是小编为大家精心整理的关于如何在excel表格加密设密码,希望能够帮助到你
- 方法一:wps光标变粗在键盘上按住“INSERT”键, 方法二:1、按住“Ctrl+A”全选文字,点击菜单栏上的“
- 本节教程就为大家解决这个问题,在数字单元格的格式设置:在数字后面添加单位的格式,在数字前面添加标识符的格式。①启动Excel2007,选中E
- (2)查询全部列将表中的列全部都选出来,可以在Select关键字后面列出所有列的名字。而当一个表的列太多达到几十甚至上百个时,全部写出来显得
- 如何按指定的名称创建一批文件夹,同样是借助Excel公式生成代码复制到文本文件,生成批处理文件,具体操作如下:Excel表格罗列生成新文件夹
- Excel 2007是2007版的Excel办公软件,这个版本适合运行的环境有Win2003、WinXP、Win2000、Win9X,这是一
- 升级win10后出现各种问题,现在将解决办法一一呈现给大家,希望对后面的用户有所帮助,供大家参考!关于打开提示文档权限,安全问题。。。等等请
- EXCEL软件中,我们可以让其自动填充一些序号,但是,有些许好在EXCEL的列表中没有,这时候,我们就需要自己来定义了,下面为大家介绍Exc
- 在Excel中录入数据的过程中,因为数据繁多而无法估计到单元格的换行,导致一些过长的数据显示异常,这个时候我们可以设置成自动换行的形式,这样
- 很多Win7用户在升级到Win10正式版后,发现原来使用Office 2013创建的文件都无法打开了,包括Word、Excel和powerp
- 简单的柱形图说服力是有限的,添加涨跌箭头,可以使数据的变化情况更为直观。今天就给大家带来一个Excel图表技巧——制作带涨跌箭头的柱形图。基
- Word2013文档怎么设置打印自选图形?Word2013文档中创建的自选图形可以打印出来,该怎么设置呢?下面我们就来看看详细的设置方法,需
- 在excel表格中观察数据的时候,很多时候用折线图很直观,但这种图表应该怎么生成呢?下面就跟小编一起来看看吧。excel生成折线图的步骤先打
- 点击确定,这个时候也可以区分一下xls文件名,选择我们要保存的位置, 4、我们先打开一下这个模板文件,点击另存为, Excel中怎么制作表格
- Excel2007基础教程:删除行和列您可能也想在一张工作表中删除多个行或列。例如,工作表中可能包含不再需要的旧数据。要删除一行或多行,可使
- 在发布win10操作系统后,微软公司最近又一次发布了一款新的操作系统win10x。因此,对win10x的这次更新主要集中在图标上面。相信一直
- 想要桌面好看,当然是选好看点的壁纸。win10自带的壁纸有些还是蛮精致好看的,设置上这些壁纸整个桌面瞬间好看了许多,也能让我们使用者心情陪佳
- win10系统相比win7来说,更丰富了服务功能与个性化,比如开机快速启动等,快速启动原理和休眠类似,但是所有用户进程都会被结束掉。结束后剩