Excel

Nástroje pro prognózování v aplikaci Microsoft Excel

Prognóza je velmi důležitým prvkem téměř v jakékoli oblasti činnosti, od ekonomiky až po strojírenství. Existuje velké množství softwaru, který se specializuje právě v tomto směru. Bohužel ne všichni uživatelé vědí, že obvyklá tabulka aplikace Excel má ve svých nástrojích pro prognózu arzenál, které svou účinností nejsou hodně horší než profesionální programy. Zjistíme, jaké jsou tyto nástroje a jak předvídat v praxi.

Prognózní postup

Cílem jakékoli předpovědi je identifikovat aktuální trend a určit očekávaný výsledek s ohledem na studovaný objekt v určitém časovém okamžiku v budoucnu.

Metoda 1: Trendline

Jedním z nejoblíbenějších typů grafického prognózování v aplikaci Excel je extrapolace prováděná budováním trendové linie.

Pokusíme se odhadnout zisk podniku za 3 roky na základě údajů o tomto ukazateli za posledních 12 let.

  1. Závislost na základě tabulkových dat sestává z argumentů a hodnot funkce. Chcete-li to provést, vyberte oblast tabulky a poté na kartě "Vložit" klepněte na ikonu požadovaného typu diagramu, který je v bloku "Diagramy" . Poté vyberte vhodný typ konkrétní situace. Nejlepší je vybrat bitmapu. Můžete zvolit jiný pohled, ale pak, aby se data zobrazovala správně, budete muset provést úpravy, zejména odstranit řádek argumentů a vybrat jinou stupnici pro vodorovnou osu.

    2. Plotování v aplikaci Microsoft Excel

  2. Teď potřebujeme vybudovat trendovou linii. Klepněte pravým tlačítkem na některý z bodů grafu. V aktivované kontextové nabídce vyberte možnost "Přidat trendovou linku" .

    3. Přidání trendu do aplikace Microsoft Excel

  3. Otevře se okno pro formátování trendové čáry. Může zvolit jeden ze šesti typů aproximace:
    • Lineární ;
    • Logaritmický ;
    • Exponenciální ;
    • Stupeň výkonu ;
    • Polynom ;
    • Lineární filtrování .

    Nejprve zvolíme lineární aproximaci.

    V poli Nastavení "Předpověď" v poli "Předat do" nastavte číslo "3.0" , protože potřebujeme provést prognózu na tři roky dopředu. Kromě toho můžete zkontrolovat nastavení "Zobrazit rovnici v diagramu" a "Umístěte hodnotu aproximace přesnosti (R ^ 2) na diagram" . Poslední ukazatel odráží kvalitu trendové linie. Po provedení nastavení klikněte na tlačítko Zavřít .

    4. Parametry Trendline v aplikaci Microsoft Excel

  4. Trendová linka je postavena a na ní můžeme stanovit přibližnou výši zisku za tři roky. Jak vidíme, v té době by měla překročit 4500 tisíc rublů. Koeficient R2 , jak již bylo zmíněno výše, odráží kvalitu trendové linie. V našem případě R2 je 0,89 . Čím vyšší koeficient, tím vyšší je spolehlivost linky. Jeho maximální hodnota může být rovna 1 . Obecně platí, že s koeficientem nad 0,85 je trendová linie spolehlivá.

    5. Trendová řada je postavena v aplikaci Microsoft Excel

  5. Pokud nejste spokojeni s úrovní spolehlivosti, můžete se vrátit do okna formátu trendové linky a vybrat libovolný jiný typ aproximace. Můžete vyzkoušet všechny dostupné možnosti k nalezení nejpřesnějších.

    Výběr jiného typu přiblížení v aplikaci Microsoft Excel

    Je třeba poznamenat, že efektivní prognóza pomocí extrapolace prostřednictvím trendové linie může být, pokud prognóza nepřesáhne 30% analyzovaného období. To znamená, že při analýze období 12 let nemůžeme provést účinnou prognózu více než 3-4 roky. Ale i v tomto případě bude poměrně spolehlivé, pokud během této doby nebude docházet k žádnému případu vyšší moci nebo naopak k extrémně příznivým okolnostem, které nebyly v předchozích obdobích.

