vynikať

Metóda aproximácie v programe Microsoft Excel

Medzi rôznymi metódami prognózovania nemožno pomôcť izolovať aproximáciu. S jeho pomocou môžete vykonať približné výpočty a vypočítať plánované ukazovatele, a to tak, že nahradíte pôvodné objekty jednoduchšími. V programe Excel je tiež možnosť použiť túto metódu na prognózovanie a analýzu. Poďme sa pozrieť na to, ako môže byť táto metóda implementovaná v špecifikovanom programe s vstavanými nástrojmi.

Vykonanie aproximácie

Názov tejto metódy pochádza z latinského slova proxima - "najbližšieho". Ide o aproximáciu zjednodušením a vyhladzovaním známych indikátorov, ich zosúladenie s trendom a jeho základom. Táto metóda sa však môže použiť nielen na prognózovanie, ale aj na výskum už dostupných výsledkov. Koniec koncov, aproximácia je v skutočnosti zjednodušenie pôvodných údajov a zjednodušená verzia je jednoduchšie vyšetriť.

Hlavným nástrojom, ktorý sa používa pri vykonávaní vyhladzovania v programe Excel, je vytvoriť trendovú líniu. Z toho vyplýva, že na základe už existujúcich ukazovateľov sa dokončí plán funkcií pre budúce obdobia. Hlavným cieľom trendovej línie, keďže nie je ťažké odhadnúť, je vypracovanie prognóz alebo identifikácia všeobecného trendu.

Môže sa však stavať pomocou jedného z piatich typov aproximácie:

  • lineárne;
  • exponenciálny;
  • logaritmické;
  • polynóm;
  • Power.

Pozrime sa na každú z možností podrobnejšie.

Lekcia: Ako vytvoriť trendu v programe Excel

Metóda 1: lineárne vyhladzovanie

Najprv zvážime najjednoduchšiu verziu aproximácie, a to pomocou lineárnej funkcie. Budeme sa na ňom podrobnejšie zaoberať, pretože načrtneme všeobecné momenty charakteristické pre iné metódy, menovite konštrukciu grafu a niektoré ďalšie nuansy, na ktorých sa nebudeme zaoberať nasledujúcimi verziami.

V prvom rade vytvoríme graf, na základe ktorého vykonáme vyhladzovací postup. Pri vykreslení grafu si zoberieme tabuľku, v ktorej sú jednotkové náklady jednotky vytvorenej podnikom a príslušný zisk v danom období zobrazované mesačne. Grafická funkcia, ktorú budeme tvoriť, odráža závislosť zvýšenia zisku od poklesu výrobných nákladov.

  1. Na vykreslenie grafu najprv vyberieme stĺpce "Jednotkové výrobné náklady" a "Zisk" . Potom prejdite na kartu "Vložiť" . Potom kliknite na tlačidlo "Spot" v poli "Diagramy". V otvorenom zozname vyberte názov "Bod s hladkými krivkami a značkami" . Je to takýto typ diagramov, ktorý je najvhodnejší na prácu s trendovou líniou, a preto na aplikáciu aproximačnej metódy do programu Excel.

    2. Vytváranie grafu v programe Microsoft Excel

  2. Graf je postavený.

    3. Graf je postavený v programe Microsoft Excel

  3. Ak chcete pridať trendový riadok, vyberte ho kliknutím pravým tlačidlom myši. Zobrazí sa kontextové menu. Vyberte položku "Pridať trendu trend ..." .

    Pridanie trendového riadku cez kontextové menu v programe Microsoft Excel

    Existuje ďalší spôsob, ako ho pridať. V ďalšej skupine kariet na karte "Práca s grafmi" prejdeme na kartu "Usporiadanie" . Ďalej v poli "Analýza" kliknite na tlačidlo "Trend Line" . Otvorí sa zoznam. Keďže potrebujeme použiť lineárnu aproximáciu, potom z ponúkaných pozícií vyberieme "lineárnu aproximáciu" .

    4. Pridanie trendového riadku cez panel nástrojov na kazetu v programe Microsoft Excel

  4. Ak ste si vybrali prvú možnosť pridaním pomocou kontextového menu, otvorí sa okno formátu.

    V bloku parametrov "Vytvorenie trendu riadku (aproximácia a vyhladenie)" nastavte prepínač na pozíciu "Line" .
    Ak chcete, môžete skontrolovať polohu "Zobraziť rovnicu v grafe" . Potom sa v diagrame zobrazí rovnica funkcie vyhladzovania.

    Aj v našom prípade je na porovnanie rôznych variantov aproximácie dôležité skontrolovať "uveďte hodnotu spoľahlivej aproximácie (R ^ 2) na diagrame" . Tento indikátor sa môže meniť od 0 do 1 . Čím vyššie je, tým lepšie je aproximácia (spoľahlivejšia). Predpokladá sa, že s hodnotou tohto indexu 0,85 a vyšším môže byť vyhladzovanie považované za spoľahlivé a ak je ukazovateľ nižší, potom to nie je.

    Po vykonaní všetkých vyššie uvedených nastavení. Kliknite na tlačidlo "Zavrieť" umiestnené v spodnej časti okna.

    5. Zahrnutie lineárnej aproximácie do programu Microsoft Excel

  5. Ako vidíte, trendová čiara je postavená na grafe. Pre lineárnu aproximáciu je označená čiernou priamkou. Tento typ vyrovnávania môže byť použitý v najjednoduchších prípadoch, keď sa dáta menia pomerne rýchlo a závislosť hodnoty funkcie na argument je zrejmá.

