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

加群免费哦

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

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

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

(0)

相关推荐

  • 函数妙用:MAX函数给合并单元格填充序号!

    在Excel中,合并的单元格行数不一致是无法进行自动填充操作的. 如下图所示,第1个合并单元格的行数为两行:第2个合并单元格的行数为4行:第3个合并单元格的行数为3行:第4个合并单元格的行数为两行:每 ...

  • Excel表格中删除行后,序号也不连续了,一个公式教你搞定

    [温馨提示]亲爱的朋友,阅读之前请您点击[关注],您的支持将是我最大的动力! 我们在制作表格时,经常会在最左边一列填充一个序号,记录表格数据.但是当我们发现有些行不需要时,将其删除后,序号中断了,变得 ...

  • Excel 筛选后序号能连续显示吗?

    Excel 筛选估计人人都用过,原始表部分截图如图1,序号是连续的自然数列,筛选后部分截图如图2,序号不是连续的.可是老板要求筛选前和筛选后序号都是连续的序号,怎么办呢? 图 1 图 2 本文教你一个 ...

  • 社区考试公共基础知识:中国四大名关

    社区考试公共基础知识:中国四大名关

  • 语文老师熬夜整理小学语文基础知识就这四张...

    语文老师熬夜整理小学语文基础知识就这四张图,家长们为了孩子也要存下来,学好这些基础知识,语文考试稳拿高分. 第一.固定结构.(ABB.AABB.AABC.ABAC.又A又B) 第二.带反义词的词语.( ...

  • 2022上海事业单位考试公共基础知识:晚唐四杰

    公共基础知识中的文史常识部分占比相对偏大,学习难度不大,但是考点涵盖十分广泛,现就一个文学常识中的常见知识点--晚唐四杰,分享给大家,以便帮助大家备考. 唐玄宗后期出现的安史之乱(755年12月16日 ...

  • 公共基础知识:晚唐四杰

    公共基础知识中的文史常识部分占比相对偏大,学习难度不大,但是考点涵盖十分广泛,现就一个文学常识中的常见知识点--晚唐四杰,分享给大家,以便帮助大家备考. 唐玄宗后期出现的安史之乱(755年12月16日 ...

  • 【Excel基础知识】相对引用、绝对引用和混合引用

    绝对引用.相对引用和混合引用可以说是三个概念,也可以说是一个概念,就是"$"这个符号的用法.对于这个概念,理解意思要比记住概念本身更重要. 重要提示:在学习下面的内容时,建议打开表 ...

  • 诗词基础知识(十四)

    转自:石湖春涨 所谓炼句炼字,就是在词的某一句的某一字,经过锤炼,而使全句乃至全篇形象鲜豁,神情飞动,顿生光辉.这在词的创作中有个术语,叫作"诗眼"或"词眼". ...

  • 墨海:六爻基础知识之十四-六神之朱雀

    作者:墨海(原名:玄生散人) 上节讲了青龙的内容,这一节给大家介绍朱雀,大家一看到朱雀就会联想到叽叽喳喳的小鸟,或者浴火凤凰,热烈的.有声音的.有传播性质的(例如语言.文化).急燥活泼的,朱红色的,主 ...

  • 元曲基础知识之(四)曲牌

    转自:七月流火的博客 元曲基础知识之四:曲牌 (一)曲牌的定义 曲牌就是曲调的名称,有一定调子.唱法:字数.句法.平仄用韵都有基本定式,可以根据曲牌填写新曲词,从韵文文体来说,曲牌即为此种文体的格律谱 ...

  • 初中物理 中考基础知识汇总(四)

    初中物理 中考基础知识汇总(四)