Beregne glidende gjennomsnitt i Excel (enkelt, vektet og eksponentielt)

Som mange av mine Excel -opplæringsprogrammer, er denne også inspirert av en av spørsmålene jeg fikk fra en venn. Hun ønsket å beregne det glidende gjennomsnittet i Excel, og jeg ba henne om å søke etter det på nettet (eller se en YouTube -video om det).

Men så bestemte jeg meg for å skrive en selv (det faktum at jeg var litt av en statistikknerd på college, spilte også en mindre rolle).

Nå, før jeg forteller deg hvordan du beregner glidende gjennomsnitt i Excel, la meg raskt gi deg en oversikt over hva glidende gjennomsnitt betyr og hvilke typer glidende gjennomsnitt det er.

Hvis du vil hoppe til delen der jeg viser hvordan du beregner glidende gjennomsnitt i Excel, klikk her.

Merk: Jeg er ikke ekspert på statistikk, og hensikten med denne opplæringen er ikke å dekke alt om glidende gjennomsnitt. Jeg tar bare sikte på å vise deg hvordan du beregner glidende gjennomsnitt i Excel (med en kort introduksjon av hva glidende gjennomsnitt betyr).

Hva er et glidende gjennomsnitt?

Jeg er sikker på at du vet hva en gjennomsnittlig verdi er.

Hvis jeg har tre dagers daglige temperaturdata, kan du enkelt fortelle meg gjennomsnittet av de tre siste dagene (hint: du kan bruke gjennomsnittsfunksjonen i Excel for å gjøre dette).

Et glidende gjennomsnitt (også kalt rullende gjennomsnitt eller løpende gjennomsnitt) er når du holder gjennomsnittsperioden den samme, men fortsetter å bevege seg etter hvert som nye data legges til.

For eksempel, på dag 3, hvis jeg spør deg den 3-dagers glidende gjennomsnittstemperaturen, vil du gi meg gjennomsnittstemperaturverdien for dag 1, 2 og 3. Og hvis jeg på dag 4 spør deg 3-dagers glidende gjennomsnittstemperatur , vil du gi meg gjennomsnittet av dag 2, 3 og 4.

Etter hvert som nye data legges til, holder du tidsperioden (3 dager) den samme, men bruker de siste dataene til å beregne det glidende gjennomsnittet.

Glidende gjennomsnitt er mye brukt for teknisk analyse, og mange banker og aksjemarkedsanalytikere bruker det daglig (nedenfor er et eksempel jeg fikk fra Market Realist-nettstedet).

En av fordelene med å bruke de glidende gjennomsnittene er at den gir deg trenden og jevner ut svingninger til en viss grad. For eksempel, hvis det er en veldig varm dag, vil det tre dagers glidende gjennomsnittet av temperaturen fortsatt sørge for at gjennomsnittsverdien er blitt jevnet (i stedet for å vise deg en virkelig høy verdi som kan være en outlier- en en- av forekomst).

Typer glidende gjennomsnitt

Det er tre typer glidende gjennomsnitt:

• Enkelt glidende gjennomsnitt (SMA)
• Vektet glidende gjennomsnitt (WMA)
• Eksponensielt glidende gjennomsnitt (EMA)

Enkelt glidende gjennomsnitt (SMA)

Dette er det enkle gjennomsnittet av datapunktene i den gitte varigheten.

I vårt daglige temperatureksempel, når du bare tar et gjennomsnitt av de siste 10 dagene, gir det 10-dagers enkelt glidende gjennomsnitt.

Dette kan oppnås ved å gjennomsnittliggjøre datapunktene i den gitte varigheten. I Excel kan du enkelt gjøre dette ved å bruke GJENNOMSNITT -funksjonen (dette dekkes senere i denne opplæringen).

Vektet glidende gjennomsnitt (WMA)

La oss si at været blir kjøligere for hver dag som går, og du bruker et glidende gjennomsnitt på 10 dager for å få temperaturutviklingen.

Dag-10 temperatur er mer sannsynlig en bedre indikator på trenden sammenlignet med dag 1 (siden temperaturen synker for hver dag som går).

Så vi har det bedre hvis vi stoler mer på verdien av dag 10.

For å få dette til å gjenspeile i vårt glidende gjennomsnitt, kan du legge mer vekt på de nyeste dataene og mindre på tidligere data. På denne måten får du fremdeles trenden, men med større innflytelse fra de siste dataene.

Dette kalles det veide glidende gjennomsnittet.

Eksponensielt glidende gjennomsnitt (EMA)

Det eksponentielle glidende gjennomsnittet er en type vektet glidende gjennomsnitt der de siste dataene blir mer vektlagt, og det synker eksponensielt for de eldre datapunktene.

Det kalles også det eksponentielle vektede glidende gjennomsnittet (EWMA)

Forskjellen mellom WMA og EMA er at med WMA kan du tilordne vekter basert på alle kriterier. For eksempel, i et glidende gjennomsnitt på 3 punkter, kan du tilordne en vektalder på 60% til det siste datapunktet, 30% til det midterste datapunktet og 10% til det eldste datapunktet.

