Rörligt medelvärde betyder att vi beräknar medelvärdet av de datauppsättningar som vi har, i excel har vi en inbyggd funktion för beräkning av glidande medel som finns på fliken dataanalys i analysavsnittet, det tar ett inmatningsområde och utdata intervall med intervall som utdata, beräkningar baserade på enbart formler i excel för att beräkna glidande medel är svåra men vi har en inbyggd funktion i excel för att göra det.
Vad är glidande medelvärde i Excel
Rörligt genomsnitt är en mycket använd teknik i tidsserieanalys som används för att förutsäga framtiden. De glidande medelvärdena i en tidsserie konstrueras i princip genom att ta medelvärden för olika sekventiella värden för en annan tidsseriedata.
Det finns tre typer av glidande medelvärden, nämligen enkelt glidande medelvärde, viktat glidande medelvärde och exponentiellt glidande medelvärde i excel.
# 1 - Enkelt glidande medelvärde i Excel
Ett enkelt glidande medelvärde hjälper till att beräkna genomsnittet för det senaste antalet perioder i en dataserie. Antag att priserna på n period anges. Då ges det enkla glidande genomsnittet som
Enkelt glidande medelvärde = (P1 + P2 + …. + Pn) / n
# 2 - Vägt glidande medelvärde i Excel
Det vägda glidande genomsnittet ger det vägda genomsnittet för de senaste n perioderna. Viktningen minskar med varje datapunkt för föregående tidsperiod.
Vägt glidande medelvärde = (Pris * viktningsfaktor) + (Pris för föregående period * viktningsfaktor-1)
# 3 - Exponentiellt glidande medelvärde i Excel
Det liknar ett enkelt glidande medelvärde som mäter trender över en tidsperiod. Medan enkelt glidande medelvärde beräknar ett medelvärde för given data, exponentiellt glidande medelvärde lägger mer vikt på aktuella data.
Exponentiellt glidande medelvärde = (K x (C - P)) + P
Var,
- K = exponentiell utjämningskonstant
- C = nuvarande pris
- P = exponentiellt glidande genomsnitt för tidigare perioder (enkelt glidande medelvärde används för beräkning av första perioder)
Hur man beräknar glidande medelvärde i Excel?
Nedan följer exemplen på ett glidande medelvärde i Excel.
Exempel # 1 - Enkelt glidande medelvärde i Excel
För att beräkna det enkla glidande genomsnittet har vi tagit försäljningsdata från ett företag från januari till december för år 2018. Vårt mål är att jämna ut uppgifterna och känna till försäljningssiffran i januari 2019. Vi kommer att använda tre månaders glidande medelvärde här.
Steg 1: Det glidande genomsnittet för januari, februari och mars beräknas genom att ta summan av månadernas försäljningssiffror och sedan dividera den med 3.
Steg 2: Att välja i hörnet av D5-cellen och sedan bara dra och släppa ner ger det glidande genomsnittet för de återstående perioderna. Detta är excels fyllningsverktygsfunktion.
Försäljningsprognosen för januari 2019 är 10456.66667.
Steg 3: Nu plottar vi försäljningssiffran och glidande genomsnitt i linjediagrammet för att förstå skillnaden i trend. Detta kan göras från fliken infoga. Först har vi valt dataserien och sedan från avsnittet Diagram under infoga har vi använt linjediagrammet.
Efter att ha skapat graferna kan man se att diagrammet med glidande medelvärde är mycket jämnare än den ursprungliga dataserien.
Exempel # 2 - Enkelt glidande medelvärde genom fliken Dataanalys i Excel
- Under fliken Data under gruppen Analys måste vi klicka på Dataanalys. Följande är skärmdumpen.
- Från dataanalysen kan det rörliga genomsnittet nås.
- Efter att ha klickat på det glidande genomsnittet har vi valt försäljningssiffran som inmatningsintervall.
- Etiketterna i den första raden klickas för att göra excel förstående att den första raden har etikettnamnet.
- Intervall 3 väljs eftersom vi vill ha tre års glidande medelvärde.
- Vi har valt produktionsområdet med närhet till försäljningssiffran.
- Vi vill också se diagramutdata, där vi kommer att kunna se skillnaden mellan det faktiska och det prognostiserade.
Detta diagram visar skillnaden mellan faktiskt och prognostiserat glidande medelvärde.
Exempel # 3 - Vägt glidande medelvärde i Excel
Vi använder det tre år viktade glidande genomsnittet, och formeln ges i skärmdumpen.
Efter att ha använt formeln fick vi det glidande genomsnittet under en period.
Vi fick det glidande genomsnittet för alla andra perioder genom att dra och släppa värden i följande celler.
Prognosen för januari 2019, dvs. 10718,33
Nu tog vi linjediagrammet för att se utjämningen av data. För detta har vi valt vår prognostiserade data och sedan infogat ett linjediagram.
Nu ska vi jämföra våra prognostiserade data med våra faktiska data. I nedanstående skärmdumpar kan vi enkelt se skillnaden mellan faktiska data och prognostiserade data. Diagrammet överst är de faktiska uppgifterna, och diagrammet nedan är glidande medelvärde och prognostiserade data. Vi kan se att diagrammet för glidande medel har minskat betydligt jämfört med diagrammet som innehåller de faktiska uppgifterna.
Exempel # 4 - Exponentiellt glidande medelvärde i Excel
Formeln för det exponentiella glidande genomsnittet är S t = α.Y t-1 + (1- α) S t-1 … (1)
Var,
- Y t-1 = faktisk observation under t-1: e perioden
- S t-1 = enkelt glidande medelvärde under t-1: e perioden
- α = utjämningsfaktor, och den varierar mellan .1 och .3. Ju större värde på α närmare är diagrammet till de faktiska värdena, och ju mindre värdet på α, desto jämnare blir diagrammet.
Först beräknar vi det enkla glidande genomsnittet, som visas tidigare. Därefter tillämpar vi formeln i ekvation (1). För att fastställa α-värdet för alla följande värden har vi tryckt på F4.
Vi får värdena genom att dra och släppa i följande celler.
Nu vill vi se jämförelsen mellan de faktiska värdena, enkelt glidande medelvärde och exponentiellt glidande medelvärde i excel. Vi har gjort detta genom att göra ett linjediagram.
Från ovanstående skärmdump kan vi se skillnaden mellan den faktiska försäljningssiffran, det enkla glidande genomsnittet och det exponentiella glidande genomsnittet i excel.
Saker att komma ihåg om glidande medelvärde i Excel
- Det enkla glidande genomsnittet kan beräknas med hjälp av en AVERAGE-funktion i excel
- Glidande medel hjälper till att utjämna data
- Säsongsgenomsnitt betecknas ofta som ett säsongsindex
- Det exponentiella glidande genomsnittet i excel ger mer tyngd åt de senaste uppgifterna än det enkla glidande genomsnittet. Utjämning i fallet med det exponentiella glidande genomsnittet i excel är därför mer än det för det enkla glidande genomsnittet.
- I företag som en aktiemarknad hjälper glidande medel näringsidkaren att lättare identifiera trenden.
