Sammanfattning
För att beräkna ett glidande eller rullande genomsnitt kan du använda en enkel formel baserad på AVERAGE-funktionen med relativa referenser. I exemplet som visas är formeln i E7:
=AVERAGE(C5:C7)
Eftersom formeln kopieras beräknar den ett 3-dagars glidande medelvärde baserat på försäljningsvärdet för den aktuella dagen och de två föregående dagarna.
Nedan följer ett mer flexibelt alternativ baserat på OFFSET-funktionen som hanterar variabla perioder.
Om glidande medelvärden
Ett glidande medelvärde (även kallat rullande medelvärde) är ett genomsnitt baserat på underuppsättningar av data med givna intervall. Att beräkna ett genomsnitt med specifika intervaller släpper ut data genom att minska effekten av slumpmässiga fluktuationer. Detta gör det lättare att se övergripande trender, särskilt i ett diagram. Ju större intervall som används för att beräkna ett glidande medel, desto mer utjämning uppstår, eftersom fler datapunkter ingår i varje beräknat genomsnitt.
Förklaring
Formlerna som visas i exemplet använder alla AVERAGE-funktionen med en relativ referensinställning för varje specifikt intervall. Det 3-dagars glidande medelvärdet i E7 beräknas genom att mata GENOMSNITT ett intervall som inkluderar den aktuella dagen och de två föregående dagarna så här:
=AVERAGE(C5:C7) // 3-day average
Genomsnittet för 5 och 7 dagar beräknas på samma sätt. I båda fallen förstoras intervallet som tillhandahålls till AVERAGE så att det inkluderar antalet dagar som krävs:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Alla formler använder en relativ referens för det intervall som levereras till AVERAGE-funktionen. När formlerna kopieras ner i kolumnen ändras intervallet vid varje rad för att inkludera de värden som behövs för varje genomsnitt.
När värdena plottas i ett linjediagram är utjämningseffekten tydlig:
Otillräcklig data
Om du startar formlerna i den första raden i tabellen har de första formlerna inte tillräckligt med data för att beräkna ett fullständigt genomsnitt, eftersom intervallet kommer att sträcka sig över den första raden med data:
Detta kanske eller inte kan vara ett problem, beroende på kalkylarkets struktur, och om det är viktigt att alla medelvärden baseras på samma antal värden. Funktionen AVERAGE ignorerar automatiskt textvärden och tomma celler, så den fortsätter att beräkna ett genomsnitt med färre värden. Det är därför det "fungerar" i E5 och E6.
Ett sätt att tydligt indikera otillräcklig data är att kontrollera det aktuella radnumret och avbryta med #NA när det finns mindre än n-värden. Till exempel, för 3-dagars genomsnittet kan du använda:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Den första delen av formeln genererar helt enkelt ett "normaliserat" radnummer, som börjar med 1:
ROW()-ROW($C$5)+1 // relative row number
I rad 5 är resultatet 1, i rad 6 är resultatet 2 och så vidare.
När det aktuella radnumret är mindre än 3 returnerar formeln # N / A. Annars returnerar formeln ett glidande medelvärde som tidigare. Detta efterliknar beteendet för analysverktygspaketversionen av glidande medelvärde, som matar ut # N / A tills den första hela perioden har uppnåtts.
Men när antalet perioder ökar kommer du så småningom att ta slut på rader ovanför data och du kommer inte att kunna ange önskat intervall inom GENOMSNITT. Du kan till exempel inte ställa in ett rörligt 7-dagars genomsnitt med kalkylbladet som visas, eftersom du inte kan ange ett intervall som sträcker sig 6 rader över C5.
Variabla perioder med OFFSET
Ett mer flexibelt sätt att beräkna ett glidande medelvärde är med OFFSET-funktionen. OFFSET kan skapa ett dynamiskt omfång, vilket innebär att vi kan ställa in en formel där antalet perioder är variabelt. Den allmänna formen är:
=AVERAGE(OFFSET(A1,0,0,-n,1))
där n är antalet perioder som ska inkluderas i varje genomsnitt. Som ovan returnerar OFFSET ett intervall som skickas till AVERAGE-funktionen. Nedan kan du se denna formel i aktion, där "n" är det namngivna intervallet E2. Från och med cellen C5 konstruerar OFFSET ett intervall som sträcker sig tillbaka till tidigare rader. Detta uppnås genom att använda en höjd som är lika med negativ n. När E5 ändras till ett annat nummer beräknas det glidande genomsnittet på alla rader:
Formeln i E5, kopierad ner, är:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Liksom den ursprungliga formeln ovan kommer versionen med OFFSET också att ha problem med otillräcklig data under de första raderna, beroende på hur många perioder som ges i E5.
I det visade exemplet beräknar medelvärdet framgångsrikt eftersom AVERAGE-funktionen automatiskt ignorerar textvärden och tomma celler, och det finns inga andra numeriska värden ovanför C5. Medan intervallet som överförs till GENOMSNITT i E5 är C1: C5, finns det bara ett värde till medelvärdet, 100. Men när perioderna ökar kommer OFFSET att fortsätta att skapa ett intervall som sträcker sig över datans början och så småningom högst upp i kalkylbladet och returnerar ett #REF-fel.
En lösning är att "begränsa" storleken på intervallet till antalet tillgängliga datapunkter. Detta kan göras genom att använda MIN-funktionen för att begränsa antalet som används för höjd enligt nedan:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Det här ser ganska läskigt ut, men är faktiskt ganska enkelt. Vi begränsar höjden som överförs till OFFSET med MIN-funktionen:
MIN(ROW()-ROW($C$5)+1,n)
Inuti MIN är det första värdet ett relativt radnummer, beräknat med:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Det andra värdet som ges till MIN är antalet perioder, n. När det relativa radnumret är mindre än n, returnerar MIN det aktuella radnumret till OFFSET för höjd. När radnumret är större än n returnerar MIN n. Med andra ord returnerar MIN helt enkelt det mindre av de två värdena.
En trevlig funktion i alternativet OFFSET är att n enkelt kan ändras. Om vi ändrar n till 7 och plottar resultaten får vi ett diagram som detta:
Obs! En egendom med OFFSET-formlerna ovan är att de inte fungerar i Google Sheets, eftersom OFFSET-funktionen i Sheets inte tillåter ett negativt värde för höjd eller bredd. Det bifogade kalkylarket har lösningsformler för Google-ark.
