Excel中if函数多重条件的使用

在实际工作中,绝大多数情况是根据两种以上的可能性处理数据,小编整理Excel中if函数多重条件的使用,一起来学习吧

如图1-1所示,在C列根据转入金额与币种,计算折合人民币的数额,在C2单元格输入公式“=IF(B2="美元",A2*6.5,IF(B2="欧元",A2*9.3,A2))”后下拉填充。
 
Excel中if函数多重条件的使用
图1-1
 
在本例中,由于币种有3种,导致出现3种不同的可能性,需要使用两个IF函数嵌套在一起对3种情况分别描述。
 
以C2单元格内公式“=IF(B2="美元",A2*6.5,IF(B2="欧元",A2*9.3,A2))”为例,外围的IF首先判断B2单元格的文字是否等于美元,如果等于美元,C2单元格将显示A2*6.5的计算结果;如果不等于美元,C2单元格将显示“IF(B2="欧元",A2*9.3,A2)”的计算结果,也就是说,当B2单元格中的文字不是美元时,公式“IF(B2="欧元",A2*9.3,A2)”才会被运算。公式“IF(B2="欧元",A2*9.3,A2)”首先判断B2单元格的值是否为欧元,如果为欧元,则C2单元格将显示A2*9.3的计算结果;而如果B2单元格的值不等于欧元,则说明B2单元格的值既不是美元也不是欧元,所以必然是人民币,将返回A2单元格的值。
 
需要注意,在多个IF函数嵌套使用时,IF函数并不遵循“先算括号里面,再算括号外面”的原则,而是自左向右运算公式。在本例中公式“IF(B2="美元",A2*6.5,IF(B2="欧元",A2*9.3,A2))”首先比较B2单元格的文字是否为美元,如果是美元,则返回“A2*6.5”的结果,在这种情况下第三参数的公式“IF(B2="欧元",A2*9.3,A2)”将不被运算;只有在B2单元格内的值不是美元时,才会运算公式“IF(B2="欧元",A2*9.3,A2)”,在这种情况下公式“A2*6.5”将不会被运算。
 
在上个示例中,由于存在3种情况(金额乘以美元汇率、金额乘以欧元汇率、金额不乘以汇率),所以将2个IF函数嵌套在一起使用,而两个IF将构成1层嵌套。如果存在4种情况,例如在币种内增加英镑,则需要使用3个IF函数嵌套处理,C2单元格公式应为“=IF(B2="美元",A2*6.5,IF(B2="欧元",A2*9.3,IF(B2="英镑",A2*10.6,A2)))”。可见,当使用IF函数处理多种可能性的问题时,如果存在N种可能性,将需要N-1个IF函数嵌套在一起。
 
通过这种函数嵌套,IF函数看似在处理多条件问题时无所不能,但实际上,嵌套的层数是有限制的。在Excel 2003中,IF函数最多嵌套7层,而在Excel 2007及Excel 2010中,IF函数最多嵌套63层。为了使IF函数能处理超过最大数量限制的条件判断,我们可以采用一些特殊的公式书写方法创建公式,例如定义名称方法、分散方法和函数并行方法。
 
● 定义名称方法
 
以Excel 2003中使用IF函数描述10种可能为例,图1-2列出了10个不同币种对人民币的汇价,在F列根据汇价与数额计算折合人民币的金额。在本例中,币种达到10个,显然已经超出了Excel 2003中7层嵌套所允许的最大数量。于是,将其中5个币种的处理定义为名称。
 
点选F2单元格后创建名称,名称命名为“后五种情况”,引用位置为公式“=IF(D2="瑞士法郎",E2*7.37,IF(D2="瑞典克朗",E2*1.02,IF(D2="丹麦克朗",E2*1.23,IF(D2="挪威克朗",E2*1.17,E2*0.81))))”。这个公式能处理瑞士法郎、瑞典克朗、丹麦克朗、挪威克朗、澳门元这5个币种的折合人民币计算。
 
