＆＃x27;数组文字＆＃x27; Google表中的错误 - 无法获得所需的输出

发布于 2025-02-09 21:21:33 字数 1512 浏览 1 评论 0 原文

这是我试验表的链接。随意编辑。我试图使用的公式是在主板单元A10中。

i我正在尝试使用数组和查询功能附加某些数据。但是，该公式会出现一个错误，说“在array_literal中，一个或多个行的文字是缺少的值。”不确定导致错误的原因。我在实际表中使用了以下公式（不同于试验的公式）：

FILTER('CLASS2 - Overall List'!A2:B,'CLASS2 - Overall List'!E2:E>=B2,'CLASS2 - Overall List'!E2:E<=B3,'CLASS2 - Overall List'!C2:C=B16), 
IF(B4="CLASS1", 
FILTER('CLASS1 - Overall List'!A2:B,'CLASS1 - Overall List'!E2:E>=B2,'CLASS1 - Overall List'!E2:E<=B3,'CLASS1 - Overall List'!C2:C=B16), 
IF(B4="CLASS3", 
FILTER('CLASS3 - Overall List'!A2:B,'CLASS3 - Overall List'!E2:E>=B2,'CLASS3 - Overall List'!E2:E<=B3,'CLASS3 - Overall List'!C2:C=B16), 
IF(B4="All", 
QUERY({FILTER('CLASS2 - Overall List'!A2:B,'CLASS2 - Overall List'!E2:E>=B2,'CLASS2 - Overall List'!E2:E<=B3,'CLASS2 - Overall List'!C2:C=B16);FILTER('CLASS1 - Overall List'!A2:B,'CLASS1 - Overall List'!E2:E>=B2,'CLASS1 - Overall List'!E2:E<=B3,'CLASS1 - Overall List'!C2:C=B16);FILTER('CLASS3 - Overall List'!A2:B,'CLASS3 - Overall List'!E2:E>=B2,'CLASS3 - Overall List'!E2:E<=B3,'CLASS3 - Overall List'!C2:C=B16)})))))```

In the final step, I am just trying to append all the results from first 3 conditions - ie, Class1, Class2 and Class3. If I modify the formula to fetch only 1 column (A2:A instead of A2:B), it's working without any issue. Am I missing something here?

原文

Here's the link to my trial sheet. Feel free to edit. The formula that I am trying to work on is in Master sheet cell A10. https://docs.google.com/spreadsheets/d/1skuD82oY9S976uPms1BvcwIrWshV2bKztSDDD76lofI/

I am trying to append certain data using arrays and query function. However, the formula throws an error saying "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." Not really sure of what's causing the error. I have used the following formula in my actual sheet (different from trial one):

FILTER('CLASS2 - Overall List'!A2:B,'CLASS2 - Overall List'!E2:E>=B2,'CLASS2 - Overall List'!E2:E<=B3,'CLASS2 - Overall List'!C2:C=B16), 
IF(B4="CLASS1", 
FILTER('CLASS1 - Overall List'!A2:B,'CLASS1 - Overall List'!E2:E>=B2,'CLASS1 - Overall List'!E2:E<=B3,'CLASS1 - Overall List'!C2:C=B16), 
IF(B4="CLASS3", 
FILTER('CLASS3 - Overall List'!A2:B,'CLASS3 - Overall List'!E2:E>=B2,'CLASS3 - Overall List'!E2:E<=B3,'CLASS3 - Overall List'!C2:C=B16), 
IF(B4="All", 
QUERY({FILTER('CLASS2 - Overall List'!A2:B,'CLASS2 - Overall List'!E2:E>=B2,'CLASS2 - Overall List'!E2:E<=B3,'CLASS2 - Overall List'!C2:C=B16);FILTER('CLASS1 - Overall List'!A2:B,'CLASS1 - Overall List'!E2:E>=B2,'CLASS1 - Overall List'!E2:E<=B3,'CLASS1 - Overall List'!C2:C=B16);FILTER('CLASS3 - Overall List'!A2:B,'CLASS3 - Overall List'!E2:E>=B2,'CLASS3 - Overall List'!E2:E<=B3,'CLASS3 - Overall List'!C2:C=B16)})))))```

In the final step, I am just trying to append all the results from first 3 conditions - ie, Class1, Class2 and Class3. If I modify the formula to fetch only 1 column (A2:A instead of A2:B), it's working without any issue. Am I missing something here?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤单情人 2025-02-16 21:21:33

尝试

=IF(B3="ClassA",FILTER(ClassA!A2:B,ClassA!C2:C=B7,ClassA!E2:E>=B1,ClassA!E2:E<=B2),
IF(B3="ClassB",FILTER(ClassB!A2:B,ClassB!C2:C=B7,ClassB!E2:E>=B1,ClassB!E2:E<=B2),
IF(B3="All",query({
  iferror(FILTER(ClassA!A2:B,ClassA!C2:C=B7,ClassA!E2:E>=B1,ClassA!E2:E<=B2),{"",""});
  iferror(FILTER(ClassB!A2:B,ClassB!C2:C=B7,ClassB!E2:E>=B1,ClassB!E2:E<=B2),{"",""})
}),"where Col1 is not null")))

如果出错，您需要通过空白填充2个单元格或错误消息

Try

=IF(B3="ClassA",FILTER(ClassA!A2:B,ClassA!C2:C=B7,ClassA!E2:E>=B1,ClassA!E2:E<=B2),
IF(B3="ClassB",FILTER(ClassB!A2:B,ClassB!C2:C=B7,ClassB!E2:E>=B1,ClassB!E2:E<=B2),
IF(B3="All",query({
  iferror(FILTER(ClassA!A2:B,ClassA!C2:C=B7,ClassA!E2:E>=B1,ClassA!E2:E<=B2),{"",""});
  iferror(FILTER(ClassB!A2:B,ClassB!C2:C=B7,ClassB!E2:E>=B1,ClassB!E2:E<=B2),{"",""})
}),"where Col1 is not null")))