Predikčné nástroje v programe Microsoft Excel

Predpovedanie je veľmi dôležitým prvkom takmer v každej oblasti činnosti, od ekonomiky až po inžiniering. Existuje veľké množstvo softvéru, ktorý sa špecializuje práve týmto smerom. Bohužiaľ, nie všetci používatelia vedia, že bežná tabuľka programu Excel má vo svojich arzenálových nástrojoch prognózovanie, ktoré svojou efektívnosťou nie sú oveľa nižšie ako profesionálne programy. Poďme zistiť, aké sú tieto nástroje a ako predpovedať v praxi.

Postup predpovedania

Cieľom akejkoľvek predikcie je identifikovať aktuálny trend a určiť očakávaný výsledok vzhľadom na študovaný objekt v určitom časovom bode v budúcnosti.

Metóda 1: Trendline

Jedným z najpopulárnejších typov grafického prognózovania v programe Excel je extrapolácia vykonávaná budovaním trendu.

Pokúsime sa predpovedať zisk podniku za tri roky na základe údajov o tomto ukazovateli za posledných 12 rokov.

1. Závislosť sa vykresľuje na základe tabuľkových údajov pozostávajúcich z argumentov a hodnôt funkcie. Za týmto účelom vyberte oblasť tabuľky a potom na karte "Vložiť" kliknite na ikonu požadovaného typu diagramu, ktorý je v blokoch "Diagramy" . Potom vyberte vhodný typ pre konkrétnu situáciu. Najlepšie je vybrať bitovú mapu. Môžete si vybrať iný pohľad, ale potom, aby sa údaje zobrazili správne, budete musieť vykonať úpravy, najmä odstrániť riadok argumentov a vybrať inú stupnicu pre vodorovnú os.
2. Teraz musíme vybudovať trendovú líniu. Kliknite pravým tlačidlom myši na niektorý z bodov grafu. V aktivovanom kontextovom menu zvoľte "Pridať trendový riadok" .
3. Otvorí sa okno pre formátovanie trendu. Môže vybrať jeden zo šiestich typov aproximácie:
   • Lineárny ;
   • Logaritmický ;
   • Exponenciálne ;
   • Stupeň výkonu ;
   • Polynóm ;
   • Lineárne filtrovanie .

   Najskôr si zvolíme lineárnu aproximáciu.

   V poli Nastavenia "Predpoveď" v poli "Preposlať do" nastavte číslo "3.0" , pretože potrebujeme vytvoriť prognózu na ďalšie tri roky. Okrem toho môžete skontrolovať nastavenie "Ukázať rovnicu v diagrame" a "Umiestniť hodnotu presnosti aproximácie (R ^ 2) na diagram" . Posledný ukazovateľ odráža kvalitu trendu. Po vykonaní nastavení kliknite na tlačidlo "Zavrieť" .

4. Trendová čiara je postavená a na ňom môžeme určiť približnú sumu zisku za tri roky. Ako vidíme, v tom čase by malo presiahnuť 4500 tisíc rubľov. Koeficient R2 , ako už bolo spomenuté vyššie, odráža kvalitu trendu. V našom prípade R2 je 0,89 . Čím vyšší je koeficient, tým vyššia je spoľahlivosť linky. Jeho maximálna hodnota sa môže rovnať 1 . Všeobecne sa uznáva, že s koeficientom nad 0,85 je trendová čiara spoľahlivá.
5. Ak nie ste spokojní s úrovňou spoľahlivosti, môžete sa vrátiť do okna formátu trendu a zvoliť akýkoľvek iný typ aproximácie. Môžete vyskúšať všetky dostupné možnosti, aby ste našli čo najpresnejšie.

   Treba poznamenať, že efektívna prognóza pomocou extrapolácie prostredníctvom trendu môže byť, ak prognózované obdobie nepresiahne 30% analyzovanej bázy. To znamená, že pri analýze obdobia 12 rokov nemôžeme efektívne predpovedať viac ako 3-4 roky. Ale aj v tomto prípade bude pomerne spoľahlivé, ak počas tejto doby nedôjde k vyššej moci alebo naopak k mimoriadne priaznivým okolnostiam, ktoré neboli v predchádzajúcich obdobiach.

