nadmašiti

Metoda približavanja u Microsoft Excelu

Među različitim metodama predviđanja ne možemo pomoći izolirati aproksimaciju. Pomoću nje možete izračunati približne izračune i izračunati planirane pokazatelje zamjenom izvornih objekata jednostavnijim. U programu Excel također postoji mogućnost korištenja ove metode za predviđanje i analizu. Pogledajmo kako se ova metoda može implementirati u određenom programu s ugrađenim alatima.

Izvršenje aproksimacije

Naziv ove metode dolazi od latinske riječi proxima - "najbliže" To je aproksimacija pojednostavljenjem i zaglađivanjem poznatih pokazatelja, usklađivanjem u trendu i njezinom osnovom. Ali ova se metoda može koristiti ne samo za predviđanje već i za istraživanje već dostupnih rezultata. Uostalom, aproksimacija zapravo predstavlja pojednostavljenje početnih podataka, a pojednostavljena verzija lakše je istražiti.

Glavni alat koji se koristi za izvođenje anti-aliasinga u Excelu jest izgraditi liniju trendova. Dno crta je da se na temelju već postojećih pokazatelja dovršava raspored funkcija za buduća razdoblja. Glavna svrha trenda, kao što nije teško pogoditi, jest priprema predviđanja ili utvrđivanje općeg trenda.

No, može se izraditi pomoću jedne od pet vrsta aproksimacije:

  • linearan;
  • eksponencijalna;
  • logaritamski;
  • polinom;
  • Snaga.

Razmotrimo svaku od opcija detaljnije.

Pouka: Kako izgraditi trend linije u programu Excel

Metoda 1: linearno zaglađivanje

Prije svega, razmotrimo najjednostavniju inačicu aproksimacije, naime pomoću jednostavne funkcije. Ovdje ćemo detaljnije preživjeti, budući da ćemo opisuju opće trenutke karakteristične za druge metode, naime, izgradnju grafikona i neke druge nijanse na kojima nećemo ostati u kasnijim verzijama.

Najprije izrađujemo grafikon na temelju kojeg ćemo provesti postupak zaglađivanja. Za iscrtavanje grafikona, uzmimo tablicu u kojoj se mjesečno prikazuju jedinični trošak jedinice koju proizvodi poduzeće i odgovarajuća dobit u određenom razdoblju. Grafička funkcija koju ćemo izgraditi odrazit će ovisnost povećanja dobiti na smanjenje troškova proizvodnje.

  1. Za iscrtavanje grafikona, prvo odaberite stupce "Jedinica troškova proizvodnje" i "Dobit" . Nakon toga pomaknite se na karticu "Umetni" . Zatim kliknite gumb "Spot" u okviru alata "Dijagrami". U otvorenom popisu odaberite naziv "Spot s glatkim krivuljama i oznakama" . To je ova vrsta dijagrama koja je najprikladnija za rad s linijom trendova, a time i za primjenu metode približavanja u Excel.

    2. Izgradnja grafikona u programu Microsoft Excel

  2. Graf je izgrađen.

    3. Graf je izgrađen u Microsoft Excelu

  3. Da biste dodali trend linije, odaberite ga klikom desne tipke miša. Pojavi se kontekstni izbornik. U njemu odaberite stavku "Dodaj liniju trendova ..." .

    Dodavanje trend linije kroz kontekstni izbornik u programu Microsoft Excel

    Postoji još jedan način da ga dodate. U dodatnoj skupini kartica na vrpci "Rad s grafikonima" prelazimo na karticu "Izgled" . Zatim, u okviru alata "Analiza" kliknite gumb "Trend Line" . Otvorit će se popis. Budući da moramo primijeniti linearnu aproksimaciju, tada ćemo s prezentiranih pozicija odabrati "Linearna aproksimacija" .

    4. Dodavanje trend linije kroz alatnu traku na kaseti u programu Microsoft Excel

  4. Ako odaberete prvu opciju s dodatkom putem kontekstnog izbornika, otvorit će se prozor formata.

    U parametarskom bloku "Izrada trend linije (aproksimacija i anti-aliasing)" postavite prekidač na položaj "Line" .
    Ako želite, možete provjeriti položaj "Prikaži jednadžbu na grafikonu" . Nakon toga, jednadžba glatke funkcije bit će prikazana na dijagramu.

    Također, u našem slučaju, da bismo usporedili različite varijante približavanja, važno je provjeriti "Stavite na dijagram vrijednost pouzdane približnosti (R ^ 2)" . Ovaj pokazatelj može varirati od 0 do 1 . Što je veći, to je bolja aproksimacija (pouzdanija). Vjeruje se da s vrijednostom ovog indeksa od 0,85 i više, izglađivanje se može smatrati pouzdanim, a ako je indikator manji, onda to nije.

    Nakon što ste izvršili sve gore navedene postavke. Kliknite gumb "Zatvori" koji se nalazi na dnu prozora.

    5. Uključivanje linearne aproksimacije u Microsoft Excel

  5. Kao što možete vidjeti, linija trendova izgrađena je na grafikonu. Za linearnu aproksimaciju označava crna ravna crta. Ova vrsta zaglađivanja može se koristiti u najjednostavnijim slučajevima, kada se podaci brzo mijenjaju, a očit je ovisnost vrijednosti funkcije na argumentu.

