Výpočet kĺzavého priemeru v programe Excel (jednoduchý, vážený a exponenciálny)

Rovnako ako mnohé z mojich tutoriálov pre program Excel, aj tento je inšpirovaný jedným z dotazov, ktoré som dostal od priateľa. Chcela vypočítať kĺzavý priemer v Exceli a ja som ju požiadal, aby ho vyhľadala online (alebo si o tom pozrela video na YouTube).

Potom som sa však rozhodol napísať jeden sám (menšiu úlohu zohral aj fakt, že som bol na škole trochu štatistický hlupák).

Teraz, skôr ako vám poviem, ako vypočítať kĺzavý priemer v programe Excel, dovoľte mi rýchlo poskytnúť prehľad o tom, čo kĺzavý priemer znamená a aké typy kĺzavých priemerov existujú.

V prípade, že chcete prejsť na časť, kde uvádzam, ako vypočítať kĺzavý priemer v programe Excel, kliknite sem.

Poznámka: Nie som odborník na štatistiky a mojím zámerom v tomto návode nie je pokryť všetko o kĺzavých priemeroch. Cieľom je len ukázať vám, ako vypočítať kĺzavé priemery v programe Excel (so stručným predstavením toho, čo kĺzavé priemery znamenajú).

Čo je kĺzavý priemer?

Som si istý, že viete, čo je priemerná hodnota.

Ak mám tri dni údajov o dennej teplote, môžete mi ľahko povedať priemer za posledné tri dni (rada: na to môžete použiť funkciu PRIEMERNE v Exceli).

Kĺzavý priemer (nazývaný tiež kĺzavý priemer alebo kĺzavý priemer) je, keď časové obdobie priemeru ponecháte rovnaké, ale pri pridávaní nových údajov sa stále pohybuje.

Napríklad v 3. deň, ak sa vás opýtam na 3-dňovú kĺzavú priemernú teplotu, zadáte mi hodnotu priemernej teploty z 1., 2. a 3. dňa. A ak vás v 4. deň požiadam o 3-dňovú kĺzavú priemernú teplotu , dáte mi priemer z 2., 3. a 4. dňa.

Keď sa pridávajú nové údaje, ponecháte časové obdobie (3 dni) rovnaké, ale na výpočet kĺzavého priemeru použijete najnovšie údaje.

Kĺzavý priemer sa často používa na technickú analýzu a mnoho bánk a analytikov akciového trhu ho používa denne (nižšie je príklad, ktorý som získal z webu Market Realist).

Jednou z výhod používania kĺzavých priemerov je to, že vám dáva trend a do určitej miery vyhladzuje výkyvy. Napríklad v prípade skutočne horúceho dňa by trojdňový kĺzavý priemer teploty stále zaistil, že priemerná hodnota bola vyrovnaná (namiesto toho, aby vám ukázala skutočne vysokú hodnotu, ktorá by mohla byť odľahlou- jednorazovou- mimo inštancie).

Typy kĺzavých priemerov

Existujú tri typy kĺzavých priemerov:

• Jednoduchý kĺzavý priemer (SMA)
• Vážený kĺzavý priemer (WMA)
• Exponenciálny kĺzavý priemer (EMA)

Jednoduchý kĺzavý priemer (SMA)

Toto je jednoduchý priemer dátových bodov za dané trvanie.

V našom príklade dennej teploty, keď jednoducho vezmete priemer za posledných 10 dní, dostanete 10-dňový jednoduchý kĺzavý priemer.

To sa dá dosiahnuť priemerom dátových bodov za dané trvanie. V programe Excel to môžete ľahko vykonať pomocou funkcie AVERAGE (to je popísané neskôr v tomto návode).

Vážený kĺzavý priemer (WMA)

Povedzme, že počasie sa každým dňom ochladzuje a na získanie teplotného trendu používate 10-dňový kĺzavý priemer.

Teplota v deň 10 je pravdepodobne lepším ukazovateľom trendu v porovnaní s dňom 1 (pretože teplota každým dňom klesá).

