Generisk formel
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Sammendrag
For å gjennomsnittlig responstid per måned, kan du bruke en formel basert på AVERAGEIFS-funksjonen, sammen med EOMONTH-funksjonen.
I eksemplet vist er formelen i G5:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Forklaring
Denne formelen bruker de nevnte områdene "datoer" (B5: B25) og "varighet" (D5: D25). Varighetskolonne D er i minutter, beregnet ved å trekke datoen åpnet fra stengt dato.
AVERAGEIFS-funksjonen er utformet for å gjennomsnittsområder basert på flere kriterier. I dette tilfellet konfigurerer vi GJENNOMSNITT til gjennomsnittlig varighet etter måned ved å bruke to kriterier: (1) samsvarende datoer som er større enn eller lik den første dagen i måneden, (2) samsvarende datoer mindre enn eller lik den siste dagen i måneden.
For å sette datoene etter måned, bruker vi et enkelt triks for å gjøre ting enklere: I kolonne F legger vi til faktiske datoer for den første av kolonnen F, i stedet for å skrive månedsnavn ("Jan", "Feb", Mar "osv.). hver måned (1/1/2016, 2/1/2016, 3/1/2016 osv.). Deretter bruker vi det tilpassede datoformatet ("mmm") for å vise månedens navn.
Dette gjør det mye lettere å bygge kriteriene vi trenger for GJENNOMSNITT ved å bruke verdier i kolonne F. For å matche datoer som er større enn eller lik den første i måneden, bruker vi:
">="&F5
For å matche datoer som er mindre enn eller lik den siste dagen i måneden, bruker vi:
"<="&EOMONTH(F5,0)
Vi får EOMONTH til å returnere den siste dagen i samme måned ved å oppgi null for måneders argument.
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 eller gjennomsnittsdata etter år, måned, kvartal og så videre, fordi pivottabeller gir kontroller for gruppering av datoer automatisk. For en sammenligning side om side av formler mot pivottabeller, se denne videoen: Hvorfor pivottabeller.
