Način približavanja u programu Microsoft Excel

Među raznim metodama predviđanja ne može se osim izdvajanja aproksimacije. Pomoću nje možete napraviti grube procjene i izračunati planirane pokazatelje zamjenom izvornih objekata jednostavnijim. U Excelu postoji i mogućnost upotrebe ove metode za predviđanje i analizu. Pogledajmo kako se ova metoda može primijeniti u navedenom programu s ugrađenim alatima.

Približavanje

Naziv ove metode dolazi od latinske riječi proxima - „najbliže“, to je približavanje pojednostavljivanjem i uglađivanjem poznatih pokazatelja, uvodeći ih u trend koji je njegova osnova. Ali ova metoda se može koristiti ne samo za predviđanje, već i za proučavanje postojećih rezultata. Napokon, aproksimacija je, u stvari, pojednostavljenje izvornih podataka, a pojednostavljena verzija je lakše istražiti.

Glavni alat s kojim se izglađivanje vrši u Excelu je izgradnja linije trenda. Dno crta je da se na osnovu postojećih pokazatelja popunjava grafikon funkcije za buduća razdoblja. Kao što pretpostavljate, glavna svrha linije trenda je predviđanje ili identificiranje općeg trenda.

Ali može se konstruirati pomoću jedne od pet vrsta aproksimacije:

• Linearno;
• Eksponencijalna;
• Logaritamska;
• Polinom;
• Snaga.

Svaku od opcija razmatramo detaljnije zasebno.

Lekcija: Kako izgraditi liniju trenda u Excelu

Metoda 1: linearno zaglađivanje

Prije svega, pogledajmo najjednostavniju opciju aproksimacije, naime koristeći linearnu funkciju. Na tome ćemo se detaljnije pozabaviti jer ćemo izložiti opće točke koje su karakteristične za druge metode, a to je izgradnja rasporeda i neke druge nijanse na kojima se nećemo zadržavati kada razmatramo sljedeće mogućnosti.

Prije svega napravit ćemo graf na osnovu kojeg ćemo izvesti postupak glađenja. Za izradu rasporeda uzimamo tablicu u kojoj su naznačeni mjesečni troškovi jedinice proizvodnje koje proizvede preduzeće i odgovarajući profit u datom periodu. Grafička funkcija koju ćemo izgraditi odražavat će ovisnost povećanja dobiti od smanjenja troškova proizvodnje.

1. Da biste nacrtali, prvo odaberite stupce "Jedinični trošak" i Profit. Nakon toga pređite na karticu Umetni. Zatim na vrpci u okviru alata Charts kliknite gumb "Spot". Na listi koja se otvori odaberite ime „Tačka sa glatkim krivinama i markerima“. Upravo je ta vrsta grafikona najprikladnija za rad s linijama trenda, a samim tim i za primjenu metode aproksimacije u Excelu.



2. Raspored je izgrađen.



3. Da biste dodali liniju trenda, odaberite je klikom desnog gumba miša. Pojavi se kontekstni meni. Odaberite stavku u njoj "Dodajte liniju trenda ...".

   

   Postoji još jedna mogućnost da se doda. U dodatnoj grupi kartica na vrpci "Rad sa grafikonima" pređite na karticu "Izgled". Dalje u blok alata "Analiza" kliknite na gumb Trend Line. Otvara se popis. Budući da trebamo primijeniti linearnu aproksimaciju, odabiremo iz predstavljenih pozicija "Linearna aproksimacija".



4. Ako ipak odaberete prvu mogućnost s dodavanjem radnji putem kontekstnog izbornika, otvorit će se prozor formata.

   U bloku parametara „Izgradnja linije trenda (aproksimacija i izravnavanje)“ postavite prekidač u položaj "Linearno".
   Ako želite, možete potvrditi potvrdni okvir pored položaja "Prikaži jednadžbu u dijagramu". Nakon toga na dijagramu će se prikazati jednadžba funkcije izglađivanja.

   Također u našem slučaju, za usporedbu različitih opcija aproksimacije, važno je potvrditi okvir pored "Stavite na grafikon vrijednost pouzdanog približavanja (R ^ 2)". Ovaj indikator može varirati od 0 prije 1. Što je veća, aproksimacija je bolja (pouzdanija). Smatra se da je s vrijednošću ovog pokazatelja 0,85 i više, zaglađivanje se može smatrati pouzdanim, ali ako je pokazatelj niži, onda ne.

   Nakon što ste izvršili sve gore navedene postavke. Kliknite na dugme Zatvorikoji se nalazi na dnu prozora.



5. Kao što vidite, linija trenda je iscrtana na grafikonu. S linearnom aproksimacijom, on je naznačen crnom ravnom linijom. Navedena vrsta izglađivanja može se upotrijebiti u najjednostavnijim slučajevima kada se podaci mijenjaju vrlo brzo i ovisnost vrijednosti funkcije o argumentu je očita.

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

y = os + b

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

y = -0.1156x + 72.255

Vrijednost tačnosti aproksimacije jednaka je 0,9418, što je prilično prihvatljiv rezultat koji karakterizira izglađivanje kao pouzdano.

Metoda 2: Eksponencijalna aproksimacija

Sada pogledajmo eksponencijalni tip aproksimacije u Excelu.

1. Da biste promijenili vrstu linije trenda, odaberite je klikom desnog gumba miša i odaberite stavku u skočnom meniju "Format linije trenda ...".



2. Nakon toga pokreće se prozor poznatog formata. U bloku za odabir tipa približavanja postavite prekidač na "Eksponencijalna". Preostale postavke ostat će iste kao u prvom slučaju. Kliknite na dugme Zatvori.



