Beräkna glidande medelvärde i Excel (enkelt, viktat och exponentiellt)

Liksom många av mina Excel -självstudier är den här också inspirerad av en av frågorna jag fick från en vän. Hon ville beräkna det glidande genomsnittet i Excel, och jag bad henne att söka efter det online (eller titta på en YouTube -video om det).

Men sedan bestämde jag mig för att skriva en själv (det faktum att jag var lite av en statistiknörd på college spelade också en mindre roll).

Nu, innan jag berättar hur du beräknar glidande medelvärde i Excel, låt mig snabbt ge dig en överblick över vad glidande medelvärde betyder och vilka typer av glidande medelvärden som finns.

Om du vill hoppa till den del där jag visar hur jag beräknar glidande medelvärde i Excel, klicka här.

Obs! Jag är ingen expert på statistik och min avsikt med denna handledning är inte att täcka allt om glidande medelvärden. Jag syftar bara till att visa dig hur du beräknar glidande medelvärden i Excel (med en kort introduktion av vad glidande medelvärden betyder).

Vad är ett glidande medelvärde?

Jag är säker på att du vet vad som är ett genomsnittligt värde.

Om jag har tre dagars dagliga temperaturdata kan du enkelt berätta för mig genomsnittet av de senaste tre dagarna (tips: du kan använda medelvärdesfunktionen i Excel för att göra detta).

Ett glidande medelvärde (även kallat rullande medelvärde eller löpande medelvärde) är när du håller genomsnittets tidsperiod densamma, men fortsätter att röra sig när ny data läggs till.

Till exempel, på dag 3, om jag frågar dig 3-dagars glidande medeltemperatur, kommer du att ge mig medeltemperaturvärdet för dag 1, 2 och 3. Och om jag på dag 4 frågar dig den 3-dagars glidande medeltemperaturen , kommer du att ge mig genomsnittet av dag 2, 3 och 4.

När nya data läggs till behåller du tidsperioden (3 dagar) densamma men använder de senaste uppgifterna för att beräkna det glidande genomsnittet.

Glidande medelvärde används starkt för teknisk analys och många banker och börsanalytiker använder det dagligen (nedan är ett exempel jag fick från Market Realist-sajten).

En av fördelarna med att använda glidande medelvärden är att det ger dig trenden och jämnar ut fluktuationer i viss utsträckning. Till exempel, om det är en riktigt varm dag, skulle det tre dagars glidande genomsnittet av temperaturen fortfarande se till att medelvärdet har jämnats ut (i stället för att visa dig ett riktigt högt värde som kan vara en outlier- ett en- av instans).

Typer av glidande medelvärden

Det finns tre typer av glidande medelvärden:

• Enkelt glidande medelvärde (SMA)
• Vägt glidande medelvärde (WMA)
• Exponentiellt glidande medelvärde (EMA)

Enkelt glidande medelvärde (SMA)

Detta är det enkla genomsnittet av datapunkterna under den givna varaktigheten.

I vårt exempel på daglig temperatur, när du bara tar ett genomsnitt av de senaste 10 dagarna, ger det det 10-dagars enkla glidande genomsnittet.

Detta kan uppnås genom att medelvärdena för datapunkterna under den givna varaktigheten. I Excel kan du enkelt göra detta med hjälp av AVERAGE -funktionen (detta täcks senare i denna handledning).

Vägt glidande medelvärde (WMA)

Låt oss säga att vädret blir svalare för varje dag som går och du använder ett glidande medelvärde på 10 dagar för att få temperaturutvecklingen.

Dag-10-temperaturen är mer sannolikt en bättre indikator på trenden jämfört med dag-1 (eftersom temperaturen sjunker för varje dag som går).

Så vi har det bättre om vi litar mer på värdet på dag 10.

För att få detta att återspegla i vårt glidande medelvärde kan du lägga mer vikt på de senaste uppgifterna och mindre på tidigare data. På så sätt får du fortfarande trenden, men med mer inflytande av de senaste uppgifterna.

Detta kallas det vägda glidande genomsnittet.

Exponentiellt glidande medelvärde (EMA)

Det exponentiella glidande genomsnittet är en typ av ett vägat glidande medelvärde där mer vikt läggs på de senaste uppgifterna och det minskar exponentiellt för de äldre datapunkterna.

Det kallas också Exponential Weighted Moving Average (EWMA)

Skillnaden mellan WMA och EMA är att med WMA kan du tilldela vikter baserat på alla kriterier. I ett glidande medelvärde på tre punkter kan du till exempel tilldela en 60% viktålder till den senaste datapunkten, 30% till den mellersta datapunkten och 10% till den äldsta datapunkten.

