Beregning af glidende gennemsnit i Excel (enkelt, vægtet og eksponentielt)

Ligesom mange af mine Excel -øvelser er denne også inspireret af en af ​​de forespørgsler, jeg fik fra en ven. Hun ville beregne det glidende gennemsnit i Excel, og jeg bad hende om at søge efter det online (eller se en YouTube -video om det).

Men så besluttede jeg at skrive en selv (det faktum, at jeg var lidt af en statistiknørd på college, spillede også en mindre rolle).

Nu, før jeg fortæller dig, hvordan du beregner glidende gennemsnit i Excel, lad mig hurtigt give dig et overblik over, hvad glidende gennemsnit betyder, og hvilke typer glidende gennemsnit der er.

Hvis du vil springe til den del, hvor jeg viser, hvordan du beregner glidende gennemsnit i Excel, klik her.

Bemærk: Jeg er ikke ekspert i statistik, og min hensigt med denne vejledning er ikke at dække alt om glidende gennemsnit. Jeg har kun til formål at vise dig, hvordan du beregner glidende gennemsnit i Excel (med en kort introduktion til, hvad glidende gennemsnit betyder).

Hvad er et glidende gennemsnit?

Jeg er sikker på, at du ved, hvad der er en gennemsnitlig værdi.

Hvis jeg har tre dages daglige temperaturdata, kan du nemt fortælle mig gennemsnittet af de sidste tre dage (tip: du kan bruge GENNEMSNIT -funktionen i Excel til at gøre dette).

Et glidende gennemsnit (også kaldet rullende gennemsnit eller løbende gennemsnit) er, når du holder gennemsnitsperioden den samme, men fortsætter med at bevæge sig, efterhånden som nye data tilføjes.

For eksempel på dag 3, hvis jeg spørger dig om 3-dages glidende gennemsnitstemperatur, vil du give mig den gennemsnitlige temperaturværdi på dag 1, 2 og 3. Og hvis jeg på dag 4 spørger dig om 3-dages glidende gennemsnitstemperatur , vil du give mig gennemsnittet af dag 2, 3 og 4.

Når nye data tilføjes, holder du tidsperioden (3 dage) den samme, men bruger de nyeste data til at beregne det glidende gennemsnit.

Glidende gennemsnit bruges stærkt til teknisk analyse, og mange banker og aktiemarkedsanalytikere bruger det dagligt (nedenfor er et eksempel, jeg fik fra Market Realist-webstedet).

En af fordelene ved at bruge de glidende gennemsnit er, at det giver dig trenden samt udjævner udsving i et omfang. For eksempel, hvis der er en virkelig varm dag, vil det tre-dages glidende gennemsnit af temperaturen stadig sikre, at gennemsnitsværdien er blevet glattet (i stedet for at vise dig en virkelig høj værdi, der kan være en outlier- en en- off instans).

Typer af glidende gennemsnit

Der er tre typer af glidende gennemsnit:

• Simpelt glidende gennemsnit (SMA)
• Vægtet glidende gennemsnit (WMA)
• Eksponentielt glidende gennemsnit (EMA)

Simpelt glidende gennemsnit (SMA)

Dette er det enkle gennemsnit af datapunkterne i den givne varighed.

I vores daglige temperatureksempel, når du blot tager et gennemsnit af de sidste 10 dage, giver det det 10-dages simple glidende gennemsnit.

Dette kan opnås ved at beregne datapunkterne i gennemsnit i den givne varighed. I Excel kan du nemt gøre dette ved hjælp af GENNEMSNIT -funktionen (dette er dækket senere i denne vejledning).

Vægtet glidende gennemsnit (WMA)

Lad os sige, at vejret bliver køligere for hver dag, der går, og du bruger et glidende gennemsnit på 10 dage til at få temperaturudviklingen.

Dag-10 temperatur er mere tilbøjelig til at være en bedre indikator for tendensen sammenlignet med dag-1 (da temperaturen falder for hver dag, der går).

Så vi har det bedre, hvis vi stoler mere på værdien af ​​dag 10.

For at få dette til at afspejle i vores glidende gennemsnit kan du lægge større vægt på de nyeste data og mindre på tidligere data. På denne måde får du stadig trenden, men med større indflydelse på de nyeste data.

Dette kaldes det vægtede glidende gennemsnit.

Eksponentielt glidende gennemsnit (EMA)

Det eksponentielle glidende gennemsnit er en type af vægtet glidende gennemsnit, hvor der lægges mere vægt på de nyeste data, og det falder eksponentielt for de ældre datapunkter.

Det kaldes også det eksponentielle vægtede glidende gennemsnit (EWMA)

Forskellen mellem WMA og EMA er, at med WMA kan du tildele vægte baseret på alle kriterier. I et glidende gennemsnit på 3 punkter kan du f.eks. Tildele en alder på 60% til det seneste datapunkt, 30% til det midterste datapunkt og 10% til det ældste datapunkt.

