學會Index+Small+Row的“萬金油”函式

1、提取不重複的值

還記得我們第二次陣列教學篇中的Small+Row函式的返回值嗎？如果忘記了，建議還是先點下面的連結看看。如果你還記得，可以忽略下方的連結繼續讀。

千呼萬喚，“萬金油”函式終於登場了。這是一份模擬資料，

如果我們現在需要統計各銷售員的銷售數量，常規來說，如果有了銷售員的明細，我們用Sumif函式就可以很方便地彙總出來銷售數量，但是我們可以看出，銷售員的姓名是無序而且重複的，如果不借助輔助列，我們就需要用到陣列函式來提取銷售員姓名的唯一值了。

函式：單元格A12

{=IFERROR(INDEX($B$2:$B$8,SMALL(IF(MATCH($B$2:$B$8,$B$2:$B$8,0)=ROW($B$2:$B$8)-1,ROW($B$2:$B$8)-1),ROW(A1))),"")}

函式說明：

利用Match函式找到第一次出現的值的行號，形成一個數組資料，再利用Small函式順序的找到出現的行號，用Index函式引用姓名，最後用Iferror函式遮蔽錯誤值。

函式執行過程：

2、提取重複值

上面我們提取了不同姓名的銷售員，那麼我們如果要提取相同姓名的銷售員的銷售記錄又該如何呢？

函式：單元格A12

{=IFERROR(INDEX(A$2:A$8,SMALL(IF(COUNTIF($B$2:$B$8,$B$2:$B$8)=2,ROW($A$2:$A$8)-1),ROW(A1))),"")}

函式說明：

我們將第一例中的Match部分，替換上Countif函式，如果出現兩次就是我們需要的記錄了，其餘函式部分同上例。

函式執行過程：

3、提取滿足條件的明細

還是上面的資料，這次我們來提取銷售員“趙”，賣的“中”碼貨物的明細。

函式：單元格A13

{=IFERROR(INDEX(A$2:A$8,SMALL(IF(($B$2:$B$8=$B$11)*($C$2:$C$8=$C$11),ROW($A$2:$A$8)-1,99^9),ROW(A1))),"")}

函式說明：

和上例不同的地方是，我們唯一改動的就是條件部分，用兩個條件相乘，我們要知道邏輯值是可以參與計算的，True=1、False=0，所以函式中的兩個條件相乘就形成了由0.1組成的陣列資料。剩下的運算道理和第一例完全一樣。

函式執行過程：

作者雲：

強大的“萬金油”函式，其實也就是這些主要的功能了。本來還想舉幾個例子，但是除了條件變換了，其他的內容都是一樣的，而且有的例子沒有必要非使用“萬金油”函式，巧妙的使用其它函式結合陣列資料一樣可以達到效果。

Excel裡有個號稱“萬能”的函式組合，這個函式組合就是INDEX+SMALL+IF，很多應用場合都能看到它們的身影。

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

讓我們透過一個例項來認識一下它們到底有多“萬能”吧！

1

公式應用情景介紹

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

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

2

解決問題思路分析

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

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

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

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

3

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

我們可以使用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))

4

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

由於上一步獲得的符合條件的行號陣列是數字和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))

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

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

5

接下來，用函式提取資料

最後一步提取資料可以選用的函式比較多，本例介紹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班”……

6

完成階段

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

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

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

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

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

完成效果圖如下：

7

遮蔽錯誤值

可以看到上步產生的結果還有錯誤值存在，我們用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所以再加上一個&""。

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

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

萬金油函式？我最先想到的就是SUBTOTAL函式，這個函式基本包括了我們平常資料彙總中經常用到的計算方法，包括求和，求積，求平均，求最大最小值等等，可以說一個函式就頂替了sum、average、product、max、min等函式。並且並且他還可以選擇是否計算隱藏值，功能上又多了一個，所以我建議平常EXCEL用的多的人可以多多使用下這個函式，非常實用！