Premještanje prosječne metode u Microsoft Excel

Pomična prosječna metoda je statistički alat pomoću kojeg možete riješiti različite zadatke. Konkretno, često se koristi u predviđanju. U programu Excel možete koristiti ovaj alat za rješavanje brojnih zadataka. Pogledajmo kako se pomični prosjek upotrebljava u programu Excel.

Primjena kretanja prosjeka

Značenje ove metode je da pomoću njegove pomoći postoji promjena apsolutnih dinamičkih vrijednosti odabranih serija do prosječne aritmetičke vrijednosti tijekom određenog razdoblja izglađivanjem podataka. Ovaj alat koristi se za ekonomske izračune, predviđanja, u postupku trgovanja na burzi itd. Primjenom Moving Average metode u programu Excel najbolje je izvršiti uz pomoć snažnog alata za obradu podataka koji se zove Analysis Pack . Osim toga, za istu svrhu možete upotrijebiti ugrađenu funkciju Excel AVERAGE .

Metoda 1: Paket analize

Analiza je dodatak za program Excel koji je prema zadanim postavkama onemogućen. Stoga je prije svega potrebno uključiti ga.

1. Prebacimo se na karticu "Datoteka" . Kliknite stavku "Opcije" .
2. U prozoru koji se otvori idite na odjeljak "Dodaci" . Pri dnu prozora parametar " Excel Add-ins" trebao bi biti postavljen u polje "Upravljanje" . Kliknite gumb "Idi" .
3. Ušli smo u prozor dodataka. Označite okvir pored "Analiza paketa" i kliknite gumb "U redu" .

Nakon ove akcije aktivira se paket "Analiza podataka" , a odgovarajući gumb pojavljuje se na vrpci na kartici "Podaci" .

I sada pogledajmo kako možete izravno koristiti mogućnosti analize podataka za podatke za pomični prosječni način. Napravimo predviđanje za dvanaesti mjesec na temelju podataka o prihodima tvrtke za prethodnih 11 razdoblja. Da bismo to učinili, koristimo tablicu popunjenu podacima, kao i alate za alat za analizu .

1. Idite na karticu "Podaci" i kliknite gumb "Analiza podataka" koji se nalazi na alatnom pojasu u bloku "Analiza" .
2. Otvara se popis alata dostupnih u analizi paketa . Izaberite od njih naziv "prosječni prosjek" i kliknite na gumb "OK" .
3. Pokreće se prozor za unos podataka za prognozu pomoću metode pomične metode.

   U polju "Interval unosa" navedite adresu raspona u kojem se mjesečni prihod izračunava bez ćelije, čiji podaci trebaju biti izračunati.

   U polju "Interval" odredite interval za obradu vrijednosti pomoću metode anti-aliasinga. Najprije postavimo vrijednost anti-aliasing na tri mjeseca, stoga unosimo broj "3" .

   U polju "Izlazni interval" morate navesti proizvoljan prazan raspon na listu na kojem će podaci biti izlazni nakon obrade, što mora biti jedna ćelija veća od intervala ulaza.

   Također provjerite okvir "Standardne pogreške" .

   Ako je potrebno, također možete označiti okvir "Izlazni grafikon" za vizualnu demonstraciju, iako u našem slučaju to nije potrebno.

   Nakon što napravite sve postavke, kliknite gumb "OK" .

4. Program prikazuje rezultat obrade.
5. Sada obavite izglađivanje u trajanju od dva mjeseca kako biste utvrdili koji je rezultat točniji. U tu svrhu ponovno pokrenemo alat "Pokretni prosjek" u analizi paketa .

   U polju "Interval za unos" ostavljamo iste vrijednosti kao u prethodnom slučaju.

   Na polju "Interval" stavili smo broj "2" .

   U polju "Izlazni interval" navodimo adresu novog praznog raspona, koji bi opet trebao biti jedna stanica veća od intervala ulaza.

   Ostatak postavki je isti. Nakon toga kliknite gumb "U redu" .

6. Nakon toga, program izračunava i prikazuje rezultat na zaslonu. Da bismo odredili koji od dva modela točniji, moramo usporediti standardne pogreške. Što je manji dani indeks, to je veća vjerojatnost točnosti dobivenog rezultata. Kao što možete vidjeti, za sve vrijednosti, standardna pogreška u izračunu dva mjeseca kreće manje od istog pokazatelja za 3 mjeseca. Dakle, predviđena vrijednost za prosinac može se smatrati vrijednost izračunatom metodom klizanja za zadnje razdoblje. U našem slučaju, ova vrijednost iznosi 990,4 tisuća rubalja.

