如何为数组X的每行匹配数组Y中相关性最高的行？

针对数据数组X的每一行，需找出数据数组Y中与之相关性最高的行（编号或索引）。

示例数据：

• X包含两行：
  • X1：值1=10、值2=5、值3=1
  • X2：值1=1、值2=5、值3=10
• Y包含三行：
  • Y1：值1=1、值2=4、值3=10
  • Y2：值1=3、值2=4、值3=3
  • Y3：值1=10、值2=4、值3=1

预期结果：X1对应Y3，X2对应Y1。

此前尝试用INDEX+SORTN结合ARRAYFORMULA(CORREL(X1,Y1:Y))的方法无效，原因是当CORREL的其中一个参数为数组而非向量时，会自动将行拼接成单一向量，导致计算结果错误。

解决方案 #

在Google Sheets中可以通过嵌套BYROW结合LET函数实现需求，公式如下：

=BYROW(X_range, LAMBDA(x_row, 
  LET(
    corr_vals, BYROW(Y_range, LAMBDA(y_row, CORREL(x_row, y_row))),
    max_corr, MAX(corr_vals),
    match_index, MATCH(max_corr, corr_vals, 0),
    "Y"&match_index
  )
))

公式说明 #

1. BYROW(X_range, LAMBDA(x_row, ...))：遍历X数组的每一行，将当前行赋值给x_row
2. 内部LET函数定义变量简化逻辑：
   • corr_vals：遍历Y数组的每一行，计算当前X行与该行的相关系数，生成一个存储所有相关系数的数组
   • max_corr：提取corr_vals中的最大值，即当前X行与Y数组的最高相关系数
   • match_index：匹配最大值在corr_vals中的位置，对应Y数组的行索引
   • 最后返回拼接后的Y行编号（如"Y1"、"Y3"）

使用注意 #

将公式中的X_range和Y_range替换为实际的单元格范围，例如X数组是A2:C3、Y数组是E2:G4。

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