在Google表格中使用QUERY功能时，如何简化多列的"IS NOT NULL"？

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&"")

有没有办法让它更短?我真的需要对每一列都重复and ColX is not null吗?

Answer 1

加入他们。假设你的范围是A:F，你想检查B:F是否有is not 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", ))