Excel Small函数的基本使用方法及与Index、IF、Match、Row组合与实现筛选不重复值

亮术网 2019-09-03 本网原创

在 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所示:

Excel Small函数的使用方法及实例

图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所示:

Excel Small函数返回数字错误值 #NUM! 的实例

图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所示:

Excel Index + Small + IF + Match + Row函数组合实现筛选不重复值(唯一值)

图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))),"")