Splošna formula
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Povzetek
Če želite povprečni odzivni čas po mesecih uporabiti s formulo, ki temelji na funkciji AVERAGEIFS, skupaj s funkcijo EOMONTH.
V prikazanem primeru je formula v G5:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Pojasnilo
Ta formula uporablja imenovana obsega "datumi" (B5: B25) in "trajanja" (D5: D25). Trajanje stolpca D je v minutah, izračunano tako, da se odprt datum odšteje od zaprtega datuma.
Funkcija AVERAGEIFS je zasnovana tako, da povpreči obsege na podlagi več meril. V tem primeru konfiguriramo AVERAGEIFS na povprečno trajanje po mesecih z uporabo dveh meril: (1) ujemajoči se datumi, večji ali enaki prvemu dnevu v mesecu, (2) ujemajoči se datumi, krajši ali enaki zadnjemu dnevu v mesecu.
Za razvrščanje datumov po mesecih uporabimo preprost trik, s katerim olajšamo stvari: v stolpec F namesto vnosa imen mesecev ("Jan", "Feb", Mar "itd.) Dodamo dejanske datume za prvo od vsak mesec (1.1.2016, 1.2.2016, 3.1.2016 itd.),. Nato za prikaz mesecev uporabimo obliko datuma po meri ("mmm").
Tako je veliko lažje zgraditi merila, ki jih potrebujemo za AVERAGEIFS, z uporabo vrednosti v stolpcu F. Za ujemanje datumov, večjih ali enakih prvemu v mesecu, uporabimo:
">="&F5
Za ujemanje datumov, krajših ali enakih zadnjemu dnevu v mesecu, uporabimo:
"<="&EOMONTH(F5,0)
Dobili smo EOMONTH, da vrne zadnji dan istega meseca, tako da za argument mesecev navede 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 povzeti ali povprečiti podatke po letu, mesecu, četrtletju itd., Saj vrtilne tabele omogočajo samodejno združevanje datumov. Za vzporedno primerjavo formul in vrtilnih tabel si oglejte ta video: Zakaj vrtilne tabele.