在F2单元格输入公式“=IF(D2="美元",E2*6.47,IF(D2="欧元",E2*9.18,IF(D2="英镑",E2*10.52,IF(D2="加拿大元",E2*6.64,IF(D2="港元",E2*0.83,后五种情况)))))”。这个公式中不仅描述了当币种为美元、欧元、英镑、加拿大元、港元时的运算,还阐明当币种不是这5个币种之一时,将采用名称“后五种情况”所定义的公式来计算。
 
通过这种定义名称的方式,可以将一部分针对某些可能性所做的运算交给定义名称功能来处理,变相增加了IF函数的嵌套层次数量。
 
● 均衡分散方法
 
分散方法与定义名称方法类似,都是将过多的条件判断分组来处理。
 
在F2单元格输入公式“=IF(OR(D2="美元",D2="欧元",D2="英镑",D2="加拿大元",D2="港元"),IF(D2="美元",E2*6.47,IF(D2="欧元",E2*9.18,IF(D2="英镑",E2*10.52,IF(D2="加拿大元",E2*6.64,E2*0.83)))),IF(D2="瑞士法郎",E2*7.37,IF(D2="瑞典克朗",E2*1.02,IF(D2="丹麦克朗",E2*1.23,IF(D2="挪威克朗",E2*1.17,E2*0.81)))))”。
 
这个公式使用了OR函数进行判断,将10个币种分为两部分,并分别作为最外侧IF函数的第二参数及第三参数进行运算,而第二参数与第三参数都由一组IF函数公式构成,每个参数负责处理5个币种。如果币种为美元、欧元、英镑、加拿大元、港元则按照最外侧IF函数第二参数中的IF函数公式处理,否则按照最外侧IF函数第三参数中的IF函数公式处理。
 
● 公式并行方法
 
除以上两种方法外,改变IF的书写方式也可以带来相同的效果。例如在图1-2所示数据中,在F2单元格内直接输入嵌套型的IF函数,将会受到嵌套层次数量的限制,这时就可以使用公式并行方法不使用嵌套而完成运算。
 
公式并行方法
图1-2
 
