Liukuvan keskiarvon laskeminen Excelissä (yksinkertainen, painotettu ja eksponentiaalinen)

Kuten monet Excel -opetusohjelmat, tämäkin on saanut inspiraationsa eräästä ystävältäni saamastani kyselystä. Hän halusi laskea liukuvan keskiarvon Excelissä, ja pyysin häntä etsimään sitä verkosta (tai katsomaan siitä YouTube -videon).

Mutta sitten päätin kirjoittaa yhden (se, että olin jonkin verran tilasto -nörtti yliopistossa, oli myös vähäinen rooli).

Ennen kuin kerron kuinka lasketaan liukuva keskiarvo Excelissä, haluan antaa teille nopeasti yleiskuvan siitä, mitä liukuva keskiarvo tarkoittaa ja millaisia ​​liikkuvia keskiarvoja siellä on.

Jos haluat siirtyä osaan, jossa näytän kuinka lasketaan liukuva keskiarvo Excelissä, napsauta tätä.

Huomautus: En ole tilastojen asiantuntija, eikä tämän opetusohjelman tarkoitus ole kattaa kaikkea liikkuvista keskiarvoista. Haluan vain näyttää sinulle, kuinka lasketaan liukuvat keskiarvot Excelissä (esittelemällä lyhyesti, mitä liukuvat keskiarvot tarkoittavat).

Mikä on liukuva keskiarvo?

Tiedät varmasti, mikä on keskiarvo.

Jos minulla on kolmen päivän päivittäiset lämpötilatiedot, voit helposti kertoa minulle viimeisten kolmen päivän keskiarvon (vihje: voit tehdä tämän Excelin KESKI -funktion avulla).

Liukuva keskiarvo (jota kutsutaan myös liukuvaksi keskiarvoksi tai juoksevaksi keskiarvoksi) on, kun pidät keskiarvon ajanjakson samana, mutta pysyt liikkeellä uusien tietojen lisäämisen yhteydessä.

Esimerkiksi päivänä 3, jos pyydän sinulta kolmen päivän liukuvaa keskilämpötilaa, annat minulle päivien 1, 2 ja 3 keskimääräisen lämpötila-arvon. Ja jos päivänä 4 kysyn sinulta kolmen päivän liukuvaa keskilämpötilaa , annat minulle päivien 2, 3 ja 4 keskiarvon.

Kun uusia tietoja lisätään, pidät ajanjakson (3 päivää) samana, mutta lasket liukuvan keskiarvon uusimpien tietojen perusteella.

Liukuvaa keskiarvoa käytetään voimakkaasti tekniseen analyysiin, ja monet pankit ja osakemarkkinoiden analyytikot käyttävät sitä päivittäin (alla on esimerkki, jonka sain Market Realist -sivustolta).

Yksi liukuvien keskiarvojen käytön eduista on se, että saat trendin ja tasoitat vaihtelut jossain määrin. Esimerkiksi jos on todella kuuma päivä, lämpötilan kolmen päivän liukuva keskiarvo varmistaisi silti, että keskiarvo on tasoitettu (sen sijaan, että näyttäisit sinulle todella korkean arvon, joka voi olla poikkeava- yksi pois tapauksesta).

Liukuvien keskiarvojen tyypit

Liukuvia keskiarvoja on kolme tyyppiä:

• Yksinkertainen liukuva keskiarvo (SMA)
• Painotettu liukuva keskiarvo (WMA)
• Eksponentiaalinen liukuva keskiarvo (EMA)

Yksinkertainen liukuva keskiarvo (SMA)

Tämä on tietyn keston datapisteiden yksinkertainen keskiarvo.

Päivittäisen lämpötilan esimerkissämme, kun otat vain viimeisten 10 päivän keskiarvon, se antaa 10 päivän yksinkertaisen liukuvan keskiarvon.

Tämä voidaan saavuttaa laskemalla tietopisteiden keskiarvo tietyn ajan kuluessa. Excelissä voit tehdä tämän helposti käyttämällä KESKI -funktiota (tämä käsitellään myöhemmin tässä opetusohjelmassa).

Painotettu liukuva keskiarvo (WMA)

Oletetaan, että sää muuttuu viileämmäksi joka päivä ja käytät 10 päivän liukuvaa keskiarvoa saadaksesi lämpötilan trendin.

