Vlookup大叔與一對多查詢(Excel函式集團)

熬肥妖 發表於 2021-12-19
Excel

所謂一對多查詢,就是根據一個條件,把多個符合條件的結果全部找出來。

其實吧,一對多查詢不是什麼,尤其是O365的Filter函式橫空出世震撼全場之後,簡直就是瞬間把所有傳統的陣列解法甩出去七八十來條街……

但是!

這世上有一種人,叫“沒了Vlookup就吃不好睡不香”,但凡和查詢搭上半點關係的問題,都必須肯定一定確定要用早已油膩的Vlookup大叔來解決。

Vlookup大叔表示,心累!

比如表格結構如下圖的一個一對多查詢問題:

Vlookup大叔與一對多查詢(Excel函式集團)

好在V大叔有個特別之處,就是他的引數常常會弄出些意外,比如第一引數¥#¥%@……

再比如第二引數#¥%&¥#……

所以就生出了這麼個野路子的公式:

=VLOOKUP(D$2,INDIRECT("a"&SMALL(IF(A$2:A$17=D$2,ROW($2:$17)),ROW(A1))&":b17"),2,)

好吧,這公式就這麼一亮相,肯定是支援頭暈模式的,還是一步一步來拆解吧。

首先,當我們遇到這個問題時,如果只需要找第一個“乙”所對應的“B”,V大叔的標準寫法是:

=VLOOKUP(D$2,A2:B17,2,)

其中的A2:B17是查詢範圍,直接引用。如果改用間接引用,並不影響公式結果:

=VLOOKUP(D$2,INDIRECT("A2:B17"),2,)

即然用了間接引用,A2:B17就不再是單元格地址,而是變成了字串,可以各種拆解了,比如拆解成這樣:

=VLOOKUP(D$2,INDIRECT("A"&2&":B17"),2,)

這樣一來,其中的2就被單獨拎了出來,這其中就可以加上一個一對多查詢的慣用手法:

=VLOOKUP(D$2,INDIRECT("A"&SMALL(IF(A$2:A$17=D$2,ROW($2:$17)),ROW(A1))&":B17"),2,)

文字解釋一下就是如果A2:A17這些等於D2,也就是符合條件時,返回對應的行號,再用Small函式對返回的結果通過公式向下複製的方式從小到大排列。

所以這公式,就是這麼回事了。

那麼,為什麼V大叔這麼用就可以起到一對多查詢的效果呢?如果把"A"&SMALL(IF(A$2:A$17=D$2,ROW($2:$17)),ROW(A1))&":B17"部分單獨放在一個單元格里,前面加上等號,公式完成後再向下複製,就會得出A3:B17、A7:B17、A11:B17和A15:B17這樣的結果。

說得再透明點就是,第一個公式是讓V大叔在A3:B17這個範圍內找D2對應的結果;向下複製到第二個公式是讓V大叔在A7:B17這個範圍內找D2對應的結果;繼續向下複製到第三個公式是讓V大叔在A11:B17這個範圍內找D2對應的結果;第四個公式是讓V大叔在A15:B17這個範圍內找D2對應的結果。

所以這結果是正確的、愉快的、大得V大叔忠粉們心的、皆大歡喜的……

可偏偏這公式,是個野路子,因為,選取第二引數後按F9功能鍵,結果是奇特奇異奇怪的。

Vlookup大叔與一對多查詢(Excel函式集團)

當然這結果也並非來自地球以外,之所以會這樣,是因為Indirect的結果,是多維引用,而正經路子多維引用都是在外面套用SumIf、Subtotal、DSum這些函式, 且完成以後的公式還可以再在外面套函式,詳參多工作表彙總的相關公式。

而V大叔用在這時,就只能這樣了,想要在外面再套別的函式,那就……沒結果了。

這是不是函式中的一個Bug呢?

但是,不管是真Bug還是假Bug,不管是正路子還是野路子,只要能解決問題,同時大得V大叔忠粉們的心,就是好路子!

最後提示一小下下,如果您的電腦不支援動態陣列,公式結束後請按【Ctrl+Shift+Enter】組合鍵,以免樂極生悲。