理解让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函数。
猜你喜欢
- 默认情况下,插入到Word2010文档中的图片作为字符插入到Word2010文档中,其位置随着其他字符的改变而改变,用户不能自由移动图片。而
- 在word使用过程中,想要制造一点心意,我们可以学着为文本框添加阴影效果吸引小伙伴的眼球,那么如何操作就一起来看看吧。Word2007文本框
- 办公室或者学生党经常会用到word做一些文档,有助于我们的工作和学习,但word大部分都比较单调,没有什么色彩,那我们就需要给它添加一点色彩
- 我们使用XY散点图绘制了一个圆。现在,在这个圆的基础上,我们让绘制过程动起来,如图1所示。图1下图2是我们在《Excel图表学习06:画圆》
- word中怎么插入手写签名?word中可以插入签名,今天我们就来看看怎么使用手写签名,比较好看,其实也很简单,就是将手写签名做成图片就可以了
- 相信有了解的朋友都清楚,有些软件或程序在Win10系统中必须以管理员的权限打开,方能正常运行。那么,在Win10 1909系统中该怎么以管理
- 因为各种需要,用户需要录制电脑屏幕来满足自己的需要。如果您是第一次接触录屏,不管是使用系统自带的录屏还是使用录屏软件,都会有会有多种录屏模式
- word制表虽不如excel强大,但是足够完成一些基本的制表工作。那么表格中常用到的斜表头怎么制作呢?在下面就由小编给大家分享下word设置
- Office2021正式版是Microsoft微软办公软件最新版,支持使用KMS进行激活,内含office专业增强版2021零售版、以及中文
- Excel排序方式有很多种,往往在排序工资表的时候我们都习惯使用隔行插入的方式来排序,这样看起来会更加明了一些。Excel排序方式有很多种,
- 本文介绍excel中根据A列公司名称找出重复的记录,并按照C列时间保留最近的一条记录的方法,希望对大家有所帮助。A列公司名称,B列公司地址,
- 这篇文章主要为大家介绍了利用excel制作类似店铺盈亏的计算方法,这个技巧很有用,不止上班的办公人员能用得到,连平时开店的都可以用这个技巧来
- 创建Excel文档后,如果需要查看或更改已有的工作簿,则需要先将其打开,然后再进行查看及更改操作。通常情况下,要打开已经创建的Excel20
- Excel2007用起来还不是很顺手,感觉还是用以前的版本习惯,笔者今天教你设置一下,适应以前的操作习惯。招式一:把“快速访问工具栏”请下来
- “选定时自动选定整个单词”就是用户在Word文档中拖动鼠标选定连续单词时,实现以单词为单位而不是以字母为单位的选中效果。Word2000、W
- 在利用Excel表格进行日常办公时,人力资源的朋友们经常需要将员工的信息进行统计,例如统计员工的工龄,那么在强大的Excel表格当中,有没有
- 步骤如下:1、打开word文档2、选中需要添加分割线的文字3、在工具栏选择 格式-----分栏(打开分栏工具对话框)4、设置分栏数量(可以设
- 目前使用Win10系统的用户还是非常多的,不过我们有时候在使用过程中总会遇到各种问题,其中就比如系统的任务栏一直在闪烁,那么碰到这种情况应该
- office2010在日常使用时会出现异常情况出现打不开文档的时候,该怎么办?不用着急,其实小编有对应的方法,下面小编马上就告诉大家offi
- 在办公室经常会打印各种文件,很多人都是按照常规的方法进行打印,其实在文档的打印中也是有很多技巧的,可以按照自己的需求设定,下面给大家分享wo