Päivän 10 lämpötila on todennäköisemmin parempi indikaattori trendistä kuin päivä 1 (koska lämpötila laskee joka päivä).

Joten meillä on parempi, jos luotamme enemmän 10. päivän arvoon.

Jotta tämä heijastuisi liukuvaan keskiarvoomme, voit antaa enemmän painoarvoa uusimmille tiedoille ja vähemmän aiemmille tiedoille. Näin saat edelleen trendin, mutta viimeisimpien tietojen vaikutus on suurempi.

Tätä kutsutaan painotetuksi liukuvaksi keskiarvoksi.

Eksponentiaalinen liukuva keskiarvo (EMA)

Eksponentiaalinen liukuva keskiarvo on eräänlainen painotettu liukuva keskiarvo, jossa uusille tiedoille annetaan enemmän painoarvoa ja se laskee eksponentiaalisesti vanhempien datapisteiden osalta.

Sitä kutsutaan myös eksponentiaaliseksi painotetuksi liukuvaksi keskiarvoksi (EWMA)

Ero WMA: n ja EMA: n välillä on se, että WMA: n avulla voit määrittää painot minkä tahansa kriteerin perusteella. Esimerkiksi 3-pisteen liukuvassa keskiarvossa voit määrittää 60%: n painoarvon viimeisimmälle datapisteelle, 30% keskimmäiselle datapisteelle ja 10% vanhimmalle datapisteelle.

EMA: ssa suurempi paino annetaan viimeisimmälle arvolle ja paino laskee jatkuvasti eksponentiaalisesti aiempien arvojen osalta.

Tilastoluento riittää.

Katsotaanpa nyt, miten lasketaan liukuvat keskiarvot Excelissä.

Yksinkertaisen liukuvan keskiarvon (SMA) laskeminen Excelin Data Analysis Toolpakin avulla

Microsoft Excelissä on jo sisäänrakennettu työkalu yksinkertaisten liukuvien keskiarvojen laskemiseen.

Sitä kutsutaan Data Analysis Toolpak.

Ennen kuin voit käyttää Data Analysis Toolpakkia, sinun on ensin tarkistettava, onko se Excel -valintanauhassa vai ei. On hyvä mahdollisuus, että sinun on suoritettava muutama vaihe sen käyttöön ottamiseksi.

Jos sinulla on jo Data Analysis -vaihtoehto Data -välilehdessä, ohita alla olevat vaiheet ja katso liukuvien keskiarvojen laskemiseen liittyvät vaiheet.

Napsauta Data -välilehteä ja tarkista, näkyykö Data Analysis -vaihtoehto vai ei. Jos et näe sitä, tee se saatavana nauhassa noudattamalla alla olevia ohjeita.

1. Napsauta Tiedosto -välilehteä
2. Napsauta Asetukset
3. Napsauta Excel-asetukset-valintaikkunassa Lisäosat
4. Valitse valintaikkunan alaosasta avattavasta valikosta Excel-apuohjelmat ja napsauta sitten Siirry.
5. Valitse avautuvasta Lisäosat-valintaikkunasta Analysis Toolpak -vaihtoehto
6. Napsauta OK.

Yllä olevat vaiheet mahdollistavat Data Analysis Toolpackin ja näet tämän vaihtoehdon Data -välilehdessä nyt.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat laskea kolmen viimeisen aikavälin liukuvan keskiarvon.

Alla on vaiheet, joiden avulla voit laskea yksinkertaisen liukuvan keskiarvon data -analyysin avulla:

1. Napsauta Data -välilehteä
2. Napsauta Data Analysis -vaihtoehtoa
3. Napsauta Tietojen analysointi -valintaikkunassa Liukuva keskiarvo -vaihtoehtoa (sinun on ehkä vieritettävä hiukan sen saavuttamiseksi).
4. Napsauta OK. Tämä avaa "Liukuva keskiarvo" -valintaikkunan.
5. Valitse syöttöalueelta tiedot, joille haluat laskea liukuvan keskiarvon (B2: B11 tässä esimerkissä)
6. Syötä Intervalli-vaihtoehtoon 3 (laskemme kolmen pisteen liukuvaa keskiarvoa)
7. Kirjoita Lähtöalueelle solu, johon haluat tulokset. Tässä esimerkissä käytän lähtöalueena C2: ta
8. Napsauta OK

