Formula generică
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
rezumat
Pentru a efectua o medie pe lună, puteți utiliza o formulă bazată pe funcția AVERAGEIFS, cu ajutorul funcției EOMONTH. În exemplul prezentat, formula din F4 este:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Această formulă utilizează intervalele denumite „cantități” (D5: D104) și „date” (C5: C104).
Explicaţie
Funcția AVERAGEIFS poate medie intervale pe baza mai multor criterii. În acest caz, configurăm AVERAGEIFS la valori medii pe lună folosind două criterii: (1) date de potrivire mai mari sau egale cu prima zi a lunii, (2) date de potrivire mai mici sau egale cu ultima zi a lunii. Dacă am codifica datele date pentru ianuarie 2016 în formula folosind funcția DATE, ar arăta astfel.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Dar nu vrem să codificăm datele, ci vrem ca Excel să genereze aceste date pentru noi. În mod normal, aceasta este o problemă, deoarece dacă adăugați nume de luni ca text (adică „ianuarie”, „februarie”, „martie” etc.) în coloana F trebuie să vă confruntați cu probleme suplimentare pentru a crea date pe care le puteți utiliza pentru criterii .
Cu toate acestea, în acest caz, folosim un truc simplu pentru a ușura lucrurile: în coloana F, în loc să tastați numele lunilor, adăugăm date efective pentru prima din fiecare lună (01.01.2016, 01.02.2016, 3 / 1/2016 etc.) și utilizați un format de dată personalizat („mmm”) pentru a afișa numele lunii.
Acest lucru facilitează construirea criteriilor de care avem nevoie pentru AVERAGEIFS. Pentru a potrivi date mai mari sau egale cu prima din lună, folosim:
">="&E4
Și pentru a potrivi date mai mici sau egale cu ultima zi a lunii, folosim:
"<="&EOMONTH(E4,0)
EOMONTH returnează automat ultima zi a aceleiași luni, deoarece furnizăm zero pentru argumentul luni.
Notă: concatenarea cu un ampersand (&) este necesară atunci când se creează criterii bazate pe o referință de celulă.
Soluție pentru tabelul pivot
Un tabel pivot este o soluție excelentă atunci când trebuie să rezumați datele în funcție de an, lună, trimestru și așa mai departe, deoarece tabelele pivot oferă controale pentru gruparea automată după dată. Pentru o comparație side-by-side a formulelor față de tabelele pivot, consultați acest videoclip: De ce tabelele pivot.
