跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

掌握 Excel 中的嵌套 IF语句——逐步指南

Author Siluvia Last modified

在 Excel 中，虽然 IF 函数对于基本的逻辑测试至关重要，但复杂的条件通常需要嵌套 IF语句来增强数据处理。在本综合指南中，我们将详细介绍嵌套 IF 的基础知识，从语法到实际应用，包括嵌套 IF 与 AND/OR 条件的组合。此外，我们还将分享如何提高嵌套 IF 函数的可读性以及一些关于嵌套 IF 的技巧，并探索强大的替代方案，如 VLOOKUP、IFS 等，以使复杂的逻辑操作更易于使用和更高效。

A screenshot of the nested IF statements in Excel

Excel IF 函数与嵌套 IF语句

Excel 中的 IF 函数和嵌套 IF语句用途相似，但在复杂性和应用上有显著差异。

IF 函数：IF 函数测试一个条件，如果条件为真则返回一个值，如果为假则返回另一个值。
  • 语法为:
    =IF (logical_test, [value_if_true], [value_if_false])
  • 限制：一次只能处理一个条件，因此不太适合需要评估多个标准的更复杂的决策场景。
嵌套 IF语句：嵌套 IF 函数，即一个 IF 函数嵌套在另一个 IF 函数中，允许您测试多个标准并增加可能的结果数量。
  • 语法为:
    =IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))
  • 复杂性：可以处理多个条件，但如果嵌套层数过多，可能会变得复杂且难以阅读。

嵌套 IF 的用法

本节演示了 Excel 中嵌套 IF语句的基本用法，包括语法、实际示例以及如何与 AND 或 OR 条件一起使用。

嵌套 IF 的语法

理解函数的语法是其在 Excel 中正确有效应用的基础。让我们从嵌套 IF语句的语法开始。

语法：

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

参数：

  • Condition1, Condition2, Condition3：这些是您要测试的条件。每个条件按顺序评估，从 Condition1 开始。
  • Result1：如果 Condition1 为 TRUE，则返回此值。
  • Result2：如果 Condition1 为 FALSE 且 Condition2 为 TRUE，则返回此值。重要的是要注意，只有当 Condition1 为 FALSE 时，Result2 才会被评估。
  • Result3：如果 Condition1 和 Condition2 都为 FALSE，而 Condition3 为 TRUE，则返回此值。基本上，要评估 Result3，前面的条件（Condition1 和 Condition2）必须都为 FALSE。
  • Result4：如果所有条件（Condition1、Condition2 和 Condition3）都为 FALSE，则返回此结果。
    简而言之，这个表达式可以解释为：
    测试 condition1，如果为 TRUE，返回 result1，如果为 FALSE，测试 condition2，如果为 TRUE，返回 result2，如果为 FALSE，测试 condition3，如果为 TRUE，返回 result3，如果为 FALSE，返回 result4

请记住，在嵌套 IF结构中，只有当所有先前条件都为 FALSE 时，才会评估每个后续条件。这种顺序检查对于理解嵌套 IF 的工作原理至关重要。

嵌套 IF 的实际示例

现在，让我们通过两个实际示例深入了解嵌套 IF 的使用。

示例1：评分系统

如下面的截图所示，假设您有一个学生分数列表，并希望根据这些分数分配等级。您可以使用嵌套 IF 来完成此任务。

备注：评分等级及其对应的分数范围列在区域 E2:F6 中。

A screenshot showing the grading system example for nested IF statements in Excel

选择一个空白单元格（在本例中为 C2），输入以下公式并按 Enter 键获取结果。然后拖动填充柄向下以获取其余结果。

=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))
备注：
  • 您可以直接在公式中指定等级，因此公式可以更改为：
    =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
  • 此公式用于根据单元格 A2 中的分数分配等级（A、B、C、D 或 F），使用标准评分阈值。这是嵌套 IF语句在学术评分系统中的典型用例。
  • 公式解释：
    1. A2>=90：这是公式检查的第一个条件。如果单元格 A2 中的分数大于或等于90，公式返回 "A"。
    2. A2>=80：如果第一个条件为假（分数小于90），则检查 A2 是否大于或等于80。如果为真，则返回 "B"。
    3. A2>=70：同样，如果分数小于80，则检查其是否大于或等于70。如果为真，则返回 "C"。
    4. A2>=60：如果分数小于70，公式检查其是否大于或等于60。如果为真，则返回 "D"。
    5. "F"：最后，如果上述条件都不满足（即分数小于60），公式返回 "F"。
