Excel SMALL函数怎么用：返回第 k 小数值的语法与示例

在 Excel 中，想从一组数字里取最小值，通常会想到 MIN；如果要取第二小、第五小，或按条件筛出数据后再取第 k 小的值，SMALL 函数更合适。它按数值从小到大排列数据，并返回指定位置上的数值。

SMALL 常用于成绩和费用排名、库存价格分析、统计下限、条件筛选后的取值等场景。它本身只负责从给定数据中选取次序，不会识别“有效记录”“符合条件”等业务含义。要得到正确结果，必须先确认传入的数据范围、k 值以及空值、文本和错误值的处理方式。

一、SMALL 函数语法

=SMALL(array, k)

参数	含义	要求
array	要查找第 k 小值的数值数组或单元格区域。	可以是引用、命名区域或数组表达式。至少需要有一个可参与计算的数值。
k	从小到大排列时要返回的位置。	必须大于或等于 1，且不能大于 array 中可用数值的个数。通常传入整数。

例如，k=1 表示最小值，k=2 表示第二小值，k=3 表示第三小值。SMALL 允许重复值参与排序；它取的是排序后的位置，不是“第 k 个不同的数”。

假设 A2:A6 中依次为 8、3、3、10、6。排序后为 3、3、6、8、10，因此：

=SMALL(A2:A6,1)

返回 3。

=SMALL(A2:A6,2)

仍返回 3。若要取得第二个不同的数值，需要另外使用去重逻辑，不能直接把 k 改成 2。

二、SMALL 的适用场景

1. 取得前几名或最低几项

当数据已经是数值列，且关注的是从小到大的若干位置时，可以直接用 SMALL。例如查找订单金额中最低的三笔，或找出测量值最小的几个读数。若要显示从大到小的排名，可以改用 LARGE。

2. 计算指定次序的统计量

例如用 SMALL 取得较低端的观测值，或把它与 MEDIAN、PERCENTILE 等统计函数组合。需要注意，SMALL 返回的是排序位置上的实际数据值，不会自动计算平均值、百分位或置信区间。

3. 从符合条件的数据中取值

SMALL 本身没有条件参数。若要找出“华东地区订单里第三低的金额”，需要先筛出华东地区对应的金额，再交给 SMALL。较新版本 Excel 可以使用 FILTER；较旧版本可以使用数组公式或辅助列。具体写法见后文。

三、基础示例：取第 k 小值

假设 B2:B8 存放一组商品单价：

单元格	单价
B2	38
B3	21
B4	45
B5	21
B6	32
B7	27
B8	40

在 D2 输入 1，在 D3 输入 2，以此类推。E2 输入下面的公式，然后向下填充：

=SMALL($B$2:$B$8,D2)

结果依次为 21、21、27、32、38、40、45。这里用绝对引用锁定 B2:B8，使公式向下复制时数据范围不变；D2 保持相对引用，填充后会依次变成 D3、D4。

如果 k 固定，也可以直接写常数：

=SMALL(B2:B8,3)

该公式返回 27，即第三小的数值。若想让用户从单元格输入 k，建议用数据验证限制输入范围，并在公式旁说明允许的取值区间。

四、用 SMALL 生成升序列表

当要把一列数据按升序提取出来时，可以在结果列中逐行使用 SMALL。以下示例假设原始数据在 A2:A10，结果从 C2 开始：

=SMALL($A$2:$A$10,ROWS($C$2:C2))

在 C2 输入后向下填充，ROWS($C$2:C2) 在第一行返回 1，向下填充后依次返回 2、3、4。这样 SMALL 每一行都会取不同的排序位置。

该写法要求填充行数不要超过可用数值的个数。若结果区多填了行，后续公式可能因 k 超出范围而返回 #NUM!。可以用 IFERROR 隐藏超出部分，但应区分“正常没有更多结果”和“源数据中存在错误”等情况，避免把所有错误一概隐藏：

=IF(ROWS($C$2:C2)>COUNT($A$2:$A$10),"",SMALL($A$2:$A$10,ROWS($C$2:C2)))

COUNT 统计区域内的数值个数。此公式适合区域中的数据是普通数值、且没有需要显式处理的错误值的情况。如果区域包含错误值，SMALL 可能先因错误而失败；外层的 COUNT 并不能清除这些错误。

五、按条件取第 k 小值

方法一：使用 FILTER（支持动态数组的 Excel）

