一个公式中大凡用到if和and、or三个函数,说明给出的条件会相对复杂,需要进行多重判断。如在下图中,题主需要设定条件来计算各业务员的提成:业绩10万以下,提成2%;业绩10-20万,提成3%;业绩20万以上,或者业绩大于10万且业务组平均业绩大于20万提成4%。

Excel中if嵌套函数与and、or条件判断函数的混合使用方法-天天办公网

我们可以看到条件中“大于等于且小于20万”,包含“且”的表述,则表示要同时符合两个条件。在excel中,and函数可以用来判断两个条件是否同时成立,如成立,则返回结果“true”,不成立则返回结果“false”。

Excel中if嵌套函数与and、or条件判断函数的混合使用方法-天天办公网

and函数的参数可以是两个及两个以上,而且参数要属于条件判断,能够判断其结果是否为真或为假。

而在条件“业绩20万以上或者业务组平均业绩大于20万”,其中出现了“或者”,也就是说两者之一任何一个成立,则该条件成立,在excel中可以用or函数来表达。

Excel中if嵌套函数与and、or条件判断函数的混合使用方法-天天办公网

or函数的含义与and函数相反,但参数和使用方法是一样的。

了解了and和or函数的含义,我们根据条件设置来写公式,如图所示:

Excel中if嵌套函数与and、or条件判断函数的混合使用方法-天天办公网

=AND(C3>100000,C3<200000)

表示C3大于10万且小于20万;

=OR(C3>200000,AVERAGEIF($A$3:$A$15,A3,$C$3:$C$15)>200000)

表示C3大于20万,或者该员工所属业务组的平均业绩大于20万。

AVERAGEIF函数的含义是给定条件计算平均值,它的表达式为:=AVERAGEIF(条件区域,条件,计算区域)。

在这个公式中,表示计算业务组1的平均业绩。

Excel中if嵌套函数与and、or条件判断函数的混合使用方法-天天办公网

计算出了业务组的平均业绩,还需要通过一个判断式来返回一个真值或假值,也就是”>200000“。

有了这两个条件的表达式,那么写下完整的公式就比较容易了。

通过学习if函数的用法,我们将最后一个条件作为它的第1个参数,第2个参数也就是条件为真返回的结果,即0.04,然后第三个参数包含了两个条件,一个是小于10万,一个是大于10万且小于20万,因此还需要嵌套一个if函数。

于是完整的公式如下:

=IF(OR(C3>200000,AVERAGEIF($A$3:$A$15,A3,$C$3:$C$15)>200000),0.04,

IF(AND(C3>100000,C3<200000),0.03,0.02))

Excel中if嵌套函数与and、or条件判断函数的混合使用方法-天天办公网

我们来捋一下这个公式的逻辑,首先是如果C3大于20万,或者所属业务组平均业绩大于20万,则返回结果0.04;

如果不符合这个条件,那么就会返回第3个参数的结果。而第3个参数也是一个if判断函数,我们再继续解析。

如果C3大于10万且小于20万,那么结果为0.03,否则就等于0.02。

也就是讲首先要看是不是满足第1个参数,即是不是大于20万,或者所属业务组平均业绩大于20万,如果不满足,才看下一个条件:是不是大于10万或者20万,如果还不满足,那只能是小于10万了。

所以其实也可以看出,if函数是有逻辑顺序的,使用多个if函数的嵌套时,要注意它逻辑的先后顺序。

最后,我们再总结一下今天的内容,主要是弄明白and和or函数的含义和用法,然后再捋清if嵌套函数的逻辑顺序。在较长的公式中,我们也要学会擅用F9快捷键来逐个解析公式中各函数的值,以加深对公式理解。