vynikať

Metóda pohybujúceho sa priemeru v programe Microsoft Excel

Metóda pohyblivého priemeru je štatistický nástroj, pomocou ktorého môžete riešiť rôzne úlohy. Najmä sa často používa pri prognózovaní. V programe Excel môžete tento nástroj použiť aj na vyriešenie viacerých úloh. Pozrime sa, ako sa v programe Excel používa kĺzavý priemer.

Aplikácia kĺzavého priemeru

Význam tejto metódy spočíva v tom, že pomocou jej pomoci dochádza k zmene absolútnych dynamických hodnôt vybranej série na priemernú aritmetiku v určitom období vyrovnaním údajov. Tento nástroj sa používa na ekonomické výpočty, predpovedanie, v procese obchodovania na burze atď. Použitie metódy Pohyblivo priemer v programe Excel sa najlepšie vykoná pomocou výkonného nástroja na štatistické spracovanie údajov nazvaného Analysis Pack . Okrem toho môžete na rovnaký účel použiť vstavanú funkciu Excel AVERAGE .

Metóda 1: Balík analýz

Balík analýzy je doplnok programu Excel, ktorý je predvolene zakázaný. Preto je v prvom rade potrebné ho zahrnúť.

  1. Prejdeme na kartu "Súbor" . Kliknite na položku "Možnosti" .

    2. Prejdite na možnosti v programe Microsoft Excel

  2. V okne, ktoré sa otvorí, prejdite do sekcie "Doplnky" . V spodnej časti okna by mal byť parameter "Doplnky programu Excel" nastavený v poli "Spravovať" . Kliknite na tlačidlo Prejsť .

    3. Prejdite do doplnkov v programe Microsoft Excel

  3. Dostávame sa do okna doplnkov. Začiarknite políčko vedľa balíka "Analýza" a kliknite na tlačidlo "OK" .

Doplnkové okno v programe Microsoft Excel

Po tejto akcii sa aktivuje balík "Analýza údajov" a na karte "Data" sa zobrazí príslušné tlačidlo.

Teraz sa pozrime na to, ako môžete priamo použiť možnosti analýzy dát pre metódu s kĺzavým priemerom. Predstavme si prognózu za dvanásty mesiac na základe informácií o príjmoch spoločnosti za 11 predchádzajúcich období. Na tento účel používame tabuľku s údajmi, ako aj nástroje nástroja na analýzu .

  1. Prejdite na kartu "Dáta" a kliknite na tlačidlo "Analýza údajov" , ktoré sa nachádza na páse nástrojov v bloku "Analýza" .

    2. Prejdite do nástrojov na analýzu údajov v programe Microsoft Excel

  2. Zobrazí sa zoznam nástrojov dostupných v balíčku na analýzu . Vyberte z nich názov "kĺzavý priemer" a kliknite na tlačidlo "OK" .

    3. Zoznam nástrojov na analýzu v programe Microsoft Excel

  3. Otvorí sa okno na zadávanie údajov na prognózovanie pomocou metódy kĺzavého priemeru.

    Do poľa "Interval zadania " zadajte adresu rozsahu, v ktorom sa mesačný výnos vypočíta bez bunky, pričom údaje, ktoré sa majú vypočítať.

    V poli "Interval" zadajte interval pre spracovanie hodnôt pomocou metódy anti-aliasing. Po prvé, nastavíme hodnotu anti-aliasing na tri mesiace, a preto zadáme číslo "3" .

    V poli "Výstupný interval" musíte na hárku zadat ľubovoľný prázdny rozsah, v ktorom budú údaje po spracovaní odoslané, čo musí byť jedna bunka väčšia ako vstupný interval.

    Takisto začiarknite políčko "Štandardné chyby" .

    Ak je to potrebné, môžete skontrolovať aj pole "Výstupný graf" pre vizuálnu prezentáciu, hoci v našom prípade to nie je potrebné.

    Po vykonaní všetkých nastavení kliknite na tlačidlo "OK" .

    4. Okno nástroja na analýzu údajov Pohyblivý priemer v programe Microsoft Excel

  4. Program zobrazí výsledok spracovania.

    5. Výsledok spracovania vyhladzovania po dobu 3 mesiacov v programe Microsoft Excel

  5. Teraz vykonajte vyhladzovanie po dobu dvoch mesiacov, aby ste zistili, ktorý výsledok je správnejší. Na tieto účely opäť spustime nástroj "Pohyblivý priemer" balíka analýz .

    V poli "Vstupný interval" ponechávame rovnaké hodnoty ako v predchádzajúcom prípade.

    V poli "Interval" uvádzame číslo "2" .

    V poli "Výstupný interval" špecifikujeme adresu nového prázdneho rozsahu, ktorý by opäť mal byť jedna bunka väčšia ako vstupný interval.

    Ostatné nastavenia sú rovnaké. Potom kliknite na tlačidlo "OK" .

    6. Okno nástroja na analýzu údajov Pohyblivý priemer v programe Microsoft Excel

  6. Potom program vypočíta a zobrazí výsledok na obrazovke. Aby sme určili, ktorý z dvoch modelov je presnejší, musíme porovnať štandardné chyby. Čím je daný index menší, tým vyššia je pravdepodobnosť presnosti dosiahnutého výsledku. Ako je zrejmé, pri všetkých hodnotách je štandardná chyba pri výpočte dvojmesačného valcovania nižšia ako ten istý ukazovateľ po dobu troch mesiacov. Preto sa predpokladaná hodnota pre december môže považovať za hodnotu vypočítanú posuvnou metódou za posledné obdobie. V našom prípade je táto hodnota 990,4 tisíc rubľov.

