Sammendrag
For å beregne et glidende eller rullende gjennomsnitt kan du bruke en enkel formel basert på AVERAGE-funksjonen med relative referanser. I eksemplet vist er formelen i E7:
=AVERAGE(C5:C7)
Når formelen kopieres ned, beregner den et 3-dagers glidende gjennomsnitt basert på salgsverdien for dagens og de to foregående dagene.
Nedenfor er et mer fleksibelt alternativ basert på OFFSET-funksjonen som håndterer variable perioder.
Om glidende gjennomsnitt
Et glidende gjennomsnitt (også kalt rullende gjennomsnitt) er et gjennomsnitt basert på delmengder av data med gitte intervaller. Å beregne et gjennomsnitt med spesifikke intervaller glatter ut dataene ved å redusere virkningen av tilfeldige svingninger. Dette gjør det lettere å se generelle trender, spesielt i et diagram. Jo større intervall som brukes for å beregne et glidende gjennomsnitt, jo mer utjevning oppstår, siden flere datapunkter er inkludert i hvert beregnede gjennomsnitt.
Forklaring
Formlene vist i eksemplet bruker alle AVERAGE-funksjonen med en relativ referanse satt opp for hvert spesifikt intervall. Det 3-dagers glidende gjennomsnittet i E7 beregnes ved å mate GJENNOMSNITT et område som inkluderer gjeldende dag og de to foregående dagene slik:
=AVERAGE(C5:C7) // 3-day average
Gjennomsnittet for 5 og 7 dager beregnes på samme måte. I begge tilfeller utvides rekkevidden til GJENNOMSNITT til å omfatte det nødvendige antall dager:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Alle formler bruker en relativ referanse for området som leveres til AVERAGE-funksjonen. Når formlene kopieres nedover i kolonnen, endres området på hver rad for å inkludere verdiene som trengs for hvert gjennomsnitt.
Når verdiene er tegnet i et linjediagram, er utjevningseffekten tydelig:
Utilstrekkelig data
Hvis du starter formlene i den første raden i tabellen, vil de første par formlene ikke ha nok data til å beregne et komplett gjennomsnitt, fordi området vil strekke seg over den første dataraden:
Dette kan være et problem, avhengig av strukturen til regnearket, og om det er viktig at alle gjennomsnitt er basert på samme antall verdier. AVERAGE-funksjonen vil automatisk ignorere tekstverdier og tomme celler, så den vil fortsette å beregne et gjennomsnitt med færre verdier. Dette er grunnen til at det "fungerer" i E5 og E6.
En måte å tydelig indikere utilstrekkelig data er å kontrollere gjeldende radnummer og avbryte med #NA når det er mindre enn n verdier. For eksempel for 3-dagers gjennomsnittet kan du bruke:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Den første delen av formelen genererer ganske enkelt et "normalisert" radnummer, som begynner med 1:
ROW()-ROW($C$5)+1 // relative row number
I rad 5 er resultatet 1, i rad 6 er resultatet 2, og så videre.
Når det nåværende radnummeret er mindre enn 3, returnerer formelen # N / A. Ellers returnerer formelen et glidende gjennomsnitt som før. Dette etterligner oppførselen til Analysis Toolpak-versjonen av Moving Average, som sender ut # N / A til den første hele perioden er nådd.
Imidlertid, når antall perioder øker, vil du til slutt løpe ut av rader over dataene, og du vil ikke være i stand til å angi ønsket område i GJENNOMSNITT. For eksempel kan du ikke sette opp et glidende 7-dagers gjennomsnitt med regnearket som vist, siden du ikke kan angi et område som strekker seg 6 rader over C5.
Variable perioder med OFFSET
En mer fleksibel måte å beregne et glidende gjennomsnitt på er med OFFSET-funksjonen. OFFSET kan skape et dynamisk område, noe som betyr at vi kan sette opp en formel der antall perioder er variabelt. Den generelle formen er:
=AVERAGE(OFFSET(A1,0,0,-n,1))
hvor n er antall perioder som skal inkluderes i hvert gjennomsnitt. Som ovenfor returnerer OFFSET et område som sendes til AVERAGE-funksjonen. Nedenfor kan du se denne formelen i aksjon, hvor "n" er det navngitte området E2. Fra og med celle C5 konstruerer OFFSET et område som strekker seg tilbake til forrige rad. Dette oppnås ved å bruke en høyde lik negativ n. Når E5 endres til et annet tall, beregner det glidende gjennomsnittet på nytt på alle radene:
Formelen i E5, kopiert ned, er:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Som den opprinnelige formelen ovenfor, vil versjonen med OFFSET også ha problemet med utilstrekkelig data i de første radene, avhengig av hvor mange perioder som er gitt i E5.
I eksemplet som vises, beregner gjennomsnittene vellykket fordi AVERAGE-funksjonen automatisk ignorerer tekstverdier og tomme celler, og det ikke er noen andre numeriske verdier over C5. Så mens rekkevidden som sendes til GJENNOMSNITT i E5 er C1: C5, er det bare en verdi til gjennomsnittet, 100. Imidlertid, når perioder øker, vil OFFSET fortsette å lage et område som strekker seg over datastarten, og til slutt løper inn i toppen av regnearket og returnerer en #REF-feil.
En løsning er å "cap" størrelsen på området til antall tilgjengelige datapunkter. Dette kan gjøres ved å bruke MIN-funksjonen for å begrense antallet som brukes for høyden, som vist nedenfor:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Dette ser ganske skummelt ut, men er faktisk ganske enkelt. Vi begrenser høyden som sendes til OFFSET med MIN-funksjonen:
MIN(ROW()-ROW($C$5)+1,n)
Inne i MIN er den første verdien et relativt radnummer, beregnet med:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Den andre verdien som er gitt til MIN er antall perioder, n. Når det relative radnummeret er mindre enn n, returnerer MIN gjeldende radnummer til OFFSET for høyde. Når radnummeret er større enn n, returnerer MIN n. Med andre ord returnerer MIN ganske enkelt den minste av de to verdiene.
En fin funksjon av OFFSET-alternativet er at n enkelt kan endres. Hvis vi endrer n til 7 og plotter resultatene, får vi et diagram som dette:
Merk: En særegenhet med OFFSET-formlene ovenfor er at de ikke fungerer i Google Sheets, fordi OFFSET-funksjonen i Sheets ikke tillater en negativ verdi for høyde eller bredde. Det vedlagte regnearket har midlertidige formler for Google-ark.
