Výpočet klouzavého průměru v aplikaci Excel (jednoduchý, vážený a exponenciální)

Stejně jako mnoho jiných mých excelových tutoriálů je i tento inspirován jedním z dotazů, které jsem dostal od přítele. Chtěla vypočítat klouzavý průměr v Excelu a já ji požádal, aby to vyhledala online (nebo se na to koukla na video na YouTube).

Ale pak jsem se rozhodl napsat jeden sám (skutečnost, že jsem byl na škole trochu statistický pitomec, také hrála malou roli).

Nyní, než vám řeknu, jak vypočítat klouzavý průměr v aplikaci Excel, mi dovolte rychle vám poskytnout přehled o tom, co klouzavý průměr znamená a jaké typy klouzavých průměrů existují.

V případě, že chcete přejít na část, kde ukazuji, jak vypočítat klouzavý průměr v Excelu, klikněte sem.

Poznámka: Nejsem odborník na statistiky a mým záměrem v tomto kurzu není pokrýt vše o klouzavých průměrech. Cílem je pouze ukázat vám, jak vypočítat klouzavé průměry v aplikaci Excel (s krátkým představením toho, co klouzavé průměry znamenají).

Co je to klouzavý průměr?

Jsem si jistý, že víte, co je průměrná hodnota.

Pokud mám tři dny denních teplotních údajů, můžete mi snadno sdělit průměr za poslední tři dny (nápověda: k tomu můžete použít funkci PRŮMĚR v Excelu).

Klouzavý průměr (také nazývaný klouzavý průměr nebo klouzavý průměr) je, když časové období průměru ponecháte stejné, ale při přidávání nových dat se stále pohybuje.

Například když vás 3. den požádám o 3denní klouzavou průměrnou teplotu, sdělíte mi hodnotu průměrné teploty 1., 2. a 3. den. A pokud vás 4. den požádám o třídenní klouzavou průměrnou teplotu , dáte mi průměr z 2., 3. a 4. dne.

Při přidávání nových dat ponecháte časové období (3 dny) stejné, ale pro výpočet klouzavého průměru použijete nejnovější data.

Klouzavý průměr se hojně používá pro technickou analýzu a mnoho bank a analytiků akciového trhu jej používá denně (níže je příklad, který jsem získal z webu Market Realist).

Jednou z výhod používání klouzavých průměrů je, že vám poskytne trend a do určité míry vyhladí výkyvy. Například v případě opravdu horkého dne by třídenní klouzavý průměr teploty stále zajišťoval vyhlazení průměrné hodnoty (místo aby vám ukázal opravdu vysokou hodnotu, která by mohla být mimořádná- jedna- mimo instanci).

Typy klouzavých průměrů

Existují tři typy klouzavých průměrů:

• Jednoduchý klouzavý průměr (SMA)
• Vážený klouzavý průměr (WMA)
• Exponenciální klouzavý průměr (EMA)

Jednoduchý klouzavý průměr (SMA)

Toto je prostý průměr datových bodů za dané trvání.

V našem příkladu denní teploty, když jednoduše vezmete průměr za posledních 10 dní, získáte 10denní jednoduchý klouzavý průměr.

Toho lze dosáhnout průměrováním datových bodů v daném trvání. V aplikaci Excel to můžete snadno provést pomocí funkce PRŮMĚR (toto je popsáno dále v tomto kurzu).

Vážený klouzavý průměr (WMA)

Řekněme, že s každým dnem se počasí ochlazuje a ke sledování trendu teploty používáte 10denní klouzavý průměr.

Teplota v den 10 bude ve srovnání s dnem 1 s větší pravděpodobností lepším indikátorem trendu (protože teplota každým dnem klesá).

Je nám tedy lépe, když se více spoléháme na hodnotu 10. dne.

Aby se to projevilo v našem klouzavém průměru, můžete dát větší váhu nejnovějším datům a méně datům minulým. Tímto způsobem stále získáte trend, ale s větším vlivem nejnovějších dat.

Toto se nazývá vážený klouzavý průměr.

Exponenciální klouzavý průměr (EMA)

Exponenciální klouzavý průměr je typ váženého klouzavého průměru, kde je nejnovějším datům přikládána větší váha a u starších datových bodů exponenciálně klesá.

Říká se mu také exponenciálně vážený klouzavý průměr (EWMA)

Rozdíl mezi WMA a EMA je v tom, že s WMA můžete přiřadit váhy na základě jakýchkoli kritérií. Například ve 3bodovém klouzavém průměru můžete poslednímu datovému bodu přiřadit věk 60%, střednímu datovému bodu 30% a nejstaršímu datovému bodu 10%.

V EMA je vyšší hodnotě přiřazena nejnovější hodnota a u dřívějších hodnot se váha stále exponenciálně snižuje.

Dost přednášky o statistikách.

Nyní se ponoříme a uvidíme, jak vypočítat klouzavé průměry v aplikaci Excel.

