Üldine valem
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Kokkuvõte
Kuu kaupa kokku võttes saate funktsiooni EOMONTH abiga kasutada funktsiooni SUMIFS põhinevat valemit. Näidatud näites on valem F4-s järgmine:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Selles valemis kasutatakse nimetatud vahemikke "summa" (C4: C9) ja "kuupäev" (B4: B9).
Selgitus
Funktsioon SUMIFS võib summeerida väärtused vahemikus mitme kriteeriumi alusel.
Sel juhul konfigureerime SUMIFS summade summeerimiseks kuude kaupa, kasutades kahte kriteeriumit: üks kuupäevade vastendamiseks, mis on suuremad või võrdsed kuu esimese päevaga, teine kuupäevad, mis on väiksemad või võrdsed kuu viimase päevaga. Teisisõnu, vajame sellist valemit:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Või kuulikindlam alternatiiv:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
See versioon on kindlam, kuna kuupäevad on koostatud eraldi aasta, kuu ja päeva väärtustega ning neid ei sisestata tekstina, mida Excel peab tõlgendama.
Tavaliselt on see piin, sest kui lisate veergu E tekstina kuunimed (nt "jaanuar", "veebruar", "märts" jne), peate kriteeriumide jaoks kasutatavate kuupäevade loomiseks lisaprobleeme nägema . Kuid sel juhul kasutame asjade lihtsustamiseks lihtsat nippi: veergu E lisame kuude nimede kirjutamise asemel tegelikud kuupäevad (1.1.2016, 1.2.2016, 03.01.2016), seejärel kasutage kuunimede kuvamiseks kohandatud kuupäevavormingut ("mmmm").
See muudab SUMIFide jaoks vajalike kriteeriumide koostamise lihtsaks. Kuude esimesest suuremate või võrdsete kuupäevade sobitamiseks kasutame järgmist:
">="&E4
Kuupäeva väiksema või võrdse kuupäeva sobitamiseks kasutame järgmist:
"<="&EOMONTH(E4,0)
Kui kuude jooksul on null, tagastab funktsioon EOMONTH automaatselt kuupäevaga antud kuu viimase päeva.
Liitmine tärniga (&) on vajalik kriteeriumide koostamisel, mis kasutavad arvväärtusega loogilist operaatorit.
Pöördlaua lahendus
Pöördtabel on suurepärane lahendus, kui peate andmed kokku võtma aasta, kuu, kvartali jne järgi, sest nad teevad teie jaoks sellise rühmitamise ilma valemiteta. Valemite ja pöördtabelite kõrvuti võrdlemiseks vaadake seda videot: Miks pöördtabeleid.
