Generisk formel
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Resumé
Til gennemsnit efter måned kan du bruge en formel baseret på AVERAGEIFS-funktionen med hjælp fra EOMONTH-funktionen. I det viste eksempel er formlen i F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Denne formel bruger de navngivne områder "beløb" (D5: D104) og "datoer" (C5: C104).
Forklaring
AVERAGEIFS-funktionen kan gennemsnitlige intervaller baseret på flere kriterier. I dette tilfælde konfigurerer vi AVERAGEIFS til gennemsnitlige beløb pr. Måned ved hjælp af to kriterier: (1) matchdatoer større end eller lig med den første dag i måneden, (2) matchdatoer mindre end eller lig med den sidste dag i måneden. Hvis vi hårdkodede datoer for januar 2016 i formlen ved hjælp af DATE-funktionen, ville det se sådan ud.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Men vi ønsker ikke at hårdkode datoer, vi vil have, at Excel genererer disse datoer for os. Normalt er dette smertefuldt, for hvis du tilføjer månedsnavne som tekst (dvs. "januar", "februar", "marts" osv.) I kolonne F, skal du gøre ekstra problemer med at oprette datoer, du kan bruge til kriterier .
I dette tilfælde bruger vi imidlertid et simpelt trick for at gøre tingene lettere: I kolonne F tilføjer vi faktiske datoer for den første i hver måned i stedet for at indtaste månedsnavne (1/1/2016, 2/1/2016, 3 / 1/2016 osv.), Og brug et brugerdefineret datoformat ("mmm") til at vise månedens navne.
Dette gør det nemt at opbygge de kriterier, vi har brug for til AVERAGEIFS. For at matche datoer, der er større end eller lig med den første i måneden, bruger vi:
">="&E4
Og for at matche datoer, der er mindre end eller lig med den sidste dag i måneden, bruger vi:
"<="&EOMONTH(E4,0)
EOMONTH returnerer automatisk den sidste dag i den samme måned, fordi vi angiver nul for argumentet måneder.
Bemærk: sammenkædning med et ampersand (&) er nødvendig, når man bygger kriterier baseret på en cellehenvisning.
Pivot Table-løsning
En pivottabel er en glimrende løsning, når du har brug for at opsummere data efter år, måned, kvartal osv., Fordi pivottabeller giver kontrol til automatisk gruppering efter dato. For en side-om-side sammenligning af formler versus pivottabeller, se denne video: Hvorfor pivottabeller.
