Generisk formel
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Sammanfattning
I genomsnitt per månad kan du använda en formel baserad på AVERAGEIFS-funktionen med hjälp av EOMONTH-funktionen. I exemplet som visas är formeln i F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Denna formel använder de angivna intervallen "belopp" (D5: D104) och "datum" (C5: C104).
Förklaring
AVERAGEIFS-funktionen kan genomsnittliga intervall baserat på flera kriterier. I det här fallet konfigurerar vi AVERAGEIFS till genomsnittliga belopp per månad med två kriterier: (1) matchdatum som är större än eller lika med den första dagen i månaden, (2) matchdatum som är mindre än eller lika med den sista dagen i månaden. Om vi hårdkodade datum för januari 2016 i formeln med DATE-funktionen skulle det se ut så här.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Men vi vill inte hårdkoda datum, vi vill att Excel ska generera dessa datum för oss. Normalt är detta en smärta, för om du lägger till månadsnamn som text (t.ex. "januari", "februari", "mars" osv.) I kolumn F måste du göra extra problem för att skapa datum som du kan använda för kriterier .
I det här fallet använder vi dock ett enkelt knep för att göra det enklare: I kolumn F lägger vi till faktiska datum för den första i varje månad i stället för att skriva månadens namn (2016-01-01, 2016-01-01, 3 / 1/2016, etc.) och använd ett anpassat datumformat ("mmm") för att visa månadsnamnen.
Detta gör det enkelt att bygga de kriterier vi behöver för AVERAGEIFS. För att matcha datum som är större än eller lika med den första i månaden använder vi:
">="&E4
Och för att matcha datum som är mindre än eller lika med den sista dagen i månaden använder vi:
"<="&EOMONTH(E4,0)
EOMONTH returnerar automatiskt den sista dagen i samma månad eftersom vi anger noll för månaderargumentet.
Obs! Sammankoppling med ett ampersand (&) är nödvändig när man bygger kriterier baserat på en cellreferens.
Pivot Table-lösning
En pivottabell är en utmärkt lösning när du behöver sammanfatta data efter år, månad, kvartal och så vidare, eftersom pivottabeller ger kontroller för automatisk gruppering efter datum. För en jämförelse sida vid sida av formler mot pivottabeller, se den här videon: Varför pivottabeller.
