Liikuva keskmise arvutamine Excelis (lihtne, kaalutud ja eksponentsiaalne)

Nagu paljud minu Exceli õpetused, on ka see inspireeritud ühest sõbralt saadud päringust. Ta tahtis Excelis liikuva keskmise arvutada ja ma palusin tal seda veebist otsida (või vaadata selle kohta YouTube'i videot).

Siis aga otsustasin ühe ise kirjutada (see, et olin ülikoolis mõnevõrra statistikahull, mängis samuti väikest rolli).

Nüüd, enne kui räägin teile, kuidas Excelis libisevat keskmist arvutada, lubage mul kiiresti anda teile ülevaade liikuva keskmise tähendusest ja sellest, milliseid liikuvaid keskmisi seal on.

Kui soovite hüpata selle osa juurde, kus ma näitan, kuidas Excelis libisevat keskmist arvutada, klõpsake siin.

Märkus. Ma ei ole statistika ekspert ja minu eesmärk selles õpetuses ei ole hõlmata kõike liikuva keskmise kohta. Eesmärk on näidata teile ainult seda, kuidas Excelis liikuvaid keskmisi arvutada (koos lühitutvustusega, mida liikuvad keskmised tähendavad).

Mis on liikuva keskmine?

Olen kindel, et teate, mis on keskmine väärtus.

Kui mul on kolme päeva päevased temperatuuriandmed, saate mulle hõlpsalt öelda viimase kolme päeva keskmise (vihje: selleks saate Excelis kasutada funktsiooni KESKMINE).

Libisev keskmine (nimetatakse ka jooksvaks keskmiseks või jooksvaks keskmiseks) on see, kui hoiate keskmise ajavahemiku samaks, kuid liigute pidevalt uute andmete lisamisel.

Näiteks 3. päeval, kui ma küsin teilt 3 päeva libisevat keskmist temperatuuri, annate mulle 1., 2. ja 3. päeva keskmise temperatuuri väärtuse. Ja kui 4. päeval küsin teilt kolme päeva libisevat keskmist temperatuuri , annate mulle 2., 3. ja 4. päeva keskmise.

Uute andmete lisamisel hoiate ajavahemiku (3 päeva) samaks, kuid liikuva keskmise arvutamiseks kasutate uusimaid andmeid.

Liikuvat keskmist kasutatakse tehniliseks analüüsiks palju ning paljud pangad ja börsianalüütikud kasutavad seda igapäevaselt (allpool on näide, mille sain saidilt Market Realist).

Üks libisevate keskmiste kasutamise eeliseid on see, et see annab teile trendi ja ühtlustab kõikumisi teatud määral. Näiteks juhul, kui on tõesti kuum päev, veenduks kolme päeva libisev keskmine temperatuur siiski selle keskmise väärtuse silumisel (selle asemel, et näidata teile tõeliselt kõrget väärtust, mis võib olla kõrvalekalle- üks eksemplarist välja).

Liuguvate keskmiste liigid

Liikuvaid keskmisi on kolme tüüpi:

• Lihtne libisev keskmine (SMA)
• Kaalutud libisev keskmine (WMA)
• Eksponentsiaalne libisev keskmine (EMA)

Lihtne liikuva keskmine (SMA)

See on antud ajavahemiku andmepunktide lihtne keskmine.

Meie igapäevase temperatuuri näites, kui võtate lihtsalt viimase 10 päeva keskmise, annab see 10-päevase liikuva keskmise.

Seda on võimalik saavutada andmepunktide keskmistamisega antud aja jooksul. Excelis saate seda hõlpsalt teha funktsiooni KESKMINE abil (seda käsitletakse käesolevas õpetuses hiljem).

Kaalutud libisev keskmine (WMA)

Oletame, et ilmad lähevad iga päevaga jahedamaks ja kasutate temperatuuritrendi saamiseks 10 päeva libisevat keskmist.

10. päeva temperatuur on tõenäolisem trendi parem näitaja võrreldes 1. päevaga (kuna temperatuur langeb iga päevaga).

Seega on meil parem, kui loodame rohkem 10. päeva väärtusele.

Selleks, et see kajastuks meie libisevas keskmises, saate anda uusimatele andmetele rohkem kaalu ja varasematele andmetele vähem. Sel moel saate endiselt trendi, kuid viimastel andmetel on suurem mõju.

Seda nimetatakse kaalutud libisevaks keskmiseks.

Eksponentsiaalne libisev keskmine (EMA)

Eksponentsiaalne libisev keskmine on kaalutud libiseva keskmise tüüp, kus uusimatele andmetele antakse rohkem kaalu ja see langeb eksponentsiaalselt vanemate andmepunktide puhul.

Seda nimetatakse ka eksponentsiaalseks kaalutud liikuvaks keskmiseks (EWMA)

WMA ja EMA erinevus seisneb selles, et WMA abil saate määrata kaalud mis tahes kriteeriumide alusel. Näiteks kolme punkti libiseva keskmise puhul saate määrata 60% kaaluvanuse viimasele andmepunktile, 30% keskmisele andmepunktile ja 10% vanimale andmepunktile.

EMA -s antakse uusimale väärtusele suurem kaal ja varasemate väärtuste korral jääb kaal pidevalt hüppeliselt madalamaks.

Piisab statistika loengust.

Nüüd sukeldume ja vaatame, kuidas Excelis liikuvaid keskmisi arvutada.

Lihtsa liikuva keskmise (SMA) arvutamine Exceli andmeanalüüsi tööriistakomplekti abil

Microsoft Excelil on juba sisseehitatud tööriist lihtsate liikuva keskmise arvutamiseks.

Seda nimetatakse Andmeanalüüsi tööriistakomplekt.

Enne andmeanalüüsi tööriistakomplekti kasutamist peate kõigepealt kontrollima, kas see on Exceli lindil või mitte. On suur võimalus, et peate esmalt selle lubamiseks tegema mõned sammud.

Kui teil on vahekaardil Andmed juba suvand Andmeanalüüs, jätke allolevad sammud vahele ja vaadake liikuva keskmise arvutamise samme.

Klõpsake vahekaarti Andmed ja kontrollige, kas näete suvandit Andmeanalüüs või mitte. Kui te seda ei näe, järgige allolevaid samme, et see lindil kättesaadavaks teha.

1. Klõpsake vahekaarti Fail
2. Klõpsake suvandil Valikud
3. Klõpsake dialoogiboksis Exceli suvandid nuppu Lisandmoodulid
4. Valige dialoogiboksi allservas rippmenüüst Exceli lisandmoodulid ja seejärel klõpsake nuppu Mine.
5. Avanevas lisandmoodulite dialoogiboksis märkige valik Analysis Toolpak
6. Klõpsake nuppu OK.

Ülaltoodud sammud võimaldaksid andmeanalüüsi tööriistakomplekti ja näete seda valikut vahekaardil Andmed.

Oletame, et teil on allpool näidatud andmekogum ja soovite arvutada kolme viimase intervalli libiseva keskmise.

Allpool on toodud sammud lihtsa libiseva keskmise arvutamiseks andmeanalüüsi kasutamiseks.

1. Klõpsake vahekaarti Andmed
2. Klõpsake suvandil Andmeanalüüs
3. Dialoogiboksis Andmeanalüüs klõpsake suvandil Liikuva keskmise (selle saavutamiseks peate võib -olla natuke kerima).
4. Klõpsake nuppu OK. See avab dialoogiboksi „Liikuv keskmine”.
5. Sisendvahemikus valige andmed, mille jaoks soovite libiseva keskmise arvutada (antud näites B2: B11)
6. Valikus Intervall sisestage 3 (kuna arvutame kolme punkti libisevat keskmist)
7. Väljundi vahemikus sisestage lahter, kuhu soovite tulemusi. Selles näites kasutan väljundvahemikuna C2
8. Klõpsake nuppu OK

Ülaltoodud sammud annaksid teile libiseva keskmise tulemuse, nagu allpool näidatud.

Pange tähele, et veeru C kahe esimese lahtri tulemus on #N/A viga. Seda seetõttu, et see on kolmepunktiline libisev keskmine ja esimese tulemuse saamiseks on vaja vähemalt kolme andmepunkti. Seega algavad tegelikud liikuva keskmise väärtused pärast kolmandat andmepunkti.

Samuti märkate, et kogu see andmeanalüüsi tööriistakomplekt on rakendanud lahtritele KESKMISE valemi. Nii et kui soovite seda teha käsitsi ilma andmeanalüüsi tööriistakomplektita, saate seda kindlasti teha.

Siiski on mõningaid asju, mida on andmeanalüüsi tööriistakomplektiga lihtsam teha. Näiteks kui soovite saada standardvea väärtust ja liikuva keskmise diagrammi, peate vaid märkima ruudu ja see on väljundi osa.

Liikuva keskmise arvutamine (SMA, WMA, EMA) Exceli valemite abil

Liikuvaid keskmisi saate arvutada ka KESKMISE valemi abil.

Tegelikult, kui vajate ainult libisevat keskmist väärtust (mitte standardviga või diagrammi), võib valemi kasutamine olla parem (ja kiirem) valik kui andmeanalüüsi tööriistakomplekti kasutamine.

Samuti annab andmeanalüüsi tööriistapakett ainult liikuva liikuva keskmise (SMA), kuid kui soovite arvutada WMA või EMA, peate tuginema ainult valemitele.

Lihtsa liikuva keskmise arvutamine valemite abil

Oletame, et teil on allpool näidatud andmekogum ja soovite arvutada 3-punktilise SMA:

Lahtrisse C4 sisestage järgmine valem:

= KESKMINE (B2: B4)

Kopeerige see valem kõigi lahtrite jaoks ja see annab teile SMA iga päeva kohta.

Pidage meeles: valemite abil SMA arvutamisel peate veenduma, et valemil olevad viited on suhtelised. See tähendab, et valem võib olla = KESKMINE (B2: B4) või = KESKMINE ($ B2: $ B4), kuid see ei saa olla = KESKMINE ($ B $ 2: $ B $ 4) või = KESKMINE (B $ 2: B $ 4) ). Viite rea number peab olema ilma dollarimärgita. Siit saate lugeda absoluutsete ja suhteliste viidete kohta.

