|
我們先來看下兩種借助輔助列進(jìn)行“一對多查詢”的方法，一種是用VLOOKUP函數(shù)，一種是用INDEX MATCH函數(shù)組合。 用VLOOKUP函數(shù)實(shí)現(xiàn)“一對多查詢”如下圖所示，首先在A列建立輔助列，在A2單元格輸入如下公式，然后向下填充至A10單元格。 =(B2=$H$2) N(A1) 這個(gè)公式是用來統(tǒng)計(jì)H2單元格的內(nèi)容第幾次出現(xiàn)在B列，返回1、2、3...n序列值。 圖1 然后在I2單元格輸入如下公式，然后向右、再向下填充至L10單元格。 =IFERROR(VLOOKUP(ROW(1:1),$A:$F,COLUMN(C:C),0),'') 圖2 用INDEX MATCH函數(shù)組合實(shí)現(xiàn)“一對多查詢”用INDEX MATCH進(jìn)行“一對多查詢”的原理和VLOOKUP相同，都是將對班級信息的查詢，轉(zhuǎn)換成對數(shù)字序列1、2、3....n的查詢。因此只需要將圖2中的公式改為如下形式，即可完成查詢。 =IFERROR(INDEX(C:C,MATCH(ROW(1:1),$A:$A,0)),'') 圖3 以上兩種方法的詳細(xì)講解及注意事項(xiàng)，可以參考之前的一篇文章：Excel中兩種常見的“一對多”查詢方式橫向?qū)Ρ?，孰?yōu)孰劣？ 用INDEX SMALL IF函數(shù)組合實(shí)現(xiàn)“一對多查詢”這種方法的最大好處是不需要輔助列，直接輸入公式即可得到結(jié)果，比前兩種方法更加便捷！如圖4所示，我們在H2單元格輸入如下公式，然后按住Ctrl Shift Enter，然后向右、向下填充至K10單元格。 =IFERROR(INDEX(B:B,SMALL(IF($A$1:$A$10=$G$2,ROW($1:$10)),ROW(A1))),'') 最終的查找結(jié)果如下圖所示： 圖4 我們來一步步分析這個(gè)公式的運(yùn)行原理。 IF($A$1:$A$10=$G$2,ROW($1:$10)) 這部分公式用來將A1:A10范圍內(nèi)的值與G2單元格的值進(jìn)行逐個(gè)比對，如果相同，則返回A1:A10對應(yīng)的行號（ROW函數(shù)的作用）。我們可以選中這部分公式，然后按下F9鍵查看最終結(jié)果。 {FALSE;2;3;FALSE;5;6;FALSE;FALSE;FALSE;FALSE} 發(fā)現(xiàn)返回的數(shù)組中只有2、3、5、6四個(gè)值，其它均為FALSE，也就是說在A1:A10范圍的第2、3、5、6行找到了需要查找的數(shù)據(jù)！ SMALL(IF($A$1:$A$10=$G$2,ROW($1:$10)),ROW(A1)) SMALL函數(shù)對上一步得到的數(shù)據(jù)結(jié)果進(jìn)行取數(shù)，隨著公式向下填充，依次提取第1、2、3...n個(gè)最小值，這些數(shù)字對應(yīng)的是符合條件班級的行號。比如我們選中H2單元格中的這部分公式，按F9顯示查找結(jié)果{2}，即得到了第一個(gè)匹配結(jié)果的行號為2。 INDEX(B:B,SMALL(IF($A$1:$A$10=$G$2,ROW($1:$10)),ROW(A1))) 這一步，是用INDEX定位對應(yīng)B列數(shù)據(jù)的位置（第2行），結(jié)果為B2。 最后的IFERROR函數(shù)是為了屏蔽多余行中的錯誤。因?yàn)椋绻麤]有IFERROR函數(shù)，查詢結(jié)果是這個(gè)樣子的： 圖5 總結(jié)本文通過討論了三種“一對多查詢”的方法，帶領(lǐng)大家進(jìn)一步熟悉了幾個(gè)函數(shù)組合在查詢中的典型用法。我們不難看出三種方法各有優(yōu)劣，在實(shí)際的工作實(shí)踐中到底選擇哪一種方法，要根據(jù)它們的特點(diǎn)，以及任務(wù)量大小等綜合考量。 三種方法的優(yōu)缺點(diǎn)對比： 1. VLOOKUP函數(shù) 優(yōu)點(diǎn)：公式相對簡單，容易理解。缺點(diǎn)：需要借助輔助列，并要求輔助列必須是查詢范圍的第1列。 2. INDEX MATCH函數(shù)組合 優(yōu)點(diǎn)：靈活多變，不用考慮輔助列順序。缺點(diǎn)：需要借助輔助列，不太方便。 3. INDEX SMALL IF函數(shù)組合 優(yōu)點(diǎn)：非常方便，不需要輔助列，一個(gè)公式就可以解決問題。缺點(diǎn)：公式相對復(fù)雜，不易理解。 |
|
|
