条件函数是最重要的一类函数,大部分朋友对IF都很熟悉,但是这也导致了IF函数的滥用。除了IF函数,还有IFS,SWITCH,还有一个CHOOSE函数,甚至VLOOKUP等查找函数都可以当作条件函数。条件判断有三种不同的形式,每一种都有相应的技巧代替传统的IF函数。 除了最简单的IF函数之外,最常用的也是很多人比较头疼的就是IF嵌套了。 比如,下面的分段条件: 要根据成绩返回每个人的等级,可以使用公式:
如果觉得这个公式可读性不强,可以采用下面的分行写法: =IF( C3 >= 90, "A", IF( C3 >= 80, "B", IF( C3 >= 70, "C", IF( C3>=60, "D", "E" ) ) ) ) 当然,我们从来不建议使用过多的嵌套IF,因为很容易就漏掉一个条件,或者写错了一个条件的位置。 如果非得写的话,可以先画一张图: 每一个黄线代表一个条件,左下方的“grade>=90, "A",就是第一重IF的条件和第一个参数,后面的就是第二重IF的条件和第一个参数,依此类推。 当然,最好还是尽量简化这个函数的逻辑。 IFS可以用来简化IF,上面的IF公式可以写成: =IFS( grade >= 90, "A", grade >= 80, "B", grade >= 70, "C", grade >= 60, "D", TRUE, "E" ) 逻辑清楚多了。 对于这种分段条件,最好的简化方式就是用VLOOKUP, =VLOOKUP(C3,{0,"E";60,"D";70,"C";80,"B";90,"A"},2,1) 这里,第二个参数: {0,"E";60,"D";70,"C";80,"B";90,"A"} 代表了一个两列的数组,第一列是每个等级的起始分数,第二列是对应的级别,相当于下面的区域: 关于VLOOKUP函数的近似匹配,请参见本公众号相应文章和视频。 有条件的可以使用XLOOKUP,更加简单: =XLOOKUP(C3,{60,70,80,90,100},{"E","D","C","B","A"},,1) 一般的条件可以分为两类:
要根据等级给出对应的成绩范围,可以使用公式: =LET( level, C3:C10, IF( level = "A", "90 ~ 100", IF( level = "B", "80 ~ 90", IF( level = "C", "70 ~ 80", IF( level = "D", "60 ~ 70", "0 ~ 60" ) ) ) ) ) 这里用到了LET函数,主要是为了可读性。如果你用不来这个函数,主要关注其中的IF就好了。 实际上,这是一种IF的滥用。我们可以用很多方法简化。 最直接的,实际上这是标准的查找匹配场景,可以使用VLOOKUP: =LET( grade_def, {"A","90 ~ 100";"B","80 ~ 90";"C","70 ~ 80";"D","60 ~ 70";"E","0 ~ 60"}, level, C3:C10, VLOOKUP(level, grade_def, 2, 0) ) 同样,只要关注其中的VLOOKUP函数即可。 有条件的话,可以使用SWTICH函数: =LET( level, C3:C10, SWITCH( level, "A", "90 ~ 100", "B", "80 ~ 90", "C", "70 ~ 80", "D", "60 ~ 70", "E", "0 ~ 60" ) ) 跟SWTICH函数类似的还有一个CHOOSE函数,当要判断的条件取值为1,2,3,...等自然数时,可以使用这个函数,在MAKEARRAY函数中,经常会这么使用: =LET( data, B3:C10, MAKEARRAY(ROWS(data), 2, LAMBDA(r,c, CHOOSE( c, INDEX(data, r, 2), INDEX(data, r, 1) ) ) ) ) 除了分段类型的条件和等于类型的条件外,别的条件都可以归类为其他类型的条件。 如果直接使用“其他类型”的条件,你可能只能使用IF函数。但是,我们可以将其他类型的条件转换为等于类型的条件,实际上分段类型的条件也可以转换为等于类型的条件。 比如,还是根据成绩分段返回对应等级, 可以使用下面的公式: =CHOOSE(SUM(--(C3>{60,70,80,90}))+1, "E", "D", "C", "B", "A" ) 这里,我们判断C3是否大于60,70,80,90,得到的是一个数组(以C3=86分为例): {TRUE, TRUE, TRUE, FALSE} 然后SUM函数得到的结果是3,加1是4,因此CHOOSE公式返回B。 还可以使用下面的公式: LET( grade, C3:C10, condition, ( grade >= 90) + (grade >= 80) + (grade >= 70) + (grade>= 60), SWITCH(condition, 0, "E", 1, "D", 2, "C", 3, "B", 4, "A" ) ) 这个公式的主体是SWITCH,但是之前还是用condition进行了条件转换,就是判断给定的分数满足几个分段,如果满足4个分段,就返回“A”,如果满足0个分段,就返回“E”。这个逻辑在下图中就很明白的表示了出来: 实际上,condition就是蓝线与黄线相交的交点个数。 还有一种非常典型的转换方式: =LET( grade, C3:C10, SWITCH( TRUE, grade >= 90, "A", grade >= 80, "B", grade >= 70, "C", grade >= 60, "D", "E" ) ) 其中的SWITCH将第一参数设为常量:TRUE,不再是大家习惯的变量grade,返回将grade放在每一个分支中。这个方法可以解决大部分的IF转换为SWITCH的场景。 详细解释请看视频 |
|
来自: zonge > 《ExcelEasy》