Přidání trendové linie v excelu. Predikční nástroje v aplikaci Microsoft Excel

Co dělat, když pro určité objemy/velikosti produktů neexistují žádná měření časování? Nebo je počet měření nedostatečný a další pozorování nelze v nejbližší době provést? Nejlepší způsobŘešením tohoto problému je konstrukce vypočítaných závislostí (regresních rovnic) pomocí trendových čar v MS Excel.

Zvažte reálnou situaci: ve skladu, abyste zjistili výši mzdových nákladů na krabicové vychystávání objednávky, pozorování načasování. Výsledky těchto pozorování jsou uvedeny v tabulce 1 níže.

Následně bylo nutné stanovit dobu vychystávání 0,6 a 0,9 m3 zboží/objednávky. Vzhledem k nemožnosti provést další chronometrické studie byl čas strávený výběrem těchto objemů zakázek vypočítán pomocí regresních rovnic v MS Excel. Za tímto účelem byla tabulka 1 převedena na tabulku 2.

Výběr bodového pozemku, Obr. 1

Další krok: kurzor myši byl umístěn na jeden z bodů v grafu a bylo použito pravé tlačítko myši pro volání kontextová nabídka, ve kterém byla položka vybrána: "přidat trendovou linii" (obr. 2).

Přidání trendové čáry, obr. 2

V zobrazeném okně nastavení formátu trendové čáry (obr. 3) byly postupně vybrány: typ čáry lineární / výkon a byla nastavena následující zaškrtávací políčka: „zobrazit rovnici na diagramu“ a „umístit do diagramu hodnotu spolehlivosti přiblížení (R ^ 2)“ (koeficient determinace).

Formát trendové čáry, obr. 3

V důsledku toho grafy uvedené na Obr. 4 a 5.

Lineární vypočtená závislost, Obr. 4

Výkon vypočtená závislost, Obr. 5

Vizuální analýza grafů jasně ukazuje na blízkost získaných závislostí. Navíc hodnota aproximační spolehlivosti (R^2), které se také říká koeficient determinace, je v případě obou závislostí stejná hodnota 0,97. Je známo, že čím blíže je koeficient determinace 1, tím více trendová linie odpovídá skutečnosti. Lze také konstatovat, že změna času stráveného zpracováním objednávky je 97% z důvodu změny množství zboží. Proto v tento případ není podstatné: kterou kalkulovanou závislost zvolit jako hlavní pro následnou kalkulaci časových nákladů.

Vezměme si jako hlavní - lineární vypočítanou závislost. Poté budou hodnoty času stráveného v závislosti na množství zboží určeny vzorcem: y = 54,511x + 0,1489. Výsledky těchto výpočtů pro množství zboží, pro které byla dříve prováděna chronometrická pozorování, jsou uvedeny v tabulce 3 níže.

Určíme průměrnou odchylku časových nákladů vypočtených regresní rovnicí od časových nákladů vypočtených z dat chronometrických pozorování: (-0,05+0,10-0,05+0,01)/4=0,0019. Časové náklady vypočítané podle regresní rovnice se tedy liší od časových nákladů vypočítaných podle údajů chronometrických pozorování pouze o 0,19%. Rozpor v údajích je zanedbatelný.

Podle vzorce: y = 54,511x + 0,1489 nastavíme časové náklady na množství zboží, pro které nebyla dříve provedena chronometrická pozorování. (tabulka 4).

Tedy konstrukce vypočtených závislostí pomocí trendových čar v MS Excel - Tento skvělá cesta stanovení času stráveného operacemi, které z různých důvodů nebyly pokryty chronometrickými pozorováními.

Nejčastěji trend je reprezentován lineárním vztahem zkoumaného množství formuláře

kde y je studovaná proměnná (například produktivita) nebo závislá proměnná;
x je číslo, které určuje pozici (druhou, třetí atd.) roku v období prognózy nebo nezávislá proměnná.

Při lineární aproximaci vztahu mezi dvěma parametry se k nalezení empirických koeficientů lineární funkce nejčastěji používá metoda nejmenších čtverců. Podstatou metody je to lineární funkce"nejlepší shoda" prochází body grafu, odpovídající minimálnímu součtu kvadrátů odchylek měřeného parametru. Takový stav vypadá takto:

kde n je objem studované populace (počet jednotek pozorování).