示例2：销售佣金计算

想象一个场景，销售代表根据其销售业绩获得不同的佣金率。如下面的截图所示，您希望根据这些不同的销售阈值计算销售人员的佣金，嵌套 IF语句可以帮助您实现这一点。

备注：佣金率及其对应的销售范围列在区域 E2:F4 中。
  • 第1 层（$20,000+）：20%
  • 第2 层（$10,000-$19,999）：15%
  • 第3 层（<$10,000）：10%

A screenshot showing the sales commission calculation example for nested IF statements in Excel

选择一个空白单元格（在本例中为 C2），输入以下公式并按 Enter 键获取结果。然后拖动填充柄向下以获取其余结果。

=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))

A screenshot displaying the results of the sales commission calculation using nested IF formulas

备注：
  • 您可以直接在公式中指定佣金率，因此公式可以更改为：
    =B2*IF(B2>20000, 20%, IF(B2>=10000, 15%, 10%))
  • 提供的公式用于根据销售人员的销售额计算其佣金，为不同的销售阈值应用不同的佣金率。
  • 公式解释：
    1. B2：这代表销售人员的销售额，用作计算佣金的基础。
    2. IF(B2>20000, "20%", ...)：这是检查的第一个条件。它检查 B2 中的销售额是否大于20,000。如果是，公式使用20% 的佣金率。
    3. IF(B2>=10000, "15%", "10%")：如果第一个条件为假（销售额不大于20,000），公式检查销售额是否等于或超过10,000。如果为真，则应用15% 的佣金率。如果销售额小于10,000，公式默认为10% 的佣金率。

带有 AND / OR 条件的嵌套 IF

在本节中，我修改了上述第一个示例“评分系统”，以演示如何在 Excel 中结合嵌套 IF 与 AND 或 OR 条件。在修订的评分示例中，我引入了基于“出勤率”的附加条件。

A screenshot illustrating the grading example with attendance criteria in Excel

使用带有 AND 条件的嵌套 IF

如果学生同时满足分数和出勤标准，他们将获得等级提升。例如，分数为60 或更高且出勤率为95% 或以上的学生，其等级将提升一级，例如从 A 提升到 A+，B 提升到 B+，依此类推。但是，如果出勤率低于95%，评分将遵循原始的基于分数的标准。在这种情况下，我们需要使用带有 AND 条件的嵌套 IF语句。

选择一个空白单元格（在本例中为 D2），输入以下公式并按 Enter 键获取结果。然后拖动填充柄向下以获取其余结果。

=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

A screenshot showing nested IF with AND condition for grading in Excel

备注：以下是该公式如何工作的解释：
  1. AND 条件检查:
    AND(B2>=60, C2>=95%)：AND 条件首先检查两个条件是否都满足——学生的分数为60 或更高，且出勤率为95% 或以上。
  2. 新等级分配:
    IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+")))：如果 AND语句中的两个条件都为真，公式然后检查学生的分数并将其等级提高一级。
    • B2>=90：如果分数为90 或以上，等级为 "A+"。新等级分配：
    • B2>=80：如果分数为80 或以上（但小于90），等级为 "B+"。
    • B2>=70：如果分数为70 或以上（但小于80），等级为 "C+"。
    • B2>=60：如果分数为60 或以上（但小于70），等级为 "D+"。
  3. 常规等级分配:
    IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))：如果 AND 条件不满足（分数低于80 或出勤率低于95%），公式分配标准等级。
    • B2>=90：分数90 或以上获得 "A"。
    • B2>=80：分数80 或以上（但小于90）获得 "B"。
    • B2>=70：分数70 或以上（但小于80）获得 "C"。
    • B2>=60：分数60 或以上（但小于70）获得 "D"。
    • 分数低于60 获得 "F"。
使用带有 OR 条件的嵌套 IF

在这种情况下，如果学生的分数为95 或以上，或者出勤率为95% 或以上，他们的等级将提高一级。以下是如何使用嵌套 IF 和 OR 条件实现这一目标。

选择一个空白单元格（在本例中为 D2），输入以下公式并按 Enter 键获取结果。然后拖动填充柄向下以获取其余结果。

