IF 函数 - 嵌套公式和避免错误

IF 函数 - 嵌套公式和避免错误

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

IF 函数允许通过测试某个条件并返回 True 或 False 的结果,从而对某个值和预期值进行逻辑比较。

  • =IF(内容为 True,则执行某些操作,否则就执行其他操作)

因此 IF 语句可能有两个结果。第一个结果是比较结果为 True,第二个结果是比较结果为 False。

IF 语句非常强大,其构成了许多电子表格模型的基础,但也是导致许多电子表格问题的根本原因。理想情况下,IF 语句应适用于最小条件(例如 Male/Female 和 Yes/No/Maybe),但是对更复杂情况求值时则需要同时嵌套* 3 个以上的 IF 函数。

*“嵌套”是指在一个公式中连接多个函数的做法。

使用逻辑函数 IF 函数时,如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。

语法

IF(logical_test, value_if_true, [value_if_false])

例如:

  • =IF(A2>B2,"超出预算","正常")

  • =IF(A2=B2,B4-A4,"")

参数名称

说明

logical_test   

(必需)

要测试的条件。

value_if_true   

(必需)

logical_test 的结果为 TRUE 时,您希望返回的值。

value_if_false   

(可选)

logical_test 的结果为 FALSE 时,您希望返回的值。

备注

虽然 Excel 允许嵌套最多 64 个不同的 IF 函数,但不建议这样做。原因?

  • 要正确地构建多个 IF 语句需要花大量心思,并要确保其逻辑在直至结尾的每个条件下都能计算正确。如果嵌套公式不是 100% 准确,那么计算过程可能花 75% 的时间,而返回结果可能花 25% 的时间,并且结果并不理想。但是得出这 25% 结果的几率很小。

  • 多个 IF 语句维护起来非常困难,特别是过一段时间后回头再看,想要了解当时你(其他人的话更糟糕)想要做什么时。

如果发现 IF 语句似乎在无穷无尽地不断增加,这时候应放下鼠标,重新思考策略。

我们来了解一下如何使用多个 IF 正确创建一个复杂的嵌套 IF 语句,以及何时应使用 Excel 库中的其他工具。

示例

以下示例介绍了一个相对标准的嵌套 IF 语句,该语句将学生考试成绩转化为等效字母等级。

复杂的嵌套 IF 语句 - E2 中的公式为 =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-",IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

    此复杂嵌套 IF 语句遵循一个简单逻辑:

  1. 如果 Test Score(单元格 D2)大于 89,则学生获得 A

  2. 如果 Test Score 大于 79,则学生获得 B

  3. 如果 Test Score 大于 69,则学生获得 C

  4. 如果 Test Score 大于 59,则学生获得 D

  5. 否则,学生获得 F

此特定示例的安全性相对较安全, 因为测试分数和信函成绩之间的相关性不可能更改, 因此不需要大量维护。但是, 如果你需要在 +、a 和 a 之间划分成绩 (等等), 这会是一种想法—?现在, 如果需要重新编写语句, 则你的四个条件有12个条件!现在, 你的公式如下所示:

  • =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))

该公式仍具有准确的功能并按预期工作,但需要花很长时间编写并花更长时间进行测试,才能确保该公式可完成所需操作。另一个明显的问题是必须手动输入分数和等效字母等级。不小心输错字的几率是多少?想象一下,需要使用更复杂的条件 64 次!当然这是可能实现的,但你真的想给自己带来这种麻烦和难以察觉的可能错误吗?

提示: Excel 中的每个函数都需要使用左括号和右括号 ()。编辑时,Excel 会通过对公式的不同部分着色来帮助你定位。例如,如果要编辑上面的公式,将光标移过每个右括号“)”时,它的相应左括号会显示相同颜色。在复杂嵌套公式中检查是否拥有足够的匹配括号时,此方法尤其有用。

更多示例

下面是一个十分常见的示例 - 根据销售额等级计算销售佣金

