按年份和月份总结分组 [英] Summarize grouping by year and month

问题描述

我有一个工作表 Movements 带有两个字段：

I have a worksheet Movements with two fields:

mydate      | amount
2009-01-01  | 10.00
2009-01-02  | 13.00
2009-02-01  | 11.00
2009-02-05  | 12.00
2009-02-08  | 52.00

我希望在另一个工作表 MonthSum 中显示总和 列中 列中的数据按年份+月份分组：

I want to have in another worksheet MonthSum that displays the sums of the data in the column amount grouped by year+month of the column date:

mydate      | amount
2009-01     | 23.00
2009-02     | 75.00

我不想指定电子表格必须求和的单元格，我想要一个通用的公式来分组我的数据每月+年。如果我在一个MySQL数据库中，我会简单地做：

I don't want to specify the cells where the spreadsheet has to sum, I want a generic formula to group my data per month+year. If I was on a MySQL database I would simply do:

SELECT DATE_FORMAT('%Y-%m', mydate), SUM(amount) 
FROM mytable GROUP BY DATE_FORMAT('%Y-%m', mydate)

我需要解决方案来处理 Google电子表格。

I need the solution to work on Google Spreadsheets.

我认为可能的解决方案是使用SUMIF或数据透视表或Google电子表格QUERY function 。

I think that possible solutions would be using SUMIF or pivot tables or Google Spreadsheets QUERY function.

推荐答案

一个数组公式，我把公式放在 A2 和 A1 中，我把月份数字放进去：

You can do it with an array formula, i put the formula in A2 and in A1, i put the month number:

=ARRAYFORMULA(SUMPRODUCT(1*(MONTH(Sheet1!$A$2:$A$6)=A1)*Sheet1!$B$2:$B$6))

请参阅 doc在Google电子表格中的作用

如果你想每年和每月：

If you want both year and month:

=ARRAYFORMULA(SUMPRODUCT(1*(YEAR(Sheet1!$A$2:$A$8)=A2)*(MONTH(Sheet1!$A$2:$A$8)=B2)*Sheet1!$B$2:$B$8))

这篇关于按年份和月份总结分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！