=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

A screenshot showing nested IF with OR condition for grading in Excel

备注：以下是该公式如何工作的分解：
  1. OR 条件检查：
    OR(B2>=95, C2>=95%)：公式首先检查任一条件是否为真——学生的分数为95 或以上，或者出勤率为95% 或以上。
  2. 带有奖励的等级分配：
    IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+"))))：如果 OR语句中的任一条件为真，学生的等级将提高一级。
    • B2>=90：如果分数为90 或以上，等级为 "A+"。
    • B2>=80：如果分数为80 或以上（但小于90），等级为 "B+"。
    • B2>=70：如果分数为70 或以上（但小于80），等级为 "C+"。
    • B2>=60：如果分数为60 或以上（但小于70），等级为 "D+"。
    • 否则，等级为 "F+"。
  3. 常规等级分配:
    IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))：如果 OR 条件均不满足（分数低于95 且出勤率低于95%），公式分配标准等级。
    • B2>=90：分数90 或以上获得 "A"。
    • B2>=80：分数80 或以上（但小于90）获得 "B"。
    • B2>=70：分数70 或以上（但小于80）获得 "C"。
    • B2>=60：分数60 或以上（但小于70）获得 "D"。
    • 分数低于60 获得 "F"。

嵌套 IF 的提示和技巧

本节介绍了四个关于嵌套 IF 的有用提示和技巧。

使嵌套 IF 易于阅读

一个典型的嵌套 IF语句可能看起来紧凑，但可能难以解读。

在以下公式中，尤其是随着复杂性的增加，很难快速识别一个条件结束和另一个条件开始的位置。

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))
解决方案：添加换行符和缩进

为了使嵌套 IF 易于阅读，您可以将公式分成多行，每个嵌套 IF 在新行上。在公式中，只需将光标放在 IF 前面，然后按 Alt + Enter 键。

在拆分上述公式后，它将显示如下：

=IF(A2>=90, "A",
      IF(A2>=80, "B",
          IF(A2>=70, "C",
              IF(A2>=60, "D", "F")))
)

这种格式使得每个条件及其对应的输出位置更加清晰，增强了公式的可读性。

嵌套 IF 函数的顺序

嵌套 IF公式中逻辑条件的顺序至关重要，因为它决定了 Excel 如何评估这些条件，从而影响公式的最终结果。

正确的公式

在评分系统示例中，我们使用以下公式根据分数分配等级。

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

A screenshot displaying the correct order of conditions in a nested IF formula for grading

Excel 按顺序评估嵌套 IF公式中的条件，从第一个条件到最后一个。此公式首先检查最高分数阈值（>=90 为 "A"），然后移动到较低的阈值。它确保分数与其符合的最高等级进行比较。如果第一个条件为真（A2>=90），则返回 "A"，并且不再评估任何其他条件。

顺序错误的公式

如果条件的顺序颠倒，从最低阈值开始，则会返回错误的结果。

=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))

A screenshot illustrating incorrect ordering in a nested IF formula

在此错误公式中，分数95 将立即满足第一个条件 B2>=60，并被错误地分配为等级 "D"。

数字和文本应区别对待

本节将向您展示在嵌套 IF语句中数字和文本的不同处理方式。

数字

数字用于算术比较和计算。在嵌套 IF语句中，您可以直接使用运算符如 >, <, =, >= 和 <= 来比较数字。

文本

在嵌套 IF语句中，文本应放在双引号中。请参见以下公式中的 A、B、C、D 和 F：

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

嵌套 IF 的限制

本节列出了嵌套 IF 的几个限制和缺点。

复杂性和可读性：

尽管 Excel允许您嵌套多达64 个不同的 IF 函数，但这样做并不明智。嵌套层数越多，公式就越复杂。这可能导致公式难以阅读、理解和维护。

易出错：

此外，复杂的嵌套 IF语句可能容易出错，并且难以调试或修改。

难以扩展或缩放：

如果您的逻辑发生变化或需要添加更多条件，深度嵌套的 IF可能难以修改或扩展。

了解这些限制是有效使用 Excel 中嵌套 IF语句的关键。通常，将嵌套 IF 与其他函数结合使用或寻找替代方法可以带来更高效和可维护的解决方案。

嵌套 IF 的替代方案

