如何在Google Sheets中按类别汇总考试成绩表格？

不用脚本或复杂的数据透视表设置，用Google Sheets的内置公式就能快速实现需求，下面提供两种实用方法：

方法1：动态生成每个类别的成绩表格（自动排版） #

在新建的工作表中输入以下公式，它会自动提取所有类别，并按类别展示对应学生的姓名和该类所有题目成绩：

=LET(
  data, Sheet1!A:Z,  // 替换Sheet1为你的原表格名称
  headers, INDEX(data,1,2:COLUMNS(data)),
  categories, UNIQUE(REGEXEXTRACT(headers, "-(.+)")),  // 提取表头中的类别（假设表头格式为「Qx-类别名」）
  results, BYROW(categories, LAMBDA(cat, {
    "### 类别: "&cat,
    HSTACK(INDEX(data,,1), FILTER(INDEX(data,,2:COLUMNS(data)), REGEXEXTRACT(headers, "-(.+)")=cat))
  })),
  VSTACK(TOCOL(results, TRUE))
)

• 注意：如果你的表头类别格式不是「题号-类别」，修改REGEXEXTRACT的匹配规则即可（比如表头直接是类别名，就把REGEXEXTRACT(headers, "-(.+)")换成headers）。

方法2：手动指定类别生成对应表格（更灵活） #

1. 先提取所有唯一类别，在新表A1单元格输入：

=UNIQUE(ARRAYFORMULA(REGEXEXTRACT(Sheet1!B1:1, "-(.+)")))

2. 针对每个类别，在B1单元格输入公式并下拉，生成对应类别的成绩表：

=QUERY(Sheet1!A:Z, "select A,"&JOIN(",", FILTER(COLUMN(Sheet1!B:Z)-COLUMN(Sheet1!A)+1, REGEXEXTRACT(Sheet1!B1:1, "-(.+)")=A1))&"", 1)

补充：数据透视表的正确打开方式 #

如果还是想用数据透视表，先把原表格转成长格式（每一行对应一个学生的一道题成绩），再创建透视表就容易成功：

1. 在新表输入转换公式：

=ARRAYFORMULA(
  QUERY(
    {
      FLATTEN(Sheet1!A2:A),  // 学生姓名
      FLATTEN(Sheet1!B1:1),  // 题号
      REGEXEXTRACT(FLATTEN(Sheet1!B1:1), "-(.+)"),  // 类别
      FLATTEN(Sheet1!B2:Z)  // 成绩
    },
    "select Col1, Col3, Col2, Col4 where Col1 is not null", 0
  )
)

2. 选中转换后的长格式数据，插入数据透视表：
   • 行：选择「学生姓名」
   • 列：选择「类别」
   • 值：选择「成绩」（如果要展示每道题的成绩，可把「题号」也加入行/列）

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