I EMA ges en högre vikt till det senaste värdet och vikten blir exponentiellt lägre för tidigare värden.

Nog med statistikföreläsning.

Låt oss nu dyka in och se hur vi beräknar glidande medelvärden i Excel.

Beräkna Simple Moving Average (SMA) med hjälp av Data Analysis Toolpak i Excel

Microsoft Excel har redan ett inbyggt verktyg för att beräkna de enkla glidande genomsnitten.

Det kallas för Data Analysis Toolpak.

Innan du kan använda dataanalysverktygspaketet måste du först kontrollera om du har det i Excel -bandet eller inte. Det finns en god chans att du måste ta några steg för att först aktivera det.

Om du redan har alternativet Dataanalys på fliken Data, hoppa över stegen nedan och se stegen för beräkning av glidande medelvärden.

Klicka på fliken Data och kontrollera om du ser alternativet Dataanalys eller inte. Om du inte ser det följer du stegen nedan för att göra det tillgängligt i menyfliksområdet.

1. Klicka på fliken Arkiv
2. Klicka på Alternativ
3. Klicka på tillägg i dialogrutan Excel-alternativ
4. Längst ned i dialogrutan väljer du Excel-tillägg i rullgardinsmenyn och klickar sedan på Kör.
5. Kontrollera alternativet Analysis Toolpak i dialogrutan Tillägg som öppnas
6. Klicka på OK.

Stegen ovan möjliggör dataanalysverktygspaketet och du kommer att se det här alternativet på fliken Data nu.

Antag att du har datauppsättningen enligt nedan och du vill beräkna det glidande medeltalet för de tre senaste intervallerna.

Nedan följer stegen för att använda dataanalys för att beräkna ett enkelt glidande medelvärde:

1. Klicka på fliken Data
2. Klicka på alternativet Dataanalys
3. I dialogrutan Dataanalys klickar du på alternativet Glidande medelvärde (du kan behöva rulla lite för att nå det).
4. Klicka på OK. Detta öppnar dialogrutan "Glidande medelvärde".
5. I inmatningsområdet väljer du de data för vilka du vill beräkna det glidande genomsnittet (B2: B11 i detta exempel)
6. I alternativet Intervall anger du 3 (eftersom vi beräknar ett trepunkts glidande medelvärde)
7. I utmatningsområdet anger du cellen där du vill ha resultaten. I det här exemplet använder jag C2 som utgångsområde
8. Klicka på OK

Stegen ovan skulle ge dig det glidande genomsnittliga resultatet enligt nedan.

Observera att de två första cellerna i kolumn C har resultatet som #N/A -fel. Detta beror på att det är ett glidande genomsnitt på tre punkter och behöver minst tre datapunkter för att ge det första resultatet. Så de faktiska glidande medelvärdena börjar efter den tredje datapunkten och framåt.

Du kommer också att märka att allt detta dataanalysverktygspaket har gjort applicerar en GENomsnittsmetod på cellerna. Så om du vill göra detta manuellt utan dataanalysverktygspaketet kan du verkligen göra det.

Det finns dock några saker som är lättare att göra med dataanalysverktygspaketet. Till exempel, om du vill få både standardfelvärdet och diagrammet över det glidande genomsnittet, är allt du behöver göra att markera en ruta och det kommer att vara en del av utdata.

Beräkna glidande medelvärden (SMA, WMA, EMA) med hjälp av formler i Excel

Du kan också beräkna de glidande medelvärdena med hjälp av MEDEL -formeln.

Faktum är att om du bara behöver det glidande medelvärdet (och inte standardfelet eller diagrammet) kan en formel vara ett bättre (och snabbare) alternativ än att använda dataanalysverktygspaketet.

Dataanalys Toolpak ger också bara det enkla rörliga genomsnittet (SMA), men om du vill beräkna WMA eller EMA måste du bara lita på formler.

Beräkna enkelt glidande medelvärde med hjälp av formler

Antag att du har datauppsättningen enligt nedan och du vill beräkna 3-punkts SMA:

I cellen C4 anger du följande formel:

= MEDEL (B2: B4)

Kopiera denna formel för alla celler och den ger dig SMA för varje dag.

Kom ihåg: När du beräknar SMA med hjälp av formler måste du se till att referenserna i formeln är relativa. Det betyder att formeln kan vara = MEDEL (B2: B4) eller = MEDEL ($ B2: $ B4), men den kan inte vara = GENNEMSNITT ($ B $ 2: $ B $ 4) eller = MEDEL (B $ 2: B $ 4) ). Radnummersdelen av referensen måste vara utan dollarstecken. Du kan läsa mer om absoluta och relativa referenser här.

