使用返回 ARRAYLITERAL ERROR 的 Query/ImportRange 公式从多个工作表导入数据 [英] Importing data from multiple sheets with Query/ImportRange formula returning ARRAYLITERAL ERROR
问题描述
我有 2 个 Google 表格,用于跟踪我们两个主要网站的课堂出勤情况,表格上的每个标签都是不同的课程/课程.每个工作表都有一个数据"选项卡,可以提取所有课程/课程数据.我试图从这两张表中提取数据表并将它们放在第三张表上,并通过程序(GED、CDP、ESL)组织班级数据.我从一张纸中提取数据没有问题,但是当我尝试堆叠 Query
和 Importrange
公式时,我不断收到 ARRAY LITERAL 错误 缺少一行.
I have 2 Google Sheets which track class attendance for our two main sites and each tab on the sheet is a different class/course. Each sheet has a "data" tab which pulls all the class/course data. I am trying to pull the data sheets from these two sheets and put them on a 3rd sheet and organize the class data by a program (GED, CDP, ESL). I have had no issues pulling the data from one sheet, but when I tried stacking the Query
and Importrange
formulas, I keep receiving an ARRAY LITERAL error that there is a missing row.
我原来的公式是
={QUERY(IMPORTRANGE("1avE5TJIDVNL7_wqjDLPgocZuecr5Aoz7aI3cI5yIe34", "Data!A3:Ak1000"), "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col26, Col27, Col28, Col29 WHERE Col3 CONTAINS 'ESL' AND Col7>0");QUERY(IMPORTRANGE("10q7kBUJVTw62p1cCZjUIR1CsFSev9Ik6q4K-X-RK40Y", "Data!A3:Ak1000"), "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col26, Col27, Col28, Col29 WHERE Col3 CONTAINS 'ESL' AND Col7>0")}
它给出了一个 VALUE
错误消息,指出 ARRAY LITERAL
缺少行.
and it gave a VALUE
error message that the ARRAY LITERAL
was missing rows.
根据其他网站的其他人的建议,我尝试了公式(如下),但收到公式解析错误消息并尝试将大括号移动到导入范围公式周围,但继续收到公式解析错误消息>
Based on other's recommendations from other sites, I tried the formula (below), but received a formula parse ERROR message and tried moving the curly brackets to just around the ImportRange formula, but continued to receive a formula parse ERROR MESSAGE
=QUERY{(IMPORTRANGE("1avE5TJIDVNL7_wqjDLPgocZuecr5Aoz7aI3cI5yIe34", "Data!A3:Ak1000");IMPORTRANGE("10q7kBUJVTw62p1cCZjUIR1CsFSev9Ik6q4K-X-RK40Y", "Data!A3:Ak1000")},"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col26, Col27, Col28, Col29 WHERE Col3 CONTAINS 'ESL' AND Col7>0")
我希望输出将列出在 Col3 中包含ESL"的所有类以及两个工作表中的相应列.
I expect that the output will list any classes that contain "ESL" in Col3 and the corresponding columns from both sheets.
推荐答案
理解ARRAY_LITERAL ERROR:
如果两个查询都输出了一些东西,那么一切都很好:
understanding ARRAY_LITERAL ERROR:
if both queries output something then all is good:
但是,如果其中一个查询没有任何输出,它会输出 #N/A
但问题是 #N/A
仅在第一个单元格:
however if one of those queries doesn't have anything to output it outputs #N/A
but the issue is that #N/A
is only in the 1st cell:
但数组希望两边的矩阵相同(来自两个查询的 4 列):
but array expects that matrix on both sides to be same (4 columns from both queries):
因此我们将每个查询都包装到 IFERROR
中,如果出现错误,我们会输出带有 4 个假列的假行 - {","",";",""}
- 这将欺骗数组输出它:
so we wrap each query into IFERROR
and in case of error we output fake row with 4 fake columns - {"","","",""}
- which will trick the array to output it like:
因此尝试这样:
=IFERROR(QUERY({IFERROR(QUERY(IMPORTRANGE(
"1avE5TJIDVNL7_wqjDLPgocZuecr5Aoz7aI3cI5yIe34", "Data!A3:AK1000"),
"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col26,Col27,Col28,Col29
where Col3 contains 'ESL'
and Col7>0", 0), {"","","","","","","","","","",""});
IFERROR(QUERY(IMPORTRANGE(
"10q7kBUJVTw62p1cCZjUIR1CsFSev9Ik6q4K-X-RK40Y", "Data!A3:AK1000"),
"select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col26,Col27,Col28,Col29
where Col3 contains 'ESL'
and Col7>0", 0), {"","","","","","","","","","",""})},
"where Col1 is not null", 0))
这篇关于使用返回 ARRAYLITERAL ERROR 的 Query/ImportRange 公式从多个工作表导入数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!