Izračunavanje pokretnog prosjeka u Excelu (jednostavno, ponderirano i eksponencijalno)

Kao i mnogi moji vodiči u Excelu, i ovaj je inspiriran jednim od upita koje sam dobio od prijatelja. Htjela je izračunati pokretni prosjek u Excelu, a ja sam je zamolio da je potraži na internetu (ili pogleda video o tome na YouTubeu).

No, onda sam odlučio sam napisati jedan (činjenica da sam pomalo bio statistički štreber na fakultetu također je odigrala manju ulogu).

Prije nego što vam kažem kako izračunati pokretni prosjek u Excelu, dopustite mi da vam na brzinu dam pregled onoga što znači pokretni prosjek i koje vrste pokretnih prosjeka postoje.

U slučaju da želite prijeći na dio gdje pokazujem kako izračunati pokretni prosjek u Excelu, kliknite ovdje.

Napomena: Nisam stručnjak za statistiku i moja namjera u ovom vodiču nije pokriti sve o pokretnim prosjecima. Cilj mi je samo pokazati vam kako izračunati pokretne prosjeke u Excelu (s kratkim uvodom u to što znače pokretni prosjeci).

Što je pokretni prosjek?

Siguran sam da znate koja je prosječna vrijednost.

Ako imam podatke o dnevnoj temperaturi za tri dana, možete mi jednostavno reći prosjek posljednja tri dana (savjet: za to možete koristiti funkciju PROSJEČNO u Excelu).

Pokretni prosjek (koji se naziva i pokretni prosjek ili tekući prosjek) je kada zadržite razdoblje prosjeka istim, ali se nastavite kretati kako se dodaju novi podaci.

Na primjer, 3. dana, ako vas pitam trodnevnu pomičnu prosječnu temperaturu, dat ćete mi prosječnu vrijednost temperature 1., 2. i 3. dana. A ako vas 4. dana pitam trodnevnu pomičnu prosječnu temperaturu , dati ćete mi prosjek 2., 3. i 4. dana.

Kako se dodaju novi podaci, vremensko razdoblje (3 dana) ostaje isto, ali za izračunavanje pomičnog prosjeka koristite najnovije podatke.

Pokretni prosjek uvelike se koristi za tehničku analizu, a mnoge banke i analitičari burze ga koriste svakodnevno (u nastavku je primjer koji sam dobio sa stranice Market Realist).

Jedna od prednosti korištenja pokretnih prosjeka je ta što vam daje trend, kao i u određenoj mjeri ublažava fluktuacije. Na primjer, u slučaju da je dan zaista vruć, trodnevni pomični prosjek temperature i dalje će osigurati da je prosječna vrijednost izglađena (umjesto da vam pokaže stvarno visoku vrijednost koja bi mogla biti izvanredna- jedno- izvan instance).

Vrste pokretnih prosjeka

Postoje tri vrste pokretnih prosjeka:

• Jednostavan pokretni prosjek (SMA)
• Ponderirani pokretni prosjek (WMA)
• Eksponencijalni pokretni prosjek (EMA)

Jednostavni pomični prosjek (SMA)

Ovo je jednostavan prosjek podatkovnih točaka u zadanom trajanju.

U našem primjeru dnevne temperature, kada jednostavno uzmete prosjek zadnjih 10 dana, on daje 10-dnevni jednostavan pokretni prosjek.

To se može postići prosjekom podatkovnih točaka u zadanom trajanju. U Excelu to možete učiniti jednostavno pomoću funkcije PROSJEK (to će biti obrađeno kasnije u ovom vodiču).

Ponderirani pokretni prosjek (WMA)

Recimo da je vrijeme sve hladnije sa svakim danom i da koristite 10-dnevni pomični prosjek da biste dobili trend temperature.

Temperatura 10. dana vjerojatnije će biti bolji pokazatelj trenda u odnosu na 1. dan (budući da temperatura pada svakim danom).

Dakle, bolje nam je ako se više oslanjamo na vrijednost 10. dana.

Kako bi se to odrazilo na naš pokretni prosjek, možete dati veću težinu najnovijim podacima, a manje prošlim. Na ovaj način i dalje dobivate trend, ali s većim utjecajem najnovijih podataka.

To se naziva ponderirani pokretni prosjek.

Eksponencijalni pomični prosjek (EMA)

Eksponencijalni pokretni prosjek je vrsta ponderiranog pokretnog prosjeka gdje se posljednjim podacima daje veća težina, a za starije podatkovne točke eksponencijalno se smanjuje.

Naziva se i eksponencijalno ponderirani pomični prosjek (EWMA)

Razlika između WMA i EMA je u tome što s WMA -om možete dodijeliti težine na temelju bilo kojeg kriterija. Na primjer, u pokretnom prosjeku s 3 točke možete najnovijoj podatkovnoj točki dodijeliti dobnu težinu od 60%, srednjoj podatkovnoj točki 30%, a najstarijoj podatkovnoj točki 10%.

U EMA -i se posljednjoj vrijednosti daje veća težina, a težina se za eksponencijalno smanjuje za ranije vrijednosti.

