Metoda pomicanja prosjeka u programu Microsoft Excel

Metoda pomičnih prosjeka je statistički alat s kojim možete riješiti razne vrste problema. Naročito se često koristi u prognoziranju. U Excelu možete koristiti ovaj alat za rješavanje brojnih problema. Pogledajmo kako se koristi pomični prosjek u Excelu.

Pomična prosječna aplikacija

Značenje ove metode je u tome što se uz pomoć nje apsolutne dinamičke vrijednosti odabranog niza mijenjaju u aritmetičke srednje vrijednosti za određeni period glađenjem podataka. Ovaj alat se koristi za ekonomske proračune, predviđanje, u procesu trgovanja na berzi itd. Korištenje Excel-ove metode pokretnih prosjeka najbolje je obaviti pomoću moćnog alata za statističku obradu podataka Paket analize. Možete koristiti i ugrađenu Excel funkciju za istu svrhu. PROSJEČNO.

Metoda 1: Paket za analizu

Paket analize je Excel dodatak koji je podrazumevano onemogućen. Stoga prije svega to trebate omogućiti.

1. Pomicanje na karticu Datoteka. Kliknite na artikl. "Opcije".



2. U prozoru s parametrima koji se otvori idite na odjeljak "Dodaci". Na dnu prozora u kutiji "Uprava" parametar mora biti postavljen Excel dodaci. Kliknite na dugme Idi na.



3. Ulazimo u prozor dodataka. Označite polje pored Paket za analizu i kliknite na dugme "OK".

Nakon ove akcije paket "Analiza podataka" aktivirana i na traci na kartici se pojavio odgovarajući gumb "Podaci".

Sada pogledajmo kako možete izravno koristiti značajke paketa. Analiza podataka za metodu pokretnih prosjeka. Napravimo prognozu za dvanaesti mjesec na osnovu podataka o prihodima kompanije za 11 prethodnih razdoblja. Da bismo to učinili, koristićemo tablicu ispunjenu podacima, kao i alate Paket analize.

1. Idite na karticu "Podaci" i kliknite na dugme "Analiza podataka", koja se postavlja na vrpcu alata u bloku "Analiza".



2. Popis alata koji su dostupni u Paket analize. Izaberite ime Pomični prosjek i kliknite na dugme "OK".



3. Pokrenut je prozor za unos podataka za predviđanje prosječnih kretanja.

   U polju Interval unosa navedite adresu raspona u kojem se nalazi mjesečni iznos prihoda bez ćelije u kojoj se podaci trebaju izračunati.

   U polju Interval Trebali biste odrediti interval za obradu vrijednosti metodom izravnavanja. Prvo postavimo vrijednost izravnavanja na tri mjeseca, pa stoga unesite broj "3".

   U polju "Izlazni interval" na listu morate odrediti proizvoljni prazan raspon na kojem će se nakon obrade prikazivati ​​podaci, a koji bi trebao biti jedna ćelija veća od ulaznog intervala.

   Označite i okvir pored parametra. "Standardne greške".

   Ako je potrebno, možete i potvrditi potvrdni okvir pored "Grafički izlaz" za vizuelnu demonstraciju, mada u našem slučaju to nije potrebno.

   Nakon što su sva podešavanja izvršena, kliknite na gumb "OK".



4. Program prikazuje rezultat obrade.



5. Sada ćemo obaviti glađenje tokom dva mjeseca kako bismo otkrili koji je rezultat tačniji. U ove svrhe ponovo pokrenite alat. Pomični prosjek Paket analize.

   U polju Interval unosa ostavljamo iste vrijednosti kao u prethodnom slučaju.

   U polju Interval stavi broj "2".

   U polju "Izlazni interval" odredite adresu novog praznog raspona koji bi, opet, trebao biti jedna ćelija veći od ulaznog intervala.

   Preostala podešavanja ostaju nepromenjena. Nakon toga kliknite na gumb "OK".



6. Nakon toga program izračunava i prikazuje rezultat na ekranu. Da bismo odredili koji je od dva modela tačniji, moramo usporediti standardne pogreške. Što je ovaj pokazatelj manji, to je veća vjerovatnoća tačnosti rezultata. Kao što možete vidjeti, za sve vrijednosti, standardna pogreška izračunavanja dvomjesečnog valjanja manja je od istog pokazatelja za 3 mjeseca. Stoga se predviđena vrijednost za decembar može smatrati vrijednošću izračunatom kliznom metodom za posljednje razdoblje. U našem slučaju ova vrijednost je 990,4 hiljade rubalja.

