在昨天的文章中,小编介绍了一对多查询的四个方法,没想到读者中卧虎藏龙,提出了很多新方法。今天我们乘胜追击,再学习一对多查询新方法,让我们更上一层楼。
我们来看下源数据:
要求:查询出美国的品名及销量。
01 辅助列、index match函数法我们通过构造辅助列和index match函数,也能解决一对多查找问题。
1、在D2单元格输入公式:=COUNTIF(A$2:A2,A2)&"-"&A2
右拉下拉填充公式,我们得到如上图的结果。
公式解读:
countif函数是统计出现次数的函数。
语法是:countif(条件区域,条件)
COUNTIF(A$2:A2,A2)统计A列中每个国家出现的次数,返回出现的次数,比如A2中,"美国"是第一次出现,返回1,然后和A2进行合并字符,最终返回:1-美国。
特别注意:COUNTIF(A$2:A2,A2)是混合引用,引用之后的公式变化如下:
同理,我们看下A11单元格,结果是4-美国,因为美国是第四次出现。
如此构造辅助列的目的是将国家出现的次数,进行编号,以便下一步统计。
2、在H5输入公式:
=IFERROR(INDEX(A:A,MATCH(ROW(A1)&"-"&$F$2,$D:$D,0)),"")
右拉下拉填充公式,我们得到如下图的结果。
公式解读:
MATCH(ROW(A1)&"-"&$F$2,$D:$D,0)意思是通过行号和国家产生的合并字符,在D列辅助列进行精确查找,得出具体的位置。
INDEX(A:A,MATCH(ROW(A1)&"-"&$F$2,$D:$D,0))是通过index、match函数返回源数据中的国家、品名、销量。因为这边是从左到右,因此不用match函数进行匹配。
最后我们在函数外围套一个iferror函数,也就是容错函数。当查找不到时候,返回空值,以防函数出错。
02 Power Query法PQ法,在此做个介绍,如果是大数据用PQ是最好的,我们现在是小数据,通过PQ你也能拓展思路。我们来看下PQ怎么做‘。
1、新建一个表格,将源数据表放在一个表,将查找条件也建一个表。
如下:
2、将以上两个表都转为超级表。
选中数据,按快捷键:ctrl、T,将其转为超级表。
3、单击选项栏:数据——自表格,打开PQ编辑界面,新建源——导入查找条件的表。
4、合并查询
单击主页选项栏——合并查询,选择两个表进行关联,连接种类选择:左外部(第一个中的所有行,第二个中的匹配行)
通过合并查询,最终查得一对多数据。
pq优势在于如果数据发生变更,刷新就能得到新数据。不必重做一遍。
你学会了吗?赶紧练习一下吧。
喜欢就关注我吧,每天分享职场知识,办公技巧!