3. Nakon toga će se na liniji prikazati linija trenda. Kao što vidite, kada koristite ovu metodu, ima blago zakrivljen oblik. U ovom slučaju je nivo povjerenja 0,9592, što je veće nego kada se koristi linearna aproksimacija. Eksponencijalna metoda se najbolje koristi kada se vrijednosti brzo mijenjaju, a zatim poprimaju uravnoteženi oblik.

Opći oblik funkcije zaglađivanja je sljedeći:

y = biti ^ x

gde e je osnova prirodnog logaritma.

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

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

Metoda 3: logaritamsko zaglađivanje

Sada je red da se razmotri metoda logaritamske aproksimacije.

1. Na isti način kao i prethodni put, kroz kontekstni izbornik pokrećemo prozor formata trenda. Podesite prekidač u položaj "Logaritamski" i kliknite na dugme Zatvori.



2. Postoji postupak za izgradnju linije trenda s logaritamskom aproksimacijom. Kao i u prethodnom slučaju, ova se opcija najbolje koristi kada se podaci u početku brzo promijene, a zatim poprimi uravnoteženi izgled. Kao što vidite, nivo povjerenja iznosi 0,946. Ovo je više nego pomoću linearne metode, ali niže od kvalitete linije trenda s eksponencijalnim zaglađivanjem.

Općenito, formula za izravnavanje izgleda ovako:

y = a * ln (x) + b

gde ln je vrijednost prirodnog logaritma. Odatle i naziv metode.

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

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

Metoda 4: izravnavanje polinoma

Došlo je vrijeme da se razmotri metoda zaglađivanja polinoma.

1. Idite na prozor forme linije trenda, kao što je već više puta. U bloku „Izgradnja linije trenda“ postavite prekidač u položaj "Polinom". Desno od ove stavke je polje "Stepen". Prilikom odabira vrijednosti "Polinom" postaje aktivna. Ovdje možete odrediti bilo koju vrijednost snage 2 (zadano postavljeno) na 6. Ovaj indikator određuje broj maksima i minima funkcije. Prilikom ugradnje polinoma drugog stepena opisuje se samo jedan maksimum, a prilikom instaliranja polinoma šestog stepena može se opisati do pet maksima. Prvo ostavimo zadane postavke, odnosno navest ćemo drugi stepen. Ostatak postavki ostavljamo isti kao što smo ih postavili u prethodnim metodama. Kliknite na dugme Zatvori.



2. Crta trenda pomoću ove metode je iscrtana. Kao što vidite, još je zakrivljenija nego kada koristite eksponencijalnu aproksimaciju. Nivo pouzdanosti je viši nego kod bilo koje prethodno korištene metode i jest 0,9724.

   

   Ova se metoda može najuspješnije primijeniti ako su podaci stalno promjenjivi. Funkcija koja opisuje ovu vrstu glađenja izgleda ovako:

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

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

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

3. Sada promijenimo stupanj polinoma kako bismo vidjeli hoće li se rezultat razlikovati. Vratimo se prozoru formata. Ostavimo polinom tipa aproksimacije, ali nasuprot njemu, u prozoru stepena, postavimo maksimalnu moguću vrednost - 6.



4. Kao što vidite, nakon ove naše trend linije poprimio je oblik izražene krivulje, u kojoj je broj maksima šest. Nivo povjerenja se još više povećao, iznosivši 0,9844.

Formula koja opisuje ovu vrstu izglađivanja poprima sljedeći oblik:

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

Metoda 5: izravnavanje snage

Zaključno, razmotrimo metodu aproksimacije zakona-moći u Excelu.

1. Prelazimo na prozor Format trend linije. Podesite vrstu prekidača za izravnavanje u položaj "Snaga". Prikaz jednačine i nivoa pouzdanja, kao i uvek, je ostavljen uključen. Kliknite na dugme Zatvori.



2. Program formira liniju trenda. Kao što vidite, u našem slučaju to je linija sa blagim zavojem. Nivo povjerenja je 0,9618, što je prilično velika stopa. Od svih gore navedenih metoda, stupanj pouzdanosti bio je viši samo kad se koristi polinomska metoda.

Ova metoda se efikasno koristi u slučajevima intenzivne promjene podataka o funkcijama. Važno je uzeti u obzir da je ova opcija primjenjiva samo pod uvjetom da funkcija i argument ne prihvaćaju negativne ili nulte vrijednosti.

Opća formula koja opisuje ovu metodu ima sljedeći oblik:

y = bx ^ n

U našem konkretnom slučaju to izgleda ovako:

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

Kao što vidite, prilikom korištenja specifičnih podataka koje smo koristili kao primjer, metoda polinomne aproksimacije s polinomom do šestog stupnja pokazala je najviši stupanj pouzdanosti (0,9844), najniži nivo povjerenja u linearnu metodu (0,9418) Ali to uopće ne znači da će ista tendencija biti i s ostalim primjerima. Ne, razina efikasnosti gore navedenih metoda može se značajno razlikovati, ovisno o specifičnoj vrsti funkcije za koju će se graditi linija trenda. Dakle, ako je odabrana metoda najefikasnija za ovu funkciju, to ne znači da će i u drugoj situaciji biti optimalna.

Ako još ne možete odmah na osnovu gornjih preporuka utvrditi koja je vrsta približavanja posebno prikladna za vaš slučaj, onda ima smisla isprobati sve metode. Nakon izgradnje linije trenda i gledanja njegove razine pouzdanosti, bit će moguće odabrati najbolju opciju.