使用Excel的朋友都知道,經常會碰到的一個操作那就是數據查詢。如果是簡單的數據查詢,我們使用vlookup、lookup或者其他的一些基本查詢函數就可以實現。但是工作中我們會發現有一種查詢經常會出現,但也是非常麻煩的一種,那就是一對多查詢。
今天我們就來詳細學習一下Excel中全部4種一對多查詢方法。
案例:通過部門名稱,查找出該部門下面的所有成員說明:如上圖,我們需要根據部門名稱,查找出銷售二部對應的所有成員。下面我們就來學習一下最快捷的4種方式。
方法一:vlookup函數一對多查詢講解:vlookup函數實現一對多查詢時,需要操作的難點在於需要做一個輔助列,計算對應部門的數量,通過數量來匹配對應的人名。
1.1 輔助函數為:
=COUNTIF(B$2:B2,G$4)
函數解析:如上圖,作用在於計算我們每一個部門出現的次數,銷售二部前面對應都促使為1-4。
1.2 查詢函數為:
IFERROR(VLOOKUP(ROW(A1),A:D,3,0),"")
函數解析:做出輔助列函數後,我們就可以通過前面的序號進行查詢數據。row(A1)=1的作用在於返回當前行數,往下拖動函數就會出現1、2、3、4...等等。這樣就實現了通過輔助列的序號來查詢數據。
二、index+small+if+row混合函數一對多查詢講解:這裡用到了index+small+if+row混合函數搭配使用查詢的方式,對於新手來講可能會比較蒙,下面我們來詳細進行一些函數解析:
查詢函數為:
{INDEX(B:B,SMALL(IF($A$2:$A$11=F$4,ROW($2:$11),4^8),ROW(F1)))&""}
函數解析:
1、index(B:B,xx):這個函數為返回B列從上往下的第幾個值。有兩個參數,B:B為我們需要查詢的位置,第二參數為需要查詢值的位置。
2、SMALL(A,B):small函數代表的是取出期間數字中的最小的一個值。A,B為對應的參數,可以為任意個。
3、IF($A$2:$A$11=F$4,ROW($2:$11),4^8)
代表黨查詢的區域有查詢的值的時候,返回當前值所在行的值。否則返回0,為了消除0的影響,所以我們最後用連接“”來取消。
三、VBA自定義Nlookup函數進行一對多查詢講解:通過自定義函數,我們可以查找出姓名為王五當月全部銷售數據。
查詢函數:
=NLOOKUP(K4,B1:F15,5,-1)
函數解析:
這是VBA自定義的函數,所以功能上面我們第四參數設置的是-1,來進行全部查找。代碼如下:
講解:這種方法主要是通過數據透視表刷選的方式來進行的,功能和數據篩選是一樣的。如果是數據比較少的情況下,我們可以直接點擊篩選特定的關鍵詞就可以實現這種操作。
現在你掌握瞭如何進行數據一對多查詢了嗎?
閱讀更多 Excel函數與VBA實例 的文章