Bude nám teda lepšie, ak sa viac spoliehame na hodnotu 10. dňa.

Aby sa to prejavilo v našom kĺzavom priemere, môžete prikladať väčšiu váhu najnovším údajom a menej predchádzajúcim údajom. Týmto spôsobom stále získate trend, ale s väčším vplyvom na najnovšie údaje.

Toto sa nazýva vážený kĺzavý priemer.

Exponenciálny kĺzavý priemer (EMA)

Exponenciálny kĺzavý priemer je typ váženého kĺzavého priemeru, kde je najnovším údajom prisúdená väčšia váha a pri starších údajových bodoch exponenciálne klesá.

Hovorí sa mu tiež exponenciálny vážený kĺzavý priemer (EWMA)

Rozdiel medzi WMA a EMA je v tom, že pomocou WMA môžete priradiť váhy na základe akýchkoľvek kritérií. Napríklad v 3-bodovom kĺzavom priemere môžete poslednému dátovému bodu priradiť 60% hmotnosti, 30% strednému dátovému bodu a 10% najstaršiemu dátovému bodu.

V EMA sa najnovšej hodnote pripisuje vyššia hmotnosť a pri predchádzajúcich hodnotách sa hmotnosť exponenciálne znižuje.

Dosť bolo prednášky o štatistike.

Teraz sa ponoríme a uvidíme, ako vypočítať kĺzavé priemery v programe Excel.

Výpočet jednoduchého kĺzavého priemeru (SMA) pomocou nástroja na analýzu údajov v programe Excel

Program Microsoft Excel už má vstavaný nástroj na výpočet jednoduchých kĺzavých priemerov.

Hovorí sa tomu Nástroj na analýzu údajov.

Pred použitím balíka nástrojov Analýza údajov musíte najskôr skontrolovať, či ho máte na páse s nástrojmi programu Excel alebo nie. Existuje veľká šanca, že budete musieť urobiť niekoľko krokov, aby ste to najskôr povolili.

V prípade, že už máte na karte Údaje možnosť Analýza údajov, preskočte nižšie uvedené kroky a pozrite si kroky na výpočet kĺzavých priemerov.

Kliknite na kartu Údaje a skontrolujte, či sa vám zobrazuje možnosť Analýza údajov alebo nie. Ak ho nevidíte, sprístupnite ho na páse s nástrojmi podľa nižšie uvedených pokynov.

1. Kliknite na kartu Súbor
2. Kliknite na Možnosti
3. V dialógovom okne Možnosti programu Excel kliknite na položku Doplnky
4. V spodnej časti dialógového okna vyberte v rozbaľovacom zozname položku Doplnky programu Excel a potom kliknite na položku Prejsť.
5. V dialógovom okne Doplnky, ktoré sa otvorí, začiarknite možnosť Analysis Toolpak
6. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by umožnili balík nástrojov na analýzu údajov a túto možnosť uvidíte teraz na karte Údaje.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete vypočítať kĺzavý priemer posledných troch intervalov.

Nasledujú kroky na použitie analýzy údajov na výpočet jednoduchého kĺzavého priemeru:

1. Kliknite na kartu Údaje
2. Kliknite na možnosť Analýza údajov
3. V dialógovom okne Analýza údajov kliknite na možnosť Kĺzavý priemer (na jeho dosiahnutie bude možno potrebné trochu posúvať).
4. Kliknite na tlačidlo OK. Otvorí sa dialógové okno „Kĺbový priemer“.
5. V poli Vstupný rozsah vyberte údaje, pre ktoré chcete vypočítať kĺzavý priemer (v tomto prípade B2: B11)
6. Do možnosti Interval zadajte 3 (pretože počítame trojbodový kĺzavý priemer)
7. Do poľa Výstup zadajte bunku, v ktorej chcete dosiahnuť výsledky. V tomto prípade používam ako výstupný rozsah C2
8. Kliknite na tlačidlo OK

Vyššie uvedené kroky vám poskytnú výsledok kĺzavého priemeru, ako je uvedené nižšie.

