使用ARRAY_LITERAL组合QUERY公式出现#VALUE!错误，求排查解决

可能的错误原因及排查步骤 #

• 数据类型/列数不匹配：检查Facility Use Requests表中各目标列（G/R/U/X/AA等日期列，以及其他选中列）的数据类型，确保日期列未混入文本值，且每个QUERY返回的列数完全一致（当前公式中每个QUERY应返回13列）。若某列被修改（如合并单元格、数据格式变更），会导致数组组合失败。
• 空QUERY结果触发数组异常：当某个QUERY无符合条件的行时，会返回空数组，与非空数组组合时可能触发#VALUE!。给每个QUERY套上IFERROR，空结果时返回对应列数的空值行，避免数组结构断裂。
• 日期解析失败：检查工作表区域设置是否变更，若区域日期格式与公式中yyyy-mm-dd不兼容，QUERY无法正确识别日期条件，导致返回错误结果。可尝试将TEXT(TODAY(),"yyyy-mm-dd")替换为TEXT(TODAY(),"yyyy-MM-dd")，或直接用日期值：WHERE G >= "&TODAY()（需确保目标列为日期格式）。
• 数据源结构变更：确认Facility Use Requests表未被删除、重命名，目标列未被移动或删除，公式引用的A2:AC范围仍包含所有需要的字段。

修复后的公式 #

给每个QUERY添加IFERROR处理空结果，确保数组结构始终一致：

=SORT(UNIQUE({
  IFERROR(QUERY('Facility Use Requests'!A2:AC, "SELECT G, H, I, J, C, F, K, L, D, E, P, N, O WHERE G >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",0),{"","","","","","","","","","","","",""});
  IFERROR(QUERY('Facility Use Requests'!A2:AC, "SELECT R, S, T, J, C, F, K, L, D, E, P, N, O WHERE R >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",0),{"","","","","","","","","","","","",""});
  IFERROR(QUERY('Facility Use Requests'!A2:AC, "SELECT U, V, W, J, C, F, K, L, D, E, P, N, O WHERE U >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",0),{"","","","","","","","","","","","",""});
  IFERROR(QUERY('Facility Use Requests'!A2:AC, "SELECT X, Y, Z, J, C, F, K, L, D, E, P, N, O WHERE X >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",0),{"","","","","","","","","","","","",""});
  IFERROR(QUERY('Facility Use Requests'!A2:AC, "SELECT AA, AB, AC, J, C, F, K, L, D, E, P, N, O WHERE AA >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",0),{"","","","","","","","","","","","",""}
}),1,TRUE,2,TRUE)

额外优化建议 #

用LET和BYROW简化重复逻辑，降低维护成本：

=LET(
  src, 'Facility Use Requests'!A2:AC,
  dateCols, {"G","R","U","X","AA"},
  selectSuffix, ", J, C, F, K, L, D, E, P, N, O",
  buildQuery, LAMBDA(col, "SELECT "&col&", "&SUBSTITUTE(col,"G","H")&", "&SUBSTITUTE(col,"G","I")&selectSuffix&" WHERE "&col&" >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'"),
  results, BYROW(dateCols, LAMBDA(c, IFERROR(QUERY(src, buildQuery(c),0),{"","","","","","","","","","","","",""}))),
  SORT(UNIQUE(VSTACK(results)),1,TRUE,2,TRUE)
)

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