Google表格查询:是否可以在没有选择的情况下进行分组/订购? [英] Google Sheets Query: Possible to Group / Order by without Select?
问题描述
在Google表格中,我每天都有多个正负数字数据条目.我想使用 query 函数创建一个表,这样我就有以下几行:日期,负数,正数,总计.
In Google Sheets I have multiple positive and negative numeric data entries per each day. I want to create a table using the query function, such that I have the following rows: Date, Negative, Positive, Total.
Date Value
28/07/2016 10
28/07/2016 -0.9
28/07/2016 -20
29/07/2016 -30
29/07/2016 -12
29/07/2016 18
29/07/2016 -3
30/07/2016 7
30/07/2016 7
30/07/2016 15
30/07/2016 -1
30/07/2016 7
看起来我可以使用查询函数来完成三个公式:
It looks like I could do three formulas with query function:
=query(A2:B13,"select A, sum(B) where B < 0 group by A order by A label A 'Date', sum(B) 'Negative'", -1)
=query(A2:B13,"select A, sum(B) where B > 0 group by A order by A label A 'Date', sum(B) 'Positive'", -1)
=query(A2:B13,"select A, sum(B) group by A order by A label A 'Date', sum(B) 'Total'", -1)
,但是问题是我必须每次都选择Date才能对其进行分组和排序.如果我未选择A,则该函数返回#VALUE!.所以结果看起来像这样:
but the problem is that I have to select Date every time to be able to group and order by it. If I do not select A, the function returns #VALUE!. So the result looks like this:
Date Negative Date Positive Date Total
28/07/2016 -20.9 28/07/2016 10 28/07/2016 -10.9
29/07/2016 -45 29/07/2016 18 29/07/2016 -27
30/07/2016 -1 30/07/2016 36 30/07/2016 35
我可以以某种方式避免为正数和总计选择日期吗?我知道我可以简单地隐藏这些列,但是还有另一种方法吗?
Can I somehow avoid selecting Date for Positive and Total? I know I can simply hide these columns, but is there another way?
推荐答案
尝试结合使用数组{} :
= {query(A2:B13,选择A,求和(B),其中B <0按A标签的A顺序分组A'日期',sum(B)'负'",-1),query(query(A2:B13,选择A,sum(B),其中B> 0,按A标签的顺序由A标签A'Date',sum(B)'Positive'",-1),选择Col2"),query(query(A2:B13,选择A,sum(B)按标签A的顺序分组A'日期',sum(B)'总计'",-1),选择Col2")}
此构造: query(query(...),"select Col2")
将仅选择第二列数据.
This construction:
query(query(...),"select Col2")
will select only the second column of data.
和 {data1,data2,data3}
将 data2
, data3
放置在新列的右侧.
and {data1, data2, data3}
makes data2
, data3
to be placed to the right in a new column.
这篇关于Google表格查询:是否可以在没有选择的情况下进行分组/订购?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!