理解让Excel更具智能之美的逻辑测试
时间:2022-05-13 05:15:02
很多时候,我们都会在公式中设置逻辑测试,然后让Excel来自动判断并计算结果。换句话说,只要你设置好条件以后,Excel来帮你决策并执行相应的计算操作,返回相应的结果。从这个意义上说,逻辑测试为Excel增添了智能功能。
下面,我们来详细解Excel中的逻辑测试,为熟练编写Excel公式打好基础。
通常,我们在IF函数中使用逻辑测试,根据测试结果来选择相应的计算。如下图1所示的工作表,当水果销售额大于1000时,就给2%的提成。
图1
在单元格D2中的公式为:
=IF(B2>1000,B2*0.02,0)
下拉至单元格D12中即可。
上面只是一个简单的条件测试,只有1个条件。更复杂一些呢?例如,如果要求销售额大于1000并且利润率大于50%时,才给2%的提成呢?这就涉及到稍微复杂点的逻辑测试。
逻辑运算原理
在继续之前,我们先来理解Excel中的布尔逻辑。以简单的串联和并联电路类比。
如下图2所示,一个简单的串联电路,只有当开关1和开关2都关上时,灯才会亮,当两个开关都打开或者只有其中一个开关打开时,灯都不会亮。
图2
开意味着FALSE或者0,关意味着TRUE或者1。
下表中左侧是简单的AND操作的真值表,可将其转换成右侧的简单的数字计算,即将TRUE转换为1,FALSE转换为0,AND变为*号。
同样,对于下图3所示简单的并联电路,当开关1和开关2都开时,灯不会亮,当至少其中一个开关关时,灯就会亮。
图3
下表中左侧是简单的OR操作的真值表,可将其转换成右侧的简单的数字计算,即将TRUE转换为1,FALSE转换为0,OR变为+号。注意,结果大于等于1时,测试结果返回TRUE。在逻辑测试中,非零数字总是TRUE,数字0总是FALSE。
上面两个表是基本的逻辑测试真值表,其原理很好理解。在此基础上,可以很容易地扩展到多个条件的逻辑测试。
Excel逻辑测试规则
在Excel中,当需要多个逻辑测试时,往往会使用到AND函数、OR函数或者NOT函数的组合。例如,对本文开头提到的例子,如果要求销售额大于1000并且利润率大于50%时,才给2%的提成,在单元格D2中的公式如下:
=IF(AND(B2>1000,C2>0.5),B2*0.02,0)
下拉至单元格D12中即可。
再复杂一点。
如果销售额大于1000并且利润率大于50%时,才给2%的提成,并且销售的火龙果都给提成,那么在单元格D2中的公式如下:
=IF(OR(AND(B2>1000,C2>0.5),A2=”火龙果“),B2*0.02,0)
下拉至单元格D12中即可。
简化之美
在应用Excel逻辑测试规则时,可以简单地将逻辑测试相乘来表示AND函数。例如,要求销售额大于1000并且利润率大于50%时,才给2%的提成,此时,单元格D2中的公式可以简化为:
=(B2>1000)*(C2>0.5)*B2*0.02
下拉至单元格D12中即可。
仔细体味一下,对于公式中的(B2>1000)*(C2>0.5),计算结果总是0或者1,即不能获得提成时是0,能够获得提成时是1,因此,可以直接乘以销售额和提成百分比。
不是所有的逻辑都能简化
如果按照上文提及的方式,将OR操作简化为+号,可能会带来问题,因为在有些情况下,逻辑测试的结果会大于1,这会导致计算结果出错。
例如,如果销售额大于1000并且利润率大于50%时,才给2%的提成,并且销售的火龙果都给提成。如果简化为公式:
(B2>1000)*(C2>0.5)+(A2=”火龙果“)
当火龙果的销售额大于1000且利润率大于50%时,会得到结果2。此时,如果再与销售额和提成率相乘,结果会扩大一倍,即结果不正确。
因此,对于OR操作应用简化方法时应谨慎。对于上例,可以使用下面任一公式:
=IF((B2>1000)*(C2>0.5)+(A2=”火龙果“),TRUE,FALSE)*B2*0.02
=IF((B2>1000)*(C2>0.5)+(A2=”火龙果“),1,0)*B2*0.02
结语
要精通并熟练编写带条件的Excel计算公式,就需要理解逻辑运算原理和Excel的逻辑测试规则。在Excel中,可以使用*号来代替AND函数,使用+号来代替OR函数。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
如何在Word中快速批量输入相同文本?Word快速批量输入相同文本的方法
![](https://img.aspxhome.com/file/2023/2/30812_0s.jpg)
在EXCEL表格中使用透视表方法介绍
![](https://img.aspxhome.com/file/2023/0/42610_0s.png)
Win10U盘驱动有异常怎么办?Win10U盘驱动有异常的解决办法
![](https://img.aspxhome.com/file/2023/1/51311_0s.jpg)
在Excel中怎么筛选唯一值或删除重复值以及对其应用条件格式
![](https://img.aspxhome.com/file/2023/7/36647_0s.gif)
SUMIF+CELL使隐藏列不参与汇总
![](https://img.aspxhome.com/file/2023/6/a142276_0s.png)
用Word 2010导航阅读超长文档技巧
Win10 20H2系统下游戏模式打不开怎么处理?
![](https://img.aspxhome.com/file/2023/9/49279_0s.jpg)
Word中的表格怎么使用公式
![](https://img.aspxhome.com/file/2023/9/33789_0s.png)
Excel如何制作跳转按钮?
![](https://img.aspxhome.com/file/2023/2/55002_0s.gif)
excel打开提示格式文件扩展名不一致该怎么办?
![](https://img.aspxhome.com/file/2023/0/a141770_0s.jpg)
Win10 1909默认字体怎么修改?Win10 1909默认字体修改教程
![](https://img.aspxhome.com/file/2023/9/48839_0s.jpg)
wps怎么套用ppt模板
![](https://img.aspxhome.com/file/2023/3/53453_0s.jpg)
如何在微软Word中添加批注?
![](https://img.aspxhome.com/file/2023/1/23691_0s.jpg)
Word文档怎么从非首页设置页码
Win10如何安装AMD的cpu
![](https://img.aspxhome.com/file/2023/4/49834_0s.png)
Excel2013中如何使用分类汇总功能?
![](https://img.aspxhome.com/file/2023/3/37563_0s.jpg)
word表格的边框和底纹怎样调整
如何关闭Word"自作我情"功能
![](https://img.aspxhome.com/file/2023/7/20097_0s.jpg)
Excel2010手动批量求和
![](https://img.aspxhome.com/file/2023/1/39291_0s.jpg)
怎样用WPS去除Word水印
![](https://img.aspxhome.com/file/2023/1/19731_0s.jpg)