Lekcia: Ako vytvoriť trendu v programe Excel

Metóda 2: PREDIKCIA operátora

Extrapoláciu tabuľkových údajov možno vykonať pomocou štandardnej funkcie Exxel PRESCASE . Tento argument patrí do kategórie štatistických nástrojov a má nasledujúcu syntax:

=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

"X" je argument, ktorého funkčná hodnota sa má určiť. V našom prípade bude argumentom rok, v ktorom sa má prognóza uskutočniť.

"Známe hodnoty y" sú základom známych hodnôt funkcie. V našom prípade zohráva úlohu zisku za predchádzajúce obdobia.

"Známe hodnoty x" sú argumenty, ktoré zodpovedajú známym hodnotám funkcie. V ich úlohe sme po celé roky očíslovaní, o ktorých sa zhromažďovali informácie o ziskoch minulých rokov.

Samozrejme, časová línia nemusí nutne byť argumentom. Napríklad môže to byť teplota a funkčnou hodnotou môže byť úroveň rozťažnosti vody pri zahrievaní.

Pri výpočte tejto metódy sa používa metóda lineárnej regresie.

Pozrime sa na nuansy používania operátora PRESCASE na konkrétny príklad. Vezmite rovnakú tabuľku. Budeme musieť zistiť prognózu zisku na rok 2018.

1. Vyberte prázdnu bunku na hárku, kde chcete vytlačiť výsledok spracovania. Kliknite na tlačidlo "Vložiť funkciu" .
2. Otvorí sa Sprievodca funkciami. V kategórii "Štatistické" zvoľte názov "PRESCASE" a potom kliknite na tlačidlo "OK" .
3. Otvorí sa okno s argumentmi. V poli "X" špecifikujeme hodnotu argumentu, ktorému chceme nájsť hodnotu funkcie. V našom prípade je to rok 2018. Preto zadáme položku "2018" . Ale je lepšie špecifikovať tento indikátor v bunke na hárku a v poli "X" len dať odkaz na to. To umožní v budúcnosti automatizovať výpočty av prípade potreby jednoducho zmeniť rok.

   V poli "Známe hodnoty y" uvádzame súradnice stĺpca "Podnikový zisk" . Môžete to urobiť nastavením kurzorového poľa a stlačením ľavého tlačidla myši a výberom príslušného stĺpca na hárku.

   Podobne v poli "Známe hodnoty x" zadáme adresu stĺpca "Rok" s údajmi za uplynulé obdobie.

   Po zadaní všetkých informácií kliknite na tlačidlo "OK" .

4. Operátor vypočíta na základe zadaných údajov a zobrazí výsledok na obrazovke. Pre rok 2018 sa plánuje zisk okolo 4564,7 tisíc rubľov. Na základe výslednej tabuľky môžeme vykresliť graf pomocou nástrojov na mapovanie, ktoré sú uvedené vyššie.
5. Ak zmeníte rok v bunke, ktorá bola použitá na zadanie argumentu, výsledok sa zmení a graf sa automaticky aktualizuje. Napríklad, podľa predpovedí v roku 2019, výška zisku bude 4637,8 tisíc rubľov.

Nezabúdajte však, že rovnako ako pri zostavovaní trendu, časový interval do prognózovaného obdobia by nemal presiahnuť 30% celého obdobia, za ktoré sa databáza nahromadila.

Lekcia: Extrapolácia v programe Excel

Metóda 3: TRENDY prevádzkovateľa

Pre predikciu môžete použiť ešte jednu funkciu - TRENDS . Patrí tiež do kategórie štatistických prevádzkovateľov. Jeho syntax sa v mnohých ohľadoch podobá syntaxi nástroja PRESCASE a vyzerá takto:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Ako vidíte, argumenty "Známe hodnoty y" a "Známe hodnoty x" úplne zodpovedajú podobným prvkom operátora PRESCASE a argument "Nové hodnoty x" zodpovedá argumentu "X" predchádzajúceho nástroja. Okrem toho má TRENDS dodatočný argument "Constant" , ale nie je povinný a používa sa len vtedy, ak existujú konštantné faktory.

