Google Sheet将表单单行多学生数据拆分为附带共同家长信息的多行方法

无需调整现有表单结构，可直接适配当前最多3名学生/行的录入格式，实现单学生行自动关联家长信息的需求，操作步骤如下：

步骤1：全量同步源数据 #

先不要单独提取学生列，先将完整的表单响应表同步到当前学生总表的独立sheet中（可将该sheet命名为「源数据同步」），在该sheet的A1单元格输入公式：

=IMPORTRANGE("你的表单响应表分享链接", "表单响应1!A:I")

注：公式中列范围A:I对应最多3名学生的完整列：A家长姓名、B家长手机号、C主邮箱、D学生1姓名、E学生1DOB、F学生2姓名、G学生2DOB、H学生3姓名、I学生3DOB，可根据你的实际列数调整范围。首次输入需要授权权限，授权后即可自动同步所有表单提交数据。

步骤2：输入拆分公式 #

在你需要输出最终学生总表的sheet的A1单元格，输入以下公式即可自动生成符合要求的结果：

=LET(
  // 过滤源数据空行，以家长姓名列为非空判断依据
  源数据, FILTER('源数据同步'!A2:I, '源数据同步'!A2:A<>""),
  // 逐行处理每个家长的报名数据
  拆分结果, BYROW(源数据, LAMBDA(当前行,
    LET(
      // 提取前3列家长信息，复制3份对应最多3名学生
      家长组, CHOOSECOLS(当前行, {1,2,3,1,2,3,1,2,3}),
      // 把当前行的所有列转成3行结构：每行是[学生姓名, DOB, 家长姓名, 家长手机号, 主邮箱]
      行结构, WRAPROWS(家长组, 5, ""),
      // 把学生信息填入对应位置
      补全学生, HSTACK(WRAPROWS(CHOOSEROWS(当前行, 4,5,6,7,8,9),2), CHOOSECOLS(行结构, {3,4,5})),
      // 过滤掉未填写学生的空行
      FILTER(补全学生, INDEX(补全学生, ,1)<>""))
  )),
  // 拼接表头+所有拆分后的行
  VSTACK({"学生姓名","DOB","家长姓名","家长手机号","主邮箱"}, REDUCE("", 拆分结果, LAMBDA(累计, 行, VSTACK(累计, 行))))
)

适配调整说明 #

• 若你当前最多只有2名学生的字段，可将公式中的CHOOSEROWS(当前行, 4,5,6,7,8,9)调整为CHOOSEROWS(当前行, 4,5,6,7)，同时家长组复制次数改为2份即可
• 公式输出结果默认和源数据录入顺序一致，同一家长的多名学生会连续排列
• 表单有新的报名提交后，数据会自动同步更新，无需手动修改公式

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