首页 > Excel专区 > Excel函数 >

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

Excel函数 2023-01-13

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(D2>89,”A”,IF(D2>79,”B”,IF(D2>69,”C”,IF(D2>59,”D”,”F”))))

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

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

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

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

    4. 如果TestScore大于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会通过对公式的不同部分着色来帮助你定位。例如,如果要编辑上面的公式,将光标移过每个右括号“)”时,它的相应左括号会显示相同颜色。在复杂嵌套公式中检查是否拥有足够的匹配括号时,此方法尤其有用。

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

  • =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。

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

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

  • =VLOOKUP(C2,C5:D17,2,TRUE)

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

  • =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语句和括号带来的麻烦。

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

    需要更多帮助吗?可随时在Excel技术社区中咨询专家,在解答社区获得支持,或在ExcelUserVoice上建议新功能或功能改进。

    相关主题视频:高级IF函数IFS函数(Office365、Excel2016和更高版本)COUNTIF函数将根据单个条件对值进行计数COUNTIFS函数将根据多个条件对值进行计数条件SUMIF函数将根据单个条件对值求和SUMIFS函数将根据多个条件和函数或函数VLOOKUP函数Excel中公式的概述如何避免损坏的公式检测公式中的错误逻辑函数excel函数(按字母顺序)excel函数(按类别)


    上一篇:IFS 函数 下一篇:IF 函数
    Copyright © 2016-2023 office学习教程网 office.tqzw.net.cn. All Rights Reserved.