Velikokrat želimo izračunati nekatere vrednosti po mesecih. Na primer, koliko prodaje je bilo opravljenega v pertikularnem mesecu. No, to je mogoče enostavno narediti s pomočjo vrtilnih tabel, če pa poskušate imeti dinamično poročilo, lahko za seštevanje po mesecih uporabimo formulo SUMPRODUCT ali SUMIFS.
Začnimo z rešitvijo SUMPRODUCT.
Tukaj je splošna formula za vsoto po mesecih v Excelu
= SUMPRODUCT (skupni_obseg,-(TEXT (datumski obseg, "MMM") = mesečni_tekst))
Sum_range : To je obseg, ki ga želite sešteti po mesecih.
Date_range : To je časovno obdobje, v katerem boste iskali mesece.
Mesec_tekst: To je mesec v besedilni obliki, v katerem želite sešteti vrednosti.
Zdaj pa poglejmo primer:
Primer: Vsote vrednosti po mesecih v Excelu
Tu imamo neko vrednost, povezano z datumi. Ti datumi so januar, februar in marec v letu 2019.
Kot lahko vidite na zgornji sliki, so vsi datumi leto 2019. Zdaj moramo samo sešteti vrednosti v E2: G2 po mesecih v E1: G1.
Za seštevanje vrednosti po mesecih napišite to formulo v E2:
= PODROČJE (B2: B9,-(BESEDILO (A2: A9, "MMM") = E1)))
Če ga želite kopirati v sosednje celice, uporabite absolutne reference ali imenovane obsege kot vi na sliki.
Tako dobimo natančno vsoto za vsak mesec.
Kako deluje?
Začnimo od znotraj, poglejmo BESEDILO (A2: A9, "MMM") del. Tu funkcija TEXT izvleče mesec iz vsakega datuma v obsegu A2: A9 v besedilni obliki v matriko. Prevajanje v formulo = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Nato TEXT (A2: A9, "MMM")= E1: Tu se vsak mesec v matriki primerja z besedilom v E1. Ker E1 vsebuje "Jan", se vsak "Jan" v nizu pretvori v TRUE, drugo pa v FALSE. To prevede formulo v = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Naprej -(TEXT (A2: A9, "MMM") = E1) pretvori TRUE FALSE v binarne vrednosti 1 in 0. Formula se prevede v = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Končno PODROČJE($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): funkcija SUMPRODUCT pomnoži ustrezne vrednosti v $ B $ 2: $ B $ 9 v niz {1; 1; 0; 1; 0; 0; 1; 0} in jih sešteje. Tako dobimo vsoto po vrednosti kot 20052 v E1.
SUM ČE Meseci iz drugega leta
V zgornjem primeru so bili vsi datumi iz istega leta. Kaj pa če bi bili iz različnih let? Zgornja formula bo seštevala vrednosti po mesecih ne glede na leto. Če na primer uporabimo zgornjo formulo, bosta na primer dodana januar 2018 in januar 2019. Kar je v večini primerov narobe.
To se bo zgodilo, ker v zgornjem primeru za leto nimamo nobenih meril. Če dodamo tudi merila za leto, bo delovalo.
Splošna formula za vsoto po mesecih in letih v Excelu
= SUMPRODUCT (sum_range,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (leto, 0)))
Tu smo dodali še eno merilo, ki preverja leto. Vse ostalo je enako.
Rešimo zgornji primer, zapišite to formulo v celico E1, da dobite vsoto januarja v letu 2017.
= PODROČJE (B2: B9,-(BESEDILO (A2: A9, "MMM") = E1),-(BESEDILO (A2: A9, "llll") = BESEDILO (D2,0)))
Pred kopiranjem v spodnje celice uporabite imenovane obsege ali absolutne reference. Na sliki sem uporabil imenovane obsege za kopiranje v sosednjih celicah.
Zdaj lahko vidimo tudi vsoto vrednosti po mesecih in letih.
Kako deluje?
Prvi del formule je enak prejšnjemu primeru. Razumemo dodatni del, ki dodaja merila za leto.
-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") pretvori datum v A2: A9 kot leta v besedilni obliki v matriko. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Večino časa je leto zapisano v številčni obliki. Za primerjavo števila z besedilom smo pretvorili leto int besedilo z uporabo TEXT (D2,0). Nato smo to besedilno leto primerjali z nizom let kot TEXT (A2: A9, "yyyy") = TEXT (D2,0). To vrne polje true-false {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Nato smo z uporabo operatorja pretvorili resnično napačno vrednost v številko. Tako dobimo {0; 0; 1; 1; 0; 1; 0; 1}.
Tako bo formula na koncu prevedena v = PODROČJE (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Kjer je prvo polje vrednosti. Naslednji je mesec, tretji pa leto. Končno dobimo vsoto vrednosti 2160.
Uporaba funkcije SUMIFS za seštevanje po mesecih
Splošna formula
= SUMIFS (sum_range, date_range, ”> =” & startdate, date_range, ”<=” & EOMONTH (start_date, 0))
Tukaj,Sum_range : To je obseg, ki ga želite sešteti po mesecih.
Date_range : To je časovno obdobje, v katerem boste iskali mesece.
Začetni datum : To je začetni datum, od katerega želite sešteti. V tem primeru bo 1. v danem mesecu.
Primer: Vsote vrednosti po mesecih v Excelu
Tu imamo neko vrednost, povezano z datumi. Ti datumi so januar, februar in marec v letu 2019.
Te vrednosti moramo le sešteti do meseca. Zdaj je bilo enostavno, če bi imeli mesece in leta ločeno. Ampak niso. Tu ne moremo uporabiti nobenega stolpca za pomoč.
Zato sem za pripravo poročila pripravil obliko poročila, ki vsebuje mesec in vsoto vrednosti. V stolpcu mesec imam dejansko datum začetka meseca. Če si želite ogledati samo mesec, izberite začetni datum in pritisnite CTRL+1.
V obliki po meri napišite "mmm".
Zdaj imamo pripravljene podatke. Vredimo vrednosti po mesecih.
To formulo zapišite v E3, če želite vsoto po mesecih.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Pred kopiranjem formule uporabite absolutne reference ali imenovane obsege.
Tako smo končno dobili rezultat.
Torej, kako deluje?
Kot vemo, lahko funkcija SUMIFS sešteje vrednosti po več merilih.
V zgornjem primeru je prvo merilo vsota vseh vrednosti v B3: B10, kjer je datum v A3: A10 večji ali enak datumu v D3. D3 vsebuje 1-Jan. Tudi to se prevaja.
= SUMIFS (B3: B10, A3: A10, "> =" & "1-jan-2019", A3: A10, "<=" EOMONTH (D3,0))
Naslednja merila so vsota samo, če je datum v A3: A10 je manjši ali enak EOMONTH (D3,0). Funkcija EOMONTH vrne samo zaporedno številko zadnjega navedenega meseca. Končno se prevede tudi formula.
= SUMIFS (B3: B10, A3: A10, "> = 1-jan-2019", A3: A10, "<= 31-jan-2019")
Tako dobimo vsoto po mesecih v Excelu.
Prednost te metode je, da lahko prilagodite začetni datum seštevanja vrednosti.
Če imajo vaši datumi drugačna leta, je najbolje uporabiti vrtilne tabele. Z vrtilnimi tabelami lahko preprosto in enostavno ločite podatke v letni, četrtletni in mesečni obliki.
Tako da fantje, tako lahko seštejete vrednosti po mesecih. Oba načina imata svoje posebnosti. Izberite, kateri način vam je všeč.
Če imate kakršna koli vprašanja v zvezi s tem člankom ali drugimi poizvedbami, povezanimi z Excelom in VBA, je odsek za komentarje odprt.
Povezani članki:
Kako uporabljati funkcijo SUMIF v Excelu
SUMIFS z datumi v Excelu
SUMIF z nepraznjenimi celicami
Kako uporabljati funkcijo SUMIFS v Excelu
SUMIFS z uporabo logike AND-OR
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost: Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu: To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu: Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu: To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.