如何简化“不是无效”对于在Google表中使用查询功能时的多个列？

Question

我有这个公式：

=QUERY(
    {AG4:AW101},
    "Select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17 
    where Col1 is not null
    and Col1 is not null
    and Col2 is not null
    and Col3 is not null
    and Col4 is not null
    and Col5 is not null
    and Col6 is not null
    and Col7 is not null
    and Col8 is not null
    and Col9 is not null
    and Col10 is not null
    and Col11 is not null
    and Col12 is not null
    and Col13 is not null
    and Col14 is not null
    and Col15 is not null
    and Col16 is not null
    and Col17 is not null
    order by Col"&$N$1&" "&$N$2&"")

无论如何是否可以使它短？我真的需要重复，而Colx不是每个列的null？

Answer 1

加入他们。假设您的范围是：f，您想检查b：f for 不是null，然后尝试：

=ARRAYFORMULA(QUERY({A:F, TRIM(FLATTEN(QUERY(TRANSPOSE(B:F),,9^9)))}, 
 "select Col1,Col2,Col3,Col4,Col5,Col6
  where Col7 is not null", ))