Izračunavanje drsečega povprečja v Excelu (preprosto, uteženo in eksponentno)

Tako kot mnoge moje vadnice v Excelu je tudi ta navdihnjena z eno od poizvedb, ki sem jih dobila od prijatelja. Želela je izračunati drseče povprečje v Excelu in prosil sem jo, naj ga poišče na spletu (ali si o tem ogleda video v YouTubu).

Potem pa sem se odločil, da ga napišem sam (dejstvo, da sem bil na fakulteti nekoliko statistični norček, je igralo tudi manjšo vlogo).

Preden vam povem, kako izračunati drseče povprečje v Excelu, naj vam na hitro predstavim, kaj pomeni drseče povprečje in kakšne vrste drsečih povprečij obstajajo.

Če želite skočiti na del, kjer pokažem, kako izračunati drseče povprečje v Excelu, kliknite tukaj.

Opomba: Nisem strokovnjak za statistiko in moj namen v tej vadnici ni zajeti vsega o drsečih srednjih vrednostih. Moj namen je le pokazati, kako izračunati drseča povprečja v Excelu (s kratkim predstavitvijo, kaj pomenijo drseča povprečja).

Kaj je drseče povprečje?

Prepričan sem, da veste, kakšna je povprečna vrednost.

Če imam tri dnevne podatke o dnevni temperaturi, mi lahko preprosto poveste povprečje zadnjih treh dni (namig: za to lahko uporabite funkcijo POVPREČNO v Excelu).

Drseče povprečje (imenovano tudi drseče povprečje ali tekoče povprečje) je, ko časovno obdobje povprečja ostane enako, vendar se ob dodajanju novih podatkov nenehno premika.

Na primer, na 3. dan, če vas vprašam o tridnevni drseči povprečni temperaturi, mi boste dali povprečno vrednost temperature 1., 2. in 3. dneva. Če pa vas 4. dan vprašam o tridnevni drseči povprečni temperaturi , podali mi boste povprečje 2., 3. in 4. dneva.

Ko se dodajo novi podatki, ohranite časovno obdobje (3 dni) enako, vendar za izračun drsečega povprečja uporabite najnovejše podatke.

Drseče povprečje se močno uporablja za tehnično analizo in veliko bank in analitikov borz ga uporablja vsak dan (spodaj je primer, ki sem ga dobil s spletnega mesta Market Realist).

Ena od prednosti uporabe drsečih povprečij je ta, da vam daje trend in do neke mere ublaži nihanja. Na primer, če je res vroč dan, bi tridnevno drseče povprečje temperature še vedno zagotovilo, da je bila povprečna vrednost zglajena (namesto da bi vam pokazala res visoko vrednost, ki bi lahko bila odstopanje- eno izven primera).

Vrste drsečih povprečij

Obstajajo tri vrste drsečih povprečij:

• Enostavno drseče povprečje (SMA)
• Uteženo drseče povprečje (WMA)
• Eksponentno drseče povprečje (EMA)

Enostavno drseče povprečje (SMA)

To je preprosto povprečje podatkovnih točk v danem trajanju.

V našem primeru dnevne temperature, ko preprosto vzamete povprečje zadnjih 10 dni, dobite 10-dnevno preprosto drseče povprečje.

To je mogoče doseči s povprečjem podatkovnih točk v danem trajanju. V Excelu lahko to preprosto storite s funkcijo POVPREČNO (to je opisano v nadaljevanju te vadnice).

Uteženo drseče povprečje (WMA)

Recimo, da je vreme iz dneva v dan hladnejše in da uporabite 10-dnevno drseče povprečje, da dobite temperaturni trend.

Temperatura na 10. dan je bolj verjetno pokazatelj trenda v primerjavi z 1. dnem (ker temperatura pada z vsakim dnem).

Zato smo bolje, če se bolj zanašamo na vrednost 10. dne.

Če želite, da se to odraža v našem drsečem povprečju, lahko dajete večjo težo najnovejšim podatkom in manj preteklim. Na ta način še vedno dobite trend, vendar z večjim vplivom najnovejših podatkov.

To se imenuje tehtano drseče povprečje.

Eksponentno drseče povprečje (EMA)

Eksponentno drseče povprečje je vrsta tehtanega drsečega povprečja, pri katerem imajo najnovejši podatki večjo težo, pri starejših podatkovnih točkah pa se eksponentno zmanjšuje.

Imenuje se tudi eksponentno tehtano drseče povprečje (EWMA)

Razlika med WMA in EMA je v tem, da lahko z WMA dodelite uteži na podlagi vseh meril. Na primer, v 3-točkovnem drsečem povprečju lahko najnovejši podatkovni točki dodelite 60-odstotno težo, 30% srednji podatkovni točki in 10% najstarejši podatkovni točki.

