Vraag Vat groeperen samen op jaar en maand


Ik heb een werkblad bewegingen met twee velden:

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

Ik wil in een ander werkblad hebben MonthSum die de sommen van de gegevens in de kolom weergeeft bedrag gegroepeerd op jaar + maand van de kolom datum:

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

Ik wil niet de cellen specificeren waar de spreadsheet moet optellen, ik wil een generieke formule om mijn gegevens per maand + jaar te groeperen. Als ik in een MySQL-database zat, zou ik gewoon doen:

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

Ik heb de oplossing nodig om aan te werken Google Spreadsheets.

Ik denk dat mogelijke oplossingen SUMIF of draaitabellen of Google Spreadsheets QUERY zouden zijn functie.


40
2017-09-05 23:32


oorsprong


antwoorden:


Je kunt het doen met een matrixformule, ik heb de formule erin gezet A2 en in A1, ik stop het maandnummer:

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

Zie de document in actie in Google Spreadsheet

[EDIT] Als je zowel jaar als maand wilt:

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

29
2017-09-06 06:45



Dit is wat draaitabellen zijn voor.

ik heb gemaakt Een voorbeeld gebruik van uw gegevens.

Voeg eerst een kolom toe om de datum als uw maandelijkse tekenreeks op te maken, bijvoorbeeld '2009-01' met de formule:

TEXT(A2, "YYYY-MM")

enter image description here


Markeer vervolgens de gegevens en kies "data"> "draaitabelrapport ..."

enter image description here


Selecteer voor "Rijen" "maand"

enter image description here


Voor waarden, selecteer "bedrag"

enter image description here


TADA! Dat is het!

enter image description here

Zie voor een snel overzicht van draaitabellen deze Google-blogpost.


87
2018-01-31 15:52



naast de bijdrage van JMax, laat me de volgende formule presenteren met behulp van de QUERY-functie ...

=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' ")

Opmerkingen: Ik heb aangenomen dat de originele gegevens in Blad3 staan en mijn resultaat presenteert de combinatie van jaar en maand als jjjj.mm in plaats van jjjj-mm Ik heb een illustratie gemaakt:

yogi


11
2017-07-07 22:46



Ik werkte met een soortgelijk probleem. Ik vond deze formule goed werken.

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

In cel A2 Ik plaatste de maand die ik wilde totaliseren.


0
2017-09-24 03:12



Het antwoord van matte brandwonden en Cœur is vrij accuraat en ik wil graag een opmerking toevoegen (ik heb het niet toegevoegd aan de commentaarsectie omdat het vrij lang is)

Misschien wilt u in de plaats deze formule gebruiken: DATE(YEAR(A2);MONTH(A2);1) om te groeperen op jaar en maand.

Op deze manier jij zullen waarden als datums houden en daarom in staat om elke vorm van toepassing te zijn datumnotatie of sorteren in uw draaitabel.


Uitleg die je kunt overslaan:

In plaats van TEXT(A2, "YYYY-MM") converteert de waarde in tekst en daarmee de vrijheid voor verdere datummanipulatie.

DATE(YEAR(A2);MONTH(A2);1) verwijder de dag en tijd vanaf de datum, bijvoorbeeld 2018/05/03 en 2018/05/06 beide worden 2018/05/01 wat nog steeds een datum is die dan kan worden gegroepeerd.

Door een formaat "JJJJ-MM" direct op de draaitabel toe te passen, bereikt u hetzelfde resultaat.


0
2018-05-01 20:35