Üldine valem
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Kokkuvõte
Kuu järgi keskmiseks saate funktsiooni AVALDAMINE abil valemi, mis põhineb funktsioonil AVERAGEIFS. Näidatud näites on valem F4-s järgmine:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Selles valemis kasutatakse nimetatud vahemikke "summad" (D5: D104) ja "kuupäevad" (C5: C104).
Selgitus
Funktsioon AVERAGEIFS võib keskmistada vahemikke mitme kriteeriumi alusel. Sellisel juhul konfigureerime AVERAGEIFS keskmisteks summadeks kuude kaupa, kasutades kahte kriteeriumi: (1) mängu kuupäevad, mis on suuremad või võrdsed kuu esimese päevaga, (2) sobivad kuupäevad, mis on väiksemad või võrdsed kuu viimase päevaga. Kui me 2016. aasta jaanuari kuupäevad valemiga DATE funktsiooni abil kodeeriksime, näeks see välja selline.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Kuid me ei taha kuupäevi kodeerida, vaid tahame, et Excel genereeriks meile need kuupäevad. Tavaliselt on see piin, sest kui lisate veergu F tekstina kuude nimed (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 F lisame selle asemel, et tippida kuude nimesid, iga kuu esimese kuupäeva tegelikud kuupäevad (1.1.2016, 1.2.2016, 3 / 1/2016 jne) ja kasutage kuude nimede kuvamiseks kohandatud kuupäevavormingut ("mmm").
See muudab keskmiste keskmiste 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)
EOMONTH tagastab automaatselt sama kuu viimase päeva, kuna me esitame kuude argumendi jaoks nulli.
Märkus: lahtriviidele tuginevate kriteeriumide koostamisel on vajalik liitmine ampersandiga (&).
Pöördlaua lahendus
Pöördtabel on suurepärane lahendus, kui peate andmed kokku võtma aasta, kuu, kvartali ja muu järgi, sest pöördtabelid pakuvad juhtnuppe automaatseks rühmitamiseks kuupäeva järgi. Valemite ja pöördtabelite kõrvuti võrdlemiseks vaadake seda videot: Miks pöördtabeleid.
