谷歌表格多Sheet数据提取：按指定条件筛选指定列求助

方法一：分Sheet筛选后合并 #

直接对每个Sheet单独执行筛选并提取对应列，再将结果合并，确保表头只保留一份：

={
  QUERY(Sheet1!A:J, "select A,B,C,D where H='ok'", 0);
  QUERY(Sheet2!A:J, "select A,B,C,D where H='ok'", 1);
  QUERY(Sheet3!A:J, "select A,B,C,D where H='ok'", 1);
  QUERY(Sheet4!A:J, "select A,B,C,E where I='ok'", 1);
  QUERY(Sheet5!A:J, "select A,B,C,D where J='ok'", 1)
}

核心逻辑： #

• 每个QUERY精准匹配对应Sheet的规则：
  • Sheet1/2/3：筛选H列为ok，提取A-D列
  • Sheet4：筛选I列为ok，提取A-C+E列
  • Sheet5：筛选J列为ok，提取A-D列
• 第一个QUERY用参数0保留表头，后续QUERY用1跳过表头，避免结果出现重复表头

方法二：FILTER+QUERY组合（适配无符合条件的场景） #

如果存在Sheet没有符合条件的数据，用FILTER配合IFERROR避免公式报错，最后清理空行：

=QUERY({
  IFERROR(FILTER(Sheet1!A:D, Sheet1!H:H="ok"), {"","","",""});
  IFERROR(FILTER(Sheet2!A:D, Sheet2!H:H="ok"), {"","","",""});
  IFERROR(FILTER(Sheet3!A:D, Sheet3!H:H="ok"), {"","","",""});
  IFERROR({FILTER(Sheet4!A:C, Sheet4!I:I="ok"), FILTER(Sheet4!E:E, Sheet4!I:I="ok")}, {"","","",""});
  IFERROR(FILTER(Sheet5!A:D, Sheet5!J:J="ok"), {"","","",""})
}, "where Col1 is not null", 1)

核心逻辑： #

• IFERROR在无符合条件数据时返回空4列数组，避免公式报错
• 外层QUERY通过where Col1 is not null自动清理空行，保证结果整洁
• Sheet4的A-C列和E列通过数组{FILTER(A:C), FILTER(E:E)}组合，与其他Sheet结果列数统一

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