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屋！