【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交流微信群

加群免费哦

推一下我新出的书，也就是上面这本，非常适合新手学习。

出版社的主编说“第一次看到这本书的时候感觉非常好，这么多年能把函数用这种风格的语言讲出来的，几乎没有”。

不夸张地说，看了肯定能受益，毕竟书里的内容，都是我自己踩过的坑。