Google工作表查询以不同的标准组合来自3个工作表的数据

Question

我需要使用Query垂直组合3组数据。前2个数据源('SOURCE 1'!A:T和EG:EZ)具有相同的条件，我能够将它们组合在一起而没有问题，但第三个数据源(' source 3'!A:T)具有不同的条件，当我添加此源时，我会导致错误。输出中的列标头将相同。

这是一个只包含前两个数据源的公式:

=QUERY({'SOURCE 1'!A:T;EG:EZ},"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col10 >= date '"&TEXT(B1,"yyyy-mm-dd")&"' and Col10 <= date '"&TEXT(D1,"yyyy-mm-dd")&"' and Col20 = 'YES' and Col1 starts with 'APP-' order by Col2,Col10,Col1,Col9 label Col11 'NAME'",1)

在添加第三个数据源时，以下是我尝试过的3个公式，但都导致错误:在ARRAY_LITERAL中，Array Literal缺少一行或多行值。

尝试1:

={QUERY({'SOURCE 1'!A:T;EG:EZ},"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col10 >= date '"&TEXT(B1,"yyyy-mm-dd")&"' and Col10 <= date '"&TEXT(D1,"yyyy-mm-dd")&"' and Col20 = 'YES' and Col1 starts with 'APP-' order by Col2,Col10,Col1,Col9 label Col11 'NAME'",1);
QUERY('SOURCE 3'!A:T,"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col1 is not null")}

尝试2:

={QUERY('SOURCE 1'!A:T,"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col10 >= date '"&TEXT(B1,"yyyy-mm-dd")&"' and Col10 <= date '"&TEXT(D1,"yyyy-mm-dd")&"' and Col20 = 'YES' and Col1 starts with 'APP-' order by Col2,Col10,Col1,Col9 label Col11 'NAME'",1);
QUERY(EG:EZ,"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col10 >= date '"&TEXT(B1,"yyyy-mm-dd")&"' and Col10 <= date '"&TEXT(D1,"yyyy-mm-dd")&"' and Col20 = 'YES' and Col1 starts with 'APP-' order by Col2,Col10,Col1,Col9 label Col11 'NAME'");
QUERY('SOURCE 3'!A:T,"Select Col1,Col2,Col3,Col4,Col5,Col16,' ',Col8,Col9,Col10,Col11,Col12,Col13 where Col1 is not null")}

尝试3:

={QUERY('SOURCE 1'!A:T,"Select A,B,C,D,E,F,G,H,I,J,K,L,M where J >= date '"&TEXT(B1,"yyyy-mm-dd")&"' and J <= date '"&TEXT(D1,"yyyy-mm-dd")&"' and T = 'YES' and A starts with 'APP-' order by B,J,A,I label K 'NAME'",1);
QUERY(EG:EZ,"Select A,B,C,D,E,F,G,H,I,J,K,L,M where J >= date '"&TEXT(B1,"yyyy-mm-dd")&"' and J <= date '"&TEXT(D1,"yyyy-mm-dd")&"' and T = 'YES' and A starts with 'APP-' order by B,J,A,I");
QUERY('SOURCE 3'!A:T,"Select A,B,C,D,E,P,' ',H,I,J,K,L,M where A is not null")}

如有任何建议将不胜感激。谢谢你！

Answer 1

如果你写QUERY(EG:EZ,"Select A,B,C,D,E,F,G,H,I,J,K,L,M，你不能选择A…在如:EZ !

我建议将'SOURCE 1'!A:T改为{'SOURCE 1'!A:T}，将EG:EZ改为idem，然后您将能够在公式中仅使用Col1, Col2等。

试

={QUERY({'SOURCE 1'!A:T},"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col10 >= date '"&TEXT(B1,"yyyy-mm-dd")&"' and Col10 <= date '"&TEXT(D1,"yyyy-mm-dd")&"' and Col20 = 'YES' and Col1 starts with 'APP-' order by Col2,Col10,Col1,Col9 label Col11 'NAME'",1);
QUERY({EG:EZ},"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13 where Col10 >= date '"&TEXT(B1,"yyyy-mm-dd")&"' and Col10 <= date '"&TEXT(D1,"yyyy-mm-dd")&"' and Col20 = 'YES' and Col1 starts with 'APP-' order by Col2,Col10,Col1,Col9 label Col11 'NAME'");
QUERY({'SOURCE 3'!A:T},"Select Col1,Col2,Col3,Col4,Col5,Col16,' ',Col8,Col9,Col10,Col11,Col12,Col13 where Col1 is not null")}

如果三个查询中有一个缺少数据，您也会得到一个错误，在这种情况下，在每个查询上添加iferror(query______,{"","","",...}