Glidande medelvärde i Excel

Glidande medelvärde betyder att vi beräknar medelvärdet av datauppsättningen 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 output, beräkningar baserade på enbart formler i excel för att beräkna glidande medelvärde är svåra men vi har en inbyggd funktion i excel för att göra det.

Vad är glidande medelvärde i Excel

Glidande medelvärde är en mycket använd teknik i tidsserie-analys som används för att förutsäga framtiden. De glidande medelvärdena i en tidsserie är i princip konstruerade 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 det enkla glidande genomsnittet som mäter trender över en tidsperiod. Medan enkelt glidande medelvärde beräknar ett genomsnitt av givna 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 exemplet på glidande medel i Excel.

Du kan ladda ner denna glidande Excel-mall här - glidande Excel-mall

Exempel # 1 - Enkelt glidande medelvärde i Excel

För att beräkna det enkla glidande genomsnittet har vi tagit försäljningsdata för 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 det glidande genomsnittet i linjediagrammet för att förstå skillnaden i trend. Detta kan göras från fliken infoga. För det första har vi valt dataserier 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 analysgruppen måste vi klicka på dataanalys. Följande är skärmdumpen.

  • Från dataanalys kan det rörliga genomsnittet nås.

  • Efter att ha klickat på det glidande genomsnittet har vi valt försäljningssiffran som ingångsområde.

  • 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 som för att 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 på 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 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 exponentiellt glidande medelvärde ä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ärdet är närmare är diagrammet till de faktiska värdena och minskar 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

  1. Det enkla glidande genomsnittet kan beräknas med hjälp av en AVERAGE-funktion i excel
  2. Glidande medel hjälper till att utjämna data
  3. Säsongsgenomsnitt betecknas ofta som ett säsongsindex
  4. 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.
  5. I företag som en aktiemarknad hjälper glidande medel näringsidkaren att lättare identifiera trenden.