Metoda 2: upotreba AVERAGE funkcije

U Excelu postoji još jedan način da se primijeni metoda pomičnih prosjeka. Da biste ga koristili, morate primijeniti brojne standardne programske funkcije, od kojih je osnovna naša svrha PROSJEČNO. Kao primjer, upotrijebit ćemo istu tablicu prihoda preduzeća kao u prvom slučaju.

Kao i prošli put, trebat ćemo stvoriti uglađen vremenski niz. Ali, ovaj put, akcije neće biti tako automatizirane. Trebali biste izračunati prosjek za svaka dva, a zatim tri mjeseca kako biste mogli da uporedite rezultate.

Prije svega pomoću funkcije izračunavamo prosječne vrijednosti za prethodna dva razdoblja PROSJEČNO. To možemo učiniti tek u martu, jer za kasnije datume dolazi do loma vrijednosti.

1. Odaberite ćeliju u praznom stupcu zaredom za mart. Zatim kliknite na ikonu "Umetanje funkcije"koja se postavlja u blizinu trake formule.



2. Prozor je aktiviran Čarobnjaci za funkcije. U kategoriji "Statistički" tražeći smisao SRZNACH, odaberite ga i kliknite na gumb "OK".



3. Pokreće se prozor argumenata operatora PROSJEČNO. Sintaksa je sljedeća:

   = PROSJEČNO (broj1; broj2; ...)

   Potreban je samo jedan argument.

   U našem slučaju na terenu "Broj1" moramo pružiti vezu prema rasponu u kojem su naznačeni prihodi za prethodna dva razdoblja (januar i februar). Postavite kursor u polje i odaberite odgovarajuće ćelije na listu u stupcu Prihod. Nakon toga kliknite na gumb "OK".



4. Kao što možete vidjeti, rezultat izračuna prosječne vrijednosti za prethodna dva razdoblja prikazan je u ćeliji. Da bismo izveli slične proračune za sve ostale mjesece razdoblja, moramo ovu formulu kopirati u druge ćelije. Da bismo to učinili, postajemo kursor u donjem desnom uglu ćelije koji sadrži funkciju. Kursor se pretvara u marker ispunjavanja, koji izgleda kao križ. Držite lijevu tipku miša i povucite je do samog kraja stupca.



5. Dobijamo izračun rezultata prosječne vrijednosti za prethodna dva mjeseca prije kraja godine.



6. Sada odaberite ćeliju u sljedećem praznom stupcu u redu za april. Nazovite prozor argumenta funkcije PROSJEČNO na isti način kao što je prethodno opisano. U polju "Broj1" upišite koordinate ćelija u stupac Prihod Januar - mart. Zatim kliknite na gumb "OK".



7. Kopirajte formulu pomoću markera za punjenje u ćelije tabele ispod.



8. Dakle, izračunali smo vrijednosti. Sada, kao i u prethodnom vremenu, morat ćemo saznati koja je analiza bolja: glađenjem na 2 ili 3 mjeseca. Da biste to učinili, izračunajte standardno odstupanje i neke druge pokazatelje. Prvo izračunavamo apsolutno odstupanje pomoću standardne Excel funkcije ABS, koji umesto pozitivnih ili negativnih brojeva vraća svoj modul. Ova vrijednost bit će jednaka razlici između pokazatelja stvarnog prihoda za odabrani mjesec i predviđenog. Postavite kursor na sljedeći prazan stupac u redu za maj. Zovemo Čarobnjak za funkcije.



9. U kategoriji "Matematički" odaberite naziv funkcije "ABS". Kliknite na dugme "OK".



10. Pokreće se prozor argumenta funkcije ABS. U jednom polju "Broj" navesti razliku između sadržaja ćelija u stupcima Prihod i 2 meseca za maj. Zatim kliknite na gumb "OK".



11. Koristite marker za unos, kopirajte ovu formulu u sve redove tablice do novembra.



12. Izračunavamo prosječnu vrijednost apsolutnog odstupanja za čitavo razdoblje pomoću funkcije koju već poznajemo PROSJEČNO.



13. Izvodimo sličan postupak kako bismo izračunali apsolutno odstupanje za pokretni u 3 mjeseca. Prvo primijenite funkciju ABS. Samo ovaj put razmotrimo razliku između sadržaja ćelija sa stvarnim dohotkom i planiranog, izračunatog metodom pomičnog prosjeka za 3 mjeseca.



