龙空技术网

没有如果的话该怎么办,EXCEL IF逻辑判断函数是办公居家必备良药

EXCEL家 223

前言:

今天同学们对“多层是指”大概比较着重,你们都需要了解一些“多层是指”的相关文章。那么小编同时在网摘上网罗了一些关于“多层是指””的相关文章,希望各位老铁们能喜欢,看官们一起来了解一下吧!

Hi,

今天我们聊聊IF函数。人数没有如果,但是EXCEL有。

没有如果让我想到了梁金茹的《没有如果》,但是未找到原版的,凑合着听吧。

在任何编程语言中,逻辑判断是必不可少的。逻辑判断决定了流程的走向。

几乎所有的语言都用IF做逻辑分支判断,EXCEL也不例外。虽然说EXCEL不是标准的程序语言,但是在EXCEL中提供了IF函数来进行逻辑判断。

一、IF的原理

在英文中,if的意思是如果。我们也经常说如果怎么怎么样,那么怎么怎么样,否则怎么怎么样。这种是比较自然的逻辑表达。

换成计算机语言的描述就是,当某个或者某些个条件成立,就执行一组语句或者程序,否则执行另外一组。

EXCEL中,IF函数的语法也非常简单:

IF(logical_test, value_if_true, [value_if_false])

IF(条件判断,条件成立时执行什么,条件不成立时执行什么)。非常简单。

我们在EXCEL中,IF是相对常用的一个函数,用于条件判断及执行。

二、IF的常见用法

1.简单if语句

即执行如果什么成立,则怎样,否则则怎样。

例如,如果你的考试成绩大于等于60分,则及格,否则为不及格。

E3单元格公式=IF(D3<60,"不及格","及格")

2.简单单向嵌套if语句

即外面一个if函数,里面还有好多层if函数,用于复杂的逻辑判断与执行。

例如,还是刚才那个例子,对考试成绩进行分类。规则是小于60分,为不及格,60-70,为及格,70-80为中,80-90为良,90-100为优。

这个例子中,用单一if语句已经搞不定,我们要用嵌套的if语句进行多层的逻辑判断。

写法1:

=IF(D3<60,"不及格",IF(D3<70,"及格",IF(D3<80,"中",IF(D3<90,"良","优"))))

说明先判断成绩是否小于60,为真,则显示不及格,为否后,则执行下一层if判断。

下一层if判断是在上一层的基础上进行,即D3成绩<60不成立(D3>=60),然后判断是否小于70,成立则显示为及格。在外层IF的作用下,D3<70实际上指的是60<=D3<70。这个数值区间我们定义为“中“。

后面的计算以此类推,逐渐深入到最后一层。每一层IF都有个前提条件,就是外层IF执行的结果和条件传递。

理解这一点非常关键。因为这种隐含的条件,可以简化我们的公式写法。例如,第二层if我们就不用显示的将条件写出来,比如写成这样IF(and(D3>=60,D3<70),”及格”,If(…))

写法2:

这种写法尤其适合对数据按大小进行分档划分。当然我们也可以从大往小了进行判断。

例如:

G3单元格

=IF(D3>=90,"优",IF(D3>=80,"良",IF(D3>=70,"中",IF(D3>=60,"及格","不及格"))))

如果不管继承外层If的隐含条件,要将所有条件补齐,不是不可以,但会使得函数写得相当复杂,缺乏逻辑性。结果如下:

3.复杂多向嵌套if语句

当我们的有多个判断条件要同时成立或者部分成立时,这时候的判断就会很复杂,这个时候我们就要先要梳理好逻辑关系,然后if结合其它逻辑判断函数and,or,not进行综合性判断。

例如:

我们有份工资表,奖金是按考核等级,并结合学历来进行发放的。现在要计算奖金,并计算实发工资。奖金规则如下:

学历本科及以上,考核为A,绩效奖金为基本薪酬的20%;学历本科及以上,考核为B,绩效奖金为基本薪酬的10%;学历本科及以上,考核为C,绩效奖金为0;学历本科及以上,考核为D,绩效奖金为基本薪酬的-5%;学历本科以下,考核为A,绩效奖金为基本薪酬的15%;学历本科以下,考核为B,绩效奖金为基本薪酬的5%;学历本科以下,考核为C,绩效奖金为0;学历本科以下,考核为D,绩效奖金为基本薪酬的-5%;

具体数据表如下图:

应该注意到规则里面,首先要判断学历,然后接着判断考核等级。至少有8个分支。2中学历构成*4种考核结果。

参考公式如下:

G3单元格公式=

=IF(AND(C3<>"大专",E3="A"),20%*D3,IF(AND(C3<>"大专",E3="B"),10%*D3,IF(AND(C3<>"大专",E3="C"),0%*D3,IF(AND(C3<>"大专",E3="D"),-5%*D3,IF(E3="A",15%*D3,IF(E3="B",5%*D3,IF(E3="C",0*D3,-5%*D3)))))))

这里大概嵌套了7层。公式逻辑简单,但写法复杂。如果if嵌套超过4层机会显得很复杂。其实我们应该再观察一下规则,来简化一下公式。

可以注意到考核为C或者D,其绩效奖金的规则是一样的,即和学历无关。

因此我们可以考虑先判断学历是否为C,或者D,然后再判断学历,这样公式就被简化了。

两种写法的结果都是一样的:

新IF公式嵌套写法如下:

F3单元格

=IF(E3="C",0,IF(E3="D",-5%*D3,IF(AND(C3="大专",E3="A"),15%*D3,IF(AND(C3="大专",E3="B"),5%*D3,IF(E3="A",20%*D3,10%*D3)))))

相比较而言,新写法先把通用分类C和D先判断掉,因为它们的奖金规则和学历无关,后面再处理AB类以及学历的组合判断。

三、IF的限制与局限

据说IF最多运行嵌套的层数为8层,我自然没有试过,因为超过8层的公式写起来就太复杂。除非万不得以,我是不愿意直接写。即使写出来,要去判断是否写错了,也是相对费力。

因此,建议通过辅助列或者其它函数来进行简化。我们要将复杂的事情简单化,这样极容易理解也不会出错。如何优雅地写出最简化的EXCEL公式才是我们的本身,过于复杂的东西就越不稳定。

还是上面的例子,现在给出其它数据处理方式:

思路:构建一个规则判断矩阵,然后利用Vlookup进行处理。

这个规则判断矩阵就是将文本的规则表格化,每一行代表一条最明细的规则。利用vlookup定位到最明细的规则,从而获取到奖金比例。

合并列中用&将学历和考核等级连接在一起,然后写入vlookup公式:

F3单元格

= VLOOKUP(C3&E3,$H$3:$K$18,4,0)*D3

具体动图操作如下:

最后,想说的就是再强调一点:不要一味追求复杂,仿佛写出复杂的公式有多牛。其实不然,复杂的公式就像是一段程序,需要反复调试,测试结果是否正确。如果我们有更为简洁的方法,过程就更为可控,结果也更为准确。同时,我们的思维压力也没那么大。

我是华哥。每日精进,不负光阴韶华。

赠人玫瑰,手留余香。欢迎评论,转发。

标签: #多层是指