EXCEL进阶课堂 · 函数说 持续更新!我们将为各位小伙伴提供更加专业、更加精炼、更加实用的EXCEL操作技能,帮助大家轻松解决工作任务,提高工作效率,不再做不停加班的表哥,表姐。欢迎各位小伙伴转发、点赞、讨论,更欢迎私信获取练习素材,刻意练习才能学有收获。
这是函数说的第35篇教程。不知不觉,函数说系列就写到了第35篇教程了,好像可以构成一个专栏了。
问题分析
本篇教程来自于一个悟空问友的提问。
问题原文是:如何在a列中查找包含“海城镇”,并把对应的b列数据显示在c列。
问题配图是:
![e7144bad56d4dc71ff83bfa6f7410906.png](https://i-blog.csdnimg.cn/blog_migrate/e1ebc474762e298830e28d046272eddc.jpeg)
分析问友的提问和配图,进阶君对问题的理解是:从A列当中,查找值为“海城镇”的单元格,然后将其对应的B列的数值放到C列当中。需要我们注意的是,查找值是有可能出现重复的。为了更好解决问题,并将问题变得可拓展,进阶君对数据结构进行了重构。实现查找区域,得到对应值。
![75f9b212d6de1a717cb806a6a2446e2e.png](https://i-blog.csdnimg.cn/blog_migrate/e738ae6635fceb1d1b4ab7f733e7ab45.jpeg)
问题解决
在一个区域当中查找一个值,很多小伙伴都会自然地想到VLOOKUP函数。可是这个函数在应用时,要求查找的值在查找区域的第一列,并且不能出现重复,如果出现重复,它只会得到第一次查找到的值。
反观上面的问题,要查找的区域,在区域这一列当中是有重复的,这应该如何解决呢?
(一)增加辅助列,将区域的值变为唯一值。
在问题的“区域”列当中,值重复的,这不利于VLOOKUP函数的使用。如何将其变成唯一呢?进阶君的思路是:将区域值和这个区域值出现的次数联合起来,形成诸如“海城镇1”、“海城镇2”、“海城镇3”等之类的唯一值。
于是,我们在现在A列的当面增加一个附加列,如下图所示。
![68b617213de564cd424492549491d129.png](https://i-blog.csdnimg.cn/blog_migrate/f098ff80e0cb0517d970ff3897b0289e.jpeg)
在A2单元格中输入公式:=B2&COUNTIF($B$1:B2,B2)公式分析:1.COUNTIF($B$1:B2,B2),是统计B2的值,在B1:B2单元格当中出现的次数。其中,B1是绝对引用,说明是固定不变的,而B2是相对引用,它可以随着公式复制的变化而变化。2.& 是文本连接符,B2&COUNTIF($B$1:B2,B2) 是表示将B2的值与其在相应区域中出现的位置连接在一起。
![54197335fb155abe002f71a701d5163c.png](https://i-blog.csdnimg.cn/blog_migrate/dbea474a7bc6f572d28df0fcad826484.jpeg)
(二)巧妙变化查找值,对应附加列的数据形式
通过上一步,我们已经成功地解决了区域值重复的问题。但是我们应该如何思考查找值呢?假如,我们要查找“海城镇”,按照我们构建的附加列,就应该去查找“海城镇1,海城镇2,海城镇3”等。
这个如何变化呢?我们在查找公式当中去变化。
![6e90c89d506fa9ff7cf0cf5d434a431b.png](https://i-blog.csdnimg.cn/blog_migrate/e15db0cb0b7f01c12837b9aeece5defb.jpeg)
在F2单元格当中输入公式:=VLOOKUP($E$2&ROW(A1),$A$1:$C$21,3,0)公式分析:1.$E$2&ROW(A1),这就是在改造查找值,$E$2是原本的查找值,而且是绝对引用不会随公式的复制面变化。 ROW(A1)表示,取得A1单元格的行号,它的值自然是1。 从公式的字面来看,自然应该将“海城镇”和 1 连接起来,形成“海城镇1”。 需要注意的是,随着公式的复制,ROW(A1)会变成ROW(A2)、ROW(A3)等,形成“海城镇2”、“海城镇3”等。 2.VLOOKUP函数其它的参数在这里不在讲解。公式是采取精确查找,故而在找不到的情况下会得到#N/A,仔细思考,出现#N/A就表示全部找完了。
![63ac1b047e480968726667a9c2b99780.png](https://i-blog.csdnimg.cn/blog_migrate/317fb9f3b8d717d5737f4b6701a85835.jpeg)
(二)美化显示,消灭#N/A
公式复制后,在全部都查找完毕的情况下会出现#N/A 。这个符号在公式应用当中是常见的符号,但是这让处女座们很难受,于是我们消灭它。
在F2单元格中修改公式:=IFERROR(VLOOKUP($E$2&ROW(A4),$A$1:$C$21,3,0),"")IFERROR(公式,"") 表示前面公式的查找结果为#N/A,将其显示为“”(空)。
![18c62405d0a028f9f8cf0191ab9713b0.png](https://i-blog.csdnimg.cn/blog_migrate/e773247116ae4c2990009ccf12fbe6f5.jpeg)
整个过程操作动图展示
![fa354cc17311c43ef727ea8331d7e546.gif](https://i-blog.csdnimg.cn/blog_migrate/c1f326905b4d7decb0f20173d437df4c.gif)
为了方便小伙伴们学习,进阶君将原始素材共享出来,获取素材的方法:
第一步:关注 Excel进阶课堂。
第二步:私信 Excel进阶课堂,因为设定的是自动回复,所以内容一定要准确
私信内容:练一练
第三步:根据得到信息打开网盘,找到 第35讲 VLOOKUP函数实现一对多查询 工作簿 自行下载