打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
世上没有如果,但有IF

世上没有如果,但有IF(狗头)——IF独立宣言。

人没有了逻辑就是蠢蛋,函数没有了IF就是白痴——IF自强宣言。

1,认识IF函数

和SUM函数一样,IF也是Excel中最基础最常用的函数之一。事实上,我们曾在论坛做过一次投票活动,在所有函数之中,你最先认知的是哪一个?结果是SUM和IF分别高居前一二名——忘了说了,投票选项只有两个。 

IF函数属于逻辑判断函数,它可以根据指定条件是否成立,返回对应的结果。官方简历如下:

IF(logical_test,value_if_true,value_if_false)

汉译之后就是:

IF(喂~谈个条件吧,如果条件成立~怎么怎么着,如果条件不成立~又怎么怎么着)

数一数,一共有三个参数。

第1个参数是必须的,是一个结果为真或假的任意表达式,也就是判断条件。

第2个参数是可选的,当判断条件成立时,返回的指定结果。

第3个参数也是可选的,当判断条件不成立时,返回的指定结果。

举个例子

=IF(B2>=60,'及格','不及格')

B2>=60是一个表达式,判断B2单元格的值是否大于等于60。

如果表达式成立,会得到逻辑值TRUE,也就是说B2>=60这事是真的。

如果不成立呢,会得到逻辑值FALSE,也就是说B2>=60这事是假的。

当B2>=60这事是真的,我们就去找第二个参数,于是得到结果,及格。

当这事是假的,那我们就去找第三个参数,于是得到结果,不及格。

加入B2是数值99,猜一猜,下面这条IF函数会返回什么结果?

=IF(B2>=60,'不及格','及格') 

          

2,IF函数多层嵌套

我们上面举了个成绩考评的例子,把成绩分为了及格和不及格两种。

在实际生活中,这显然很不靠谱。

你说作为一位人民教师,怎么能犯这种非是即否的思维错误呢?你把60分以下的都叫做不及格——那就算了,怎麽能把60分以上的都叫做及格呢?这对孩子的幼小心灵造成了多大的打击?你怎么着得划分个优秀出来吧?——来自多位优秀生家长的质问。

好吧,为了和谐社会,咱们只能把公式修改一下:

=IF(B2<60,'不及格',IF(B2>90,'优秀','及格'))

公式先计算B2单元格的值是否小于60,如果成立则返回“不及格”,如果B2的值大于或等于60呢?公式继续往右走,到第三个参数那里,第三参数是一个IF函数,它先判断B2的值是否大于90,如果成立,则返回优秀,不然就统统返回及格。

第2个IF函数是第1个IF的嵌套函数,用于解决第1个IF条件为假时的分支问题。

这就好比走路,IF这家伙只有两个脑细胞,第一个二叉路口,IF自己就可以搞定,到了第二个二岔路口,它脑细胞用完了,怎么办呢?赶紧生个孩子,让它去继续探路吧,又到了下一个路口,孩子的两个脑细胞也用完了,怎么办呢?没事,反正生函数又不犯法,再生一个,继续探路吧……

如此延续,IF函数最多能生64个孩子,也就是说Excel最多支持64个不同的IF函数嵌套。

……

一条优秀的IF函数嵌套公式,通常都是从假的一端不断延伸,而不会一会儿真端,一会儿假端。

这句话是什么意思呢?

还是举个栗子。

假设成绩大于80分的家长,同样不满意学生的评级制度,要求增加一个“良好”。

好吧,我们只能把评级规则再修改一下,小于60的,都是不及格;60-80及格;80-90为良好;90分以上优秀。

有朋友此时可能会把IF函数写成这样:

=IF(B2>90,'优秀',IF(B2>60,IF(B2>80,'良好','及格'),'不及格'))

尽管这公式能得到正确的结果,但阅读起来逻辑很不清楚。

它的路径是这样的,一会儿从“假”的路径上分叉,提出判断条件,一会儿又从“真”的路径上分叉,提出判断条件。

          

换个写法:

=IF(B2>90,'优秀',IF(B2>80,'良好',IF(B2>60,'及格','不及格')))

这样逻辑是不是就清楚多了?

它的路径如下图,一直在从“假”的路径上分叉,层层推进。

          

……

IF函数简单又好用,但有句话不知当讲不当讲反正我要讲,当IF函数嵌套超过三层时,必然会有更高效更简洁的函数代替它工作。

会是哪些函数呢?

微笑脸,请坐,不久以后你就知道了。

3,AND和OR函数

这节我们来讲下IF函数的多条件判断问题。

多条件判断分为两种情况,一种是“并且”关系,一种是“或”关系。

什么是“并且”关系呢?

并且关系指的是多个条件都同时成立,结果方才为真的情况。

