Google Sheets如何用ARRAYFORMULA+INDIRECT自动处理A列非空行对应工作表数据

问题原因 #

你之前编写的ARRAYFORMULA无法返回完整数组，是因为INDIRECT属于标量函数，无法在数组公式中自动迭代处理A列的每一个单元格值，需要借助LAMBDA类函数实现数组遍历逻辑。

优化后公式 #

=QUERY(
  UNIQUE(
    REDUCE(
      {};
      FILTER(A:A; LEN(A:A) > 0);
      LAMBDA(acc; sheet_name;
        LET(
          current_data; INDIRECT(sheet_name & "!A7:N");
          backup_data; INDIRECT(A1 & "!A7:N");
          VSTACK(acc; IF(COUNTA(INDIRECT(sheet_name & "!A7:A")) > 5; current_data; backup_data))
        )
      )
    )
  );
  "Select Col1,Col3,Col5,Col4,Col6,Col7,Col13,Col14 Where Col1 is not null and not Col1 like 'Методист'";
  0
)

逻辑说明 #

• 先用FILTER函数自动筛选出A列所有非空的工作表名称，无需手动指定行范围，后续A列新增工作表名会自动被识别
• 用REDUCE函数遍历所有筛选出的工作表名，逐一把对应工作表的A7:N区域数据堆叠到同一个数组中，完全替代你之前手动逐行添加引用的操作
• 完整保留了你原有的COUNTA判断规则、UNIQUE去重逻辑和QUERY数据筛选条件，功能和你原有公式完全一致

注意事项 #

如果你的工作表名称包含空格、特殊字符或者非英文内容，建议把INDIRECT部分修改为INDIRECT("'" & sheet_name & "'!A7:N")，用单引号包裹工作表名避免识别报错。

内容的提问来源于stack exchange，提问作者Костя Коростелев