Excel中一对多查询的5种方法

如下图所示,查询右侧员工编号为“45424”的所有的销售数量。

方法01

辅助列+VLOOKUP

对于查询类的问题,大家第一时间可能想到的会是VLOOKUP函数,是的小必老师给大家教一种简单的方法。

在A列前插入一列辅助列,在B2单元格中输入公式:

=COUNTIF(B$2:B2,B2)&"-"&B2,然后按Enter键完成后向下填充。

然后在G4单元格中输入公式:

=IFERROR(VLOOKUP(ROW(A1)&"-"&$H$2,$A$2:$E$13,5,0),"")

按Enter键后向下填充至没有内容为止。

方法02

高级筛选

对于一对多查询,高级筛选的功能也完成类似上面的查找。

Step-01:首先,将查找的条件列出来。条件的标题一行,条件对应在下一行,再列出要查询的字段的名称,如下图所示:

Step-02:然后单击选项卡【数据】-【高级】,在弹出的对话框中按如下设置,然后单击【确定】,如下图所示:

结果如下图所示:

方法03

万金油函数组合

对于一对多的查询,有一个专门的查询套路的组合,即:

INDEX+SMALL+IF+ROW函数组合。

在H4单元格中输入公式:

{=INDEX(D:D,SMALL(IF($F$2=A:A,ROW(A:A),65533),ROW(A1)))&""}

按组合键<Ctrl+Shift+Enter>键完成后向下填充至空白出现。

方法04

VLOOKUP

VLOOKUP函数单独也能完成。

在H4单元格中输入公式:

{=IFERROR(VLOOKUP($F$2&ROW(A1),IF({1,0},$A$2:$A$13&COUNTIF(INDIRECT("a2:a"&ROW($2:$13)),$F$2),$D$2:$D$13),2,0),"")}

按组合键<Ctrl+Shift+Enter>键完成后向下填充至空白出现。

方法05

Power Query

解决问题的方法总是多种多样的,下面以多个一对多的查询来给大家讲Power Query中的合并查询。如下图所示:

Step-01:首先将两个表同时加载到PQ里面去,如下图所示:

Step-02:然后在第二个表里单击【合并查询】,如下图所示:

Step-02:在弹出的对话框中做如下的设置,在第二个框中选择【表1】,然后选择表2里的员工编号与表1里的员工编号,联接种类为:“左外部”,最后确定。如下图所示:

Step-03:在弹出的界面中单击扩展按钮,在筛选框中选择“销售额”即可,如下图所示:

Step-04:将结果上载至表中即可:

(0)

相关推荐