本节列出了 Excel 中可以用作嵌套 IF语句替代方案的几个函数。

使用 VLOOKUP

您可以使用 VLOOKUP 函数代替嵌套 IF语句来完成上述两个实际示例。以下是如何实现的：

示例1：使用 VLOOKUP 的评分系统

在这里，我将展示如何使用 VLOOKUP 根据分数分配等级。

步骤1：创建一个评分查找表

首先，您需要为分数范围和相应的等级创建一个查找表（例如在本例中为 E1:F6）。备注： 表格第一列的分数必须按升序排序。

A screenshot showing a lookup table for grades to use with VLOOKUP in Excel

步骤2：应用 VLOOKUP 函数分配等级

选择一个空白单元格（在本例中为 C2），输入以下公式并按 Enter 键获取第一个等级。选择此公式单元格并拖动其填充柄向下以获取其余等级。

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

A screenshot demonstrating the use of VLOOKUP for grading in Excel

备注：
  • 单元格 B2 中的值95 是 VLOOKUP 在查找表的第一列（$E$2:$F$6）中搜索的内容。如果找到，它将返回表中与匹配值位于同一行的第二列中的相应等级。
  • 请记住将查找表引用设为绝对引用（在引用前添加美元符号 ($)），这意味着如果公式被复制到另一个单元格，引用将不会改变。
  • 要了解有关 VLOOKUP 函数的更多信息，请访问此页面。
示例2：使用 VLOOKUP 的销售佣金计算

您也可以使用 VLOOKUP 来完成 Excel 中的销售佣金计算。请按以下步骤操作。

步骤1：创建一个佣金查找表

首先，您需要为销售额和相应的佣金率创建一个查找表，例如在本例中为 E2:F4。备注：表格第一列的销售额必须按升序排序。

A screenshot showing a lookup table for sales commission rates to use with VLOOKUP in Excel

步骤2：应用 VLOOKUP 函数分配等级

选择一个空白单元格（在本例中为 C2），输入以下公式并按 Enter 键获取第一个佣金。选择此公式单元格并拖动其填充柄向下以获取其余结果。

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

A screenshot demonstrating the use of VLOOKUP for sales commission calculation in Excel

备注：
  • 在这两个示例中，VLOOKUP 用于根据查找值（分数或销售额）在表中查找值，并从指定列（等级或佣金率）中返回同一行的值。第四个参数 TRUE 表示近似匹配，这适用于这些场景，其中查找表中可能不存在确切的查找值。
  • 要了解有关 VLOOKUP 函数的更多信息，请访问此页面。

使用 IFS

IFS 函数通过消除嵌套的需要简化了过程，使公式更易于阅读和管理。它增强了可读性并简化了多个条件检查的处理。要使用 IFS 函数，请确保您使用的是 Excel2019 或更高版本，或拥有 Office365订阅。让我们看看它如何应用于实际示例。

示例1：使用 IFS 的评分系统

假设与之前相同的评分标准，可以按以下方式使用 IFS 函数：

选择一个空白单元格，例如 C2，输入以下公式并按 Enter 键获取第一个结果。选择此结果单元格并拖动其填充柄向下以获取其余结果。

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")

A screenshot showing the use of the IFS function for grading in Excel

备注：
  • 每个条件按顺序评估。一旦满足某个条件，其对应的结果就会返回，公式停止检查进一步的条件。在这种情况下，公式用于根据 B2 中的分数分配等级，遵循典型的评分标准，其中更高的分数对应于更好的等级。
  • 要了解有关 IFS 函数的更多信息，请访问此页面。
示例2：使用 IFS 的销售佣金计算

对于销售佣金计算场景，IFS 函数的应用如下：

选择一个空白单元格，例如 C2，输入以下公式并按 Enter 键获取第一个结果。选择此结果单元格并拖动其填充柄向下以获取其余结果。

=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)

A screenshot showing the use of the IFS function for sales commission calculation in Excel

使用 CHOOSE 和 MATCH

与嵌套 IF语句相比，CHOOSE 和 MATCH 方法可以更高效且更易于管理。这种方法简化了公式，使更新或更改更加直接。下面我将演示如何使用 CHOOSE 和 MATCH 函数的组合来处理本文中的两个实际示例。

示例1：使用 CHOOSE 和 MATCH 的评分系统

