Splošna formula
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Povzetek
Za povprečje po mesecih lahko s pomočjo funkcije EOMONTH uporabite formulo, ki temelji na funkciji AVERAGEIFS. V prikazanem primeru je formula v F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Ta formula uporablja imenovane obsege "zneski" (D5: D104) in "datumi" (C5: C104).
Pojasnilo
Funkcija AVERAGEIFS lahko v povprečju obsega na podlagi več meril. V tem primeru nastavimo AVERAGEIFS na povprečne zneske po mesecih z uporabo dveh kriterijev: (1) datumi ujemanja, ki so večji ali enaki prvemu dnevu v mesecu, (2) datumi ujemanja, krajši ali enaki zadnjemu dnevu v mesecu. Če bi v formulo s pomočjo funkcije DATE trdo kodirali datume za januar 2016, bi to izgledalo tako.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Vendar ne želimo trdo kodirati datumov, temveč želimo, da jih Excel ustvari za nas. Običajno je to bolečina, ker če v stolpec F dodate imena mesecev (npr. "Januar", "februar", "marec" itd.), Morate ustvariti datume, ki jih lahko uporabite za kriterije .
Vendar v tem primeru za preprostejše trike uporabimo preprost trik: v stolpec F namesto vnosa imen mesecev dodamo dejanske datume za prvi v vsakem mesecu (1. 1. 2016, 1. 2. 2016, 3 / 1/2016 itd.) In za prikaz mesecev uporabite obliko datuma po meri ("mmm").
Tako je enostavno zgraditi merila, ki jih potrebujemo za AVERAGEIFS. Za ujemanje datumov, večjih ali enakih prvemu v mesecu, uporabimo:
">="&E4
Za ujemanje datumov, krajših ali enakih zadnjemu dnevu v mesecu, uporabimo:
"<="&EOMONTH(E4,0)
EOMONTH samodejno vrne zadnji dan istega meseca, ker za argument mesecev damo nič.
Opomba: združevanje z znakom (&) je potrebno pri gradnji meril, ki temeljijo na referenci celice.
Rešitev vrtilne tabele
Vrtilna tabela je odlična rešitev, ko morate podatke povzeti po letu, mesecu, četrtletju itd., Saj vrtilne tabele omogočajo nadzor za samodejno razvrščanje po datumih. Za vzporedno primerjavo formul in vrtilnih tabel si oglejte ta video: Zakaj vrtilne tabele.
