Question to say "I can!"

一對多查詢,神器index+small

2016-12-12

參考Excel之家ExcelHome

我們對數據進行查詢時,經常會使用VLOOKUP函數。

但有時,我們會碰到這樣的問題,提取符合條件的結果是多個,而不是一個,這時候VLOOKUP就犯難了。

舉個例子,如下圖,左側A1:C10是一份學員名單表,現在需要根據F1單元格的“EH圖班”這個指定的條件,在F2:F10單元格區域中,提取該班級全部學員名單。

e1

今天說一個函數查詢方面的萬金油套路: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、如果是要做成橫向的,又要怎么寫函數呢?

這里是隱藏的:=INDEX($B:$B,SMALL(IF(A$1:A$10=$F2,ROW($1:$10),4^8),Column(A$1)))

作者:admin | Categories:辦公能手 | Tags:

發表評論

電子郵件地址不會被公開。 必填項已用*標注

*

澳洲f1赛车b盘开奖套路 青海十一选五下期预测号码 排列七开奖结果今天晚 日本女优相田沙耶香 黑龙江省福彩36选7的中奖号 上海哈灵棋牌 大发pk10返水计算方法 成都股票配资 安徽11选5开奖彩票控 8bo比分网 日本女优名器rio 北京赛车pk10开奖 广东11选5任二在 双色球开奖直播视频今天新浪 11选5山东夺金走 全民麻将全集 nba比分战绩