Trendska linija konstruirana je pomoću linearne aproksimacije u Microsoft Excelu

Smoothing, koji se koristi u ovom slučaju, opisan je sljedećom formulom:

y=ax+b

U našem konkretnom slučaju formula ima sljedeći oblik:

y=-0,1156x+72,255

Vrijednost pouzdanosti aproksimacije u našoj zemlji iznosi 0,9418 , što je prilično prihvatljiv rezultat, karakterizirajući izglađivanje, kao pouzdan.

Metoda 2: eksponencijalna aproksimacija

Pogledajmo sada eksponencijalni tip aproksimacije u programu Excel.

  1. Da biste promijenili vrstu trend linije, odaberite ga klikom desne tipke miša i odaberite "Trendline format ..." u otvorenom izborniku.

    2. Idite na format trend linije u programu Microsoft Excel

  2. Nakon toga se pokreće prozor formata koji nam je već poznat. U izboru bloka tipa aproksimacije postavite prekidač na položaj "Eksponencijalni" . Preostale postavke ostaju jednake kao u prvom slučaju. Kliknemo na gumb "Zatvori" .

    3. Izgradnja eksponencijalne linije trendova u programu Microsoft Excel

  3. Nakon toga će trend linija biti izgrađen na grafikonu. Kao što vidimo, prilikom korištenja ove metode ima pomalo zakrivljen oblik. Razina pouzdanosti je 0,9592 , što je veće od korištenja linearne aproksimacije. Eksponencijalna metoda najbolje se koristi kada se prvo promjene vrijednosti brzo mijenjaju, a zatim se uravnoteže.

Eksponencijalna linija trendova izgrađena je u programu Microsoft Excel

Opći oblik glatke funkcije je sljedeći:

y=be^x

gdje e je baza prirodnog logaritma.

U našem konkretnom slučaju formula je dobila sljedeći oblik:

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

Treća metoda: Logaritamsko zaglađivanje

Sada je vrijeme da razmotrimo metodu logaritamske aproksimacije.

  1. Na isti način kao i prethodno kroz kontekstni izbornik pokrenuli smo prozor formata trend linije. Postavite prekidač na položaj "Logaritamski" i kliknite gumb "Zatvori" .

    2. Uključi logaritamsku prilagodbu u Microsoft Excel

  2. Postoji postupak za konstruiranje trend linije s logaritamskom aproksimacijom. Kao iu prethodnom slučaju, ova je opcija bolja za upotrebu prilikom prvobitne promjene podataka brzo, a zatim uravnotežen izgled. Kao što možete vidjeti, razina pouzdanosti je 0.946. To je veće od korištenja linearne metode, ali je niža od kvalitete trend linije s eksponencijalnim zaglađivanjem.

Logaritamska trend linija je izgrađena u Microsoft Excelu

Općenito, formula izglađivanja izgleda ovako:

y=a*ln(x)+b

gdje je ln vrijednost prirodnog logaritma. Stoga naziv metode.

U našem slučaju formula ima sljedeći oblik:

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

Metoda 4: Polinomna zaglađivanje

