跳到主要内容

掌握 Excel 中的嵌套 IF 语句 - 分步指南

添加一名作者 最后修改时间:2024-02-01

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


Excel IF 函数与嵌套 IF 语句

Excel 中的 IF 函数和嵌套 IF 语句具有相似的用途,但其复杂性和应用程序存在显着差异。

如果函数: IF 函数测试一个条件,如果条件为 true,则返回一个值;如果条件为 false,则返回另一个值。
  • 语法是:
    =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: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
  • Result1: This is the value returned if Condition1 is TRUE.
  • Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
  • Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
  • Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
    In short, this expression can be interpreted as follows:
    Test condition1, if TRUE, return result1, if FALSE,
    test condition2, if TRUE, return result2, if FALSE,
    test condition3, if TRUE, return result3, if FALSE,
    return result4

请记住,在嵌套 IF 结构中,仅当前面的所有条件均为 FALSE 时,才会计算每个后续条件。这种顺序检查对于理解嵌套 IF 的工作原理至关重要。


嵌套 IF 的实际例子

现在,让我们通过两个实际示例深入探讨嵌套 IF 的使用。

示例 1:评分系统

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

备注:分级级别及其相应的分数范围列在 E2:F6 范围内。

选择一个空白单元格(本例中为 C2),输入以下公式并按 输入 得到结果。然后拖动 填充手柄 下来得到其余的结果。

=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。如果为 true,则返回“C”。
    4. A2>=60:如果分数小于 70,则公式检查它是否大于或等于 60。如果为 true,则返回“D”。
    5. "F":最后,如果以上条件都不满足(即分数小于 60),则公式返回“F”。
示例2:销售佣金计算

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

备注:佣金率及其对应的销售范围列在范围 E2:F4 中。
  • 销售额超过 20 美元为 20,000%
  • 销售额在 15 美元至 10,000 美元之间为 20,000%
  • 销售额低于 10 美元为 10,000%

选择一个空白单元格(本例中为 C2),输入以下公式并按 输入 得到结果。然后拖动 填充手柄 下来得到其余的结果。

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

:
  • 您可以直接在公式中指定佣金率,因此公式可以改为:
    =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%的佣金率。

嵌套 if 与 AND / OR 条件

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

使用嵌套 if 和 A​​ND 条件

如果学生同时满足分数和出勤标准,他们将获得成绩提升。例如,成绩达到60分以上且出勤率达到95%以上的学生,其成绩将提升一级,如从A升到A+、B升到B+等。但如果出勤率低于95%,则按照原来的分数标准进行评分。在这种情况下,我们需要使用带有 AND 条件的嵌套 IF 语句。

选择一个空白单元格(本例中为 D2),输入以下公式并按 输入 得到结果。然后拖动 填充手柄 下来得到其余的结果。

=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")))))

:这是该公式如何工作的解释:
  1. 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”。
使用嵌套 if 和 OR 条件

在这种情况下,如果学生的成绩达到 95 分或以上,或者出勤率达到 95% 或以上,则其成绩将提高一级。以下是我们如何使用嵌套 IF 和 OR 条件来完成它。

选择一个空白单元格(本例中为 D2),输入以下公式并按 输入 得到结果。然后拖动 填充手柄 下来得到其余的结果。

=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")))))

:以下是该公式的工作原理的详细说明:
  1. 或条件检查:
    或(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"))))

Excel 按顺序计算嵌套 IF 公式中的条件(从第一个条件到最后一个条件)。此公式首先检查最高分数阈值(“A”>=90),然后移至较低阈值。它确保将分数与其符合资格的最高等级进行比较。如果第一个条件为真 (A2>=90),则返回“A”并且不评估任何其他条件。

顺序公式不正确

如果条件顺序颠倒,从最低阈值开始,它将返回错误的结果。

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

在这个不正确的公式中,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)。 备注: 表第一列中的分数必须按升序排序。

步骤 2:应用 VLOOKUP 函数分配成绩

选择一个空白单元格(本例中为 C2),输入以下公式并按 输入 获得第一名的关键。选择此公式单元格并将其拖动 填充手柄 下来获得其余的成绩。

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

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

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

第 1 步:创建成绩查找表

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

步骤 2:应用 VLOOKUP 函数分配成绩

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

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

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

使用 IFS

我们推荐使用 IFS函数 通过消除嵌套需求简化了流程,并使公式更易于阅读和管理。它增强了可读性并简化了多个条件检查的处理。要使用 IFS 函数,请确保您使用的是 Excel 2019 或更高版本,或者拥有 Office 365 订阅。让我们看看如何将其应用到实际示例中。

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

假设评分标准与之前相同,则可以按如下方式使用 IFS 函数:

选择一个空白单元格,例如C2,输入以下公式并按 输入 得到第一个结果。选择该结果单元格并将其拖动 填充手柄 下来得到其余的结果。

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

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

对于销售佣金计算场景,应用IFS函数如下:

选择一个空白单元格,例如C2,输入以下公式并按 输入 得到第一个结果。选择该结果单元格并将其拖动 填充手柄 下来得到其余的结果。

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


使用选择和匹配

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

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

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

第 1 步:使用搜索值创建查找数组

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

第 2 步:应用选择和匹配来分配成绩

选择一个空白单元格(本例中为 C2),输入以下公式并按 输入 获得第一名的关键。选择此公式单元格并将其拖动 填充手柄 下来得到其余的结果。

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

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

使用 CHOOSE 和 MATCH 组合进行销售佣金计算也很有效,特别是当佣金率基于指定的销售阈值时。让我们看看我们能做什么。

第 1 步:使用搜索值创建查找数组

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

第 2 步:应用 CHOOSE 和 MATCH 以获得结果

选择一个空白单元格(本例中为 C2),输入以下公式并按 输入 获得第一名的关键。选择此公式单元格并将其拖动 填充手柄 下来得到其余的结果。

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

:

总之,掌握 Excel 中的嵌套 IF 语句是一项宝贵的技能,可以增强您在数据分析和决策过程中处理复杂逻辑场景的能力。虽然嵌套 IF 对于复杂的逻辑运算非常强大,但请务必注意它们的局限性。 VLOOKUP、IFS 和 CHOOSE with MATCH 等更简单的替代方案可以在某些情况下提供更简化的解决方案。有了这些见解,您现在可以自信地将最合适的 Excel 技术应用于您的数据分析任务,确保电子表格的清晰度、准确性和效率。对于那些渴望深入研究 Excel 功能的人,我们的网站拥有丰富的教程。 在这里了解更多 Excel 提示和技巧.

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations