Podsumuj Grupowanie według roku i miesiąca
Mam arkusz z dwoma polami:
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
Chcę mieć w innym arkuszu MonthSum który wyświetla sumy danych w kolumnie amount pogrupowane według roku+miesiąca w kolumnie Data :
mydate | amount
2009-01 | 23.00
2009-02 | 75.00
Nie chcę określać komórek, w których arkusz kalkulacyjny ma sumować, chcę ogólną formułę grupującą moje dane na miesiąc+rok. Gdybym był na bazie MySQL to po prostu bym zrobił:
SELECT DATE_FORMAT('%Y-%m', mydate), SUM(amount)
FROM mytable GROUP BY DATE_FORMAT('%Y-%m', mydate)
Potrzebuję rozwiązania do pracy nad Arkusze Kalkulacyjne Google .
Myślę, że możliwe rozwiązania to użycie SUMIF lub pivot tables lub Google Spreadsheets QUERY function .
5 answers
Możesz to zrobić za pomocą formuły tablicy, ja umieszczam formułę w A2 i w A1, wpisuję numer miesiąca:
=ARRAYFORMULA(SUMPRODUCT(1*(MONTH(Sheet1!$A$2:$A$6)=A1)*Sheet1!$B$2:$B$6))
Zobacz doc w działaniu w arkuszu kalkulacyjnym Google
[edytuj] jeśli chcesz rok i miesiąc:
=ARRAYFORMULA(SUMPRODUCT(1*(YEAR(Sheet1!$A$2:$A$8)=A2)*(MONTH(Sheet1!$A$2:$A$8)=B2)*Sheet1!$B$2:$B$8))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-09-06 14:31:39
Do tego służą tabele przestawne .
Stworzyłem przykład używając Twoich danych.
Najpierw dodaj kolumnę, aby sformatować datę jako ciąg miesięczny, np. "2009-01" ze wzorem:
TEXT(A2, "YYYY-MM")
Następnie zaznacz Dane i wybierz" dane " > " raport tabeli przestawnej..."
Dla wierszy wybierz "miesiąc"
Dla wartości wybierz "kwota"
TADA! To jest to!
Aby uzyskać szybki przegląd tabel przestawnych, zobacz ten wpis na blogu Google .
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-19 04:27:29
Oprócz wkładu JMax, pozwól, że przedstawię następującą formułę za pomocą funkcji zapytania ...
=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' ")
Przypisy: Założyłem, że oryginalne dane są w arkuszu 3 a mój wynik przedstawia zestawienie roku i miesiąca yyyy.mm zamiast RRRR-mm Stworzyłem ilustrację:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-07-08 03:32:34
Odpowiedź Matta Burnsa i Cœura jest dość dokładna i chciałbym dodać komentarz (nie dodałem go do sekcji komentarzy, ponieważ jest dość długi)
Możesz użyć tej formuły zamiast:
DATE(YEAR(A2);MONTH(A2);1) do grupowania według roku i miesiąca.
W ten sposób zachowasz wartości jako daty i będziesz mógł zastosować dowolny rodzaj formatowania daty lub sortowania w tabeli przestawnej.
Wyjaśnienie można pominąć:
Zamiast TEXT(A2, "YYYY-MM") konwertuje wartość w tekście i w związku z tym pozbawienie wolności o dalszej manipulacji datą.
DATE(YEAR(A2);MONTH(A2);1) Usuń dzień i godzinę Z daty, więc na przykład 2018/05/03 oraz 2018/05/06 oboje stają się 2018/05/01 która nadal jest datą, którą można następnie pogrupować.
Stosując format" RRRR-MM " bezpośrednio na tabeli przestawnej uzyskujesz ten sam wynik.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-05-01 20:35:42
Pracowałem nad podobnym problemem. Znalazłem, że ta formuła działa dobrze.
=SUMIF(ARRAYFORMULA(MONTH('Sheet1'!A:A)), A2, 'Sheet1'!B:B)
W komórce A2 wkładam miesiąc, który chciałem
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-09-24 03:12:36