google-sheets - arrayformula 中的 Google Sheets 查询充当 SUMIFS

Question

我的查询有以下表格源bets_lps

+----+-----------+--------+------------+-----------+---------------+----------+
|  X | A         | B      | C          | D         | E             |    F     |
+----+-----------+--------+------------+-----------+---------------+----------+
| 1  | Monthdate | USERID | SPENT      | PURCHASED | NOT ALLOCATED | INCOME   |
+----+-----------+--------+------------+-----------+---------------+----------+
| 2  | 11-2019   | USER1  | 0          | 0         | -119.37       | 11937    |
+----+-----------+--------+------------+-----------+---------------+----------+
| 3  | 07-2019   | USER2  | -566       | -3897     | -2137         | 0        |
+----+-----------+--------+------------+-----------+---------------+----------+
| 4  | 08-2019   | USER3  | -872       | 0         | -487          | 0        |
+----+-----------+--------+------------+-----------+---------------+----------+
| 5  | 09-2019   | USER10 | -559       | 0         | 0             | 1231     |
+----+-----------+--------+------------+-----------+---------------+----------+
| 6  | 07-2019   | USER5  | -9063.68   | 0         | -11500.82     | 0        |
+----+-----------+--------+------------+-----------+---------------+----------+
| 7  | 11-2019   | USER6  | 0          | 0         | -100          | 0        |
+----+-----------+--------+------------+-----------+---------------+----------+
| 8  | 10-2019   | USER1  | -41384.99  | -33015    | -51895.01     | 12629500 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 9  | 11-2019   | USER8  | -320316.33 | 0         | -2299.99      | 91881    |
+----+-----------+--------+------------+-----------+---------------+----------+
| 10 | 12-2019   | USER9  | -0.01      | -4499.99  | 0             | 18000000 |
+----+-----------+--------+------------+-----------+---------------+----------+
| 11 | 11-2019   | USER10 | 0          | 0         | -518          | 51800    |
+----+-----------+--------+------------+-----------+---------------+----------+
| 12 | 07-2019   | USER5  | -799.32    | 0         | -3320.68      | 0        |
+----+-----------+--------+------------+-----------+---------------+----------+
| 13 | 12-2019   | USER12 | 0          | 0         | -80           | 0        |
+----+-----------+--------+------------+-----------+---------------+----------+
| 14 | 09-2019   | USER1  | -5820.12   | -13.1     | -2777.78      | 0        |
+----+-----------+--------+------------+-----------+---------------+----------+
| 15 | 11-2019   | USER7  | -1637      | -35       | -182          | 185400   |
+----+-----------+--------+------------+-----------+---------------+----------+

我正在尝试创建arrayformula一个查询sumifs，用于为下表中的每个用户查找特定 MO​​NTH（第 1 行）上一个表中的 INCOME（col F）总和

+----+--------+---------+---------+---------+---------+---------+---------+
| x  | A      | B       | C       | D       | E       | F       | G       |
+----+--------+---------+---------+---------+---------+---------+---------+
| 1  | MONTH  | 07-2019 | 08-2019 | 09-2019 | 10-2019 | 11-2019 | 12-2019 |
+----+--------+---------+---------+---------+---------+---------+---------+
| 2  | USER1  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 3  | USER2  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 4  | USER3  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 5  | USER4  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 6  | USER5  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 7  | USER6  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 8  | USER7  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 9  | USER8  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 10 | USER9  |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+
| 11 | USER10 |         |         |         |         |         |         |
+----+--------+---------+---------+---------+---------+---------+---------+

我使用此查询在特定月份为每个用户获取正确的总和：'07-2019' 中的 USER1 示例：

=query(query(bets_lps!$A:$F,"select SUM(F) WHERE B='"&$A2&"' and A='"&B$1&"' GROUP BY B,A",0),"SELECT * OFFSET 1", 0)

当尝试将查询转换为 arrayformula 以避免填充所有单元格时，它不会按预期扩展。有什么建议吗？

添加：Google Sheets演示（使用 sumifs 转换为 arrayformulas）

标签： google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querysumifs

Answer 1

尝试：

=QUERY(A2:F, "select B,sum(F) where B is not null group by B pivot A")