一、应用场景分析
在档案管理、库存管理或数据整理工作中，我们经常遇到这样的需求：根据某个条件（如盒号）快速查找并返回所有相关的数据（如档号）。传统的手工筛选方式效率低下，特别是当数据量大时，重复操作会消耗大量时间。
今天我将分享一个高效解决方案：使用Excel的SMALL函数结合INDEX函数，实现根据盒号动态返回所有档号的功能。
二、数据示例
假设我们有如下档案数据表：
需求：在指定单元格（如F1）中输入盒号，自动返回所有对应档号。
三、核心公式解析
3.1 基本查询公式
=INDEX(A:A, SMALL(IFERROR(($B$2:$B$16=$F$1)^0*ROW($B$2:$B$16), 25536), ROW(1:1))) & ""
3.2 逐层拆解分析
第一层：条件判断
$B$2:$B$16 = $F$1
作用：将B列（盒号）的每个单元格与F1（查询条件）进行比较
结果：返回TRUE或FALSE的数组
示例：如果F1=1，返回 {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;...}
第二层：逻辑值转换
(...)^0
技巧：任何数的0次方都等于1，但Excel中TRUE^0=1，FALSE^0=#VALUE!错误
作用：将TRUE转换为1，FALSE转换为错误值
结果：{1;1;1;#VALUE!;#VALUE!;#VALUE!;1;...}
第三层：生成行号数组
ROW($B$2:$B$16)
作用：生成对应区域的行号
结果：{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}
第四层：条件筛选行号
($B$2:$B$16=$F$1)^0 * ROW($B$2:$B$16)
原理：1 * 行号 = 行号，错误值 * 行号 = 错误值
结果：符合条件的行号保留，不符合的变为错误值
示例：{2;3;4;#VALUE!;#VALUE!;#VALUE!;8;9;10;#VALUE!;#VALUE!;#VALUE!;14;15;16}
第五层：错误值处理
IFERROR(..., 25536)
作用：将所有错误值替换为一个极大值（25536）
技巧：25536是Excel 2003的最大行数，新版Excel最大行数是1048576
结果：{2;3;4;25536;25536;25536;8;9;10;25536;25536;25536;14;15;16}
第六层：提取第k个最小值
SMALL(数组, ROW(1:1))
第一次计算（ROW(1:1)=1）：提取最小的符合条件的行号 = 2
第二次计算（ROW(2:2)=2）：提取第二小的符合条件的行号 = 3
以此类推：依次提取所有符合条件的行号
当没有更多匹配项时：返回25536
第七层：根据行号返回值
INDEX(A:A, 行号)
作用：返回A列（档号列）对应行的值
当行号=25536时：INDEX(A:A, 25536) 通常返回空值（0）
第八层：空值美化
& ""
问题：INDEX返回空值时显示为0
解决：连接空字符串，将0显示为空白单元格
结果：整洁美观的查询结果
视频演示：
根据盒号返回所有相关的档号（small、index函数）
四、完整设置步骤
4.1 准备查询区域
在F1单元格输入查询盒号（如1）
在D列（或其他空白列）设置返回区域
4.2 输入公式
在D2单元格输入：
=INDEX(A:A, SMALL(IFERROR(($B$2:$B$16=$F$1)^0*ROW($B$2:$B$16), 25536), ROW(1:1))) & ""
4.3 向下填充
将D2公式向下拖动填充足够行数（至少覆盖可能的最大结果数）
4.4 动态显示
当F1输入"1"时，D列显示：0563-gx-001-0001、0563-gx-001-0002、0563-gx-001-0003、0563-gx-003-0001...
当F1输入"2"时，D列显示：0563-gx-002-0001、0563-gx-002-0002、0563-gx-002-0003、0563-gx-004-0001...
五、重要技巧说明
5.1 &"" 与 IFERROR 的选择
// 情况1：INDEX参数为整列引用
=INDEX(A:A, ...)& ""
// 当行号超出数据范围时，INDEX返回0，&""可将0转为空白// 情况2：INDEX参数为限定区域
=INDEX($A$1:$A$16, ...) & ""
// 当行号超出$A$1:$A$16范围时（如25536），返回#REF!错误
// &""无法处理错误值，需要IFERROR：
=IFERROR(INDEX($A$1:$A$16, ...) ,"")
5.2 为什么用25536？
作为错误值的替代标志
远大于实际数据行数，确保SMALL函数最后才提取到这个值
避免与有效行号冲突
5.3 动态调整数据范围
如果数据可能增加，建议使用动态范围：
=INDEX(A:A, SMALL(IFERROR(($B$2:$B$1000=$F$1)^0*ROW($B$2:$B$1000), 65536), ROW(1:1))) & ""
预留足够空间（如1000行）
新版Excel可使用65536或1048576作为极大值
六、进阶应用
6.1 多条件查询
如果需要同时满足盒号和年份条件：
=INDEX(A:A, SMALL(IFERROR(($B$2:$B$100=$F$1)*($C$2:$C$100="2023")*ROW($B$2:$B$100), 65536), ROW(1:1))) & ""
6.2 显示序号
在结果前添加序号：
=IF(E2="", "", ROW(1:1) & ". " & E2)
6.3 统计匹配数量
=COUNTIF($B$2:$B$100, $F$1)
七、常见问题解答
Q1：为什么显示#NUM!错误？
A：检查ROW(1:1)参数是否正确，确保向下填充时ROW函数能正确递增。
Q2：如何让查询结果不重复？
A：如果需要去重，可以结合MATCH函数创建更复杂的数组公式。
Q3：数据更新后公式不自动重算？
A：按F9手动重算，或设置Excel为自动计算模式。
Q4：如何提高大数据的计算速度？
A：1. 精确限定数据范围，避免整列引用
2. 使用Excel表格（Ctrl+T）获得结构化引用
3. 考虑使用Power Query处理超大数据集
八、总结
通过这个案例，我们掌握了：
✅SMALL函数的巧妙应用：提取符合条件的行号序列
✅INDEX函数的精准定位：根据行号返回对应数据
✅逻辑运算的技巧：使用^0转换TRUE/FALSE
✅错误处理的智慧：IFERROR与&""的配合使用
✅动态查询的实现：根据输入条件实时返回结果
这个方法不仅适用于档案管理，还可广泛应用于：
库存查询（根据产品编号查询所有批次）
学生管理（根据班级查询所有学生）
销售分析（根据地区查询所有订单）
项目管理（根据负责人查询所有任务）
掌握这个技巧，你将能显著提升数据处理效率，告别繁琐的手工筛选工作！
计算机科学与技术 & 计算机网络技术：双专业课程体系完全导航指南
