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 和 A-(等等)?现在您四个条件 IF 语句需要重写以有 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)

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

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

同样,此公式查找 B2:B22 区域中的单元格 B9 中的值。如果找到值,然后相应的值返回从列 C 中同一行

注意: 这两个这些 VLOOKUPs 的公式,这意味着我们希望他们查看 approxiate 匹配的末尾使用参数为 TRUE。换言之,它将匹配的确切的值的查阅表中,以及在它们之间的任何值。在此例中查找表需要将按升序顺序排序,从最小到最大。

VLOOKUP 包含更多详细信息下面,但这是确定比 12 级别、 复杂的嵌套 IF 语句非常简单 !有其他不太明显的优点:

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

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

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

你知道吗?

没有现在可以替换多个, IFS 函数嵌套 IF 语句与单个函数。因此,而不是初始分数本例中,其中有 4 嵌套的 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 函数将根据多个条件的值进行求和
AND 函数
OR 函数
VLOOKUP 函数
在 Excel 中的公式概述
如何避免损坏的公式
使用错误检查功能检查公式中的错误
逻辑函数
Excel 函数 (按字母顺序)
Excel 函数 (按类别)

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

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

此信息是否有帮助?

谢谢您的反馈!

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

×