Tento operátor sa najefektívnejšie používa v prítomnosti lineárnej závislosti funkcie.

Pozrime sa, ako tento nástroj bude pracovať všetko s rovnakým súborom údajov. S cieľom porovnať získané výsledky budeme určovať prognózovaný bod v roku 2019.

1. Označíme bunku pre výstup výsledku a spustíme funkčnú sprievodcu obvyklým spôsobom. V kategórii "Štatistické" nájdeme a vyberieme názov "TRENDS" . Kliknite na tlačidlo "OK" .
2. Otvorí okno s argumentom operátora TENDENCY. V poli "Známe hodnoty y" zadáme súradnice stĺpca "Podnikový zisk " spôsobom popísaným vyššie. V poli "Známe hodnoty x" zadajte adresu stĺpca "Rok" . V poli "Nové hodnoty x" zadáme odkaz na bunku, kde sa nachádza číslo roka, ktorému chcete určiť predpoveď. V našom prípade je to rok 2019. Pole "Konštantné" zostáva prázdne. Klikneme na tlačidlo "OK" .
3. Operátor spracováva údaje a zobrazí výsledok na obrazovke. Ako vidíte, súčet predpokladaného zisku za rok 2019, vypočítaný metódou lineárnej závislosti, spôsobí rovnako ako v predchádzajúcej metóde výpočtu 4637,8 tisíc rubľov.

Metóda 4: Operátor RAST

Ďalšou funkciou, pomocou ktorej môžete robiť predpovede v programe Excel, je obsluha GROWTH. Tiež odkazuje na štatistickú skupinu nástrojov, ale na rozdiel od predchádzajúcich sa metóda aplikuje nie metódou lineárnej závislosti, ale exponenciálnou metódou. Syntax tohto nástroja vyzerá takto:

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Ako vidíte, argumenty tejto funkcie presne opakujú argumenty operátora TENDENCY , preto ich nebudeme diskutovať druhýkrát, ale okamžite sa obrátime na aplikáciu tohto nástroja v praxi.

1. Vyberte výstupnú bunku a zavolajte sprievodcu funkciami obvyklým spôsobom. V zozname štatistických operátorov vyhľadajte položku "GROWTH" , vyberte ju a kliknite na tlačidlo "OK" .
2. Okno s argumentmi uvedenej funkcie je aktivované. Údaje v poliach tohto okna zadávame rovnakým spôsobom, ako sme ich zadali do okna argumentov operátora TENDENCY. Po zadaní informácií kliknite na tlačidlo "OK" .
3. Výsledok spracovania údajov sa zobrazuje na monitore v bunke uvedenej skôr. Ako môžete vidieť, tentoraz výsledok je 4682,1 tisíc rubľov. Rozdiely medzi výsledkami spracovania údajov prevádzkovateľom TENDENCY sú nevýznamné, ale sú k dispozícii. Je to spôsobené tým, že tieto nástroje používajú rôzne metódy výpočtu: metódu lineárnej závislosti a metódu exponenciálnej závislosti.

Metóda 5: LINEST operátor

Operátor LINEST používa metódu lineárnej aproximácie. Nemal by sa zamieňať s metódou lineárnej závislosti, ktorú používa nástroj TRENDS . Jeho syntax je toto:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Posledné dva argumenty sú nepovinné. S prvými dvoma sme oboznámení s predchádzajúcimi metódami. Ale pravdepodobne ste si všimli, že v tejto funkcii nie je žiadny argument, ktorý by poukazoval na nové hodnoty. Faktom je, že tento nástroj určuje iba zmenu výšky výnosu za jednotku obdobia, čo je v našom prípade jeden rok, ale celková suma sa má vypočítať samostatne a k poslednej skutočnej hodnote zisku sa vypočíta výsledok výpočtu operátora LINEST vynásobený počtom rokov.

