Mõnikord tahame arvutada mõned väärtused kuude kaupa. Näiteks kui palju müüdi ühe kuu jooksul. Seda saab hõlpsalt teha liigendtabelite abil, kuid kui proovite saada dünaamilist aruannet, siis saame kasutada kuude kaupa summeerimiseks valemit SUMPRODUCT või SUMIFS.
Alustame SUMPRODUCT lahendusega.
Siin on üldvalem Excelis iga kuu summa saamiseks
= SUMPRODUCT (summa_vahemik,-(TEXT (kuupäeva_vahemik, "MMM") = kuu_tekst))
Summa_vahemik : See on vahemik, mille soovite kuude kaupa summeerida.
Kuupäevavahemik : See on kuupäevavahemik, mida vaatate mitu kuud.
Kuu_tekst: See on tekstivormingus kuu, mille väärtused soovite kokku liita.
Vaatame nüüd näidet:
Näide: Summa väärtused kuude kaupa Excelis
Siin on meil kuupäevadega seotud väärtus. Need kuupäevad on aasta jaanuar, veebruar ja märts 2019.
Nagu ülaltoodud pildilt näha, on kõik kuupäevad 2019. aastal. Nüüd peame lihtsalt summad E2: G2 summeerima kuude kaupa E1: G1.
Nüüd, kui summeerida väärtused kuude järgi, kirjutage see valem lahtrisse E2:
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1)))
Kui soovite selle kopeerida kõrvalasuvatesse lahtritesse, kasutage absoluutviiteid või nimevahemikke nagu pildil.
See annab meile iga kuu täpse summa.
Kuidas see töötab?
Alustades seestpoolt, vaatame TEKST (A2: A9, "MMM") osa. Siin ekstraheerib funktsioon TEXT teksti vormingus iga kuupäeva vahemikus A2: A9 kuu. Valemisse tõlkimine = SUMPRODUCT (B2: B9,-({"Jan"; "Jan"; "Feb"; "Jan"; "Feb"; "Mar"; "Jan"; "Feb"} = E1) )
Järgmine, TEXT (A2: A9, "MMM")= E1: Siin võrreldakse iga kuu massiivis teksti E1 tekstiga. Kuna E1 sisaldab “Jan”, teisendatakse massiivi iga “Jan” väärtuseks TRUE ja teine väärtuseks FALSE. See tõlgib valemi väärtuseks = SUMPRODUCT ($ B $ 2: $ B $ 9,-{TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE})
Järgmine -(TEXT (A2: A9, "MMM") = E1) teisendab TRUE FALSE binaarväärtusteks 1 ja 0. Valem tõlgitakse järgmiselt = SUMPRODUCT ($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}).
Lõpuks ometi SUMPRODUCT($ B $ 2: $ B $ 9, {1; 1; 0; 1; 0; 0; 1; 0}): funktsioon SUMPRODUCT korrutab vastavad väärtused $ B $ 2: $ B $ 9 massiivi {1; 1; 0; 1; 0; 0; 1; 0} ja liidab need kokku. Seega saame E1 väärtuse järgi summa 20052.
SUM, kui kuud erinevast aastast
Ülaltoodud näites olid kõik kuupäevad samast aastast. Mis oleks, kui nad oleksid erinevatest aastatest? Ülaltoodud valem summeerib väärtused kuude kaupa olenemata aastast. Näiteks, kui kasutame ülaltoodud valemit, lisatakse 2018. aasta jaanuar ja 2019. aasta jaanuar. Mis on enamikul juhtudel vale.
See juhtub seetõttu, et ülaltoodud näites pole meil aasta kohta ühtegi kriteeriumi. Kui lisada ka aastakriteeriumid, siis see toimib.
Üldvalem Excelis kuu ja aasta kaupa summa saamiseks
= SUMPRODUCT (summa_vahemik,-(TEXT (date_range, "MMM") = month_text),-(TEXT (date_range, "yyyy") = TEXT (aasta, 0)))
Siin oleme lisanud veel ühe kriteeriumi, mis kontrollib aastat. Kõik muu on sama.
Lahendame ülaltoodud näite, kirjutage see valem lahtrisse E1, et saada jaani summa aastal 2017.
= SUMPRODUCT (B2: B9,-(TEXT (A2: A9, "MMM") = E1),-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)))
Enne allolevatesse lahtritesse kopeerimist kasutage nimevahemikke või absoluutviiteid. Pildil olen kasutanud külgnevates lahtrites kopeerimiseks nimega vahemikke.
Nüüd näeme väärtuse summat ka kuude ja aastate kaupa.
Kuidas see töötab?
Valemi esimene osa on sama, mis eelmises näites. Mõistame täiendavat osa, mis lisab aasta kriteeriumid.
-(TEXT (A2: A9, "yyyy") = TEXT (D2,0)): TEXT (A2: A9, "yyyy") teisendab A2: A9 kuupäeva tekstivormingus aastateks massiiviks. {"2018"; "2019"; "2017"; "2017"; "2019"; "2017"; "2019"; "2017"}.
Enamasti kirjutatakse aasta numbrivormingus. Arvude võrdlemiseks meie tekstiga olen teisendanud aasta siseteksti tekstiga TEXT (D2,0). Järgmisena võrdlesime seda tekstiaastat aastate massiiviga TEXT (A2: A9, "yyyy") = TEXT (D2,0). See tagastab massiivi tõene-vale {FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}. Järgmisena teisendasime tõese vale arvuks, kasutades - operaatorit. See annab meile {0; 0; 1; 1; 0; 1; 0; 1}.
Lõpuks tõlgitakse valem järgmiselt = SUMPRODUCT (B2: B9, {1; 1; 0; 1; 0; 0; 1; 0}, {0; 0; 1; 1; 0; 1; 0; 1 }). Kus esimene massiiv on väärtused. Järgmine on sobitatud kuu ja kolmas aasta. Lõpuks saame oma väärtuste summa 2160.
Funktsiooni SUMIFS kasutamine kuu kaupa summeerimiseks
Üldine valem
= SUMIFS (summa_vahemik, kuupäeva_vahemik, ”> =” & alguskuupäev, kuupäeva_vahemik ”, <<” & EOMONTH (alguskuupäev, 0))
Siin,Summa_vahemik : See on vahemik, mille soovite kuude kaupa summeerida.
Kuupäevavahemik : See on kuupäevavahemik, mida vaatate mitu kuud.
Algus kuupäev : See on alguskuupäev, millest soovite summeerida. Selle näite puhul on see antud kuu 1. kuupäev.
Näide: Summa väärtused kuude kaupa Excelis
Siin on meil kuupäevadega seotud väärtus. Need kuupäevad on aasta jaanuar, veebruar ja märts 2019.
Peame need väärtused lihtsalt kuu jooksul kokku võtma. Nüüd oli lihtne, kui meil olid kuud ja aastad eraldi. Aga nad ei ole. Me ei saa siin kasutada ühtegi abiveergu.
Seega olen aruande koostamiseks koostanud aruande vormi, mis sisaldab kuud ja väärtuste summat. Kuu veerus on mul tegelikult kuu alguskuupäev. Kuu nägemiseks valige alguskuupäev ja vajutage klahvikombinatsiooni CTRL+1.
Kohandatud vormingus kirjutage “mmm”.
Nüüd on meil andmed valmis. Võtame väärtused kokku kuude kaupa.
Kirjutage see valem kuvasse E3.
= SUMIFS (B3: B10, A3: A10, "> =" & D3, A3: A10, "<=" & EOMONTH (D3,0))
Enne valemi kopeerimist kasutage absoluutviiteid või nimevahemikke.
Niisiis, lõpuks saime tulemuse.
Niisiis, kuidas see toimib?
Nagu me teame, et funktsioon SUMIFS võib liita väärtused mitme kriteeriumi alusel.
Ülaltoodud näites on esimeseks kriteeriumiks kõigi B3: B10 väärtuste summa, kus kuupäev A3 -s: A10 on suurem või võrdne kuupäevaga D3. D3 sisaldab 1. jaan. See tõlgib ka.
= SUMIFS (B3: B10, A3: A10, "> =" & "1-jan-2019", A3: A10, "<=" EOMONTH (D3,0))
Järgmised kriteeriumid on summa ainult siis, kui kuupäev on A3: A10 on väiksem või võrdne EOMONTH (D3,0). Funktsioon EOMONTH tagastab lihtsalt esitatud kuu viimase kuupäeva seerianumbri. Lõpuks tõlgib ka valem.
= SUMIFS (B3: B10, A3: A10, "> = 1-jaanuar-2019", A3: A10, "<= 31-jaan-2019")
Seega saame Excelis summa kuude kaupa.
Selle meetodi eeliseks on see, et saate kohandada väärtuste summeerimise alguskuupäeva.
Kui teie kuupäevadel on erinevad aastad, on parem kasutada pöördtabeleid. Pivot -tabelid aitavad teil hõlpsasti eraldada andmeid aasta-, kvartali- ja igakuises vormingus.
Nii et jah, poisid, nii saate väärtusi kuude kaupa kokku võtta. Mõlemal viisil on oma eripärad. Valige, milline viis teile meeldib.
Kui teil on selle artikli või mõne muu Exceli ja VBA -ga seotud päringu kohta küsimusi, on kommentaaride jaotis teile avatud.
Seotud artiklid:
Funktsiooni SUMIF kasutamine Excelis
SUMIFS koos kuupäevadega Excelis
SUMIF tühjade lahtritega
Funktsiooni SUMIFS kasutamine Excelis
SUMIFS kasutades JA-VÕI loogikat
Populaarsed artiklid
50 Exceli otsetee tootlikkuse suurendamiseks: Saate oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada tta funktsioon VLOOKUP Excelis: See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni COUNTIF kasutamine Excelis: Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis: See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.