|
有朋友认为IF函数不需要简化，应该一层层堆积下去，这样计算逻辑才清楚，也方便后期修改与维护。 甚至有朋友认为里面的VLOOKUP/LOOKUP等解法属于奇巧淫技，毫无存在的意义。 你们要是都持有这样的观点那我可就不困了。 无论是Excel还是其它软件的数据分析，逻辑压缩与简化是再正常不过的事情；而IF函数虽然说是条件判断函数，但条件判断和条件查询本来就是一丘之貉……咳，是一家人。当IF函数嵌套层次超过3层后，被其它查询函数（VLOOKUP/LOOKUP等）代替——简直理所应当。摊手，看我小眼睛，骗你娶你不论男女。 我举3个常见的小栗子。 每当Office二级考试来临的那段时间，总有学生跑来问我下面这样类似的问题。
如上图所示，假设有位老师需要对班级学生的成绩进行评分，其中60分以下不及格，60~69分之间及格，70~79分良好，80~89分优秀，90~100优异，如何用公式对如图所示的表格数据进行评分？
这问题很多人会立刻想到使用IF函数去处理：=IF(B2>90,"优异",IF(B2>80,"优秀",IF(B2>70,"良好",IF(B2>=60,"及格","不及格")))) =LOOKUP(B2,{0;60;70;80;90},{"不及格";"及格";"良好";"优秀";"优异"}) LOOKUP查询范围升序排列，查找小于或等于查找值的最大值。比如查找89分，在常量数组{0,60,70,80,90}中，小于等于89的最大值是80，于是返回80所对应的结果优秀。
如果你所使用的Excel是2019版或MS365，还可以使用下IFS函数，但依然没有LOOKUP简洁。=IFS(B2<60,"不及格",B2<70,"及格",B2<80,"良好",B2<90,"优秀",B2>=90,"优异") LOOKUP函数还可以将参数表格化，公式更简洁，对计算标准的修改与维护也更方便。第2个例子和第一个相似却又不同。
如下图所示，如果B列的数据等于福清/厦门/泉州之一，则返回福建；如果等于枣庄/济南/青岛之一，则返回山东；如果等于徐州/南京之一，则返回江苏。你看，虽然这是一个条件判断问题，但也是一个标准的条件查询问题，所以……试试条件查询大神VLOOKUP函数吧。
如上图所示，在E:F列构建参数表，E列是市，F列是对应的省份。C2单元格只需要输入以下公式，并复制填充至数据表的最后一行，即可获取结果。
=VLOOKUP(B2,$E$2:$F$9,2,0)
如果你觉得参数表是拖油瓶，不想带它玩，可以在编辑栏选中VLOOKUP第2参数按F9键，一键将其转换为常量数组，然后再删除参数表即可。
第3个例子说来和第1个例子还是有点点像……请看图：A:D是数据源，需要根据B列的性别和C列的体重，在D列进行评级。如果性别为男，则0~13.6为低体重，13.7~18.4正常，18.5~20.4超重，20.5以上肥胖。如果性别为女，则0~13.4低体重，13.5~17.8正常，17.9~20.2超重，20.3以上肥胖。
嗯，IF函数……大概是这样的…… =IF(B2="男",IF(C2<13.6,"低体重",IF(C2<18.4,"正常",IF(C2<20.4,"超重","肥胖"))),IF(C2<13.4,"低体重",IF(C2<17.8,"正常",IF(C2<20.2,"超重","肥胖"))))
这还只是一个性别维度，如果再增加年级/年龄等维度呢？听说公式写的越长越复杂水平越流弊？呵呵哒，年轻真好……=LOOKUP(1,0/((F$2:F$9=B2)*(G$2:G$9<=C2)*(H$2:H$9>=C2)),$I$2:$I$9) 
如上图所示，在F:I列构建参数表。F列是姓名，G:H列是起、止区间，I列是对应等级。LOOKUP(1,0/查询条件,查询结果)是函数中经典的多条件查询套路。(F$2:F$9=B2)*(G$2:G$9<=C2)*(H$2:H$9>=C2)为查询条件，当B2的性别等于F列，同时C2的体重大于等于G列起始值且小于等于H列的终止值时，则返回对应的等级。
|
