我有以下查询,我正在为我的工作/建设(位于我的示例链接的'查询结果'表)。
=QUERY({'1st'!A7:D;'2nd'!A7:D}, "SELECT Col1, SUM(Col2), SUM(Col3), SUM(Col4) GROUP BY Col1")
它正在按照我的意图进行计算,但是我需要按照"主图表"中显示的排序顺序进行计算。床单我已经尝试使用ORDER BY,但由于我使用GROUP BY与聚合函数,它没有按我期望的方式工作。
有人能告诉我最好的方法来获得相同的结果,查询是给出,但在我想要的顺序?
我的工作表的简化副本在这里。
答案
下面的公式应该会产生你想要的结果:
=ARRAY_CONSTRAIN(SORT({QUERY({'1st'!A7:D;'2nd'!A7:D}, "SELECT Col1, SUM(Col2), SUM(Col3), SUM(Col4) WHERE Col1 IS NOT NULL GROUP BY Col1 LABEL SUM(Col2) '', SUM(Col3) '', SUM(Col4) ''"),SORT(FILTER(VLOOKUP(UNIQUE({'1st'!A7:A;'2nd'!A7:A}), {'Master Chart'!A2:A,'Master Chart'!E2:E},{1,2}, FALSE), UNIQUE({'1st'!A7:A;'2nd'!A7:A})<>""),1,TRUE)},6,TRUE),COUNTA(UNIQUE({'1st'!A7:A;'2nd'!A7:A})),4)
该查询由两个主要部分组成,封装在=ARRAY_CONSTRAIN(SORT(...))
中。
A部分是QUERY(...)
,它与您已经拥有的事情大致相同,尽管我冒昧地不包括Col1(轮胎尺寸)为null的结果,并且我还删除了自动生成的"one_answers";=QUERY
创建的标签
B部分是SORT(FILTER(VLOOKUP(...)))
,它按QUERY
的顺序对Master Chart
中的每一行进行排序。然后为QUERY
返回的每一行提供一个Sort Order
值(来自Master Sheet
)。
最外层的SORT
从A部分获取结果,并使用B部分的Sort Order
对它们进行排序。最后,=ARRAYCONSTRAIN
去掉多余的部分——B部分仅用于排序,不应显示在最终结果中。
如果您希望使用"sum"标签,您可以通过将整个公式包含在下面来手动添加它们:
={{"","sum","sum","sum"}; FORMULA HERE }
如果你想从Col1中计算null结果,这个方法将无法工作,因为你没有为null结果提供一个不同的Sort Order
。
功能使用:
- = ARRAY_CONSTRAIN
- =查询
- = <
- =过滤器/gh>
- = VLOOKUP 独特
- =
- = COUNTA
使用vlookup()
仅显示query()
结果中大小与Master Chart
列表匹配的行,以相同的顺序,如下所示:
=arrayformula(
iferror(
vlookup(
'Master Chart'!A2:A,
query(
{ '1st'!A7:D;'2nd'!A7:D },
"select Col1, sum(Col2), sum(Col3), sum(Col4)
group by Col1",
0
),
{ 1, 2, 3, 4 },
false
)
)
)