如何基于Group #查找对应分组中与随机数最接近的数值？

假设你的数据结构如下：

• 第一个数据表命名为Table1，包含列Group #、Food、Qty
• 第二个表中，指定Group值在单元格A1（值为1），随机数在单元格B1（值为100）

适用于Excel 365/2021（支持动态数组）的公式 #

=INDEX(FILTER(Table1[Qty],Table1[Group #]=A1),MATCH(MIN(ABS(FILTER(Table1[Qty],Table1[Group #]=A1)-B1)),ABS(FILTER(Table1[Qty],Table1[Group #]=A1)-B1),0))

公式逻辑拆解：

• FILTER(Table1[Qty],Table1[Group #]=A1)：筛选出Group #等于指定值的所有Qty数据
• ABS(筛选后的Qty - B1)：计算每个筛选出的数值与目标随机数的差的绝对值
• MIN(...)：找到最小的绝对值，对应最接近目标数的数值
• MATCH(...)：定位这个最小绝对值在差数组中的位置
• INDEX(...)：从筛选后的Qty数组中取出对应位置的数值

适用于旧版Excel（无动态数组支持）的数组公式 #

=INDEX(Table1[Qty],MATCH(MIN(ABS(IF(Table1[Group #]=A1,Table1[Qty],"")-B1)),ABS(IF(Table1[Group #]=A1,Table1[Qty],"")-B1),0))

输入公式后需按Ctrl+Shift+Enter确认数组公式生效

为什么VLOOKUP/FILTER单独用不行？ #

• VLOOKUP仅支持按匹配值查找，无法直接处理“找最接近值”的逻辑，且近似匹配要求数据排序，不符合你的场景
• FILTER只能完成分组筛选，无法单独定位到最接近目标数的数值，必须结合绝对值计算、最小值查找和定位函数组合实现

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