如何在17000行多城市数据中用INDEX&MATCH找最接近权重均值的数值？

嘿，这个需求我刚好碰过类似的，针对你这17000行的大数据量，我给你两种实用方案，一种是你提到的INDEX+MATCH组合，另一种是更高效的新版函数方案，都能完美解决：

方案1：INDEX+MATCH组合（兼容多数Excel版本） #

假设你的城市数据在A列，权重在B列，已经算出目标城市（比如A）的均值放在单元格D1（可以用=AVERAGEIF(A:A,"A",B:B)计算）。

要找到A城市中最接近该均值的权重，你可以用这个数组公式：

=INDEX(B2:B17001,MATCH(MIN(IF(A2:A17001="A",ABS(B2:B17001-D1),999999)),IF(A2:A17001="A",ABS(B2:B17001-D1),999999),0))

注意：旧版Excel需要按Ctrl+Shift+Enter确认数组公式，新版Excel会自动识别。

公式逻辑拆解： #

• IF(A2:A17001="A",ABS(B2:B17001-D1),999999)：只计算A城市权重与均值的绝对差，非A城市的差设为一个极大值（避免干扰最小差的计算）
• MIN(...)：找出A城市里最小的绝对差
• MATCH(...)：定位这个最小差在数组中的位置
• INDEX(...)：根据位置返回对应的权重值

如果有多个权重和均值的差相同，这个公式会返回第一个出现的那个值。

方案2：XLOOKUP（Excel 365/2021及以上版本，更简洁高效） #

如果你用的是新版Excel，XLOOKUP会更省心，不需要手动触发数组输入，公式更简洁：

=XLOOKUP(MIN(IF(A2:A17001="A",ABS(B2:B17001-D1))),IF(A2:A17001="A",ABS(B2:B17001-D1)),B2:B17001,"",0,1)

公式逻辑拆解： #

• 第一个参数：算出A城市权重与均值的最小绝对差
• 第二个参数：生成A城市所有权重与均值的绝对差数组
• 第三个参数：对应返回的权重值
• 最后两个参数0,1：表示精确匹配，返回第一个符合条件的结果

针对17000行数据的性能优化 #

因为数据量较大，强烈建议不要用整列引用（比如A:A、B:B），而是用实际的数据范围（比如A2:A17001、B2:B17001），这样能大幅减少公式的计算量，提升运行速度。

如果需要批量处理多个城市，可以把城市名作为变量（比如放在D列），均值放在E列，然后把公式里的"A"替换成D1，下拉就能批量生成所有城市的结果：

=INDEX(B2:B17001,MATCH(MIN(IF(A2:A17001=D1,ABS(B2:B17001-E1),999999)),IF(A2:A17001=D1,ABS(B2:B17001-E1),999999),0))

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