Všimnite si toho, že prvé dve bunky v stĺpci C majú výsledok ako #N/A chyba. Je to preto, že je to trojbodový kĺzavý priemer a na dosiahnutie prvého výsledku sú potrebné najmenej tri dátové body. Skutočné hodnoty kĺzavého priemeru teda začínajú po treťom dátovom bode.

Tiež si všimnete, že všetok tento balík nástrojov pre analýzu údajov vykonal, že je na bunky aplikovaný PRIEMERNÝ vzorec. Ak to teda chcete urobiť ručne bez balíka nástrojov na analýzu údajov, určite to môžete urobiť.

Existuje však niekoľko vecí, ktoré je jednoduchšie vykonať pomocou balíka nástrojov na analýzu údajov. Ak napríklad chcete získať štandardnú chybovú hodnotu aj graf kĺzavého priemeru, stačí začiarknuť políčko a bude súčasťou výstupu.

Výpočet kĺzavých priemerov (SMA, WMA, EMA) pomocou vzorcov v programe Excel

Kĺzavé priemery môžete vypočítať aj pomocou PRIEMERNÉHO vzorca.

Ak v skutočnosti potrebujete iba hodnotu kĺzavého priemeru (a nie štandardnú chybu alebo graf), použitie vzorca môže byť lepšou (a rýchlejšou) možnosťou ako použitie nástroja na analýzu údajov.

Nástroj na analýzu údajov tiež poskytuje iba jednoduchý kĺzavý priemer (SMA), ale ak chcete vypočítať WMA alebo EMA, musíte sa spoliehať iba na vzorce.

Výpočet jednoduchého kĺzavého priemeru pomocou vzorcov

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete vypočítať 3-bodový SMA:

Do bunky C4 zadajte nasledujúci vzorec:

= PRIEMERNÉ (B2: B4)

Skopírujte tento vzorec pre všetky bunky a získate SMA na každý deň.

Pamätajte si: Pri výpočte SMA pomocou vzorcov musíte zaistiť, aby odkazy na vzorec boli relatívne. To znamená, že vzorec môže byť = AVERAGE (B2: B4) alebo = AVERAGE ($ B2: $ B4), ale nemôže byť = AVERAGE ($ B $ 2: $ B $ 4) alebo = AVERAGE (B $ 2: B $ 4 ). Časť odkazu na číslo riadka musí byť bez znaku dolára. Viac o absolútnych a relatívnych referenciách si môžete prečítať tu.

Pretože počítame 3-bodový jednoduchý kĺzavý priemer (SMA), prvé dve bunky (počas prvých dvoch dní) sú prázdne a od tretieho dňa začíname používať vzorec. Ak chcete, môžete použiť prvé dve hodnoty tak, ako sú, a od tretej použiť hodnotu SMA.

Výpočet váženého kĺzavého priemeru pomocou vzorcov

Pre WMA potrebujete vedieť váhy, ktoré by boli priradené k hodnotám.

Predpokladajme napríklad, že musíte vypočítať 3 -bodový WMA pre nasledujúci súbor údajov, kde 60% hmotnosti je priradené najnovšej hodnote, 30% váhe pred predchádzajúcou a 10% hodnoty pred ňou.

Za týmto účelom zadajte do bunky C4 nasledujúci vzorec a skopírujte pre všetky bunky.

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

Pretože vypočítavame 3-bodový vážený kĺzavý priemer (WMA), prvé dve bunky (počas prvých dvoch dní) sú prázdne a od tretieho dňa začíname používať vzorec. Ak chcete, môžete použiť prvé dve hodnoty tak, ako sú, a od tretej použiť hodnotu WMA.

Výpočet exponenciálneho kĺzavého priemeru pomocou vzorcov

Exponenciálny kĺzavý priemer (EMA) dáva najnovšej hodnote väčšiu váhu a pri predchádzajúcich hodnotách sa váhy exponenciálne znižujú.

Nasleduje vzorec na výpočet EMA pre trojbodový kĺzavý priemer:

