Budování trendu v excelu. Jak vykreslit trendovou čáru v MS Excel

Trend je vzorec, který popisuje vzestup nebo pokles ukazatele v průběhu času. Pokud na grafu zobrazujete jakoukoli dynamickou řadu (statistická data, což je seznam pevných hodnot proměnného ukazatele v čase), často je zvýrazněn určitý úhel - křivka jde buď postupně nahoru nebo dolů, v takových případech je zvykem říkat, že řada dynamiky má tendenci (ke vzestupu, resp. poklesu).

Trend jako modelka

Pokud sestavíme model, který tento jev popisuje, získáme celkem jednoduchý a velmi pohodlný nástroj pro prognózování, který nevyžaduje žádné složité výpočty ani časové náklady na kontrolu významnosti či přiměřenosti ovlivňujících faktorů.

Jaký je tedy trend jako modelka? Jedná se o soubor vypočtených koeficientů rovnice, které vyjadřují regresní závislost ukazatele (Y) na změně času (t). To znamená, že se jedná o přesně stejnou regresi jako ty, které jsme zvažovali dříve, pouze ukazatel času je zde ovlivňujícím faktorem.

Důležité!

Ve výpočtech t obvykle neznamená rok, měsíc nebo týden, ale sériové číslo období ve studované statistické populaci - dynamická řada. Pokud je například časová řada studována několik let a data byla zaznamenávána měsíčně, pak je použití přestavitelného číslování měsíců od 1 do 12 a znovu od začátku zásadně špatné. Nesprávné také není, pokud studium řady začíná např. od března, jako hodnotu t použijte 3 (třetí měsíc v roce), pokud se jedná o první hodnotu ve studované populaci, pak by její pořadové číslo mělo být 1.

Model lineárního trendu

Jako každá jiná regrese může být trend buď lineární (stupeň ovlivňujícího faktoru t je 1) nebo nelineární (stupeň je větší nebo menší než jedna). Protože lineární regrese je nejjednodušší, i když ne vždy nejpřesnější, budeme tento typ trendu zvažovat podrobněji.

Obecný pohled na rovnici lineárního trendu:

Y(t) = a 0 + a 1 *t + Ɛ

Kde a 0 je nulový regresní koeficient, tedy jaké bude Y, pokud je ovlivňující faktor nulový, a 1 je regresní koeficient, který vyjadřuje míru závislosti studovaného ukazatele Y na ovlivňujícím faktoru t, Ɛ je a náhodná složka nebo standardní chyba je ve skutečnosti rozdíl mezi skutečnými hodnotami Y a vypočtenými. t je jediný ovlivňující faktor – čas.

Čím výraznější bude trend růstu ukazatele nebo jeho pokles, tím větší bude koeficient a 1. V souladu s tím se předpokládá, že konstanta a 0 spolu s náhodnou složkou Ɛ odrážejí kromě času i zbytek regresních vlivů, tedy všechny ostatní možné ovlivňující faktory.

Můžete vypočítat koeficienty modelu standardní metoda nejmenších čtverců (LSM). Se všemi těmito výpočty Microsoft Excel vyrovnává se s třeskem sám o sobě, navíc k získání lineárního trendového modelu nebo hotové prognózy existuje až pět způsobů, které budeme samostatně analyzovat níže.

Grafický způsob, jak získat lineární trend

V tomto a ve všech dalších příkladech budeme používat stejnou časovou řadu - úroveň HDP, která se počítá a zaznamenává ročně, v našem případě bude studie probíhat za období 2004 až 2012.

K výchozím údajům přidáme ještě jeden sloupec, který nazveme t a čísly označíme vzestupně pořadová čísla všech zaznamenaných hodnot HDP za zadané období 2004 až 2012. – 9 let resp 9 období.

Excel přidá prázdné pole - označení pro budoucí graf, vyberte tento graf a aktivujte záložku, která se objeví na liště nabídek - Konstruktér, hledá tlačítko Vyberte data, v okně, které se otevře, stiskněte tlačítko Přidat. Vyskakovací okno vás vyzve k výběru dat pro vykreslení grafu. Jako hodnota pole Název řádku vyberte buňku obsahující text, který nejvíce odpovídá názvu grafu. V terénu X hodnot uveďte interval buněk sloupce t - ovlivňující faktor. V terénu hodnoty Y uveďte interval buněk ve sloupci se známými hodnotami HDP (Y) - sledovaný ukazatel.

Po vyplnění zadaných polí stiskněte několikrát tlačítko OK a získáte hotový graf dynamiky. Nyní klikněte pravým tlačítkem na samotný řádek grafu a vyberte položku z kontextové nabídky, která se zobrazí. Přidat trendovou linii

Otevře se okno pro nastavení parametrů pro konstrukci trendové čáry, kde mezi typy modelů vybíráme Lineární, zaškrtněte před položky P vykreslit rovnici do diagramu A Umístěte na diagram hodnotu spolehlivosti aproximace R2, to bude stačit k zobrazení již vytvořené trendové čáry na grafu a také matematické verze zobrazení modelu ve formě hotové rovnice a indikátoru kvality modelu R2. Pokud máte zájem o zobrazení prognózy na grafu, abyste mohli vizuálně posoudit mezeru mezi studovaným indikátorem, uveďte v poli Předpověď pro počet období zájmu.

Ve skutečnosti je to vše, co se týká této metody, samozřejmě můžete dodat, že zobrazená lineární trendová rovnice je samotný model, který lze použít jako vzorec pro získání vypočítaných hodnot pro model a podle toho, přesné hodnoty předpovědi (předpověď zobrazená na grafu lze odhadnout pouze přibližně), což jsme provedli v příkladu připojeném k článku.

Vytvoření lineárního trendu pomocí vzorce LINREGRESE

Podstatou této metody je nalezení lineárních trendových koeficientů pomocí funkce LINEST, poté dosazením těchto ovlivňujících koeficientů do rovnice získáme prediktivní model.