单元格 D9 中的公式为 IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
  • =IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))

此公式表示如果 (C9 大于 15,000 则返回 20%,如果 (C9 大于 12,500 则返回 17.5% 等等…

虽然该公式与前面的“成绩”示例非常相似,但它很好地说明了维护大型 IF 语句的难度 - 如果组织决定增加新的薪酬等级,甚至改变现有美元或百分比值,那么你需要做些什么?必须手动完成大量工作!

提示: 为了使长公式更易于阅读,可在编辑栏中插入换行符。只需在将文本换到新行前按 Alt+Enter。

下面是一个包含混乱逻辑的佣金方案示例:

D9 中的公式顺序颠倒,变为 =IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))

是否可以看到错误?将收入比较的顺序与上一个示例进行比较。此方法将转到哪一种情况?正确的是, 它从自下而上 ($5000 到 $15000), 而不是其他方面。但是为什么应该如此大的交易?这是一件很大的交易, 因为该公式不能在 $5000 上传递任何值的第一个求值。假设你有 $12500 的收入-IF 语句将返回 10%, 因为它大于 $5000, 将在那里停止。这可能是非常令人难以置信的问题, 因为在许多情况下, 这些类型的错误不会被忽略, 直到它们产生负面影响。因此, 了解复杂嵌套的 IF 语句有一些严重的缺陷, 你可以执行哪些操作?在大多数情况下, 您可以使用 VLOOKUP 函数, 而不是使用 IF 函数生成复杂公式。使用VLOOKUP, 您首先需要创建引用表:

单元格 D2 中的公式为 =VLOOKUP(C2,C5:D17,2,TRUE)
  • =VLOOKUP(C2,C5:D17,2,TRUE)

此公式指示在单元格区域 C5: C17 中查找 C2 中的值。如果找到值, 则从 D 列中的同一行返回相应的值。

单元格 C9 中的公式为 =VLOOKUP(B9,B2:C6,2,TRUE)
  • =VLOOKUP(B9,B2:C6,2,TRUE)

类似地,此公式将在 B2:B22 区域中查找单元格 B9 的值。如果找到值,则从 C 列的同一行返回相应值。

注意: 这两个 VLOOKUP 公式在公式末尾使用 TRUE 参数,这表示需要它们查找适当的匹配项。也就是说,它将匹配查找表中的精确值以及范围内的任何值。在这种情况下,查找表需要按升序​​排序(从小到大)。

此处介绍了 VLOOKUP 的更多详细信息,VLOOKUP 肯定比一个 12 级的复杂嵌套 IF 语句简单得多!还有其他一些不太明显的优点:

  • VLOOKUP 引用表是开放的,易于查看。

  • 条件更改后,可轻松更新表值,无需更改公式。

  • 如果不希望他人查看或更改引用表,只需将其置于其他工作表。

你知道吗?

现在, 可以使用单个函数替换多个嵌套 IF 语句的IFS 函数。而不是我们的初始成绩示例, 它有四个嵌套 IF 函数:

  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

可使用单个 IFS 函数使其变得更简洁:

  • =IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

IFS 函数十分有用,因为无需担心所有这些 IF 语句和括号带来的麻烦。

注意: 仅当具有 Office 365 订阅时,此功能才可用。如果你是 Office 365 订阅者,请确保你具有最新版本的 Office

试用 Office 365 或最新版本的 Excel

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

相关主题

视频: 高级 IF 函数
IFS 函数 (Office 365、Excel 2016 和更高版本)
COUNTIF 函数将根据单个条件对值进行计数
COUNTIFS 函数将根据多个条件对值进行计数条件

SUMIF 函数将根据单个条件对值求和 SUMIFS 函数将根据多个条件
和函数
或函数
VLOOKUP 函数

Excel 中公式的概述 如何避免损坏的公式
检测公式
中的错误逻辑函数
excel 函数 (按字母顺序)
excel 函数 (按类别)

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×