Yllä olevat vaiheet antavat sinulle liukuvan keskimääräisen tuloksen, kuten alla on esitetty.

Huomaa, että sarakkeen C kahden ensimmäisen solun tulos on #N/A -virhe. Tämä johtuu siitä, että se on kolmen pisteen liukuva keskiarvo ja tarvitsee vähintään kolme datapistettä ensimmäisen tuloksen antamiseksi. Todelliset liukuvat keskiarvot alkavat siis kolmannen datapisteen jälkeen.

Huomaat myös, että kaikki tämä tietojen analysointityökalupakki on tehnyt soluihin keskimääräisen kaavan. Joten jos haluat tehdä tämän manuaalisesti ilman Data Analysis -työkalupakettia, voit varmasti tehdä sen.

On kuitenkin muutamia asioita, jotka on helpompi tehdä tietojen analysointityökalupaketin avulla. Jos esimerkiksi haluat saada vakiovirhearvon ja liukuvan keskiarvon kaavion, sinun tarvitsee vain valita ruutu ja se on osa tulosta.

Liukuvien keskiarvojen (SMA, WMA, EMA) laskeminen Excelin kaavojen avulla

Voit myös laskea liukuvat keskiarvot käyttämällä KESKI -kaavaa.

Itse asiassa, jos tarvitset vain liukuvan keskiarvon (eikä vakiovirheen tai kaavion), kaavan käyttäminen voi olla parempi (ja nopeampi) vaihtoehto kuin Data Analysis Toolpakin käyttö.

Lisäksi tietojen analysointityökalupakki antaa vain yksinkertaisen liukuvan keskiarvon (SMA), mutta jos haluat laskea WMA- tai EMA -arvon, sinun on luotettava vain kaavoihin.

Yksinkertaisen liukuvan keskiarvon laskeminen kaavojen avulla

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat laskea kolmen pisteen SMA: n:

Kirjoita soluun C4 seuraava kaava:

= KESKIMÄÄRÄINEN (B2: B4)

Kopioi tämä kaava kaikille soluille ja se antaa sinulle SMA: n jokaiselle päivälle.

Muista: Kun lasket SMA: ta kaavojen avulla, sinun on varmistettava, että kaavan viittaukset ovat suhteellisia. Tämä tarkoittaa, että kaava voi olla = KESKI (B2: B4) tai = KESKI ($ B2: $ B4), mutta se ei voi olla = KESKI ($ B $ 2: $ B $ 4) tai = KESKI (B $ 2: B $ 4) ). Viittauksen rivinumeron osan on oltava ilman dollarimerkkiä. Voit lukea lisää absoluuttisista ja suhteellisista viitteistä täältä.

Koska laskemme 3-pisteen yksinkertaista liukuvaa keskiarvoa (SMA), kaksi ensimmäistä solua (kahden ensimmäisen päivän aikana) ovat tyhjiä ja aloitamme kaavan käytön kolmannesta päivästä alkaen. Jos haluat, voit käyttää kahta ensimmäistä arvoa sellaisenaan ja käyttää SMA -arvoa kolmannesta lähtien.

Painotetun liukuvan keskiarvon laskeminen kaavojen avulla

WMA: n osalta sinun on tiedettävä painot, jotka annettaisiin arvoille.

Oletetaan esimerkiksi, että sinun on laskettava 3 pisteen WMA alla olevalle tietojoukolle, jossa 60% paino annetaan viimeisimmälle arvolle, 30% sitä edeltävälle arvolle ja 10% sitä edeltävälle arvolle.

Kirjoita seuraava kaava soluun C4 ja kopioi kaikki solut.

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

Koska laskemme 3-pisteistä painotettua liukuvaa keskiarvoa (WMA), kaksi ensimmäistä solua (kahden ensimmäisen päivän aikana) ovat tyhjiä ja aloitamme kaavan käytön kolmannesta päivästä alkaen. Jos haluat, voit käyttää kahta ensimmäistä arvoa sellaisenaan ja käyttää WMA -arvoa kolmannesta lähtien.

Eksponentiaalisen liukuvan keskiarvon laskeminen kaavojen avulla

Eksponentiaalinen liukuva keskiarvo (EMA) antaa korkeamman painon viimeisimmälle arvolle ja painot laskevat jatkuvasti eksponentiaalisesti aiempien arvojen osalta.

