Excel如何查找与参考值最接近的前3个数值

适用版本（Excel 365/2021及以上） #

直接输入以下动态数组公式，会自动溢出前3个结果：

=TAKE(SORTBY('[外部文件名.xlsx]工作表名'!$数据区域,ABS('[外部文件名.xlsx]工作表名'!$数据区域-参考值),1),3)

示例适配你的场景 #

你的测试数据如果存在外部文件数值库.xlsx的Sheet1工作表A2:A10区域，参考值为30，公式写为：
=TAKE(SORTBY('[数值库.xlsx]Sheet1'!$A$2:$A$10,ABS('[数值库.xlsx]Sheet1'!$A$2:$A$10-30),1),3)

公式说明

• 跨文件引用格式固定为'[文件名]工作表名'!单元格区域，外部文件关闭时可填写完整绝对路径，比如'C:\桌面\[数值库.xlsx]Sheet1'!$A$2:$A$10即可正常读取
• 内层ABS(数据区域-参考值)计算每个数值和参考值的绝对差值，作为排序依据
• SORTBY按差值升序排列，差值越小的数值越靠前
• TAKE提取排序后的前3个结果，需要调整输出数量直接修改末尾的数字即可
• 针对你的测试数据，公式输出结果为29、28、27，完全匹配预期

兼容旧版Excel（无SORTBY/TAKE函数） #

在首个输出单元格输入以下数组公式，按Ctrl+Shift+Enter触发数组计算，之后下拉填充3行即可：
=INDEX('[数值库.xlsx]Sheet1'!$A$2:$A$10,SMALL(IF(ABS('[数值库.xlsx]Sheet1'!$A$2:$A$10-30)=SMALL(ABS('[数值库.xlsx]Sheet1'!$A$2:$A$10-30),ROW(A1)),ROW($1:$9),4^8),1))

注意事项 #

• 公式中ROW($1:$9)对应数据区域的行数，你的测试数据共9行，数据量变化时同步调整即可
• 4^8是远大于数据行数的容错值，可根据实际数据量调整

内容的提问来源于stack exchange，提问作者Edwin