Rýže. 5.3. Vytvoření trendu pomocí metody nejmenších čtverců

Hodnoty konstant b a a nebo koeficient proměnné X a volný člen rovnice jsou určeny vzorcem:

V tabulce. 5.1 ukazuje příklad výpočtu lineárního trendu na základě dat.

Tabulka 5.1. Výpočet lineárního trendu

Metody vyhlazování vibrací.

Se silnými nesrovnalostmi mezi sousedními hodnotami je obtížné analyzovat trend získaný regresní metodou. Při prognózování, kdy řada obsahuje data s velkým rozptylem kolísání sousedních hodnot, je třeba je vyhlazovat podle určitých pravidel a pak hledat v prognóze význam. K metodě vyhlazování vibrací
zahrnují: metodu klouzavého průměru (vypočítá se n-bodový průměr), metodu exponenciálního vyhlazování. Zvažme je.

Metoda "klouzavých průměrů" (MSA).

MCC vám umožňuje vyhladit řadu hodnot za účelem zvýraznění trendu. Tato metoda bere průměr (obvykle aritmetický průměr) pevného počtu hodnot. Například tříbodový klouzavý průměr. Je vzata první trojice hodnot složená z dat za leden, únor a březen (10 + 12 + 13) a je určen průměr rovný 35: 3 = 11,67.

Výsledná hodnota 11,67 je umístěna do středu rozsahu, tzn. na únorové lince. Poté „posuneme o jeden měsíc“ a vezmeme druhou trojici čísel, počínaje únorem až dubnem (12 + 13 + 16), vypočítáme průměr rovný 41: 3 = 13,67 a tímto způsobem zpracujeme data pro celou sérii. Výsledné průměry představují nový řádek data pro sestavení trendu a jeho aproximace. Čím více bodů se vezme pro výpočet klouzavého průměru, tím silnější jsou výkyvy. Příklad z MVA budování trendu je uveden v tabulce. 5.2 a na Obr. 5.4.

Tabulka 5.2 Výpočet trendu metodou tříbodového klouzavého průměru

Charakter fluktuací počátečních dat a dat získaných metodou klouzavého průměru je znázorněn na Obr. 5.4. Z porovnání grafů řady počátečních hodnot (řádek 3) a tříbodových klouzavých průměrů (řádek 4) lze vidět, že výkyvy lze vyhladit. Jak více body budou zapojeny do rozsahu výpočtu klouzavého průměru, tím zřetelněji se trend objeví (řádek 1). Postup pro zvětšení rozsahu ale vede ke snížení počtu konečných hodnot, a to snižuje přesnost předpovědi.

Předpovědi by měly být založeny na odhadech regresní přímky, sestavených z hodnot původních dat nebo klouzavých průměrů.

Rýže. 5.4. Charakter změny tržeb podle měsíců v roce:
počáteční údaje (řádek 3); klouzavé průměry (řádek 4); exponenciální vyhlazování (řádek 2); regresní trend (řádek 1)

Metoda exponenciálního vyhlazování.

Alternativním přístupem ke snížení rozptylu sériových hodnot je použití metody exponenciálního vyhlazování. Metoda se nazývá "exponenciální vyhlazování" kvůli skutečnosti, že každá hodnota období, které jdou do minulosti, je snížena o faktor (1 - α).

Každá vyhlazená hodnota se vypočítá pomocí vzorce jako:

St =aYt +(1−α)St−1,

kde St je aktuální vyhlazená hodnota;
Yt je aktuální hodnota časové řady; St - 1 - předchozí vyhlazená hodnota; α je vyhlazovací konstanta, 0 ≤ α ≤ 1.

Čím menší je hodnota konstanty α , tím méně je citlivá na změny trendu v dané časové řadě.

Podíváme-li se na jakýkoli soubor dat rozložených v čase (časové řady), můžeme vizuálně identifikovat vzestupy a pády indikátorů, které obsahuje. Vzorec vzestupů a pádů se nazývá trend, který může naznačovat, zda naše data rostou nebo klesají.

Možná začnu sérii článků o prognózování tou nejjednodušší věcí – vytvořením trendové funkce. Vezměme například data o prodeji a sestavme model, který popisuje závislost prodeje na čase.

Základní pojmy

Myslím, že každý je obeznámen s lineární funkcí od školy, jen podtrhuje trend:

Y(t) = a0 + a1*t + E