V EMA najnovejša vrednost dobi večjo težo, za prejšnje vrednosti pa se eksponentno znižuje.

Dovolj predavanja o statistiki.

Zdaj pa se potopimo in poglejmo, kako izračunati drseča povprečja v Excelu.

Izračun enostavnega drsečega povprečja (SMA) s pomočjo orodja za analizo podatkov v Excelu

Microsoft Excel že ima vgrajeno orodje za izračun preprostih drsečih povprečij.

Imenuje se Paket orodij za analizo podatkov.

Preden lahko uporabite paket orodij za analizo podatkov, morate najprej preveriti, ali ga imate na Excelovem traku ali ne. Obstaja velika verjetnost, da morate narediti nekaj korakov, da ga najprej omogočite.

Če že imate možnost Analiza podatkov na zavihku Podatki, preskočite spodnje korake in si oglejte korake za izračun drsečih povprečij.

Kliknite zavihek Podatki in preverite, ali vidite možnost Analiza podatkov ali ne. Če ga ne vidite, sledite spodnjim korakom, da bo na voljo na traku.

1. Kliknite zavihek Datoteka
2. Kliknite Možnosti
3. V pogovornem oknu Možnosti programa Excel kliknite Dodatki
4. Na dnu pogovornega okna v spustnem meniju izberite Excelovi dodatki in kliknite Pojdi.
5. V pogovornem oknu Dodatki, ki se odpre, preverite možnost Analiza orodja
6. Kliknite V redu.

Zgornji koraki bi omogočili nabor orodij za analizo podatkov in to možnost boste zdaj videli na zavihku Podatki.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izračunati drseče povprečje zadnjih treh intervalov.

Spodaj so navedeni koraki za uporabo analize podatkov za izračun preprostega drsečega povprečja:

1. Kliknite zavihek Podatki
2. Kliknite možnost Analiza podatkov
3. V pogovornem oknu Analiza podatkov kliknite možnost Drseče povprečje (do nje se boste morda morali nekoliko pomakniti).
4. Kliknite V redu. S tem se odpre pogovorno okno »Drseče povprečje«.
5. V vnosnem območju izberite podatke, za katere želite izračunati drseče povprečje (B2: B11 v tem primeru)
6. V možnost Interval vnesite 3 (saj izračunamo tritočkovno drseče povprečje)
7. V obsegu Output vnesite celico, v kateri želite rezultate. V tem primeru za izhodno območje uporabljam C2
8. Kliknite V redu

Zgornji koraki bi vam dali rezultat drsečega povprečja, kot je prikazano spodaj.

Upoštevajte, da imata prvi dve celici v stolpcu C napako #N/A. To je zato, ker gre za tritočkovno drseče povprečje in potrebuje vsaj tri podatkovne točke za prvi rezultat. Tako se dejanske vrednosti drsečega povprečja začnejo po tretji podatkovni točki naprej.

Opazili boste tudi, da je ves ta paket orodij za analizo podatkov za celice uporabljen povprečno formulo. Torej, če želite to narediti ročno brez nabora orodij za analizo podatkov, to zagotovo lahko storite.

Z orodjem za analizo podatkov pa je lažje narediti nekaj stvari. Na primer, če želite dobiti standardno vrednost napake in grafikon drsečega povprečja, morate le potrditi polje in to bo del izhoda.

Izračun drsečih povprečij (SMA, WMA, EMA) s formulami v Excelu

Drseča povprečja lahko izračunate tudi s formulo POVPREČNO.

Če dejansko potrebujete le vrednost drsečega povprečja (in ne standardne napake ali grafikona), je uporaba formule lahko boljša (in hitrejša) možnost kot uporaba orodja za analizo podatkov.

Paket orodja za analizo podatkov daje samo preprosto drseče povprečje (SMA), če pa želite izračunati WMA ali EMA, se morate zanašati samo na formule.

Izračun enostavnega drsečega povprečja s formulami

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite izračunati 3-točkovno SMA:

V celico C4 vnesite naslednjo formulo:

= POVPREČNO (B2: B4)

Kopirajte to formulo za vse celice in vam bo dala SMA za vsak dan.

Ne pozabite: pri izračunu SMA z uporabo formul se morate prepričati, da so sklice na formulo relativne. To pomeni, da je formula lahko = POVPREČNA (B2: B4) ali = POVPREČNA ($ B2: $ B4), vendar ne more biti = POVPREČNA ($ B $ 2: $ B $ 4) ali = POVPREČNA (B $ 2: B $ 4) ). Del sklica številke vrstice mora biti brez znaka dolarja. Več o absolutnih in relativnih referencah lahko preberete tukaj.

