难啊！这个户籍片儿警也很难解决，EXCEL却瞬间搞定了！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

今天这个帖子的内容又和合并单元格有关。

下面是一幅户籍关系清单。要求我们根据给定的身份证号码来筛选所对应的“户”中的所有人员的信息。

初看到这个题目，这应该是一个一对多的查询问题。但是由于涉及到了合并单元格，这个题目是有一定的难度的。

再仔细分析一下，解决合并单元格的问题我们有个好帮手--LOOKUP函数，因此这道题目的思路就是LOOKUP函数配合一对多经典查询函数来解决这道题目。

01

我们首先来看看如何查询户号信息。

在单元格G6中输入公式“=LOOKUP(1,0/INDIRECT("B3:B"&(MATCH($G$3,$E$3:$E$21,)+2)),$B$3:$B$21)”即可。

思路：

这是一个典型的LOOKUP函数的经典应用。

  • MATCH函数部分，首先查询给定的身份证号在E列中的位置

  • 由于有合并单元格存在，需要用INDIRECT函数来确定“户号”列的源数据区域

  • 利用LOOKUP函数返回该区域内最后一个户号信息，即给定的身份证号所对应的户号信息

如果在“户号”列中是文本信息，那么我们就需要用“座”字法来解决这个问题。详细的分析请参看帖子总结篇-LOOKUP函数实用终极帖。

02

接下来我们来看看如何查询其它人员的信息。

在单元格H7中输入公式“=IFERROR(INDEX(C:C,SMALL(IF(LOOKUP(ROW($B$3:$B$21),MATCH($B$3:$B$21,$B$3:$B$21,)+2,ROW($B$3:$B$21))=LOOKUP(MATCH($G$3,$E$3:$E$21,)+2,LOOKUP(ROW($B$3:$B$21),MATCH($B$3:$B$21,$B$3:$B$21,)+2,ROW($B$3:$B$21))),ROW($B$3:$B$21)),ROW(A1))),"")”，三键回车并向右向下拖曳即可。

思路：

  • MATCH($B$3:$B$21,$B$3:$B$21,)+2部分，利用MATCH函数来确定单元格区域$B$3:$B$21中各个数据的位置信息。如果是空值则返回错误值

  • LOOKUP(ROW($B$3:$B$21),MATCH($B$3:$B$21,$B$3:$B$21,)+2,ROW($B$3:$B$21))部分，利用LOOKUP函数为每个户号合并单元格中空值单元格也赋予了所在合并单元格的行号。其值为{3;3;3;6;6;6;6;10;10;12;13;13;15;15;17;17;17;17;17}

  • LOOKUP(MATCH($G$3,$E$3:$E$21,)+2,LOOKUP(ROW($B$3:$B$21),MATCH($B$3:$B$21,$B$3:$B$21,)+2,ROW($B$3:$B$21)))部分，简单说，就是返回了给定身份证号所对应的合并单元格所在的行号

  • 利用IF函数将上述两部分对比判断，并返回符合条件的值所对应的行号

  • INDEX函数部分就是一对多的经典查询应用了，这里不再详细解释了

  • IFERROR函数容错处理

03

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

本期阅读分享赠书书目为：

中文版Excel 2016宝典（第9版）

此书亦是我了解2016版的用书。内容详实充分，适用于小白进阶，也同样适用于作为高手的伴手书。

赠书规则：

  • 本公众号下文章“阅读最多”排名和“分享最多”排名各自第一名的朋友将会获赠一本

  • 截止时间：2021-4-11

我就知道你“在看”

戳原文，有惊喜，更有料！免费模板文档！

推荐阅读
赞 (0)

相关推荐