INDEX+SMALL+IF+ROW函數組合使用解析

(2013-12-20 19:33:09)

轉載▼

很多人在Excel中用函數公式做查詢的時候，都必然會遇到的一個大問題，那就是一對多的查找/查詢公式應該怎麼寫？大多數人都是從VLOOKUP、INDEX+MATCH中入門的，縱然你把全部的多條件查找方法都學會了而且運用嫻熟，如VLOOKUP和&、SUMPRODUCT、LOOKUP(1,0/....，但仍然只能對這種一對多的查詢望洋興嘆。

這裡講的INDEX+SMALL+IF+ROW的函數組合，就是解決一對多查詢的一種通式，如果你能掌握，那在Excel里基本上就沒有什麼查詢你是實現不了的了（除了INDIRECT+RC引用）。

下面，我們先來看看示例數據和查詢要求：

由於VLOOKUP、INDEX+MATCH、LOOKUP(1,0/都只是一對一的查詢，有的是只查詢第一個，有的是只查找最後一個，所以這種組合對於我們這裡的要求完全無用武之地。所以，你也別把精力都花在這個上面，雖然也是可以構造出來的，但今天我們要講的這個組合，是最基本，也是最容易理解的通式，所以請把精力花在這上面。

問題1，解答：

=IF(ROW(A1)>COUNTIF($B:$B,"Sam"),"",INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20)),ROW(A1))))[公式一]

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20)),ROW(A1))),"")[公式二]

=INDEX(A:A,SMALL(IF($B$2:$B$20="Sam",ROW($2:$20),4^8),ROW(A1)))&""[公式三]

首先，這三個都是數組公式，什麼是「數組公式」呢？數組公式最特殊也最直接的表現，就是你在單元格里輸入完公式之後，要按Ctrl+Shift+Enter三鍵結束，跟「普通公式」只按Enter結束有明顯的區別。

為什麼有三個公式呢？其實這三個公式的作用和核心是一樣的，只是應對不同Excel版本、不同數據類型所用到的屏蔽錯誤值的手法有所差異而已。

下面我們著重講講[公式三]，因為這個結構里函數要素最齊全，而組合也是非常符合我們標題所講到的。

首先我們來簡化和分解一下這個公式：

從上圖我們不難看出，這個組合就是由INDEX作為主體函數，第一參數就是我們要查詢並返回的數據區域，第二參數就是由SMALL構造的一個公式，而SMALL構造的函數，無非就是由IF判斷生成的一個內存array。

如果你沒有函數基礎的話，估計還是理解不了，那我們先返回IF結構的計算結果，也就是判斷B2:B20區域，如果等於「Sam」，就返回對應的所在行號，不相等的話，就返回4^8，就是4的8次方冪，即65,536，這在xls格式文檔中，相當於最大行號，在xlsx格式則不然。

OK，就我們圖中的數據，抹黑SMALL函數的array參數，再按F9，不難返回一個內存數組如下：

{65536;65536;65536;65536;6;65536;65536;65536;65536;11;65536;13;65536;65536;65536;65536;18;65536;65536}

簡化一下，我們用「極大」來表示65536，那結果就是：

{極大;極大;極大;極大;6 ;極大;極大;極大;極大;11 ;極大;13;極大;極大;極大;極大;18 ;極大;極大 }

這個內存數組也就是這個公式組合里最關鍵的，你可以看到SMALL函數的第二參數是ROW(A1)，這個是返回A1單元格所在的行號，也就是1，當我們整個公式下拉填充之後，就可以得到ROW(A2)、ROW(A3)、ROW(A4)這樣的變化，也就是1、2、3、4……這樣的自然數序列，從而可以把上面簡化了的內存數組裡的6、11、13、18給提取出來，因為6是最小值、11是倒數第二小、13是倒數第三小、18是倒數第四小的值，如果還不明白，那請在單元格里輸入「=SMALL(」然後按F1查閱SMALL函數的語法和功能說明。

6、11、13、18代表什麼，我們提取出來有什麼用呢？回過頭去看看IF函數就明白了，原來這就是那些滿足條件的記錄所在的行號，這樣一來我們就可以把一對多的所有符合條件的記錄全都提取出來了。

現在回過頭來，說說這三個公式都有什麼差異和優勢？

從上面的分解過程我們也可以看到，其實我們只能憑藉下拉公式來得到所有滿足條件的所有記錄，但具體有多少記錄我們不清楚，而且不同的條件返回的記錄數量也是不確定的，所以這個公式就決定了我們必須要有容錯機制，保證公式下拉之後，不因為返回記錄數量的不同而顯示多餘的0值或者錯誤值，最常見的如#NUM!。

----------------------------------------------------------

第一個公式比較長，但公式用了一個IF，直接用COUNTIF返回滿足條件的記錄數量，然後只顯示滿足條件的記錄，公式下拉後其餘數量一率用空值表示，而且這裡IF函數的False結果可以直接省略以返回FALSE；

第二個公式尤其適用於xlsx格式文檔上，直接省略IF的第三參數，因為IFERROR可以涵蓋所有錯誤而不必多費心；

第三個公式只適用在沒有特殊格式的數據上，如我們示例數據里的日期、數值，其實都不適合用這個公式，因為我們公式有一個4^8的極大值，而且INDEX函數最後面接了一個&""，其根本目的是為了避免返回65536行里空值通過公式得到0，但這個的間接作用就是將數據直接轉化為文本，所以當你要返回的數據里有數值或者日期值，或者其他自定義格式時，就都會被打回原形。。。。

到此為止，你應該基本上能自己應用了吧？如果還不行，那請重讀一遍，熟能生巧嘛~~

下面講講第二、第三個問題的公式寫法，其實會了第一個，第二個依瓢畫葫蘆是不成問題的，巧妙的是第三個問題，由於我們本身就是在SMALL的第一參數返回一個內存數組，所以第三個問題才突顯這個組合的優勢。

這裡就只講公式寫法而不展開討論，公式很容易看明白的，只是內在的機理可能需要先去接觸學習一下數組公式的基礎內容，才容易深化。

問題2，解答：

=IFERROR(INDEX(A:A,SMALL(IF($C$2:$C$20%<50,ROW($2:$20)),ROW(A1))),"")

=IF(ROW(A1)>COUNTIF($C:$C,"<5000"),"",INDEX(A:A,SMALL(IF($C$2:$C$20<5000,ROW($2:$20)),ROW(A1))))

問題3，解答：

=IFERROR(INDEX(A:A,SMALL(IF(MONTH($D$2:$D$20)=3,ROW($2:$20)),ROW(A1))),"")

=IF(ROW(A1)>SUMPRODUCT(N(MONTH($D$2:$D$20)=3)),"",INDEX(A:A,SMALL(IF(MONTH($D$2:$D$20)=3,ROW($2:$20)),ROW(A1))))

然後，而且必須是數組公式，Ctrl+Shift+Enter三鍵結束，自己書寫公式的時候注意絕對引用與相對引用的適當使用，這又屬於基本功咯，請加油。

另外這種組合里你可能看到INDEX+SMALL+IF+ROW+COUNTIF，COUNTIF就是用在SMALL函數的第二個參數，這個主要是根據列出的數據的個數，提取第幾個的值，對於雜序無指條件的重複值提取，就正好派上用場，具體可以根據自己的使用情況和需求，消化吸收為自己的知識。