Výpočet jednoduchého klouzavého průměru (SMA) pomocí nástroje pro analýzu dat v aplikaci Excel

Aplikace Microsoft Excel již má integrovaný nástroj pro výpočet jednoduchých klouzavých průměrů.

Říká se tomu Nástroj pro analýzu dat.

Než budete moci použít balíček nástrojů pro analýzu dat, musíte nejprve zkontrolovat, zda jej máte na pásu karet aplikace Excel nebo ne. Existuje velká šance, že musíte nejprve provést několik kroků, abyste to mohli povolit.

V případě, že již máte možnost Data Analysis na kartě Data, přeskočte níže uvedené kroky a podívejte se na kroky pro výpočet klouzavých průměrů.

Klikněte na kartu Data a zkontrolujte, zda se vám zobrazuje možnost Analýza dat. Pokud ji nevidíte, zpřístupněte ji na pásu karet podle níže uvedených kroků.

1. Klikněte na kartu Soubor
2. Klikněte na Možnosti
3. V dialogovém okně Možnosti aplikace Excel klikněte na Doplňky
4. V dolní části dialogového okna vyberte v rozevíracím seznamu Doplňky aplikace Excel a poté klikněte na Přejít.
5. V dialogovém okně Doplňky, které se otevře, zaškrtněte možnost Analysis Toolpak
6. Klikněte na OK.

Výše uvedené kroky by umožnily nástroj Data Analysis Toolpack a tuto možnost uvidíte nyní na kartě Data.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete vypočítat klouzavý průměr posledních tří intervalů.

Níže jsou uvedeny kroky k použití analýzy dat k výpočtu jednoduchého klouzavého průměru:

1. Klikněte na kartu Data
2. Klikněte na možnost Analýza dat
3. V dialogovém okně Analýza dat klikněte na možnost Klouzavý průměr (možná budete muset trochu posunout, abyste ji dosáhli).
4. Klikněte na OK. Otevře se dialogové okno „Klouzavý průměr“.
5. V poli Rozsah vstupu vyberte data, pro která chcete vypočítat klouzavý průměr (v tomto případě B2: B11)
6. V možnosti Interval zadejte 3 (při výpočtu tříbodového klouzavého průměru)
7. V oblasti výstupu zadejte buňku, kde chcete výsledky. V tomto případě používám C2 jako výstupní rozsah
8. Klikněte na OK

Výše uvedené kroky vám poskytnou výsledek klouzavého průměru, jak je uvedeno níže.

Všimněte si, že první dvě buňky ve sloupci C mají výsledek jako #N/A chyba. Je to proto, že se jedná o tříbodový klouzavý průměr a k dosažení prvního výsledku potřebuje alespoň tři datové body. Skutečné hodnoty klouzavého průměru tedy začínají po třetím datovém bodě dále.

Také si všimnete, že všechny tyto nástroje pro analýzu dat provedly, je na buňky aplikován PRŮMĚRNÝ vzorec. Pokud to tedy chcete provést ručně bez sady nástrojů pro analýzu dat, určitě to můžete udělat.

Existuje však několik věcí, které lze s nástrojem pro analýzu dat jednodušeji provést. Pokud například chcete získat standardní chybovou hodnotu i graf klouzavého průměru, stačí zaškrtnout políčko a bude součástí výstupu.

Výpočet klouzavých průměrů (SMA, WMA, EMA) pomocí vzorců v aplikaci Excel

Klouzavé průměry můžete také vypočítat pomocí PRŮMĚRNÉHO vzorce.

Pokud ve skutečnosti potřebujete pouze hodnotu klouzavého průměru (a ne standardní chybu nebo graf), může být použití vzorce lepší (a rychlejší) než použití nástroje pro analýzu dat.

Nástroj pro analýzu dat také poskytuje pouze jednoduchý klouzavý průměr (SMA), ale pokud chcete počítat WMA nebo EMA, musíte se spolehnout pouze na vzorce.

Výpočet jednoduchého klouzavého průměru pomocí vzorců

Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete vypočítat tříbodovou SMA:

Do buňky C4 zadejte následující vzorec:

= PRŮMĚR (B2: B4)

Zkopírujte tento vzorec pro všechny buňky a získáte SMA pro každý den.

Pamatujte: Při výpočtu SMA pomocí vzorců musíte zajistit, aby odkazy ve vzorci byly relativní. To znamená, že vzorec může být = PRŮMĚR (B2: B4) nebo = PRŮMĚR ($ B2: $ B4), ale nemůže být = PRŮMĚR ($ B $ 2: $ B $ 4) nebo = PRŮMĚR (B $ 2: B $ 4 ). Část odkazu na číslo řádku musí být bez znaku dolaru. Více o absolutních a relativních referencích si můžete přečíst zde.