Kuna arvutame 3-punktilist lihtsat liikuva keskmist (SMA), on kaks esimest lahtrit (kahe esimese päeva kohta) tühjad ja hakkame valemit kasutama alates kolmandast päevast. Kui soovite, võite kasutada kahte esimest väärtust sellisena, nagu see on, ja kasutada SMA väärtust alates kolmandast.

Kaalutud liikuva keskmise arvutamine valemite abil

WMA puhul peate teadma kaalusid, mis väärtustele omistatakse.

Oletame näiteks, et peate arvutama alloleva andmestiku jaoks 3 -punktilise WMA, kus 60% kaalust antakse viimasele väärtusele, 30% sellele eelnevale ja 10% eelnevale väärtusele.

Selleks sisestage lahtrisse C4 järgmine valem ja kopeerige kõik lahtrid.

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

Kuna arvutame 3-punktilist kaalutud libisevat keskmist (WMA), on kaks esimest lahtrit (kahe esimese päeva kohta) tühjad ja hakkame valemit kasutama alates kolmandast päevast. Soovi korral võite kasutada kahte esimest väärtust sellisena, nagu see on, ja kasutada WMA väärtust alates kolmandast.

Eksponentsiaalse liikuva keskmise arvutamine valemite abil

Eksponentsiaalne libisev keskmine (EMA) annab viimasele väärtusele suurema kaalu ja varasemate väärtuste puhul väheneb kaal pidevalt eksponentsiaalselt.

