EXCEL APPROXIMATION

Među različitim metodama predviđanja nije moguće ne razlikovati aproksimaciju. Uz njegovu pomoć, možete napraviti približne izračune i izračunati planirane pokazatelje zamjenom izvornih objekata jednostavnim. U Excelu postoji i mogućnost korišćenja ove metode za predviđanje i analizu. Pogledajmo kako se ovaj metod može primijeniti na specificirani program s ugrađenim alatima.

Izvršenje aproksimacije

Naziv ove metode dolazi od latinske reči proxima - „najbliže“, što je aproksimacija pojednostavljivanjem i izglađivanjem poznatih indikatora, njihovom izgradnjom u trend i njegovom osnovom. Ali ovaj metod se može koristiti ne samo za predviđanje, već i za istraživanje postojećih rezultata. Uostalom, aproksimacija je, u stvari, pojednostavljenje originalnih podataka, a pojednostavljena verzija je lakše istražiti.

Glavni alat sa kojim se vrši izglađivanje u Excelu je konstrukcija linije trenda. Zaključak je da je, na osnovu već raspoloživih indikatora, raspored funkcije završen za buduće periode. Glavna svrha linije trenda, kao što nije teško pogoditi, je izrada prognoza ili identifikovanje opšteg trenda.

Ali može se konstruisati koristeći jedan od pet tipova aproksimacija:

Linear;
Exponential;
Logaritamska;
Polinom;
Power.

Razmotrite svaku od opcija detaljnije odvojeno.

Lekcija: Kako izgraditi liniju trenda u Excelu

Metoda 1: Linearno zaglađivanje

Prvo, razmotrimo najjednostavniju aproksimaciju, naime, koristeći linearnu funkciju. O tome ćemo se detaljnije baviti, budući da smo postavili opšte tačke koje su karakteristične za druge metode, naime, crtanje i neke druge nijanse na koje se nećemo baviti prilikom razmatranja naknadnih opcija.

Prije svega, izgradićemo grafikon na osnovu kojeg ćemo izvesti postupak izglađivanja. Da bismo napravili grafikon, uzmemo tabelu u kojoj se jedinični trošak proizvodnje koji proizvodi preduzeće i odgovarajući profit u datom periodu navedu mesečno. Grafička funkcija koju gradimo odražava zavisnost povećanja profita od smanjenja troškova proizvodnje.

Za izradu grafa, prije svega, odaberite stupce "Jedinični troškovi proizvodnje" i "Profit". Nakon toga pređite na karticu "Umetni". Zatim na vrpci u bloku alata "Diagrams" kliknite na dugme "Spot". Na listi koja se otvori, izaberite ime "Točka sa glatkim krivuljama i markerima". Upravo je ova vrsta grafikona najpogodnija za rad s linijom trenda, te stoga i za primjenu metode aproksimacije u Excelu.

Raspored je izgrađen.

Da biste dodali liniju trenda, odaberite je klikom na desnu tipku miša. Pojavljuje se kontekstni izbornik. Izaberite stavku u njoj "Dodaj liniju trenda ...".
Postoji još jedna mogućnost da je dodate. U dodatnoj grupi kartica na vrpci "Rad sa grafikonima" pređite na tab "Layout". Sledeće u kutiji sa alatkama "Analiza" kliknite na dugme "Trend line". Otvara se lista. Pošto moramo da primenimo linearnu aproksimaciju, iz prikazanih pozicija biramo "Linearna aproksimacija".

Ako, međutim, odaberete prvu opciju akcija sa dodatkom preko kontekstnog izbornika, otvara se prozor formata.
U bloku parametara "Izgradnja linije trenda (aproksimacija i izglađivanje)" postavite prekidač u položaj "Linear".
Ako želite, možete postaviti kvačicu u blizini pozicije "Prikaži jednadžbu na grafikonu". Nakon toga, dijagram će prikazati jednadžbu funkcije izglađivanja.
Takođe u našem slučaju, za poređenje različitih aproksimacionih opcija, važno je da označite polje "Stavite na grafikon vrednost pouzdane aproksimacije (R ^ 2)". Ovaj indikator može varirati od 0 do 1. Što je veća, to je bolja aproksimacija (pouzdanija). Vjeruje se da kada vrijednost ovog pokazatelja 0,85 i veće izglađivanje može se smatrati pouzdanim, a ako je broj niži, onda - ne.
Nakon što ste imali sve gore navedene postavke. Pritisnemo dugme "Zatvori"postavljen na dnu prozora.

Kao što možete vidjeti, linija trenda je iscrtana na grafikonu. U slučaju linearne aproksimacije, ona je označena crnom ravnom linijom. Ovaj tip izglađivanja može se primijeniti u najjednostavnijim slučajevima, kada se podaci vrlo brzo mijenjaju, a zavisnost vrijednosti funkcije od argumenta je očigledna.

Izravnavanje, koje se koristi u ovom slučaju, opisano je sljedećom formulom:

y = ax + b

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

y = -0.1156x + 72.255

Veličina tačnosti aproksimacije je jednaka nama 0,9418, što je prilično prihvatljiv rezultat, opisujući izglađivanje kao pouzdano.

Metod 2: Eksponencijalna aproksimacija

Razmotrimo sada eksponencijalni tip aproksimacije u Excelu.

Da biste promijenili vrstu linije trenda, odaberite je klikom na desnu tipku miša iu padajućem izborniku odaberite stavku "Oblik trend linije ...".

Nakon toga, pokrenut je prozor za format koji nam je već poznat. U bloku za izbor tipa aproksimacije, postavite prekidač na "Eksponencijalno". Preostale postavke ostaju iste kao u prvom slučaju. Kliknite na dugme "Zatvori".