Y je objem prodeje, proměnná, kterou si vysvětlíme časem a na které závisí, tedy Y (t);

t je číslo období ( sériové číslo měsíc), který vysvětluje plán prodeje Y;

a0 je nulový regresní koeficient, který ukazuje hodnotu Y(t), při absenci vlivu vysvětlujícího faktoru (t=0);

a1 je regresní koeficient, který ukazuje, jak moc závisí zkoumaný ukazatel prodeje Y na ovlivňujícím faktoru t;

E jsou náhodné poruchy, které odrážejí vliv dalších faktorů, které model nezohledňuje, s výjimkou času t.

Vytváření modelu

Známe tedy objem prodeje za posledních 9 měsíců. Takto vypadá naše deska:

Další věc, kterou musíme udělat, je určit koeficienty a0 A a1 předpovídat objem prodeje na 10. měsíc.

Stanovení modelových koeficientů

Vytváříme graf. Horizontálně vidíme nevyřízené měsíce, vertikálně objem prodeje:

V Tabulkách Google vyberte Editor diagramů -> Další a zaškrtněte políčko vedle trendové linie. V nastavení vyberte Označení — Rovnice A Zobrazit R^2.

Pokud vše děláte v MS Excel, tak klikněte pravým tlačítkem myši na graf a v rozbalovací nabídce zvolte "Přidat trendovou linii".

Ve výchozím nastavení je vytvořena lineární funkce. Vpravo vyberte „Zobrazit rovnici v diagramu“ a „Hodnota spolehlivosti přiblížení R^2“.

Co se stalo:

Na grafu vidíme rovnici funkce:

y = 4856*x + 105104

Popisuje objem prodeje v závislosti na čísle měsíce, pro který chceme tyto prodeje predikovat. Nedaleko vidíme koeficient determinace R^2, který udává kvalitu modelu a jak dobře popisuje naše prodeje (Y). Čím blíže k 1, tím lépe.

Mám R^2 = 0,75. Jedná se o průměrný ukazatel, říká, že model nezohledňuje žádné další významné faktory kromě času t, může to být například sezónnost.

Předpovídáme

y = 4856*10 + 105104

Máme 153664 prodejů příští měsíc. Pokud přidáme nový bod na grafu okamžitě vidíme, že se R^2 zlepšil.

Můžete tak předpovídat data na několik měsíců dopředu, ale bez zohlednění dalších faktorů bude vaše předpověď ležet na trendové linii a nebude tak informativní, jak byste chtěli. Takto provedená dlouhodobá předpověď bude navíc velmi přibližná.