Dosta je bilo predavanja o statistici.

Hajdemo sada zaroniti i vidjeti kako izračunati pokretne prosjeke u Excelu.

Izračunavanje jednostavnog pomičnog prosjeka (SMA) pomoću alata za analizu podataka u Excelu

Microsoft Excel već ima ugrađen alat za izračunavanje jednostavnih pokretnih prosjeka.

Zove se Paket alata za analizu podataka.

Prije nego počnete koristiti paket alata za analizu podataka, najprije morate provjeriti imate li ga na vrpci programa Excel ili ne. Postoji velika šansa da morate poduzeti nekoliko koraka da biste je prvo omogućili.

U slučaju da već imate opciju Analiza podataka na kartici Podaci, preskočite donje korake i pogledajte korake za izračunavanje pokretnih prosjeka.

Pritisnite karticu Podaci i provjerite vidite li opciju Analiza podataka ili ne. Ako ga ne vidite, slijedite korake u nastavku kako biste ga učinili dostupnim na vrpci.

1. Kliknite karticu Datoteka
2. Pritisnite Opcije
3. U dijaloškom okviru Excel mogućnosti kliknite Dodaci
4. Pri dnu dijaloškog okvira odaberite Excel programske dodatke na padajućem izborniku, a zatim kliknite Idi.
5. U dijaloškom okviru Dodaci koji se otvori provjerite opciju Analiza alata
6. Pritisnite U redu.

Gore navedeni koraci omogućili bi paket alata za analizu podataka, a ovu ćete opciju sada vidjeti na kartici Podaci.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izračunati pomični prosjek posljednja tri intervala.

U nastavku su navedeni koraci za korištenje analize podataka za izračun jednostavnog pomičnog prosjeka:

1. Kliknite karticu Podaci
2. Kliknite na opciju Analiza podataka
3. U dijaloškom okviru Analiza podataka kliknite opciju Pokretni prosjek (možda ćete se morati pomaknuti da biste došli do nje).
4. Pritisnite U redu. Ovo će otvoriti dijaloški okvir "Moving Average".
5. U rasponu unosa odaberite podatke za koje želite izračunati pokretni prosjek (B2: B11 u ovom primjeru)
6. U opciju Interval unesite 3 (jer računamo pokretni prosjek u tri točke)
7. U rasponu izlaza unesite ćeliju u koju želite rezultate. U ovom primjeru koristim C2 kao izlazni raspon
8. Pritisnite U redu

Gore navedeni koraci dali bi vam rezultat pomičnog prosjeka kao što je prikazano u nastavku.

Imajte na umu da prve dvije ćelije u stupcu C imaju rezultat kao #N/A pogreška. To je zato što je to pokretni prosjek s tri točke i potrebne su mu najmanje tri podatkovne točke da bi se dao prvi rezultat. Dakle, stvarne vrijednosti pomičnog prosjeka počinju nakon treće podatkovne točke nadalje.

Također ćete primijetiti da se sve što je ovaj paket alata za analizu podataka učinio primjenjuje PROSJEČNU formulu na ćelije. Dakle, ako to želite učiniti ručno bez alata za analizu podataka, to svakako možete učiniti.

Postoji, međutim, nekoliko stvari koje je lakše učiniti s alatom za analizu podataka. Na primjer, ako želite dobiti standardnu ​​vrijednost pogreške, kao i grafikon pokretnog prosjeka, sve što trebate učiniti je potvrditi okvir i on će biti dio izlaza.

Izračunavanje pokretnih prosjeka (SMA, WMA, EMA) pomoću formula u Excelu

Također možete izračunati pokretne prosjeke koristeći formulu PROSJEK.

Zapravo, ako vam je potrebna samo vrijednost pomičnog prosjeka (a ne standardna pogreška ili grafikon), upotreba formule može biti bolja (i brža) opcija od korištenja paketa alata za analizu podataka.

Također, Paket alata za analizu podataka daje samo jednostavan pomični prosjek (SMA), ali ako želite izračunati WMA ili EMA, morate se osloniti samo na formule.

Izračunavanje jednostavnog pomičnog prosjeka pomoću formula

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite izračunati SMA u 3 točke:

U ćeliju C4 unesite sljedeću formulu:

= PROSJEČNO (B2: B4)

Kopirajte ovu formulu za sve ćelije i ona će vam dati SMA za svaki dan.

Upamtite: Prilikom izračunavanja SMA -a prema formulama morate provjeriti jesu li reference u formuli relativne. To znači da formula može biti = PROSJEČNA (B2: B4) ili = PROSJEČNA ($ B2: $ B4), ali ne može biti = PROSJEČNA ($ B $ 2: $ B $ 4) ili = PROSJEČNA (B $ 2: B $ 4) ). Dio referenci s brojem retka mora biti bez znaka dolara. Ovdje možete pročitati više o apsolutnim i relativnim referencama.