EMA = [Najnovšia hodnota - predchádzajúca hodnota EMA] * (2 / N + 1) + predchádzajúca EMA

… kde N bude v tomto prípade 3 (pretože počítame trojbodové EMA)

Poznámka: Pri prvej hodnote EMA (ak nemáte žiadnu predchádzajúcu hodnotu na výpočet EMA) jednoducho vezmite hodnotu tak, ako je, a považujte ju za hodnotu EMA. Túto hodnotu potom môžete použiť v budúcnosti.

Predpokladajme, že máte nasledujúci súbor údajov a chcete vypočítať trojdobovú EMA:

Do bunky C2 zadajte rovnakú hodnotu ako v B2. Dôvodom je, že neexistuje žiadna predchádzajúca hodnota na výpočet EMA.

Do bunky C3 zadajte nasledujúci vzorec a skopírujte ho pre všetky bunky:

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

V tomto prípade som to zjednodušil a na výpočet aktuálnej EMA som použil najnovšiu hodnotu a predchádzajúcu hodnotu EMA.

Ďalším populárnym spôsobom, ako to dosiahnuť, je najskôr vypočítať jednoduchý kĺzavý priemer a potom ho použiť namiesto skutočnej najnovšej hodnoty.

Pridanie kĺzavého priemerného trendu do stĺpcového grafu

Ak máte množinu údajov a vytvárate pomocou nej stĺpcový graf, môžete tiež pridať trendovú čiaru kĺzavého priemeru niekoľkými kliknutiami.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:

Nasledujú kroky na vytvorenie stĺpcového grafu pomocou týchto údajov a pridanie trojdielnej trendovej čiary kĺzavého priemeru do tohto grafu:

1. Vyberte množinu údajov (vrátane hlavičiek)
2. Kliknite na kartu Vložiť
3. V skupine Graf kliknite na ikonu „Vložiť stĺpcový alebo stĺpcový graf“.
4. Kliknite na možnosť Klastrovaný stĺpcový graf. Tabuľka sa vloží do pracovného hárka.
5. Keď je graf vybratý, kliknite na kartu Návrh (táto karta sa zobrazí iba vtedy, keď je vybraný graf)
6. V skupine Rozloženie grafu kliknite na položku „Pridať prvok grafu“.
7. Ukážte kurzorom na možnosť „Trendline“ a potom kliknite na „Ďalšie možnosti trendovej čiary“
8. Na table Formátovať trendovú čiaru vyberte možnosť „Kĺzavý priemer“ a nastavte počet období.

To je všetko! Vyššie uvedené kroky by do vášho stĺpcového grafu pridali pohyblivú trendovú čiaru.

V prípade, že chcete vložiť viac ako jednu trendovú čiaru kĺzavého priemeru (napríklad jednu na 2 obdobia a jednu na 3 obdobia), zopakujte kroky 5 až 8).

Rovnaké kroky môžete použiť aj na vloženie trendovej čiary kĺzavého priemeru do čiarového grafu.

Formátovanie čiary trendu kĺzavého priemeru

Na rozdiel od bežného čiarového grafu trendová čiara s kĺzavým priemerom neumožňuje veľa formátovania. Ak napríklad chcete na trendovej čiare zvýrazniť konkrétny údajový bod, nebudete to môcť urobiť.

Niekoľko vecí, ktoré môžete formátovať v trendovej línii, zahŕňa:

• Farba linky. Môžete to použiť na zvýraznenie jednej z trendových línií tak, že všetko v grafe zosvetlí a rozbalí trendovú čiaru jasnou farbou.
• The hrúbka linky
• The transparentnosť linky

Ak chcete naformátovať trendovú čiaru kĺzavého priemeru, kliknite na ňu pravým tlačidlom myši a potom vyberte možnosť Formátovať trendovú čiaru.

Vpravo sa otvorí tabla Formátovať trendovú čiaru. Táto tabla obsahuje všetky možnosti formátovania (v rôznych častiach - Možnosti výplne a čiary, Efekty a Trendline).