求X、Y、Z坐标最邻近匹配公式（Y值符号反转场景）

问题背景 #

• 两组XYZ点数据需匹配对应点，核心差异为Y值符号反转（目标匹配的Y值为数据源D列值的相反数）
• 当前使用的精确匹配公式仅能处理完全匹配的场景，无法覆盖数百条数据中约百条非精确匹配的情况，需要实现最邻近匹配逻辑
• 当前精确匹配公式：

  =INDEX($C$7:$C$1048576; MATCH(1; (I120=$D$7:$D$1048576) * (J120=$E$7:$E$1048576) * (K120=$F$7:$F$1048576); 0))
  

最邻近匹配公式方案 #

Excel 365/2021（动态数组版本） #

直接输入以下公式即可自动计算：

=INDEX($C$7:$C$1048576,MATCH(MIN(SQRT((I120-$D$7:$D$1048576)^2+(J120-(-$E$7:$E$1048576))^2+(K120-$F$7:$F$1048576)^2)),SQRT((I120-$D$7:$D$1048576)^2+(J120-(-$E$7:$E$1048576))^2+(K120-$F$7:$F$1048576)^2),0))

• 逻辑：计算目标点（I120,J120,K120）与数据源中每个点（D列X、取反后的E列Y、F列Z）的欧氏距离，找到距离最小的点，返回对应C列的值
• 注意：若存在多个距离相同的最小点，公式会返回第一个匹配的结果

旧版Excel（需数组输入） #

输入公式后，必须按Ctrl+Shift+Enter触发数组计算：

=INDEX($C$7:$C$1048576,MATCH(MIN(SQRT((I120-$D$7:$D$1048576)^2+(J120-(-$E$7:$E$1048576))^2+(K120-$F$7:$F$1048576)^2)),SQRT((I120-$D$7:$D$1048576)^2+(J120-(-$E$7:$E$1048576))^2+(K120-$F$7:$F$1048576)^2),0))

效率优化建议 #

如果数据量较大，全量计算欧氏距离会拖慢速度，可先缩小匹配范围：

• 先筛选出X值在I120±设定误差、Z值在K120±设定误差范围内的行
• 再在这个子集内计算Y值（取反后）的距离，找到最邻近点

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