Excel

Metoda Pohyblivého průměru v aplikaci Microsoft Excel

Metoda pohyblivého průměru je statistický nástroj, pomocí něhož můžete vyřešit různé úkoly. Zejména se často používá při prognózování. V aplikaci Excel můžete tento nástroj použít také k vyřešení několika úkolů. Uvidíme, jak se v aplikaci Excel používá klouzavý průměr.

Použití klouzavého průměru

Význam této metody spočívá v tom, že pomocí její pomoci dochází ke změně absolutních dynamických hodnot vybrané série na průměrnou aritmetiku za určité období vyhlazením dat. Tento nástroj se používá pro ekonomické výpočty, prognózy, v procesu obchodování na burze apod. Aplikace metody Moving Average v aplikaci Excel se nejlépe provádí pomocí nástroje pro zpracování statistických dat nazvaného Analysis Pack . Navíc pro stejný účel můžete použít vestavěnou funkci Excel AVERAGE .

Metoda 1: Balíček pro analýzu

Balíček analýzy je doplněk aplikace Excel, který je ve výchozím nastavení zakázán. Proto je zapotřebí jej především zahrnout.

  1. Přejdeme na kartu "Soubor" . Klikněte na položku "Možnosti" .

    2. Přejít na možnosti v aplikaci Microsoft Excel

  2. V okně, které se otevře, přejděte do části "Doplňky" . V dolní části okna by měl být v poli "Spravovat" nastaven parametr "Doplňky aplikace Excel " . Klikněte na tlačítko "Přejít" .

    3. Přejděte do doplňků v aplikaci Microsoft Excel

  3. Dostaneme se do okna doplňků. Zaškrtněte políčko vedle položky "Balíček analýzy" a klikněte na tlačítko "OK" .

Doplněk v aplikaci Microsoft Excel

Po této akci se aktivuje balíček "Analýza dat" a na kartě "Data" se na pásu objeví odpovídající tlačítko.

A teď se podívejme na to, jak můžete přímo využít možnosti analýzy dat pro metodu klouzavého průměru. Představme si prognózu za dvanáctý měsíc na základě informací o příjmech společnosti za 11 předchozích období. K tomu použijeme tabulku s údaji a nástroje nástroje pro analýzu .

  1. Přejděte na kartu "Data" a klikněte na tlačítko "Analýza dat" , které se nachází na pásu nástrojů v bloku "Analýza" .

    2. Přejděte do Nástroje pro analýzu dat v aplikaci Microsoft Excel

  2. Zobrazí se seznam dostupných nástrojů v balíčku pro analýzu . Vyberte z nich název "klouzavý průměr" a klikněte na tlačítko "OK" .

    3. Seznam nástrojů pro analýzu v aplikaci Microsoft Excel

  3. Zobrazí se okno pro zadání dat pro prognózování pomocí metody pohyblivého průměru.

    Do pole "Vstupní interval" určete adresu rozsahu, kde se vypočítávají měsíční výnosy bez buňky, přičemž údaje by měly být vypočítány.

    V poli "Interval" zadejte interval pro zpracování hodnot pomocí metody anti-aliasing. Nejprve nastavíme hodnotu anti-aliasing na tři měsíce, a proto zadáme číslo "3" .

    V poli "Výstupní interval" musíte na listu zadat libovolný prázdný rozsah, kde budou po zpracování zpracovány data, která musí být jedna buňka větší než vstupní interval.

    Zkontrolujte také políčko Standardní chyby .

    Pokud je to nutné, můžete zaškrtnout políčko "Výstupní graf" pro vizuální demonstraci, ačkoli v našem případě to není nutné.

    Po provedení všech nastavení klikněte na tlačítko "OK" .

    4. Položka nástroje pro analýzu dat v Průměru v aplikaci Microsoft Excel

  4. Program zobrazí výsledek zpracování.

    5. Výsledek zpracování vyhlazení po dobu 3 měsíců v aplikaci Microsoft Excel

  5. Nyní proveďte vyhlazení po dobu dvou měsíců, abyste zjistili, který výsledek je správnější. Pro tyto účely opět spustíme nástroj "Pohyblivý průměr" analytického balíčku .

    V poli "Vstupní interval" ponecháme stejné hodnoty jako v předchozím případě.

    V poli "Interval" uvedeme číslo "2" .

    V poli "Výstupní interval" specifikujeme adresu nového prázdného rozsahu, který by opět měl být o jednu buňku větší než vstupní interval.

    Zbytek nastavení je stejný. Poté klikněte na tlačítko "OK" .

    6. Okno nástroje pro analýzu dat Pohybující se průměr v aplikaci Microsoft Excel

  6. Poté program vypočítá a zobrazí výsledek na obrazovce. Abychom zjistili, který z obou modelů je přesnější, musíme porovnat standardní chyby. Čím menší je daný index, tím vyšší pravděpodobnost přesnosti získaného výsledku. Jak je vidět, u všech hodnot je standardní chyba při výpočtu dvouměsíčního pohybu menší než stejný indikátor po dobu 3 měsíců. Předpokládaná hodnota pro prosinec tedy může být považována za hodnotu vypočtenou kluznou metodou za poslední období. V našem případě je tato hodnota 990,4 tisíc rublů.