Musíme vybrat dvě sousední buňky (na snímku obrazovky jsou to buňky A38 a B38), poté v řádku vzorců nahoře (zvýrazněno červeně na snímku obrazovky výše) zavoláme funkci napsáním „= LINREGRESE (“, po kterém Excel zobrazí rady, co je pro tyto funkce vyžadováno, konkrétně:

1. vyberte rozsah se známými hodnotami popsaného ukazatele Y (v našem případě HDP, na snímku obrazovky je rozsah zvýrazněn modře) a vložte středník
2. označte rozsah ovlivňujících faktorů X (v našem případě je to indikátor t, pořadové číslo období, na snímku zvýrazněné zeleně) a vložte středník
3. dalším požadovaným parametrem funkce je určit, zda je nutné konstantu vypočítat, protože zpočátku uvažujeme model s konstantou (koeficient 0 ), pak dáme buď "TRUE" nebo "1" a středník
4. pak je potřeba specifikovat, zda je vyžadován výpočet parametrů statistiky (pokud bychom uvažovali o této možnosti, museli bychom zpočátku zvolit rozsah „pod vzorcem“ o pár řádků níže). Uveďte nutnost výpočtu statistických parametrů, jmenovitě standardní chyba pro koeficienty, koeficient determinismu, standardní chyba pro Y, Fisherovo kritérium, stupně volnosti atd., dává to smysl pouze tehdy, když rozumíte, co znamenají, v tomto případě uvedeme buď „PRAVDA“ nebo „1“. V případě zjednodušeného modelování, které se snažíme naučit, v této fázi psaní vzorce dáme "FALSE" nebo "0" a přidáme uzavírací závorku ")" za
5. pro "oživení" vzorce, tedy aby fungoval po předepsání všech potřebných parametrů, nestačí stisknout tlačítko Enter, je potřeba držet postupně tři klávesy: Ctrl, Shift, Enter

Jak můžete vidět na obrázku výše, buňky, které jsme vybrali pro vzorec, byly vyplněny vypočítanými hodnotami regresních koeficientů pro lineární trend v buňce B38 koeficient je nalezen 0 a v cele A38- koeficient závislosti na parametru t (nebo X ), tzn 1 . Dosaďte získané hodnoty do rovnice lineární funkce a dostaneme hotový model v matematickém vyjádření - y = 169572,2+138454,3*t

Chcete-li získat vypočítané hodnoty Y podle modelu a podle toho, abyste získali předpověď, stačí nahradit vzorec v buňce Excel a místo t zadejte odkaz na buňku s požadovaným číslem období (viz buňka na snímku obrazovky D25).

Pro porovnání výsledného modelu s reálnými daty můžete sestavit dva grafy, kde jako X uvedete pořadové číslo období a jako Y v jednom případě - reálný HDP, a ve druhém - vypočítaný (na snímku obrazovky, diagram napravo).

Vytváření lineárního trendu pomocí nástroje regrese v sadě analytických nástrojů

V článku je ve skutečnosti tato metoda plně popsána, jediný rozdíl je v tom, že v našich výchozích datech je pouze jeden ovlivňující faktor X (číslo období - t ).

Jak je vidět na obrázku výše, rozsah dat se známými hodnotami HDP zvýrazněno jako vstupní interval Y a odpovídající rozsah s čísly period t - jako vstupní interval X. Výsledky výpočtů analytického balíčku jsou zobrazeny na samostatný list a vypadá jako sada tabulek (viz obrázek níže), ve kterých nás zajímají buňky, které jsem namaloval žlutě a zelené barvy. Analogicky k pořadí popsanému ve výše uvedeném článku je ze získaných koeficientů sestaven model lineárního trendu y=169 572,2+138 454,3*t, na základě kterých se dělají předpovědi.

Předpovídání s lineárním trendem pomocí funkce TREND

Tato metoda se liší od předchozích v tom, že přeskakuje dříve nutné kroky výpočtu parametrů modelu a dosazení získaných koeficientů ručně jako vzorec v buňce pro získání prognózy, tato funkce pouze dává hotovou vypočítanou prediktivní hodnotu na základě známá počáteční data.

Do cílové buňky (buňka, kde chceme vidět výsledek) vložíme znaménko rovná se a zavolejte magickou funkci napsáním " TREND(“, pak musíte vybrat , to znamená poté, co vložíme středník a vybereme rozsah se známými hodnotami X, tedy s počtem period t, které odpovídají sloupci se známými hodnotami HDP, opět dáme středník a vybereme buňku s číslem období, na které prognózu děláme (v našem případě však číslo období nelze uvést odkazem do buňky, ale jednoduše číslem přímo ve vzorci), pak vložte další středník a označte SKUTEČNÝ nebo 1 , jako potvrzení pro výpočet koeficientu 0 konečně dát uzavírací závorka a stiskněte klávesu Vstupte.

Nevýhodou této metody je, že nezobrazuje ani modelovou rovnici, ani její koeficienty, a proto nelze říci, že na základě toho a takového modelu jsme dostali takovou a takovou předpověď, stejně jako neexistuje žádná odraz kvalitativních parametrů modelu. , ten samý koeficient determinace, podle kterého by bylo možné říci, zda má smysl brát přijatou předpověď v úvahu či nikoliv.

Předpovídání s lineárním trendem pomocí funkce FORECAST

Podstata této funkce je zcela identická s předchozí, rozdíl je pouze v pořadí předepisování počátečních dat ve vzorci a v tom, že neexistuje žádné nastavení přítomnosti nebo nepřítomnosti koeficientu. 0 (to znamená, že funkce znamená, že tento koeficient stejně existuje)

Jak můžete vidět z obrázku výše, do cílové buňky zapíšeme " =PŘEDPOVĚĎ(“ a poté specifikujte buňka s číslem období, u kterého je nutné vypočítat hodnotu podle lineárního trendu, tedy prognózy, pak dát středník, pak vybrat rozsah známých hodnot Y, to je sloupec se známými hodnotami HDP, pak vložte středník a vyberte rozsah se známými hodnotami X, to je s dobovými čísly t, které odpovídají sloupci se známými hodnotami HDP a nakonec nastavené uzavírací závorka a stiskněte klávesu Vstupte.

Získané výsledky, stejně jako u výše uvedené metody, jsou pouze konečným výsledkem výpočtu předpovědní hodnoty pomocí lineárního trendového modelu, nedává žádné chyby ani samotný model v matematickém vyjádření.

Shrnutí článku

Dá se říci, že každá z metod může být mezi ostatními nejpřijatelnější v závislosti na aktuálním cíli, který si stanovíme. První tři metody se významem i výsledkem vzájemně prolínají a jsou vhodné pro jakoukoliv více či méně seriózní práci, kde je nutný popis modelu a jeho kvality. Poslední dvě metody jsou zase totožné a dají vám co nejrychleji odpověď, například na otázku: „Jaká je prognóza prodejů na příští rok?“.

Trend ihned po zadání dostupných dat do pole. Chcete-li to provést, vyberte na listu s datovou tabulkou alespoň dvě buňky rozsahu, pro který bude graf sestaven, a ihned poté vložte graf. Můžete použít takové typy grafů, jako je graf, bodový, histogram, bublina, akcie. Jiné typy grafů nepodporují funkci trendování.

Z nabídky Graf vyberte Přidat trendovou linii. V okně, které se otevře, na záložce "Typ" vyberte požadovaný typ spojnice trendu, což v matematickém vyjádření znamená i způsob aproximace dat. Při použití popsané metody to budete muset udělat "od oka", protože. neprováděli jste žádné matematické výpočty pro vykreslení grafu.

Stačí se tedy zamyslet nad tím, jaký typ funkce nejlépe vyhovuje grafu dostupných dat: lineární, logaritmická, exponenciální nebo jiná. Pokud máte pochybnosti o volbě typu aproximace, můžete sestavit několik řádků a pro větší přesnost předpovědi zaškrtněte na kartě „Parametry“ ve stejném okně políčko „zadejte hodnotu spolehlivosti aproximace (R ^ 2) na diagramu“.

Porovnání hodnot R^2 pro různé linie, můžete si vybrat typ grafu, který nejpřesněji charakterizuje vaše data, a proto sestaví nejspolehlivější předpověď. Čím blíže je hodnota R^2 jedné, tím přesněji jste vybrali typ čáry. Zde na záložce "Parametry" je třeba určit období, pro které se předpověď vytváří.

Tento způsob budování trendu je velmi přibližný, proto je lepší provést alespoň to nejprimitivnější statistické zpracování dostupných dat. To vám umožní vytvořit předpověď přesněji.

Pokud předpokládáte, že jsou popsána dostupná data lineární rovnice, stačí je vybrat kurzorem a automaticky vyplnit na požadovaný počet teček nebo počet buněk. V tomto případě není potřeba zjišťovat hodnotu R^2, protože předpřipravili jste předpověď na rovnici přímky.

Pokud si myslíte, že známé hodnoty proměnné lze nejlépe popsat pomocí exponenciální rovnice, vyberte také počáteční rozsah a podržením pravého tlačítka myši automaticky vyplňte požadovaný počet buněk. S automatickým doplňováním nebudete moci kreslit jiné typy čar kromě dvou uvedených.

Proto pro co největší přesnost při sestavování prognózy budete muset použít jednu z několika statistických funkcí: FORECAST, TREND, GROWTH, LINEST nebo LGRFPRIBL. V tomto případě budete muset vypočítat hodnotu pro každé následující období prognózy ručně. Pokud potřebujete provést složitější regresní analýzu dat, budete potřebovat doplněk „Analysis Toolkit“, který není součástí standartní instalace kancelář MS.

Teoretické zázemí

V praxi se při modelování různých procesů - zejména ekonomických, fyzických, technických, sociálních - široce používá jeden nebo druhý způsob výpočtu přibližných hodnot funkcí z jejich známých hodnot v některých pevných bodech.

Problémy s aproximací funkcí tohoto druhu často vznikají:

• při konstrukci přibližných vzorců pro výpočet hodnot charakteristických veličin studovaného procesu podle tabulkových údajů získaných jako výsledek experimentu;
• v numerické integraci, derivaci, řešení diferenciální rovnice atd.;
• pokud je nutné vypočítat hodnoty funkcí v mezilehlých bodech uvažovaného intervalu;
• při určování hodnot charakteristických veličin procesu mimo uvažovaný interval, zejména při prognózování.

Tento článek pojednává o možnostech balíku MS Excel pro řešení takových problémů, dále jsou uvedeny metody a techniky pro konstrukci (vytváření) regresí pro tabulkově zadané funkce (což je základem regresní analýzy).

Existují dvě možnosti vytváření regresí v Excelu.

1. Přidání vybraných regresí (trendových linií) do grafu vytvořeného na základě datové tabulky pro studovanou charakteristiku procesu (dostupné pouze v případě, že je graf vytvořen);
2. Použití vestavěných statistik pracovníků list Excelu, umožňující získat regrese (trendové linie) přímo na základě tabulky zdrojových dat.

Přidání trendových linií do grafu

Pro tabulku dat popisující určitý proces a reprezentovanou diagramem má Excel účinný nástroj pro regresní analýzu, který vám umožňuje:

• postavit na základě metody nejmenších čtverců a přidat do diagramu pět typů regresí, které modelují zkoumaný proces s různou mírou přesnosti;
• doplňte do diagramu rovnici sestrojené regrese;
• určit míru shody vybrané regrese s údaji zobrazenými v grafu.

y = y (x)

kde x je nezávislá proměnná, která často nabývá hodnot posloupnosti přirozených čísel (1; 2; 3; ...) a vytváří např. odpočet času zkoumaného procesu (charakteristiky) .

1 . Lineární regrese je dobrá při modelování prvků, které se zvyšují nebo snižují konstantní rychlostí. Toto je nejjednodušší model studovaného procesu. Ona

y=mx+b

kde m je tečna sklonu lineární regrese k ose x; b - souřadnice průsečíku lineární regrese s osou y.

2 . Polynomiální spojnice trendu je užitečná pro popis charakteristik, které mají několik odlišných extrémů (horní a spodní). Volba stupně polynomu je určena počtem extrémů studované charakteristiky. Polynom druhého stupně tedy může dobře popsat proces, který má pouze jedno maximum nebo minimum; polynom třetího stupně - ne více než dva extrémy; polynom čtvrtého stupně - ne více než tři extrémy atd.

V tomto případě je trendová čára vytvořena v souladu s rovnicí:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

kde koeficienty c0, c1, c2,... c6 jsou konstanty, jejichž hodnoty jsou určeny při konstrukci.

3 . Logaritmická trendová čára se úspěšně používá při modelování charakteristik, jejichž hodnoty se nejprve rychle mění a poté se postupně stabilizují.

Je postaven podle rovnice:

y = c ln(x) + b

4 . Trendová čára výkonu dává dobré výsledky, pokud jsou hodnoty studované závislosti charakterizovány neustálou změnou rychlosti růstu. Příkladem takové závislosti může být graf rovnoměrně zrychleného pohybu vozu. Pokud jsou v datech nulové nebo záporné hodnoty, nemůžete použít spojnici trendu výkonu.

Je postaven podle rovnice:

y = cxb

kde koeficienty b, c jsou konstanty.

5 . Exponenciální trendová křivka by měla být použita, pokud se rychlost změny v datech neustále zvyšuje. Pro data obsahující nulové nebo záporné hodnoty není tento druh aproximace rovněž použitelný.

Je postaven podle rovnice:

y=cebx

kde koeficienty b, c jsou konstanty.

Při výběru linky Trend Excelu automaticky vypočítá hodnotu R2, která charakterizuje přesnost aproximace: čím blíže je hodnota R2 jednotce, tím spolehlivěji se trendová linie aproximuje zkoumanému procesu. V případě potřeby lze hodnotu R2 vždy zobrazit na diagramu.

Určeno podle vzorce:

Postup přidání trendové čáry do datové řady:

• aktivujte graf vytvořený na základě datové řady, tj. klikněte do oblasti grafu. V hlavním menu se objeví položka Graf;
• po kliknutí na tuto položku se na obrazovce objeví nabídka, ve které vyberte příkaz Přidat čáru trendu.

Stejné akce lze snadno implementovat, pokud najedete myší na graf odpovídající jedné z datových řad a kliknete pravým tlačítkem; v se objevil kontextová nabídka vyberte příkaz Přidat spojnici trendu. Na obrazovce se objeví dialogové okno Trendová čára s otevřenou záložkou Typ (obr. 1).

Poté potřebujete:

Na záložce Typ vyberte požadovaný typ čáry trendu (ve výchozím nastavení je vybrána Lineární). U typu Polynom zadejte v poli Stupeň stupeň vybraného polynomu.

V případě potřeby můžete přechodem na záložku Parametry (obr. 2) nastavit pro linii trendu následující parametry:

• změňte název spojnice trendu v poli Název aproximující (vyhlazené) křivky.
• nastavte počet období (dopředu nebo dozadu) pro předpověď v poli Předpověď;
• zobrazit rovnici trendové čáry v oblasti grafu, pro kterou byste měli zaškrtnout políčko zobrazit rovnici v grafu;
• zobrazte v oblasti diagramu hodnotu spolehlivosti aproximace R2, pro kterou zaškrtněte políčko vložte do diagramu hodnotu spolehlivosti aproximace (R^2);
• nastavte průsečík křivky trendu s osou Y, pro který byste měli zaškrtnout políčko Průsečík křivky s osou Y v bodě;
• klepnutím na tlačítko OK zavřete dialogové okno.

Na obrazovce se objeví dialogové okno Formát čáry trendu (obr. 3), které obsahuje tři karty: View, Type, Parameters a obsah posledních dvou se zcela shoduje s podobnými kartami dialogového okna Trendline (obr. 1-2). ). Na kartě Zobrazit můžete nastavit typ čáry, její barvu a tloušťku.