I EMA gis en høyere vekt til den siste verdien, og vekten blir stadig eksponentielt lavere for tidligere verdier.

Nok statistikkforedrag.

La oss nå dykke ned og se hvordan vi beregner glidende gjennomsnitt i Excel.

Beregning av enkelt glidende gjennomsnitt (SMA) ved hjelp av Data Analysis Toolpak i Excel

Microsoft Excel har allerede et innebygd verktøy for å beregne de enkle glidende gjennomsnittene.

Det kalles Data Analysis Toolpak.

Før du kan bruke verktøypakken Dataanalyse, må du først kontrollere om du har det i Excel -båndet eller ikke. Det er en god sjanse for at du må ta noen få skritt for å aktivere det først.

Hvis du allerede har alternativet Dataanalyse i kategorien Data, hopper du over trinnene nedenfor og ser trinnene for beregning av glidende gjennomsnitt.

Klikk på fanen Data og sjekk om du ser alternativet Dataanalyse eller ikke. Hvis du ikke ser det, følger du trinnene nedenfor for å gjøre det tilgjengelig på båndet.

1. Klikk kategorien Fil
2. Klikk på Alternativer
3. Klikk på tillegg i dialogboksen Alternativer for Excel
4. Velg Excel-tillegg nederst i dialogboksen på rullegardinmenyen, og klikk deretter på Gå.
5. Kontroller alternativet Analysis Toolpak i dialogboksen Tillegg som åpnes
6. Klikk OK.

Trinnene ovenfor vil aktivere dataanalyseverktøypakken, og du vil se dette alternativet i fanen Data nå.

Anta at du har datasettet som vist nedenfor, og du vil beregne det glidende gjennomsnittet av de tre siste intervallene.

Nedenfor er trinnene for å bruke dataanalyse for å beregne et enkelt glidende gjennomsnitt:

1. Klikk kategorien Data
2. Klikk på alternativet Dataanalyse
3. I dialogboksen Dataanalyse klikker du på alternativet Glidende gjennomsnitt (du må kanskje rulle litt for å nå det).
4. Klikk OK. Dette åpner dialogboksen "Glidende gjennomsnitt".
5. Velg Inndataområdet dataene du vil beregne det glidende gjennomsnittet for (B2: B11 i dette eksemplet)
6. I alternativet Intervall, skriv inn 3 (som vi beregner et trepunkts glidende gjennomsnitt)
7. I Output -området skriver du inn cellen der du vil ha resultatene. I dette eksemplet bruker jeg C2 som utgangsområde
8. Klikk OK

Trinnene ovenfor vil gi deg det glidende gjennomsnittsresultatet som vist nedenfor.

Vær oppmerksom på at de to første cellene i kolonne C har resultatet som #N/A -feil. Dette er fordi det er et trepunkts glidende gjennomsnitt og trenger minst tre datapunkter for å gi det første resultatet. Så de faktiske glidende gjennomsnittsverdiene starter etter det tredje datapunktet og fremover.

Du vil også legge merke til at alt dette Data Analysis toolpakken har gjort, er brukt en GJENNOMGANG -formel på cellene. Så hvis du vil gjøre dette manuelt uten dataanalyseverktøypakken, kan du absolutt gjøre det.

Det er imidlertid noen få ting som er lettere å gjøre med verktøypakken for dataanalyse. For eksempel, hvis du vil få standardfeilverdien samt diagrammet over det glidende gjennomsnittet, er alt du trenger å gjøre å merke av i en boks, og den vil være en del av utgangen.

Beregning av glidende gjennomsnitt (SMA, WMA, EMA) ved hjelp av formler i Excel

Du kan også beregne de glidende gjennomsnittene ved å bruke gjennomsnittsformelen.

Faktisk, hvis alt du trenger er den glidende gjennomsnittsverdien (og ikke standardfeilen eller diagrammet), kan bruk av en formel være et bedre (og raskere) alternativ enn å bruke Data Analysis Toolpak.

Dataanalyse Toolpak gir også bare Simple Moving Average (SMA), men hvis du vil beregne WMA eller EMA, må du bare stole på formler.

Beregning av enkelt glidende gjennomsnitt ved hjelp av formler

Anta at du har datasettet som vist nedenfor, og du vil beregne 3-punkts SMA:

Skriv inn følgende formel i cellen C4:

= GJENNOMSNITT (B2: B4)

Kopier denne formelen for alle cellene, og den gir deg SMA for hver dag.

Husk: Når du beregner SMA ved hjelp av formler, må du sørge for at referansene på formelen er relative. Dette betyr at formelen kan være = GJENNOMSNITT (B2: B4) eller = GJENNOMSNITT ($ B2: $ B4), men den kan ikke være = GJENNOMSNITT ($ B $ 2: $ B $ 4) eller = GJENNOMSNITT (B $ 2: B $ 4) ). Radnummerdelen av referansen må være uten dollartegnet. Du kan lese mer om absolutte og relative referanser her.