I EMA gives en højere vægt til den seneste værdi, og vægten bliver ved med at blive eksponentielt lavere for tidligere værdier.

Nok statistikforedrag.

Lad os nu dykke ned og se, hvordan du beregner glidende gennemsnit i Excel.

Beregning af Simple Moving Average (SMA) ved hjælp af Data Analysis Toolpak i Excel

Microsoft Excel har allerede et indbygget værktøj til at beregne de simple glidende gennemsnit.

Det kaldes Dataanalyse Toolpak.

Inden du kan bruge værktøjspakken til dataanalyse, skal du først kontrollere, om du har det i Excel -båndet eller ej. Der er en god chance for, at du skal tage et par trin for først at aktivere det.

Hvis du allerede har muligheden Dataanalyse på fanen Data, skal du springe trinene nedenfor over og se trinene til beregning af glidende gennemsnit.

Klik på fanen Data, og kontroller, om du ser muligheden for dataanalyse eller ej. Hvis du ikke kan se det, skal du følge nedenstående trin for at gøre det tilgængeligt på båndet.

1. Klik på fanen Filer
2. Klik på Indstillinger
3. Klik på tilføjelsesprogrammer i dialogboksen Excel-indstillinger
4. Nederst i dialogboksen skal du vælge Excel-tilføjelsesprogrammer i rullemenuen og derefter klikke på Gå.
5. Kontroller indstillingen Analysis Toolpak i dialogboksen Tilføjelser, der åbnes
6. Klik på OK.

Ovenstående trin vil muliggøre dataanalyseværktøjspakken, og du vil se denne mulighed på fanen Data nu.

Antag, at du har datasættet som vist herunder, og du vil beregne det glidende gennemsnit for de sidste tre intervaller.

Nedenfor er trinene til at bruge dataanalyse til at beregne et simpelt glidende gennemsnit:

1. Klik på fanen Data
2. Klik på dataanalyse
3. I dialogboksen Dataanalyse skal du klikke på indstillingen Glidende gennemsnit (du skal muligvis rulle lidt for at nå den).
4. Klik på OK. Dette åbner dialogboksen 'Glidende gennemsnit'.
5. I inputområdet skal du vælge de data, du vil beregne det glidende gennemsnit for (B2: B11 i dette eksempel)
6. I indstillingen Interval skal du indtaste 3 (da vi beregner et trepunkts glidende gennemsnit)
7. I Output -området skal du indtaste cellen, hvor du vil have resultaterne. I dette eksempel bruger jeg C2 som outputområde
8. Klik på OK

Ovenstående trin ville give dig det glidende gennemsnitlige resultat som vist nedenfor.

Bemærk, at de to første celler i kolonne C har resultatet som #N/A fejl. Dette skyldes, at det er et glidende gennemsnit på tre punkter og har brug for mindst tre datapunkter for at give det første resultat. Så de faktiske glidende gennemsnitsværdier starter efter det tredje datapunkt og fremefter.

Du vil også bemærke, at alt dette dataanalyseværktøjspakke har udført, anvender en GENNEMGANGS -formel på cellerne. Så hvis du vil gøre dette manuelt uden dataanalyse -værktøjspakken, kan du bestemt gøre det.

Der er dog et par ting, der er lettere at gøre med værktøjet til dataanalyse. For eksempel, hvis du vil få standardfejlværdien samt diagrammet over det glidende gennemsnit, er alt du skal gøre at markere en boks, og det vil være en del af output.

Beregning af glidende gennemsnit (SMA, WMA, EMA) ved hjælp af formler i Excel

Du kan også beregne de glidende gennemsnit ved hjælp af GENNEMSNIT -formlen.

Faktisk, hvis alt hvad du behøver er den glidende gennemsnitsværdi (og ikke standardfejlen eller diagrammet), kan brug af en formel være en bedre (og hurtigere) mulighed end at bruge Data Analysis Toolpak.

Dataanalyse Toolpak giver også kun det simple glidende gennemsnit (SMA), men hvis du vil beregne WMA eller EMA, skal du kun stole på formler.

Beregning af enkelt glidende gennemsnit ved hjælp af formler

Antag, at du har datasættet som vist herunder, og du vil beregne 3-punkts SMA:

Indtast følgende formel i celle C4:

= Gennemsnit (B2: B4)

Kopiér denne formel for alle cellerne, og den giver dig SMA for hver dag.

Husk: Når du beregner SMA ved hjælp af formler, skal du sørge for, at referencerne på formlen er relative. Det betyder, at formlen kan være = Gennemsnit (B2: B4) eller = Gennemsnit ($ B2: $ B4), men den kan ikke være = Gennemsnit ($ B $ 2: $ B $ 4) eller = Gennemsnit (B $ 2: B $ 4 ). Rækketalsdelen af ​​referencen skal være uden dollartegnet. Du kan læse mere om absolutte og relative referencer her.

