Generisk formel
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Sammendrag
Til gjennomsnitt etter måned kan du bruke en formel basert på AVERAGEIFS-funksjonen, med hjelp fra EOMONTH-funksjonen. I eksemplet vist er formelen i F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Denne formelen bruker de nevnte områdene "mengder" (D5: D104) og "datoer" (C5: C104).
Forklaring
AVERAGEIFS-funksjonen kan gjennomsnittsområder basert på flere kriterier. I dette tilfellet konfigurerer vi GJENNOMSNITT til gjennomsnittlige beløp per måned ved hjelp av to kriterier: (1) kampdatoer større enn eller lik den første dagen i måneden, (2) kampdatoer mindre enn eller lik den siste dagen i måneden. Hvis vi hardkodede datoer for januar 2016 i formelen ved hjelp av DATE-funksjonen, vil det se slik ut.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Men vi vil ikke hardkode datoer, vi vil at Excel skal generere disse datoene for oss. Normalt er dette en smerte, for hvis du legger til månedsnavn som tekst (dvs. "januar", "februar", "mars" osv.) I kolonne F, må du gjøre ekstra problemer med å lage datoer du kan bruke til kriterier .
I dette tilfellet bruker vi imidlertid et enkelt triks for å gjøre ting lettere: I kolonne F legger vi til faktiske datoer for den første i hver måned (i stedet for å skrive inn månedsnavn) (1/1/2016, 2/1/2016, 3 / 1/2016 osv.), Og bruk et tilpasset datoformat ("mmm") for å vise månedens navn.
Dette gjør det enkelt å lage kriteriene vi trenger for AVERAGEIFS. For å matche datoer som er større enn eller lik den første i måneden, bruker vi:
">="&E4
Og for å matche datoer som er mindre enn eller lik den siste dagen i måneden, bruker vi:
"<="&EOMONTH(E4,0)
EOMONTH returnerer automatisk den siste dagen i samme måned fordi vi oppgir null for månedersargumentet.
Merk: sammenkobling med et ampersand (&) er nødvendig når du bygger kriterier basert på en cellehenvisning.
Pivot Table-løsning
En pivottabell er en utmerket løsning når du trenger å oppsummere data etter år, måned, kvartal og så videre, fordi pivottabeller gir kontroller for automatisk gruppering etter dato. For en sammenligning side om side av formler mot pivottabeller, se denne videoen: Hvorfor pivottabeller.
