如何在Google Sheets中提取非空参与者数据并垂直排列

我用Google Form做了图书馆阅读项目的报名表单，生成的Google Sheet里，每个报名最多支持6个孩子，对应“Participant 1 Name”“Participant 1 Age”这类成对的字段。大部分用户只报1-2个孩子，剩下的列都是空的。现在要在同表格里新建工作表，生成只有“Participant Name”和“Age”两列的垂直列表，自动忽略空白项。

之前试过用QUERY函数：

=QUERY(FormResponses!F:Q, "select F,H,J,L,N,P where F<>'' or H<>'' or J<>'' or L<>'' or N<>'' or P<>''", 1)

这个函数能提取姓名数据，但还是保留原行结构，还意外带了原表头，想结合TRANSPOSE调整排列但不知道怎么操作。

解决方案1：用ARRAYFORMULA+FLATTEN+FILTER #

直接在新工作表的A1单元格粘贴以下公式：

=ARRAYFORMULA({
  "Participant Name", "Age";
  FILTER(
    FLATTEN(FormResponses!F2:F, FormResponses!H2:H, FormResponses!J2:J, FormResponses!L2:L, FormResponses!N2:N, FormResponses!P2:P),
    FLATTEN(FormResponses!F2:F, FormResponses!H2:H, FormResponses!J2:J, FormResponses!L2:L, FormResponses!N2:N, FormResponses!P2:P)<>""
  ),
  FILTER(
    FLATTEN(FormResponses!G2:G, FormResponses!I2:I, FormResponses!K2:K, FormResponses!M2:M, FormResponses!O2:O, FormResponses!Q2:Q),
    FLATTEN(FormResponses!F2:F, FormResponses!H2:H, FormResponses!J2:J, FormResponses!L2:L, FormResponses!N2:N, FormResponses!P2:P)<>""
  )
})

• 第一行手动定义了目标表头"Participant Name", "Age"
• FLATTEN把所有姓名列（F、H、J、L、N、P）转成垂直列表，从第二行开始跳过原表单的表头
• FILTER自动过滤掉姓名为空的项，保证只保留有报名信息的参与者
• 年龄列和姓名列一一对应，确保每个姓名匹配正确的年龄

解决方案2：用QUERY+FLATTEN+SPLIT #

如果偏好QUERY语法，也可以用这个公式：

=QUERY(
  FLATTEN(
    ARRAYFORMULA(
      FormResponses!F2:F&"|"&FormResponses!G2:G&";"&
      FormResponses!H2:H&"|"&FormResponses!I2:I&";"&
      FormResponses!J2:J&"|"&FormResponses!K2:K&";"&
      FormResponses!L2:L&"|"&FormResponses!M2:M&";"&
      FormResponses!N2:N&"|"&FormResponses!O2:O&";"&
      FormResponses!P2:P&"|"&FormResponses!Q2:Q
    )
  ),
  "select split(Col1, '|') where Col1 <> '|' and Col1 <> ''",
  0
)

• 先把每行的姓名和年龄用|拼接，每个参与者对用;分隔，再用FLATTEN转成垂直列表
• QUERY筛选掉空的拼接项，最后用split把拼接的内容拆成姓名和年龄两列
• 参数0表示不使用原数据的表头，后续可以手动添加表头或者在公式里补充

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