Resumé
For at beregne et glidende eller rullende gennemsnit kan du bruge en simpel formel baseret på funktionen GENNEMSNIT med relative referencer. I det viste eksempel er formlen i E7:
=AVERAGE(C5:C7)
Da formlen kopieres ned, beregner den et 3-dages glidende gennemsnit baseret på salgsværdien for den aktuelle dag og de to foregående dage.
Nedenfor er en mere fleksibel mulighed baseret på OFFSET-funktionen, der håndterer variable perioder.
Om glidende gennemsnit
Et glidende gennemsnit (også kaldet et rullende gennemsnit) er et gennemsnit baseret på delmængder af data med givne intervaller. Beregning af et gennemsnit med specifikke intervaller udjævner dataene ved at reducere virkningen af tilfældige udsving. Dette gør det lettere at se overordnede tendenser, især i et diagram. Jo større interval, der bruges til at beregne et glidende gennemsnit, jo mere udjævning sker der, da flere datapunkter er inkluderet i hvert beregnede gennemsnit.
Forklaring
Formlerne vist i eksemplet bruger alle AVERAGE-funktionen med en relativ reference opsat for hvert specifikt interval. Det 3-dages glidende gennemsnit i E7 beregnes ved at fodre GENNEMSNITT et interval, der inkluderer den aktuelle dag og de to foregående dage som denne:
=AVERAGE(C5:C7) // 3-day average
5-dages og 7-dages gennemsnit beregnes på samme måde. I begge tilfælde forstørres det interval, der leveres til GENNEMSNIT til at omfatte det krævede antal dage:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Alle formler bruger en relativ reference for det interval, der leveres til AVERAGE-funktionen. Når formlerne kopieres ned i kolonnen, ændres rækkevidden i hver række for at inkludere de nødvendige værdier for hvert gennemsnit.
Når værdierne er plottet i et linjediagram, er udjævningseffekten klar:
Utilstrækkelige data
Hvis du starter formlerne i den første række i tabellen, har de første par formler ikke nok data til at beregne et komplet gennemsnit, fordi området vil strække sig over den første række med data:
Dette kan måske ikke være et problem afhængigt af regnearkets struktur, og om det er vigtigt, at alle gennemsnit er baseret på det samme antal værdier. AVERAGE-funktionen ignorerer automatisk tekstværdier og tomme celler, så den fortsætter med at beregne et gennemsnit med færre værdier. Det er derfor, det "fungerer" i E5 og E6.
En måde at tydeligt angive utilstrækkelige data er at kontrollere det aktuelle række nummer og afbryde med #NA, når der er mindre end n værdier. For eksempel til 3-dages gennemsnittet kan du bruge:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Den første del af formlen genererer simpelthen et "normaliseret" række nummer, der starter med 1:
ROW()-ROW($C$5)+1 // relative row number
I række 5 er resultatet 1, i række 6 er resultatet 2 osv.
Når det aktuelle række nummer er mindre end 3, returnerer formlen # N / A. Ellers returnerer formlen et glidende gennemsnit som før. Dette efterligner opførelsen af Analysis Toolpak-versionen af glidende gennemsnit, som udsender # N / A, indtil den første komplette periode er nået.
Efterhånden som antallet af perioder øges, løber du til sidst ud af rækker over dataene og vil ikke være i stand til at indtaste det krævede interval inden for GENNEMSNIT. For eksempel kan du ikke oprette et glidende 7-dages gennemsnit med regnearket som vist, da du ikke kan indtaste et interval, der strækker sig 6 rækker over C5.
Variable perioder med OFFSET
En mere fleksibel måde at beregne et glidende gennemsnit på er med OFFSET-funktionen. OFFSET kan skabe et dynamisk område, hvilket betyder, at vi kan oprette en formel, hvor antallet af perioder er variabelt. Den generelle form er:
=AVERAGE(OFFSET(A1,0,0,-n,1))
hvor n er antallet af perioder, der skal medtages i hvert gennemsnit. Som ovenfor returnerer OFFSET et interval, der overføres til FUNKTION. Nedenfor kan du se denne formel i aktion, hvor "n" er det navngivne område E2. Fra celle C5 konstruerer OFFSET et interval, der strækker sig tilbage til tidligere rækker. Dette opnås ved at bruge en højde svarende til negativ n. Når E5 ændres til et andet tal, genberegner det glidende gennemsnit på alle rækker:
Formlen i E5, kopieret ned, er:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Ligesom den oprindelige formel ovenfor vil versionen med OFFSET også have problemet med utilstrækkelige data i de første par rækker, afhængigt af hvor mange perioder der er angivet i E5.
I det viste eksempel beregner gennemsnittene med succes, fordi AVERAGE-funktionen automatisk ignorerer tekstværdier og tomme celler, og der er ingen andre numeriske værdier over C5. Så mens området, der sendes til GENNEMSNIT i E5, er C1: C5, er der kun en værdi til gennemsnittet, 100. Når perioderne stiger, vil OFFSET imidlertid fortsætte med at oprette et interval, der strækker sig over datastart, og til sidst løber ind i øverst på regnearket og returnerer en #REF-fejl.
En løsning er at "dække" størrelsen af området til det antal tilgængelige datapunkter. Dette kan gøres ved at bruge MIN-funktionen til at begrænse antallet, der bruges til højden, som vist nedenfor:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Dette ser ret skræmmende ud, men er faktisk ret simpelt. Vi begrænser højden, der sendes til OFFSET med MIN-funktionen:
MIN(ROW()-ROW($C$5)+1,n)
Inde i MIN er den første værdi et relativt række nummer, beregnet med:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Den anden værdi, der gives MIN, er antallet af perioder, n. Når det relative række nummer er mindre end n, returnerer MIN det aktuelle række nummer til OFFSET for højde. Når række nummer er større end n, returnerer MIN n. Med andre ord returnerer MIN simpelthen den mindste af de to værdier.
En god egenskab ved OFFSET-indstillingen er, at n let kan ændres. Hvis vi ændrer n til 7 og plotter resultaterne, får vi et diagram som dette:
Bemærk: En finurlighed med OFFSET-formlerne ovenfor er, at de ikke fungerer i Google Sheets, fordi OFFSET-funktionen i Sheets ikke tillader en negativ værdi for højde eller bredde. Det vedhæftede regneark har midlertidige formler til Google-ark.
