Google Sheets Query语法：筛选非空列生成指定格式报表

核心思路 #

不同年级的信息分散在三组列中（EFG=8年级、HIJ=7年级、KLM=6年级），先通过ARRAYFORMULA将每行的年级、姓名、班级、去向数据统一映射到新列，再用QUERY筛选出Status为Out的有效记录。

完整公式 #

=QUERY(
  ARRAYFORMULA({
    A:C,
    IF(E:E<>"", "8年级", IF(H:H<>"", "7年级", IF(K:K<>"", "6年级", ""))),
    IF(E:E<>"", E:E, IF(H:H<>"", H:H, K:K)),
    IF(E:E<>"", F:F, IF(H:H<>"", I:I, L:L)),
    IF(E:E<>"", G:G, IF(H:H<>"", J:J, M:M))
  }),
  "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7 WHERE Col3='Out' AND Col4<>''",
  1
)

公式说明 #

1. 数据映射部分：

   • A:C：直接提取原表的Timestamp（A列）、Email（B列）、Status（C列）
   • 第四列：通过嵌套IF判断哪组年级列有数据，自动标记对应年级（8/7/6年级）
   • 第五列：提取对应年级的姓名（E/H/K列中不为空的单元格）
   • 第六列：提取对应年级的原班级（F/I/L列中不为空的单元格）
   • 第七列：提取对应年级的去向（G/J/M列中不为空的单元格）

2. Query筛选部分：

   • SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7：选择映射后的所有目标列
   • WHERE Col3='Out' AND Col4<>''：只保留Status为Out且年级信息非空的有效记录
   • 最后的1：表示原数据包含表头行，Query会保留表头

注意事项 #

• 确认原表列对应关系：如果Timestamp、Email、Status不在A/B/C列，需修改公式中A:C和Col3的对应位置
• 将公式粘贴到空白单元格（比如N1），避免覆盖原表单数据
• 若存在多列空值的异常行，Col4<>''会自动过滤掉这些无效记录

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