Protože počítáme tříbodový jednoduchý klouzavý průměr (SMA), první dvě buňky (pro první dva dny) jsou prázdné a od třetího dne začneme používat vzorec. Pokud chcete, můžete použít první dvě hodnoty tak, jak jsou, a od třetí použít hodnotu SMA.

Výpočet váženého klouzavého průměru pomocí vzorců

Pro WMA potřebujete znát váhy, které by byly přiřazeny k hodnotám.

Předpokládejme například, že je třeba vypočítat tříbodovou WMA pro níže uvedenou datovou sadu, kde 60% váhy je dáno nejnovější hodnotě, 30% hmotnosti před ní a 10% hodnoty před ní.

Chcete -li to provést, zadejte do buňky C4 následující vzorec a zkopírujte pro všechny buňky.

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

Jelikož počítáme tříbodový vážený klouzavý průměr (WMA), první dvě buňky (pro první dva dny) jsou prázdné a od třetího dne začneme používat vzorec. Pokud chcete, můžete použít první dvě hodnoty tak, jak jsou, a od třetí použít hodnotu WMA.

Výpočet exponenciálního klouzavého průměru pomocí vzorců

Exponenciální klouzavý průměr (EMA) dává vyšší hodnotě nejnovější hodnotu a váhy se u dřívějších hodnot exponenciálně snižují.

Níže je uveden vzorec pro výpočet EMA pro tříbodový klouzavý průměr:

EMA = [nejnovější hodnota - předchozí hodnota EMA] * (2 / N + 1) + předchozí EMA

… kde N bude v tomto případě 3 (protože počítáme tříbodový EMA)

Poznámka: Pro první hodnotu EMA (pokud nemáte žádnou předchozí hodnotu pro výpočet EMA) jednoduše vezměte hodnotu tak, jak je, a považujte ji za hodnotu EMA. Tuto hodnotu pak můžete použít do budoucna.

Předpokládejme, že máte níže uvedenou sadu dat a chcete vypočítat tři období EMA:

Do buňky C2 zadejte stejnou hodnotu jako v B2. Důvodem je, že neexistuje žádná předchozí hodnota pro výpočet EMA.

Do buňky C3 zadejte níže uvedený vzorec a zkopírujte pro všechny buňky:

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

V tomto příkladu jsem to zjednodušil a pro výpočet aktuální EMA jsem použil nejnovější hodnotu a předchozí hodnotu EMA.

Dalším populárním způsobem, jak toho dosáhnout, je nejprve vypočítat jednoduchý klouzavý průměr a poté jej použít místo skutečné nejnovější hodnoty.

Přidání klouzavého průměrného trendu do sloupcového grafu

Pokud máte datovou sadu a vytváříte pomocí ní sloupcový graf, můžete také přidat čáru trendu klouzavého průměru několika kliknutími.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže:

Níže jsou uvedeny kroky k vytvoření sloupcového grafu pomocí těchto dat a přidání třídílné trendové křivky klouzavého průměru:

1. Vyberte datovou sadu (včetně záhlaví)
2. Klikněte na kartu Vložit
3. Ve skupině Graf klikněte na ikonu „Vložit sloupcový nebo sloupcový graf“.
4. Klikněte na možnost Seskupený sloupcový graf. Tím se graf vloží do listu.
5. Když je vybraný graf, klikněte na kartu Návrh (tato karta se zobrazí pouze při výběru grafu)
6. Ve skupině Rozložení grafu klikněte na „Přidat prvek grafu“.
7. Najeďte kurzorem na možnost „Trendline“ a poté klikněte na „Další možnosti trendové linie“
8. V podokně Formátovat trendovou čáru vyberte možnost „Klouzavý průměr“ a nastavte počet období.

A je to! Výše uvedené kroky by do vašeho sloupcového grafu přidaly pohyblivou trendovou čáru.

V případě, že chcete vložit více než jednu trendovou linii klouzavého průměru (například jednu pro 2 období a jednu pro 3 období), opakujte kroky 5 až 8).

Stejné kroky můžete použít i k vložení trendové klouzavé průměrné čáry do spojnicového grafu.

Formátování čáry trendu klouzavého průměru

Na rozdíl od běžného spojnicového grafu trendová čára klouzavého průměru neumožňuje mnoho formátování. Pokud například chcete zvýraznit konkrétní datový bod na trendové čáře, nebudete to moci udělat.

Několik věcí, které můžete formátovat v trendové linii, zahrnuje:

• Barva linky. Toho můžete využít ke zvýraznění jedné z trendových linek tak, že vše v grafu zesvětlíte barvou a trendovou linku vyskočí jasnou barvou
• The tloušťka linky
• The průhlednost linky

Chcete-li naformátovat trendovou čáru klouzavého průměru, klikněte na ni pravým tlačítkem a poté vyberte možnost Formátovat linii trendu.

Vpravo se otevře podokno Format Trendline. Toto podokno obsahuje všechny možnosti formátování (v různých sekcích - Možnosti výplně a čáry, Efekty a Trendline).