Google Sheets中按类别加权平均算成绩而非所有项目加权平均

问题核心 #

当前用AVERAGE.WEIGHTED公式会把每一项作业单独计算权重，导致作业数量多的类别（比如10次Quizzes、1次Tests）主导最终成绩，违背了Quizzes和Tests各占50%的权重规则。需要先计算每个类别的平均百分比得分，再对这些类别平均分按设定权重算出最终成绩。

数据结构 #

• 第5行：作业类别名称（如"Quizzes"、"Tests"）
• 第7行：对应作业的满分值
• 第8行：学生在对应作业的得分
• 设置表'SUBJECT SETTINGS'!I6:J15：I列为类别名称，J列为该类别的权重（如50%）

修正后的公式（放在C8单元格） #

=IFERROR(
  SUMPRODUCT(
    QUERY(
      {TRANSPOSE($E$5:$5), TRANSPOSE($E$7:$7), TRANSPOSE($E8:8)},
      "select avg(Col3/Col2) where Col3 is not null group by Col1 label avg(Col3/Col2)''"
    ),
    VLOOKUP(
      UNIQUE(FILTER($E$5:$5, $E8:8<>"")),
      'SUBJECT SETTINGS'!$I$6:$J$15,
      2,
      FALSE
    )
  ),
  "-"
)

公式解析 #

1. 转置数据：用TRANSPOSE把横向的类别、满分、得分转成纵向结构，方便按类别分组计算
2. 计算类别平均百分比：QUERY函数筛选出有得分的记录，按类别分组后，算出每个类别下「得分/满分」的平均值，得到该类别的整体表现百分比
3. 匹配类别权重：通过UNIQUE(FILTER)提取当前学生有得分的所有类别，再用VLOOKUP从设置表中获取对应权重
4. 加权求和：SUMPRODUCT把每个类别的平均百分比和对应权重相乘后累加，得到最终加权成绩
5. 异常处理：IFERROR确保学生无任何得分时，显示"-"而非错误值

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