Google表阵列不匹配不同的查询大小
我们希望通过单个Google Sheet查询或在每个列大小不同的单个数组中创建动态表。
当我们单独使用单个查询时，它可以工作，但是如果我们汇总所有查询
对于单个数组中的单个列，然后显示数组不匹配错误... 因为每个列的单个查询返回大小是不同的，这意味着单个数组中大小不同的数组，因此会产生错误。.
有任何方法可以绕过此错误...？
还是我们需要在每个单独的列中使用单独的查询...？
我的样本公式：具有不同数组大小的数组在下面给出。 请让我知道，有什么方法可以绕过数组尺寸不匹配问题。
={ QUERY(Leads!A2:P," SELECT P, COUNT(N) WHERE N IS NOT NULL AND N='Contact in Progress' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Contact in Progress' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Rejected / DND' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Rejected / DND' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Made Offer' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Made Offer' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Under Contract' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Under Contract' ")}
We want to create dynamic table by single google sheet query or in single array where each column size is different .
It work when we use individual query for each column separately, but if we aggregate all query
for individual column in a single array then it show array mismatch error ...
Because each column individual query return size is different ,that mean arrays with different size in a single array , so it create error..
There are any way to bypass this error...?
Or we need to use individual query in each individual column...?
My sample formula : array with different array size is given below.
please let me know , is there any way to bypass array size mismatch problem..
={ QUERY(Leads!A2:P," SELECT P, COUNT(N) WHERE N IS NOT NULL AND N='Contact in Progress' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Contact in Progress' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Rejected / DND' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Rejected / DND' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Made Offer' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Made Offer' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Under Contract' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Under Contract' ")}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试（在您的添加条件下添加其他条件）
，尝试
Try (add additional criteria)
with your additionnal conditions, try