Druga metoda: Koristite funkciju PROSTORA

U programu Excel postoji još jedan način primjene metode pomične metode. Da biste ga koristili, trebate primijeniti niz standardnih funkcija programa, od kojih je osnovni jedan od naših ciljeva AVERAGE . Na primjer, koristit ćemo istu tablicu prihoda tvrtke kao u prvom slučaju.

Kao i posljednji put, moramo stvoriti izglađenu vremensku seriju. Ali ovaj put akcije neće biti tako automatizirane. Izračunajte prosječnu vrijednost svake dvije, a zatim tri mjeseca da biste uspoređivali rezultate.

Prije svega, izračunavamo prosječne vrijednosti za dva prethodna razdoblja upotrebom funkcije PROSTORA . To možemo učiniti samo od ožujka, jer za kasnije datume dolazi do prekida vrijednosti.

1. Odaberite ćeliju u praznom stupcu u redu za ožujak. Zatim kliknite ikonu "Umetni funkciju" koja se nalazi pored linije formule.
2. Aktiviran je prozor čarobnjake . U kategoriji "Statistička" tražimo vrijednost "PROSJEK" , odaberite ga i kliknite na gumb "OK" .
3. Pokrenut je prozor s argumentima AVERAGE izjave. Sintaksa je sljedeća:

   =СРЗНАЧ(число1;число2;…)

   Potreban je samo jedan argument.

   U našem slučaju, u polju "Broj 1", moramo navesti vezu s rasponom gdje je naznačeno prihod za dva prethodna razdoblja (siječanj i veljača). Postavite pokazivač u polje i odaberite odgovarajuće ćelije na listi u stupcu "Prihod" . Nakon toga kliknite gumb "U redu" .

