談起數(shù)據(jù)查詢，大部分朋友首先想到應(yīng)該是VLOOKUP函數(shù)。它既簡(jiǎn)單又實(shí)用，是表哥表妹必備的兩項(xiàng)Excel技能之一（另外一個(gè)是數(shù)據(jù)透視表）。但VLOOKUP有一個(gè)典型的問(wèn)題，當(dāng)查詢范圍內(nèi)符合條件的結(jié)果有多個(gè)時(shí)，也就是通常所說(shuō)的一對(duì)多查詢時(shí)，它只返回首個(gè)結(jié)果。今天給大家聊一個(gè)在Excel函數(shù)世界號(hào)稱萬(wàn)金油的組合套路，可以輕松解決一對(duì)多、多對(duì)多的查詢問(wèn)題。4、簡(jiǎn)單實(shí)用的輔助列解決方案5、有疑惑之處可在會(huì)員群內(nèi)提問(wèn)交流。1 丨
VLOOKUP的局限性
先說(shuō)一下VLOOKUP函數(shù)的局限性。我舉個(gè)例子。如下圖所示，A:C是數(shù)據(jù)源?，F(xiàn)在需要根據(jù)F1的班級(jí)名稱，在F2:F11區(qū)域中，查詢?cè)摪嗉?jí)全部學(xué)員名單。
這事如果用VLOOKUP函數(shù)處理，F(xiàn)2單元格輸入以下公式并向下復(fù)制填充：
最終會(huì)發(fā)現(xiàn)所有的公式都返回Word班的第一個(gè)人名：沐沐。
但這是不是就說(shuō)明VLOOKUP解決不了這種問(wèn)題呢？
攤手，并不是。
VLOOKUP確實(shí)只能返回首個(gè)符合條件的匹配結(jié)果，不過(guò)，正確的說(shuō)法是，VLOOKUP只能返回指定查詢范圍內(nèi)的首個(gè)符合條件的匹配結(jié)果。
后者比前者多了一個(gè)定語(yǔ)：查詢范圍。
有個(gè)喜歡穿拖鞋出門的朋友，叫愛(ài)因斯坦的說(shuō)過(guò)，位置是相對(duì)的。當(dāng)查詢范圍是一個(gè)變動(dòng)的區(qū)域時(shí)，VLOOKUP就可以返回符合條件的多個(gè)結(jié)果。
在F2單元格輸入以下VLOOKUP函數(shù)，并向下復(fù)制填充，即可獲取F1班級(jí)所有的人員名單。
數(shù)組公式 ▼
=IFERROR(VLOOKUP($F$1,INDIRECT("A"&SMALL(IF(A$2:A$11=$F$1,ROW($2:$11)),ROW(A1))&":C10"),2,0),"")
公式使用了INDIRECT函數(shù)制作VLOOKUP的查詢范圍，隨著公式向下復(fù)制填充，查詢范圍不斷變化收窄變小……但這種解法冷門又低效，只適合拓展VLOOKUP函數(shù)的用法思路，并不具有良好的實(shí)用性。
具備較好實(shí)用性且被廣大表里表親所接受的是INDEX+SMALL+IF函數(shù)組合，一個(gè)被稱為查詢問(wèn)題萬(wàn)金油的組合函數(shù)公式。
……
2 丨
一個(gè)萬(wàn)金油函數(shù)組合
F2單元格輸入以下數(shù)組公式，并復(fù)制填充至F2:F11區(qū)域，即可獲取正確結(jié)果。
數(shù)組公式 ▼
=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$11=F$1,ROW($1:$11)),ROW(A1))),"")
這個(gè)公式看起來(lái)可就比上面那個(gè)VLOOKUP的解法苗條養(yǎng)眼多了，坦白的說(shuō)，很符合俺兩年前的外號(hào)——小清新。
講解一下該公式。
IF(A$1:A$11=F$1,ROW($1:$11))
判斷A1:A11區(qū)域內(nèi)的值是否等于F1，如果相等，則返回對(duì)應(yīng)的行號(hào)，否則返回邏輯值FALSE。結(jié)果是一個(gè)內(nèi)存數(shù)組：
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;9;10;11}
SMALL(IF(A$1:A$11=F$1,ROW($1:$11)),ROW(A1))
SMALL函數(shù)對(duì)IF函數(shù)的計(jì)算結(jié)果進(jìn)行取數(shù)，隨著公式的向下復(fù)制填充，依次提取第1、2、3……n個(gè)最小值，得到符合查詢條件的行號(hào)。也就是第7行、第9行、第10行……
隨后使用INDEX函數(shù)，以SMALL函數(shù)返回的行號(hào)作為索引值，在B列中取出對(duì)應(yīng)的姓名。
當(dāng)SMALL函數(shù)返回的結(jié)果為錯(cuò)誤值#NUM時(shí)，意味著符合條件的行號(hào)被取之殆盡了，金山銀山變成了空山廢山，此時(shí)INDEX函數(shù)會(huì)返回一個(gè)錯(cuò)誤值。于是最后再使用一個(gè)IFERROR函數(shù)，屏蔽錯(cuò)誤值，使它返回假空""。
總結(jié)一下，公式首先判斷查詢范圍的值是否符合查詢條件，如果符合，則返回位置行號(hào)，然后把行號(hào)從小到大依次取出，使用INDEX函數(shù)獲取結(jié)果……
就這么回事。
有時(shí)候，你會(huì)見(jiàn)到有人把該函數(shù)寫成以下兩種形式▼解法2：&"" ▼
=INDEX(B:B,SMALL(IF(A$1:A$11=F$1,ROW($1:$11),4^8),ROW(A1)))&""
解法2：&"" ▼
=INDEX(B:B,SMALL((A$1:A$11<>F$1)/1%+ROW($1:$11),ROW(A1)))&""
這兩個(gè)函數(shù)公式通過(guò)空單元格搭配&""的方法，很巧妙的規(guī)避了錯(cuò)誤值的出現(xiàn)，省略了IFERROR函數(shù)，公式的長(zhǎng)度也得到了精簡(jiǎn)。
但這是IFERROR函數(shù)未出現(xiàn)前屏蔽錯(cuò)誤值的技巧，是無(wú)可奈何的選擇；它們會(huì)把數(shù)值變成文本值，當(dāng)查詢結(jié)果為純數(shù)值或者日期時(shí)，就很不利于數(shù)據(jù)的準(zhǔn)確呈現(xiàn)及再次統(tǒng)計(jì)分析。
畢竟大部分統(tǒng)計(jì)類函數(shù)會(huì)直接忽略文本型數(shù)值，不予計(jì)算，比如下圖所示的SUM函數(shù)。
所以通常還是建議大家使用IFERROR函數(shù)來(lái)處理錯(cuò)誤值，沒(méi)必要為了省幾個(gè)字符，裝A裝到給自己添麻煩。
3 丨
氪金~FILTER函數(shù)
有朋友覺(jué)得INDEX+SMALL+IF太麻煩了，太長(zhǎng)了，看不懂，心累，不能再愛(ài)了……有沒(méi)有簡(jiǎn)單的函數(shù)可以直接解決此類問(wèn)題？
打個(gè)響指，當(dāng)然是有的。
如果你所使用的Excel版本是365，使用FILTER函數(shù)就簡(jiǎn)單多了，它就是為這類問(wèn)題蛋生的。
F2單元格輸入以下函數(shù)即可：
=FILTER(B2:B11,A2:A11=F1,"")
FILTER函數(shù)有3個(gè)參數(shù)，第1個(gè)參數(shù)是需要篩選的數(shù)據(jù)，本例為B2:B11，第2個(gè)參數(shù)是篩選的條件，本例為(A2:A11=F1)，第3個(gè)參數(shù)是找不到查詢結(jié)果時(shí)返回指定值，本例為假空。
FILTER函數(shù)是365新增的動(dòng)態(tài)數(shù)組函數(shù)之一，它可以根據(jù)計(jì)算結(jié)果的大小，智能的顯示在相應(yīng)區(qū)域。比如，本例FILTER計(jì)算結(jié)果是一個(gè)一列垂直數(shù)組，包含3個(gè)元素，則自動(dòng)在F2:F5單元格顯示全部——只需要計(jì)算一次，即可獲取全部結(jié)果，很明顯，這極大提高了函數(shù)運(yùn)算效率。
如果你只需要FILTER返回首個(gè)結(jié)果，可以在公式前添加符號(hào)@，取消動(dòng)態(tài)數(shù)組功能。
=@FILTER(B2:B11,A2:A11=F1,"")
更多FILTER函數(shù)的用法總結(jié)，可以后臺(tái)回復(fù)關(guān)鍵字365。4 丨
輔助列解決方案
但如果你所使用的Excel不是365呢？有沒(méi)有簡(jiǎn)單的解法辦法？
當(dāng)然……還是有的。
這就是傳說(shuō)中的——輔助列。
首先，在A列制作輔助列，輸入以下公式，復(fù)制填充至A2:A11區(qū)域；公式的作用是計(jì)算B列班級(jí)符合條件的累加序號(hào)。
G2單元格輸入以下VLOOKUP函數(shù)即可獲取正確結(jié)果。
=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")
VLOOKUP查找值為ROW(A1)，即行號(hào)1，當(dāng)公式向下復(fù)制填充時(shí)，該值不斷累加，1、2、3……也就對(duì)應(yīng)A列累加的序列號(hào)，查詢?cè)撔蛱?hào)即可依次返回相關(guān)班級(jí)對(duì)應(yīng)的C列人名。
沒(méi)了。
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
