Суммируйте группировку по годам и месяцам

У меня есть рабочий лист Движения с двумя полями:

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, который отображает суммы данных в столбце, сгруппированные по году + месяца по дате столбца:

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

Я не хочу указывать ячейки, где должна сводиться таблица, я хочу, чтобы общая формула группировала мои данные в месяц + год. Если бы я был в базе данных MySQL, я бы просто сделал:

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

Мне нужно решение для работы с таблицами Google.

Я думаю, что возможные решения будут использовать SUMIF или сводные таблицы или таблицы Google QUERY функция.

Ответы

Ответ 1

Вы можете сделать это с помощью формулы массива, я ставлю формулу в A2 и в A1, я поставлю номер месяца:

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

См. doc в действии в Таблице Google

[EDIT] Если вы хотите как год, а месяц:

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

Ответ 2

Вот для чего предназначены сводные таблицы.

Я создал пример, используя ваши данные.

Сначала добавьте столбец для форматирования даты в виде вашей ежемесячной строки, например "2009-01", с формулой:

TEXT(A2, "YYYY-MM")

enter image description here

Затем выделите данные и выберите "data" > "отчет сводной таблицы..."

enter image description here

Для строк выберите "месяц"

enter image description here

Для значений выберите "количество"

enter image description here

TADA! Что это!

enter image description here

Для краткого обзора сводных таблиц см. этот пост в блоге Google.

Ответ 3

в дополнение к вкладу от JMax, позвольте мне представить следующую формулу, используя функцию QUERY...

=query(index('Sheet3'!A:B); "select year(Col1)+(month(Col1)+1)/100,sum(Col2) where Col1 is not null group by year(Col1)+(month(Col1)+1)/100 label year(Col1)+(month(Col1)+1)/100 'mydate',sum(Col2) 'amount' ")

Примечания: Я предположил, что исходные данные приведены в Sheet3 и мой результат представляет год и месяц combinationas yyyy.mm вместо yyyy-mm Я создал иллюстрацию:

yogi

Ответ 4

Я работал с аналогичной проблемой. Я нашел эту формулу хорошо работать.

=SUMIF(ARRAYFORMULA(MONTH('Sheet1'!A:A)), A2, 'Sheet1'!B:B)

В ячейке A2 я помещаю месяц, который хотел получить.