大家好， 今天和大家分享“讓您心服口服的一對多查詢”， 一對多查詢在我們工作中也是常見的事， 下面我以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))根據查找值得到查找值姓名所在的區域文本運算式，
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 Then
一對多查詢 = arr2(y, 1)
Else
一對多查詢 = ""
End If
End Function
3、操作方法
第1步：快速鍵Alt+F11打開vbe視窗
第2步：插入功能表，插入模組
第3步：複製上面的代碼，粘貼到模組的空白處，就可以在工作表儲存格使用此函數了
4、公式截圖
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 Then
一對多查詢 = arr2(y, 1)
Else
一對多查詢 = ""
End If
End Function
3、操作方法
第1步：快速鍵Alt+F11打開vbe視窗
第2步：插入功能表，插入模組
第3步：複製上面的代碼，粘貼到模組的空白處，就可以在工作表儲存格使用此函數了
4、公式截圖