如何在查询 where 子句中使用 ARRAYFORMULA? [英] How to use ARRAYFORMULA in query where clause?
问题描述
在表格的单元格 C1:C
中,我有 6 行带有票证 ID.我喜欢搜索不同的电子表格来搜索这些票证 ID 并计算在该票证上花费的总小时数.
In cell C1:C
of my table I got 6 rows with ticket id's. I like to search different spreadsheets to search for those ticket id's and calculate the total hours spent on that ticket.
我使用以下公式进行工作:
I have it working using the following formula:
=QUERY({IMPORTRANGE("SPREADSHEETID";"B3:B")\ARRAYFORMULA(TO_PURE_NUMBER(IMPORTRANGE("SPREADSHEETID";"F3:F")-IMPORTRANGE("SPREADSHEETID";"E3:E")))};"SELECT SUM(Col2) WHERE Col1 = '"&C1&"' GROUP BY Col1 LABEL SUM(Col2) ''")
在此示例中,C1 是可以找到票证 ID 的位置.
现在我想我可以将 QUERY
包装在 ARRAYFORMULA
中并使用 C1:C
而不是 C1
> 但这行不通.现在我可以将上述公式复制并粘贴到每个单元格中,但必须有一种更简洁的方法.
Now I thought I could just wrap QUERY
in a ARRAYFORMULA
and use C1:C
instead of just C1
but that won't work. Now I could just copy and paste the above formula in every cell but there must be a cleaner way.
答案
感谢 Max 在下面的回答,我使用了以下公式使其有效.
=QUERY(
{
IMPORTRANGE("SPREADSHEETID";"B3:B")\
ARRAYFORMULA(
TO_PURE_NUMBER(
IMPORTRANGE("SPREADSHEETID";"F3:F") - IMPORTRANGE("SPREADSHEETID";"E3:E")
)
)
};
"
SELECT Col1, SUM(Col2)
WHERE Col1 = '" & JOIN("' OR Col1 = '";FILTER(C:C; C:C <> "")) & "'
GROUP BY Col1
LABEL SUM(Col2) ''
")
推荐答案
示例公式为:
=QUERY({A:B},"select * where Col1 = '"&JOIN("' or Col1 = '",FILTER(D2:D,D2:D<>""))&"'")
这篇关于如何在查询 where 子句中使用 ARRAYFORMULA?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!