Slīdošā vidējā aprēķināšana programmā Excel (vienkārša, svērta un eksponenciāla)

Tāpat kā daudzas manas Excel apmācības, arī šo ir iedvesmojis viens no jautājumiem, ko saņēmu no drauga. Viņa gribēja aprēķināt slīdošo vidējo rādītāju programmā Excel, un es lūdzu viņu to meklēt tiešsaistē (vai noskatīties YouTube videoklipu par to).

Bet tad es nolēmu uzrakstīt vienu (fakts, ka koledžā biju nedaudz statistikas nerd, arī spēlēja nelielu lomu).

Tagad, pirms es jums pastāstīšu, kā aprēķināt slīdošo vidējo rādītāju programmā Excel, ļaujiet man ātri sniegt pārskatu par to, ko nozīmē slīdošais vidējais un kādi ir slīdošo vidējo rādītāju veidi.

Ja vēlaties pāriet uz daļu, kurā es parādīšu, kā aprēķināt slīdošo vidējo rādītāju programmā Excel, noklikšķiniet šeit.

Piezīme. Es neesmu statistikas eksperts, un šajā apmācībā mans mērķis nav aptvert visu par mainīgajiem vidējiem rādītājiem. Es tikai cenšos parādīt, kā aprēķināt slīdošos vidējos rādītājus programmā Excel (ar īsu ievadu par to, ko nozīmē mainīgie vidējie rādītāji).

Kas ir mainīgais vidējais?

Esmu pārliecināts, ka jūs zināt, kāda ir vidējā vērtība.

Ja man ir trīs dienu dienas temperatūras dati, varat viegli pateikt pēdējo trīs dienu vidējo vērtību (mājiens: lai to izdarītu, programmā Excel varat izmantot funkciju VIDĒJĀ).

Slīdošais vidējais rādītājs (saukts arī par slīdošo vidējo rādītāju vai tekošo vidējo rādītāju) ir tad, ja vidējā laika periods paliek nemainīgs, bet turpina kustēties, pievienojot jaunus datus.

Piemēram, 3. dienā, ja es jautāju jums 3 dienu mainīgo vidējo temperatūru, jūs man norādīsit 1., 2. un 3. dienas vidējo temperatūras vērtību. Un, ja 4. dienā es jautāju jums 3 dienu mainīgo vidējo temperatūru , jūs man iedosit 2., 3. un 4. dienas vidējo vērtību.

Pievienojot jaunus datus, laika periods (3 dienas) paliek nemainīgs, bet slīdošā vidējā lieluma aprēķināšanai izmanto jaunākos datus.

Slīdošais vidējais lielums tiek izmantots tehniskai analīzei, un daudzas bankas un akciju tirgus analītiķi to izmanto katru dienu (zemāk ir piemērs, ko saņēmu no vietnes Market Realist).

Viena no slīdošo vidējo vērtību izmantošanas priekšrocībām ir tā, ka tā sniedz jums tendenci, kā arī zināmā mērā izlīdzina svārstības. Piemēram, ja ir patiešām karsta diena, trīs dienu slīdošais vidējais temperatūras rādītājs joprojām pārliecinās, ka vidējā vērtība ir izlīdzināta (tā vietā, lai parādītu patiešām augstu vērtību, kas varētu būt izņēmums- viena ārpus instances).

Slīdošo vidējo rādītāju veidi

Pastāv trīs veidu mainīgie vidējie rādītāji:

• Vienkāršs mainīgais vidējais (SMA)
• Svērtais slīdošais vidējais (WMA)
• Eksponenciālais slīdošais vidējais (EMA)

Vienkāršs mainīgais vidējais rādītājs (SMA)

Tas ir vienkāršais datu punktu vidējais rādītājs noteiktā laika periodā.

Mūsu ikdienas temperatūras piemērā, kad jūs vienkārši ņemat vidējo vērtību pēdējo 10 dienu laikā, tas dod 10 dienu vienkāršo slīdošo vidējo.

To var panākt, vidēji aprēķinot datu punktus noteiktā laika periodā. Programmā Excel to var viegli izdarīt, izmantojot funkciju AVERAGE (tas ir aplūkots vēlāk šajā apmācībā).

Svērtais slīdošais vidējais (WMA)

Pieņemsim, ka laika apstākļi ar katru dienu kļūst vēsāki un jūs izmantojat 10 dienu slīdošo vidējo, lai iegūtu temperatūras tendenci.