Budući da računamo Jednostavni pomični prosjek u tri točke (SMA), prve dvije ćelije (prva dva dana) su prazne i formulu počinjemo koristiti od trećeg dana nadalje. Ako želite, možete koristiti prve dvije vrijednosti kakve jesu, a vrijednost SMA koristiti od treće pa nadalje.

Izračunavanje ponderiranog pomičnog prosjeka pomoću formula

Za WMA morate znati težine koje bi bile dodijeljene vrijednostima.

Na primjer, pretpostavimo da trebate izračunati WMA s 3 točke za donji skup podataka, gdje se 60% težine daje najnovijoj vrijednosti, 30% onoj prije nje i 10% onoj prije nje.

Da biste to učinili, unesite sljedeću formulu u ćeliju C4 i kopirajte za sve ćelije.

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

Budući da izračunavamo ponderirani pomični prosjek s 3 točke (WMA), prve dvije ćelije (prva dva dana) su prazne i formulu počinjemo koristiti od trećeg dana nadalje. Ako želite, možete koristiti prve dvije vrijednosti kakve jesu, a vrijednost WMA koristiti od treće pa nadalje.

Izračunavanje eksponencijalnog pomičnog prosjeka pomoću formula

Eksponencijalni pomični prosjek (EMA) daje veću težinu najnovijoj vrijednosti, a utezi se eksponencijalno smanjuju za ranije vrijednosti.

Ispod je formula za izračunavanje EMA-e za pokretni prosjek s tri točke:

EMA = [Najnovija vrijednost - prethodna EMA vrijednost] * (2 / N + 1) + prethodna EMA

… Gdje bi N u ovom primjeru bilo 3 (jer računamo EMA u tri točke)

Napomena: Za prvu vrijednost EMA -e (ako nemate prethodnu vrijednost za izračun EMA -e), jednostavno uzmite vrijednost kakva jest i smatrajte je vrijednošću EMA. Zatim možete koristiti ovu vrijednost naprijed.

Pretpostavimo da imate donji skup podataka i želite izračunati troperiodnu EMA-u:

U ćeliju C2 unesite istu vrijednost kao u B2. To je zato što ne postoji prethodna vrijednost za izračun EMA -e.

U ćeliju C3 unesite donju formulu i kopirajte za sve ćelije:

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

U ovom primjeru pojednostavio sam i upotrijebio najnoviju vrijednost i prethodnu vrijednost EMA -e za izračun trenutne EMA -e.

Drugi popularan način za to je prvo izračunavanje jednostavnog pomičnog prosjeka, a zatim ga koristite umjesto stvarne najnovije vrijednosti.

Dodavanje linije trendova pomičnog prosjeka u stupac grafikon

Ako imate skup podataka i pomoću njega stvarate stupčasti grafikon, također možete dodati pokretnu liniju trenda s nekoliko klikova.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku:

U nastavku su navedeni koraci za stvaranje stupčastog grafikona pomoću ovih podataka i dodavanje trodijelne linije trendova pomičnog prosjeka na ovaj grafikon:

1. Odaberite skup podataka (uključujući zaglavlja)
2. Kliknite karticu Umetanje
3. U grupi Grafikon kliknite ikonu "Umetni stupac ili trakasti grafikon".
4. Kliknite opciju grafikona grupiranih stupaca. Ovo će umetnuti grafikon u radni list.
5. Kad je grafikon odabran, kliknite karticu Dizajn (ova se kartica prikazuje samo kada je grafikon odabran)
6. U grupi Rasporedi grafikona kliknite ‘Dodaj element grafikona’.
7. Zadržite pokazivač miša na opciji "Trendline", a zatim kliknite na "More Trendline Options"
8. U oknu Oblikovanje linije trenda odaberite opciju "Pokretni prosjek" i postavite broj točaka.

To je to! Gore navedeni koraci dodali bi pokretnu liniju trenda vašem grafikonu stupaca.

U slučaju da želite umetnuti više od jedne linije trendova pomičnog prosjeka (na primjer jednu za 2 razdoblja i jednu za 3 razdoblja), ponovite korake od 5 do 8).

Iste korake možete koristiti za umetanje pokretne prosječne linije trenda u linijski grafikon.

Oblikovanje linije trenda pomičnog prosjeka

Za razliku od običnog linijskog grafikona, linija trenda pokretnog prosjeka ne dopušta puno oblikovanja. Na primjer, ako želite istaknuti određenu podatkovnu točku na liniji trenda, to nećete moći učiniti.

Nekoliko stvari koje možete oblikovati u trendu uključuju:

• Boja linije. Ovo možete upotrijebiti za isticanje jedne od linija trenda tako što ćete učiniti sve na grafikonu svjetlom bojom i učiniti da linija trenda iskače svijetlom bojom
• The debljina linije
• The transparentnost linije

Za oblikovanje linije trendova pomičnog prosjeka kliknite desnom tipkom miša na nju, a zatim odaberite opciju Format Trendline.

Ovo će otvoriti okno Oblikovanje linije trenda s desne strane. Ovo okno kao sve mogućnosti oblikovanja (u različitim odjeljcima - Ispunjavanje i crtanje, Učinci i Opcije trenda).