当前位置:首页 > 办公 > Excel教程 > 正文内容

函数界也搞裁员?我们恐将告别IF函数!

酷网1个月前 (10-27)Excel教程26

问题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函数的确也有自身的优势,对于一些非数字性的判断,就非它不可了。

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/40605.html

分享给朋友:

相关文章

Excel中如何跳过空格粘贴?Excel中跳过空格粘贴方法

Excel中如何跳过空格粘贴?Excel中跳过空格粘贴方法

  相信有的小伙伴也会遇到类似于这种情况,就是由两列数字,但是这些数据数据是带有空格隔开的,并不是连续的,这时候想要把数据复制到同一列该怎么办?直接复制是肯定不行的,接下来就给大家分享...

Excel查找,除了LOOKUP函数还有这对CP函数组合

Excel查找,除了LOOKUP函数还有这对CP函数组合

我们都知道Excel的VLOOKUP函数是经典的查找引用函数。但很多小伙伴们不知道的是INDEX+MATCH这个CP组合,其操作上更灵活,很多时候比VLOOKUP函数更高效。 Match函数和index函数是干什么的?...

厉害了,我的SUMIFS函数

厉害了,我的SUMIFS函数

今天讲的SUMIFS函数,相对LOOKUP而言,它有两个优势: 1、计算效率更高,当数据超过1万行,LOOKUP函数就会很卡,而SUMIFS函数依然不卡。 2、显示效果会更好,LOOKUP函数查找不到对应值显示错误值,而...

在Excel表格中怎么设置主次坐标轴?

在Excel表格中怎么设置主次坐标轴?

  Excel是一款功能非常强大的表格工具,通常用于数据统计。有时候为了是数据成趋势化表现出来,往往会在表格中制作坐标数据,那就涉及到设置主次坐标轴的问题,你知道怎么设置主次坐标轴吗?...

怎么减小excel的大小?减小Excel文件的大小

怎么减小excel的大小?减小Excel文件的大小

          随着Excel文件内容不断增加和进行编辑,文件会变得越来越大。一个工作簿除了文字,还会有各种图表和彩色图片,这些都会占用大量空间。文件过大不仅会影响邮件...

Excel表格不显示批注怎么办?Excel表格不显示批注的解决方法

Excel表格不显示批注怎么办?Excel表格不显示批注的解决方法

  Excel是一款办公软件,因其强大的制图功能深受用户喜爱。使用Excel制作图表,有时候我们需要为表格中的内容做批注,但是做完批注之后发现不显示批注,这该怎么办?针对这个问题,接下...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。