比如说,你的电脑有三个盘,分别是C盘、D盘和G盘。现在杀毒,C盘没有毒,D盘没有毒,G盘也没有毒,三个的条件都成立,OK,我们可以得出结论你的电脑没中毒,你是个老实人。但倘若其中任意一个盘有毒,那你就不能说自己电脑没中毒了,看我小眼神,对不对?

          

这就是一种典型的“并且”关系问题。

如何使用函数判断“并且”关系是否成立呢?

函数新人通常会使用AND函数。

其语法如下:

=AND(条件1,条件2,条件3,条件n……)

举个实际公式的例子。

=AND(2>1,3>2,4>3)

AND函数包含了三个条件,每个条件计算结果都为TRUE,也就是说每个条件都成立,计算结果返回TRUE。

但倘若哪怕有一个条件为假,比如公式:

=AND(1>2,3>2,4>3)

结果即为FALSE         

……

举个HR常见的小栗子:

以下图所示数据表例,假设女人55岁退休,男人60岁退休,现在我们需要计算数据列表中的女性是否应该退休……

          

D列公式如下:

=IF(AND(B2='女',C2>55),'退休','')

AND函数是一个表达式,作为IF函数的第一参数,有两个判断条件,一个是B2单元格性别是否为“女”,另一个是C2单元格的年龄是否大于55,只有两个条件都成立,AND结果才返回TRUE。

当AND函数结果为TRUE,IF函数返回值“退休”,否则返回假空''。

……

……

说完了“并且”关系,我们再聊下“或”关系。

“或”关系刚好和“并且”关系唱反调,“并且”关系认为所有的条件都成立,才算数儿。“或”关系不这么认为,它觉得做人要大度,有一个条件成立就算OK了,只有所有条件都不成立时,才不算数。

如果“并且”和“或”去谈恋爱,他们的爱情观大概是这样的。“并且”觉得爱情像眼睛,容不下一粒沙子,有一粒沙子咱们就拜拜吧。“或”觉得爱情像沙子,抓的越紧,漏的越多,所以能抓住一个条件就行了——说实话,你喜欢谁?——你又是谁?

举个栗子,如下图所示,是一张成绩表,我们要判断A列学生是不是优秀生,规则是只要语文或数学有一门成绩大于90分即可。

D列公式如下:

=IF(OR(B2>90,C2>90),'优秀','')

OR函数是一个表达式,作为IF函数的第一参数,有两个判断条件,分别判断语文和数学的成绩是否大于90分,如果有一个条件成立,则OR函数返回TRUE。

当OR函数结果为TRUE时,IF函数返回值“优秀”,否则返回假空。

好吧,换下规则——和双科均优秀生的家长无关——只有当语文和数学双科成绩都大于90分时,才可以评定为优秀生,公式又该如何书写呢?

你来!

……

……

小贴士:

我有时会见到有人将“并且”关系的逻辑表达式写成下面这样。

=IF(60<B2<80,'良好','')

从人类思维的角度讲,这么写,倒也没错,但问题是——Excel它不是人啊?(我这是骂出了多少人的心声)。

你想的是比较B2单元格的值是不是大于60同时小于80,但Excel不这么算。

它会照常从左向右算,先算B2的值是不是大于60,于是返回逻辑值TRUE或FALSE,然后再和80比较谁大谁小。我们第3章时讲过了,不管是TRUE还是FALSE,作为逻辑值都比数值大,所以60<B2<80必然返回结果FALSE,不论B2单元格的值是大是小,整条IF函数也就必然会返回第三参数所对应的值:假空。

4,温馨提示:

新人对于本节内容,可先了解,不用强求理解。

我们上一节详细讲了AND和OR函数,但我这会儿忽然很明确告诉你,像我这样的函数高手(什么眼神,我一米八不高吗?)几乎从来不用这俩函数,你会是什么反应? 

          

如果不用AND和OR函数,那我们用什么呢?

……

……

不急,来杯珍珠奶茶,听我慢慢道来~

你还记得吗?在讲数据类型的时候,我们说过逻辑值是可以参与数学运算的。

其中TRUE视为1,比如公式:=TRUE+1,结果等于2。

另一个逻辑值FALSE视为0,比如公式:=FALSE+1,结果等于1。

那么反过来,数值能否参与逻辑判断呢?

也可以。

比如……

= IF(1, TRUE, FALSE),结果为TRUE,这意味着在IF函数眼中,1就等同于逻辑值TRUE。

= IF(0, TRUE, FALSE),结果为FALSE,这意味着在IF函数眼中,0就等同于逻辑值FALSE。

= IF(-1, TRUE, FALSE),结果为TRUE,这意味着在IF函数眼中,负数也等同于逻辑值TRUE。

事实上,在工作表函数的逻辑判断中,所有的数值,除了0,不管是正数、负数、小数都等同于TRUE,只有0是假的。