10. dienas temperatūra, visticamāk, būs labāks tendences rādītājs salīdzinājumā ar 1. dienu (jo temperatūra ar katru dienu pazeminās).

Tātad mums ir labāk, ja mēs vairāk paļaujamies uz 10. dienas vērtību.

Lai tas atspoguļotos mūsu mainīgajā vidējā rādītājā, varat piešķirt lielāku nozīmi jaunākajiem datiem un mazāk - pagātnes datiem. Tādā veidā jūs joprojām redzat tendenci, bet vairāk ietekmējot jaunākos datus.

To sauc par svērto slīdošo vidējo.

Eksponenciālais slīdošais vidējais (EMA)

Eksponenciālais slīdošais vidējais ir svērtā slīdošā vidējā veida veids, kurā lielāka nozīme tiek piešķirta jaunākajiem datiem un vecāku datu punktu gadījumā tā eksponenciāli samazinās.

To sauc arī par eksponenciāli svērto mainīgo vidējo (EWMA)

Atšķirība starp WMA un EMA ir tāda, ka, izmantojot WMA, jūs varat piešķirt svarus, pamatojoties uz jebkādiem kritērijiem. Piemēram, 3 punktu mainīgajā vidējā lielumā jūs varat piešķirt 60% svara vecumu jaunākajam datu punktam, 30% vidējam datu punktam un 10% vecākajam datu punktam.

EMA jaunākajai vērtībai tiek piešķirts lielāks svars, un iepriekšējām vērtībām svars kļūst eksponenciāli zemāks.

Pietiek ar statistikas lekciju.

Tagad iedziļināsimies un redzēsim, kā Excel aprēķināt slīdošos vidējos rādītājus.

Vienkāršā mainīgā vidējā (SMA) aprēķināšana, izmantojot datu analīzes rīku komplektu programmā Excel

Programmā Microsoft Excel jau ir iebūvēts rīks vienkāršo mainīgo vidējo vērtību aprēķināšanai.

To sauc par Datu analīzes rīkkopa.

Pirms varat izmantot rīku Datu analīze, vispirms jāpārbauda, ​​vai tas ir iekļauts Excel lentē. Pastāv liela iespēja, ka vispirms jāveic dažas darbības, lai to iespējotu.

Ja cilnē Dati jums jau ir opcija Datu analīze, izlaidiet tālāk norādītās darbības un skatiet slīdošo vidējo rādītāju aprēķināšanas darbības.

Noklikšķiniet uz cilnes Dati un pārbaudiet, vai redzat opciju Datu analīze. Ja to neredzat, veiciet tālāk norādītās darbības, lai padarītu to pieejamu lentē.

1. Noklikšķiniet uz cilnes Fails
2. Noklikšķiniet uz Opcijas
3. Dialoglodziņā Excel opcijas noklikšķiniet uz Papildinājumi
4. Dialoglodziņa apakšdaļā nolaižamajā izvēlnē atlasiet Excel pievienojumprogrammas un pēc tam noklikšķiniet uz Iet.
5. Atvērtajā dialoglodziņā Pievienojumprogrammas pārbaudiet opciju Analīzes rīkkopa
6. Noklikšķiniet uz Labi.

Iepriekš minētās darbības ļautu aktivizēt datu analīzes rīku komplektu, un tagad jūs redzēsit šo opciju cilnē Dati.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties aprēķināt pēdējo trīs intervālu slīdošo vidējo vērtību.

Tālāk ir norādītas darbības, lai izmantotu datu analīzi, lai aprēķinātu vienkāršu slīdošo vidējo.

1. Noklikšķiniet uz cilnes Dati
2. Noklikšķiniet uz opcijas Datu analīze
3. Dialoglodziņā Datu analīze noklikšķiniet uz opcijas Slīdošais vidējais (lai to sasniegtu, iespējams, būs nedaudz jāritina).
4. Noklikšķiniet uz Labi. Tiks atvērts dialoglodziņš “Slīdošais vidējais”.
5. Ievades diapazonā atlasiet datus, kuriem vēlaties aprēķināt slīdošo vidējo (šajā piemērā B2: B11)
6. Opcijā Intervāls ievadiet 3 (jo mēs aprēķinām trīs punktu slīdošo vidējo vērtību)
7. Izvades diapazonā ievadiet šūnu, kurā vēlaties iegūt rezultātus. Šajā piemērā es izmantoju C2 kā izvades diapazonu
8. Noklikšķiniet uz Labi

