各位小伙伴们大家好,我是Excel归故乡。前几期给大家讲解了AND函数和OR函数,但是我们都是单独使用的,本期我们来看一下将这两个函数结合起来使用,解决更复杂的问题,同时再带着大家一起分析思路,理清逻辑,帮助大家以后在自己写复杂嵌套函数时能够游刃有余。
我们来看看本期的案例,基于下图的数据源,如果有一个及以上的产品大于90或者3个产品都大于60,则为通过,否则为不通过,请判断以下员工是否考核通过。
我们看到这个题干,是将and函数和or函数的两个案例进行了结合,现在条件放宽了,我们即照顾平均水平均衡的员工,也照顾单品突出的员工,因此两种情况任意满足一种都可以通过考核。
本例我们的思路是先分别判断两种条件是否满足,然后再根据结果判断是否满足其中一种或者两种,最后再判断考核是否通过。接下来我们来一步步将思路变成公式:
第一步:判断是否有一个产品大于90,这是我们or函数的案例,不再赘述,直接给出公式=OR(D60>90,E60>90,F60>90)
第二步:判断是否三个产品都大于60,and函数的案例,也不再赘述,公式为=AND(D60>60,E60>60,F60>60)
第三步:关键是如何判断以上两个条件满足其一即可,我们可以看到以上两步的结果是TRUE和FALSE,只要有一个TRUE则说明至少有一个条件满足,我们会发现还是在or函数的逻辑里面,所以我们可以将两次判断的结果再次作为or函数的两个参数,是不是就可以判断出是否有一个或者一个以上的条件满足了,这时候的公式就变成了:=OR(H60,I60)
第四步:我们观察到,经过最后一步的判断后,显示为TRUE的必定是有一个条件满足的,如果是FALSE必定是两个条件都蛮有满足。这时候是不是仅需一个IF函数来判断一下就知道谁通过谁不通过了=IF(J60,'考核通过','未通过')
此时我们就得到了题干要求的结果,但是使用了三个辅助列,过于麻烦,但是我们分部完成之后,只需要将公式综合在一起,就可以去除辅助列了。具体怎么去除呢,我们找到最后一步得出结果的单元格,里面的J60是引用的J60单元格里面的公式OR(H60,I60),我们就将J60里面的公式复制,直接替换掉J60,此时最后一步的公式就变成=IF(OR(H60,I60),'考核通过','未通过')。我们继续观察,发现H60和I60也是辅助列,那就用同样的方式,将H60和I60里面的公式复制出来直接替换,公式进一步变成了=IF(OR(OR(D60>90,E60>90,F60>90),AND(D60>60,E60>60,F60>60)),'考核通过','未通过')。到了这一步,我们再观察,发现已经没有引用辅助列的单元格了,全是我们数据源当中单元格,这时候我们再把辅助列删除,也不会影响公式得出结果了,最终就得到以下的结果:
以上的分析过程就是我们复杂嵌套函数的写法,首先理清楚思路,然后分步将思路用公式表达出来,最后再通过替换内容把辅助列去掉。经过这样的分析,是不是会感觉多层嵌套函数简单了许多。这种写法希望大家一定要掌握,熟练之后就可以做到辅助列在心中,很快的写出这种复杂嵌套公式了!
联系客服