基于Google Forms与Sheets的学生烹饪实验评分自动计算求助

1. 自动关联自评/互评并计算单次实验平均分 #

前提：表单需包含实验编号/日期、评分人姓名、被评分人姓名、评分（15分制）四个核心字段，确保所有提交绑定到对应实验。

• 筛选单次实验数据：在关联的「表单响应 1」表中，用QUERY函数提取目标实验的所有评分记录：

  =QUERY('表单响应 1'!A:D, "SELECT * WHERE A = '实验1'", 1)
  

• 计算单个学生平均分：新建临时区域，A列填学生姓名，B列用AVERAGEIFS整合自评与互评得分：

  =AVERAGEIFS('表单响应 1'!D:D, '表单响应 1'!B:B, A2, '表单响应 1'!A:A, "实验1")
  

  如需拆分自评/互评单独计算，可追加条件区分：

  # 自评分数
  =AVERAGEIFS('表单响应 1'!D:D, '表单响应 1'!B:B, A2, '表单响应 1'!A:A, "实验1", '表单响应 1'!C:C, A2)
  # 互评分数
  =AVERAGEIFS('表单响应 1'!D:D, '表单响应 1'!B:B, A2, '表单响应 1'!A:A, "实验1", '表单响应 1'!C:C, "<>"&A2)
  

2. 自动生成单次实验独立工作表 #

用Google Apps Script实现表单提交时自动创建对应实验的工作表：

1. 打开关联的Sheets文件，点击「扩展程序」→「Apps脚本」
2. 替换默认代码为以下脚本（需根据实际表单字段名调整注释处的字段名称）：

function createExperimentSheetOnSubmit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const responseSheet = ss.getSheetByName('表单响应 1');
  const lastRow = responseSheet.getLastRow();
  const currentExp = responseSheet.getRange(lastRow, 1).getValue(); // 第1列是实验编号，按需调整列号
  
  // 若该实验工作表已存在则跳过
  if (ss.getSheetByName(currentExp)) return;
  
  // 新建实验工作表
  const newSheet = ss.insertSheet(currentExp);
  const header = responseSheet.getRange(1, 1, 1, responseSheet.getLastColumn()).getValues()[0];
  newSheet.appendRow(header);
  
  // 筛选并写入当前实验的所有数据
  const allData = responseSheet.getDataRange().getValues();
  const expData = allData.filter(row => row[0] === currentExp); // 第0位对应实验编号列，按需调整索引
  expData.slice(1).forEach(row => newSheet.appendRow(row));
  
  // 自动添加平均分计算区域
  newSheet.getRange('F1').setValue('学生姓名');
  newSheet.getRange('G1').setValue('平均分');
  const students = [...new Set(expData.slice(1).map(row => row[1]))]; // 第1位对应被评分人姓名列，按需调整索引
  
  students.forEach((student, idx) => {
    newSheet.getRange(idx+2, 6).setValue(student);
    newSheet.getRange(idx+2, 7).setFormula(`=AVERAGEIFS('${currentExp}'!D:D, '${currentExp}'!B:B, F${idx+2}, '${currentExp}'!A:A, "${currentExp}")`);
  });
}

3. 保存脚本后，设置触发器：点击「编辑」→「当前项目的触发器」→ 添加触发器，选择「事件源：表单提交」，触发该函数。此后每次表单提交都会自动检查并创建对应实验的独立工作表。

3. 汇总各次实验评分计算最终总成绩 #

新建「总成绩汇总」工作表，按以下步骤配置：

• A列提取所有学生姓名（去重）：

  =UNIQUE('表单响应 1'!B:B)
  

• B列及以后各列对应单次实验的平均分，以「实验1」为例，B2单元格公式：

  =IFERROR(VLOOKUP(A2, '实验1'!F:G, 2, FALSE), 0)
  

  （缺勤学生返回0，可根据需求调整为空白）
• 最后一列计算最终总成绩（按各实验平均分平均计算）：

  =AVERAGE(B2:Y2)
  

  如需加权计算，可使用SUMPRODUCT（假设B1:Y1为各实验权重）：

  =SUMPRODUCT(B2:Y2, $B$1:$Y$1)/SUM($B$1:$Y$1)
  

内容的提问来源于stack exchange，提问作者Rodney St. Pierre