假设 A2:A8 是地区，B2:B8 是金额，D2 填写要查询的地区，E2 填写 k。要返回所选地区内第 k 小的金额，可以使用：

=SMALL(FILTER(B2:B8,A2:A8=D2),E2)

例如 D2 为“华东”、E2 为 2，公式会先筛出 A2:A8 等于“华东”的金额，再从筛选结果中取第二小值。FILTER 属于较新版本 Excel 的动态数组函数；若当前版本不支持，会显示函数名称相关错误。

如果一个条件都没有匹配，FILTER 默认会返回计算错误。可以指定空结果提示，再配合检查处理：

=IFERROR(SMALL(FILTER(B2:B8,A2:A8=D2),E2),"无可用结果")

此写法简单，但 IFERROR 会把多种错误都转换成同一段文字，包括 k 不合法、筛选结果为空以及源数据错误。正式报表中若需要定位问题，应先单独检查匹配数量和 k 值，而不是只用 IFERROR 遮盖错误。

方法二：使用辅助列

如果 Excel 版本没有 FILTER，可以增加辅助列。假设 A 列为地区、B 列为金额，D2 是目标地区。在 C2 输入：

=IF(A2=$D$2,B2,"")

向下填充后，C 列会保留目标地区的金额，其他行显示空字符串。在 F2 输入：

=SMALL(C2:C8,E2)

其中 E2 是 k。SMALL 会忽略区域中的文本，因此空字符串不会作为数值参与排序。实际工作簿中应确认 B 列确实是数值，避免数字以文本形式存储导致结果与预期不一致。

方法三：旧版数组公式

在支持传统数组公式、但没有 FILTER 的版本中，也可以用 IF 生成条件数组。示例公式为：

=SMALL(IF($A$2:$A$8=$D$2,$B$2:$B$8),$E$2)

在较旧版 Excel 中，输入公式后通常需要按 Ctrl+Shift+Enter 确认；Excel 会以数组公式方式计算。较新的动态数组版本一般直接按 Enter。不同版本的行为可能不同，应根据当前 Excel 版本测试。若条件匹配数量少于 E2，仍会遇到无效 k 的问题。

六、空白、文本、逻辑值和重复值

空白单元格

在引用区域中，真正的空白单元格不作为数值参与 SMALL 的排序。区域里即使有空白，只要仍有足够的数值，函数可以正常返回结果。空白不会自动当作 0；但如果单元格实际存的是数值 0，0 会参与排序，并可能成为最小值。

文本和数字文本

区域中的普通文本不参与数值排序。看起来像数字的文本，例如以文本格式保存的“12”，不一定会按数值 12 参与计算。可以检查单元格格式和数据来源，必要时使用“分列”、VALUE 或乘以 1 等方式将数据转换为数值；转换前要确认不会破坏前导零等业务信息。

SMALL 对数组参数中的文本和逻辑值有忽略规则，但不同的数据来源和公式构造方式会影响值以何种形式进入数组。不要依赖不清楚的隐式转换；对需要纳入排序的内容，先明确转换成数值，对不需要的内容则明确排除。

逻辑值

在引用区域中，TRUE 和 FALSE 通常不作为数值数据参与 SMALL 的排序。若通过公式构造数组，逻辑值是否被转换成数值取决于构造过程，不能把它们未经检查地当作普通数值使用。

重复值

重复值会分别占据排序位置。例如数据为 2、2、5，第一小和第二小都为 2。若需要第 k 小的不同值，必须先去重，再排序或取值。支持动态数组的 Excel 可先用 UNIQUE 生成唯一列表，再对其使用 SMALL：

=SMALL(UNIQUE(A2:A10),2)

该公式依赖 UNIQUE 函数。若数据包含空白或文本，也应先决定这些内容是否要保留，再构造唯一值列表。

七、参数限制与错误结果

#NUM!：k 越界或没有可用数值

当 k 小于 1、为 0 或负数时，SMALL 返回 #NUM!。当 k 大于 array 中可供计算的数值个数时，也会返回 #NUM!。区域中没有可用数值时，任何正整数 k 都无法取得结果。

例如，A2:A4 中只有两个数值，下面公式的 k=3 超出范围：

=SMALL(A2:A4,3)

k 通常应是整数。若单元格中输入了小数，不要依赖函数按预期把它四舍五入；应在业务规则中明确是否允许小数，并通过数据验证或显式取整处理。

#VALUE!：参数类型不合适

