首页 > Excel专区 > Excel函数 >

朋友,这些问题就别再用IF函数了……

Excel函数 2023-01-13

如果谈起Excel里最先被大家所认识和熟悉的函数,大概也就是IF、SUM和VLOOKUP这三家伙了,其中IF函数作为条件判断函数,简单又实用,不但职场常用,也是Office等级考试必考。

在工作和学习中,也许有很多表格问题你已习惯了使用IF函数,但有时候IF函数并不是最适用的,特别是嵌套多层的情况下,比如=if(if(if(if),if(),if()))),自己都能把自己绕晕了不是?坦白的说,当嵌套层次超过3层,If函数就应该被其它函数替代了。

跟我来,给您表演举几个例子……

案例1

连续区间判断

每当Office二级考试来临的那段时间,总有很多学生跑来问星光下面这样类似的问题。

如上图所示,假设有位老师需要对班级学生的成绩进行评分,其中60分以下不及格,60~69分之间及格,70~79分良好,80~89分优秀,90~100优异,如何用公式对如图所示的表格数据进行评分?

这问题很多人会立刻想到使用IF函数去处理:

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

……其实……这类问题更适合LOOKUP函数:

=LOOKUP(B2,{0,60,70,80,90},{“不及格”;”及格”;”良好”;”优秀”;”优异”})

LOOKUP查询范围升序排列,查找小于或等于查找值的最大值。比如查找89分,在常量数组{0,60,70,80,90}中,小于等于89的最大值是80,于是返回80所对应的结果优秀。

如果你所使用的Excel是2019版或O365,还可以使用下IFS函数,但依然没有LOOKUP简洁。

=IFS(B2<60,”不及格”,B2<70,”及格”,B2<80,”良好”,B2<90,”优秀”,b2>=90,”优异”)

案例2

多值匹配判断

这个例子和第一个相似却又不同。

如下图所示,如果A列的数据等于“看见星光”,就返回男孩,如果是“大红花”,就返回女孩,如果是“萧才人”就返回淑女,如果是“芬子”就返回乖乖女,如果是“随风”就返回小正太……

怎么?你还在想怎么使用IF函数?真是单纯的家伙。

你看,虽然这是一个条件判断问题,但也是一个条件查询问题呢,所以……试试条件查询大神VLOOKUP函数吧……

=VLOOKUP(A2,{“看见星光”,”男孩”;”大红花”,”女孩”;”芬子”,”乖乖女”;”随风”,”小正太”;”萧才人”,”淑女”},2,0)

如果你原意建立一个匹配表,公式会更加简单。

如上图所示,在D:E列编写匹配表,B2单元格只需要输入以下公式,并复制填充至数据表的最后一行,即可获取结果。

=VLOOKUP(A2,D:E,2,0)

是不是很简单?

案例3

非连续区间查询

第3个例子,说来和第1个例子还是有点像……请看图……

根据E:G列的计算规则,对B列的编号划分班级。例如0-100之间为1班,200-300为2班,500-600之间为3班……但如果编号不在规则范围内,例如102,则返回“界外”。

嗯,IF函数……大概是这样的……

=IF((B2>=E$2)*(B$2<=f$2),g$2,if((b2>=E$3)*(B$2<=f$3),g$3,if((b2>=E$4)*(B$2<=f$4),g$4,if((b2>=E$5)*(B$2<=f$5),g$5,if((b2>=E$6)*(B$2<=F$6),G$6,”界外”)))))

听说公式写的越长越复杂水平越流弊?呵呵哒,谁信谁年轻。

其实公式可以很简单的:

=IFERROR(LOOKUP(1,0/((B2>=E$2:E$6)*(B2<=F$2:F$6)),G$2:G$6),”界外”)

LOOKUP(1,0/查询条件,查询结果)是函数中经典的条件查询套路,常用于多条件查询;本例中当该公式查无结果时,使用IFERROR返回指定结果:界外。

(B2>=E$2:E$6)*(B2<=F$2:F$6)是条件,当查找值既大于等于E列的值,又小于等于F列的值时,说明它处在正确的区间内,会返回True,相反则返回逻辑值False

0/True等同0/1,结果返回0,0/False等同0/0,结果返回错误值。

LOOKUP忽略错误值,查找值1比查找范围内所有的0都大,因而返回最后一个0所对应的结果,也就是目标班级。

思考时间

第3个案例为什么不能直接使用例子1的LOOKUP模糊查询套路?


Copyright © 2016-2023 office学习教程网 office.tqzw.net.cn. All Rights Reserved.