Přesnost modelu můžete zvýšit přidáním sezónnosti do trendové funkce, což uděláme v příštím článku.

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á čára ( 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 dává 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.

Prognóza je velmi důležitý prvek téměř jakýkoli obor činnosti, od ekonomiky po strojírenství. Existuje velký počet software specializující se na tuto oblast. Bohužel ne všichni uživatelé vědí, že obvyklé tabulkový procesor Excel má ve svém arzenálu nástroje pro provádění prognóz, které nejsou ve své účinnosti horší profesionální programy. Pojďme zjistit, jaké jsou tyto nástroje a jak vytvořit předpověď v praxi.

Účelem jakékoli prognózy je identifikovat současný trend a určit očekávaný výsledek ve vztahu ke studovanému objektu v určitém časovém okamžiku v budoucnosti.

Metoda 1: trendová linie

Jedním z nejpopulárnějších typů grafického předpovídání v Excelu je extrapolace prováděná vytvořením trendové čáry.

Zkusme předpovědět výši zisku podniku za 3 roky na základě údajů o tomto ukazateli za předchozích 12 let.

Metoda 2: Operátor FORECAST

Extrapolaci pro tabulková data lze provést pomocí standardní funkce Excelu PŘEDPOVĚĎ. Tento argument patří do kategorie statistických nástrojů a má následující syntaxi:

FORECAST(X; známé hodnoty_y; známé hodnoty x)

"X" je argument, jehož funkční hodnota má být určena. V našem případě bude argumentem rok, pro který by měla být předpověď vytvořena.

"Známé hodnoty y" je základem známých hodnot funkce. V našem případě je jeho rolí výše zisku za minulá období.

"Známých x hodnot" jsou argumenty, které odpovídají známým hodnotám funkce. V jejich roli máme číslování let, za které byly sbírány informace o ziscích minulých let.

Časové období samozřejmě nemusí působit jako argument. Může to být například teplota a hodnota funkce může být úroveň expanze vody při zahřívání.

Při tomto výpočtu se používá metoda lineární regrese.

Podívejme se na nuance používání operátora PŘEDPOVĚĎ na konkrétní příklad. Vezměme stejný stůl. Budeme potřebovat znát prognózu zisku na rok 2018.

Ale nezapomeňte, že stejně jako při konstrukci trendové čáry by doba před obdobím prognózy neměla překročit 30 % celého období, za které byla databáze nashromážděna.

Metoda 3: Operátor TREND

Pro prognózování můžete použít další funkci - TREND. Patří také do kategorie statistických operátorů. Jeho syntaxe je velmi podobná syntaxi nástroje PŘEDPOVĚĎ a vypadá takto:

TREND(známé_y-hodnoty, známé_x-hodnoty, nové_x-hodnoty, [konst])

Jak vidíte, argumenty "Známé hodnoty y" A "Známých x hodnot" plně odpovídají podobným prvkům operátora PŘEDPOVĚĎ a argument "Nové hodnoty x" odpovídá argumentu "X" předchozí nástroj. Kromě toho při TREND existuje další argument "Konstantní", ale není to povinné a používá se pouze za přítomnosti konstantních faktorů.

Tento operátor se nejefektivněji používá, když existuje lineární závislost funkcí.

Podívejme se, jak bude tento nástroj pracovat se stejným datovým polem. Pro srovnání výsledků definujme rok 2019 jako bod prognózy.

Metoda 4: Operátor RŮST

Další funkcí, pomocí které můžete v Excelu vytvářet prognózy, je operátor RŮST. Patří také do statistické skupiny nástrojů, ale na rozdíl od předchozích při výpočtu nepoužívá lineární závislostní metodu, ale exponenciální. Syntaxe tohoto nástroje vypadá takto:

RŮST(známé_y-hodnoty, známé_x-hodnoty, nové_x-hodnoty, [konst])

Jak vidíte, argumenty této funkce přesně opakují argumenty operátoru TREND, nebudeme se tedy podruhé zdržovat jejich popisem, ale rovnou přistoupíme k aplikaci tohoto nástroje v praxi.

Metoda 5: Operátor LINREGRESE

Operátor LINEST při výpočtu se používá metoda lineární aproximace. Nemělo by se zaměňovat s metodou lineárního vztahu, kterou nástroj používá. TREND. Jeho syntaxe vypadá takto:

LINREGRESE(známé_y, známé_x, nové_x, [konst], [statistiky])

Poslední dva argumenty jsou volitelné. První dva známe z předchozích metod. Možná jste si ale všimli, že v této funkci chybí argument, který ukazuje na nové hodnoty. Faktem je, že tento nástroj určuje pouze změnu výše příjmů za jednotku období, která se v našem případě rovná jednomu roku, ale musíme spočítat celkovou částku samostatně a k poslední skutečné hodnotě zisku přičíst výsledek výpočtu operátora LINEST vynásobený počtem let.

Jak vidíte, předpokládaná hodnota zisku, vypočtená metodou lineární aproximace, bude v roce 2019 činit 4614,9 tisíc rublů.

Metoda 6: Operátor LFPRIB

Poslední nástroj, na který se podíváme, bude LGRFPRIBL. Tento operátor provádí výpočty založené na metodě exponenciální aproximace. Jeho syntaxe má následující strukturu:

LFPPRIB(známé_y-hodnoty, známé_x-hodnoty, nové_x-hodnoty, [konst], [statistiky])

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

Předpokládaná výše zisku v roce 2019, která byla vypočtena metodou exponenciální aproximace, bude 4639,2 tisíc rublů, což se opět příliš neliší od výsledků získaných výpočtem předchozích metod.

Zjišťovali jsme, jakými způsoby je možné dělat předpovědi v programu Excel. Graficky to lze provést pomocí trendové linie a analyticky pomocí řady vestavěných statistických funkcí. V důsledku zpracování identických údajů těmito operátory lze získat odlišný výsledek. Ale to není překvapující, protože všechny používají různé metody výpočet. Pokud je fluktuace malá, pak všechny tyto možnosti platí konkrétní případ lze považovat za poměrně spolehlivé.