Generička formula
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Sažetak
Za prosječno vrijeme odgovora po mjesecu možete upotrijebiti formulu koja se temelji na funkciji AVERAGEIFS, zajedno s funkcijom EOMONTH.
U prikazanom primjeru formula u G5 je:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Obrazloženje
Ova formula koristi imenovane raspone "datumi" (B5: B25) i "trajanja" (D5: D25). Trajanje stupca D izraženo je u minutama, izračunato oduzimanjem datuma otvorenog od zatvorenog datuma.
Funkcija AVERAGEIFS dizajnirana je za prosječno kretanje raspona na temelju više kriterija. U ovom slučaju, AVERAGEIFS konfiguriramo na prosječna trajanja po mjesecima koristeći dva kriterija: (1) podudaranje datuma većih ili jednakih prvom danu u mjesecu, (2) podudaranje datuma kraćih ili jednakih posljednjem danu u mjesecu.
Da bismo datume zacrtali po mjesecima, koristimo jednostavan trik kako bismo stvari olakšali: U stupac F, umjesto da upisujemo nazive mjeseci ("Jan", "Feb", Mar "itd.), Dodamo stvarne datume za prvi od svakog mjeseca (1/1/2016, 2/1/2016, 3/1/2016, itd.),. Zatim koristimo prilagođeni format datuma ("mmm") za prikaz naziva mjeseca.
To puno olakšava izgradnju kriterija koji su nam potrebni za AVERAGEIFS pomoću vrijednosti u stupcu F. Da bismo podudarali datume veće ili jednake prvom u mjesecu, koristimo:
">="&F5
Da bismo podudarali datume manje ili jednake posljednjem danu u mjesecu, koristimo:
"<="&EOMONTH(F5,0)
Dobivamo EOMONTH da vrati zadnji dan istog mjeseca davanjem nule za argument mjeseci .
Napomena: spajanje s ampersandom (&) potrebno je prilikom izrade kriterija na temelju reference stanice.
Rješenje zaokretne tablice
Zaokretna tablica izvrsno je rješenje kada trebate sažeti ili prosječno izračunati podatke po godini, mjesecu, tromjesečju i tako dalje, jer pivot tablice pružaju kontrole za automatsko grupiranje datuma. Za usporednu usporedbu formula i zaokretnih tablica pogledajte ovaj videozapis: Zašto zaokretne tablice.