Chcete-li odstranit již vytvořenou trendovou linii, vyberte trendovou linii, kterou chcete odstranit, a stiskněte klávesu Delete.

Výhody uvažovaného nástroje regresní analýzy jsou:

• relativní snadnost vykreslení trendové čáry do grafů bez vytvoření datové tabulky;
• poměrně široký seznam typů navrhovaných trendových čar a tento seznam zahrnuje nejčastěji používané typy regrese;
• možnost predikce chování zkoumaného procesu pro libovolný (v rámci zdravého rozumu) počet kroků vpřed i vzad;
• možnost získání rovnice trendové čáry v analytické podobě;
• možnost v případě potřeby získat posouzení spolehlivosti aproximace.

Nelze přidávat spojnice trendu do datových řad na 3D, standardních, radarových, koláčových a prstencových grafech.

Použití vestavěných funkcí aplikace Excel

Excel také poskytuje nástroj pro regresní analýzu pro vykreslování trendových linií mimo oblast grafu. K tomuto účelu lze použít řadu funkcí statistického listu, ale všechny umožňují vytvářet pouze lineární nebo exponenciální regrese.

Excel má několik funkcí pro vytváření lineární regrese, zejména:

• TREND;
• LINEST;
• SLOPE a ŘEZ.

Stejně jako několik funkcí pro konstrukci exponenciální trendové linie, zejména:

• VÝŠKA;
• LGRFPpřibl.

Výhody vestavěného nástroje funkcí pro regresní analýzu jsou:

• poměrně jednoduchý proces stejného typu tvorby datových řad studované charakteristiky pro všechny vestavěné statistické funkce, které nastavují trendové linie;
• standardní technika pro konstrukci trendových čar na základě generovaných datových řad;
• schopnost předvídat chování zkoumaného procesu pro požadovaný počet kroků vpřed nebo vzad.

Je třeba poznamenat, že autoři si nekladli za cíl článku představit průběh regresní analýzy s různou mírou úplnosti. Jeho hlavním úkolem je na konkrétních příkladech ukázat schopnosti balíku Excel při řešení aproximačních úloh; ukázat jak efektivní nástroje má Excel pro vytváření regresí a prognózování; ilustrují, jak relativně snadno může takové problémy vyřešit i uživatel, který nemá hluboké znalosti regresní analýzy.

Příklady řešení konkrétních problémů

Zvažte řešení konkrétních problémů pomocí uvedených nástrojů balíku Excel.

Úkol 1

S tabulkou údajů o zisku podniku motorové dopravy za roky 1995-2002. musíte udělat následující.

1. Sestavte graf.
2. Přidejte do grafu lineární a polynomiální (kvadratické a kubické) trendové čáry.
3. Pomocí rovnic trendových linií získejte tabulková data o zisku podniku pro každou trendovou linii za období 1995-2004.
4. Vytvořte prognózu zisku podniku na roky 2003 a 2004.

Řešení problému

1. Do rozsahu buněk A4:C11 listu Excelu zadáme list uvedený na Obr. 4.
2. Po výběru rozsahu buněk B4:C11 vytvoříme graf.
3. Zkonstruovaný graf aktivujeme a výše popsanou metodou po výběru typu trendové čáry v dialogovém okně Trendová čára (viz obr. 1) do grafu střídavě přidáváme lineární, kvadratické a kubické trendové čáry. Ve stejném dialogovém okně otevřete záložku Parametry (viz obr. 2), do pole Název aproximační (vyhlazené) křivky zadejte název přidávaného trendu a do pole Prognóza vpřed pro: období nastavte hodnotu 2, protože se plánuje provést prognózu zisku na dva roky dopředu. Pro zobrazení regresní rovnice a hodnoty aproximační spolehlivosti R2 v oblasti diagramu zaškrtněte políčka Zobrazit rovnici na obrazovce a umístěte do diagramu hodnotu aproximační spolehlivosti (R^2). Pro lepší vizuální vnímání měníme typ, barvu a tloušťku vykreslovaných čar trendu, k čemuž využíváme kartu Zobrazit dialogového okna Formát čáry trendu (viz obr. 3). Výsledný graf s přidanými trendovými čarami je znázorněn na Obr. 5.
4. Získat tabulkové údaje o zisku podniku pro každou trendovou linii za období 1995-2004. Použijme rovnice trendových čar uvedených na Obr. 5. Chcete-li to provést, zadejte do buněk rozsahu D3: F3 textové informace o typu zvolené trendové linie: Lineární trend, Quad trend, Cubic trend. Dále zadejte vzorec lineární regrese do buňky D4 a pomocí značky výplně zkopírujte tento vzorec s relativními odkazy na oblast buněk D5:D13. Je třeba poznamenat, že každá buňka se vzorcem lineární regrese z oblasti buněk D4:D13 má jako argument odpovídající buňku z oblasti A4:A13. Podobně pro kvadratickou regresi se vyplní oblast buněk E4:E13 a pro kubickou regresi se vyplní oblast buněk F4:F13. Byl tedy proveden odhad zisku podniku na roky 2003 a 2004. se třemi trendy. Výsledná tabulka hodnot je na obr. 6.