在F2单元格内输入公式“=IF(D2="美元",E2*6.47,0)+IF(D2=”欧元",E2*9.18,0)+IF(D2=”英镑",E2*10.52,0)+IF(D2=”加拿大元",E2*6.64,0)+IF(D2=”港元",E2*0.83,0)+IF(D2=”瑞士法郎",E2*7.37,0)+IF(D2=”瑞典克朗",E2*1.12,0)+IF(D2=”丹麦克朗",E2*1.23,0)+IF(D2=”挪威克朗",E2*1.17,0)+IF(D2=”澳门元",E2*0.81,0)”。
 
这个公式使用了10个IF,并把这10个IF加在一起,每个IF只描述一个币种的处理方式,如果不是该币种则返回0,最后将这些IF加在一起得到折合人民币的金额。这样的写法将会造成10个IF中有且仅有一个IF函数中的条件判断结果为TRUE,从而得到金额,而另外9个关于其他币种的IF函数仅会等于0,9个0值与1个折合人民币金额相加,不会改变金额的数量。
 
需要注意,无论使用何种方式,回避IF函数最大嵌套数量限制,都仅是权宜之计,并不是主流的函数用法。上述3种方法都会造成公式书写过于繁复,不仅不利于公式的阅读,也会给公示排错造成困难。由于IF函数的特性,导致其高能却低效的特点,所以在使用IF函数时,如果遇到判断条件过多的情况,要积极寻找新的运算方式,例如后续章节中将会涉及到使用其他函数代替IF函数的问题。
 
注意:本节提到了对于IF函数的最大嵌套层次限制,Excel 2003为7层,Excel 2007及Excel2010为63层。嵌套是指函数的某一参数由另外一个函数公式构成的情况,单独书写一个IF函数的公式并没有形成嵌套。以公式“=IF(B2="美元",A2*6.5,IF(B2="欧元",A2*9.3,IF(B2="英镑",A2*10.6,A2)))”为例,3个IF函数构成2层嵌套,描述4种可能。
 
猜你喜欢:EXCEL函数
  • Excel中OR函数怎么用

    Excel中OR函数怎么用

    Excel中OR函数怎么用?OR函数同样用作将多个条件判断关联在一起,最终得到一个布尔值,在这些条件判断中,任何一个或多个条件判断的结果为TRUE时,OR函数就会返回结果...

    2019-10-03 来源:未知 浏览:113 次

    分享
  • 如何通过函数公式隐藏空单元格

    如何通过函数公式隐藏空单元格

    如何通过函数公式隐藏空单元格?在工作中,绝大多数数据都是逐渐增加的,反映在表格上,则意味着数据区域的行数增加。在数据不断增加的情况下,如果希望新数据也...

    2019-06-24 来源:未知 浏览:159 次

    分享
  • Excel如何用IF公式准确写出分段函数

    Excel如何用IF公式准确写出分段函数

    Excel如何用IF公式准确写出分段函数?IF函数的第一参数在绝大多数情况下都需要由一个条件判断公式构成,前面几个例子中都使用了“=”创建条件判断公式。...

    2019-06-24 来源:未知 浏览:172 次

    分享
  • 多条件if函数的使用

    多条件if函数的使用

    在IF函数第一参数的条件判断比较复杂的情况中,也经常会看到AND函数与OR函数的身影,小编整理多条件if函数的使用,一起来学习吧。...

    2019-06-24 来源:未知 浏览:179 次

    分享
  • Excel中if函数怎么用

    Excel中if函数怎么用

    if函数怎么用?IF函数在参数表述上非常贴近语言习惯,其函数结构为“如果怎么样,就怎么样,否则怎么样”,这3个“怎么样”就是IF函数的3个参数。...

    2019-06-24 来源:未知 浏览:176 次

    分享
换一换
热门推荐:Excel教程
  • excel怎么自定义页眉

    excel怎么自定义页眉

    excel怎么自定义页眉?如果用户需要自定义添加页眉,则Excel会将页眉分为左、中、右三个区域,并可以在页眉中插入文字、时间、图片等多种内容。...

    2019-07-19 来源:未知 浏览:59 次

    分享
  • 如何让数据透视表的报表筛选字段水平显示

    如何让数据透视表的报表筛选字段水平显示

    如何让数据透视表的报表筛选字段水平显示?在默认情况下,当数据透视表中出现多个筛选字段时,是按垂直并排的方式进行排列的,如果用户对该排列方式不满意,可以...

    2019-06-19 来源:未知 浏览:91 次

    分享
  • excel怎么更改图例位置

    excel怎么更改图例位置

    excel怎么更改图例位置?当一个图表中有多个数据系列时,图例就显得非常重要,因为它是区分各个系列的标志,能够帮助用户快速正确地读图。如果图例位置不合适,就...

    2019-07-31 来源:未知 浏览:66 次

    分享
  • EXCEL如何刷新数据透视表

    EXCEL如何刷新数据透视表

    EXCEL如何刷新数据透视表?当用户创建并激活了数据透视表后,可在“数据透视表工具-分析”选项卡下看到一个刷新按钮,在改变了源数据内容后,可通过该按钮的刷新功...

    2019-06-17 来源:未知 浏览:199 次

    分享
  • excel绩效工作表怎么做

    excel绩效工作表怎么做

    excel绩效工作表怎么做?前面介绍了Excel 2016中记录单的使用、数据排序与筛选的方法及分类汇总数据等内容。为了进一步巩固本章所学知识,加深理解和提高应用能力,接...

    2019-07-26 来源:未知 浏览:147 次

    分享
换一换