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:B,B:B,)=ROW(:),ROW(:)),ROW(A1))),"") 复制到E2，按 Ctrl + Shift + 回车，返回“销售部”；把鼠标移到 E2 右下角的单元格填充柄上，鼠标变为加号（+）后，按住左键，往下拖一直拖到出现空单元格，则筛选出 B 列“部门”的所有不重复值；选中 E2，往右拖，返回 C 列的第一个不重复值“员工”，再往下拖，也一直拖到出现空单元格，则返回 C 列的所有不重复值；操作过程步骤，如图3所示：

图3

2、公式 =IFERROR(INDEX(B:B,SMALL(IF(MATCH(B:B,B:B,)=ROW(:),ROW(:)),ROW(A1))),"") 说明：

A、B 中的 $ 表示对行的绝对引用，往下拖时，2 不会变为 3、4 等；B:B 以数组形式返回 B2 至 B8 中的所有部门，即 {"销售部";"行政部";"销售部";"财务部";"行政部";"销售部";"财务部"}。

B、MATCH(B:B,B:B,) 执行时，第一次取出 B2（即“销售部”），然后返回“销售部”在 B2:B8 中的位置 1；第二次取出 B3（即“行政部”），也返回在 B2:B8 中的位置 2；其它的以此类推，最后返回数组 {1;2;1;4;2;1;4}；这里最主要的是相同的值返回一样的序号。

C、: 用于返回 1 到 7 的数组，即 {1;2;3;4;5;6;7}；ROW(:) 用于返回 1 到 7 行的行号，即{1;2;3;4;5;6;7}。ROW(:) 用于返回 2 到 8 行的行号，即{2;3;4;5;6;7;8}。

D、则 IF(MATCH(B:B,B:B,)=ROW(:),ROW(:)) 变为 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))),"")

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。