Lekce: Jak vytvořit trendovou řadu v aplikaci Excel

Metoda 2: Operátor PREDICTION

Extrapolaci tabelárních dat lze provést pomocí standardní funkce Exxel PRESCASE . Tento argument patří do kategorie statistických nástrojů a má následující syntaxi:

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

"X" je argument, jehož funkční hodnota má být určena. V našem případě bude sloužit argument jako rok, ve kterém by mělo dojít k předpovědi.

"Známé hodnoty y" jsou základem známých hodnot funkce. V našem případě hraje roli zisku za předchozí období role.

"Známé hodnoty x" jsou argumenty, které odpovídají známým hodnotám funkce. V jejich roli jsme počítali roky, pro které byly shromážděny informace o ziscích minulých let.

Samozřejmě, časový řádek nemusí být nutně argument. Může se jednat např. O teplotu a funkční hodnotou může být úroveň roztažnosti vody při zahřátí.

Při výpočtu této metody se používá metoda lineární regrese.

Podívejme se na nuance použití operátora PRESCASE pro konkrétní příklad. Vezměte stejnou tabulku. Budeme potřebovat zjistit prognózu zisku na rok 2018.

  1. Vyberte prázdnou buňku na listu, kde chcete vytisknout výsledek zpracování. Klikněte na tlačítko "Vložit funkci" .

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

  2. Otevře se Průvodce funkcí . V kategorii "Statistické" vyberte název "PRESCASE" a poté klikněte na tlačítko "OK" .

    3. Přepínání na argumenty funkce PRESET v aplikaci Microsoft Excel

  3. Otevře se okno s argumenty. V poli "X" specifikujeme hodnotu argumentu, kterému chceme najít hodnotu funkce. V našem případě je to rok 2018. Proto zadáme položku "2018" . Je však lepší specifikovat tento indikátor v buňce na listu a v poli "X" jen dát odkaz na něj. To v budoucnu umožní automatizovat výpočty a v případě potřeby snadno změnit rok.

    V poli "Známé hodnoty y" udáváme souřadnice sloupce "Podnikový zisk" . To lze provést nastavením kurzoru v poli a podržením levého tlačítka myši a výběrem příslušného sloupce na listu.

    Podobně v poli "Známé hodnoty x" zadáváme adresu sloupce "Rok" s údaji za uplynulé období.

    Po zadání všech informací klikněte na tlačítko "OK" .

    4. Argumenty pro funkci FORTRESS v aplikaci Microsoft Excel

  4. Operátor vypočítává na základě zadaných dat a zobrazí výsledek na obrazovce. Pro rok 2018 je plánován zisk kolem 4564,7 tisíc rublů. Na základě výsledné tabulky můžeme graf vykreslit pomocí nástrojů mapování popsaných výše.

    5. Výsledek funkce PREDICT v aplikaci Microsoft Excel

  5. Pokud změníte rok v buňce, která byla použita k zadání argumentu, výsledek se změní odpovídajícím způsobem a graf se automaticky aktualizuje. Například, podle prognóz v roce 2019, výše zisku bude 4637,8 tisíc rublů.

Změna argumentu funkce PRESET v aplikaci Microsoft Excel

Nezapomeňte však, že stejně jako při sestavování trendové linie by časový interval do prognózovaného období neměl překročit 30% celého období, za které byla databáze načtena.

Lekce: Extrapolace v aplikaci Excel

Metoda 3: Operátory TRENDY

Pro predikci můžete použít jednu další funkci - TRENDS . Patří sem také do kategorie statistických operátorů. Jeho syntaxe se v mnoha směrech podobá syntaxi nástroje PRESCASE a vypadá takto:

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

Jak je vidět, argumenty "Známé hodnoty y" a "Známé hodnoty x" plně odpovídají podobným prvkům operátoru PRESCASE a argument "Nové hodnoty x" odpovídá argumentu "X" předchozího nástroje. Navíc má TRENDS další argument "Constant" , ale není povinný a používá se pouze v případě, že existují konstantní faktory.

Tento operátor je nejúčinněji využíván v přítomnosti lineární závislosti funkce.

