分享

Excel中的条件函数们:IF函数和它的同伴们

 zonge 2025-02-14 发布于湖北

条件函数是最重要的一类函数,大部分朋友对IF都很熟悉,但是这也导致了IF函数的滥用。除了IF函数,还有IFS,SWITCH,还有一个CHOOSE函数,甚至VLOOKUP等查找函数都可以当作条件函数。条件判断有三种不同的形式,每一种都有相应的技巧代替传统的IF函数。

IF函数

除了最简单的IF函数之外,最常用的也是很多人比较头疼的就是IF嵌套了。

比如,下面的分段条件:

图片

要根据成绩返回每个人的等级,可以使用公式:

  • =IF(C3>=90,"A",IF(C3>=80,"B",IF(C3>=70,"C",IF(C3>=60,"D","E"))))

如果觉得这个公式可读性不强,可以采用下面的分行写法:

=IF(        
     C3 >= 90,        
     "A",        
     IF(            
        C3 >= 80,            
        "B",            
        IF(                
           C3 >= 70,                
           "C",                
           IF(                    
              C3>=60,                    
              "D",                    
              "E"                
              )             
           )        
        )    
     )

当然,我们从来不建议使用过多的嵌套IF,因为很容易就漏掉一个条件,或者写错了一个条件的位置。

如果非得写的话,可以先画一张图:

图片

每一个黄线代表一个条件,左下方的“grade>=90, "A",就是第一重IF的条件和第一个参数,后面的就是第二重IF的条件和第一个参数,依此类推。

当然,最好还是尽量简化这个函数的逻辑。

IFS

IFS可以用来简化IF,上面的IF公式可以写成:

=IFS(        
      grade >= 90, "A",        
      grade >= 80, "B",        
      grade >= 70, "C",        
      grade >= 60, "D",        
      TRUE, "E"    
    )

逻辑清楚多了。

VLOOKUP/XLOOKUP

对于这种分段条件,最好的简化方式就是用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)
基本IF条件判断公式的简化

一般的条件可以分为两类:

  • 分段类型的条件
    上面的案例就是很简单的分段类型的条件,对这种条件最好的办法就是用VLOOKUP或者XLOOKUP,此时,这些查找函数的作用就是条件判断。

  • 等于类型的条件
    这种条件是最常见的,比如:

图片

要根据等级给出对应的成绩范围,可以使用公式:

=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的场景。


详细解释请看视频

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多