如果 k 不是可用的数值，或参数表达式产生无法计算的类型，可能出现 #VALUE!。检查 k 所在单元格是否存为文本、是否引用了错误单元格，以及公式中的参数分隔符是否符合当前 Excel 的区域设置。

源数据中的错误值

如果 array 引用的区域含有诸如 #N/A、#VALUE! 等错误，错误可能传递到 SMALL 的计算结果。遇到此类情况，应先查明错误产生原因，或在输入数据阶段进行明确清理。不要把错误单元格简单替换为 0，除非 0 在业务含义上确实正确；否则最小值可能因此被错误地拉低。

区域范围过大或变化

使用整列引用（例如 A:A）虽然方便，但大型工作簿中可能增加计算负担，也容易把表头、说明文字或其他数据一并纳入。优先使用准确的区域、Excel 表格结构化引用，或根据数据长度动态扩展的范围。

八、常见错误及排查方法

现象	常见原因	检查方法
返回 #NUM!	k 小于 1；k 大于可用数值数量；范围没有数值。	确认 k 是有效整数，并用 COUNT 检查区域中的数值数量。
第二小值和最小值相同	数据中存在重复的最小值。	检查排序后的数据；SMALL 按位置取值，不会自动去重。
忽略了一些看似数字的内容	这些单元格可能以文本形式存储。	用 ISNUMBER 检查，确认后再转换数据类型。
最小值意外变成 0	区域中存在实际数值 0，或公式把不满足条件的行返回为 0。	检查源数据及条件数组的构造方式，避免用 0 代表“不匹配”。
条件筛选公式报错	没有匹配记录、匹配数量小于 k，或版本不支持 FILTER。	分别检查匹配数量、k 值和 Excel 版本。
复制公式后范围变化	数据区域使用相对引用，填充时地址发生移动。	对固定区域使用绝对引用，例如 $B$2:$B$8。

可以用以下公式辅助统计区域中的数值个数：

=COUNT(A2:A10)

若想验证 k 是否在有效范围内，可在辅助单元格中使用：

=AND(ISNUMBER(B1),B1=INT(B1),B1>=1,B1<=COUNT(A2:A10))

这里假设 B1 是 k。检查通过只表示 k 的类型和范围符合基本要求，不代表 array 中没有错误值，也不代表业务筛选条件正确。

九、SMALL 与相关函数的区别

• MIN：返回最小值，不需要指定 k。只关心最低值时，MIN 表达意图更直接。
• SMALL：返回第 k 小的值，重复值占据多个位置。
• LARGE：返回第 k 大的值，适合从大到小取值。
• RANK.EQ：计算一个数值在数据集中的排名；它回答“这个值排第几”，而 SMALL 回答“第 k 位的值是多少”。并列值的排名处理与 SMALL 的位置计数不同。
• SORT：对数组整体排序；若需要输出完整的有序列表，动态数组版本可考虑 SORT，而非逐行重复调用 SMALL。

选函数时，应先明确目标：要一个极值用 MIN，要指定位置的数值用 SMALL，要排名用 RANK.EQ，要整列排序则考虑 SORT。函数返回结果相似，不代表统计含义相同。

十、安全与数据质量边界

SMALL 是用于数值计算的工作表函数，不是安全验证工具。它不会验证数据来源、权限、身份、文件是否可信，也不能证明某个记录符合合规要求。不要用“某列的第 1 小值”作为授权、风控放行或访问控制的唯一依据。安全策略应在具备权限控制和输入校验的应用逻辑中实现，并保留必要的审计记录。

SMALL 不会执行单元格中的任意文本作为代码，但这不意味着包含公式的工作簿天然安全。打开来源不可信的工作簿时，仍需按照组织的安全流程处理公式、外部链接、宏和数据连接。不要把函数计算结果当作恶意内容检测或公式注入防护机制。

对于从 CSV、网页或其他系统导入的数据，应先确定哪些字段是可信数值，检查文本数字、空值、异常值和错误值，再进行统计。公式只能根据实际输入计算，无法替代数据清洗和业务规则校验。

结语

SMALL 的核心很简单：=SMALL(array,k) 从给定数据中返回第 k 小的数值。使用时重点检查三件事：array 是否包含预期的数值、k 是否在可用范围内、重复值和条件筛选是否符合业务定义。掌握这些边界后，SMALL 可以用于提取低位数值、构造升序列表，以及配合 FILTER 或辅助列完成条件取值；但它不会替你清理数据，也不适合承担安全校验职责。