14. Zatim izračunavamo prosječnu vrijednost svih podataka o apsolutnim odstupanjima pomoću funkcije PROSJEČNO.



15. Sljedeći korak je izračunavanje relativnog odstupanja. Ona je jednaka omjeru apsolutnog odstupanja prema stvarnom pokazatelju. Kako bi izbjegli negativne vrijednosti, ponovo ćemo koristiti mogućnosti koje operator nudi ABS. Ovog puta, koristeći ovu funkciju, dijelimo vrijednost apsolutnog odstupanja kod korištenja metode pomičnog prosjeka za 2 mjeseca stvarnim prihodom za odabrani mjesec.



16. Ali relativno odstupanje obično se prikazuje u postotnom obliku. Stoga odaberite odgovarajući raspon na listu, idite na karticu "Početna"gdje u okviru s alatima "Broj" u specijalnom polju oblikovanja postavljamo format postotka. Nakon toga, rezultat izračuna relativnog odstupanja prikazuje se u postocima.



17. Izvodimo sličnu operaciju za izračunavanje relativnog odstupanja s podacima koristeći izravnavanje za 3 mjeseca. Samo u ovom slučaju za izračunavanje kao dividendu koristimo drugi stupac tabele, koji imamo i naziv "Abs. Isključeno (3m)". Zatim numeričke vrijednosti prevedemo u postotni oblik.



18. Nakon toga izračunavamo prosječne vrijednosti za oba stupca s relativnim odstupanjem, kao i prije korištenja funkcije PROSJEČNO. Budući da kao argumente funkciji uzimamo postotne vrijednosti, ne moramo vršiti dodatnu konverziju. Operator izlaza daje rezultat već u postotnom formatu.



19. Sada dolazimo do izračuna standardnog odstupanja. Ovaj indikator omogućit će nam da izravno usporedimo kvalitetu računanja kada koristimo izglađivanje za dva i tri mjeseca. U našem slučaju, standardna devijacija bit će jednaka kvadratnom korijenu zbroja kvadrata razlika u stvarnom prihodu i pokretnog prosjeka podijeljenog s brojem mjeseci. Da bismo mogli izračunati u programu, moramo koristiti brojne funkcije, posebno KORAK, SAŽETAK i RAČUN. Na primjer, za proračun srednjeg kvadratnog odstupanja kod korištenja linije za izravnavanje u dva mjeseca u maju, u našem slučaju će se koristiti sljedeća formula:

    = KORAK (SAŽETAK (B6: B12; C6: C12) / COUNT (B6: B12))

    Kopirajte ga u druge ćelije u stupcu s izračunavanjem standardnog odstupanja pomoću markera ispunjavanja.



20. Slična operacija izračunavanja standardnog odstupanja izvodi se za pomični prosjek 3 mjeseca.



21. Nakon toga izračunavamo prosječnu vrijednost za cijelo razdoblje za oba ova pokazatelja, primjenjujući funkciju PROSJEČNO.



22. Upoređujući izračune pomoću metode pomičnog prosjeka s izglađivanjem na 2 i 3 mjeseca za pokazatelje poput apsolutne devijacije, relativne devijacije i standardne devijacije, možemo sa sigurnošću reći da izglađivanje za dva mjeseca daje pouzdanije rezultate od primjene izglađivanja tri mjeseca. O tome svjedoči činjenica da su gornji pokazatelji za dvomjesečni pomični prosjek manji nego za tromjesečni.



23. Tako će predviđeni pokazatelj prihoda kompanije za decembar biti 990,4 hiljade rubalja. Kao što vidite, ova se vrijednost u potpunosti podudara s onom koju smo dobili izračunavanjem pomoću alata Paket analize.

Lekcija: Čarobnjak za funkcije u Excelu

Proračun smo izračunali pomoću metode pomičnih prosjeka na dva načina. Kao što vidite, ovaj postupak je mnogo lakše izvesti pomoću alata. Paket analize. Međutim, neki korisnici ne vjeruju uvijek automatskom proračunu i radije koriste funkciju za izračun. PROSJEČNO i povezane operatore radi provjere najpouzdanije opcije. Iako bi, ako je sve izvedeno ispravno, ishod proračuna trebao biti potpuno isti.