Excel教程:和IF函数说再见!用这个方法搞定逻辑判断,不要太简单!

每天一点小技能

职场打怪不得怂

编按:逻辑值在Excel运算中,是一个奇妙的存在。在很多需要写函数或一长串公式去做判断的地方,只要你学会巧妙运用逻辑值的方法,就能更快更准确的解决问题。你不信?看完今天的课程,相信即便是Excel菜鸟的你,也能瞬间秒懂!

在Excel中有一类特殊的运算:比较运算。

分别是:=(等于)、>(大于)、<(小于)、>=(大于或等于)、<=(小于或等于)、<>(不等号)。

例如:=(A1<=b1),=(a1>B1)*10,=A1="优秀",这三个公式中用到了比较运算。

比较运算的结果是逻辑值TRUE和FALSE,奇妙的逻辑值可以参与计算,在计算的时候TRUE=1,FALSE=0。

今天通过四个实际案例和大家一起看看有趣的逻辑值。

01

示例1:对部门进行编号。当部门发生变化时,序号加1。

公式为:=(B1<>B2)+N(A1)

结果如图所示。

在这个公式中用到了比较运算 (B1<>B2),单独看看这个运算的结果:

可以发现一个规律,TRUE所在行就是各部门第一次出现的位置,也就是序号增加(+1)的位置。FALSE所在的位置序号不需要变化,与上一行的单元格序号一样。

公式中还有个N(A1),作用是把A1中的文本变成0,因为直接用文本相加会得到错误值,就无法实现向下编号的效果了。

关于N函数的用法,可以参阅之前的教程:

你哭着对我说,excel函数都是骗人的,最简单实用的竟是N

02

示例2:计算阶梯价

某地天然气实行阶梯收费方式,规则如下:购买量在300方以内(含300方)价格为1.7元/方,300-500方以内(含500方),价格为2元/ 方,500方以上,价格为2.5元/方。

根据实际用量计算收费金额的方法很多,如果要用逻辑值的话,公式是这样的:

=D2*1.7+(D2>300)*(D2-300)*0.3+(D2>500)*(D2-500)*0.5,结果如图所示。

用逻辑值解决这类问题考验的就是计算思维,简单说一下这个公式的原理:

不管最后购买多少方天然气,1.7元都是底价,所以购买量*1.7是一定的。

当发生超量购买的时候,会出现加价的情况,超过300方的最少都要多收0.3元,这是第一次加价;超过500方的会再增加0.5元,这是第二次加价。

按照这个逻辑,最终的收费就由三部分构成,底价、一次加价、二次加价。

底价是D2*1.7,这个很容易理解。

一次加价是(D2>300)*(D2-300)*0.3,这里就有了一个比较,如果大于300 (D2>300),即判断为TRUE,计算时当做1,(D2-300)是超过300方的部分,0.3是第一次加价的差额(2-1.7=0.3),这部分一定要搞明白。

二次加价是同样的逻辑,(D2>500)*(D2-500)*0.5中,(D2>500)是一个比较,(D2-500)是超过二次加价的购买量,0.5是二次加价的差额(2.5-2=0.5)。

所以最终的公式就是分别计算出三部分金额之后,再相加得到收费金额。

搞清楚这个逻辑之后,不管多少级定价,只要找到每个阶梯对应的比较值,以及加收的单价,就能计算出最终的阶梯价格。

03

示例3:计算职称津贴

某公司按照不同的职称设置了津贴,具体规则为:高工200,工程师100,其他岗位0。

要按照员工的职称匹配对应的津贴,通常都是使用IF函数来完成的,实际上也可以用逻辑值来解决这类问题,就本例而言,可以使用公式=(B3="高工")*200+(B3="工程师")*100计算出津贴,结果如图所示。

在这个公式中,用了两次比较。

第一个比较是:B3="高工",当职称为高工时,比较的结果为TRUE,(B3="高工")*200的结果就是200;同理,当职称为工程师时,(B3="工程师")*100就是100,将两个比较相加就得到了最终的结果。

04

示例4:计算员工的年假天数

某公司的年假规则为:

非正式员工不享受年假;正式员工有5天年假,女性多3天,工龄满10年多5天,年龄满40多两天。

按照这个规则要是用IF函数去计算年假的话,很多人估计能晕掉,而用逻辑值计算的公式就非常简单。

公式:=(C2="是")*(5+(B2="女")*3+(E2>=10)*5+(D2>=40)*2),结果如图所示。

这个公式是A*B的形式,A是(C2="是"),B是(5+(B2="女")*3+(E2>=10)*5+(D2>=40)*2)。

因为正式员工是年假的首要条件,也就是说比较运算(C2="是")的结果为TRUE时,才会根据其他条件去计算年假天数,比较运算(C2="是")的结果为FALSE时,年假天数直接为0。

具体的年假天数是根据四个规则计算后相加而来的。

规则1:正式员工享受5天,可以直接记为5;

规则2:女性多3天,可以用(B2="女")*3得到;

规则3:工龄够10年多5天,可以用(E2>=10)*5得到;

规则4:年龄满40多两天,可以用(D2>=40)*2得到。

将这四部分相加后再与(C2="是")相乘,就得到了公式=(C2="是")*(5+(B2="女")*3+(E2>=10)*5+(D2>=40)*2)。

逻辑值虽然只有TRUE和FALSE两个,但在实际应用中可以实现出千变万化的效果,看似简单,实则需要大量的练习才能运用自如。

扫一扫,在线咨询Excel课程

Excel教程相关推荐

30秒录入800条员工信息的Excel神技巧,分享给你!
公司资深HR写了一个公式,瞬间搞定500个员工的年假时长核算!
“月薪2k的实习生,Excel单元格标色竟比我快还好看?”
常用Excel函数的避坑指南,建议收藏!

想要全面系统学习Excel,不妨关注部落窝教育的《一周Excel直通车》视频课或者《Excel极速贯通班》。

主讲老师:滴答

 

Excel技术大神,资深培训师;

课程粉丝100万+;

开发有《Excel小白脱白系列课》

《Excel极速贯通班》。

原价299元

限时特价 99 元

少喝两杯咖啡,少吃两袋零食

就能习得受用一生的Excel职场技能!

(0)

相关推荐