1. Vybrali sme bunku, v ktorej sa uskutoční výpočet, a spustite Sprievodcu funkciami. Zvoľte meno "LINEST" v kategórii "Štatistické údaje" a kliknite na tlačidlo "OK" .
2. V poli "Známe hodnoty y" v okne otvoreného argumentu zadajte súradnice stĺpca "Podnikový zisk". V poli "Známe hodnoty x" zadáme adresu stĺpca "Rok" . Zostávajúce polia zostávajú prázdne. Potom kliknite na tlačidlo "OK" .
3. Program vypočíta a zobrazí hodnotu lineárneho trendu vo vybranej bunke.
4. Teraz musíme zistiť hodnotu predpokladaného zisku za rok 2019. Značku "=" nastavte na ľubovoľnú prázdnu bunku na hárku. Klikneme na bunku, ktorá obsahuje skutočnú sumu zisku za posledný študovaný rok (2016). Vložte znak "+" . Ďalej kliknite na bunku, ktorá obsahuje predtým vypočítaný lineárny trend. Vložili sme znak "*" . Keďže medzi posledným rokom študijného obdobia (2016) a rokom, na ktorý sa má predpoveď urobiť (2019), je obdobie tri roky, potom sme v bunke nastavili číslo "3" . Ak chcete vykonať výpočet, kliknite na tlačidlo Enter .

Ako môžeme vidieť, predpokladaný zisk, vypočítaný metódou lineárnej aproximácie, bude v roku 2019 4614,9 tisíc rubľov.

Metóda 6: operátor LGRFPRIBL

Posledným nástrojom, ktorý budeme zvažovať, bude LGRF . Tento operátor vykoná výpočty na základe metódy exponenciálnej aproximácie. Jeho syntax má nasledujúcu štruktúru:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Ako vidíte, všetky argumenty úplne opakujú príslušné prvky predchádzajúcej funkcie. Algoritmus výpočtu prognózy sa mierne zmení. Funkcia vypočíta exponenciálny trend, ktorý ukáže, koľkokrát sa objem výnosov za jedno obdobie zmení, to znamená za jeden rok. Budeme musieť nájsť rozdiel v zisku medzi posledným skutočným a prvým plánovaným obdobím a vynásobiť ho počtom plánovaných období (3) a pripočítať k výsledku sumu posledného aktuálneho obdobia.

1. V zozname operátorov Sprievodcov funkcií vyberte názov "LGRFI" . Kliknite na tlačidlo "OK" .
2. Otvorí sa okno s argumentmi. V ňom zadáme údaje presne tak, ako sme urobili s funkciou LINEST . Klikneme na tlačidlo "OK" .
3. Výsledok exponenciálneho trendu sa vypočíta a vystupuje do určenej bunky.
4. Vložte "=" do prázdnej bunky. Otvorte zátvorky a vyberte bunku, ktorá obsahuje hodnotu výnosu za posledné skutočné obdobie. Vložte znak "*" a vyberte bunku s exponenciálnym trendom. Zadali sme znamienko mínus a opäť klikneme na prvok, v ktorom sa nachádza výnosy za posledné obdobie. Zatvoríme držiak a riadime znaky "* 3 +" bez úvodzoviek. Znova kliknite na rovnakú bunku, ktorá bola naposledy zvolená. Pre vykonanie výpočtu stlačte tlačidlo Enter .

Predpokladaná výška zisku v roku 2019, ktorá bola vypočítaná metódou exponenciálnej aproximácie, bude predstavovať 4 639,2 tisíc rubľov, čo sa opäť výrazne nelíši od výsledkov získaných pri výpočte podľa predchádzajúcich metód.

Lekcia: Ďalšie štatistické funkcie v programe Excel

Zistili sme, akým spôsobom je možné robiť predpovede v programe Excel. Graficky sa to dá dosiahnuť pomocou trendovej čiary a analytickej - pomocou niekoľkých vstavaných štatistických funkcií. Výsledkom spracovania identických údajov týchto operátorov môžu byť rôzne výsledky. Ale to nie je prekvapujúce, pretože všetci používajú rôzne výpočtové metódy. Ak sú výkyvy malé, všetky tieto možnosti, ktoré sa vzťahujú na konkrétny prípad, možno považovať za relatívne spoľahlivé.

• ← Transpozícia matice v programe Microsoft Excel
• Výpočet NPV v programe Microsoft Excel →