Google Sheets如何无需逐个命名查询所有持续新增的工作表

问题场景 #

• 大型Google Sheets文档内置Lookup查询工作表，用于跨所有工作表检索特定人员对应的行信息
• 原有实现通过QUERY公式合并多工作表数据，配合自定义sheetnumber()函数获取工作表名称，动态拉取对应工作表行数据，同时新增工作表名称作为标识列
• 现存问题：工作表数量超过30个后，每新增一个工作表都需要手动在QUERY公式中追加对应条目，操作繁琐易出错
• 原有公式如下：

=QUERY(
{{indirect(sheetnumber(3)&"!$A$3:$e$"&counta(indirect(sheetnumber(3)&"!A3:A"))),  TRANSPOSE(SPLIT(REPT(sheetnumber(3)&"♦",
ROWS(indirect(sheetnumber(3)&"!$A$3:$e$"&counta(indirect(sheetnumber(3)&"!A3:A"))))),  "♦"))};
{indirect(sheetnumber(4)&"!$A$3:$e$"&counta(indirect(sheetnumber(4)&"!A3:A"))),  TRANSPOSE(SPLIT(REPT(sheetnumber(4)&"♦",  ROWS(indirect(sheetnumber(4)&"!$A$3:$e$"&counta(indirect(sheetnumber(4)&"!A3:A"))))),  "♦"))};
{indirect(sheetnumber(5)&"!$A$3:$e$"&counta(indirect(sheetnumber(5)&"!A3:A"))),  TRANSPOSE(SPLIT(REPT(sheetnumber(5)&"♦",  ROWS(indirect(sheetnumber(5)&"!$A$3:$e$"&counta(indirect(sheetnumber(5)&"!A3:A"))))),  "♦"))}
},
"select Col6,Col2,Col3,Col4,Col5
where lower(Col1) contains '"& lower($A$2) &"'
",0)

优化方案 #

核心逻辑是用REDUCE()+LAMBDA()函数自动遍历所有需要纳入查询的工作表，替代手动逐表拼接公式块的操作，新增工作表无需修改公式即可自动纳入查询范围。

优化后公式 #

=LET(
  // 配置项：需要纳入查询的起始工作表序号，与原逻辑保持一致从3开始
  start_sheet, 3,
  // 自动获取当前文档总工作表数量，识别新增工作表
  total_sheets, SHEETS(),
  // 遍历所有目标工作表，自动拼接拉取数据
  all_data, REDUCE("", SEQUENCE(total_sheets - start_sheet + 1, 1, start_sheet),
    LAMBDA(acc, sh_num,
      LET(
        sh_name, sheetnumber(sh_num),
        last_row, COUNTA(INDIRECT(sh_name & "!A3:A")),
        // 自动跳过无数据的空白工作表，避免报错
        IF(last_row = 0, acc,
          VSTACK(acc,
            HSTACK(
              INDIRECT(sh_name & "!$A$3:$E$" & last_row),
              TRANSPOSE(SPLIT(REPT(sh_name & "♦", ROWS(INDIRECT(sh_name & "!$A$3:$E$" & last_row))), "♦"))
            )
          )
        )
      )
    )
  ),
  // 沿用原有QUERY查询逻辑，返回结果与原公式完全一致
  QUERY(all_data,
    "select Col6,Col2,Col3,Col4,Col5
     where lower(Col1) contains '" & LOWER($A$2) & "'",
  0)
)

方案说明 #

• 自动适配新增工作表：只要新增工作表的序号在起始序号到总表数的范围内，公式会自动拉取其数据，无需手动修改公式
• 兼容原有逻辑：数据拉取、查询规则和原公式完全一致，返回结果无差异
• 自动容错：自动跳过无数据的空白工作表，不会因为空表导致公式报错

注意事项 #

• 若存在固定不需要纳入查询的工作表，可将公式中的SEQUENCE(total_sheets - start_sheet + 1, 1, start_sheet)替换为手动维护的序号数组，例如{3,4,5,7,9}，维护成本远低于原逐块拼接的公式
• 该公式依赖Google Sheets原生的现代函数（LET/REDUCE/LAMBDA/VSTACK/HSTACK），2022年后更新的Google Sheets版本默认支持，无需额外配置

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