https://www.toutiao.com/article/7165856908442976809/?log_from=72042d59b598a_1679282322819
所謂一對多，就是一個查找值對應(yīng)了多個結(jié)果值，如何使用vlookup函數(shù)來完成一對多的查找引用，是我們要學(xué)習(xí)的進階課題。
一對多查找又有兩個常用方法，一個是創(chuàng)建輔助列來提取，另一個則是嵌套其他函數(shù)的組合公式引用。
一對多匹配引用是excel表格比較常見的一個場景，但也成為許多使用者的難題。
在過去通常要利用嵌套多個函數(shù)的數(shù)組公式來達到匹配效果，但隨著excel版本更新，新函數(shù)filter可以一步到位，快速提取所有符合條件的值。
但今天作者還是以過去應(yīng)用較多的vlookup函數(shù)來介紹具體匹配引用方法。
首先還是繼續(xù)介紹創(chuàng)建輔助列來一對多查詢。
1、創(chuàng)建輔助列
創(chuàng)建輔助列的作用是為了獲取一個唯一不重復(fù)的查詢列表，因此結(jié)合原查詢列表和countif函數(shù)計數(shù)值，得到新的查詢列。
公式為：=B2&COUNTIF($B$2:B2,B2)
接下來直接設(shè)置vlookup函數(shù)公式：
=VLOOKUP($E$5&COLUMN(A1),$C:$D,2,0)
其第1參數(shù)查找值使用了column函數(shù)，它的作用是返回單元格的列序號，比如COLUMN(A1)等于1，COLUMN(B1)則等于2；
第2參數(shù)查找區(qū)域以輔助列為查詢列，然后返回銷量列對應(yīng)的數(shù)據(jù)。
這個公式的特點，就是通過查找值與column函數(shù)的結(jié)合，來動態(tài)引用查找值對應(yīng)的多個結(jié)果。
如下圖所示，由于是橫向拖動公式，因此嵌套column函數(shù)，將依次得到1、2、3、4的列序號值，而輔助列中查找值與1-4的合并數(shù)據(jù)，都是唯一不重復(fù)的，因而得以被vlookup函數(shù)進行動態(tài)引用。
既然可以橫向引用，自然也可以縱向引用，只需要將column函數(shù)替換為引用行序號的row函數(shù)即可！
公式為：=VLOOKUP($E$6&ROW(A1),$C:$D,2,0)
從這樣一個案例來看，其實vlookup函數(shù)的一對多應(yīng)用非常簡單，沒有任何復(fù)雜的地方，都是基礎(chǔ)函數(shù)的常見用法。
但接下來要介紹的第二種一對多公式寫法，就相對繁瑣了，其中嵌套了多個函數(shù)，執(zhí)行數(shù)組運算，理解起來需要費些腦細胞。
2、組合公式引用
通過一個公式怎么快速引用所有的結(jié)果值，首先要思考解題的思路。
有了創(chuàng)建輔助列解答的經(jīng)驗，其實我們可以借鑒countif函數(shù)獲取一個唯一值列表。
但countif函數(shù)輸出的是一個結(jié)果，如何使一個結(jié)果轉(zhuǎn)換成一個列表，則是問題的關(guān)鍵。
這里作者利用row函數(shù)來獲取一個數(shù)字列表，然后結(jié)合indirect函數(shù)的單元格引用，來創(chuàng)建一個單元格列表，再利用countif函數(shù)計數(shù)時，將會得到一個數(shù)組結(jié)果。
到了這一步，其實已經(jīng)能夠獲取不重復(fù)的唯一值列表。
但語言描述太籠統(tǒng)，下面直接上公式：
=VLOOKUP($D$5&COLUMN(A1),IF({1,0},$B$2:$B$29&COUNTIF(INDIRECT("b2:b"&ROW($2:$29)),$D$5),$C$2:$C$29),2,0)
這個公式組合了if數(shù)組、countif、indirect和row函數(shù)，如上所講，countif函數(shù)得到一個計數(shù)的數(shù)組結(jié)果時，然后直接與貨號列進行合并，直接得到了與創(chuàng)建輔助列的結(jié)果相同的查詢列表。
然后利用if數(shù)組在vlookup函數(shù)中的應(yīng)用，進行查詢列和返回列的設(shè)置。從這一步來看，其實之前講解的vlookup+if函數(shù)的兩種用法，也是這個案例中的一個解題要點！
最后給vlookup的第1參數(shù)查找值連接上column函數(shù)，進行橫向的動態(tài)引用。
對于這個組合公式的理解，可以在公式編輯欄中對公式各組成部分進行分步解析，它的關(guān)鍵點還是在于數(shù)組列表的創(chuàng)建和查找區(qū)域的指定設(shè)置。
聯(lián)系客服
