Excel里函數中的萬金油，你確定不要點進來看看？

05-31

Excel里有個號稱「萬能」的函數組合，這個函數組合就是INDEX SMALL IF，很多應用場合都能看到它們的身影。

這個函數的特點是：能提取出滿足某條件或某些條件的所有數據，可以一次性返回單行單列或多行多列的結果。

讓我們通過一個實例來認識一下它們到底有多「萬能」吧！

公式應用情景介紹

如下圖，A1:F9單元格為某次考試結果，每個人一行數據。現在需要在B11單元格（棕色底色填充）輸入班級名稱，在A14:F21區域返回該班級所有學生考試成績信息。

本例假設需要查詢【1班】所有學生的成績。

解決問題思路分析

我們的目的是要提取出A列班級名稱與B11單元格內容一致的所有數據，如果：1）能提取出符合條件的所有行數；

2）這些行數能從小到大一個一個的輸出；

3）可以從原始數據中按第2步輸出的行數把數據引用過來，那麼問題就解決了。

恰好，在Excel中存在這麼幾個函數能達到目的，下面我們來逐個介紹。

如何提取出符合條件的所有行數？

我們可以使用IF函數判斷來生成所有符合條件的行號數組，如下：

=IF($A$2:$A$9=$B$11,ROW($A$2:$A$9))

$A$2:$A$9=B11判斷A列數據是否和B11單元格內容一致，會返回TRUE和FALSE的數組。

如果$A$2:$A$9=B11則返回A2:A9的行號，否則返回FALSE。

本例生成的數組為棕色部分（A2=1班，所以返回A2的行號2，以此類推），按F9可以看到如下數組：

{2;FALSE;FALSE;5;6;FALSE;FALSE;9}

如果要滿足多個條件，可以用

=IF((條件區域1=條件)*(條件區域2=條件2),ROW(區域))

這種方式來實現，例如要返回【班級】為「1班」且【總分】>200的查詢結果，此部分可以改成：

=IF(($A$2:$A$9="一班")*($F$2:$F$9>200),ROW($A$2:$A$9))

如何順序輸出上一步獲得的行號？

由於上一步獲得的符合條件的行號數組是數字和FALSE的組合，我們利用SMALL函數就可以逐步輸出符合條件的行號。

舉例來說，SMALL({11,22,33},1)=11，也就是SMALL返回第一參數中第1（第二參數）小的數字。

同理，SMALL({11,22,33},2)=22。

為了下拉公式方便，我們一般用ROW()函數做SMALL的第二參數，保證下拉公式時自動提取第2小，第3小……

也就是用：

=SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))

將第一步產生的數組中符合條件的行號從最小的開始連續輸出。從下圖可以看到，符合條件的行號已經可以連續輸出了。

錯誤值可以先不用關心，後面我們用其他函數屏蔽。

接下來，用函數提取數據

最後一步提取數據可以選用的函數比較多，本例介紹INDEX函數方法。

簡單說，INDEX函數可以實現從第一參數中提取出第二參數個數據的目的。

例如：=INDEX({11,21,31},1)返回11，也就是從{11,21,31}提取第1個數字；=INDEX({11,21,31},2)返回21，也就是從{11,21,31}提取第2個數字。

那麼，我們用（紅色部分是上一步SMALL函數，第一個返回值為2）：

=INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1)))

就可以返回A1:A9中的第2個值，也就是第1個「1班」；下拉公式就返回了第2個「1班」……

完成階段

我們把上一步的函數組合放在查詢結果區域的第一列，Ctrl Shift enter運行公式，並下拉，結果如下：

這樣就獲得了班級的返回結果。

後面要獲取姓名和各科成績等信息怎麼辦呢？

通過分析，我們知道了SMALL函數輸出的結果已經固定不變了，現在只需要改變INDEX函數引用的列即可。

因此我們將INDEX函數的第一參數設置成了A$1:A$9【行絕對引用，列相對引用】，這樣，我們向右拉動公式就會自動變成B$1:B$9。

完成效果圖如下：

屏蔽錯誤值

可以看到上步產生的結果還有錯誤值存在，我們用IFERROR函數去屏蔽錯誤值，如果INDEX函數返回的是錯誤值，則在單元格返回空。

=IFERROR(INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))),"")

這樣就最終完成了查詢功能的設置。

補充說明

一般還有用INDEX(大區域,SMALL(IF(條件區域=條件,ROW(條件區域),4^8),ROW(A1)))&""這種方式去屏蔽錯誤值，這種屏蔽錯誤值的思路是，如果條件區域=條件就返回條件區域行號，否則返回一個比較大的用不到的行號，比如說4^8行或數據區域下面的一個空行行號。這樣，INDEX在輸出完所有符合條件的行後開始調用4^8行的值（空單元格），為了避免返回0所以再加上一個&""。

本例未使用此種方法，主要是由於後面要查詢的其他列有數字，用上述&""的方法數字會變成文本。

如果有日期的時候，日期格式存儲的數據會變成一個數字。

圖文作者：ExcelHome超級版主 shaowu459