Excel教程下載和軟件下載中心,Microsoft技術社區聯盟成員,全球極具影響力的Excel門戶,Office技術培訓社區

一對多查詢,少不了這兩刷子

2018-06-23  作者:ExcelHome  閱讀:
我們對數據進行查詢時,經常會使用VLOOKUP函數。
但有時,我們會碰到這樣的問題,提取符合條件的結果是多個,而不是一個,這時候VLOOKUP就犯難了。
舉個例子,如下圖,左側A1:C10是一份學員名單表,現在需要根據F1單元格的“EH圖班”這個指定的條件,在F2:F10單元格區域中,提取該班級全部學員名單。

今天說一個函數查詢方面的萬金油套路:Index+Small。
F2單元格輸入以下數組公式,按住Ctrl+Shift鍵不放,再按回車鍵,然后向下填充:
=INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1))),"")
這個公式看起來可就比上面那個VLOOKUP的解法苗條養眼多了,坦白的說,很搭俺星光十年后的匪號——小清新。

1、公式講解
IF(A$1:A$10=F$1,ROW($1:$10),4^8)
這部分,先判斷A1:A10的值是否等于F1,如果相等,則返回A列班級相對應的行號,否則返回4^8,也就是65536,一般情況下,工作表到這個位置就沒有數據了。
結果得到一個內存數組:
{65536;2;3;65536;65536;65536;65536;8;65536;10}

SMALL函數對IF函數的結果進行取數,隨著公式的向下填充,依次提取第1、2、3……n個最小值,由此依次得到符合班級條件的行號。
隨后使用INDEX函數,以SMALL函數返回的行號作為索引值,在B列中提取出對應的姓名結果。
當SMALL函數所得到的結果為65536時,意味著符合條件的行號已經被取之殆盡了,此時INDEX函數也隨之返回B65536單元格的引用,結果是一個無意義的0,為了避免這個問題,可以在公式后面加上一個小尾巴&""
利用&””的方法,很巧妙的規避了無意義0值的出現,只是當查找結果為數值或日期時,這個方法會把數值轉變為文本值,并不利于數據的準確呈現以及再次統計分析。

2、練手題
最后留下一道練手題,如下圖,根據A1:C10區域的數據,將E列相關班級的姓名,填充到F2:I5區域。



轉載請注明來源"ExcelHome"并保留原文鏈接。
固定鏈接:http://www.fvtgnn.tw/lesson/article/excel/1906.html
分享到:
Copyright 1999 - 2017 Excel Home.All Rights Reserved.
本站特聘法律顧問:徐懷玉律師 李志群律師   滬ICP備11019229號

滬公網安備 31011702000001號

征信       
35选7摇奖器