13进阶技巧之一对多查询 [复制链接]

hello~朋友们，有一段时间没见面啦，我一直都在，也会持续给大家更新一些更实用的内容。

感谢大家不离不弃呀~

今天就分享点很有意思的进阶查询技巧：一对多查询。

什么意思呢？

比如你要查看

公司各个部门员工都有哪些？

员工一共授予期权次数？

客户合同续签的情况？

甚至是倩倩的前男友中，有哪些是渣男？

有了这个技能，都是小问题~

先看案例：（喜欢《权游》的小伙伴应该很熟悉哈~）

动画效果

效果还可以吧~

无论需求多奇葩，一步一步实现它~

我们要写的公式

公式1：

=A2COUNTIF($A$2:A2,$E$2)

公式2：

=IFERROR(VLOOKUP(E$2ROW(B1),$B$2C$19,2,0),"")

---------------（我是分割线）---------------

第一步：原材料准备，需求拆解

“家族”所在列用的是数据验证，下拉选择，不会做评论区见吧~

要用到的4个函数：COUNTIF()IFFERROR()ROW()还有我们熟知的VLOOKUP()

不会用COUNTIF()VLOOKUP()的同学查看历史推送02、03

简单说一下IFFERROR()和ROW()

IFERROR(value,value_if_error)

value是被检查是否有错的数据，错误类型包括：#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？或#NULL！。

value_if_error是如果出现上述错误，返回的内容一般我们用返回空值：""来美化一些表格错误，用法后面再细讲，不赘述。

ROW([reference])也非常简单，返回[reference]引用单元格的行号，常见用法为自动更新的编号。

这里主要功能是用来辅助生成VLOOKUP()函数的“查找值”功能。

第二步：步骤拆解

数据验证-辅助列-辅助列写入公式1-姓名列写入公式2

数据验证跳过

辅助列，在姓名前面插入辅助列，如下图

在辅助列写入公式1：

=A2COUNTIF($A$2:A2,$E$2)

向下填充之后看看发生了什么？

这里的COUNTIF()求出来的是所选家族，在A列对应区域的出现次数，注意公式里面的区域处理技巧。

$A$2:A2，后面的A2没有加“$”说明下拉是活动的区域，每多下拉一行，就多一个单元格区域。

公式的含义是A列单元格值跟COUNTIF()函数求得的值的拼接，也即是A列单元格值跟家族在活动区域出现次数的拼接。

这么做目的是利用A列重复出现的家族跟出现次数拼接，实现唯一值，方便我们姓名查找的时候直接用VLOOKUP()

在姓名列写入公式2：

=IFERROR(VLOOKUP(E$2ROW(B1),$B$2C$19,2,0),"")

向下填充公式实现动图的结果：

公式里面的：VLOOKUP(E$2ROW(B1),$B$2C$19,2,0)就是简单的查找函数应用，不多讲，不明白的小伙伴请看历史推送去。

这里注意一下里面的参数：E$2ROW(B1)这里也是一个拼接，原理一样。

公式外面套的IFERROR()主要是为了美化错误直接返回空值，我们看下不用IFERROR()的效果：

有错误值出现，因为表中的“马尔泰”家族只有3个人，所以第四个拼接结果：“马尔泰4”就没查找到，出现了#N/A报错，用IFERROR()之后直接把错误美化成空值就达到下面的效果啦

最后一步：隐藏辅助列

隐藏辅助列，实现动图内容效果。

你，看懂了吗？

下面的一个案例，我们可以通过姓名（name）字段查询一系列的信息。

以上所述知识点是做表的核心，综合学过的查找函数即可实现最终效果。

Excel除了掌握一些基础函数用法外，还是要动脑筋的。

达到各个基础函数之间的协同去实现一些比较复杂的需求，祝大家学会、用好工具，快速完成工作，不加班~！

喜欢请点再看，转发，[鞠躬~]

ExcelPro铁粉交流群~