Generisk formel
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Resumé
For at gennemsnitlige svartider pr. Måned kan du bruge en formel baseret på AVERAGEIFS-funktionen sammen med EOMONTH-funktionen.
I det viste eksempel er formlen i G5:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Forklaring
Denne formel bruger de navngivne intervaller "datoer" (B5: B25) og "varighed" (D5: D25). Varighedskolonne D er i minutter beregnet ved at fratrække den åbnede dato fra den lukkede dato.
AVERAGEIFS-funktionen er designet til gennemsnitlige intervaller baseret på flere kriterier. I dette tilfælde konfigurerer vi AVERAGEIFS til gennemsnitlige varigheder pr. Måned ved hjælp af to kriterier: (1) matchende datoer større end eller lig med den første dag i måneden, (2) matching datoer mindre end eller lig den sidste dag i måneden.
For at parentesere datoer efter måned bruger vi et simpelt trick for at gøre tingene lettere: I kolonne F tilføjer vi faktiske datoer for den første af i stedet for at skrive månedsnavne ("Jan", "Feb", Mar "osv.) hver måned (1/1/2016, 2/1/2016, 3/1/2016 osv.). Derefter bruger vi det brugerdefinerede datoformat ("mmm") til at vise månedens navne.
Dette gør det meget nemmere at opbygge de kriterier, vi har brug for til AVERAGEIFS ved hjælp af værdier i kolonne F. For at matche datoer, der er større end eller lig med den første i måneden, bruger vi:
">="&F5
For at matche datoer, der er mindre end eller lig med den sidste dag i måneden, bruger vi:
"<="&EOMONTH(F5,0)
Vi får EOMONTH til at returnere den sidste dag i den samme måned ved at angive nul for måneders argumentet.
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 fremragende løsning, når du har brug for at opsummere eller gennemsnitlige data efter år, måned, kvartal osv., Fordi pivottabeller automatisk giver kontrol til gruppering af datoer. For en side-om-side sammenligning af formler versus pivottabeller, se denne video: Hvorfor pivottabeller.
