Metoda pokretnih proseka je statistički alat sa kojim možete rešavati različite vrste problema. Posebno se često koristi u prognoziranju. U Excelu se ovaj alat može koristiti i za rješavanje raznih zadataka. Pogledajmo kako se koristi pomični prosjek u Excelu.
Primjena pokretnog prosjeka
Smisao ove metode je da uz njenu pomoć dolazi do promjene apsolutnih dinamičkih vrijednosti odabranih serija na aritmetičke prosjeke za određeni period zaglađivanjem podataka. Ovaj alat se koristi za ekonomske kalkulacije, prognoziranje, u procesu trgovanja na berzi, itd. Najbolje je koristiti metod pokretnog prosjeka u Excelu uz pomoć najmoćnijeg alata za statističku obradu podataka, koji se zove Analizni paket. Pored toga, za ove svrhe možete koristiti ugrađenu Excel funkciju. AVERAGE.
Metod 1: Paket analize
Analizni paket je Excel dodatak koji je podrazumevano onemogućen. Stoga je, prije svega, potrebno da se to omogući.
- Pređite na karticu "Datoteka". Kliknite na stavku. "Opcije".
- U prozoru parametara koji se pokreće, idite na odjeljak Dodaci. Na dnu prozora u polju "Upravljanje" parametar mora biti postavljen Excel-ovi dodaci. Kliknite na dugme "Idi".
- Ulazimo u prozor dodataka. Postavite kvačicu pored stavke "Analizni paket" i kliknite na dugme "OK".
Nakon ovog paketa akcija "Analiza podataka" aktiviran, a odgovarajuća tipka se pojavila na vrpci na kartici "Data".
Pogledajmo sada kako možete direktno koristiti mogućnosti paketa. Analiza podataka da radimo na metodi pokretnih proseka. Dozvolite da, na osnovu informacija o prihodima firme u prethodnih 11 perioda, napravimo prognozu za dvanaesti mjesec. Da bismo to uradili, koristimo tabelu ispunjenu podacima i alatima. Analizni paket.
- Idite na karticu "Data" i kliknite na dugme "Analiza podataka"koji se stavlja na traku alata u bloku "Analiza".
- Lista alata koji su dostupni u Analizni paket. Mi biramo od njih ime "Pokretna prosek" i kliknite na dugme "OK".
- Prozor za unos podataka pokreće se za predviđanje pokretnog prosjeka.
Na terenu "Interval unosa" navedite adresu opsega, gdje se mjesečni iznos prihoda nalazi bez ćelije, podaci u kojima bi se trebali izračunati.
Na terenu "Interval" odredite interval obrade vrijednosti pomoću metode izglađivanja. Za početak, postavimo vrijednost izglađivanja na tri mjeseca i stoga unesite broj "3".
Na terenu "Izlazni razmak" potrebno je da navedete proizvoljni prazan raspon na listu, gdje će podaci biti prikazani nakon obrade, što bi trebala biti jedna ćelija veća od ulaznog intervala.
Takođe označite polje pored "Standardne greške".
Ako je potrebno, možete označiti i polje pored "Plotting" za vizuelnu demonstraciju, iako u našem slučaju to nije potrebno.
Nakon što su sva podešavanja izvršena, kliknite na dugme. "OK".
- Program prikazuje rezultat obrade.
- Sada ćemo izvršiti izglađivanje za period od dva mjeseca kako bismo otkrili koji je rezultat ispravniji. U tu svrhu ponovo pokrećemo alat. "Pokretna prosek" Analizni paket.
Na terenu "Interval unosa" Ostavite iste vrijednosti kao u prethodnom slučaju.
Na terenu "Interval" stavi broj "2".
Na terenu "Izlazni razmak" specificiramo adresu novog praznog opsega, koji opet mora biti jedna ćelija veća od ulaznog intervala.
Preostale postavke ostaju nepromijenjene. Nakon toga kliknite na dugme "OK".
- Nakon toga, program izračunava i prikazuje rezultat na ekranu. Da bismo utvrdili koji je od ta dva modela točniji, moramo usporediti standardne greške. Što je ovaj indikator manji, veća je vjerojatnost točnosti rezultata. Kao što možete vidjeti, za sve vrijednosti standardne pogreške u izračunu dvomjesečnog klizanja je manje od iste vrijednosti za 3 mjeseca. Stoga se predviđena vrijednost za prosinac može smatrati vrijednošću izračunatom metodom slip-a za posljednji period. U našem slučaju, ova vrijednost je 990,4 tisuća rubalja.
Metod 2: koristite funkciju AVERAGE
U Excel-u postoji još jedan način da se koristi metoda pomičnog prosjeka. Da biste ga koristili, potrebno je primijeniti niz standardnih programskih funkcija, od kojih je osnovno za našu svrhu AVERAGE. Na primer, koristićemo istu tabelu prihoda preduzeća kao u prvom slučaju.
Kao i prošli put, moraćemo stvoriti izglađene vremenske serije. Ali ovaj put akcije neće biti tako automatizovane. Izračunajte prosečnu vrednost za svaka dva i tri meseca da biste mogli da uporedite rezultate.
Pre svega, izračunavamo prosečne vrednosti za dva prethodna perioda koristeći funkciju AVERAGE. Mi to možemo da počnemo tek u martu, jer za kasnije datume postoji prekid vrednosti.
- Izaberite ćeliju u praznoj koloni u redu za mart. Zatim kliknite na ikonu "Umetni funkciju"koji se nalazi u blizini šipke formule.
- Aktivirani prozor Funkcioneri. U kategoriji "Statistical" looking for value "SRZNACH"izaberite ga i kliknite na dugme "OK".
- Počinje prozor operatera. AVERAGE. Njegova sintaksa je sledeća:
= AVERAGE (broj1; broj2; ...)Potreban je samo jedan argument.
U našem slučaju, na terenu "Number1" moramo dati vezu sa rasponom u kojem su naznačeni prihodi za dva prethodna perioda (januar i februar). Postavite kursor u polje i odaberite odgovarajuće ćelije na listu u koloni "Prihod". Nakon toga kliknite na dugme "OK".
- Kao što možete vidjeti, rezultat izračunavanja prosjeka za dva prethodna perioda je prikazan u ćeliji. Da bismo izvršili slične izračune za sve preostale mjesece perioda, moramo kopirati ovu formulu u druge ćelije. Da bismo to uradili, postajemo kursor u donjem desnom uglu ćelije koja sadrži funkciju. Kursor se pretvara u marker za popunjavanje, koji izgleda kao križ. Držite lijevi gumb miša i povucite ga do samog kraja stupca.
- Dobijamo izračunavanje prosječnih rezultata za dva prethodna mjeseca prije kraja godine.
- Sada izaberite ćeliju u sledećoj praznoj koloni u redu za april. Pozovite prozor argumenta funkcije AVERAGE na isti način kao što je prethodno opisano. Na terenu "Number1" unesite koordinate ćelija u koloni "Prihod" od januara do marta. Zatim kliknite na dugme "OK".
- Koristeći marker za popunjavanje, kopirajte formulu u ćelije tabele ispod.
- Dakle, izračunali smo vrijednosti. Sada, kao i ranije, moraćemo da utvrdimo koji je tip analize bolji: sa anti-aliasingom u 2 ili 3 meseca. Da biste to uradili, izračunajte standardnu devijaciju i neke druge indikatore. Prvo izračunavamo apsolutno odstupanje koristeći standardnu Excel funkciju. ABS, koji umjesto pozitivnog ili negativnog broja vraća njihov modul. Ova vrijednost će biti jednaka razlici između stvarnog prihoda za odabrani mjesec i prognoze. Postavite kursor u sljedeću praznu kolonu u nizu za svibanj. Call Čarobnjak za funkcije.
- U kategoriji "Matematički" odaberite ime funkcije "Abs". Pritisnemo dugme "OK".
- Počinje prozor argumenta funkcije. ABS. U jednom polju "Broj" odredite razliku između sadržaja ćelija u kolonama "Prihod" i "2 meseca" za maj. Zatim kliknite na dugme "OK".
- Koristeći marker za popunjavanje, kopiramo ovu formulu u sve redove tabele do novembra uključivo.
- Izračunajte prosječnu vrijednost apsolutnog odstupanja za cijelo razdoblje koristeći već poznatu funkciju AVERAGE.
- Izvodimo istu proceduru kako bismo izračunali apsolutno odstupanje za klizni za 3 mjeseca. Najprije koristimo funkciju ABS. Samo ovaj put razmatramo razliku između sadržaja ćelija sa stvarnim prihodom i planiranim, izračunatim metodom pokretnog prosjeka za 3 mjeseca.
- Zatim izračunavamo prosjek svih podataka o apsolutnim odstupanjima pomoću funkcije AVERAGE.
- Sljedeći korak je izračunavanje relativnog odstupanja. Ona je jednaka omjeru apsolutnog odstupanja od stvarnog pokazatelja. Da bismo izbegli negativne vrednosti, ponovo koristimo mogućnosti koje nudi operator ABS. Ovog puta, koristeći ovu funkciju, podelimo apsolutnu vrijednost odstupanja kada koristimo metodu pomičnog prosjeka za 2 mjeseca stvarnim prihodom za odabrani mjesec.
- Međutim, relativno odstupanje se obično prikazuje kao procenat. Stoga, izaberite odgovarajući raspon na listu, idite na karticu "Home"gde su u bloku alati "Broj" U posebnom polju za formatiranje postavite format procenta. Nakon toga, rezultat izračunavanja relativnog odstupanja je prikazan u procentima.
- Sličnu operaciju vršimo i za izračunavanje relativnog odstupanja sa podacima pomoću zaglađivanja za 3 mjeseca. Samo u ovom slučaju, za izračunavanje dividende, koristimo drugu kolonu tabele, koju imamo ime "Abs. Off (3m)". Potom prevodimo numeričke vrijednosti u postotak.
- Nakon toga izračunavamo prosječne vrijednosti za oba stupca s relativnim odstupanjem, kao i prije korištenja za tu svrhu AVERAGE. Pošto uzimamo procentualne vrednosti za funkciju kao argumente funkcije, ne treba nam dodatna konverzija. Operator na izlazu daje rezultat već u postotnom formatu.
- Sada dolazimo do izračuna standardne devijacije. Ovaj indikator će nam omogućiti da direktno uporedimo kvalitet obračuna kada koristimo anti-aliasing za dva i tri meseca. U našem slučaju, standardna devijacija će biti jednaka kvadratnom korijenu suma kvadrata razlika u stvarnom prihodu i pokretnom proseku podeljenom sa brojem meseci. Da bismo izvršili proračun u programu, moramo koristiti više funkcija, posebno ROOT, SUMMKRAVN i ACCOUNT. Na primer, da bi se izračunala standardna devijacija kada se koristi linija izglađivanja za dva meseca u maju, u našem slučaju, primenjuje se sledeća formula:
= ROOT (SUMKVRAZN (B6: B12; C6: C12) / RAČUN (B6: B12))Kopiramo ga u druge ćelije kolone izračunavanjem standardne devijacije pomoću markera za punjenje.
- Izvodimo sličnu operaciju za izračunavanje standardne devijacije za pokretni prosjek za 3 mjeseca.
- Nakon toga izračunavamo prosječnu vrijednost za cijeli period za oba indikatora, primjenjujući funkciju AVERAGE.
- Nakon što smo napravili poređenje kalkulacija pomoću metode pomičnog prosjeka sa izglađivanjem na 2 i 3 mjeseca koristeći indikatore kao što su apsolutno odstupanje, relativna odstupanja i standardna devijacija, možemo sa sigurnošću reći da dva mjeseca izglađivanje daje pouzdanije rezultate nego tri mjeseca izglađivanja. Na to ukazuje činjenica da su navedeni pokazatelji za dvomjesečni pokretni prosjek manji od tri mjeseca.
- Dakle, projicirani prihod kompanije u decembru će biti 990,4 hiljade rubalja. Kao što možete vidjeti, ova vrijednost je ista kao i ona koju smo dobili, čineći izračunavanje pomoću alata Analizni paket.
Lekcija: Čarobnjak funkcije Excel
Prognozu smo izračunali metodom pokretnog prosjeka na dva načina. Kao što vidite, ovaj postupak je mnogo lakše izvoditi pomoću alata. Analizni paket. Međutim, neki korisnici ne vjeruju uvijek automatskom izračunu i preferiraju korištenje funkcije za izračunavanje. AVERAGE i srodne operatore za provjeru najpouzdanije opcije. Iako je, ako je sve urađeno ispravno, na izlazu rezultat kalkulacija trebao biti potpuno isti.