Výsledok spracovania vyhladzovania po dobu 2 mesiacov v programe Microsoft Excel

Metóda 2: Použite funkciu AVERAGE

V programe Excel existuje iný spôsob, ako použiť metódu s pohyblivým priemerom. Ak ho chcete použiť, musíte použiť niekoľko štandardných funkcií programu, z ktorých základný je pre náš účel priemerný . Napríklad použijeme rovnakú tabuľku príjmov spoločnosti ako v prvom prípade.

Rovnako ako v minulosti potrebujeme vytvoriť vyhladzovanú časovú sériu. Tentoraz však tieto akcie nebudú tak automatizované. Mali by ste vypočítať priemernú hodnotu pre každé dve a potom tri mesiace, aby ste mohli porovnať výsledky.

Najprv vypočítame priemerné hodnoty za dve predchádzajúce obdobia pomocou funkcie AVERAGE . Môžeme to urobiť až od marca, pretože pre neskoršie dátumy dochádza k prerušeniu hodnôt.

  1. Vyberte bunku v prázdnom stĺpci v riadku pre mesiac Marec. Potom kliknite na ikonu "Vložiť funkciu" , ktorá sa nachádza v blízkosti riadku vzorca.

    2. Prejdite na sprievodcu funkciami v programe Microsoft Excel

  2. Okno Sprievodcovia je aktivované. V kategórii "Štatistické" hľadáme hodnotu "AVERAGE" , vyberte ju a kliknite na tlačidlo "OK" .

    3. Prejdite na argumenty funkcie AVERAGE v programe Microsoft Excel

  3. Otvorí sa okno argumentov príkazu AVERAGE . Syntax je nasledujúci:

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

    Vyžaduje sa iba jeden argument.

    V našom prípade v poli "Číslo 1" musíme uviesť odkaz na rozsah, v ktorom sú uvedené príjmy za dve predchádzajúce obdobia (január a február). Nastavte kurzor do poľa a vyberte príslušné bunky na hárku v stĺpci "Výnosy" . Potom kliknite na tlačidlo "OK" .

    4. argumenty funkcie AVERAGE v programe Microsoft Excel

  4. Ako vidíte, výsledok výpočtu priemeru za predchádzajúce dve obdobia bol zobrazený v bunke. Aby sme vykonali takéto výpočty za všetky ostatné mesiace obdobia, musíme tento vzorec skopírovať do iných buniek. K tomu sa stáva kurzor v pravom dolnom rohu bunky obsahujúcej túto funkciu. Kurzor sa prevádza na značku vyplnenia, ktorá vyzerá ako kríž. Uchopte ľavé tlačidlo myši a presuňte ho na úplný koniec stĺpca.

    5. Filler v programe Microsoft Excel

  5. Dostávame výpočet výsledkov priemeru za dva predchádzajúce mesiace pred koncom roka.

    6. Priemer za predchádzajúce 2 mesiace v programe Microsoft Excel

  6. Teraz vyberte bunku v nasledujúcom prázdnom stĺpci v riadku pre apríl. Voláme okno s argumentmi funkcie AVERAGE rovnakým spôsobom, ako sme opísali vyššie. V poli "Číslo1" zadáme súradnice buniek v stĺpci "Výnosy" od januára do marca. Potom kliknite na tlačidlo "OK" .

    7. Argumenty pre funkciu AVERAGE počas 3 mesiacov v programe Microsoft Excel

  7. Pomocou značky naplnenia skopírujte vzorec do buniek nižšie uvedenej tabuľky.

    8. Použitie popisovač výplne v programe Microsoft Excel

  8. Takže sme vypočítali hodnoty. Teraz, ako v minulosti, budeme musieť zistiť, aký druh analýzy je lepší: s vyhladzovaním 2 alebo 3 mesiace. Vykonajte to, vypočítajte priemernú štvorcovú odchýlku a niektoré ďalšie ukazovatele. Najprv vypočítame absolútnu odchýlku pomocou štandardnej funkcie Excel, ktorá namiesto pozitívnych alebo záporných čísel vracia ich modul. Táto hodnota sa bude rovnať rozdielu medzi skutočným výnosom za vybraný mesiac a predpokladaným. Nastavte kurzor na ďalší prázdny stĺpec v riadku pre mesiac máj. Zavolajte sprievodcu funkciami .

    9. Vložiť funkciu v programe Microsoft Excel

  9. V kategórii "Matematické" zvýrazňujeme názov funkcie "ABS" . Kliknite na tlačidlo "OK" .

    10. Prechod na argumenty funkcie ABS v programe Microsoft Excel

  10. Okno s argumentmi funkcií ABS sa otvorí. V jednom poli "Číslo" uvádzame rozdiel medzi obsahom buniek v stĺpcoch "Výnosy" a "2 mesiace" pre máj. Potom kliknite na tlačidlo "OK" .

    11. Argumenty funkcie ABS v programe Microsoft Excel

  11. Pomocou výplne skopírujte tento vzorec do všetkých riadkov tabuľky vrátane novembra vrátane.

    12. Absolútne odchýlky v programe Microsoft Excel

  12. Priemernú hodnotu absolútnej odchýlky vypočítavame za celé obdobie pomocou známej funkcie AVERAGE .

    13. Priemerná hodnota absolútnej odchýlky v programe Microsoft Excel

  13. Vykonávame podobný postup, aby sme vypočítali absolútnu odchýlku pre pohyblivú za 3 mesiace. Najskôr aplikujte funkciu ABS . Iba tentokrát zvažujeme rozdiel medzi obsahom buniek so skutočným príjmom a plánovaným, vypočítaným metódou kĺzavého priemeru na 3 mesiace.

    14. Absolútne odchýlky 3 mesiace v programe Microsoft Excel

  14. Ďalej vypočítajte priemer všetkých údajov o absolútnej odchýlke pomocou funkcie AVERAGE .

    15. Priemerná hodnota absolútnej odchýlky v programe Microsoft Excel 3 mesiace

  15. Ďalším krokom je vypočítať relatívnu odchýlku. Rovná sa pomeru absolútnej odchýlky k skutočnému indikátoru. Aby sme sa vyhli negatívnym hodnotám, opäť využívame príležitosti ponúkané operátorom ABS . Pomocou tejto funkcie tentokrát rozdelíme hodnotu absolútnej odchýlky pomocou metódy kĺzavého priemeru na 2 mesiace pre skutočný príjem za vybraný mesiac.

    16. Relatívne odmietnutie v programe Microsoft Excel

  16. Relatívna odchýlka sa však zvyčajne zobrazuje ako percento. Preto vyberte príslušný rozsah na hárku, prejdite na kartu "Domov" , kde v poli "Číslo" v poli špeciálneho formátu nastavte percentuálny formát. Potom sa výsledok výpočtu relatívnej odchýlky zobrazí v percentách.

    17. Zmeniť formát v programe Microsoft Excel

  17. Podobná operácia na výpočet relatívnej odchýlky sa vykonáva s údajmi s použitím vyhladzovania po dobu 3 mesiacov. Len v tomto prípade, aby sme vypočítali ako deliteľný, použijeme ďalší stĺpec tabuľky, ktorá má názov "Abs. (3m) " . Potom prepočítame číselné hodnoty na percentuálny podiel.

    18. Relatívna odchýlka pre línie sklzu 2 mesiace v programe Microsoft Excel

  18. Potom vypočítajte priemerné hodnoty pre obe stĺpce s relatívnou odchýlkou, ako pred použitím funkcie AVERAGE . Vzhľadom na to, že na parametre používame percentuálne hodnoty ako argumenty, nemusíte ju konvertovať. Operátor výstup výsledok v percentuálnom formáte.

    19. Stredné hodnoty relatívnej odchýlky v programe Microsoft Excel

  19. Teraz sme dospeli k výpočtu strednej štvorcovej odchýlky. Tento indikátor nám umožní priamo porovnať kvalitu výpočtu pri používaní vyhladzovania počas dvoch a troch mesiacov. V našom prípade sa štandardná odchýlka rovná druhému odmocninu súčtu štvorcov rozdielov skutočného príjmu a kĺzavého priemeru vydeleného počtom mesiacov. Aby sme mohli vykonať výpočet v programe, musíme použiť niekoľko funkcií, najmä ROOT , SUMMKVRAZN a ACCOUNT . Napríklad na výpočet strednej štvorcovej odchýlky pri použití linky vyhladzovania na dva mesiace v máji sa v našom prípade použije nasledujúci vzorec:

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

    Skopírujte ho do iných buniek v stĺpci pomocou výpočtu strednej štvorcovej odchýlky pomocou značky naplnenia.

    20. Výpočet štandardnej odchýlky v programe Microsoft Excel

  20. Podobná operácia na výpočet priemernej štvorcovej odchýlky sa vykonáva pre kĺzavý priemer 3 mesiace.

    21. Výpočet štandardnej odchýlky kĺzavého priemeru za 3 mesiace v programe Microsoft Excel

  21. Potom vypočítame priemernú hodnotu pre celé obdobie pre obidva tieto ukazovatele, pričom použijeme funkciu AVERAGE .

    22. Stredná hodnota štandardnej odchýlky v programe Microsoft Excel

  22. Porovnaním výpočtov pomocou metódy kĺzavého priemeru s vyhladzovaním na 2 a 3 mesiace pre takéto ukazovatele ako sú absolútna odchýlka, relatívna odchýlka a odchýlka medzi koreňmi a strednými štvorcami, môžeme s istotou povedať, že vyhladzovanie počas dvoch mesiacov prináša spoľahlivejšie výsledky ako používanie vyhladzovania po dobu troch mesiacov. To je naznačené skutočnosťou, že vyššie uvedené ukazovatele pre dvojmesačný kĺzavý priemer sú kratšie ako tri mesiace.

    23. Mapovanie metrík v programe Microsoft Excel

  23. Preto očakávaný príjem spoločnosti za december bude 990,4 tisíc rubľov. Ako vidíte, táto hodnota sa úplne zhoduje s tou hodnotou, ktorú sme získali výpočtami pomocou nástrojov Analysis Package .

Predpokladané výnosy v programe Microsoft Excel

Lekcia: Sprievodca funkciami v programe Excel

Prognózu sme vypočítali metódou kĺzavého priemeru dvomi spôsobmi. Ako vidíte, tento postup je oveľa jednoduchšie vykonať pomocou nástrojov analytického balíka . Niektorí používatelia však nie vždy dôverujú automatickému výpočtu a radšej používajú funkciu AVERAGE a sprievodných operátorov na výpočty na kontrolu najspoľahlivejšej verzie. Aj keď je všetko vykonané správne, výstup výpočtu by mal byť na výstupe rovnaký.