4. Kao što vidite, u ćeliji je prikazan rezultat izračuna prosjeka za dva prethodna razdoblja. Da bismo izvršili takve izračune za sve ostale mjesece razdoblja, moramo kopirati ovu formulu u druge stanice. Da bismo to postigli, postajemo pokazivač u donjem desnom kutu ćelije koji sadrži tu funkciju. Kursor se pretvara u oznaku za ispunjavanje, što izgleda kao križ. Pritisnite lijevu tipku miša i povucite je do kraja stupca.
5. Dobivamo izračun rezultata prosjeka za dva prethodna mjeseca prije kraja godine.
6. Sada odaberite ćeliju u sljedećem praznom stupcu u redu za travanj. Pozivamo prozor argumente na AVERAGE funkciju na isti način kao što je ranije opisano. U polju "Broj1" unosimo koordinate ćelija u stupac "Prihod" od siječnja do ožujka. Zatim kliknite gumb "OK" .
7. Pomoću oznake za ispunjavanje kopirajte formulu u ćelije u donjoj tablici.
8. Dakle, izračunali smo vrijednosti. Sada, kao i prethodno, morat ćemo saznati koja je vrsta analize bolja: s izglađivanjem od 2 ili 3 mjeseca. Da biste to učinili, izračunajte srednje kvadratno odstupanje i neki drugi pokazatelji. Prvo, izračunavamo apsolutno odstupanje pomoću standardne ABS funkcije ABS , koja umjesto pozitivnih ili negativnih brojeva vraća svoj modul. Ta će vrijednost biti jednaka razlici između stvarnog iznosa prihoda za odabrani mjesec i predviđenog. Postavite pokazivač na sljedeći prazan stupac u retku za svibanj. Nazovite Čarobnjak za funkcije .
9. U kategoriji "Matematički" označavamo naziv funkcije "ABS" . Kliknite gumb "OK" .
10. Otvori se prozor funkcija ABS argumenata. U jednom polju "Broj" označavamo razliku između sadržaja ćelija u stupcima "Prihodi" i "2 mjeseca" za svibanj. Zatim kliknite gumb "OK" .
11. Koristeći punilo, kopirajte ovu formulu na sve redove tablice do studenog.
12. Izračunavamo prosječnu vrijednost apsolutnog odstupanja za cijelo razdoblje uz pomoć poznate funkcije AVERAGE .
13. Izvršavamo sličan postupak kako bismo izračunali apsolutno odstupanje za kreće u 3 mjeseca. Prvo primijenite ABS funkciju. Samo ovaj put uzmemo u obzir razliku između sadržaja ćelija s realnim dohotkom i planiranog, izračunatog pomoću pomične prosječne metode za 3 mjeseca.
14. Zatim izračunajte prosjek svih apsolutnih odstupanja pomoću funkcije AVERAGE .
15. Sljedeći korak je izračunavanje relativnog odstupanja. To je jednak omjeru apsolutnog odstupanja od stvarnog pokazatelja. Da bismo izbjegli negativne vrijednosti, ponovno iskoristimo mogućnosti koje pruža ABS operater. Ovaj put pomoću ove funkcije podijelimo vrijednost apsolutne devijacije pomoću metode pomične metode za 2 mjeseca za stvarni prihod za odabrani mjesec.
16. No, relativno odstupanje se obično prikazuje kao postotak. Stoga odaberite odgovarajući raspon na listu, idite na karticu "Početna" , gdje u okviru alata "Broj" u polju posebnog formata postavite format postotka. Nakon toga, rezultat izračuna relativnog odstupanja prikazuje se u postocima.
17. Slična operacija za izračun relativnog odstupanja obavlja se podacima uz upotrebu zaglađivanja za 3 mjeseca. Samo u ovom slučaju da bi se izračunalo kao djeljivo, koristimo drugi stup tablice, koji imamo ime "Abs. off (3m) " . Tada prevodimo numeričke vrijednosti u postotak.
18. Nakon toga izračunajte prosječne vrijednosti za oba stupca s relativnim odstupanjem, kao i prije upotrebe funkcije AVERAGE . Budući da uzmemo postotne vrijednosti kao argumente za neku funkciju, ne moramo ga pretvoriti. Operater izlaže rezultat u postotnom formatu.
19. Sada smo došli do izračuna srednjeg kvadratnog odstupanja. Ovaj pokazatelj će nam omogućiti da izravno uspoređujemo kvalitetu izračuna kada koristimo anti-aliasing za dva i tri mjeseca. U našem slučaju, standardno odstupanje bit će jednako kvadratnom korijenu zbroja kvadrata razlika u stvarnom prihodu i kretanju prosjeka podijeljenog brojem mjeseci. Da bismo napravili izračun u programu, moramo koristiti brojne funkcije, posebno ROOT , SUMMKVRAZN i ACCOUNT . Na primjer, za izračunavanje srednjeg kvadratnog odstupanja kada koristimo liniju za izravnavanje za dva mjeseca u svibnju, sljedeća formula će se koristiti u našem slučaju:

    =КОРЕНЬ(СУММКВРАЗН(B6:B12;C6:C12)/СЧЁТ(B6:B12))

    Kopirajte ga na druge ćelije u stupcu uz izračun srednjeg kvadratnog odstupanja pomoću oznake za ispunjavanje.

20. Slična operacija za izračunavanje srednjeg kvadratnog odstupanja provodi se za pomični prosjek 3 mjeseca.
21. Nakon toga izračunavamo prosječnu vrijednost za cijelo razdoblje za oba ova indikatora primjenom funkcije PROSJEČAN .
22. Usporedbom proračuna pomoću pomične prosječne metode i izglađivanjem na 2 i 3 mjeseca za takve pokazatelje kao apsolutno odstupanje, relativno odstupanje i devijacija srednje vrijednosti četvrtog korijena, sa sigurnošću možemo sa sigurnošću reći da izglađivanje za dva mjeseca daje pouzdane rezultate od korištenja anti-aliasinga kroz tri mjeseca. To je naznačeno činjenicom da su gore navedeni pokazatelji za dva mjeseca kretanja prosjeka manje od tri mjeseca.
23. Tako će predviđeni prihod tvrtke za prosinac iznositi 990,4 tisuća rubalja. Kao što možete vidjeti, ova se vrijednost u potpunosti podudara s onim koje smo dobili izračunavanjima pomoću alata Analiza paketa .

Pouka: Čarobnjak za funkcije u Excelu

Izračun smo izračunali pomoću metode pomične metode na dva načina. Kao što vidite, ovaj je postupak lakše izvesti pomoću alata Analiza paketa . Ipak, neki korisnici ne vjeruju uvijek automatskom izračunu i preferiraju upotrebu funkcije AVERAGE i pratećih operatora za izračune kako bi provjerili najpouzdaniju verziju. Iako, ako je sve ispravno, izlazni izračun trebao bi biti isti na izlazu.

• ← Izračunavanje funkcije Laplace u Microsoft Excelu
• Brisanje linije u programu Microsoft Excel →