Excel函数轻松学05:IF函数和他亲密无间的战友们(上)

论坛 期权论坛 期权     
VBA编程学习与实践   2019-7-7 20:44   2782   0

         

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


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

本章概要
1,IF函数
        1.1 认识IF函数
        1.2 多层IF函数嵌套
2,IF的左右手
        2.1 “并且”关系和AND函数
        2.2 “或”关系和OR函数
        2.3 逻辑值能作为数值运算吗?数值能作为逻辑值运算吗?
        2.4 如何使用乘法代替AND函数
        2.5 如何使用加法代替OR函数
        2.6 乘法、加法相比AND、OR的优势是什么?


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这事是真的,我们就去找第二个参数,于是得到结果,及格。

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

脑筋急转弯,猜一猜,如下图所示的数据,这条IF函数会返回什么结果?

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



         



         


2,IF函数多层嵌套

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

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

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

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

=IF(B290,"优秀","及格"))

公式先计算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(6090),"优秀","")

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

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

另一种情况是B290),"优秀","")

加法又怎么理解呢?

很简单。在所有表达式里,只要有一个是真的,就会返回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

盖木欧瓦~下期再见啦~

本系列教程致力于从入门到精通,精细讲解Excel函数,帮你告别小白,告别碎片化学习,告别函数时灵时不灵的困惑,一步步构建完整的函数知识体系,并最终搭建以函数为主力军的模块化数据处理模版,使工作结构化、自动化,告别重复而枯燥性工作~


分享到 :
0 人收藏
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

积分:220
帖子:44
精华:0
期权论坛 期权论坛
发布
内容

下载期权论坛手机APP