Úkol 2

1. Sestavte graf.
2. Přidejte do grafu logaritmické, exponenciální a exponenciální trendové čáry.
3. Odvoďte rovnice získaných trendových čar a také hodnoty aproximační spolehlivosti R2 pro každou z nich.
4. Pomocí rovnic trendových linií získejte tabulková data o zisku podniku pro každou trendovou linii za roky 1995-2002.
5. Vytvořte prognózu zisku pro podnikání na roky 2003 a 2004 pomocí těchto trendových čar.

Řešení problému

Podle metodiky uvedené v řešení úlohy 1 získáme diagram s přidanými logaritmickými, exponenciálními a exponenciálními trendovými čarami (obr. 7). Dále pomocí získaných trendových rovnic vyplníme tabulku hodnot pro zisk podniku včetně predikovaných hodnot pro roky 2003 a 2004. (obr. 8).

Na Obr. 5 a Obr. je vidět, že model s logaritmickým trendem odpovídá nejnižší hodnotě aproximační spolehlivosti

R2 = 0,8659

Nejvyšší hodnoty R2 odpovídají modelům s polynomiálním trendem: kvadratický (R2 = 0,9263) a kubický (R2 = 0,933).

Úkol 3

S tabulkou údajů o zisku podniku motorové dopravy za roky 1995-2002, uvedené v úloze 1, musíte provést následující kroky.

1. Získejte datové řady pro lineární a exponenciální spojnice trendu pomocí funkcí TREND a GROW.
2. Pomocí funkcí TREND a GROWTH vytvořte prognózu zisku pro podnik na roky 2003 a 2004.
3. Pro počáteční data a přijaté datové řady vytvořte diagram.

Řešení problému

Využijme pracovní list úlohy 1 (viz obr. 4). Začněme funkcí TREND:

1. vyberte rozsah buněk D4:D11, který by měl být vyplněn hodnotami funkce TREND odpovídající známým údajům o zisku podniku;
2. zavolejte příkaz Funkce z nabídky Vložit. V dialogovém okně Průvodce funkcí, které se zobrazí, vyberte funkci TREND z kategorie Statistické a klepněte na tlačítko OK. Stejnou operaci lze provést stisknutím tlačítka (funkce Vložit) standardní panel nástroje.
3. V zobrazeném dialogovém okně Argumenty funkce zadejte rozsah buněk C4:C11 do pole Známé_hodnoty_y; v poli Known_values_x - rozsah buněk B4:B11;
4. chcete-li ze zadaného vzorce vytvořit maticový vzorec, použijte kombinaci kláves + + .

V důsledku toho je rozsah buněk D4:D11 vyplněn odpovídajícími hodnotami funkce TREND (obr. 9).

1. vyberte rozsah buněk D12:D13, kam budou zadány hodnoty předpovězené funkcí TREND.
2. zavolejte funkci TREND a v zobrazeném dialogovém okně Argumenty funkce zadejte do pole Known_values_y rozsah buněk C4:C11; v poli Known_values_x - rozsah buněk B4:B11; a v poli Nové_hodnoty_x - rozsah buněk B12:B13.
3. přeměňte tento vzorec na maticový vzorec pomocí kombinace klávesy Ctrl+ Shift + Enter.
4. Zadaný vzorec bude vypadat takto: =(TREND(C4:C11;B4:B11;B12:B13)) a rozsah buněk D12:D13 bude vyplněn predikovanými hodnotami funkce TREND (viz obr. 9).

Obdobně je datová řada naplněna pomocí funkce GROWTH, která se používá při analýze nelineárních závislostí a funguje úplně stejně jako její lineární protějšek TREND.

Obrázek 10 ukazuje tabulku v režimu zobrazení vzorce.

Pro počáteční data a získané datové řady je diagram znázorněný na Obr. jedenáct.

Úkol 4

S tabulkou údajů o příjmu žádostí o výkony dispečerskou službou podniku motorové dopravy za období od 1. do 11. aktuální měsíc musíte udělat následující.

1. Získejte datové řady pro lineární regresi:pomocí funkcí SLOPE a INTERCEPT; pomocí funkce LINREGRESE.
2. Získejte řadu údajů pro exponenciální regrese pomocí funkce LGRFPRIB.
3. Pomocí výše uvedených funkcí vytvořte prognózu příjmu žádostí na dispečink na období od 12. do 14. dne aktuálního měsíce.
4. Pro původní a přijatou datovou řadu vytvořte diagram.

Řešení problému

Všimněte si, že na rozdíl od funkcí TREND a GROW žádná z výše uvedených funkcí (SLOPE, INTERCEPTION, LINEST, LGRFPRIB) není regrese. Tyto funkce hrají pouze pomocnou roli, určující potřebné regresní parametry.

Pro lineární a exponenciální regrese vytvořené pomocí funkcí SLOPE, INTERCEPT, LINEST, LGRFPRIB, vzhled jejich rovnice jsou vždy známé, na rozdíl od lineárních a exponenciálních regresí odpovídajících funkcím TREND a GROWTH.

1 . Sestavme lineární regresi, která má rovnici:

y=mx+b

Za tímto účelem provádíme následující akce:

1. zadejte zdrojovou tabulku v rozsahu buněk A4:B14;
2. hodnota parametru m bude určena v buňce C19. Vyberte z kategorie Statistika funkci Sklon; zadejte rozsah buněk B4:B14 do pole známé_hodnoty_y a rozsah buněk A4:A14 do pole známé_hodnoty_x. Do buňky C19 se zadá vzorec: =SLOPE(B4:B14;A4:A14);
3. podobnou metodou se určí hodnota parametru b v buňce D19. A jeho obsah bude vypadat takto: = INTERCEPT(B4:B14;A4:A14).Hodnoty parametrů ma b, nezbytné pro konstrukci lineární regrese, budou tedy uloženy v buňkách C19, D19;
4. poté zadáme vzorec lineární regrese do buňky C4 ve tvaru: = $ C * A4 + $ D. V tomto vzorci se buňky C19 a D19 zapisují s absolutními odkazy (adresa buňky by se případným kopírováním neměla měnit). Podepsat absolutní odkaz$ lze zadat buď z klávesnice, nebo pomocí klávesy F4 po umístění kurzoru na adresu buňky. Pomocí úchytu výplně zkopírujte tento vzorec do oblasti buněk C4:C17. Dostaneme požadovanou datovou řadu (obr. 12). Vzhledem k tomu, že počet požadavků je celé číslo, měli byste na kartě Číslo v okně Formát buňky nastavit formát čísla s počtem desetinných míst na 0.

y=mx+b

Pro tohle:

1. zadejte funkci LINREGRESE jako maticový vzorec do rozsahu buněk C20:D20: =(LINEST(B4:B14;A4:A14)). V důsledku toho získáme hodnotu parametru m v buňce C20 a hodnotu parametru b v buňce D20;
2. zadejte vzorec do buňky D4: =$C*A4+$D;
3. zkopírujte tento vzorec pomocí značky výplně do oblasti buněk D4:D17 a získejte požadovanou datovou řadu.

y=bmx

Na Obr. 13 ukazuje tabulku, kde můžeme vidět funkce, které používáme s potřebnými rozsahy buněk a také vzorce.

Pro počáteční data a získané datové řady je diagram znázorněný na Obr. 14.

Tabulky a grafy se používají k analýze číselných údajů, například k vyhodnocení vztahu mezi dvěma druhy hodnot. Za tímto účelem lze do grafu nebo grafu přidat trendovou linii a její rovnici, předpovědní hodnoty vypočítané pro několik období dopředu nebo dozadu.

trendová linie je přímá nebo zakřivená čára, která aproximuje (aproximuje) původní data na základě regresní rovnice nebo klouzavého průměru. Aproximace je určena metodou nejmenších čtverců. V závislosti na povaze chování výchozích dat (pokles, nárůst atd.) se zvolí interpolační metoda, která by měla být použita pro sestavení trendu.

