Excel Small函數的基本使用方法及與Index、IF、Match、Row組合與實現篩選不重復值

在 Excel 中,Small函數用于返回數組或引用單元格中從小到大排序的指定數值,例如第一個最小值、第二個最小值等。它只有兩個參數,一個是 Array,另一個是 k,其中 k 不能小于等于 0 且不能大于數組元素總個數,否則會返回錯誤。

Small函數常與 Index、IF、Match、Row 函數組合,一方面實現一對多查找,另一方面實現篩選重復值,這兩個方面的應用十分廣泛。下面先看Small函數的基本用法,再看與各函數組合的擴展應用。

一、Excel Small函數語法

1、表達式:SMALL(Array,k)

中文表達式:SMALL(數組或引用單元格區域, 返回位置[從小到大])

2、說明:

如果 Array 為空和 k 小于等于 0 或 k 大于 Array 中數值個數,Small 都返回數字錯誤值 #NUM!。

二、Excel Small函數的使用方法及實例

(一)Array 中有重復值的實例

1、雙擊 C8 單元格,把公式 =SMALL(C2:C7,3) 復制到 C8,按回車,返回第三個最小值 3.5;雙擊 C8,把 3 改為 4,按回車,返回第四個最小值 5;再次雙擊 C8,把 4 改為 5,按回車,返回第五個最小值 5;操作過程步驟,如圖1所示:

圖1

2、公式說明:

A、公式 =SMALL(C2:C7,3) 中,C2:C7 為查找區域,3 為返回位置;公式的意思是在 C2 至 C7 中返回第三個最值,C2:C7 不用排序。

B、公式 =SMALL(C2:C7,4) 和 =SMALL(C2:C7,5) 都返回 5,說明Small函數把重復值排到兩個不同的位置,如例中的 5,一個排在第四位,另一個排在第五位。

(二)返回數字錯誤值 #NUM! 的實例

1、雙擊 D8 單元格,把公式 =SMALL(D2:D7,0) 復制到 D8,按回車,返回數字錯誤值 #NUM!;雙擊 D8,把 0 改為 7,按回車,同樣返回#NUM!;操作過程步驟,如圖2所示:

圖2

2、公式 =SMALL(D2:D7,0) 在 D2:D7 中返回第 0 個最小值,由于 K 必須大于 0,所以返回 #NUM!錯誤;而公式 =SMALL(D2:D7,7) 返回第七個最小值,由于 D2:D7 中只有 6 個值,而 K 必須小于等于 6,所以也返回 #NUM!錯誤。

三、Excel Small函數的擴展使用實例

(一)Index + Small + IF + Row函數組合實現一對多查找

Index + Small + IF + Row 組合的詳細解析,在《Excel Index函數的使用方法及與Match、Small、If配合返回行列對應的多個值和一對多、多對多查找》一文中已經介紹,查看請點擊書名號中的文章標題。

(二)Index + Small + IF + Match + Row函數組合實現篩選不重復值(唯一值)

1、雙擊 E2 單元格,把公式 =IFERROR(INDEX(B:B,SMALL(IF(MATCH(B$2:B$8,B$2:B$8,)=ROW($1:$7),ROW($2:$8)),ROW(A1))),"") 復制到E2,按 Ctrl + Shift + 回車,返回“銷售部”;把鼠標移到 E2 右下角的單元格填充柄上,鼠標變為加號(+)后,按住左鍵,往下拖一直拖到出現空單元格,則篩選出 B 列“部門”的所有不重復值;選中 E2,往右拖,返回 C 列的第一個不重復值“員工”,再往下拖,也一直拖到出現空單元格,則返回 C 列的所有不重復值;操作過程步驟,如圖3所示:

圖3

2、公式=IFERROR(INDEX(B:B,SMALL(IF(MATCH(B$2:B$8,B$2:B$8,)=ROW($1:$7),ROW($2:$8)),ROW(A1))),"")說明:

A、B$2 中的 $ 表示對行的絕對引用,往下拖時,2 不會變為 3、4 等;B$2:B$8 以數組形式返回 B2 至 B8 中的所有部門,即 {"銷售部";"行政部";"銷售部";"財務部";"行政部";"銷售部";"財務部"}。

B、MATCH(B$2:B$8,B$2:B$8,) 執行時,第一次取出 B2(即“銷售部”),然后返回“銷售部”在 B2:B8 中的位置 1;第二次取出 B3(即“行政部”),也返回在 B2:B8 中的位置 2;其它的以此類推,最后返回數組 {1;2;1;4;2;1;4};這里最主要的是相同的值返回一樣的序號。

C、$1:$7 用于返回 1 到 7 的數組,即 {1;2;3;4;5;6;7};ROW($1:$7) 用于返回 1 到 7 行的行號,即{1;2;3;4;5;6;7}。ROW($2:$8) 用于返回 2 到 8 行的行號,即{2;3;4;5;6;7;8}。

D、則 IF(MATCH(B$2:B$8,B$2:B$8,)=ROW($1:$7),ROW($2:$8)) 變為 IF({1;2;1;4;2;1;4}={1;2;3;4;5;6;7},{2;3;4;5;6;7;8}) ,接著從= 左邊的數組中取出第一個元素 1,再從 = 右邊的數組中取出第一個元素 1,它們相等,所以返回 True;第二次從 = 左邊的數組中取出第二個元素 2 和從 = 右邊的數組中取出第二個元素 2,它們也相等,所以也返回 True;其它的以此類推,最后返回數組{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE};這一步主要是過濾掉重復數值,只留下重復“部門”的第一個部門,為 True 的將留下。

E、則 IF({1;2;1;4;2;1;4}={1;2;3;4;5;6;7},{2;3;4;5;6;7;8}) 變為 IF({TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE},

{2;3;4;5;6;7;8}),接著從條件的數組中取出第一個元素 TRUE,它為真,所以返回 If 的第二個參數(即“數字數組”)中對應元素 2;第二次取出第二個元素 TRUE,返回數字數組中對應元素 3;第三次取出 FALSE,它為假,所以返回 If 的第三個參數,由于這里省略了,默認返回 FALSE;最后返回 {2;3;FALSE;5;FALSE;FALSE;FALSE}。

F、ROW(A1) 用于返回 A1 的行號 1;則 SMALL(IF(MATCH(B$2:B$8,B$2:B$8,)=ROW($1:$7),ROW($2:$8)),ROW(A1)) 變為 SMALL({2;3;FALSE;5;FALSE;FALSE;FALSE},1),接著在數組中返回第一個最小值 2。

G、則公式變為 =IFERROR(INDEX(B:B,2),""),B:B 表示引用 B 列;接著用Index函數返回 B 列第二行的值“銷售部”;IfError 是錯誤判斷函數,在前面篇章已多次介紹。

3、公式=IFERROR(INDEX(B:B,SMALL(IF(MATCH(B$2:B$8,B$2:B$8,)=ROW($1:$7),ROW($2:$8)),ROW(A1))),"")還有以下兩種寫法:

=IFERROR(INDEX(B:B,SMALL(IF(MATCH(B$2:B$8,B$2:B$8,)=ROW(B$1:B$7),ROW(B$2:B$8)),ROW(A1))),"")

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