Podívejme se, jak tento nástroj bude pracovat všichni se stejným souborem dat. Za účelem porovnání získaných výsledků určíme prognostický bod v roce 2019.

  1. Určujeme buňku pro výstup výsledku a běžným způsobem spustíme Průvodce funkcemi . V kategorii "Statistické" najdeme a vybíráme název "TRENDY" . Klikněte na tlačítko "OK" .

    2. Přechod na argumenty funkce TREND v aplikaci Microsoft Excel

  2. Otevře argument argumentu operátora TENDENCY. V poli "Známé hodnoty y" zadáváme souřadnice sloupce "Podnikový zisk " způsobem popsaným výše. V poli "Známé hodnoty x" zadejte adresu sloupce "Rok" . V poli "Nové hodnoty x" zadejte odkaz na buňku, kde se nachází číslo roku, ke kterému chcete zadat prognózu. V našem případě je to rok 2019. Políčko "Konstantní" je prázdné. Klepněte na tlačítko "OK" .

    3. Argumenty pro funkci TREND v aplikaci Microsoft Excel

  3. Operátor zpracovává data a zobrazí výsledek na obrazovce. Jak je vidět, součet prognózovaného zisku za rok 2019, vypočítaný metodou lineární závislosti, způsobí stejně jako v předchozím způsobu výpočtu 4637,8 tisíc rublů.

Výsledek funkce TREND v aplikaci Microsoft Excel

Metoda 4: Operátor RŮST

Další funkcí, pomocí kterých můžete provádět prognózy v aplikaci Excel, je obsluha GROWTH. Odkazuje také na statistickou skupinu nástrojů, ale na rozdíl od předchozích metod se metoda neuplatňuje metodou lineární závislosti, ale exponenciální metodou. Syntaxe tohoto nástroje vypadá takto:

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

Jak můžete vidět, argumenty této funkce přesně zopakují argumenty operátora TENDENCY , proto o nich nebudeme diskutovat podruhé, ale my se okamžitě obrátíme k použití tohoto nástroje v praxi.

  1. Vyberte výstupní buňku a zavolejte Průvodce funkcemi obvyklým způsobem. V seznamu statistických operátorů vyhledejte položku "GROWTH" , vyberte ji a klikněte na tlačítko "OK" .

    2. Přepínání na argumenty funkce ROST v aplikaci Microsoft Excel

  2. Zobrazí se okno s argumenty výše uvedené funkce. Do polí tohoto okna zadáváme data stejným způsobem, jakým jsme je zadali do okna argumentů operátora TENDENCY. Po zadání informací klikněte na tlačítko "OK" .

    3. Argumenty pro funkci ROST v aplikaci Microsoft Excel

  3. Výsledek zpracování dat se zobrazuje na monitoru v buňce, kterou jste zadali dříve. Jak vidíte, tentokrát je výsledkem 4682,1 tisíc rublů. Rozdíly mezi výsledky zpracování dat provozovatelem TENDENCY jsou nevýznamné, ale jsou k dispozici. To je způsobeno tím, že tyto nástroje používají různé metody výpočtu: metoda lineární závislosti a metoda exponenciální závislosti.

Výsledek funkce GROWTH v aplikaci Microsoft Excel

Metoda 5: Operátor LINEST

Operátor LINEST používá metodu lineární aproximace. Nemělo by být zaměňováno s metodou lineární závislosti používané nástrojem TRENDS . Jeho syntax je následující:

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