Vrijeme je da uzmemo u obzir metodu poliedrijskog zaglađivanja.

  1. Prolazimo kroz prozor oblika trendline, kao što smo to učinili više puta. U bloku "Izrada trendline" postavite prekidač na poloľaj poloľaja. Desno od ove stavke je polje "Stupanj" . Ako odaberete "Polinomi", ona postaje aktivna. Ovdje možete odrediti bilo koju vrijednost snage od 2 (postavljeno prema zadanim postavkama) na 6 . Ovaj indikator određuje broj maksimuma i minima funkcije. Kada instalirate polinom drugog stupnja, opisuje se samo jedan maksimum, a prilikom postavljanja šestog stupnja polinoma može se opisati do pet maksimuma. Prvo, ostavljamo zadane postavke, to jest označavamo drugi stupanj. Preostale postavke ostaju iste kao što smo ih stavili u prethodne metode. Kliknite gumb "Zatvori" .

    2. Uključivanje polinomske aproksimacije u Microsoft Excel

  2. Konstruirana je linija trenda koja koristi ovu metodu. Kao što vidite, to je još više zakrivljeno nego pomoću eksponencijalne aproksimacije. Razina pouzdanosti je viša nego kod bilo koje od prethodno korištenih metoda i iznosi 0,9724 .

    Polinoma trend linije u programu Microsoft Excel

    Ova se metoda može uspješno primijeniti u slučaju da su podaci stalno varijabilni. Funkcija koja opisuje ovu vrstu zaglađivanja izgleda ovako:

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

    U našem slučaju formula je u obliku:

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

  3. Sada promijenimo stupanj polinoma kako bismo vidjeli hoće li se rezultat razlikovati. Vraćamo se u prozor formata. Vrsta aproksimacije je lijevo polinom, ali ispred njega u stupnjevitom prozoru postavljamo maksimalnu moguću vrijednost - 6 .

    4. Uključivanje polinomne aproksimacije u šestom stupnju u Microsoft Excelu

  4. Kao što vidite, nakon toga je naš trend linija u obliku izražene krivulje, u kojoj je broj maksima šest. Razina pouzdanosti povećana je još više, dostižući 0,9844 .

Polinomična linija trenda u šestom stupnju u Microsoft Excelu

Formula koja opisuje ovu vrstu zaglađivanja ima sljedeću formu:

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

5. metoda: postupno ublažavanje

Na kraju, razmotrite metodu aproksimacije snage u Excelu.

  1. Prelazimo u prozor "Oblik linije trendova" . Namjestili smo prekidač tipa zaglađivanja na položaj "Napajanje" . Prikazujući jednadžbu i razinu pouzdanosti, kao i uvijek, ostavljamo je. Kliknite gumb "Zatvori" .

    2. Polinomična linija trenda u šestom stupnju u Microsoft Excelu

  2. Program je trend linija. Kao što vidimo, u našem slučaju to je linija s laganim zavojima. Razina povjerenja je 0,9618 , što je prilično visok pokazatelj. Od svih gore navedenih metoda, razina pouzdanosti bila je viša samo kada se koristi polinomna metoda.

Trend trendova snaga je izgrađen u Microsoft Excelu

Ova se metoda učinkovito koristi u slučajevima intenzivne promjene funkcijskih podataka. Važno je uzeti u obzir da se ova varijanta primjenjuje samo pod uvjetom da funkcija i argument ne uzimaju negativne ili nulte vrijednosti.

Opća formula koja opisuje ovu metodu je kako slijedi:

y=bx^n

U našem konkretnom slučaju izgleda ovako:

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

Kao što vidimo, korištenjem specifičnih podataka korištenih za primjer, najviša razina pouzdanosti pokazala se metodom polinomne aproksimacije s polinomom u šestoj snazi ​​( 0.9844 ), najnižom razinom pouzdanosti u linearnoj metodi ( 0.9418 ). Ali to ne znači da će se ista tendencija koristiti pri korištenju drugih primjera. Ne, razina učinkovitosti gore navedenih metoda može se značajno razlikovati, ovisno o specifičnoj vrsti funkcije za koju će se graditi trend linija. Stoga, ako je odabrana metoda najučinkovitija za ovu funkciju, to ne znači da će ona također biti optimalna u drugoj situaciji.

Ako ne možete odmah utvrditi, na temelju gore navedenih preporuka, kakva je aproksimacija prikladna u vašem slučaju, onda ima smisla isprobati sve metode. Nakon izgradnje trend linije i gledanja razine pouzdanosti, možete odabrati najbolju opciju.