如何在Google Sheets中按指定方式聚合人员月度平均得分

一、仅用Google Sheets实现需求的最优方法 #

推荐使用LET+FLATTEN+QUERY的组合公式，一步完成数据重塑、分组求平均和透视，代码简洁且易维护：

=LET(
  原始数据, test_1!A:E,
  姓名列, FILTER(INDEX(原始数据,,2), INDEX(原始数据,,1)<>""),
  日期列, FILTER(INDEX(原始数据,,1), INDEX(原始数据,,1)<>""),
  指标列表, {"Quality","Speech","Politeness"},
  指标值列, FILTER(HSTACK(INDEX(原始数据,,3), INDEX(原始数据,,4), INDEX(原始数据,,5)), INDEX(原始数据,,1)<>""),
  重塑数据, FLATTEN(HSTACK(姓名列, 日期列, REPT(指标列表, ROWS(姓名列)), 指标值列)),
  透视结果, QUERY(重塑数据, "select Col1, Col3, AVG(Col4) where Col1 is not null group by Col1, Col3 pivot FORMAT_DATE('yyyy-MM', Col2)", 1),
  透视结果
)

公式说明： #

• LET：封装变量，简化公式结构，避免重复引用原始数据
• FILTER：过滤原始数据中的空行，仅保留有效记录
• FLATTEN+HSTACK：将宽格式的指标列（Quality/Speech/Politeness）转成「姓名-日期-指标-分值」的长格式，为后续分组做准备
• QUERY：按姓名、指标分组计算月份平均分，通过pivot将月份转为列名，直接输出目标格式

二、修改现有QUERY公式以得到目标结果 #

现有公式的核心问题：未将日期格式化为月份、指标以列形式呈现而非行、未做透视转换。可分两步修改：

步骤1：修改原始QUERY，按姓名+月份分组计算指标平均分 #

先将日期格式化为yyyy-MM格式，得到姓名、月份、三个指标平均分的中间结果：

=QUERY(test_1!A:E, "select B, FORMAT_DATE('yyyy-MM', A), AVG(C), AVG(D), AVG(E) where A is not null group by B, FORMAT_DATE('yyyy-MM', A) label FORMAT_DATE('yyyy-MM', A) '月份', AVG(C) 'Quality', AVG(D) 'Speech', AVG(E) 'Politeness'", 1)

步骤2：将中间结果的指标列转为行，并透视月份 #

假设步骤1的结果放在F1:I区域，用以下公式完成最终转换：

=QUERY(
  FLATTEN(HSTACK(F2:F, REPT({"Quality","Speech","Politeness"}, ROWS(F2:F)), G2:G, H2:H, I2:I)),
  "select Col1, Col2, AVG(Col3) where Col1 is not null group by Col1, Col2 pivot Col4",
  1
)

合并为单公式（无需中间区域） #

如果不想使用中间区域，可将两步合并为一个公式：

=QUERY(
  FLATTEN(
    HSTACK(
      QUERY(test_1!A:E, "select B where A is not null group by B, FORMAT_DATE('yyyy-MM', A)", 1),
      REPT({"Quality","Speech","Politeness"}, ROWS(QUERY(test_1!A:E, "select B where A is not null group by B, FORMAT_DATE('yyyy-MM', A)", 1))),
      QUERY(test_1!A:E, "select AVG(C), AVG(D), AVG(E) where A is not null group by B, FORMAT_DATE('yyyy-MM', A)", 1)
    )
  ),
  "select Col1, Col2, AVG(Col3) where Col1 is not null group by Col1, Col2 pivot FORMAT_DATE('yyyy-MM', Col1)",
  1
)

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