Google Sheets中基于多列匹配的AVERAGEIF函数使用问题

我需要在Google Sheets中实现：当某个姓名出现在某一行的指定列中时，计算该行对应「分数」列的平均值，且姓名可能出现在同一行的多个指定列里。

示例数据 #

期望结果 #

当前实现及问题 #

我已经通过以下步骤完成部分功能：

• 用 =sort(UNIQUE(FLATTEN(B2:D4))) 生成唯一姓名列表
• 用 =countif(B$2:D$4,A7) 统计每个姓名的出现次数

但计算平均分数时出错：在C7单元格使用 =averageif(B$2:D$4,A7,A$2:A$4) 并下拉后，得到错误结果：

问题表现：

• Anna和Michelle仅取了第二次匹配对应的分数
• Lois直接出现#DIV/0!错误

错误原因 #

AVERAGEIF 要求条件区域和平均区域的尺寸完全匹配。你用的条件区域是 B2:D4（3行×3列），平均区域是 A2:A4（3行×1列），两者尺寸不匹配。此时函数只会检查条件区域中每行第一个单元格是否匹配，比如：

• Anna在C2（第2行第3列）和B3（第3行第2列），但AVERAGEIF只识别B列的匹配项（B3），对应分数4
• Lois在C3（第3行第3列），B列无匹配项，所以找不到对应分数，触发#DIV/0!

解决方案 #

单个单元格公式（针对单个姓名） #

在C7单元格输入以下公式，按 Ctrl+Shift+Enter 执行数组计算（Google Sheets部分版本直接按Enter即可生效）：

=AVERAGE(IF(B$2:D$4=A7,A$2:A$4))

更稳妥的通用公式（兼容所有场景） #

使用FILTER+MMULT组合，避免数组计算的兼容性问题：

=AVERAGE(FILTER(A$2:A$4,MMULT(--(B$2:D$4=A7),SEQUENCE(COLUMNS(B$2:D$4),1))>0))

原理：

1. --(B$2:D$4=A7) 将姓名匹配结果转为1（匹配）或0（不匹配）
2. MMULT(...,SEQUENCE(...)) 对每行的匹配值求和，大于0说明该行存在目标姓名
3. FILTER 筛选出所有符合条件的分数，最后用AVERAGE取平均值

批量生成所有姓名的平均值 #

如果要一次性计算所有姓名的平均值，可使用BYROW批量处理（假设姓名列表在A7:A10）：

=BYROW(A7:A10,LAMBDA(name,AVERAGE(FILTER(A$2:A$4,MMULT(--(B$2:D$4=name),SEQUENCE(COLUMNS(B$2:D$4),1))>0))))

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