Često želimo izračunati neke vrijednosti po mjesecima. Kao, koliko je prodaje obavljeno u pertikularnom mjesecu. Pa to se može lako učiniti pomoću zaokretnih tablica, ali ako pokušavate imati dinamičko izvješće onda možemo upotrijebiti formulu SUMPRODUCT ili SUMIFS za zbroj po mjesecima.
Počnimo s rješenjem SUMPRODUCT.
Evo opće formule za dobivanje zbroja po mjesecima u Excelu
= SUMPRODUCT (zbroj_sumjena,-(TEXT (datumski raspon, "MMM") = mjesečni_tekst))
Zbir_donja : To je raspon koji želite zbrojiti po mjesecima.
Raspon datuma : To je razdoblje u kojem ćete gledati mjesecima.
Mjesec_tekst: To je mjesec u tekstualnom formatu za koji želite zbrojiti vrijednosti.
Pogledajmo sada primjer:
Primjer: Zbirne vrijednosti po mjesecima u Excelu
Ovdje imamo neku vrijednost povezanu s datumima. Ovi datumi su siječanj, veljača i ožujak u mjesecu 2019.
Kao što možete vidjeti na gornjoj slici, svi datumi su 2019. godina. Sada samo trebamo zbrojiti vrijednosti u E2: G2 po mjesecima u E1: G1.
Sada za zbroj vrijednosti prema mjesecima napišite ovu formulu u E2:
= PODIZVOD (B2: B9,-(TEKST (A2: A9, "MMM") = E1)))
Ako ga želite kopirati u susjedne ćelije, upotrijebite apsolutne reference ili imenovane raspone kao na slici.
To nam daje točan iznos svakog mjeseca.
Kako radi?
Polazeći iznutra, pogledajmo TEKST (A2: A9, "MMM") dio. Ovdje funkcija TEXT izdvaja mjesec iz svakog datuma u rasponu A2: A9 u tekstualnom formatu u niz. Prevođenje u formulu = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Zatim, TEKST (A2: A9, "MMM")= E1: Ovdje se svaki mjesec u nizu uspoređuje s tekstom u E1. Budući da E1 sadrži "Jan", svaki "Jan" u nizu se pretvara u TRUE, a drugi u FALSE. Ovo prevodi formulu u = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Dalje -(TEXT (A2: A9, "MMM") = E1), pretvara TRUE FALSE u binarne vrijednosti 1 i 0. Formula se prevodi u = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Konačno PODUZIMAK($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): funkcija SUMPRODUCT množi odgovarajuće vrijednosti u $ B $ 2: $ B $ 9 u niz {1; 1; 0; 1; 0; 0; 1; 0} i zbraja ih. Stoga dobivamo zbroj po vrijednosti kao 20052 u E1.
ZBIRAJTE AKO Mjeseci iz različite godine
U gornjem primjeru svi datumi su iz iste godine. Što ako su iz različitih godina? Gornja formula zbrajat će vrijednosti po mjesecima bez obzira na godinu. Na primjer, siječanj 2018. i siječanj 2019. bit će dodani, ako koristimo gornju formulu. Što je u većini slučajeva pogrešno.
To će se dogoditi jer nemamo kriterije za godinu u gornjem primjeru. Ako dodamo i kriterije za godinu, to će uspjeti.
Opća formula za dobivanje zbroja po mjesecima i godinama u Excelu
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (godina, 0)))
Ovdje smo dodali još jedan kriterij koji provjerava godinu. Sve ostalo je isto.
Riješimo gornji primjer, napišite ovu formulu u ćeliju E1 kako biste dobili zbroj siječnja u 2017. godini.
= PODIZVOD (B2: B9,-(TEKST (A2: A9, "MMM") = E1),-(TEKST (A2: A9, "gggg") = TEKST (D2,0)))
Prije kopiranja u donje ćelije koristite imenovane raspone ili apsolutne reference. Na slici sam koristio imenovane raspone za kopiranje u susjedne ćelije.
Sada možemo vidjeti i zbroj vrijednosti po mjesecima i godinama.
Kako radi?
Prvi dio formule isti je kao i prethodni primjer. Shvatimo dodatni dio koji dodaje kriterije godine.
-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") pretvara datum u A2: A9 kao godine u tekstualnom formatu u niz. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Većinu vremena godina je napisana u obliku broja. Za usporedbu broja s tekstom pretvorili smo int tekst godine koristeći TEXT (D2,0). Zatim smo usporedili ovu tekstualnu godinu s nizom godina kao TEXT (A2: A9, "yyyy") = TEXT (D2,0). Ovo vraća niz true-false {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Zatim smo pretvorili true false u broj pomoću operatora -. To nam daje {0; 0; 1; 1; 0; 1; 0; 1}.
Tako će se na kraju formula prevesti u = PODIZVOD (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Gdje su prvi niz vrijednosti. Sljedeći je odgovarajući mjesec, a treći je godina. Konačno dobivamo zbroj vrijednosti kao 2160.
Korištenje funkcije SUMIFS za zbroj po mjesecima
Opća formula
= SUMIFS (sum_range, date_range, ”> =” & startdate, date_range, ”<=” & EOMONTH (datum_početka, 0))
Ovdje,Zbir_donja : To je raspon koji želite zbrojiti po mjesecima.
Raspon datuma : To je razdoblje u kojem ćete gledati mjesecima.
Početni datum : To je datum početka s kojeg želite zbrojiti. U ovom primjeru bit će to 1. u danom mjesecu.
Primjer: Zbirne vrijednosti po mjesecima u Excelu
Ovdje imamo neku vrijednost povezanu s datumima. Ovi datumi su siječanj, veljača i ožujak u mjesecu 2019.
Moramo samo zbrojiti te vrijednosti do mjeseca. Sada je bilo lako ako smo imali mjesece i godine odvojeno. Ali nisu. Ovdje ne možemo koristiti nikakav stupac pomoći.
Stoga sam za pripremu izvješća pripremio format izvješća koji sadrži mjesec i zbroj vrijednosti. U stupcu mjesec zapravo imam datum početka mjeseca. Da biste samo vidjeli mjesec, odaberite datum početka i pritisnite CTRL+1.
U prilagođenom formatu napišite “mmm”.
Sada imamo spremne podatke. Zbrojimo vrijednosti po mjesecima.
Napišite ovu formulu u E3 za zbroj po mjesecima.
= ZBIRA (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Prije kopiranja formule upotrijebite apsolutne reference ili imenovane raspone.
Dakle, konačno smo dobili rezultat.
Dakle, kako to radi?
Kao što znamo da funkcija SUMIFS može zbrojiti vrijednosti po više kriterija.
U gornjem primjeru prvi kriterij zbraja sve vrijednosti u B3: B10 gdje je datum u A3: A10 veći ili jednak datumu u D3. D3 sadrži 1-Jan. I ovo se prevodi.
= SUMIFS (B3: B10, A3: A10, "> =" & "1-jan-2019", A3: A10, "<=" EOMONTH (D3,0))
Sljedeći kriterij je zbroj samo ako je datum u A3: A10 je manji ili jednak EOMONTH -u (D3,0). Funkcija EOMONTH samo vraća serijski broj zadnjeg navedenog mjeseca. Konačno se prevodi i formula.
= SUMIFS (B3: B10, A3: A10, "> = 1-jan-2019", A3: A10, "<= 31-jan-2019")
Dakle, dobivamo zbroj po mjesecima u excelu.
Prednost ove metode je što možete prilagoditi datum početka za zbrajanje vrijednosti.
Ako vaši datumi imaju različite godine, najbolje je koristiti zaokretne tablice. Zaokretne tablice mogu vam pomoći da lako odvojite podatke u godišnjem, tromjesečnom i mjesečnom formatu.
Pa da, dečki, ovako možete zbrojiti vrijednosti po mjesecima. Oba načina imaju svoje specijalitete. Odaberite koji način želite.
Ako imate bilo kakvih upita vezanih uz ovaj članak ili bilo koje druge upite vezane uz Excel i VBA, odjeljak komentara je otvoren za vas.
Vezani članci:
Kako koristiti funkciju SUMIF u Excelu
ZBIRKE s datumima u Excelu
SUMIF s ćelijama koje nisu prazne
Kako koristiti funkciju SUMIFS u Excelu
ZBIRA pomoću logike AND-OR
Popularni članci
50 Excel prečac za povećanje vaše produktivnosti: Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti tFunkcija VLOOKUP u Excelu: Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju COUNTIF u Excelu: Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu: Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.