Allpool on valem kolme punkti liikuva keskmise EMA arvutamiseks:

EMA = [Viimane väärtus - eelmine EMA väärtus] * (2 / N + 1) + eelmine EMA

… Kus N oleks selles näites 3 (kuna arvutame kolmepunktilist EMA-d)

Märkus. Esimese EMA väärtuse puhul (kui teil pole varasemat väärtust EMA arvutamiseks) võtke lihtsalt väärtus sellisena, nagu see on, ja lugege seda EMA väärtuseks. Seejärel saate seda väärtust edaspidi kasutada.

Oletame, et teil on järgmine andmekogum ja soovite arvutada kolme perioodi EMA:

Lahtrisse C2 sisestage sama väärtus, mis lahtris B2. Seda seetõttu, et EMA arvutamiseks puudub varasem väärtus.

Lahtrisse C3 sisestage järgmine valem ja kopeerige kõik lahtrid:

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

Selles näites olen seda lihtsana hoidnud ja kasutanud praeguse EMA arvutamiseks viimast väärtust ja eelmist EMA väärtust.

Teine populaarne viis seda teha on kõigepealt arvutada lihtliikuv keskmine ja seejärel kasutada seda tegeliku viimase väärtuse asemel.

Liikuva keskmise trendijoone lisamine veergdiagrammile