Iepriekš minētās darbības sniegs jums slīdošo vidējo rezultātu, kā parādīts zemāk.

Ņemiet vērā, ka C slejas pirmo divu šūnu rezultāts ir #N/A kļūda. Tas ir tāpēc, ka tas ir trīs punktu mainīgais vidējais rādītājs, un, lai iegūtu pirmo rezultātu, ir nepieciešami vismaz trīs datu punkti. Tātad faktiskās slīdošās vidējās vērtības sākas pēc trešā datu punkta.

Jūs arī pamanīsit, ka viss šis datu analīzes rīku komplekts ir izmantojis šūnām AVERAGE formulu. Tātad, ja vēlaties to izdarīt manuāli, neizmantojot rīku Datu analīze, to noteikti varat izdarīt.

Tomēr ir dažas lietas, kuras ir vieglāk izdarīt, izmantojot datu analīzes rīku komplektu. Piemēram, ja vēlaties iegūt standarta kļūdas vērtību, kā arī slīdošā vidējā diagrammu, viss, kas jums jādara, ir atzīmēt izvēles rūtiņu, un tā būs daļa no izvades.

Slīdošo vidējo rādītāju (SMA, WMA, EMA) aprēķināšana, izmantojot formulas programmā Excel

Jūs varat arī aprēķināt slīdošos vidējos rādītājus, izmantojot AVERAGE formulu.

Faktiski, ja viss, kas jums nepieciešams, ir mainīgā vidējā vērtība (nevis standarta kļūda vai diagramma), formulas izmantošana var būt labāka (un ātrāka) iespēja nekā datu analīzes rīku pakotnes izmantošana.

Arī datu analīzes rīkkopa sniedz tikai vienkāršo mainīgo vidējo (SMA), bet, ja vēlaties aprēķināt WMA vai EMA, jums jāpaļaujas tikai uz formulām.

Vienkārša slīdošā vidējā aprēķināšana, izmantojot formulas

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties aprēķināt 3 punktu SMA:

Šūnā C4 ievadiet šādu formulu:

= VIDĒJĀ (B2: B4)

Kopējiet šo formulu visām šūnām, un tā sniegs jums SMA katrai dienai.

Atcerieties: aprēķinot SMA, izmantojot formulas, jums jāpārliecinās, ka atsauces uz formulu ir relatīvas. Tas nozīmē, ka formula var būt = AVERAGE (B2: B4) vai = AVERAGE ($ B2: $ B4), bet tā nevar būt = AVERAGE ($ B $ 2: $ B $ 4) vai = AVERAGE (B $ 2: B $ 4) ). Atsauces rindas numura daļai jābūt bez dolāra zīmes. Vairāk par absolūtām un relatīvām atsaucēm varat lasīt šeit.

Tā kā mēs aprēķinām 3 punktu vienkāršo mainīgo vidējo lielumu (SMA), pirmās divas šūnas (pirmajās divās dienās) ir tukšas, un mēs sākam izmantot formulu, sākot ar trešo dienu. Ja vēlaties, pirmās divas vērtības varat izmantot tādas, kādas tās ir, un izmantot SMA vērtību, sākot ar trešo.

Svērtā slīdošā vidējā aprēķināšana, izmantojot formulas

WMA gadījumā jums jāzina svari, kas tiktu piešķirti vērtībām.

Piemēram, pieņemsim, ka jums ir jāaprēķina 3 punktu WMA zemāk esošajai datu kopai, kur 60% svars tiek dots jaunākajai vērtībai, 30% - vienībai pirms tās un 10% no tās, kas atrodas pirms tās.

Lai to izdarītu, šūnā C4 ievadiet šādu formulu un kopējiet visas šūnas.

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

Tā kā mēs aprēķinām 3 punktu svērto mainīgo vidējo (WMA), pirmās divas šūnas (pirmajās divās dienās) ir tukšas, un mēs sākam izmantot formulu, sākot ar trešo dienu. Ja vēlaties, pirmās divas vērtības varat izmantot tādas, kādas tās ir, un izmantot WMA vērtību, sākot ar trešo.

Eksponenciālā slīdošā vidējā aprēķināšana, izmantojot formulas

Eksponenciālais mainīgais vidējais (EMA) piešķir jaunākajai vērtībai lielāku svaru, un iepriekšējo vērtību svars turpina eksponenciāli samazināties.

