如何在Google Sheets中使用QUERY()函数消除空白行？

问题原因 #

原公式里，当QUERY无匹配结果时，IFERROR返回了{"","","",""}这个含空值的数组，会直接生成一行空白行，导致首行出现不必要的空白。

修正后的公式 #

=IF(I4<>"", 
  QUERY(
    {IFERROR(QUERY(Datasets!X3:AU,"select AS, AT, AJ, AR where AS matches date '"&TEXT(DATEVALUE(I4),"yyyy-mm-dd")&"'",0),{});
     IFERROR(QUERY(Datasets!BC3:BT,"select BC, BD, BS, BQ where BC matches date '"&TEXT(DATEVALUE(I4),"yyyy-mm-dd")&"'",0),{})},
    "where Col1 is not null", 0
  ),
  UNIQUE(
    QUERY(
      {QUERY(Datasets!X3:AU,"select AS, AT, ' ', SUM(AR) where AS is not null group by AS, AT, ' ' label ' '' ', SUM(AR) '  '",0);
       QUERY(Datasets!BC3:BT,"select BC, BD, ' ', SUM(BQ) where BC is not null group by BC, BD, ' ' label ' '' ', SUM(BQ) '  '",0)},
      "where Col1 is not null", 0
    )
  )
)

修改说明 #

1. 把IFERROR中返回的空值数组{"","","",""}替换为{}，无匹配结果时不再生成空白行
2. 给两组QUERY结果套外层QUERY，通过where Col1 is not null过滤所有首列为空的行
3. 对UNIQUE分支做相同处理，确保分组统计结果也不会出现空白行

问题截图 #

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