二维码 购物车
部落窝在线教育欢迎您!

IF函数经典案例:判断一个单元格内是否包含指定关键词

 

作者:郅龙来源:部落窝教育发布时间:2021-10-18 10:45:59点击:392

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

Hello小伙伴们,IF函数是我们在工作中使用最频繁的函数之一。当我们想知道某一个单元格中的数据是否包含指定的关键词时,我们就可以用IF函数搭配IFERRORSUM等函数进行判断,这其中充分利用了数组公式的特性。阿硕老师沿袭了自己由浅入深的讲解风格,为我们带来一篇干货满满的教程,大家一起来学习一下吧~

 

哈喽,大家好,欢迎来到部落窝教育!我是阿硕。最近,有小伙伴问了这样一个问题:有一组关键词,我想找出包含这组关键词中任意一个关键词的单元格,该如何做?

 

案例数据如下图所示。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(如果考虑到函数向下复制填充,则依次为B2B7);

FIND函数的第三参数为1,即从首字符开始查询。同时,要谨记,输入函数之后,要按Ctrl+Shift+Enter构造数组。

 

接下来,我们修改一下刚才所写的FIND函数。由于在本例中,我们要查询的关键词始终是C2:C3中的数据,所以为了防止向下复制填充公式的时候参数发生变化,我们需要对C2:C3做一下绝对引用。我们将D2中的函数公式修改为=FIND($C$2:$C$3,B2,1)”,改好之后,按Ctrl+Shift+Enter构造数组。然后,我们向下复制填充公式至D7,得到的结果如下图所示。

 

 

这里,有一点需要大家要注意:由于我们要在其中查询关键词的单元格是变化的,在本例中依次为B2B7,所以D2FIND函数公式中的第二参数,不需要加绝对引用,直接使用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构造数组,然后再向下复制填充公式,得到的结果如下图所示。

 

 

此时,D2D7中的内容,已经是用IFERROR函数修正过之后的数据总和,也就是关键词在B2B7中相应的位置序号的和,由于我们刚刚做了求和运算,所以D2D7中现在只保存一个数据了。

 

这里,也有一点需要注意,虽然D2D7中每个单元格内只保存一个数据,但它们依然是数组,只不过每个单元格中数组存放的数据数量是1而已。

 

下面,让我们来观察一下数据中的规律:如果员工经过长春和唐山这两个城市之一,那么D2D7中相应单元格中的数据一定是大于0的,如果这两个城市均未经过,则D2D7相应单元格中的数据为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列删除后,得到的结果并不受影响,如下图所示。

 

 

好了,各位小伙伴,我们今天的学习内容就是这些,你学会了吗?

最后,给大家留一个小作业:请对经过沈阳、哈尔滨、黑河这三个城市的员工进行标记。一定要试着做一下哦!

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

7个Excel小技巧,提高表格查看效率

Excel运用规范1:一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式!(建议收藏)

版权申明:

本文作者阿硕;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。