Google Sheets动态跨表查询遇ARRAY_LITERAL行值缺失错误求助

问题背景 #

需要从12个以月份命名的工作表中动态提取数据：

• 提取目标表的C2:E列
• 添加数据来源的工作表名称（对应A1:A12单元格存储的表名）
• 筛选Col3包含AutoService的行，最终返回Col4、Col1、Col2

原使用公式如下，但因最后3个工作表尚未创建，报错：In ARRAY_LITERAL, an Array Literal was missing values for one or more rows

原公式：

=ARRAYFORMULA(QUERY({
IFERROR({INDIRECT(A1&"!C2:E"), IF(INDIRECT(A1&"!C2:C")<>"", A1, "")}, {"","","",""}); 
IFERROR({INDIRECT(A2&"!C2:E"), IF(INDIRECT(A2&"!C2:C")<>"", A2, "")}, {"","","",""}); 
IFERROR({INDIRECT(A3&"!C2:E"), IF(INDIRECT(A3&"!C2:C")<>"", A3, "")}, {"","","",""}); 
IFERROR({INDIRECT(A4&"!C2:E"), IF(INDIRECT(A4&"!C2:C")<>"", A4, "")}, {"","","",""}); 
IFERROR({INDIRECT(A5&"!C2:E"), IF(INDIRECT(A5&"!C2:C")<>"", A5, "")}, {"","","",""}); 
IFERROR({INDIRECT(A6&"!C2:E"), IF(INDIRECT(A6&"!C2:C")<>"", A6, "")}, {"","","",""}); 
IFERROR({INDIRECT(A7&"!C2:E"), IF(INDIRECT(A7&"!C2:C")<>"", A7, "")}, {"","","",""}); 
IFERROR({INDIRECT(A8&"!C2:E"), IF(INDIRECT(A8&"!C2:C")<>"", A8, "")}, {"","","",""}); 
IFERROR({INDIRECT(A9&"!C2:E"), IF(INDIRECT(A9&"!C2:C")<>"", A9, "")}, {"","","",""}); 
IFERROR({INDIRECT(A10&"!C2:E"), IF(INDIRECT(A10&"!C2:C")<>"", A10, "")}, {"","","",""}); 
IFERROR({INDIRECT(A11&"!C2:E"), IF(INDIRECT(A11&"!C2:C")<>"", A11, "")}, {"","","",""}); 
IFERROR({INDIRECT(A12&"!C2:E"), IF(INDIRECT(A12&"!C2:C")<>"", A12, "")}, {"","","",""})
}, "select Col4, Col1, Col2 where Col3 contains 'AutoService'", 0)) 

报错原因 #

当引用未创建的工作表时，INDIRECT会返回多行错误数组，而原公式中IFERROR的兜底值{"","","",""}是单行数组，导致拼接的数组维度不匹配，触发ARRAY_LITERAL错误。

解决方案 #

使用BYROW遍历A1:A12，统一每个表返回的数组结构：不存在的工作表返回空的4列数组，存在的工作表返回合并后的C2:E列+表名列。优化后的公式：

=QUERY(
  BYROW(A1:A12, LAMBDA(tab,
    IFERROR(
      {INDIRECT(tab&"!C2:E"), IF(INDIRECT(tab&"!C2:C")<>"", tab, "")},
      IF(ISERROR(INDIRECT(tab&"!C1")), SEQUENCE(0,4), {"";"";"";""})
    )
  )),
  "select Col4, Col1, Col2 where Col3 contains 'AutoService'",
  0
)

公式说明 #

1. BYROW(A1:A12, LAMBDA(tab, ...))：遍历A1:A12中的每个表名
2. IFERROR(..., IF(ISERROR(INDIRECT(tab&"!C1")), SEQUENCE(0,4), {"";"";"";""}))：
   • 若表存在，返回C2:E列与表名列的合并数组
   • 若表不存在，返回0行4列的空数组（SEQUENCE(0,4)），避免维度不匹配
3. 外层QUERY统一筛选符合条件的行，返回指定列

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