Case / if / filter / ?在谷歌表格查询

Question

我有一个注册表，其中包含所有具有其帐户id的交易。该寄存器有一个"类型"字段，我想在其上拉汇总数据。

事务:

| A       | B       | C           | D          |
|---------|---------|-------------|------------|
| Account | Amount  | Type        | Date       |
|---------|---------|-------------|------------|
| 1b6f    | 44.21   | Charge      | 2016-09-01 |
| 5g0p    | 101.57  | Charge      | 2016-09-01 |
| 5g0p    | 21.53   | Upgrade Fee | 2016-09-01 |
| 5g0p    | -123.10 | Payment     | 2016-09-07 |
| 1b6f    | 4.43    | Late Fee    | 2016-10-01 |
| 1b6f    | 4.87    | Late Fee    | 2016-11-01 |

我想要一个单一的查询，将允许我拉所有以下摘要信息

Account
Current Balance
Total Charges
Last Charge Date
First Charge Date
Total Fees
Last Fee Date
Total Payments
Last Payment Date

像这样:

=QUERY(
    A:D,
    "SELECT A,
     SUM(B),
     SUM(FILTER(D:D,C:C='Charge')),
     MAX(FILTER(D:D,C:C='Charge')),
     MIN(FILTER(D:D,C:C='Charge')),
     SUM(FILTER(B:B,ISNUMBER(FIND('Fee',C)))),
     MAX(FILTER(D:D,ISNUMBER(FIND('Fee',C)))),
     SUM(FILTER(B:B,C:C='Payment')),
     MAX(FILTER(D:D,C:C='Payment'))
     GROUP BY A
     label
     A 'Account',
     SUM(B) 'Current Balance',
     SUM(FILTER(D:D,C:C='Charge')) 'Total Charges',
     MAX(FILTER(D:D,C:C='Charge')) 'Last Charge Date',
     MIN(FILTER(D:D,C:C='Charge')) 'First Charge Date',
     SUM(FILTER(B:B,ISNUMBER(FIND('Fee',C)))) 'Total Fees',
     MAX(FILTER(D:D,ISNUMBER(FIND('Fee',C)))) 'Last Fee Date',
     SUM(FILTER(B:B,C:C='Payment')) 'Total Payments',
     MAX(FILTER(D:D,C:C='Payment')) 'Last Payment Date'
    "
)

与这些结果

| Account | Current Balance | Total Charges | Last Charge Date | First Charge Date | Total Fees | Last Fee Date | Total Payments | Last Payment Date |
|---------|-----------------|---------------|------------------|-------------------|------------|---------------|----------------|-------------------|
| 1b6f    | 53.51           | 44.21         | 2016-09-01       | 2016-09-01        | 9.30       | 2016-11-01    | 0              |                   |
| 5g0p    | 121.55          | 223.12        | 2016-10-01       | 2016-09-01        | 21.53      | 2016-09-01    | -123.10        | 2016-09-07        |

不幸的是，MAX需要输入一个列标识符，似乎你不能使用任何非标量函数(如FILTER)，甚至任何未列出的聚合函数(如JOIN)。

我目前正在使用不同的WHERE参数的一堆单独的查询，但是它是非常非常慢。

Answer 1

简短回答

在Google Sheets中，不可能使用唯一的QUERY来实现所需的结果。一种解决方案是优化独立查询集

<标题>

正如你已经提到的，Google Query Language不支持非标量值作为聚合函数的参数。

优化查询的一种方法是使用FILTER从源数组中删除没有WHERE子句的查询的空白行。

例子

=QUERY(
  FILTER(A:D,LEN(A:A),
  "SELECT A, SUM(B) LABEL A 'Current Balance' SUM(B)' Total Charges'"
)

然后你可以使用数组sintax

来连接所有查询的结果

={query1,query2}

<标题>引用

Google spreadsheet "=QUERY"Join()等效函数?

Answer 2

@trex005

答案在这里:https://docs.google.com/spreadsheets/d/1oC9RYfZD4ITnVfksIVFbC0KkadtFma-JUVk2PdiXqIA/edit?usp=sharing

主公式示例为:

=SUMPRODUCT(FILTER(Sheet1!$B$2:$B,Sheet1!$A$2:$A=$A2,Sheet1!$C$2:$C="Charge"))

它将自动从Sheet1添加一个新的ID到Sheet2，并对它们进行计数。我用同样的方式与谷歌表格来计算我的收入。这样使用google表单是非常有效的

"收费"的最后日期公式:

=MAX(FILTER(Sheet1!$D$2:$D,Sheet1!$A$2:$A=$A2,Sheet1!$C$2:$C="Charge"))

其中A2:A为

=UNIQUE(Sheet1!A2:A)

Answer 3

Pivot是一个选项:

=QUERY(A:D,"select A, max(D), min(D), sum(B) where B is not null group by A pivot C",0)

它不会显示总数