Generička formula
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Sažetak
Za prosječno izračunavanje po mjesecima možete koristiti formulu koja se temelji na funkciji AVERAGEIFS, uz pomoć funkcije EOMONTH. U prikazanom primjeru formula u F4 je:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Ova se formula koristi imenovanim rasponima "iznosi" (D5: D104) i "datumi" (C5: C104).
Obrazloženje
Funkcija AVERAGEIFS može prosječno kretati raspone na temelju više kriterija. U ovom slučaju konfiguriramo AVERAGEIFS za prosječne iznose po mjesecima koristeći dva kriterija: (1) datumi podudaranja veći ili jednaki prvom danu u mjesecu, (2) datumi podudaranja manji ili jednaki posljednjem danu u mjesecu. Ako čvrsto formuliramo datume za siječanj 2016. u formulu pomoću funkcije DATE, to bi izgledalo ovako.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Ali ne želimo tvrdo kodirati datume, već želimo da ih Excel generira za nas. To je obično muka, jer ako nazive mjeseci dodate kao tekst (npr. "Siječanj", "Veljača", "Ožujak", itd.) U stupac F, morate se dodatno pozabaviti stvaranjem datuma koje možete koristiti za kriterije .
Međutim, u ovom slučaju koristimo jednostavan trik kako bismo stvari olakšali: u stupac F, umjesto da upisujemo nazive mjeseci, dodajemo stvarne datume za prvi u svakom mjesecu (1/1/2016, 1/2016, 3 / 1/2016, itd.) I koristite prilagođeni format datuma ("mmm") za prikaz naziva mjeseca.
To olakšava izgradnju kriterija koji su nam potrebni za PROSJEČNE PROIZVODE. Za podudaranje datuma većih ili jednakih prvom u mjesecu koristimo:
">="&E4
A da bismo podudarali datume manje ili jednake posljednjem danu u mjesecu, koristimo:
"<="&EOMONTH(E4,0)
EOMONTH automatski vraća zadnji dan istog mjeseca jer za argument mjeseci dostavljamo nulu.
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 podatke po godini, mjesecu, tromjesečju itd., Jer pivot tablice pružaju kontrole za automatsko grupiranje po datumu. Za usporednu usporedbu formula i zaokretnih tablica pogledajte ovaj videozapis: Zašto zaokretne tablice.
