ArrayFormula +多个IMPORTRANGE + QUERY [英] ArrayFormula + multiple IMPORTRANGE + QUERY
问题描述
我想根据一些条件将一些电子表格导入到一个电子表格中,不幸的是,如果其中一个电子表格未返回任何数据,则 ARRAYFORMULA
会要求输入 ARRAY_LITERAL
空结果.
I want to import a few spreadsheets into one with some criteria, unfortunately if one of the spreadsheets doesn't return any data, the ARRAYFORMULA
is asking for ARRAY_LITERAL
instead of empty result.
我需要在 IFERROR
函数中使用什么,以便 ARRAYFORMULA
不添加任何内容并乐于继续?
What do I need to use in the IFERROR
function so that ARRAYFORMULA
appends nothing and is happy to continue?
这就是我现在拥有的:
={
QUERY(IMPORTRANGE("<spreadsheet>", "Range!B2:J"), "SELECT Col1, Col2, Col3, Col5, Col9 WHERE Col1 <> '' AND Col5 >= 18 ");
IFERROR(QUERY(IMPORTRANGE("<spreadsheet2>", "Range!B2:K"), "SELECT Col1, Col3, Col2, Col4, Col6, Col10 WHERE Col10 <> '' AND Col6 >= 18"), "")
}
我将第二个包裹在 IFERROR
中,但是空字符串不是有效的 ARRAY_LITERAL
,那么我该怎么办呢?
I wrapped the second one in IFERROR
but the empty string is not a valid ARRAY_LITERAL
, so what can I do instead of it?
完整错误:
在ARRAY_LITERAL中,数组文字缺少一个或多个行的值.
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
我意识到我可以给它一个空行,其列数与 I1:N2
相同,但您永远不知道是否需要使用这些列,然后查询就需要更改.
I realised I could give it an empty row with the same number of columns like I1:N2
but you never know if you might need to use these columns and then the query would need to change.
推荐答案
我建议使用空的 = iferror(query(...,...),{",",",...,"})
注意:
- 您选择6列,因此重复"六次.
修改
@Moseleyi.
by @Moseleyi.
有道理,但当然,它仍然会产生空行,如果我将我的ARRAYFORMULA包裹在SORT中,将首先显示空行,但是当我使用空单元格时,它们不会使用,可能是因为它们实际上是空值.但是我用
IFERROR(1/0)
替换了空字符串,并且可以正常工作.
Makes sense, but of course, it still produces empty rows, and if I wrap my ARRAYFORMULA in SORT, the empty rows will be shown first, but when I use empty cells they won't, probably because they're actually NULL. But I replaced empty strings with
IFERROR(1/0)
and it worked.
这篇关于ArrayFormula +多个IMPORTRANGE + QUERY的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!