Existuje několik možností pro vytvoření trendové linie.

Lineární funkce: y=mx+b

kde m je tečna sklonu přímky, b je posunutí.

Přímá trendová linie ( lineární trend) nejlepší způsob vhodné pro množství měnící se konstantní rychlostí. Používá se v případech, kdy jsou datové body blízko přímky.

Logaritmická funkce: y=c*ln⁡x+b

kde c a b jsou konstanty.

Logaritmická trendová linie odpovídá řadě dat, která nejprve rychle stoupá nebo klesá a poté se postupně stabilizuje. Lze použít pro pozitivní i negativní data.

Polynomiální funkce (do 6. stupně včetně): y= b + c 1 *x + c 2 *x 2 + c 3 *x 3 + ...+ c 6* x 6

kde b, c 1 , c 2 , ... c 6 jsou konstanty.

Polynomiální spojnice trendu se používá k popisu střídavě rostoucích a klesajících dat. Stupeň polynomu se volí tak, aby byl jedna více množství extrémy (výšky a minima) křivky.

Funkce napájení: y=cxb

kde c a b jsou konstanty.

Trendová čára výkonu poskytuje dobré výsledky pro pozitivní data s konstantním zrychlením. U sérií s nulovými nebo zápornými hodnotami nelze zadanou trendovou linii nakreslit.

Exponenciální funkce: y=cebx

kde c a b jsou konstanty, e je základ přirozeného logaritmu.

Exponenciální trend se používá v případě kontinuálního nárůstu změny dat. Konstrukce zadaného trendu není možná, pokud sada hodnot členů řady obsahuje nulová nebo záporná data.

Použití lineární filtrace podle vzorce: F t = (A t +A (t-1) +⋯+A (t-n+1))/n

kde n je celkový počet členů řady, t je daný počet bodů (2 ≤ t< n).

trend od té doby lineární filtrování umožňuje vyhladit kolísání dat a jasně demonstruje povahu závislostí. Pro sestavení zadané trendové linie musí uživatel zadat číslo – parametr filtru. Pokud je číslo 2, pak je první bod trendové čáry definován jako průměr prvních dvou datových položek, druhý bod je průměr druhé a třetí datové položky a tak dále.

U některých typů grafů nelze trendovou linii v zásadě sestavit – skládané grafy, objemové grafy, okvětní grafy, kruhové grafy, plošné grafy, kruhové grafy. Pokud je to možné, lze do diagramu přidat několik řádků s různými parametry. Korespondence trendové čáry se skutečnými hodnotami datové řady je stanovena pomocí aproximačního faktoru spolehlivosti:

Trendová čára, stejně jako její parametry, jsou přidány do dat grafu pomocí následujících příkazů:

V případě potřeby lze parametry čáry změnit kliknutím na datovou řadu grafu nebo čáry trendu, okno Formát trendové čáry. Můžete přidat (nebo odebrat) regresní rovnici, aproximační faktor spolehlivosti, určit směr a předpověď změny v datové řadě a také provést opravu prvků návrhu trendové čáry. Zvýrazněnou trendovou linii lze také smazat.

Na obrázku je tabulka údajů o změnách hodnoty cenného papíru. Na základě těchto podmíněných dat byl sestaven bodový graf, byla přidána polynomická trendová čára třetího řádu (daná přerušovanou čarou) a některé další parametry. Získaná hodnota aproximačního koeficientu spolehlivosti R 2 na diagramu se blíží jednotce, což naznačuje, že vypočtená trendová čára se blíží problémovým datům. Predikovaná hodnota změny hodnoty cenného papíru směřuje k růstu.

• okvětní lístek;
• oběžník;
• povrch;
• prstencový;
• hlasitost;
• s akumulací.

Lineární aproximace

Dostáváme výsledek:

y = 4,503x + 6,1333

• 6.1333 - ofsety;
• x je číslo období.

y = 7,6403е^-0,084x

Například:

Jednou z důležitých součástí každé analýzy je určit hlavní trend událostí. S těmito informacemi můžete provést předpověď další vývoj situace. To je patrné zejména na příkladu trendové čáry na grafu. Pojďme zjistit, jak v program Microsoft Excel to umí postavit.

Trendová čára v Excelu

Aplikace Excel poskytuje možnost vykreslit trendovou linii pomocí grafu. Zároveň jsou výchozí data pro její vznik převzata z předem připravené tabulky.

Vykreslování

Abyste mohli sestavit graf, potřebujete mít hotovou tabulku, na jejímž základě se bude tvořit. Jako příklad si vezměme údaje o hodnotě dolaru v rublech za určité časové období.

1. Vytváříme tabulku, kde jeden sloupec bude obsahovat časové úseky (v našem případě data) a druhý sloupec bude obsahovat hodnotu, jejíž dynamika bude zobrazena v grafu.
2. Vyberte tuto tabulku. Přejděte na kartu "Vložit". Tam na pásu karet v bloku nástrojů "Grafy" klikněte na tlačítko "Graf". Z zobrazeného seznamu vyberte úplně první možnost.
3. Poté se bude sestavovat harmonogram, ale musí být ještě dopracován. Vytvoření názvu grafu. Chcete-li to provést, klikněte na něj. Ve skupině karet „Práce s grafy“, která se zobrazí, přejděte na kartu „Rozvržení“. V něm klikněte na tlačítko „Název grafu“. V seznamu, který se otevře, vyberte položku „Nad grafem“.
4. Do pole, které se zobrazí nad grafem, zadejte název, který považujeme za vhodný.
5. Pak podepíšeme osy. Na stejné kartě "Rozvržení" klikněte na tlačítko na pásu karet "Názvy os". Postupně procházíme položkami „Název hlavní vodorovné osy“ a „Název pod osou“.
6. Do zobrazeného pole zadejte název vodorovné osy podle kontextu dat na ní umístěných.
7. Abychom přiřadili název svislé ose, používáme také kartu "Rozvržení". Klikněte na tlačítko "Názvy os". Postupně procházejte položky vyskakovacího menu "Název hlavní vertikální osy" a "Otočený nadpis". Právě tento typ umístění názvu osy bude pro náš typ grafů nejvhodnější.
8. Do zobrazeného pole pro název svislé osy zadejte požadovaný název.

Lekce: Jak vytvořit graf v Excelu

Vytvoření trendové linie

Nyní musíte přímo přidat trendovou linii.

1. V záložce "Rozvržení" klikněte na tlačítko "Trendová čára", které se nachází v bloku nástrojů "Analýza". Ze seznamu, který se otevře, vyberte položku "Exponenciální aproximace" nebo "Lineární aproximace".
2. Poté se do grafu přidá trendová čára. Ve výchozím nastavení je černá.

Nastavení trendové linie

Je zde možnost další nastavení linky.

1. Postupně přejděte na kartu „Rozvržení“ v položkách nabídky „Analýza“, „Trendová linie“ a „ Extra možnosti trendové linie...
2. Otevře se okno parametrů a lze provést různá nastavení. Můžete například změnit typ vyhlazení a přizpůsobení výběrem jedné ze šesti možností:
   • polynom;
   • Lineární;
   • Napájení;
   • logaritmický;
   • exponenciální;
   • Lineární filtrování.

   Aby bylo možné určit spolehlivost našeho modelu, zaškrtněte políčko vedle položky „Umístit hodnotu aproximační spolehlivosti do diagramu“. Chcete-li zobrazit výsledek, klikněte na tlačítko "Zavřít".

   Pokud je tento indikátor roven 1, pak je model maximálně spolehlivý. Čím dále je úroveň od jedničky, tím méně důvěry.

Pokud nejste spokojeni s úrovní spolehlivosti, můžete se vrátit k parametrům a změnit typ vyhlazování a aproximace. Poté koeficient vygenerujte znovu.

Prognózování

Hlavním úkolem trendové čáry je schopnost na ní předpovídat další vývoj.

1. Vraťme se k nastavení. V bloku nastavení "Prognóza" v odpovídajících polích určete, o kolik období dopředu nebo dozadu potřebujete, abyste mohli pokračovat v trendové linii pro prognózu. Klikněte na tlačítko "Zavřít".
2. Vraťme se k grafu. Ukazuje, že čára je protáhlá. Nyní jej lze použít k určení, který přibližný ukazatel je predikován pro určité datum při zachování současného trendu.

Jak vidíte, v Excelu není těžké nakreslit trendovou čáru. Program poskytuje nástroje, aby mohl být nakonfigurován tak, aby zobrazoval indikátory co nejpřesněji. Na základě grafu můžete vytvořit předpověď pro konkrétní časové období.

Jsme rádi, že jsme vám mohli pomoci problém vyřešit.

Zeptejte se v komentářích a podrobně popište podstatu problému. Naši odborníci se pokusí odpovědět co nejrychleji.

