SMALL函数：精准定位数据中的“第N小”值

在Excel中分析数据时，我们常常需要找到特定排位的数值。

例如，找出销售数据中第三低的业绩，或自动列出成绩单中最靠后的五个分数。这时，SMALL函数就是你的得力工具。

它用法不难，但有几个关键点必须掌握，否则容易出错。本文将为你详细解析。

一、基础前提：确保数据与参数有效

使用SMALL函数前，必须确认以下两点，这是避免#NUM!错误的关键。

第一，数据区域必须为有效数值。

你选定的区域（如A2:A15）内应是纯数字。空格、文本或#DIV/0!等错误值不会被函数视为有效数据。

第二，参数“k”值必须合法。

“k”代表你要找“第几小”的值，它必须是正整数，且不能超过数据区域中有效数字的个数。

你可以用COUNT函数快速统计有效数字个数：=COUNT(A2:A15)。

假设结果为12，那么“k”值就必须在1到12之间。小于1或大于12，Excel都会返回错误。

二、直接调用：快速获取单个目标值

如果你只需要偶尔查找第3小或第5小的值，直接输入公式是最快的方法。

基本语法为：=SMALL(数据区域, k)。

例如，在空白单元格输入=SMALL(A2:A15, 3)，回车即可得到结果。函数会在原数据顺序不变的情况下，将数字从小到大排序，并返回排第三位的值。

若想连续列出最小的前几个值，可以在相邻单元格分别设置公式，并将“k”值依次改为1、2、3……

技巧：记得使用$符号锁定数据区域（如$A$2:$A$15），以便正确下拉填充公式。

三、动态序列：结合ROW函数自动填充

手动修改“k”值较为繁琐，尤其在需要生成长序列时。此时，ROW函数能实现自动填充。

操作步骤如下：

• 在首个单元格（如B2）输入公式：=SMALL($A$2:$A$15, ROW(A1))。
• 其中，ROW(A1)返回数字1，故此公式等同于=SMALL(...,1)。
• 将此公式向下拖动填充。ROW(A1)会随行号自动变为ROW(A2)、ROW(A3)…，返回值相应变为2、3…

这样，你就能一键生成从最小到第N小的完整升序列表。原始数据更新时，列表也会自动同步。

四、条件筛选：嵌套IF提取特定范围的值

实际数据往往更复杂，你可能需要按条件筛选。例如，从数据中找出“销售部”里业绩第二低的数字。

单独使用SMALL无法筛选，需要结合IF函数构建内存数组。

公式示例：=SMALL(IF(A2:A20="销售部", B2:B20), 2)

这个公式的含义是：

• 先用IF函数判断，如果A列为“销售部”，则返回对应的B列数值，否则返回FALSE。
• 然后，SMALL函数会忽略所有FALSE值，仅对符合条件的数值进行排序，并找出第二小的值。

版本注意：

• 在旧版Excel中，输入此公式后需按Ctrl+Shift+Enter组合键确认（公式两端会出现大括号{}）。
• 在Office 365或Excel 2021等新版中，直接按回车即可。

五、稳健进阶：用AGGREGATE函数忽略错误值

当数据区域本身包含#N/A、#DIV/0!等错误值时，SMALL函数会直接返回错误。

此时，功能更强大的AGGREGATE函数是更好的选择。它相当于SMALL的“防错增强版”。

用法示例：=AGGREGATE(15, 6, B2:B15, 3)

• 第一个参数15：代表执行“找第N小值”操作。
• 第二个参数6：代表“忽略错误值和隐藏行”。
• 后两个参数：与SMALL一致，分别为数据区域和“k”值。

使用此公式，即使B2:B15区域存在错误值，它也能稳定计算出所有有效数字中的第三小值。

总结

使用SMALL函数提取第N小的值，核心在于确保数据区域为纯数值，且k值在有效范围内。

你可以：

• 直接输入公式获取单个值。
• 结合ROW函数动态生成序列。
• 嵌套IF函数进行条件筛选。
• 在数据包含错误值时，改用AGGREGATE函数规避问题。

《夸克》非常好用的免费AI浏览器

下载APP查看