Eftersom vi beräknar ett 3-punkts Simple Moving Average (SMA) är de två första cellerna (under de två första dagarna) tomma och vi börjar använda formeln från och med den tredje dagen. Om du vill kan du använda de två första värdena som de är och använda SMA -värdet från det tredje.

Beräkning av viktat glidande medelvärde med hjälp av formler

För WMA måste du veta vilka vikter som skulle tilldelas värdena.

Anta till exempel att du måste beräkna 3 -punkts WMA för nedanstående dataset, där 60% vikt ges till det senaste värdet, 30% till det före det och 10% av det före det.

För att göra detta, ange följande formel i cell C4 och kopiera för alla celler.

= 0,6*B4+0,3*B3+0,1*B2

Eftersom vi beräknar ett 3-punkts vägat glidande medelvärde (WMA) är de två första cellerna (under de två första dagarna) tomma och vi börjar använda formeln från och med den tredje dagen. Om du vill kan du använda de två första värdena som de är och använda WMA -värdet från och med det tredje.

Beräkna exponentiellt glidande medelvärde med hjälp av formler

Exponential Moving Average (EMA) ger högre vikt till det senaste värdet och vikterna fortsätter att bli lägre exponentiellt för tidigare värden.

Nedan är formeln för att beräkna EMA för ett trepunkts glidande medelvärde:

EMA = [Senaste värde - Föregående EMA -värde] * (2 / N + 1) + Föregående EMA

… där N skulle vara 3 i detta exempel (som vi beräknar en trepunkts EMA)

Obs! För det första EMA -värdet (när du inte har något tidigare värde för att beräkna EMA), ta bara värdet som det är och betrakta det som EMA -värdet. Du kan sedan använda detta värde framöver.

Anta att du har nedanstående datamängd och du vill beräkna EMA med tre perioder:

I cell C2 anger du samma värde som i B2. Detta beror på att det inte finns något tidigare värde för att beräkna EMA.

I cell C3 anger du nedanstående formel och kopierar för alla celler:

= (B3-C2)*(2/4)+C2

I det här exemplet har jag hållit det enkelt och använt det senaste värdet och tidigare EMA -värde för att beräkna det aktuella EMA.

Ett annat populärt sätt att göra detta är genom att först beräkna det enkla rörliga genomsnittet och sedan använda det istället för det faktiska senaste värdet.

Lägga till glidande genomsnittlig trendlinje i ett kolumndiagram

Om du har en dataset och du skapar ett stapeldiagram med den kan du också lägga till trendlinjen för glidande medelvärde med några klick.

Anta att du har en dataset som visas nedan:

Nedan följer stegen för att skapa ett stapeldiagram med hjälp av dessa data och lägga till en tredelad glidande genomsnittlig trendlinje till detta diagram:

1. Välj datauppsättningen (inklusive rubriker)
2. Klicka på fliken Infoga
3. Klicka på ikonen "Infoga kolumn eller stapeldiagram" i diagramgruppen.
4. Klicka på alternativet Clustered Column chart. Detta infogar diagrammet i kalkylbladet.
5. Med diagrammet markerat klickar du på fliken Design (den här fliken visas bara när diagrammet är valt)
6. Klicka på "Lägg till diagramelement" i gruppen diagramlayouter.
7. Håll muspekaren på alternativet "Trendline" och klicka sedan på "Fler trendlinjealternativ"
8. I fönstret Format trendlinje väljer du alternativet "Glidande medelvärde" och anger antalet perioder.

Det är allt! Stegen ovan skulle lägga till en rörlig trendlinje i ditt kolumndiagram.

Om du vill infoga mer än en glidande genomsnittlig trendlinje (till exempel en för 2 perioder och en för 3 perioder), upprepa stegen från 5 till 8).

Du kan använda samma steg för att infoga en trendrad för glidande medelvärde också i ett linjediagram.

Formatera den rörliga genomsnittliga trendlinjen

Till skillnad från ett vanligt linjediagram tillåter inte en glidande genomsnittlig trendlinje mycket formatering. Om du till exempel vill markera en specifik datapunkt på trendlinjen kommer du inte att kunna göra det.

Några saker du kan formatera i trendlinjen inkluderar:

• Färg av raden. Du kan använda detta för att markera en av trendlinjerna genom att göra allt i diagrammet ljus i färg och göra trendlinjen pop-out med en ljus färg
• De tjocklek av raden
• De genomskinlighet av raden

För att formatera den glidande genomsnittliga trendlinjen, högerklicka på den och välj sedan alternativet Formatera trendlinje.

Detta öppnar rutan Format Trendline till höger. Den här rutan som alla formateringsalternativ (i olika sektioner - Fill & Line, Effects och Trendline Options).