Pomohl vám tento článek?

Proč jsou grafy potřeba? Aby to bylo "krásné"? Vůbec ne – hlavním úkolem diagramu je umožnit prezentovat nejasná čísla v grafické podobě, která je snadno pochopitelná. Takže na první pohled byl stav věcí jasný a nebylo třeba ztrácet čas studiem suchých statistik.

Další obrovskou výhodou grafů je, že výrazně usnadňují ukazování trendů, tedy tvorbu prognózy do budoucna. Pokud to jde celý rok do kopce, není důvod si myslet, že v příštím čtvrtletí se situace náhle změní.

Jak nás tabulky a grafy klamou

Nicméně grafy (zejména pokud jde o vizuální reprezentaci velkého množství dat), i když jsou pro vnímání mimořádně vhodné, nejsou zdaleka vždy zřejmé.

Dovolte mi ilustrovat svůj názor na jednoduchém příkladu:

Graf založený na tabulce v MS Excel

Tato tabulka ukazuje průměrný počet návštěvníků daného webu za den po měsíci a také počet zobrazení stránky na návštěvníka. Je logické, že vždy by mělo být více zobrazení stránek než návštěvníků, protože jeden uživatel může zobrazit několik stránek najednou.

Neméně logický je fakt, že více stránek návštěvník prohlíží, tím je web lepší – upoutá pozornost uživatele a nutí ho číst hlouběji.

Co vidí vlastník webu z našeho diagramu? Že mu to jde dobře! Během letních měsíců docházelo k sezónnímu poklesu zájmu, ale na podzim se čísla vrátila a dokonce převýšila ty jarní. Závěry? Pokračujeme ve stejném duchu a brzy dosáhneme úspěchu!

Je schéma jasné? Docela. Ale je to zřejmé? Pojďme na to přijít.

Pochopení trendů v MS Excel

Je velkou chybou ze strany vlastníka webu brát diagram takový, jaký je. Ano, pouhým okem vidíte, že modré a oranžové pruhy „podzimu“ narostly oproti „jaru“ a ještě více „létu“. Důležitá jsou však nejen čísla a velikost sloupců, ale také vztah mezi nimi. To znamená, že v ideálním případě by s celkovým růstem měly „oranžové“ sloupce zobrazení mnohem silněji než ty „modré“, což by znamenalo, že web nejen přiláká více čtenářů, ale také se zvětší a bude zajímavější.

Co vidíme na grafu? Oranžové sloupce „podzimu“ nejsou více než „jaro“ nebo dokonce méně. To nesvědčí o úspěchu, spíše naopak – návštěvníci přijdou, ale v průměru méně čtou a nezdržují se na stránkách!

Je čas bít na poplach a ... seznámit se s takovou věcí, jako je trendová linie.

Proč potřebujete trendovou linii

Trendová čára „jednoduchým způsobem“ je souvislá čára sestavená na základě zprůměrovaných hodnot založených na speciálních algoritmech, ze kterých je náš graf postaven. Jinými slovy, pokud naše data „přeskočí“ přes tři reportovací body z „-5“ na „0“ a poté na „+5“, nakonec dostaneme téměř rovnou čáru: „plusy“ situace samozřejmě vyvažují „minusy“.

Na základě směru trendové linie je mnohem lépe vidět reálná situace případy a vidět ty stejné trendy, a proto - dělat předpovědi do budoucna. Teď se pusťte do práce!

Jak vykreslit trendovou čáru v MS Excel

Přidání do diagramu v MS excelová řada trend

Klikněte pravým tlačítkem myši na jeden z "modrých" sloupců a vyberte položku v kontextové nabídce "Přidat trendovou linii".

Nyní se zobrazí list s grafem tečkovaná čára trend. Jak vidíte, neshoduje se 100% s hodnotami diagramu - postavený na vážených průměrech, pouze obecně opakuje svůj směr. To nám však nebrání v neustálém nárůstu návštěvnosti webu – ani „letní“ čerpání neovlivňuje celkový výsledek.

Trendová čára pro sloupec Návštěvníci

Nyní zopakujme stejný trik s „oranžovými“ pruhy a postavme druhou trendovou linii. Jak jsem již řekl: situace zde není tak dobrá. Trend jasně ukazuje, že za počítané období se počet zhlédnutí nejen nezvyšoval, ale dokonce začal – pomalu, ale vytrvale – klesat.

Další trendová linie umožňuje objasnit situaci

Mentálně pokračujeme v trendové linii pro nadcházející měsíce a dojdeme k neuspokojivému závěru – počet zainteresovaných návštěvníků bude nadále klesat. Protože se zde uživatelé nezdržují, pokles zájmu o stránky v blízké budoucnosti nevyhnutelně způsobí pokles návštěvnosti.

Vlastník projektu si proto musí naléhavě vzpomenout, co udělal letos v létě („na jaře“, vše bylo zcela normální, soudě podle harmonogramu), a urychleně přijmout opatření k nápravě situace.

Chcete-li předpovědět událost na základě již dostupných dat, pokud není čas, můžete použít trendovou linii. S ním můžete vizuálně pochopit, jakou dynamiku mají data, ze kterých je graf sestaven. V softwarovém balíku Microsoftu je úžasná funkce Excelu, která vám pomůže vytvořit poměrně přesnou předpověď pomocí tohoto nástroje – trendová čára v Excelu. Vytvoření tohoto analytického nástroje je velmi jednoduché, níže Detailní popis proces a typy trendových čar.

Trendová čára v Excelu. Stavební proces

Trendová čára je jedním z hlavních nástrojů analýzy dat.

K vytvoření trendové linie je nutné dokončit tři fáze, a to:
1. Vytvořte tabulku;
2. Sestavte diagram;
3. Vyberte typ spojnice trendu.

Po shromáždění všech nezbytné informace, můžete přejít přímo k realizaci kroků na cestě ke konečnému výsledku.

Prvním krokem je vytvoření tabulky s původními daty. Dále vyberte požadovaný rozsah a na kartě "Vložit" vyberte funkci "Graf". Po výstavbě lze aplikovat konečný výsledek Další funkce, ve formě hlaviček, stejně jako podpisů. Chcete-li to provést, stačí kliknout levým tlačítkem myši na graf, vybrat kartu s názvem "Konstruktor" a vybrat "Rozvržení". Dalším krokem je jednoduše zadat název.

Dalším krokem je vytvoření samotné trendové linie. Chcete-li to provést, musíte znovu vybrat graf a vybrat kartu "Rozvržení" na pásu úloh. Dále v této nabídce musíte kliknout na tlačítko "Trend Line" a vybrat "lineární aproximace" nebo "exponenciální aproximace".

Různé variace ltrendové linie

V závislosti na vlastnostech dat zadaných uživatelem se vyplatí vybrat jednu z uvedených možností, níže je popis typů trendových čar
Exponenciální aproximace. Pokud se rychlost změny vstupních dat zvyšuje a nepřetržitě, pak je daný řádek bude nejvíce užitečné. Pokud však data zadaná do tabulky obsahují nulové nebo záporné charakteristiky, je tento typ nepřijatelný.

Lineární aproximace. Tato čára je přirozeně přímá a obvykle se používá v elementárních případech, kdy se funkce zvyšuje nebo snižuje v přibližné stálosti.

Logaritmická aproximace. Pokud hodnota nejprve správně a rychle roste, nebo naopak klesá, ale poté se po hodnotě stabilizuje, pak se tato trendová linie bude hodit.

Polynomiální aproximace. Variabilní nárůst a pokles jsou vlastnosti, které jsou charakteristické pro tuto řadu. Navíc stupeň samotných polynomů (polynomů) je určen počtem maxim a minima.

Přibližný výkon. Charakterizuje monotónní nárůst a pokles hodnoty, ale jeho aplikace je nemožná, pokud mají data záporné a nulové hodnoty.

klouzavý průměr. Používá se k vizuálnímu znázornění přímé závislosti jednoho na druhém vyhlazením všech bodů kmitání. Toho je dosaženo zvýrazněním průměrné hodnoty mezi dvěma sousedními body. Graf je tedy zprůměrován a počet bodů je snížen na hodnotu, která byla zvolena uživatelem v nabídce "Body".

Jak se to používá? K predikci ekonomických opcí se používá polynomická čára, jejíž stupeň polynomu je určen na základě několika principů: maximalizace koeficientu determinace a také ekonomická dynamika ukazatele v období, pro které je vyžadována předpověď.

Po všech fázích tvorby a po pochopení funkcí můžete vytvořit celou primární trendovou linii, která jen vzdáleně odpovídá skutečným prognózám. Po nastavení parametrů se už ale můžeme bavit o reálnějším obrazu předpovědi.