Poslední dva argumenty jsou volitelné. S prvními dvěma jsme obeznámeni s předchozími metodami. Ale pravděpodobně jste si všimli, že v této funkci neexistuje žádný argument, který by ukazoval na nové hodnoty. Faktem je, že tento nástroj určuje pouze změnu výše výnosu za jednotku období, což je v našem případě jeden rok, ale součet se má vypočítat odděleně, přičemž k poslední skutečné hodnotě zisku se vypočítá výsledek výpočtu operátoru LINEST vynásobený počtem let.

  1. Vybíráme buňku, ve které bude výpočet proveden, a spustíme Průvodce funkcí. Vyberte název "LINEST" v kategorii "Statistické" a klikněte na tlačítko "OK" .

    2. Přejděte na argumenty funkce LINEST v aplikaci Microsoft Excel

  2. V poli "Známé hodnoty y" v okně otevřených argumentů zadejte souřadnice sloupce "Podnikový zisk". V poli "Známé hodnoty x" zadáváme adresu sloupce "Rok" . Zbývající pole jsou prázdná. Poté klikněte na tlačítko "OK" .

    3. Argumenty pro funkci LINEST v aplikaci Microsoft Excel

  3. Program vypočítá a zobrazí hodnotu lineárního trendu ve vybrané buňce.

    4. Výsledek funkce LINEST v aplikaci Microsoft Excel

  4. Nyní musíme zjistit hodnotu předpokládaného zisku za rok 2019. Značku "=" nastavte na libovolnou prázdnou buňku na listu. Klikneme na buňku, která obsahuje skutečný objem zisku za studovaný rok (2016). Vložte značku "+" . Dále klikněte na buňku, která obsahuje dříve vypočítaný lineární trend. Vložili jsme znak "*" . Protože mezi posledním rokem období studia (2016) a rokem, pro který má být prognóza provedena (2019), je období tři roky, pak v buňce nastavíme číslo "3" . Pro výpočet klikněte na tlačítko Enter .

Konečný výpočet funkce LINEST v aplikaci Microsoft Excel

Jak můžeme vidět, předpokládaný zisk vypočtený metodou lineární aproximace bude v roce 2019 4614,9 tisíc rublů.

Metoda 6: Operátor LGRFPRIBL

Poslední nástroj, který budeme zvažovat, bude LGRF . Tento operátor provádí výpočty na základě metody exponenciální aproximace. Jeho syntaxe má následující strukturu:

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

Jak vidíte, všechny argumenty zcela opakují odpovídající prvky předchozí funkce. Algoritmus výpočtu prognózy se mírně změní. Funkce vypočítá exponenciální trend, který ukáže, kolikrát se změní výše výnosu za jedno období, tedy za jeden rok. Budeme muset najít rozdíl v zisku mezi posledním a prvním plánovaným obdobím, vynásobením počtem plánovaných období (3) a přidáním k výsledku součet posledního skutečného období.

  1. V seznamu operátorů Průvodců funkcí vyberte název "LGRFI" . Klikněte na tlačítko "OK" .

    2. Přechod na argumenty funkce LGRFIBRIBL v aplikaci Microsoft Excel

  2. Otevře se okno s argumenty. V něm zadáváme data přesně tak, jak jsme udělali s funkcí LINEST . Klepněte na tlačítko "OK" .

    3. Argumenty funkce LGRFDRIBL v aplikaci Microsoft Excel

  3. Výsledek exponenciálního trendu je vypočítán a vyveden na určenou buňku.

    4. Výsledek funkce LGRF v aplikaci Microsoft Excel

  4. Vložte "=" do prázdné buňky. Otevřete závorky a vyberte buňku, která obsahuje hodnotu výnosu za poslední skutečné období. Vložte značku "*" a vyberte buňku s exponenciálním trendem. Vložíme znaménko mínus a znovu klikneme na prvek, ve kterém se nachází výnos tržní za poslední období. Zavřete držák a pohněte znaky "* 3 +" bez uvozovek. Opět klikněte na stejnou buňku, která byla vybrána naposledy. Pro provedení výpočtu stiskněte tlačítko Enter .

Konečný výpočet funkce LGRFPRIBL v aplikaci Microsoft Excel

Předpokládaná výše zisku v roce 2019, která byla vypočtena metodou exponenciálního přiblížení, činí 4 639,2 tisíc rublů, což se opět výrazně neliší od výsledků získaných při výpočtu podle předchozích metod.

Lekce: Další statistické funkce v aplikaci Excel

Zjistili jsme, jakým způsobem je možné provádět prognózy v programu Excel. Graficky to lze provést pomocí trendové linie a analytické - pomocí několika vestavěných statistických funkcí. V důsledku zpracování stejných dat těmito operátory může dojít k různým výsledkům. Ale to není překvapující, protože všichni používají různé metody výpočtu. Pokud jsou fluktuace malé, pak všechny tyto možnosti, použitelné v konkrétním případě, lze považovat za poměrně spolehlivé.