如何修复 ARRAY_LITERAL 中的错误，数组文字缺少一行或多行的值 [英] How to fix error IN ARRAY_LITERAL, An Array Literal was missing values for one or more rows

问题描述

我需要将多张纸合并到一个文件中，不能有空格或空格.但是总是出现这个错误:

<块引用>

在 ARRAY_LITERAL 中，数组文字缺少一行或多行的值.

=QUERY({导入范围(列表！B2"，文件名！A3:T")；导入范围(列表！B3"，文件名！A3:T")；导入范围(列表！B4"，文件名！A3:T")；导入范围(列表！B5"，文件名！A3:T")；导入范围(列表！B6"，文件名！A3:T")；导入范围(列表！B7"，文件名！A3:T")；导入范围(列表！B8"，文件名！A3:T")；导入范围(列表！B9"，文件名！A3:T")；导入范围(列表！B10"，文件名！A3:T")}, "SELECT * WHERE Col1<>;''")

我应该从我提到的工作表中获取所有信息，没有空格，但错误持续存在

<块引用>

在 ARRAY_LITERAL 中，数组文字缺少一行或多行的值.

我不知道缺少什么，因为根据检查列出的所有工作表都很好.我应该检查什么才能显示所有值和行?

解决方案

使用这个逻辑来解决 Array_Literal 错误:

=ARRAYFORMULA(IFERROR(QUERY({IFERROR(IMPORTRANGE("ID 1", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));IFERROR(IMPORTRANGE("ID 2", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));IFERROR(IMPORTRANGE("ID 3", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));IFERROR(IMPORTRANGE("ID 4", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));IFERROR(IMPORTRANGE("ID 5", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));IFERROR(IMPORTRANGE("ID 6", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));IFERROR(IMPORTRANGE("ID 7", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));IFERROR(IMPORTRANGE("ID 8", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));IFERROR(IMPORTRANGE("ID 9", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ))},其中 Col1 不为空")))

I need to consolidate multiple sheets in one file, no blanks or space. But always got this error:

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

=QUERY({
IMPORTRANGE("LISTS!B2","Filename!A3:T");
IMPORTRANGE("LISTS!B3","Filename!A3:T");
IMPORTRANGE("LISTS!B4","Filename!A3:T");
IMPORTRANGE("LISTS!B5","Filename!A3:T");
IMPORTRANGE("LISTS!B6","Filename!A3:T");
IMPORTRANGE("LISTS!B7","Filename!A3:T");
IMPORTRANGE("LISTS!B8","Filename!A3:T");
IMPORTRANGE("LISTS!B9","Filename!A3:T");
IMPORTRANGE("LISTS!B10","Filename!A3:T")
}, "SELECT * WHERE Col1<>;''")

I should get all the information from the sheets I have mentioned with no blanks but the error is persistent that

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

I don't know what's missing since all the sheets listed are good as per checking. What should I check in order to display all the values, rows?

解决方案

use this logic which counters Array_Literal error:

=ARRAYFORMULA(IFERROR(QUERY({
 IFERROR(IMPORTRANGE("ID 1", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 2", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 3", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 4", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 5", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 6", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 7", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 8", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ));
 IFERROR(IMPORTRANGE("ID 9", "sheetname!A3:T"), SUBSTITUTE(COLUMN(A1:T1)^0, 1, ))}, 
 "where Col1 is not null")))

ARRAY_LITERAL error explained

---

UPDATE:

if you want to reference ID from another cell, those cell references cannot be quoted. eg it should look like this:

这篇关于如何修复 ARRAY_LITERAL 中的错误，数组文字缺少一行或多行的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！