Generička formula
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Sažetak
Za sumiranje po mjesecima možete upotrijebiti formulu koja se temelji na funkciji SUMIFS, uz pomoć funkcije EOMONTH. U prikazanom primjeru formula u F4 je:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Ova formula koristi imenovane raspone "iznos" (C4: C9) i "datum" (B4: B9).
Obrazloženje
Funkcija SUMIFS može zbrajati vrijednosti u rasponima na temelju više kriterija.
U ovom slučaju konfiguriramo SUMIFS za zbrajanje iznosa po mjesecima koristeći dva kriterija: jedan koji odgovara datumima većim ili jednakim prvom danu u mjesecu, drugi koji odgovaraju datumima kraćim ili jednakim posljednjem danu u mjesecu. Drugim riječima, potrebna nam je formula poput ove:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Ili što je neprobojnija alternativa:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Ova je verzija robusnija jer se datumi izrađuju s odvojenim vrijednostima godine, mjeseca i dana, a ne unose se kao tekst koji Excel treba protumačiti.
To je obično muka, jer ako nazive mjeseci dodate kao tekst (npr. "Siječanj", "Veljača", "Ožujak", itd.) U stupac E, morate se dodatno pozabaviti stvaranjem datuma koje možete koristiti za kriterije . Međutim, u ovom slučaju koristimo jednostavan trik kako bismo stvari olakšali: u stupac E, umjesto da upisujemo nazive mjeseci, dodajemo stvarne datume (1.1.2016., 1.6.2016., 1.3.2016.), zatim upotrijebite prilagođeni format datuma ("mmmm") za prikaz naziva mjeseca.
To olakšava izgradnju kriterija koji su nam potrebni za SUMIFS. Za podudaranje datuma većih ili jednakih prvom u mjesecu koristimo:
">="&E4
A da bismo podudarali datume manje ili jednake posljednjem danu u mjesecu, koristimo ovo:
"<="&EOMONTH(E4,0)
S nula mjesecima, funkcija EOMONTH automatski vraća zadnji dan u mjesecu predviđen datumom.
Spajanje znakom & (&) potrebno je kod izrade kriterija koji koriste logički operator s numeričkom vrijednošću.
Rješenje zaokretne tablice
Zaokretna tablica izvrsno je rješenje kada trebate sažeti podatke po godini, mjesecu, tromjesečju itd., Jer će oni za vas napraviti takvu vrstu grupiranja bez ikakvih formula. Za usporednu usporedbu formula i zaokretnih tablica pogledajte ovaj videozapis: Zašto zaokretne tablice.