Trendová čiara je vytvorená pomocou lineárnej aproximácie v programe Microsoft Excel

Vyhlazovanie, ktoré sa v tomto prípade používa, je opísané nasledujúcim vzorcom:

y=ax+b

V našom konkrétnom prípade má vzorec nasledujúcu formu:

y=-0,1156x+72,255

Hodnota spoľahlivosti aproximácie v našej krajine je 0,9418 , čo je celkom prijateľný výsledok, charakterizujúci vyhladzovanie, ako spoľahlivé.

Metóda 2: exponenciálna aproximácia

Teraz sa pozrime na exponenciálny typ aproximácie v Exceli.

  1. Ak chcete zmeniť typ trendu, vyberte ju kliknutím pravým tlačidlom myši a zvoľte "Trendline format ..." v otvorenom menu.

    2. Prejdite do formátu trendu v programe Microsoft Excel

  2. Potom sa spustí okno formátu, ktoré je už známe. Vo výbere bloku typu aproximácie prepnite prepínač do polohy "Exponenciálne" . Zostávajúce nastavenia zostávajú rovnaké ako v prvom prípade. Klikneme na tlačidlo Zavrieť .

    3. Vybudovanie exponenciálneho trendu v programe Microsoft Excel

  3. Potom bude trendová čiara postavená na grafe. Ako vidíme, pri použití tejto metódy má trochu zakrivený tvar. Úroveň spoľahlivosti je 0,9592 , čo je vyššia ako pri použití lineárnej aproximácie. Exponenciálna metóda sa najlepšie používa, keď sa najskôr hodnoty rýchlo menia a potom vyvážené.

Exponenciálna trenka je postavená v programe Microsoft Excel

Všeobecná forma funkcie vyhladzovania je nasledovná:

y=be^x

kde e je základom prirodzeného logaritmu.

V našom konkrétnom prípade vzorec nadobudol nasledujúcu formu:

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

Metóda 3: Logaritmické vyhladzovanie

Teraz je čas zvážiť metódu logaritmickej aproximácie.

  1. Rovnako ako v predchádzajúcej dobe cez kontextové menu spustíme okno formátu trendu. Prepnite prepínač do polohy "Logaritmický" a kliknite na tlačidlo "Zavrieť" .

    2. Zahrňte logaritmické aproximácie v programe Microsoft Excel

  2. Existuje postup pre zostavenie trendu s logaritmickou aproximáciou. Rovnako ako v predchádzajúcom prípade je táto možnosť lepšie použiť, keď sa najprv dáta rýchlo menia a potom vyvážený vzhľad. Ako môžete vidieť, úroveň dôvery je 0,946. Toto je vyššie ako pri použití lineárnej metódy, ale je nižšia ako kvalita trendu s exponenciálnym vyhladzovaním.

Logaritmická trendová linka je postavená v programe Microsoft Excel

Všeobecne platí, že vyhladzovací vzorec vyzerá takto:

y=a*ln(x)+b

kde ln je hodnota prirodzeného logaritmu. Preto je názov metódy.

V našom prípade vzorec má nasledujúcu formu:

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

Metóda 4: vyhladzovanie polynómov