Da vi beregner et 3-punkts simpelt glidende gennemsnit (SMA), er de to første celler (i de første to dage) tomme, og vi begynder at bruge formlen fra den tredje dag og fremefter. Hvis du vil, kan du bruge de to første værdier som den er, og bruge SMA -værdien fra den tredje og fremefter.

Beregning af vægtet glidende gennemsnit ved hjælp af formler

For WMA skal du kende de vægte, der ville blive tildelt værdierne.

Antag for eksempel, at du skal beregne 3 -punkts WMA for nedenstående datasæt, hvor 60% vægt er givet til den seneste værdi, 30% til den før den og 10% af den før den.

For at gøre dette skal du indtaste følgende formel i celle C4 og kopiere for alle celler.

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

Da vi beregner et 3-punkts vægtet glidende gennemsnit (WMA), er de to første celler (i de første to dage) tomme, og vi begynder at bruge formlen fra den tredje dag og fremefter. Hvis du vil, kan du bruge de to første værdier som den er, og bruge WMA -værdien fra den tredje og fremefter.

Beregning af eksponentielt glidende gennemsnit ved hjælp af formler

Eksponentielt glidende gennemsnit (EMA) giver højere vægt til den seneste værdi, og vægtene bliver ved med at blive lavere eksponentielt for tidligere værdier.

Nedenfor er formlen til beregning af EMA for et trepunkts glidende gennemsnit:

EMA = [Seneste værdi - Forrige EMA -værdi] * (2 / N + 1) + Forrige EMA

… hvor N ville være 3 i dette eksempel (da vi beregner en trepunkts EMA)

Bemærk: For den første EMA -værdi (når du ikke har nogen tidligere værdi til at beregne EMA), skal du blot tage værdien som den er og betragte den som EMA -værdien. Du kan derefter bruge denne værdi fremover.

Antag, at du har nedenstående datasæt, og du vil beregne EMA med tre perioder:

I celle C2 skal du indtaste den samme værdi som i B2. Dette skyldes, at der ikke er nogen tidligere værdi til beregning af EMA.

I celle C3 skal du indtaste nedenstående formel og kopiere for alle celler:

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

I dette eksempel har jeg holdt det enkelt og brugt den seneste værdi og tidligere EMA -værdi til at beregne den aktuelle EMA.

En anden populær måde at gøre dette på er ved først at beregne det simple glidende gennemsnit og derefter bruge det i stedet for den faktiske seneste værdi.

Tilføjelse af glidende gennemsnitlig trendlinje til et søjlediagram

Hvis du har et datasæt, og du opretter et søjlediagram ved hjælp af det, kan du også tilføje den glidende gennemsnitlige trendlinje med et par klik.

Antag, at du har et datasæt som vist herunder:

Nedenfor er trinene til at oprette et søjlediagram ved hjælp af disse data og tilføje en tredelt glidende gennemsnitlig trendlinje til dette diagram:

1. Vælg datasættet (inklusive overskrifterne)
2. Klik på fanen Indsæt
3. I diagramgruppen skal du klikke på ikonet ‘Indsæt kolonne eller søjlediagram’.
4. Klik på indstillingen Clustered Column chart. Dette indsætter diagrammet i regnearket.
5. Når diagrammet er valgt, skal du klikke på fanen Design (denne fane vises kun, når diagrammet er valgt)
6. Klik på "Tilføj diagramelement" i gruppen Chartlayouts.
7. Hold markøren på "Trendline" -indstillingen, og klik derefter på "More Trendline Options"
8. I ruden Formatér trendlinje skal du vælge "Glidende gennemsnit" og indstille antallet af perioder.

Det er det! Ovenstående trin tilføjer en bevægelig trendlinje til dit kolonnediagram.

Hvis du ønsker at indsætte mere end én glidende gennemsnitlig trendlinje (f.eks. En for 2 perioder og en for 3 perioder), gentag trinene fra 5 til 8).

Du kan også bruge de samme trin til at indsætte en glidende gennemsnitlig trendlinje i et stregdiagram.

Formatering af den glidende gennemsnitlige trendlinje

I modsætning til et almindeligt linjediagram tillader en glidende gennemsnitlig trendlinje ikke meget formatering. For eksempel, hvis du vil fremhæve et bestemt datapunkt på trendlinjen, vil du ikke være i stand til at gøre det.

Et par ting, du kan formatere i trendlinjen, omfatter:

• Farve af linjen. Du kan bruge dette til at fremhæve en af ​​trendlinjerne ved at gøre alt i diagrammet lyst i farve og få trendlinjen til at springe ud med en lys farve
• Det tykkelse af linjen
• Det gennemsigtighed af linjen

For at formatere den glidende gennemsnitlige trendlinje skal du højreklikke på den og derefter vælge indstillingen Format Trendline.

Dette åbner ruden Formatér trendlinje til højre. Denne rude som alle formateringsindstillinger (i forskellige sektioner - Fill & Line, Effects og Trendline Options).