Výsledek zpracování vyhlazení po dobu 2 měsíců v aplikaci Microsoft Excel

Metoda 2: Použijte funkci AVERAGE

V aplikaci Excel existuje jiný způsob, jak použít metodu klouzavého průměru. Chcete-li jej použít, musíte použít řadu standardních funkcí programu, z nichž základní je pro náš účel průměrné . Například použijeme stejnou tabulku příjmů společnosti jako v prvním případě.

Stejně jako v minulosti potřebujeme vytvořit vyhlazené časové řady. Tentokrát však akce nebudou tak automatizované. Měli byste vypočítat průměrnou hodnotu za každé dvě, a pak za tři měsíce, abyste mohli porovnávat výsledky.

Nejprve vypočítáme průměrné hodnoty za dvě předchozí období pomocí funkce AVERAGE . Můžeme to provést až od března, protože pro pozdější data dochází k přerušení hodnot.

  1. Vyberte buňku v prázdném sloupci v řádku pro březen. Dále klikněte na ikonu "Vložit funkci" , která se nachází poblíž řádku vzorce.

    2. Přejděte na Průvodce funkcí v aplikaci Microsoft Excel

  2. Okno Průvodce je aktivováno. V kategorii "Statistické" hledáme hodnotu "AVERAGE" , vyberte ji a klikněte na tlačítko "OK" .

    3. Přejít na argumenty funkce AVERAGE v aplikaci Microsoft Excel

  3. Zobrazí se okno argumentů příkazu AVERAGE . Syntaxe je následující:

    =СРЗНАЧ(число1;число2;…)

    Je vyžadován pouze jeden argument.

    V našem případě v poli "Číslo 1" musíme zadat odkaz na rozsah, v němž jsou uvedeny příjmy za dvě předchozí období (leden a únor). Nastavte kurzor v poli a vyberte odpovídající buňky na listu ve sloupci "Příjmy" . Poté klikněte na tlačítko "OK" .

    4. argumenty funkce AVERAGE v aplikaci Microsoft Excel

  4. Jak vidíte, výsledek výpočtu průměru za dvě předchozí období byl zobrazen v buňce. K provedení takových výpočtů pro všechny ostatní měsíce daného období musíme tuto vzorec zkopírovat do jiných buněk. K tomu se stáváme kurzorem v pravém dolním rohu článku obsahujícího tuto funkci. Kurzor je převeden na značku pro vyplnění, která vypadá jako kříž. Slepte levým tlačítkem myši a přetáhněte ho dolů na konec sloupce.

    5. Filler v aplikaci Microsoft Excel

  5. Získali jsme výpočet výsledků průměru za dva předchozí měsíce před koncem roku.

    6. Průměr za předchozí dva měsíce v aplikaci Microsoft Excel

  6. Nyní vyberte buňku v dalším prázdném sloupci v řádku pro duben. Nazýváme okno argumentů funkce AVERAGE stejným způsobem, jak je popsáno výše. V poli "Číslo1" zadáváme souřadnice sloupců ve sloupci "Výnosy" od ledna do března. Poté klikněte na tlačítko "OK" .

    7. Argumenty pro funkci AVERAGE po dobu 3 měsíců v aplikaci Microsoft Excel

  7. Pomocí značky pro vyplnění zkopírujte vzorec do buněk tabulky níže.

    8. Použití popisovače výplně v aplikaci Microsoft Excel

  8. Takže jsme vypočítali hodnoty. Nyní, stejně jako v minulosti, budeme muset zjistit, jaký druh analýzy je lepší: s vyhlazením 2 nebo 3 měsíce. Chcete-li to provést, vypočtejte průměrnou čtvercovou odchylku a některé další indikátory. Nejprve vypočítáme absolutní odchylku pomocí standardní funkce Excel ABS , která místo kladného nebo záporného čísla vrací svůj modul. Tato hodnota se bude rovnat rozdílu mezi skutečným výnosem za vybraný měsíc a předpokládaným výnosem. Nastavte kurzor na další prázdný sloupec v řádku v květnu. Zavolejte Průvodce funkcí .

    9. Vložit funkci v aplikaci Microsoft Excel

  9. V kategorii "Matematické" zvýrazňujeme název funkce "ABS" . Klikněte na tlačítko "OK" .

    10. Přechod na argumenty funkce ABS v aplikaci Microsoft Excel

  10. Otevře se okno argumentů funkce ABS . V jednom poli "Číslo" udáváme rozdíl mezi obsahem buněk ve sloupcích "Výnos" a "2 měsíce" pro měsíc květen. Poté klikněte na tlačítko "OK" .

    11. Argumenty funkce ABS v aplikaci Microsoft Excel

  11. Pomocí výplně zkopírujte tento vzorec do všech řádků tabulky až po listopad.

    12. Absolutní odchylky v aplikaci Microsoft Excel

  12. Vypočítáme průměrnou hodnotu absolutní odchylky pro celou dobu pomocí známé funkce AVERAGE .

    13. Průměrná hodnota absolutní odchylky v aplikaci Microsoft Excel

  13. Pro výpočet absolutní odchylky pro pohyblivou hodnotu za 3 měsíce provádíme podobný postup. Nejprve aplikujte funkci ABS . Teprve tentokrát považujeme rozdíl mezi obsahem buněk se skutečným příjmem a plánovaným, vypočítaným metodou klouzavého průměru po dobu 3 měsíců.

    14. Absolutní odchylky po dobu 3 měsíců v aplikaci Microsoft Excel

  14. Dále vypočítat průměr všech absolutních odchylek pomocí funkce AVERAGE .

    15. Průměrná hodnota absolutní odchylky po dobu 3 měsíců v aplikaci Microsoft Excel

  15. Dalším krokem je výpočet relativní odchylky. Je rovno poměru absolutní odchylky k aktuálnímu indikátoru. Abychom předešli negativním hodnotám, opět využíváme příležitosti nabízené operátorem ABS . Tentokrát s využitím této funkce rozdělujeme hodnotu absolutní odchylky metodou klouzavého průměru na 2 měsíce pro skutečný příjem za vybraný měsíc.

    16. Relativní odmítnutí v aplikaci Microsoft Excel

  16. Relativní odchylka se však obvykle zobrazuje jako procentní podíl. Proto vyberte příslušný rozsah na listu, přejděte na kartu "Domů" , kde v poli "Číslo" v poli zvláštního formátu nastavte procentní formát. Poté je výsledek výpočtu relativní odchylky zobrazen v procentech.

    17. Změňte formát v aplikaci Microsoft Excel

  17. Podobná operace pro výpočet relativní odchylky se provádí s údaji s použitím vyhlazení po dobu 3 měsíců. Pouze v tomto případě, abychom vypočítali jako dělitelný, použijeme jiný sloupec tabulky, který má název "Abs. vypnuto (3m) " . Poté překládáme číselné hodnoty do procent.

    18. Relativní odchylka pro skluzu o délce 2 měsíců v aplikaci Microsoft Excel

  18. Poté vypočítat průměrné hodnoty obou sloupců s relativní odchylkou, stejně jako před použitím funkce AVERAGE . Vzhledem k tomu, že pro parametry používáme procentní hodnoty jako argumenty, nemusíte je převádět. Operátor provede výsledek v procentním formátu.

    19. Průměrné hodnoty pro relativní odchylku v aplikaci Microsoft Excel

  19. Nyní jsme dospěli k výpočtu střední kvadratická odchylka. Tento indikátor nám umožní porovnat kvalitu výpočtu při použití anti-aliasingu po dobu dvou a tří měsíců. V našem případě se standardní odchylka rovná odmocnině součtu čtverců rozdílů skutečného příjmu a klouzavého průměru dělených počtem měsíců. Aby bylo možné provést výpočet v programu, musíme použít řadu funkcí, zejména ROOT , SUMMKVRAZN a ACCOUNT . Například pro výpočet střední kvadratická odchylka při použití vyhlazovací linky na dva měsíce v květnu bude v našem případě použit následující vzorec:

    =КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))

    Zkopírujte jej do ostatních buněk ve sloupci pomocí výpočtu střední kvadratická odchylka pomocí značky pro vyplnění.

    20. Výpočet standardní odchylky v aplikaci Microsoft Excel

  20. Podobná operace pro výpočet střední kvadratická odchylka se provádí za klouzavý průměr po dobu 3 měsíců.

    21. Výpočet standardní odchylky klouzavého průměru za 3 měsíce v aplikaci Microsoft Excel

  21. Poté vypočítáme průměrnou hodnotu pro celé období pro oba tyto indikátory s použitím funkce AVERAGE .

    22. Průměr standardní odchylky v aplikaci Microsoft Excel

  22. Porovnáním výpočtů metodou klouzavého průměru s vyhlazením na 2 a 3 měsíce u takových indikátorů jako je absolutní odchylka, relativní odchylka a odchylka od středních čtverců můžeme s jistotou říci, že vyhlazení po dobu dvou měsíců poskytuje spolehlivější výsledky než použití anti-aliasing po dobu tří měsíců. To je naznačeno skutečností, že výše uvedené ukazatele pro klouzavý průměr za dva měsíce jsou kratší než tři měsíce.

    23. Mapování metrik v aplikaci Microsoft Excel

  23. Předpokládané příjmy společnosti za prosinec tak činí 990,4 tisíc rublů. Jak můžete vidět, tato hodnota se zcela shoduje s hodnotou, kterou jsme získali výpočty pomocí nástrojů Analysis Package .

Předpokládané výnosy v aplikaci Microsoft Excel

Lekce: Průvodce funkcemi v aplikaci Excel

Prognózu jsme vypočítali metodou klouzavého průměru dvěma způsoby. Jak můžete vidět, tento postup je mnohem snazší provést pomocí nástrojů analytického balíčku . Nicméně někteří uživatelé ne vždy věří automatickému výpočtu a preferují využívání funkce AVERAGE a doprovodných operátorů pro výpočty ke kontrole nejspolehlivější verze. I když je vše v pořádku, výstup výpočtu by měl být na výstupu přesně stejný.