Kui teil on andmestik ja loote selle abil tulpdiagrammi, saate mõne klõpsuga lisada ka liikuva keskmise trendijoone.

Oletame, et teil on allpool näidatud andmekogum:

Allpool on toodud sammud tulpdiagrammi loomiseks, kasutades neid andmeid ja lisades sellele diagrammile kolmeosalise liikuva keskmise trendijoone:

1. Valige andmekogum (sh päised)
2. Klõpsake vahekaarti Lisa
3. Klõpsake diagrammirühmas ikooni „Lisa veerg või tulpdiagramm”.
4. Klõpsake suvandit Rühmitatud veeru diagramm. See lisab diagrammi töölehele.
5. Kui diagramm on valitud, klõpsake vahekaarti Kujundus (see vahekaart kuvatakse ainult siis, kui diagramm on valitud)
6. Klõpsake rühmas Diagrammi paigutused nuppu „Lisa diagrammi element”.
7. Hõljutage kursorit valikul „Trendline” ja seejärel klõpsake „Rohkem trendijoone valikuid”
8. Paanil Trendline vormindamine valige suvand „Liikuv keskmine” ja määrake perioodide arv.

See on kõik! Ülaltoodud sammud lisavad teie veerudiagrammile liikuva trendijoone.

Kui soovite sisestada rohkem kui ühe liikuva keskmise trendijoone (näiteks ühe kahe perioodi ja ühe kolme perioodi kohta), korrake samme 5 kuni 8).

Samade toimingute abil saate liikuva keskmise trendijoone lisada ka joondiagrammile.

Liikuva keskmise trendijoone vormindamine

Erinevalt tavalisest joondiagrammist ei võimalda liikuva keskmise trendijoon palju vormindamist. Näiteks kui soovite trendijoonel teatud andmepunkti esile tõsta, ei saa te seda teha.

Mõned asjad, mida saate trendijoones vormindada, on järgmised.

• Värv liinist. Seda saate kasutada ühe trendijoone esiletõstmiseks, muutes kõik diagrammil heledad värvid ja muutes trendijoone erksaks
• paksus liinist
• läbipaistvus liinist

Liikuva keskmise trendijoone vormindamiseks paremklõpsake sellel ja valige suvand Vorminda trendijoon.

See avab paremal paani Vormindusjoon. Sellel paanil on kõik vormindamisvalikud (erinevates jaotistes - täitmine ja jooned, efektid ja suundjoone valikud).