Siden vi beregner et 3-punkts enkelt glidende gjennomsnitt (SMA), er de to første cellene (for de to første dagene) tomme, og vi begynner å bruke formelen fra og med den tredje dagen. Hvis du vil, kan du bruke de to første verdiene som de er, og bruke SMA -verdien fra den tredje og fremover.

Beregning av veid glidende gjennomsnitt ved hjelp av formler

For WMA må du kjenne vektene som vil bli tilordnet verdiene.

Anta for eksempel at du må beregne 3 -punkts WMA for datasettet nedenfor, der 60% vekt er gitt til den siste verdien, 30% til den før den og 10% av den før den.

For å gjøre dette, skriv inn følgende formel i celle C4 og kopier for alle cellene.

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

Siden vi beregner et 3-punkts vektet glidende gjennomsnitt (WMA), er de to første cellene (for de to første dagene) tomme, og vi begynner å bruke formelen fra og med den tredje dagen. Hvis du vil, kan du bruke de to første verdiene som de er, og bruke WMA -verdien fra den tredje og fremover.

Beregning av eksponentielt glidende gjennomsnitt ved hjelp av formler

Eksponentielt glidende gjennomsnitt (EMA) gir høyere vekt til den siste verdien, og vektene blir stadig lavere eksponentielt for tidligere verdier.

Nedenfor er formelen for å beregne EMA for et trepunkts glidende gjennomsnitt:

EMA = [Siste verdi - Forrige EMA -verdi] * (2 / N + 1) + Forrige EMA

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

Merk: For den første EMA -verdien (når du ikke har noen tidligere verdi for å beregne EMA), bare ta verdien som den er og betrakte den som EMA -verdien. Du kan deretter bruke denne verdien fremover.

Anta at du har datasettet nedenfor, og du vil beregne EMA for tre perioder:

Skriv inn den samme verdien som i B2 i celle C2. Dette er fordi det ikke er noen tidligere verdi for å beregne EMA.

I celle C3 skriver du inn formelen nedenfor og kopierer for alle cellene:

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

I dette eksemplet har jeg holdt det enkelt og brukt den siste verdien og forrige EMA -verdi for å beregne gjeldende EMA.

En annen populær måte å gjøre dette på er ved først å beregne det enkle glidende gjennomsnittet og deretter bruke det i stedet for den faktiske siste verdien.

Legge til glidende gjennomsnittlig trendlinje i et kolonnediagram

Hvis du har et datasett og du lager et stolpediagram med det, kan du også legge til trendlinjen for glidende gjennomsnitt med noen få klikk.

Anta at du har et datasett som vist nedenfor:

Nedenfor er trinnene for å lage et stolpediagram med disse dataene og legge til en tredelt glidende gjennomsnittlig trendlinje til dette diagrammet:

1. Velg datasettet (inkludert overskriftene)
2. Klikk på Sett inn -fanen
3. I diagramgruppen klikker du på ikonet "Sett inn kolonne eller stolpediagram".
4. Klikk på alternativet Clustered Column chart. Dette vil sette inn diagrammet i regnearket.
5. Når diagrammet er valgt, klikker du på kategorien Design (denne kategorien vises bare når diagrammet er valgt)
6. I diagramoppsettgruppen klikker du på "Legg til diagramelement".
7. Hold markøren på alternativet "Trendline", og klikk deretter på "More Trendline Options"
8. I vinduet Format trendlinje, velg alternativet "Glidende gjennomsnitt" og angi antall perioder.

Det er det! Trinnene ovenfor vil legge til en bevegelig trendlinje i kolonnediagrammet.

Hvis du vil sette inn mer enn én glidende gjennomsnittlig trendlinje (for eksempel en for 2 perioder og en for 3 perioder), gjenta trinnene fra 5 til 8).

Du kan også bruke de samme trinnene til å sette inn en trendlinje i glidende gjennomsnitt i et linjediagram.

Formatering av glidende gjennomsnittlig trendlinje

I motsetning til et vanlig linjediagram tillater ikke en glidende gjennomsnittlig trendlinje mye formatering. For eksempel, hvis du vil markere et bestemt datapunkt på trendlinjen, vil du ikke kunne gjøre det.

Noen få ting du kan formatere i trendlinjen inkluderer:

• Farge av linjen. Du kan bruke dette til å markere en av trendlinjene ved å gjøre alt i diagrammet lyst i farger og få trendlinjen til å komme ut med en lys farge
• De tykkelse av linjen
• De åpenhet av linjen

For å formatere glidende gjennomsnittlig trendlinje, høyreklikk på den og velg deretter Format Trendline-alternativet.

Dette åpner Format Trendline -ruten til høyre. Denne ruten som alle formateringsalternativene (i forskjellige seksjoner - Fill & Line, Effects og Trendline Options).