Aproximační metoda v aplikaci Microsoft Excel

Mezi různými metodami prognózování nelze pomoci aproximaci aproximace. S jeho pomocí můžete provést přibližné výpočty a vypočítat plánované indikátory tím, že původní objekty nahradíte jednoduššími. V aplikaci Excel je také možnost použít tuto metodu pro prognózu a analýzu. Podívejme se, jak lze tuto metodu implementovat ve specifikovaném programu s vestavěnými nástroji.

Provádění aproximace

Název této metody pochází z latinského slova proxima - "nejbližší" Je to aproximace zjednodušením a vyhlazením známých indikátorů, jejich zarovnání do trendu a jejím základem. Tato metoda však může být použita nejen pro prognózu, ale i pro výzkum již dostupných výsledků. Koneckonců aproximace je ve skutečnosti zjednodušení původních údajů a zjednodušená verze je snadněji prozkoumána.

Hlavním nástrojem, který se používá k provádění vyhlazení v aplikaci Excel, je vybudovat trendovou linii. Z toho vyplývá, že na základě již existujících ukazatelů je dokončen plán funkcí pro budoucí období. Hlavním účelem trendové linie, neboť není těžké odhadnout, je příprava prognóz nebo identifikace obecného trendu.

Ale může být postavena pomocí jednoho z pěti typů aproximace:

• Lineární;
• Exponenciální;
• Logaritmický;
• Polynom;
• Napájení.

Zvažme každou z možností podrobněji odděleně.

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

Metoda 1: lineární vyhlazování

Nejprve zvažte nejjednodušší verzi aproximace, a to pomocí lineární funkce. Zde budeme podrobněji vysvětlovat, protože budeme načrtávat obecné momenty charakteristické pro jiné metody, jmenovitě konstrukci grafu a některé další nuance, na které se nebudeme zabývat následnými verzemi.

Nejdříve vytvoříme graf, na jehož základě budeme provádět vyhlazovací proces. Pro vykreslení grafu si vezměme tabulku, ve které jsou jednotkové náklady na jednotku vytvořenou podnikem a odpovídající zisk v daném období zobrazeny měsíčně. Grafická funkce, kterou budeme vytvářet, odráží závislost zvýšení zisku na poklesu výrobních nákladů.

1. Chcete-li graf vykreslit, nejdříve vybereme sloupce "Jednotkové výrobní náklady" a "Zisk" . Potom přejděte na kartu "Vložit" . Potom klikněte na tlačítko "Spot" v poli "Diagramy". V otevřeném seznamu zvolte název "Bod s hladkými křivkami a značkami" . Je to takový druh diagramů, který je nejvhodnější pro práci s trendovou čarou, a proto pro aplikaci aproximační metody do aplikace Excel.
2. Graf je postaven.
3. Chcete-li přidat trendovou řadu, vyberte ji kliknutím pravým tlačítkem myši. Zobrazí se kontextová nabídka. Vyberte položku "Přidat trendovou řadu ..." .

   Existuje další způsob, jak ji přidat. V další skupině karet na pásu "Práce s grafy" přejdeme na kartu "Rozvržení" . Dále v poli "Analýza" klikněte na tlačítko "Trend Line" . Otevře se seznam. Vzhledem k tomu, že potřebujeme použít lineární aproximaci, pak z prezentovaných pozic zvolíme "Lineární aproximaci" .

4. Pokud zvolíte první možnost s přidáním pomocí kontextového menu, otevře se okno formátu.

   V bloku parametrů "Vytvoření trendové linie (aproximace a vyhlazení)" nastavte přepínač na pozici "Line" .
   Pokud chcete, můžete zkontrolovat polohu "Zobrazit rovnici na grafu" . Potom se v diagramu zobrazí rovnice funkce vyhlazení.

   Také v našem případě je pro porovnání různých variant aproximace důležité zkontrolovat "Umístěte na schéma hodnotu spolehlivého přiblížení (R ^ 2)" . Tento indikátor se může lišit od 0 do 1 . Čím vyšší je, tím lepší aproximace (spolehlivější). Předpokládá se, že s hodnotou tohoto indexu 0,85 a vyšším může být vyhlazení považováno za spolehlivé a pokud je indikátor nižší, pak tomu tak není.

   Po provedení všech výše uvedených nastavení. Klikněte na tlačítko Zavřít v dolní části okna.

5. Jak vidíte, trendová řada je postavena na grafu. Pro lineární aproximaci je označena černou přímkou. Tento typ vyhlazení lze použít v nejjednodušších případech, kdy se data mění poměrně rychle a je zřejmá závislost hodnoty funkce na argumentu.

Vyhlazování, které se v tomto případě používá, je popsáno následujícím způsobem:

y=ax+b

V našem konkrétním případě má vzorec následující formu:

y=-0,1156x+72,255

Hodnota spolehlivosti aproximace v naší zemi je 0,9418 , což je poměrně přijatelný výsledek, který charakterizuje vyhlazování a spolehlivost.

Metoda 2: exponenciální aproximace

Nyní se podívejme na exponenciální typ aproximace v aplikaci Excel.

1. Chcete-li změnit typ trendové linie, vyberte ji kliknutím pravým tlačítkem myši a v otevřené nabídce vyberte položku "Trendline formát ..." .
2. Potom je spuštěno okno formátu, které je již známé. Ve výběrovém bloku typu přiblížení nastavte přepínač do polohy "Exponenciální" . Zbývající nastavení zůstávají stejné jako v prvním případě. Klepněte na tlačítko Zavřít .
3. Poté bude trendová řada postavena na grafu. Jak vidíme, při použití této metody má poněkud zakřivený tvar. Úroveň spolehlivosti je 0,9592 , což je vyšší než použití lineární aproximace. Exponenciální metoda se nejlépe použije, když se hodnoty nejprve rychle změní a poté vyváženě vylíčí.