在IF看来,你腰缠万贯,是有钱人,你负债千万,还是有钱人,只有你一个子儿也无,才算是真正的穷光蛋,是个FALSE。

我早说函数是个势利眼儿,这下你信了吧?

……

既然逻辑值能参与数学运算,数值又能够替代逻辑值做真假判断,那么多条件的逻辑表达式,我们也就可以改用乘法和加法了。

这句话怎么说? 

比如之前举的例子,当学生语文和数学得分均高于90分才算优秀生。

AND函数是这样的:

=IF(AND(B2>90,C2>90),'优秀','')

使用乘法表示是这样的:

=IF((B2>90)*(C2>90),'优秀','')

在上述公式中,(B2>90)*(C2>90)部分有3种结果。

一种情况是B2>90计算结果为TRUE,C2>90计算结果也为TRUE,TRUE*TRUE=1*1,结果为1,在逻辑判断中,所有的数值除了0以外都为真,说明IF的判断条件成立。

另一种情况是B2<90或C2<90有一条是成立的,另外一条不成立,也就是说两个判断条件里有一条会返回FALSE,在数学运算中,FALSE等同于0,零乘以任何数值结果必然为零。而在逻辑判断中,0为假,这就说明IF的判断条件不成立。

最后一种情况,B2<90和C2<90两条都不成立,结果都返回FALSE,FALSE*FALSE=0*0,IF函数计算结果显然等同于第2种情况。

总结一下:

逻辑值FALSE在数学运算中被视为0,TRUE会被视为1。而在乘法运算中,0乘以任何数值结果均为0,所以只有当每一个表达式计算结果均为TRUE时,整个条件方才成立,否则不成立——这,不就是一个赤果果的“并且”关系的概念吗?

因此AND函数完全可以被乘法代替。总结套路如下,注意括号的存在:

=(条件1)*(条件2)*(条件n)

……

现在,我们把优秀生规则拨回到最初的时候,只要语文或数学有一门成绩达到90分以上,就可以被评为优秀生。

OR函数是这样的:

=IF(OR(B2>90,C2>90),'优秀','')

我们可以用加法来代替OR:

=IF((B2>90)+(C2>90),'优秀','')

加法又怎么理解呢?

很简单。在所有表达式里,只要有一个是真的,就会返回TRUE。而TRUE在数学运算中作为1,1加上任何一个大于等于0的数值,结果必然大于0,所以即便剩下的表达式全部不成立,全部返回FALSE,你手上的筹码也会大于0。而在逻辑判断中,所有非0的数值均被视为真,也就是条件成立。

什么时候条件不成立呢?所有的表达式全部返回FALSE的时候呗,无数个0相加,结果还是0,在逻辑判断中,0被视为假,所以条件也就不成立了。

这不就是一个赤果果的“或”关系的概念吗?

因此OR函数完全可以被加法代替。总结套路如下,注意括号的存在:

=(条件1)+(条件2)+(条件n)

……

也许你会想,即便AND函数能用乘法表示,OR函数能用加法表示,也不至于你们就把AND和OR淘汰了啊?我也没看出来乘法就比AND函数简便多少?

你最帅,但你说的不对。

AND和OR能干的事,乘法和加法都能做。但乘法和加法能做的事,前者就不一定做的来了。

乘法、加法相比于AND、OR的优势,一方面在于当有多层复杂逻辑值嵌套时,编写会更简洁清晰,另一方面,最主要的是它俩支持数组运算,能够在内存中返回多个可用于逻辑判断的数值,而AND和OR只能返回一个值。

这一点我们会放到数组章节再细说。也许可以先举个浅尝则止的栗子?——是的,我们家什么都不多,就是栗子多。

          

如上图所示,B列是性别,C是列年龄,D列是工资。我们现在需要计算性别为男、年龄大于30的人,工资总计是多少?

这是一个并且关系的逻辑问题,需要性别和年龄两个条件同时成立,如果使用SUMPRODUCT函数,我们会把公式写为:

=SUMPRODUCT((B2:B7='男')*(C2:C7>30)*D2:D7)

但我们不能把该公式写成:

=SUMPRODUCT(AND(B2:B7='男',C2:C7>30)*D2:D7)

(B2:B7='男')*(C2:C7>30)返回的是一组值,比如此例中返回内存数组:{1;1;0;1;0;0}

而AND(B2:B7='男',C2:C7>30)只返回一个值,比如此例中,即返回FALSE。

好了,今天的内容就是这样吧,下期再见~

图文制作:看见星光

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
为什么说0和1是Excel函数逻辑运算的核心要义?
使用逻辑函数判断产品是否合格
Excel逻辑潜规则,少有人知,看过你就是知者第一人
Excel逻辑函数
07多层嵌套IF函数如何简化?
非黑即白的世界观-IF函数!
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服