Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako koristiti funkciju SUMIFS za zbrajanje podataka koji odgovaraju određenim mjesecima u Excelu i Google tablicama.
Zbroj ako po mjesecu
Prvo ćemo pokazati kako zbrojiti podatke koji odgovaraju datumima koji padaju u određeni mjesec i godinu.
Možemo upotrijebiti funkciju SUMIFS, zajedno s funkcijama DATE, YEAR, MONTH i EOMONTH da zbrojimo Broj prodaje unutar svakog Mjesec.
| 1 | = ZBIRKE (C3: C9, B3: B9, "> =" & DATUM (GODINA (E3), MJESEC (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Kako bismo izgradili gornju formulu, počinjemo definiranjem datumskog raspona za svaki mjesec. Koristimo funkciju DATE za definiranje prvog dana u mjesecu (npr. 5/1/2021). To možemo učiniti "tvrdo kodiranjem" datuma u formulu:
| 1 | "> =" & DATE (2021,5,1) |
Ili, još bolje, možemo učiniti kriterije fleksibilnim pozivanjem na datum unutar ćelije E3, izdvajanjem godine i mjeseca datuma (i postavljanjem dana jednakim 1), kao što je prikazano u gornjem primjeru:
| 1 | "> =" & DATUM (GODINA (E3), MJESEC (E3), 1) |
Za definiranje posljednjeg dana u mjesecu možemo koristiti funkciju EOMONTH:
| 1 | "<=" & EOMONTH (E3,0) |
Spajajući sve ove kriterije, možemo napisati sljedeću formulu SUMIFS:
| 1 | = ZBIRKE (C3: C9, B3: B9, "> =" & DATUM (GODINA (E3), MJESEC (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Zaključavanje referenci ćelija
Radi lakšeg čitanja formula prikazali smo formule bez zaključanih referenci ćelija:
| 1 | = ZBIRKE (C3: C9, B3: B9, "> =" & DATUM (GODINA (E3), MJESEC (E3), 1), B3: B9, "<=" & EOMONTH (E3,0)) |
Ali ove formule neće raditi ispravno ako ih kopirate i zalijepite na drugo mjesto u datoteci. Umjesto toga, trebali biste koristiti zaključane ćelijske reference poput ove:
| 1 | = ZBIRKE ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATUM (GODINA (E3), MJESEC (E3), 1), $ B $ 3: $ B $ 9, "<=" & EOMONTH (E3,0)) |
Pročitajte naš članak o zaključavanju referenci stanica da biste saznali više.
Oblikovanje mjesečnih vrijednosti
U ovom primjeru, naveli smo mjesece u stupcu E. Ove mjesečne vrijednosti su zapravo datumi oblikovani tako da izostavljaju dan, koristeći oblikovanje prilagođenog broja.
Prilagođeni format podataka je "mmm gggg" za prikaz svibnja 2022.
Zbroj po mjesecu kroz više godina
Gornji primjer sažeo je podatke s datumima koji su padali unutar određenog mjeseca i godine. Umjesto toga možete zbrojiti podatke s datumima koji padaju unutar mjesec dana u bilo kojoj godini pomoću funkcije SUMPRODUCT.
| 1 | = PODIZVOD (C3: C8,-(MJESEC (B3: B8) = MJESEC (G3))) |
U ovom primjeru koristimo funkciju SUMPRODUCT za izvođenje kompliciranih izračuna "zbroj ako". Prođimo kroz formulu.
Ovo je naša konačna formula:
| 1 | = PODIZVOD (C3: C8,-(MJESEC (B3: B8) = MJESEC (G3))) |
Prvo, funkcija SUMPRODUCT navodi popis Broj prodaje za svakoga Datum prodaje a zatim uspoređuje svaki mjesec Datum prodaje protiv navedenog Mjesec, vraća TRUE ako se mjeseci podudaraju, ili FALSE ako se ne podudaraju:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})) |
Dalje crtice (-) pretvaraju TRUE i FALSE vrijednosti u 1s i 0s:
| 1 | = PODIZVOD ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
Funkcija SUMPRODUCT zatim množi svaki par unosa u nizovima:
| 1 | = PODIZVOD ({30; 0; 51; 0; 17; 0}) |
Konačno, brojevi u nizu se zbrajaju:
| 1 | =98 |
Više pojedinosti o korištenju logičkih izraza i naredbe “-” u funkciji SUMPRODUCT možete pronaći ovdje
Zbroj ako po mjesecu u Google tablicama
Ove formule rade potpuno isto u Google tablicama kao i u Excelu.