Obecná forma vyhlazovací funkce je následující:

y=be^x

kde e je základem přirozeného logaritmu.

V našem konkrétním případě vzorec získal následující formu:

y=6282,7*e^(-0,012*x)

Metoda 3: Logaritmické vyhlazení

Nyní je čas uvažovat o metodě logaritmické aproximace.

1. Stejně jako v předchozím čase přes kontextové menu spustíme okno formátu trendové linky. Nastavte přepínač do polohy "Logaritmický" a klikněte na tlačítko "Zavřít" .
2. Existuje postup pro vytváření trendové linie s logaritmickou aproximací. Stejně jako v předchozím případě je tato volba vhodnější, když se nejprve data rychle změní a poté vyvážený vzhled. Jak můžete vidět, úroveň důvěryhodnosti je 0,946. To je vyšší než pomocí lineární metody, ale je nižší než kvalita trendové linie s exponenciálním vyhlazováním.

Obecně platí, že vyhlazovací vzorec vypadá takto:

y=a*ln(x)+b

kde ln je hodnota přirozeného logaritmu. Název metody.

V našem případě má vzorec následující formu:

y=-62,81ln(x)+404,96

Metoda 4: Vyhlazení polynomu

Je čas uvažovat o způsobu vyhlazování polynomu.

1. Přecházíme do okna formátu trendové linky, jak jsme to udělali vícekrát. V bloku "Budování trendové linie" nastavte přepínač na pozici "Polynomial" . Vpravo od této položky je pole "Degree" . Pokud zvolíte "Polynom", bude aktivní. Zde můžete zadat jakoukoli hodnotu výkonu od 2 (výchozí nastavení) na hodnotu 6 . Tento indikátor určuje počet maxim a minima funkce. Při instalaci polynomu druhého stupně je popsáno pouze jedno maximum a při nastavení polynomu šestého stupně lze popsat až pět maxim. Nejdříve ponecháme výchozí nastavení, tedy označujeme druhý stupeň. Zbývající nastavení jsou ponechána stejná, jako je tomu v předchozích metodách. Klikněte na tlačítko Zavřít .
2. Trendová řada pomocí této metody je postavena. Jak je vidět, je ještě víc zakřivené než použití exponenciální aproximace. Úroveň spolehlivosti je vyšší než u kterékoli z předchozích metod a je 0,9724 .

   Tuto metodu lze nejúspěšněji použít v případě, že jsou data stále proměnlivá. Funkce popisující tento druh vyhlazování vypadá takto:

   y=a1+a1*x+a2*x^2+…+an*x^n

   V našem případě vzorec získal podobu:

   y=0,0015*x^2-1,7202*x+507,01

3. Nyní změníme stupeň polynomů, abychom zjistili, zda se výsledek bude lišit. Vrátíme se do okna formátu. Typ přiblížení je levý polynom, ale před ním v okně stupně nastavíme maximální možnou hodnotu - 6 .
4. Jak vidíte, pak naše trendová linie měla podobu výrazné křivky, v níž je počet maxima šest. Úroveň spolehlivosti se zvýšila ještě více a dosáhla hodnoty 0,9844 .

Vzorec popisující tento typ vyhlazení má následující podobu:

y=8E-08x^6-0,0003x^5+0,3725x^4-269,33x^3+109525x^2-2E+07x+2E+09

Metoda 5: Postupné vyhlazení

Nakonec zvažte metodu aproximace výkonu v aplikaci Excel.

1. Přejdeme do okna "Trend Line Format" . Přepínač vyhlazení nastavíme do polohy "Napájení" . Zobrazuje rovnici a úroveň spolehlivosti, jako vždy, ponecháme to. Klikněte na tlačítko Zavřít .
2. Program tvoří trendovou linii. Jak vidíme, v našem případě jde o linku s mírným zatáčkem. Úroveň spolehlivosti je 0,9618 , což je poměrně vysoký ukazatel. Ze všech výše uvedených metod byla úroveň spolehlivosti vyšší pouze při použití polynomiální metody.

Tato metoda je účinně používána v případech intenzivních změn funkčních dat. Je důležité vzít v úvahu, že tato varianta je použitelná pouze za předpokladu, že funkce a argument neobsahují záporné nebo nulové hodnoty.

Obecný vzorec popisující tuto metodu je následující:

y=bx^n

V našem konkrétním případě to vypadá takto:

y = 6E+18x^(-6,512)

Jak vidíme, za použití konkrétních dat, které jsme použili pro příklad, byla nejvyšší spolehlivost prokázána metodou aproximace polynomů s polynomem v šestém výkonu ( 0.9844 ), nejnižší spolehlivostí lineární metody ( 0.9418 ). Ale to neznamená, že stejná tendence bude použita při použití jiných příkladů. Ne, úroveň účinnosti výše uvedených metod se může výrazně lišit v závislosti na konkrétním typu funkce, pro kterou bude trendová linka postavena. Pokud je tedy zvolená metoda pro tuto funkci nejúčinnější, neznamená to, že bude také optimální v jiné situaci.

Pokud na základě výše uvedených doporučení nemůžete okamžitě zjistit, jaká aproximace je pro vás vhodná, pak je vhodné vyzkoušet všechny metody. Po vybudování řady trendů a zobrazení úrovně důvěryhodnosti můžete vybrat nejlepší možnost.

• ← Převedení dat z aplikace Microsoft Excel do formátu DBF
• Přesnost zaokrouhlení jako na obrazovce v aplikaci Microsoft Excel →