在第二个参数中使用带有text()
或To_text()
函数的Query
函数完全适合我,如下例所示:
=QUERY({June2019!$M$3:$Z;July2019!$M$3:$Z;August2019!$M$3:$Z},"select
SUM(Col5) where Col1 > "&text((Column()-2)*5,"#")&" and Col1 <=
"&text((Column()-1)*5,"#")&" label SUM(Col5) ''")
然而,一旦我以一种更复杂的方式使用这个查询函数,我就会得到以下两个错误之一:
=IF((ISBLANK(B27:27)=False),
(QUERY({June2019!$M$3:$Z;July2019!$M$3:$Z;August2019!$M$3:$Z},"select
SUM(Col5) where (Col1 > "&text((Column()-2)*5,"#")&") and (Col1 <=
"&text((Column()-1)*5,"#")&") label SUM(Col5)
''"))/(QUERY({June2019!$M$3:$Z;July2019!$M$3:$Z;August2019!$M$3:$Z},"select
COUNT(Col5) where (Col1 > "&text((Column()-2)*5,"#")&") and (Col1 <=
"&text((Column()-1)*5,"#")&") label COUNT(Col5) ''")),)
错误:查询已完成,输出为空。
错误:无法分析函数查询参数2的查询字符串: PARSE_ERROR:在第1行、第24列遇到"“Col1”“。应该是以下之一:"(“... "(”...
任何帮助都将不胜感激
发布于 2019-06-24 08:42:05
尝试:
=ARRAYFORMULA(IF(ISBLANK(B27:27)=FALSE,
QUERY({June2019!$M$3:$Z; July2019!$M$3:$Z; August2019!$M$3:$Z},
"select SUM(Col5)
where Col1 > "&(COLUMN()-2)*5&"
and Col1 <= "&(COLUMN()-1)*5&"
label SUM(Col5)''")/
QUERY({June2019!$M$3:$Z; July2019!$M$3:$Z; August2019!$M$3:$Z},
"select COUNT(Col5)
where Col1 > "&(COLUMN()-2)*5&"
and Col1 <= "&(COLUMN()-1)*5&"
label COUNT(Col5)''"), ))
或者:
=ARRAYFORMULA(IF(ISBLANK(B27:27)=FALSE,
QUERY({June2019!$M$3:$Z; July2019!$M$3:$Z; August2019!$M$3:$Z},
"select avg(Col5)
where Col1 > "&(COLUMN()-2)*5&"
and Col1 <= "&(COLUMN()-1)*5&"
label avg(Col5)''"), ))
https://stackoverflow.com/questions/56728575
复制相似问题