了解让Excel更智能、更美观的逻辑测试
很多情况下,我们会在公式中设置逻辑测试,然后让Excel自动判断并计算结果。换句话说,只要你设置好条件,Excel就会帮你做决策,执行相应的计算操作,返回相应的结果。从这个意义上说,逻辑测试给Excel增加了智能功能。
下面,我们将详细了解Excel中的逻辑测试,为熟练编写Excel公式打下良好的基础。
通常,我们在IF函数中使用逻辑测试,并根据测试结果选择相应的计算。在下图1所示的工作表中,当水果的销售量超过1000时,将给予2%的佣金。
图1
单元格D2中的公式为:
=中频(B21000,B2*0.02,0)
把它放到单元格D12。
上面只是一个简单的条件测试,只有一个条件。更复杂?比如销量1000以上,利润率50%以上,给2%的佣金怎么样?这涉及到稍微复杂一点的逻辑测试。
逻辑运算原理
在继续之前,让我们先了解一下Excel中的布尔逻辑。用简单的串并联电路类比。
如下图2所示,在一个简单的串联电路中,只有当开关1和2都关断时,灯才会亮,而当两个开关都接通或只有一个开关接通时,灯就不会亮。
图2。
开表示假或0,关表示真或1。
下表左侧是简单AND运算的真值表,右侧可以转换为简单的数值计算,即TRUE转换为1,FALSE转换为0,AND变成*。
类似地,对于下面图3所示的简单并联电路,当开关1和2都打开时,灯不会点亮,但是当至少一个开关关闭时,灯会点亮。
图3。
下表中,左侧是OR运算的简单真值表,右侧可以转换为简单的数值计算,即TRUE转换为1,FALSE转换为0,OR变成数字。注意,当结果大于或等于1时,测试结果返回真。在逻辑测试中,非零数字始终为真,数字0始终为假。
以上两个表是基本的逻辑测试真值表,其原理很好理解。在此基础上,可以很容易地扩展到多条件的逻辑测试。
Excel逻辑测试规则。
在Excel中,当需要进行多个逻辑测试时,经常使用AND函数、OR函数或NOT函数的组合。例如,对于本文开头提到的例子,如果销售金额超过1000,利润率超过50%,则只给2%的佣金。单元格D2中的公式如下:
=中频(与(B21000,C20.5),B2*0.02,0)
把它放到单元格D12。
稍微复杂一点。
如果销售金额超过1000,利润率超过50%,只给2%的佣金,所有销售的火龙果都给佣金,那么cell D2中的公式如下:
=IF(OR(AND(B21000,C20.5),A2=“火龙果”),B2*0.02,0)。
把它放到单元格D12。
简化之美。
应用Excel逻辑测试规则时,可以简单地将逻辑测试相乘来表示AND函数。比如销量大于1000,利润率大于50%时,只给2%的佣金。此时,单元格D2中的公式可以简化为:
=(B21000)*(C20.5)*B2*0.02
把它放到单元格D12。
仔细欣赏一下,对于公式中的(B21000)*(C20.5),计算结果始终为0或1,即得不到佣金时为0,能得到佣金时为1,所以可以直接乘以销售额和佣金的百分比。
不是所有的逻辑都可以简化。
如果用上面提到的方式将OR运算简化为一个符号,可能会带来问题,因为在某些情况下,逻辑测试的结果会大于1,从而导致计算结果出现误差。
比如销售金额在1000以上,利润率在50%以上,只给2%的佣金,销售的龙果全部给佣金。如果简化为一个公式:
(B21000)*(C20.5) (A2=“火龙果”)。
当火龙果销量大于1000,利润率大于50%时,将得到结果2。这时,如果乘以销售额和佣金率,结果就会翻倍,也就是结果不正确。
因此,在对OR运算应用简化方法时,应谨慎。对于上面的示例,您可以使用以下任何公式:
=IF((B21000)*(C20.5) (A2=“火龙果”),TRUE,FALSE)*B2*0.02。
=IF((B21000)*(C20.5) (A2=“火龙果”),1,0)*B2*0.02。
标签
掌握并熟练编写条件型Excel计算公式,需要了解Excel的逻辑运算原理和逻辑测试规则。在Excel中,可以用*号代替AND函数,用#号代替OR函数。
版权声明:了解让Excel更智能、更美观的逻辑测试是由宝哥软件园云端程序自动收集整理而来。如果本文侵犯了你的权益,请联系本站底部QQ或者邮箱删除。