【Excel基础知识】逻辑值的四个妙用,高手必备技能!
公众号回复2016 下载office2016
昨天介绍了一些逻辑值的基础知识:
【Excel基础知识】Excel中逻辑值是哪来的?石头缝里蹦出来的吗……
今天通过四个典型的案例来了解逻辑值的妙用。
先来分析这个问题的特点:
相同的部门都是在一起的,当部门发生变化序号对应增加1。
如果这个问题给一个函数高手来做,很可能会写一个这样的公式:=SUMPRODUCT(1/COUNTIF(B$2:B2,B$2:B2))
实际上,这个问题可以利用逻辑值的特性来解决:
=(B1<>B2)+N(A1)。
验证一下公式的正确性:
思路分析:
对B列进行上下位置的比较,可以发现,结果为TRUE的位置就是需要序号增加1的位置,结果为FALSE的位置,序号不需要变,直接等于上一个单元格的序号即可。
解释两点:
1、这里用到了一种公式设计时常用的思路,就是利用上一个单元格的数据进行叠加计算,如果A1为空,在进行加法运算的时候被看作是0,就有了这样的结果。
2、比较运算的优先级低于加减乘除运算,所以要加括号。关于运算顺序,大家可以根据这张表去理解。
在本例来说,A1不为空,所以使用N函数进行处理,就有了=(B1<>B2)+N(A1)这个公式的诞生。
公式看上去很简单,但是要想到就真的不简单了。
某地天然气实行阶梯收费方式,300方以内(含300方)每方1.7元,300-500方以内(含500方),每方为2元,500方以上,每方2.5元。
这个问题可以写出很多个公式来计算,今天我们只看使用逻辑值的计算方法:=E2*1.7+(E2>300)*(E2-300)*0.3+(E2>500)*(E2-500)*0.5
对于这类问题,用逻辑值的做法非常简单,有规律可循,而且不需要使用函数,首先换个角度来看看阶梯价格的计算方式,我们用这个表格来进行说明:
上面这个图更直观的体现出了阶梯的含义。
这个问题是三级定价,所以公式实际上也是三部分组成:E2*1.7、(E2>300)*(E2-300)*0.3、(E2>500)*(E2-500)*0.5;
不管实际用量是多少方,1.7的部分肯定都有,所以E2*1.7必须有,这可以理解为1级收费;
再往上的话,超过300的部分,0.3肯定都是有的,这里就有一个比较了,E2>300,如果实际用量小于等于300,这个比较的结果是FALSE,这部分计算结果为0;反之,如果是大于300的话,(E2-300)*0.3正好就是超过部分加收的金额,与第一级收费相加构成了二级收费的结果;
第三部分同样,判断实际用量是否超过500,如果不超过,这部分整个为0,超过的话,按0.5进行加收(注意这个0.5是与前一级的差额而不是与第一级的差额);
明白这一点以后,不过多少级定价,只要找到合适的比较值,以及加收的单价,就能计算出最终的阶梯价格。
这种方法绕过了if函数的多层判断,在级数较少的时候非常方便,可以使用这个思路去试试计算个人所得税(7级),可能公式会长一点,但是容易理解。
例二相对于例一来说,更容易上手,例一过于取巧了。下面再来看几个难度适中的例子
最终成绩计算规则:本地生源在初试成绩的基础上增加10分,异地生源的最终成绩就是初试成绩。
这类问题一般都用if解决了:
=IF(C3="本地",B3+10,B3)
其实这种问题用逻辑值完全可以解决,下面就通过这个例子,让我们来一步步了解逻辑值的魅力吧。
在这个问题中,只有一个判断,就是C3="本地"。
通过这个判断,可以把是不是本地的区分开,接下来的一步很重要,怎么利用这个逻辑值呢?
让我们再来回顾一下这个问题的规则和逻辑值与数字的对应关系:
本地加10分,异地加0分;true对应1,false对应0;1*10=10,0*10=0。
好像领悟到了什么,马上来试试:
到了这一步,答案已经呼之欲出了。
最后的公式为:=B3+(C3="本地")*10
这个问题我们用了三个辅助列来说明过程,将解决问题的过程使用辅助列来一步一步分解,最后再合成一个公式,对于新手学习函数嵌套来说非常有用。
与例三类似的,我们再看一个例子:
这是一张工资表,需要计算每个人的补贴金额,补贴的发放规则为:高工补贴为200,工程师补贴为100,其他岗位补贴0。
对于这个问题,用逻辑值来计算补贴的公式为:=(B3="高工")*200+(B3="工程师")*100
测试题:需要计算员工的年假天数
规则为:非正式员工不享受年假;正式员工有5天年假,女性多3天,工龄满10年多5天,年龄满40多两天。
F列为正确结果,只用逻辑值该如何设计公式?
小结:
通过以上介绍,逻辑值可以不需要函数,直接拿来计算就能解决很多问题,尤其是当逻辑值遇上数组,再一起配合函数使用的话,功能就会显得无比强大,随便拿出一些很经典的公式组合,都会看到逻辑值的影子(当然还有数组)。
学习公式函数,最基本的技能有四个:
1、数列构造方法
2、绝对引用、相对引用和混合引用
3、逻辑值
4、数组知识
掌握上述四个基本功,再配合各种各样的函数,就能驰骋职场了。
《菜鸟的Excel函数修炼手册》是你学习公式函数的最佳选择。
扫描二维码
老师带你进Excel交流微信群
加群免费哦
推一下我新出的书,也就是上面这本,非常适合新手学习。
出版社的主编说“第一次看到这本书的时候感觉非常好,这么多年能把函数用这种风格的语言讲出来的,几乎没有”。
不夸张地说,看了肯定能受益,毕竟书里的内容,都是我自己踩过的坑。