如何用ARRAYFORMULA生成的范围列自动构建Google Sheets QUERY数据源

解决方法 #

INDIRECT不支持直接批量解析数组内的多个范围字符串做垂直合并，你可以用REDUCE函数迭代拼接所有有效周表的数据源：

步骤1：调用AA列生成合并数据源 #

如果你已经在AA列存储了筛选后的非空有效范围（AA1为表头，有效范围从AA2开始向下排列），用下面的公式即可生成所有周表合并后的完整数据源：

=REDUCE(
  TOROW(,1), // 初始值设为空数组
  FILTER(AA:AA, AA:AA<>""), // 筛选AA列所有非空的有效范围
  LAMBDA(acc, cur, VSTACK(acc, INDIRECT(cur))) // 迭代把每个范围的内容垂直拼到累积数组里
)

步骤2：替换原有QUERY的硬编码范围 #

把你原有公式里所有出现硬编码周表数组的位置{'Week 1'!A2:Z133;'Week 2'!A2:Z133;...}全部替换为上述合并数据源即可，优化后的完整公式如下：

=LET(
  // 预生成所有周表的合并数据源，仅计算1次，避免重复运算
  all_weeks_data, REDUCE(TOROW(,1), FILTER(AA:AA, AA:AA<>""), LAMBDA(acc,cur,VSTACK(acc,INDIRECT(cur)))),
  // 原有业务逻辑，硬编码范围替换为预生成的合并数据源
  combined, {
    IFNA(QUERY(all_weeks_data,"select Col26, Col1, Col2,  Col4,  Col5  where Col1 IS NOT NULL and Col4  IS NOT NULL", 0), { "","","","","" });
    IFNA(QUERY(all_weeks_data,"select Col26, Col1, Col6,  Col8,  Col9  where Col1 IS NOT NULL and Col8  IS NOT NULL", 0), { "","","","","" });
    IFNA(QUERY(all_weeks_data,"select Col26, Col1, Col10, Col12, Col13 where Col1 IS NOT NULL and Col12 IS NOT NULL", 0), { "","","","","" });
    IFNA(QUERY(all_weeks_data,"select Col26, Col1, Col14, Col16, Col17 where Col1 IS NOT NULL and Col16 IS NOT NULL", 0), { "","","","","" });
    IFNA(QUERY(all_weeks_data,"select Col26, Col1, Col18, Col20, Col21 where Col1 IS NOT NULL and Col20 IS NOT NULL", 0), { "","","","","" })
  },
  // 最终去空排序
  QUERY(combined, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1")
)

逻辑说明 #

• REDUCE会遍历所有有效范围字符串，每一轮用INDIRECT解析当前范围的内容，再用VSTACK垂直拼接到之前累积的数组中，最终得到所有周表合并后的完整数据源
• 加入LET函数将合并数据源预存为变量，避免每个子QUERY都重复执行一次合并逻辑，大幅提升公式运行效率
• 后续新增周表只要AA列识别到有效范围，就会自动加入合并数据源，不需要手动修改公式

内容的提问来源于stack exchange，提问作者Tom J Nowell