优化Google Sheets中多选手“好友与对手”统计量的计算

需求说明 #

计算两名选手的「好友与对手」统计量：取两人各轮分数的绝对差，再求平均值——差值越高（百分位越高）则为「对手」，越低则为「好友」。

示例：

• 选手1分数：60%、80%、50%、80%、100%、90%
• 选手2分数：50%、10%、30%、30%、80%、80%
• 各轮差值：10%、70%、20%、50%、20%、10%
• 平均值（统计量）：30%

当前问题 #

你当前使用的单个单元格公式：

=IF(H$2=$A3,,ARRAYFORMULA(AVERAGE(ABS(VLOOKUP($A3,$A$3:$G$13,SEQUENCE(6,1,2),0)-VLOOKUP(H$2,$A$3:$G$13,SEQUENCE(6,1,2),0)))))

仅适用于单个单元格，扩展到数百个单元格时操作繁琐，且重复的VLOOKUP计算会导致表格严重卡顿。

优化方案 #

使用MAKEARRAY+LET组合的数组公式，只需在结果矩阵的左上角单元格输入一次，即可自动生成所有选手对的统计量，大幅提升效率。

假设：

• 选手名单区域：A3:A13
• 选手分数区域：B3:G13
• 结果矩阵从H3开始（列标题为选手名）

公式如下：

=MAKEARRAY(ROWS(A3:A13), COUNTA(H2:2), LAMBDA(r,c, 
  LET(
    player1, INDEX(A3:A13, r),
    player2, INDEX(H2:2, c),
    IF(player1=player2, "",
      AVERAGE(ABS(INDEX(B3:G13, r, 0)-INDEX(B3:G13, MATCH(player2, A3:A13, 0), 0)))
    )
  )
))

公式解释 #

1. MAKEARRAY：直接生成对应行列数的结果矩阵，无需逐个单元格填充公式
2. LET：定义变量简化公式逻辑，player1为当前行的选手，player2为当前列的选手
3. 选手重复判断：当player1和player2为同一人时，返回空值
4. 高效差值计算：用INDEX直接定位选手的分数行（替代多次VLOOKUP），计算两行分数的绝对差后取平均值，减少重复遍历区域的计算量

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