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，因为很容易就漏掉一个条件，或者写错了一个条件的位置。

如果非得写的话，可以先画一张图：

每一个黄线代表一个条件，左下方的“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的场景。

详细解释请看视频