编按
Hello小伙伴们,IF函数是我们在工作中使用最频繁的函数之一。当我们想知道某一个单元格中的数据是否包含指定的关键词时,我们就可以用IF函数搭配IFERROR、SUM等函数进行判断,这其中充分利用了数组公式的特性。阿硕老师沿袭了自己由浅入深的讲解风格,为我们带来一篇干货满满的教程,大家一起来学习一下吧~
哈喽,大家好,欢迎来到部落窝教育!我是阿硕。最近,有小伙伴问了这样一个问题:有一组关键词,我想找出包含这组关键词中任意一个关键词的单元格,该如何做?
扫码入群,下载Excel文件练习
案例数据如下图所示。A列是员工的姓名;B列是员工出差所经过的城市;C列是要查询的城市,也就是本例中我们要查询的关键词。
在本例中,我们以两个关键词为例,分别是“长春”和“唐山”,如果员工经过长春或者唐山,那么我们就将该员工标注为“经过”,否则标记为“未经过”。
下面,就让我们开始今天的学习吧!
首先,我们在D2中输入 “=FIND(C2:C3,B2,1)”,输入之后,我们按下Ctrl+Shift+Enter,完成数组公式的输入,得到的结果如下图所示。
FIND数组函数解析
1.由上图我们可以看到,D2单元格显示的数据是“4”。但是,由于刚才我们是使用数组方式来输入的函数,所以此时我们要注意了,D2中实际存放的是一个数组。
下面我们来对D2中的内容一探究竟。我们先单击选中D2单元格,再去函数编辑栏内选中我们刚才写的FIND函数,然后按下F9键,就可以查看D2中的内容,如下图所示。(注:按ESC键即可退出查看。)
我们可以看到,D2中实际的内容是“{4;#VALUE!}”,实际上它是由两个数据构成的一个数组。那么,这两个数据,是怎么来的呢?
2.我们刚才写下的FIND函数的第一参数是C2:C3,即为我们要查询的两个关键词“长春”和“唐山”,所以此处D2中的内容,就是FIND函数返回的“长春”和“唐山”在B2中的位置序号,这两个位置序号构成了一个数组。
我们可以用“手工”判断一下:“长春”在B2中出现在第4个字符处,所以构成数组的第一个数据是“4”;“唐山”在B2中未出现,所以FIND函数定位不到“唐山”的位置,于是返回了一个错误值“#VALUE!”,相应地,构成数组的第二个数据是“#VALUE!”。
小结:
FIND函数的第一参数,是我们要查询的一组关键词,在本例中为C2:C3;
FIND函数的第二参数,是要在其中定位关键词的单元格,在本例中为B2(如果考虑到函数向下复制填充,则依次为B2至B7);
FIND函数的第三参数为1,即从首字符开始查询。同时,要谨记,输入函数之后,要按Ctrl+Shift+Enter构造数组。
接下来,我们修改一下刚才所写的FIND函数。由于在本例中,我们要查询的关键词始终是C2:C3中的数据,所以为了防止向下复制填充公式的时候参数发生变化,我们需要对C2:C3做一下绝对引用。
我们将D2中的函数公式修改为“=FIND($C$2:$C$3,B2,1)”,改好之后,按Ctrl+Shift+Enter构造数组。然后,我们向下复制填充公式至D7,得到的结果如下图所示。
这里,有一点需要大家要注意:由于我们要在其中查询关键词的单元格是变化的,在本例中依次为B2至B7,所以D2中FIND函数公式中的第二参数,不需要加绝对引用,直接使用B2作为第二参数即可。
小伙伴们可以通过F9键来查看D2:D7中的内容。为了便于大家理解,阿硕为大家做了整理,如下图所示。
接下来,我们对“#VALUE!”进行一下修正。对于“#VALUE!”或“#N/A”等错误值,我们可以用IFERROR函数来修正。
在本例中,我们把“#VALUE”替换成0。我们将D2中的公式修改为“=IFERROR(FIND($C$2:$C$3,B2,1),0)”,然后按Ctrl+Shift+Enter构造数组,然后再向下复制填充公式,得到的结果如下图所示。
有的小伙伴可能会问,为什么要用0来修正?这是因为,在接下来的步骤中,我们将要进行求和运算,如果使用其他数据来修正的话,会影响求和结果,只有使用0来修正才不会影响求和的结果。
此时,如果我们再来查看D2:D7中的内容,就会发现,其中的数据内容已经发生了变化,如下图所示。
好了,接下来,我们对D2:D7中的内容来求和,我们将D2中的公式修改为“=SUM(IFERROR(FIND($C$2:$C$3,B2,1),0))”,然后按Ctrl+Shift+Enter构造数组,最后再向下复制填充公式,得到的结果如下图所示。
此时,D2至D7中的内容,已经是用IFERROR函数修正过之后的数据总和,也就是关键词在B2至B7中相应的位置序号的和,由于我们刚刚做了求和运算,所以D2至D7中现在只保存一个数据了。
这里,也有一点需要注意,虽然D2至D7中每个单元格内只保存一个数据,但它们依然是数组,只不过每个单元格中数组存放的数据数量是1而已。
下面,让我们来观察一下数据中的规律:如果员工经过长春和唐山这两个城市之一,那么D2至D7中相应单元格中的数据一定是大于0的,如果这两个城市均未经过,则D2于D7相应单元格中的数据为0。找到这个规律后,距离胜利就不远了,我们只需用IF函数来判断一下就行啦!
我们将D2中函数公式修改为“=IF(SUM(IFERROR(FIND($C$2:$C$3,B2,1),0))>0,"经过","未经过")”,然后按Ctrl+Shift+Enter构造数组,然后再向下复制填充公式,得到的结果如下图所示。
好了,至此,查询一组关键词的方法,就讲完啦!
小彩蛋
有的小伙伴可能会觉得C列摆在数据表中有点碍眼,想问问如果把C列去掉,能不能查询一组关键词。答案是能!下面我们来讲一下。
我们可以单击选中D2单元格,然后在函数栏中,选中“$C$2:$C$3”这一段(注意:只选这一段代码即可,千万别多选或者少选),然后按下F9键,如下图所示。
此时,“$C$2:$C$3”变成了“{"长春";"唐山"}”,也就是我们要查询的关键词本身(之前我们那个方式是引用C2:C3中的内容)。相应地,D2单元格中的公式变成了“=IF(SUM(IFERROR(FIND({"长春";"唐山"},B2,1),0))>0,"经过","未经过")”,然后我们按Ctrl+Shift+Enter构建数组并向下复制填充公式,得到的结果如下图所示。
这时,我们就可以将C列删除了。可以看到,将C列删除后,得到的结果并不受影响,如下图所示。
好了,各位小伙伴,我们今天的学习内容就是这些,你学会了吗?
最后,给大家留一个小作业:请对经过沈阳、哈尔滨、黑河这三个城市的员工进行标记。一定要试着做一下哦!