Je čas zvážiť metódu vyhladzovania polynomiálov.

  1. Prejdeme do okna formátu trendovej čiary, ako sme to urobili viackrát. V bloku "Budovanie trendu" nastavte prepínač na pozíciu "Polynomial" . Vpravo od tejto položky je pole "Degree" . Ak vyberiete "Polynom", stane sa aktívny. Tu môžete nastaviť akúkoľvek hodnotu výkonu od 2 (štandardne nastavená) na hodnotu 6 . Tento indikátor určuje počet maximálnych a minimálnych hodnôt funkcie. Pri inštalácii polynomu druhého stupňa je opísané len jedno maximum a pri nastavení polynomálneho šiesteho stupňa možno opísať maximálne päť maximov. Najskôr necháme predvolené nastavenia, to znamená, že označujeme druhý stupeň. Ostatné nastavenia zostávajú rovnaké, ako sme ich vložili do predchádzajúcich metód. Kliknite na tlačidlo Zavrieť .

    2. Zahrnutie aproximácie polynomov do programu Microsoft Excel

  2. Trendová čiara využívajúca túto metódu je konštruovaná. Ako vidíte, je to ešte viac zakrivené ako použitie exponenciálnej aproximácie. Úroveň spoľahlivosti je vyššia ako pri ktorejkoľvek z predchádzajúcich metód a je 0,9724 .

    Polynomiálna trenka v programe Microsoft Excel

    Táto metóda sa dá najúspešnejšie uplatniť v prípade, že sú údaje nepretržite premenlivé. Funkcia popisujúca tento druh vyhladzovania vyzerá takto:

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

    V našom prípade vzorec nadobudol formu:

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

  3. Teraz zmeňte stupeň polynómov, aby sme videli, či sa výsledok bude líšiť. Vrátime sa do okna formátu. Typ aproximácie je ľavý polynom, ale pred ním v okne stupňa nastavíme maximálnu možnú hodnotu - 6 .

    4. Zahrnutie aproximácie polynomov do šiesteho stupňa v programe Microsoft Excel

  4. Ako môžete vidieť, potom naša trendová línia nadobudla formu výraznej krivky, v ktorej počet maximov je šesť. Úroveň spoľahlivosti sa zvýšila ešte viac a dosiahla hodnotu 0,9844 .

Polynomiálna trendová línia šiesteho stupňa v programe Microsoft Excel

Vzorec, ktorý popisuje tento typ vyhladzovania, má nasledujúcu formu:

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

Metóda 5: Postupné vyhladzovanie

Nakoniec zvážte metódu aproximácie výkonu v programe Excel.

  1. Prejdeme do okna "Trend Line Format" . Prepínač typu vyhladzovania nastavíme do polohy "Napájanie" . Zobrazuje rovnicu a úroveň spoľahlivosti, ako vždy, ponecháme ju. Kliknite na tlačidlo Zavrieť .

    2. Polynomiálna trendová línia šiesteho stupňa v programe Microsoft Excel

  2. Program tvorí trendovú líniu. Ako vidíme, v našom prípade ide o čiaru s miernym ohybom. Úroveň dôvery je 0,9618 , čo je pomerne vysoký ukazovateľ. Zo všetkých uvedených metód bola úroveň spoľahlivosti vyššia len pri použití polynomickej metódy.

Trendová linka výkonu je postavená v programe Microsoft Excel

Táto metóda sa účinne používa v prípadoch intenzívnej zmeny funkčných údajov. Je dôležité brať do úvahy, že tento variant je použiteľný len za podmienky, že funkcia a argument nemajú negatívne alebo nulové hodnoty.

Všeobecný vzorec opisujúci túto metódu je nasledujúci:

y=bx^n

V našom konkrétnom prípade to vyzerá takto:

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

Ako vidíme, pri použití špecifických údajov, ktoré sme použili pre príklad, najvyššiu úroveň spoľahlivosti dokázala metóda aproximácie polynomov s polynomom v šiestom napätí ( 0.9844 ), čo je najnižšia úroveň spoľahlivosti v lineárnej metóde ( 0.9418 ). To však neznamená, že rovnaká tendencia sa použije pri použití iných príkladov. Nie, úroveň účinnosti uvedených metód sa môže výrazne líšiť v závislosti od konkrétneho typu funkcie, pre ktorú bude postavená trendu. Preto, ak je zvolená metóda najúčinnejšia pre túto funkciu, neznamená to, že bude aj optimálna v inej situácii.

Ak na základe vyššie uvedených odporúčaní nemôžete okamžite zistiť, aký druh aproximácie je pre vás správny, potom má zmysel skúsiť všetky metódy. Po vytvorení trendovej línie a sledovaní jej úrovne spoľahlivosti si môžete zvoliť najlepšiu možnosť.