爱刨根知识网

  1. 首页 > 经验知识 > >

不用辅助列一对多查询

生活经验不用

举个例子 ， 左边是公司各部门的员工名单数据 ， 现在我们需要根据部门 ， 来查找匹配出该部门下的所有员工姓名 ， 如下所示：

不用辅助列一对多查询

文章插图
方法一：Vlookup+辅助列
首先我们来看下添加辅助列的情况 ， 我们在A列前面插入一列 ， 命名为辅助列 ， 然后在辅助列输入的公式是：
=C2&COUNTIFS($C$2:C2,C2)
不用辅助列一对多查询

文章插图
counifs是计数公式 ， 我们第一个C2进行固定引用 ， 当我们将公式下拉填充的时候 ， 起始数据区域还是C2 ， 结束匹配是C6 ， 也就是从C2到C6里面找 ， 有多少个市场部的 ， 这个时候已经是第3个市场部了 ， 所以结果是市场3
不用辅助列一对多查询

文章插图
通过辅助列的构建 ， 我们得到了唯一值
所以我们需要查找各个部门的第1个员工的话 ， 就把查找值改成F2&1 ， 既市场1的员工
=VLOOKUP(F2&1,$A:$D,4,0)
不用辅助列一对多查询

文章插图
如果想查找第2个员工的话 ， 就是市场2 ， 第3个就是市场3对应的员工 ， 为了让我们公式能够向右填充 ， 所以我们使用Column(A1)公式来替代数字1 ， Column()公式是返回单元格在第几列,A1在第1列 ， 所以结果是1 ， 
然后E1单元格要按3下F4进行固定引用列的位置 ， IFERROR公式用来屏蔽错误值 ， 所以最后我们使用的公式是：
=IFERROR(VLOOKUP($F2&COLUMN(A1),$A:$D,4,0),"")
不用辅助列一对多查询

文章插图
2、Vlookup公式不用辅助列
如果说我们想不用辅助列 ， 要用VLOOUP实现一对多查询的话 ， 直接使用公式：
=IFERROR(VLOOKUP($E2&COLUMN(A1),IF({1,0},$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$E2),$C$1:$C$100),2,0),"")
不用辅助列一对多查询

文章插图


接下来是理解过程：
其实和插入辅助列一样 ， 首先要构建一个虚拟数组 ， 也就是说累计计数虚拟存在 ， 但是不体现在单元格中 ， 我们用虚拟数组构建的公式是：
$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$E2) ， 为了方便理解 ， 我们把这个公式放在单元格 ， 就得到了如下的一个虚拟数组
不用辅助列一对多查询

文章插图
以上只得到了查找列 ， 我们还要把结果列放进行 ， 所以我们用IF({1,0})来构建 ， 公式为：
=IF({1,0},$B$1:$B$100&COUNTIF(INDIRECT("b1:b"&ROW($1:$100)),$G2),$C$1:$C$100)
不用辅助列一对多查询

文章插图
最关键的就是解决了第二参数 ， 虚拟数据源的构建 ， 最后就是我们只需要用VLOOKUP ， 对查找值+COLUMN公式 ， 就能得到我们想要的结果了
=IFERROR(VLOOKUP(查找值&COLUMN(),虚拟数组,2,0),"")
3、FILTER+TRANSPOSE
如果你的EXCEL版本够高 ， 我们就可以用FILTER+TRANSPOSE公式 ， 快速查找 ， 我们输入的公式是：
=TRANSPOSE(FILTER(C:C,B:B=E2))
不用辅助列一对多查询

推荐阅读


上一篇：微博经常访问那一栏删掉，微博经常访问怎么关闭这个功能2022

下一篇：枣庄金钻脚轮厂家，枣庄金钻公馆靠谱吗