Google Sheets多QUERY合并时子查询无结果如何返回有效值

纵向拼接多个QUERY结果报In ARRAY_LITERAL, an Array Literal was missing values for one or more rows错误，核心是子查询无匹配结果时，不会返回空行占位，而是直接返回#N/A，导致构造数组时各段列数不匹配。外层套IFERROR、外层QUERY无法生效，是因为错误发生在数组构造阶段，外层逻辑还没开始执行。

给每一个子QUERY外层包裹IFERROR，当子查询无结果时，返回和查询列数一致的空值占位行，保证数组拼接时列数始终统一。
修正后的公式如下：

=QUERY({
IFERROR(QUERY({Sheet1!A:FR},"Select Col142,Col11,Col12,Col74 where Col7 = '"&P4&"' and not Col142 contains '#N/A' and not Col142 matches '-' and Col142 is not null",0),{"","","",""});
IFERROR(QUERY({Sheet1!A:FR},"Select Col150,Col11,Col12,Col78 where Col7 = '"&P4&"' and not Col150 contains '#N/A' and not Col150 matches '-' and Col150 is not null",0),{"","","",""});
IFERROR(QUERY({Sheet1!A:FR},"Select Col152,Col11,Col12,Col82 where Col7 = '"&P4&"' and not Col152 contains '#N/A' and not Col152 matches '-' and Col152 is not null",0),{"","","",""})
},"select * where Col1 <> '#N/A' and Col1 is not ''")

• 每个子查询返回的列数是4列，所以IFERROR的兜底值必须写4个空值，列数和子查询返回列数完全一致，否则还是会触发数组拼接错误。
• 如果后续调整子查询的返回列数，要同步修改兜底空值的数量，和子查询列数保持匹配。

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