Trendová čára v Excelu. Nastavení parametrůve funkční řadě

Kliknutím na tlačítko "Trend Line" vyberte požadovanou nabídku s názvem "Advanced Options". V okně, které se objeví, klikněte na "Formát trendové čáry" a poté zaškrtněte před hodnotu "uveďte do grafu hodnotu spolehlivosti přiblížení R^ 2". Poté nabídku zavřete kliknutím na příslušné tlačítko. Na samotném diagramu se objeví koeficient R^2= 0,6442.

Poté zavedené změny rušíme. Po výběru grafu a kliknutí na záložku "Rozvržení" klikněte na "Trendová čára" a klikněte na "Žádné". Dále přejděte na funkci "Formát čáry trendu", klikněte na čáru polynomu a pokuste se dosáhnout hodnoty R^ 2 = 0,8321, přičemž změňte stupeň.

K prohlížení vzorců nebo vytváření jiných, odlišných od standardních variací předpovědí, stačí nebát se experimentovat s hodnotami a zejména s polynomy. Tedy pomocí pouze jednoho program Excel, můžete na základě vstupních dat vytvořit poměrně přesnou předpověď.

(Návštíveno 10 510 krát, z toho 27 návštěv dnes)

Trendová čára v Excelu na různých grafech

Trendová čára se používá k vizuální ilustraci cenových trendů. Prvek technické analýzy je geometrický obraz průměrné hodnoty analyzovaného ukazatele.

Podívejme se, jak přidat spojnici trendu do grafu v aplikaci Excel.

Přidání spojnice trendu do grafu

Vezměme si například průměrné ceny ropy od roku 2000 z otevřených zdrojů. Data pro analýzu zapíšeme do tabulky:

1. Sestavme graf na základě tabulky. Vyberte rozsah - přejděte na kartu "Vložit". Z navržených typů grafů vybereme jednoduchý graf. Horizontální - rok, vertikální - cena.
2. Klikněte pravým tlačítkem myši na samotný graf. Klikněte na "Přidat trendovou linii".
3. Otevře se okno pro konfiguraci parametrů linky. Zvolme lineární typ a umístíme do grafu hodnotu aproximační spolehlivosti.
4. Na grafu se objeví šikmá čára.

Trendová čára v Excelu je graf aproximační funkce. Proč je to potřeba - dělat předpovědi na základě statistických údajů. K tomu je nutné prodloužit čáru a určit její hodnoty.

Pokud R2 = 1, pak je chyba aproximace nulová. V našem příkladu poskytla volba lineární aproximace nízkou spolehlivost a špatný výsledek. Předpověď bude nepřesná.

Pozornost!!! Trendovou linii nelze přidat následující typy grafy a tabulky:

• okvětní lístek;
• oběžník;
• povrch;
• prstencový;
• hlasitost;
• s akumulací.

Rovnice trendové čáry v Excelu

Ve výše uvedeném příkladu byla lineární aproximace zvolena pouze pro ilustraci algoritmu. Jak ukazuje hodnota spolehlivosti, volba nebyla zcela úspěšná.

Měli byste zvolit typ zobrazení, který nejpřesněji znázorňuje trend v uživatelském vstupu. Pojďme se podívat na možnosti.

Lineární aproximace

Jeho geometrickým zobrazením je přímka. Proto se k znázornění indikátoru, který se zvyšuje nebo snižuje konstantní rychlostí, používá lineární aproximace.

Zvažte podmíněný počet smluv uzavřených manažerem na 10 měsíců:

Na základě údajů v excelovská tabulka pojďme vytvořit bodový graf (pomůže ilustrovat lineární typ):

Vyberte graf - "přidat trendovou linii". V parametrech vyberte lineární typ. Přidáme hodnotu aproximační spolehlivosti a rovnici spojnice trendu v Excelu (stačí zaškrtnout políčka ve spodní části okna "Parametry").

Dostáváme výsledek:

Poznámka! U lineárního typu aproximace jsou datové body umístěny co nejblíže přímce. Tento pohled používá následující rovnici:

y = 4,503x + 6,1333

• kde 4,503 je ukazatel sklonu;
• 6.1333 - ofsety;
• y je posloupnost hodnot,
• x je číslo období.

Přímá čára na grafu ukazuje neustálý nárůst kvality práce manažera. Hodnota aproximační spolehlivosti je 0,9929, což ukazuje na dobrou shodu mezi vypočtenou přímkou ​​a původními daty. Předpovědi musí být přesné.

Chcete-li předpovědět počet uzavřených smluv například v 11. období, musíte do rovnice dosadit místo x číslo 11. V průběhu výpočtů se dozvídáme, že v 11. období tento manažer uzavře 55-56 smluv.

Exponenciální trendová linie

Tento typ bude užitečný, pokud se vstupní hodnoty mění neustále rostoucí rychlostí. Exponenciální aproximace se nepoužívá v přítomnosti nulových nebo záporných charakteristik.

Vytvořme exponenciální trendovou linii v Excelu. Vezměme si například podmíněné hodnoty užitečné dodávky elektřiny v oblasti X:

Vytváříme graf. Přidejte exponenciální čáru.

Rovnice má následující tvar:

y = 7,6403е^-0,084x

• kde 7,6403 a -0,084 jsou konstanty;
• e je základ přirozeného logaritmu.

Aproximační index spolehlivosti byl 0,938 - křivka odpovídá údajům, chyba je minimální, předpovědi budou přesné.

Log Trendline v Excelu

Používá se pro následující změny ukazatele: nejprve rychlý nárůst nebo pokles, poté relativní stabilita. Optimalizovaná křivka se tomuto „chování“ veličiny dobře přizpůsobuje. Logaritmický trend je vhodný pro predikci prodeje nového produktu, který se právě uvádí na trh.

Na počáteční fázeÚkolem výrobce je zvýšit zákaznickou základnu. Když má produkt svého kupce, musí si ho ponechat, podávat.

Vytvořme graf a přidejte logaritmickou trendovou linii pro předpovědi prodeje podmíněného produktu:

R2 se blíží hodnotě 1 (0,9633), což indikuje minimální chybu aproximace. Budeme předpovídat objemy prodeje v následujících obdobích. Chcete-li to provést, musíte místo x dosadit číslo tečky v rovnici.

Například:

Pro výpočet předpovědních hodnot byl použit následující vzorec: =272,14*LN(B18)+287,21. Kde B18 je číslo období.

Polynomiální spojnice trendu v Excelu

Tato křivka má vzestupné a sestupné proměnné. U polynomů (polynomů) se stupeň určuje (podle počtu maximálních a minimálních hodnot). Například jeden extrém (minimum a maximum) je druhý stupeň, dva extrémy jsou třetí stupeň, tři jsou čtvrtý.

Polynomiální trend v Excelu se používá k analýze velkého souboru dat o nestabilní hodnotě. Podívejme se na příklad první sady hodnot (ceny ropy).

Pro získání takové hodnoty aproximační spolehlivosti (0,9256) jsem musel dát 6. stupeň.

Stáhněte si příklady grafů s trendovou linií

Ale takový trend vám umožňuje dělat více či méně přesné předpovědi.

Zdravím vás, drazí soudruzi! Dnes si rozebereme jednu ze subjektivních obchodních metod – obchodování pomocí trendových čar. Zvažme následující otázky:

1) Co je trend (toto je důležité jako výchozí bod)
2) Budování trendových čar
3) Využití v praktickém obchodování
4) Subjektivita metody

1) Co je trend
_________________
Než se přistoupí ke konstrukci trendové čáry, je nutné se přímo vypořádat s trendem samotným. Nebudeme se pouštět do akademických sporů a pro jednoduchost přijmeme následující vzorec:

Trend (vzestupný) je sekvence rostoucích maxim a minim, přičemž každé následující maximum (a minimum) je vyšší než předchozí.

Trend (sestupný) je sekvence klesajících (klesajících) maxim a minim, kde každé následující minimum (a maximum) je NIŽŠÍ než předchozí.

Trendová čára je čára mezi dvěma maximy (pokud je trend klesající) nebo dvěma minimy (pokud je trend vzestupný). To znamená, že trendová čára nám ukazuje, že v grafu existuje trend! Ale nemusí být (v případě bytu).

2) Budování trendových linií
____________________________

To je nejtěžší otázka! Na mnoha stránkách jsem viděl diskuze pouze o tom, JAK SPRÁVNĚ nakreslit trendovou čáru! Ale musíme na tom nejen stavět, ale také na tom obchodovat...

Chcete-li nakreslit trendovou čáru, musíte mít alespoň dvě maxima (sestupný trend) nebo dvě minima (narůstající trend). Tyto extrémy musíme spojit čárou.

Při kreslení čar je důležité dodržovat následující pravidla:

Důležitý je úhel trendové čáry. Čím strmější je úhel sklonu, tím je méně spolehlivý.
- Optimální je postavit úsečku na dvou bodech. Pokud stavíte na třech nebo více bodech, spolehlivost trendové linie se snižuje (je pravděpodobné, že dojde k jejímu rozpadu).
- Nepokoušejte se stavět linku za žádných podmínek. Pokud to není možné nakreslit, pak s největší pravděpodobností neexistuje žádný trend. Tento nástroj proto není vhodný pro použití v současných tržních podmínkách.

Tato pravidla vám pomohou správně sestavit trendové linie!

3) Obchodujte s trendovými liniemi
____________________________

Máme dvě zásadně odlišné možnosti:
A) Použijte čáru jako úroveň podpory (odporu) pro vstup podél ní ve směru trendu
B) Použijte Forex trendovou linii, abyste mohli hrát o rozklad (obrácení) trendu.

Oba způsoby jsou dobré, pokud víte, jak je „správně uvařit“.

Takže jsme postavili čáru na dvou bodech. Jakmile se cena dotkne čáry, měli bychom vstoupit na trh ve směru stávajícího trendu. Pro zadání používáme příkazy typu „limit nákupu nebo limitu prodeje“.

Vše je zde jednoduché a jasné. Jediné, co je třeba si zapamatovat, je, že čím častěji cena testuje trendovou linii a odrazí se od ní, tím vyšší je pravděpodobnost, že příští dotek bude prolomení linie!

Pokud si chceme hrát na prolomení trendové linie, musíme jednat trochu jinak:
1) Čekáme na dotyk linky
2) Čekáme na odraz
3) Umístěte nákupní stop příkaz (nebo prodejní stop příkaz) na zaškrtávací políčko
Věnujte pozornost kresbě.

Počkali jsme, až se objeví značka zaškrtnutí, a zadali příkaz k zastavení nákupu na maximum.

Po chvíli objednávka fungovala a my jsme vstoupili na trh.
Vyvstává legitimní otázka– proč nebylo možné okamžitě vstoupit na trh?
Věc se má tak, že nevíme, jestli bude testování trendové čáry úspěšné nebo ne. A po čekání na „tik“, dramaticky zvyšujeme naše šance na úspěch (vylučujeme falešné signály).

4) Subjektivita metody
_________________________

Všechno se zdá jednoduché? Ve skutečnosti při použití této metody narazíme na následující potíže:
A) Sklon čáry (vždy můžete kreslit trendové čáry s různými sklony.
B) Co se považuje za prolomení trendové čáry (o kolik bodů nebo procent by měla cena „prolomit“ linii, aby to bylo považováno za prolomení)?
C) Kdy by měla být trať považována za „zastaralou“ a kdy by měla být postavena nová?

Věnujte pozornost kresbě.

Červená čára označuje jeden ze stylů. Nezkušený obchodník by mohl udělat takovou čáru (a zaplatit za to).

V tomto případě jsou důležité praktické zkušenosti. To znamená, že není možné zredukovat vše na pár jednoduchá pravidla konstrukce. Proto indikátor trendové čáry neexistuje. Přesněji řečeno, může existovat, ale staví je „křivě“ a nesprávně. Tato technika byla původně „vybroušena“ pro zkušenost a zručnost obchodníka.

Osobně zřídka používám trendové čáry jako nezávislý nástroj. Ale přesto o nich mluvím z jednoho prostého důvodu. Faktem je, že je používá mnoho dalších obchodníků. Proto si my (vy i já) musíme být vědomi technik našich konkurentů.

Potřebujete tento nástroj ve svém obchodování - je to na vás!

Hodně štěstí a šťastné obchodování. Arthure.
blog-forex.org

Související příspěvky:

Trend obchodování koncept (video)

Trendové vzory (čísla)

Video k tomuto tématu:

Část 10. Výběr vzorců podle rozpisu. trendová linie

Pro výše uvedené problémy bylo možné sestavit rovnici nebo soustavu rovnic.

Ale v mnoha případech jsou při řešení praktických problémů pouze experimentální (výsledky měření, statistické, referenční, experimentální) údaje. Pomocí nich se s jistou mírou blízkosti snaží obnovit empirický vzorec (rovnici), který lze použít k nalezení řešení, modelování, hodnocení řešení a prognózám.

Proces výběru empirického vzorce P(x) pro zkušenou závislost F(x) volal přiblížení(vyhlazení). Pro závislosti s jednou neznámou používá Excel grafy a pro závislosti s mnoha neznámými dvojicemi funkcí ze skupiny Statistický LINEST a TREND, LGRFP a RŮST.

V této části uvažujeme o aproximaci experimentálních dat pomocí Excelové grafy: na základě dat se vykreslí graf, vybere se trendová linie , tj. aproximační funkce, která se blíží experimentální závislosti s maximální mírou blízkosti.

Odhadne se míra blízkosti zvolené funkce determinační koeficient R2 . Pokud neexistují žádné další teoretické úvahy, pak zvolte funkci s koeficientem R2 tendence k 1. Všimněte si, že výběr vzorců pomocí trendové čáry umožňuje stanovit jak formu empirického vzorce, tak určit číselné hodnoty neznámých parametrů.

Excel nabízí 5 typů aproximačních funkcí:

1. Lineární - y=cx+b. Tento nejjednodušší funkce, která odráží růst a úbytek dat konstantní rychlostí.

2. Polynom – y=c0+c1x+c2x2+…+c6x6. Funkce popisuje střídavě rostoucí a klesající data. Polynom 2. stupně může mít jeden extrém (min nebo max), polynom 3. stupně může mít až 2 extrémy, polynom 4. stupně může mít až 3 extrémy atd.

3. Logaritmické - y=c ln x+b. Tato funkce popisuje rychle rostoucí (klesající) data, která se následně stabilizují.

4. Výkon - y=cxb, (X>0 a y>0). Funkce zobrazuje data s neustále rostoucím (klesajícím) tempem růstu.

5. Exponenciální – y=cebx, (E je základem přirozeného logaritmu). Funkce popisuje rychle rostoucí (klesající) data, která se následně stabilizují.

Pro všech 5 typů funkcí je použita aproximace dat metodou nejmenších čtverců (viz nápověda k F1 "trend line").

Tak jako příklad zvažte závislost prodeje na reklamě danou následující statistikou pro určitou firmu:

Je nutné sestrojit funkci, která nejlépe odráží tuto závislost. Kromě toho je nutné odhadnout tržby za reklamní investice na 6 tisíc rublů.

Pojďme k řešení. Nejprve zadejte tato data do Excelu a vytvořte graf, jako na Obr. 38. Jak můžete vidět, graf je založen na řadě B2:J2. Dále kliknutím pravým tlačítkem myši na graf přidejte trendovou linii, jak je znázorněno na obr. 38.

Pro označení osy X odpovídajícími reklamními hodnotami (jako na obr. 38) vyberte položku v rozbalovací nabídce (obr. 38). A podobné údaje. V otevřeném stejnojmenném okně v záložce Řádek, v terénu Pštítky osy x, zadejte rozsah buněk, kde jsou zapsány hodnoty X (zde $B$1:$K$1):

V okně nastavení, které se otevře (obr. 39), na záložce Typ vyberte pro přiblížení logaritmický trendová čára (podle typu grafu). Záložka do knihy Možnosti zaškrtněte políčka, která zobrazují rovnici a koeficient determinace v grafu.

Po stisknutí OK Dostanete výsledek jako na obr. 40. Koeficient determinace R2= 0,9846, což je dobrý stupeň blízkosti. Pro potvrzení správnosti zvolené funkce (protože neexistují žádné další teoretické úvahy) predikujte vývoj tržeb na 10 období dopředu. Chcete-li to provést, klikněte pravým tlačítkem myši na linii trendu - změňte formát - poté v poli Předpověď: vpřed od: sada 10 (obr.

Po nastavení předpovědi uvidíte změnu křivky grafu na 10 období pozorování dopředu, jako na Obr. 42. S největší pravděpodobností odráží další zvýšení prodeje se zvýšením investic do reklamy.

Výpočet podle získaného vzorce \u003d 237,96 * LN (6) + 5,9606 v Excelu dává hodnotu 432 tisíc rublů.

Excel má funkci FORECAST(), která vypočítává budoucí hodnotu Y z existujících dvojic hodnot X a Y pomocí lineární regrese. Funkce Y by měla být pokud možno lineární, tzn. být popsána rovnicí typu c+bx.

Predikční funkce pro náš příklad by byla: =PREDICTION(K1;B2:J2;B1:J1). Zapište si to - měli byste získat hodnotu 643,6 tisíc rublů.

Část 11. Kontrolní úkoly

Předchozí12345678910111213141516Další