您可以使用 CHOOSE 和 MATCH 函数的组合根据不同的分数分配等级。

步骤1：创建一个包含搜索值的查找数组

首先，您需要创建一个包含 MATCH 将搜索的阈值的单元格范围，例如在本例中为 $E$2:$E$6。备注： 此范围内的数字必须按升序排序，以便 MATCH 函数在使用近似匹配类型时正确工作。

A screenshot showing a lookup array for grades using CHOOSE and MATCH in Excel

步骤2：应用 CHOOSE 和 MATCH 分配等级

选择一个空白单元格（在本例中为 C2），输入以下公式并按 Enter 键获取第一个等级。选择此公式单元格并拖动其填充柄向下以获取其余结果。

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

A screenshot demonstrating CHOOSE and MATCH for grading in Excel

备注：
  • MATCH(B2, $E$2:$E$6,1)：公式的这一部分在范围 $E$2:$E$6 中查找单元格 B2 中的分数（95）。1 表示 MATCH 应该找到一个近似匹配，这意味着它找到范围中小于或等于 B2 的最大值。
  • CHOOSE(..., "F", "D", "C", "B", "A")：根据 MATCH 函数返回的位置，CHOOSE选择相应的等级。
  • 要了解有关 MATCH 函数的更多信息，请访问此页面。
  • 要了解有关 CHOOSE 函数的更多信息，请访问此页面。
示例2：使用 IFS 的销售佣金计算

使用 CHOOSE 和 MATCH组合进行销售佣金计算也可以是有效的，特别是当佣金率基于指定的销售阈值时。让我们看看如何操作。

步骤1：创建一个包含搜索值的查找数组

首先，您需要创建一个包含 MATCH 将搜索的阈值的单元格范围，例如在本例中为 $E$2:$E$4。备注： 此范围内的数字必须按升序排序，以便 MATCH 函数在使用近似匹配类型时正确工作。

A screenshot showing a lookup array for sales commission rates using CHOOSE and MATCH in Excel

步骤2：应用 CHOOSE 和 MATCH 获取结果

选择一个空白单元格（在本例中为 C2），输入以下公式并按 Enter 键获取第一个等级。选择此公式单元格并拖动其填充柄向下以获取其余结果。

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

A screenshot demonstrating CHOOSE and MATCH for sales commission calculation in Excel

备注：

总之，掌握 Excel 中的嵌套 IF语句是一项有价值的技能，可以增强您在数据分析和决策过程中处理复杂逻辑场景的能力。虽然嵌套 IF 对于复杂的逻辑操作非常强大，但重要的是要注意它们的限制。在某些情况下，像 VLOOKUP、IFS 和 CHOOSE 与 MATCH这样的简单替代方案可以提供更简化的解决方案。凭借这些见解，您现在可以自信地将最合适的 Excel 技术应用于您的数据分析任务，确保电子表格的清晰性、准确性和效率。对于那些渴望深入了解 Excel 功能的人，我们的网站拥有丰富的教程。在这里发现更多 Excel 技巧和窍门。

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件VLookup|多值VLookup|多表查找|模糊查找……
高级下拉列表：快速创建下拉列表 |依赖下拉列表 | 多选下拉列表……
列管理器： 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域与列……
特色功能：网格聚焦 |设计视图 | 增强编辑栏 | 工作簿及工作表管理器 | 资源库（自动文本） | 日期提取 | 合并数据 | 加密/解密单元格 | 按名单发送电子邮件 | 超级筛选 | 特殊筛选（筛选粗体/倾斜/删除线等）……
15大工具集：12项 文本工具（添加文本、删除特定字符等）|50+种 图表 类型（甘特图等）|40+实用 公式（基于生日计算年龄等）|19项 插入工具（插入二维码、从路径插入图片等）|12项 转换工具（小写金额转大写、汇率转换等）|7项 合并与分割工具（高级合并行、分割单元格等）| ……
Kutools支持多种语言——可选择英语、西班牙语、德语、法语、中文等40多种语言！

通过Kutools for Excel提升您的Excel技能，体验前所未有的高效办公。 Kutools for Excel提供300多项高级功能，助您提升效率并节省时间。 点击此处获取您最需要的功能……

Office Tab为Office带来多标签界面，让您的工作更加轻松

  • 支持在Word、Excel、PowerPoint中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