Zemāk ir formula, lai aprēķinātu trīs punktu slīdošā vidējā EMA:

EMA = [Jaunākā vērtība - iepriekšējā EMA vērtība] * (2 / N + 1) + iepriekšējā EMA

… Kur šajā piemērā N būtu 3 (mēs aprēķinām trīs punktu EMA)

Piezīme. Pirmajai EMA vērtībai (ja jums nav iepriekšējas vērtības, lai aprēķinātu EMA), vienkārši ņemiet vērtību tādu, kāda tā ir, un uzskatiet to par EMA vērtību. Pēc tam šo vērtību varat izmantot arī turpmāk.

Pieņemsim, ka jums ir šāda datu kopa un vēlaties aprēķināt trīs periodu EMA:

Šūnā C2 ievadiet to pašu vērtību, kas B2. Tas ir tāpēc, ka nav iepriekšējas vērtības EMA aprēķināšanai.

Šūnā C3 ievadiet zemāk esošo formulu un kopējiet visas šūnas:

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

Šajā piemērā es esmu saglabājis to vienkāršu un izmantojis jaunāko vērtību un iepriekšējo EMA vērtību, lai aprēķinātu pašreizējo EMA.

Vēl viens populārs veids, kā to izdarīt, ir vispirms aprēķināt vienkāršo mainīgo vidējo un pēc tam izmantot to faktiskās jaunākās vērtības vietā.

Slīdošās vidējās tendences līnijas pievienošana kolonnu diagrammai

Ja jums ir datu kopa un veidojat joslu diagrammu, izmantojot to, ar dažiem klikšķiem varat pievienot arī mainīgo vidējo tendenču līniju.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk:

Tālāk ir norādītas darbības, lai izveidotu joslu diagrammu, izmantojot šos datus un pievienojot šai diagrammai trīsdaļīgu mainīgo vidējo tendenču līniju.

1. Atlasiet datu kopu (ieskaitot galvenes)
2. Noklikšķiniet uz cilnes Ievietot
3. Diagrammas grupā noklikšķiniet uz ikonas “Ievietot kolonnu vai joslu diagrammu”.
4. Noklikšķiniet uz opcijas Klasteru kolonnu diagramma. Tādējādi diagramma tiks ievietota darblapā.
5. Kad diagramma ir atlasīta, noklikšķiniet uz cilnes Dizains (šī cilne parādās tikai tad, kad diagramma ir atlasīta)
6. Diagrammas izkārtojumu grupā noklikšķiniet uz “Pievienot diagrammas elementu”.
7. Virziet kursoru uz opcijas “Trendline” un pēc tam noklikšķiniet uz “Citas tendences opcijas”
8. Rūtī Trendline formatēt atlasiet opciju “Slīdošais vidējais” un iestatiet periodu skaitu.

Tieši tā! Iepriekš minētās darbības jūsu kolonnu diagrammai pievienos kustīgu tendenču līniju.

Ja vēlaties ievietot vairāk nekā vienu mainīgo vidējo tendenču līniju (piemēram, vienu 2 periodiem un vienu 3 periodiem), atkārtojiet darbības no 5 līdz 8).

Jūs varat izmantot tās pašas darbības, lai līniju diagrammā ievietotu arī mainīgo vidējo tendenču līniju.

Slīdošā vidējā tendenču līnijas formatēšana

Atšķirībā no parastās līniju diagrammas, mainīgā vidējā tendenču līnija neļauj daudz formatēt. Piemēram, ja tendenču līnijā vēlaties izcelt konkrētu datu punktu, to nevarēsit izdarīt.

Tendences līnijā var formatēt dažas lietas:

• Krāsa no līnijas. Varat to izmantot, lai izceltu vienu no tendenču līnijām, padarot visu diagrammā gaišo krāsu un padarot tendences līniju iznirstošu ar spilgtu krāsu
• biezums no līnijas
• pārredzamību no līnijas

Lai formatētu mainīgo vidējo tendenču līniju, ar peles labo pogu noklikšķiniet uz tās un pēc tam atlasiet opciju Formatēt trendlīniju.

Tiks atvērta rinda Formatēt tendenci labajā pusē. Šī rūts ir visas formatēšanas opcijas (dažādās sadaļās - Aizpildīšana un līnija, Efekti un Trendline opcijas).