Nakon toga će se crtati linija trenda. Kao što možete vidjeti, kada koristite ovu metodu, ona ima blago zakrivljeni oblik. Nivo pouzdanosti je 0,9592, što je više nego kada se koristi linearna aproksimacija. Eksponencijalna metoda se najbolje koristi kada se vrijednosti prvo brzo promijene, a zatim uzmu uravnoteženi oblik.

Opći prikaz funkcije izglađivanja je sljedeći:

y = biti ^ x

gdje e - ovo je osnova prirodnog logaritma.

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

y = 6282.7 * e ^ (- 0.012 * x)

Metoda 3: Log Smoothing

Sada je red da razmotrimo metodu logaritamske aproksimacije.

Na isti način kao u prethodnom vremenu, preko kontekstnog menija, pokrećete prozor formata trend linije. Postavite prekidač u položaj "Logaritamska" i kliknite na dugme "Zatvori".

Postoji procedura izgradnje linije trendova sa logaritamskom aproksimacijom. Kao iu prethodnom slučaju, ova opcija je bolje koristiti kada se podaci u početku brzo promijene, a zatim se izbalansira. Kao što vidite, nivo pouzdanosti je 0,946. Ovo je više nego kada se koristi linearna metoda, ali je niža od kvaliteta linije trenda sa eksponencijalnim izglađivanjem.

U principu, formula za izravnavanje izgleda ovako:

y = a * ln (x) + b

gdje ln je veličina prirodnog logaritma. Otuda i ime metode.

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

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

Metod 4: polinomno izglađivanje

Vrijeme je da razmotrimo metod polinomnog izglađivanja.

Idite na prozor formata linije trenda, kao što ste već učinili više od jednom. U bloku "Izgradnja linije trenda" postavite prekidač u položaj "Polinom". Desno od ove stavke nalazi se polje "Stepen". Kada odaberete "Polinom" postaje aktivan. Ovdje možete odrediti bilo koju vrijednost snage 2 (postavljeno po defaultu) na 6. Ovaj indikator određuje broj maksimuma i minimuma funkcije. Kada se instalira polinom drugog stepena, opisuje se samo jedan maksimum, a kada se instalira polinom šestog stepena, može se opisati do pet maksimuma. Da počnemo, ostavljamo podrazumevane postavke, to jest, specificiramo drugi stepen. Preostale postavke ostaju iste kao što smo ih postavili u prethodnim metodama. Pritisnemo dugme "Zatvori".

Izgrađena je linija trenda pomoću ove metode. Kao što vidite, još je više zakrivljeno nego kada se koristi eksponencijalna aproksimacija. Nivo pouzdanosti je viši nego kod bilo koje od prethodno korišćenih metoda i jeste 0,9724.
Ova metoda se može najuspješnije primijeniti ako se podaci stalno mijenjaju. Funkcija koja opisuje ovaj tip izglađivanja izgleda ovako:
y = a1 + a1 * x + a2 * x ^ 2 +… + an * x ^ n
U našem slučaju, formula je dobila sljedeći oblik:
y = 0.0015 * x ^ 2-1.7202 * x + 507.01
Sada promijenimo stupanj polinoma da vidimo da li će rezultat biti drugačiji. Vraćamo se na prozor formata. Tip aproksimacije je lijevi polinom, ali ispred njega u prozoru stupnja postavljamo maksimalnu moguću vrijednost - 6.

Kao što možete vidjeti, nakon toga, naša linija trenda poprimila je oblik naglašene krivulje, u kojoj je broj vrhunaca šest. Nivo poverenja je porastao još više, čineći 0,9844.

Formula koja opisuje ovaj tip izglađivanja, dobila je sljedeći oblik:

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

Metoda 5: Izglađivanje snage

U zaključku razmotrimo metodu aproksimacije snage u Excelu.

Idite do prozora "Trend Line Format". Postavite prekidač pogleda za izravnavanje na položaj "Power". Pokazivanje jednakosti i nivoa povjerenja, kao i uvijek, ostavite ga na. Pritisnemo dugme "Zatvori".

Program formira liniju trenda. Kao što vidite, u našem slučaju, to je linija sa blagim zavojem. Nivo pouzdanosti je 0,9618što je prilično visoka cifra. Od svih gore opisanih metoda, nivo pouzdanosti bio je viši samo kada se koristio polinomni metod.

Ova metoda se efikasno koristi u slučajevima intenzivnih promjena podataka o funkciji. Važno je napomenuti da je ova opcija primjenjiva samo ako funkcija i argument ne prihvaćaju negativne ili nulte vrijednosti.

Opšta formula koja opisuje ovaj metod je sledeća:

y = bx ^ n

U našem slučaju, izgleda ovako:

y = 6E + 18x ^ (- 6.512)

Kao što možete vidjeti, kada se koriste specifični podaci koje smo koristili za primjer, metoda polinomne aproksimacije s polinomom u šestom stupnju (0,9844), najniži nivo povjerenja u linearnu metodu (0,9418). Ali to uopšte ne znači da će ista tendencija biti i kod drugih primjera. Ne, nivo efikasnosti gore navedenih metoda može se značajno razlikovati, u zavisnosti od specifičnog tipa funkcije za koju će biti izgrađena linija trenda. Stoga, ako je izabrana metoda najefikasnija za ovu funkciju, to uopšte ne znači da će ona biti optimalna iu drugoj situaciji.

Ako ne možete odmah utvrditi, na osnovu gore navedenih preporuka, koja je vrsta aproksimacije prikladna u vašem konkretnom slučaju, onda ima smisla isprobati sve metode. Nakon izgradnje linije trenda i gledanja nivoa pouzdanosti, možete odabrati najbolju opciju.