大家好，今天和大家分享“让您心服口服的一对多查询”，一对多查询在我们工作中也是常见的事，下面我以4种方法来讲解给大家
一、把查询结果放在一个单元格里
1、动画操作效果
2、公式截图
3、公式|更多Excel学习和问题请加群：289393114、570064677
=MID(SUBSTITUTE(PHONETIC(INDIRECT('A'&MATCH(,0/($A$1:$A$9=D1),)&':B'&MATCH(1,0/($A$1:$A$9=D1)))),D1,','),2,99)
4、公式解释
要注意两点，第1点A列要排序，让相同的姓名挨在一起；第2点B列要是文本型的数字，因为函数PHONETIC连接数字时会显示空
'A'&MATCH(,0/($A$1:$A$9=D1),)&':B'&MATCH(1,0/($A$1:$A$9=D1))根据查找值得到查找值姓名所在的区域文本表达式，外面嵌套的一indirect函数，让单元格区域文本表达式变为单元格区域，因为PHONETIC只支持单元格区域
MATCH(,0/($A$1:$A$9=D1),)这个公式的Match找第一个曹丽出现的位置
MATCH(1,0/($A$1:$A$9=D1))这个公式的Macth找最后一个曹丽出现的位置
用substitute把曹丽替换成逗号，然后用mid函数从第2个位置提取，提取99个，目的就是让第1个逗号不要
二、把查询结果纵向显示
1、公式截图
2、公式
=INDEX(B:B,SMALL(IF($A$1:A9=$D$1,ROW($A$1:A9),2^20),ROW(A1)))&''
3、公式解释
这个不要求A列排序，也不要求B列是文本型的数值
IF($A$1:A9=$D$1,ROW($A$1:A9),2^20)判断区域是否有等于“曹丽”，如果有就返回区域的行号，否则就返回２＾２０也就是1048576，2010版本最大的行号
通过Small函数下拉，把第几小提取出来
用index函数从B列把相应的数据引用出来
三、把查询结果横向显示
1、公式截图
2、公式
=INDEX($B:$B,SMALL(IF($A$1:$A$9=$D$1,ROW($A$1:$A$9),2^20),COLUMN(A1)))&''
3、公式解释
和上面的公式原理一样，也是数组公式，记得复制好公式之后，要把光标点到编辑栏里，然后三键一齐下Ctrl+Shift+Enter
由于是右拉，所以上面的公式Small第2参数Row(A1)改成Column(A1)
四、Vlookup函数实现一对多查询
1、公式截图
2、公式
=IFERROR(VLOOKUP($D$1&ROW(A1),CHOOSE({1,2},$A$1:$A$9&COUNTIF(INDIRECT('A1:A'&ROW($A$1:$A$9)),$D$1),$B$1:$B$9),2,0),'')
3、公式解释
首先申明，如果现在现实工作中，不建议你用这种方法，这种方法练习可以。工作中建议大家采用上面2种方法
公式$A$1:$A$9&COUNTIF(INDIRECT('A1:A'&ROW($A$1:$A$9)),$D$1)这个公式难理的地方在这里INDIRECT('A1:A'&ROW($A$1:$A$9))，用了多区域，这里有9个区域，A1一个区域；A1:A2一个区域；A1:A3一个区域；A1:A4一个区域；A1:A5一个区域；A1:A6一个区域；A1:A7一个区域；A1:A8一个区域；A1:A9一个区域；
用countif函数统计这9个区域里的曹丽的个数，统计的结果相当于给每一个姓名编了号一样，从1开始编号，然后在前面连接A列的姓名，这样得到把相同的姓名变成不相同了
最后用choose函数把连接的结果放在第1列，数量放在第2列，便可以用 vlookup函数引用了
五、vba自定义函数实现
1、动画效果
2、代码
Option Explicit
Function 一对多查询(SRg As Range, Rg1 As Range, y&)
Dim arr1, arr2(1 To 1000, 1 To 1), k, x
arr1 = SRg
For x = 1 To UBound(arr1)
If arr1(x, 1) = Rg1 Then
k = k + 1
arr2(k, 1) = arr1(x, 2)
End If
Next x
If y <= k="">
一对多查询 = arr2(y, 1)
Else
一对多查询 = ''
End If
End Function
3、操作方法
第1步：快捷键Alt+F11打开vbe窗口
第2步：插入菜单，插入模块
第3步：复制上面的代码，粘贴到模块的空白处，就可以在工作表单元格使用此函数了
4、公式截图
联系客服