Ker izračunamo 3-točkovno preprosto drseče povprečje (SMA), sta prvi dve celici (prva dva dni) prazni in formulo začnemo uporabljati od tretjega dne dalje. Če želite, lahko uporabite prvi dve vrednosti, kot je, in uporabite vrednost SMA od tretje naprej.

Izračun tehtanega drsečega povprečja s formulami

Za WMA morate poznati uteži, ki bi bile dodeljene vrednostim.

Recimo, da morate izračunati 3 -točkovni WMA za spodnji nabor podatkov, kjer je 60% uteži dana najnovejša vrednost, 30% tista pred njo in 10% tista pred njo.

Če želite to narediti, vnesite naslednjo formulo v celico C4 in kopirajte za vse celice.

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

Ker izračunamo 3-točkovno tehtano drseče povprečje (WMA), sta prvi dve celici (prva dva dni) prazni in formulo začnemo uporabljati od tretjega dne dalje. Če želite, lahko uporabite prvi dve vrednosti, kot je, in uporabite vrednost WMA od tretje naprej.

Izračun eksponentnega drsečega povprečja s formulami

Eksponentno drseče povprečje (EMA) daje višjo težo najnovejši vrednosti, uteži pa se za prejšnje vrednosti še naprej eksponentno znižujejo.

Spodaj je formula za izračun EMA za tritočkovno drseče povprečje:

EMA = [Najnovejša vrednost - prejšnja vrednost EMA] * (2 / N + 1) + prejšnja EMA

… Kjer bi bilo N v tem primeru 3 (saj izračunamo tritočkovno EMA)

Opomba: Za prvo vrednost EMA (če nimate predhodne vrednosti za izračun EMA), preprosto vzemite vrednost takšno, kot je, in jo upoštevajte kot vrednost EMA. Nato lahko to vrednost uporabite naprej.

Recimo, da imate spodnji nabor podatkov in želite izračunati trikratno EMA:

V celico C2 vnesite isto vrednost kot v B2. To je zato, ker ni prejšnje vrednosti za izračun EMA.

V celico C3 vnesite spodnjo formulo in kopirajte za vse celice:

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

V tem primeru sem poenostavil in uporabil najnovejšo vrednost in prejšnjo vrednost EMA za izračun trenutne EMA.

Drug priljubljen način za to je, da najprej izračunate preprosto drseče povprečje in ga nato uporabite namesto dejanske najnovejše vrednosti.

Dodajanje vrstice gibljivih povprečnih trendov v stolpčni grafikon

Če imate nabor podatkov in z njim ustvarjate stolpčni grafikon, lahko z nekaj kliki dodate tudi trendno linijo gibljivega povprečja.

Recimo, da imate nabor podatkov, kot je prikazano spodaj:

Spodaj so navedeni koraki za ustvarjanje stolpnega grafikona z uporabo teh podatkov in temu grafikonu dodamo tridelno smer gibljivega povprečja:

1. Izberite nabor podatkov (vključno z glavami)
2. Kliknite zavihek Vstavi
3. V skupini Grafikon kliknite ikono »Vstavi stolpec ali palični grafikon«.
4. Kliknite možnost grafikona v gručastem stolpcu. To bo grafikon vstavilo na delovni list.
5. Ko je grafikon izbran, kliknite zavihek Oblikovanje (ta zavihek se prikaže samo, ko je grafikon izbran)
6. V skupini Postavitve grafikonov kliknite »Dodaj element grafikona«.
7. Premaknite kazalec na možnost »Trendline« in nato kliknite »More Trendline Options«
8. V podoknu Oblika trenda izberite možnost »Drseče povprečje« in nastavite število obdobij.

To je to! Zgornji koraki bi dodali premikajočo se linijo trenda v vaš stolpčni grafikon.

Če želite vstaviti več kot eno smer trenda drsečega povprečja (na primer eno za 2 obdobja in eno za 3 obdobja), ponovite korake od 5 do 8).

Z istimi koraki lahko v črtni grafikon vstavite tudi linijo drsečega povprečja trenda.

Oblikovanje črte trenda drsečega povprečja

Za razliko od običajnega grafikona črtna linija gibljivega povprečja ne dopušča veliko oblikovanja. Na primer, če želite označiti določeno podatkovno točko na liniji trenda, tega ne boste mogli storiti.

Nekaj ​​stvari, ki jih lahko oblikujete v trendni liniji, je:

• Barva črte. S tem lahko označite eno od trendnih linij tako, da naredite vse v grafikonu svetlo barvno in da trendna linija izstopi s svetlo barvo
• The debeline črte
• The preglednosti črte

Če želite oblikovati trendno črto drsečega povprečja, jo kliknite z desno miškino tipko in izberite možnost Oblikuj trendno črto.

S tem se odpre podokno Oblika trenda na desni. To podokno kot vse možnosti oblikovanja (v različnih razdelkih - Polni in črti, Učinki in Možnosti trenda).