Alla on kaava, jolla lasketaan kolmen pisteen liukuvan keskiarvon EMA:

EMA = [Viimeisin arvo - edellinen EMA -arvo] * (2 / N + 1) + edellinen EMA

… Jossa N olisi tässä esimerkissä 3 (laskettaessa kolmen pisteen EMA-arvoa)

Huomaa: Ensimmäisen EMA -arvon (jos sinulla ei ole aiempaa arvoa EMA: n laskemista varten) varten yksinkertaisesti ota arvo sellaisenaan ja pidä sitä EMA -arvona. Voit sitten käyttää tätä arvoa eteenpäin.

Oletetaan, että sinulla on alla oleva tietojoukko ja haluat laskea kolmen jakson EMA: n:

Kirjoita soluun C2 sama arvo kuin kohtaan B2. Tämä johtuu siitä, että EMA: n laskemiseen ei ole aikaisempaa arvoa.

Kirjoita soluun C3 alla oleva kaava ja kopioi kaikki solut:

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

Tässä esimerkissä olen pitänyt asian yksinkertaisena ja laskenut nykyisen EMA: n uusimman arvon ja edellisen EMA -arvon.

Toinen suosittu tapa tehdä tämä on ensin laskea yksinkertainen liukuva keskiarvo ja käyttää sitä sen jälkeen todellisen viimeisimmän arvon sijasta.

Liukuvan keskiarvon trendiviivan lisääminen sarakekaavioon

Jos sinulla on tietojoukko ja luot pylväskaaviota sen avulla, voit myös lisätä liukuvan keskiarvon trendiviivan muutamalla napsautuksella.

Oletetaan, että sinulla on alla näkyvä tietojoukko:

Alla on vaiheet pylväskaavion luomiseksi näiden tietojen avulla ja kolmiosaisen liukuvan keskiarvon trendiviivan lisääminen tähän kaavioon:

1. Valitse tietojoukko (mukaan lukien otsikot)
2. Napsauta Lisää -välilehteä
3. Napsauta Kaavio -ryhmässä Lisää sarake- tai pylväskaavio -kuvaketta.
4. Napsauta Clustered Column chart -vaihtoehtoa. Tämä lisää kaavion laskentataulukkoon.
5. Kun kaavio on valittu, napsauta Suunnittelu -välilehteä (tämä välilehti näkyy vain, kun kaavio on valittu)
6. Napsauta Kaavion asettelut -ryhmässä Lisää kaavion elementti.
7. Vie hiiri Trendline -vaihtoehdon kohdalle ja napsauta sitten Lisää Trendline -vaihtoehtoja
8. Valitse Muotoile trendiviiva -ruudussa "Liukuva keskiarvo" -vaihtoehto ja aseta jaksojen määrä.

Se siitä! Yllä olevat vaiheet lisäisivät sarakekaavioon liikkuvan trendiviivan.

Jos haluat lisätä useamman kuin yhden liukuvan keskiarvon trendiviivan (esimerkiksi yhden 2 jaksolle ja yhden 3 jaksolle), toista vaiheet 5–8).

Voit käyttää samoja vaiheita myös liukuvan keskiarvon trendiviivan lisäämiseen viivakaavioon.

Liukuvan keskiarvon trendirivin muotoilu

Toisin kuin tavallinen viivakaavio, liukuva keskiarvo -trendiviiva ei salli paljon muotoilua. Jos esimerkiksi haluat korostaa tietyn datapisteen trendiviivalla, et voi tehdä sitä.

Muutamia asioita, joita voit muotoilla trendilinjalla, ovat:

• Väri linjasta. Voit käyttää tätä korostaaksesi yhden trendiviivoista tekemällä kaikki kaavion vaaleat värit ja tekemällä trendilinjan ponnahdusikkunan kirkkaaksi
• paksuus linjasta
• avoimuutta linjasta

Jos haluat muotoilla liukuvan keskiarvon trendiviivan, napsauta sitä hiiren kakkospainikkeella ja valitse sitten Muotoile trendiviiva.

Tämä avaa Muotoile trendiviiva -ruudun oikealla puolella. Tämä ruutu on kaikki muotoiluvaihtoehdot (eri osioissa - Täyttö ja viiva, Tehosteet ja Trendline -asetukset).