Excel

Gibanje povprečne metode v Microsoft Excelu

Metoda gibanja je statistično orodje, s katerim lahko rešite različne naloge. Zlasti se pogosto uporablja pri napovedovanju. V Excelu lahko to orodje uporabite tudi za reševanje številnih nalog. Poglejmo, kako se giblje povprečje uporablja v Excelu.

Uporaba gibajočega se povprečja

Pomen te metode je, da s svojo pomočjo spremenimo absolutne dinamične vrednosti izbrane serije na povprečno aritmetiko v določenem obdobju z izravnavo podatkov. To orodje se uporablja za gospodarske izračune, napovedovanje, v procesu trgovanja na borzi itd. Uporaba metode Moving Average v Excelu je najbolje narediti s pomočjo zmogljivega orodja za obdelavo statističnih podatkov, imenovanega Analysis Pack . Poleg tega lahko za isti namen uporabite vgrajeno funkcijo Excel AVERAGE .

Metoda 1: paket analize

Paket analize je dodatek Excel, ki je privzeto onemogočen. Zato ga je treba najprej vključiti.

  1. Premaknemo se na kartico »Datoteka «. Kliknite na "Možnosti" .

    2. Pojdite na možnosti v programu Microsoft Excel

  2. V oknu, ki se odpre, odprite razdelek »Dodatki« . Na dnu okna morate v polju »Upravljanje« nastaviti parameter » Dodate vrednosti za Excel « . Kliknite gumb »Pojdi« .

    3. Odprite dodatke v programu Microsoft Excel

  3. Pojdimo v okno dodatkov. Označite polje poleg "Paket analize" in kliknite gumb "V redu" .

Dodatno okno v programu Microsoft Excel

Po tem dejanju se aktivira paket »Analiza podatkov« in na traku se na ustreznem gumbu na zavihku »Podatki« prikaže.

In zdaj si oglejmo, kako lahko neposredno uporabite zmožnosti analize podatkov podatkov za metodo, ki se giblje v povprečju. Naredimo napoved za dvanajsti mesec, ki temelji na podatkih o dohodku družbe za 11 prejšnjih obdobij. Če želite to narediti, uporabimo tabelo, ki jo zapolnjuje podatke, in orodja za orodje za analizo .

  1. Pojdite na kartico »Podatki « in kliknite gumb »Analiza podatkov« , ki se nahaja na orodnem pasu v bloku »Analiza« .

    2. Odprite orodja za analizo podatkov v programu Microsoft Excel

  2. Odpre se seznam orodij, ki so na voljo v paketu Analiza . Izberite med njimi ime "premično povprečje" in kliknite gumb "V redu" .

    3. Seznam orodij za analizo v programu Microsoft Excel

  3. Začelo se bo okno za vnašanje podatkov za napovedovanje s pomočjo metode s hitrim povprečjem.

    V polju »Vnos intervala« navedite naslov območja, v katerem se mesečni prihodek izračuna brez celice, katere podatke je treba izračunati.

    V polju "Interval" določite interval za obdelavo vrednosti z uporabo metode za preprečevanje poravnave. Prvič, določimo vrednost anti-aliasing na tri mesece, zato vnesemo številko »3« .

    V polju "Izhodni interval" morate navesti poljuben prazen obseg na listu, kjer bodo podatki po obdelavi prikazani, kar mora biti ena celica večja od vhodnega intervala.

    Preverite tudi polje »Standard error« .

    Če je potrebno, lahko za vizualno demonstracijo potrdite tudi polje »Izhodni graf« , čeprav v našem primeru to ni potrebno.

    Ko so vse nastavitve opravljene, kliknite gumb "V redu" .

    4. Okno orodja za analizo podatkov Moving Average v programu Microsoft Excel

  4. Program prikaže rezultat obdelave.

    5. Rezultat obdelave glajenja za 3 mesece v programu Microsoft Excel

  5. Sedaj opravite izravnavo za obdobje dveh mesecev, da ugotovite, kateri rezultat je pravilnejši. Za te namene spet zaženemo orodje "Moving Average" v paketu analize .

    V polju »Vhodni interval« zapišemo enake vrednosti kot v prejšnjem primeru.

    V polju "Interval" smo postavili številko "2" .

    V polju "Izhodni interval" podajamo naslov novega praznega območja, ki mora biti znova ena celica večja od vhodnega intervala.

    Preostale nastavitve so enake. Nato kliknite gumb "OK" .

    6. Okno orodja za analizo podatkov Moving Average v programu Microsoft Excel

  6. Po tem program izračuna in prikaže rezultat na zaslonu. Da bi ugotovili, kateri od obeh modelov je natančnejši, moramo primerjati standardne napake. Manjši je naveden indeks, večja je verjetnost točnosti dobljenega rezultata. Kot lahko vidite, je pri vseh vrednostih standardna napaka pri izračunu dvomesečnega premika manj kot isti kazalec za 3 mesece. Tako se lahko predvidena vrednost za december šteje za vrednost, izračunano z metodo drsenja za zadnje obdobje. V našem primeru je ta vrednost 990,4 tisoč rubljev.

Rezultat obdelave glajenja za 2 meseca v programu Microsoft Excel

2. način: Uporabite funkcijo AVERAGE

V Excelu je na voljo drug način, kako uporabiti metodo gibanja povprečja. Če ga želite uporabiti, morate uporabiti številne standardne funkcije programa, katerih osnovna je za naš namen POVPRAŠEVANJE . Na primer, bomo uporabili isto tabelo prihodkov družbe, kot v prvem primeru.

Kot v zadnjem času, moramo ustvariti zglajeno časovno vrsto. Toda tokrat dejanja ne bodo tako avtomatizirana. Če želite primerjati rezultate, morate izračunati povprečno vrednost za vsaka dva in nato tri mesece.

Najprej izračunamo povprečne vrednosti za prejšnja obdobja z uporabo funkcije AVERAGE . To lahko storimo šele od marca, saj za poznejše datume pride do preloma vrednosti.

  1. Izberite celico v praznem stolpcu v vrstici za marec. Nato kliknite ikono »Vstavi funkcijo« , ki se nahaja v bližini linije s formulo.

    2. Odprite čarovnika za funkcije v programu Microsoft Excel

  2. Vklopljeno je okno Čarovnikov . V kategoriji "Statistični podatki" iščemo vrednost "AVERAGE" , jo izberite in kliknite gumb "OK" .

    3. Skok na argumente funkcije AVERAGE v programu Microsoft Excel

  3. Začne se okno argumentov stavka AVERAGE . Sintaksa je naslednja:

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

    Potreben je samo en argument.

    V našem primeru moramo na polju "Številka 1" navesti povezavo do območja, kjer je naveden prihodek za prejšnja obdobja (januar in februar). Nastavite kazalko na polju in v stolpcu »Prihodki« izberite ustrezne celice na listu. Nato kliknite gumb "OK" .

    4. argumente funkcije AVERAGE v programu Microsoft Excel

  4. Kot lahko vidite, je bil rezultat izračunavanja povprečja dveh predhodnih obdobij prikazan v celici. Za izvedbo takšnih izračunov za vse ostale mesece v obdobju, moramo to formulo kopirati v druge celice. Če želite to narediti, postanemo kazalec v spodnjem desnem kotu celice, ki vsebuje funkcijo. Kazalec se pretvori v oznako za polnjenje, ki je videti kot križec. Pritegnite levi gumb miške in ga povlecite navzdol do samega konca stolpca.

    5. Polnilo v programu Microsoft Excel

  5. Dobimo izračun rezultatov povprečja za dva predhodna meseca pred koncem leta.

    6. Povprečje za prejšnje 2 mesece v programu Microsoft Excel

  6. Sedaj izberite celico v naslednjem praznem stolpcu v vrstici za april. Okno argumentov funkcije AVERAGE imenujemo na enak način, kot je opisano prej. V polju »Številka 1« vnesemo koordinate celic v stolpcu »Prihodki« od januarja do marca. Nato kliknite gumb "OK" .

    7. Argumenti za funkcijo AVERAGE za 3 mesece v programu Microsoft Excel

  7. Z uporabo označevalnika polnjenja kopirajte formulo v celice spodnje tabele.

    8. Uporaba polnila v Microsoft Excelu

  8. Torej smo izračunali vrednosti. Zdaj, kot v prejšnjem času, bomo morali ugotoviti, katere vrste analize je bolje: z glajenjem 2 ali 3 mesece. Da bi to naredili, izračunajte srednji kvadratni odklon in nekatere druge kazalnike. Najprej izračunamo absolutno odstopanje z uporabo standardne funkcije Excel ABS , ki namesto pozitivnih ali negativnih številk vrne svoj modul. Ta vrednost bo enaka razliki med dejansko vrednostjo prihodka za izbrani mesec in napovedano. Kazalko postavite na naslednji prazen stolpec v vrstici za maj. Pokličite čarovnika za funkcije .

    9. Vstavi funkcijo v programu Microsoft Excel

  9. V kategoriji "Matematični" označujemo ime funkcije "ABS" . Kliknite gumb "V redu" .

    10. Prehod na argumente funkcije ABS v programu Microsoft Excel

  10. Odpre se okno argumentov funkcij ABS . V enotnem polju "Številka" navedemo razliko med vsebino celic v stolpcih "Prihodki" in "2 meseci" za maj. Nato kliknite gumb "OK" .

    11. Argumenti funkcije ABS v programu Microsoft Excel

  11. Z uporabo polnilnika kopirajte to formulo v vse vrstice tabele do vključno novembra.

    12. Absolutna odstopanja v programu Microsoft Excel

  12. Izračunamo povprečno vrednost absolutnega odstopanja za celotno obdobje s pomočjo znane funkcije AVERAGE .

    13. Povprečna vrednost absolutnega odstopanja v programu Microsoft Excel

  13. Izvajamo podoben postopek, da izračunamo absolutni odklon premikajočega se v 3 mesecih. Najprej uporabite funkcijo ABS . Le tokrat upoštevamo razliko med vsebino celic z dejanskim dohodkom in načrtovanimi, izračunane s povprečno metodo gibanja za 3 mesece.

    14. Absolutna odstopanja za 3 mesece v programu Microsoft Excel

  14. Nato izračunajte povprečje vseh absolutnih odstopanj s funkcijo AVERAGE .

    15. Povprečna vrednost absolutnega odstopanja za 3 mesece v programu Microsoft Excel

  15. Naslednji korak je izračun relativnega odstopanja. To je enako razmerju absolutnega odstopanja do dejanskega kazalnika. Da bi preprečili negativne vrednosti, ponovno uporabimo priložnosti, ki jih ponuja operater ABS . Tokrat z uporabo te funkcije delimo absolutno deviacijsko vrednost z uporabo metode gibanja povprečne vrednosti za 2 meseca za dejanski dohodek za izbrani mesec.

    16. Relativna zavrnitev v programu Microsoft Excel

  16. Toda relativno odstopanje je navadno prikazano kot odstotek. Zato izberite ustrezno območje na listu, pojdite na zavihek "Domov" , kjer v polju "Številka" v polju za posebne oblike nastavite odstotkovni format. Po tem se izračuna izračuna relativnega odstopanja v odstotkih.

    17. Spremenite obliko v programu Microsoft Excel

  17. Podobno operacijo za izračun relativnega odstopanja opravimo s podatki z uporabo glajenja za 3 mesece. Samo v tem primeru, da izračunamo kot deljivo, uporabimo drug stolpec tabele, ki jo imenujemo »Abs. off (3m) " . Nato številske vrednosti prevedemo v odstotke.

    18. Relativni odklon za drsenje v dveh mesecih v programu Microsoft Excel

  18. Po tem izračunajte povprečne vrednosti obeh stolpcev z relativnim odklonom, tako kot prej uporabite funkcijo AVERAGE . Ker vrednosti za odstotke upoštevamo kot argumente za funkcijo, jih ni treba pretvoriti. Operater izvede rezultat v obliki odstotka.

    19. Povprečne vrednosti za relativno odstopanje v programu Microsoft Excel

  19. Zdaj smo prišli do izračuna srednjega kvadratnega odstopanja. Ta indikator nam bo omogočil neposredno primerjavo kakovosti izračuna pri uporabi anti-aliasing za dva in tri mesece. V našem primeru bo standardni odklon enak kvadratnemu korenu vsote kvadratov razlik v dejanskem prihodku in gibajočem povprečju, deljenem s številom mesecev. Za izračun v programu moramo uporabiti več funkcij, še posebej ROOT , SUMMKVRAZN in ACCOUNT . Na primer, če želite izračunati srednji kvadratni odklon pri uporabi linije za izravnavo za dva meseca meseca maja, se v našem primeru uporabi naslednja formula:

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

    Kopirajte ga v druge celice v stolpcu z izračunom povprečnega kvadratnega odklona s pomočjo označevalnika polnila.

    20. Izračun standardnega odklona v programu Microsoft Excel

  20. Podobno operacijo za izračun srednjega kvadratnega odklona se izvede za gibalno povprečje 3 mesece.

    21. Izračun standardnega odklona gibajočega povprečja za 3 mesece v programu Microsoft Excel

  21. Po tem izračunamo povprečno vrednost celotnega obdobja za oba kazalca, pri čemer uporabimo funkcijo AVERAGE .

    22. Sredina standardnega odklona v programu Microsoft Excel

  22. S primerjanjem izračunih z metodo premikajočih se povprečij z glajenjem v 2 in 3 mesecih za takšne indikatorje kot absolutno odstopanje, relativno odstopanje in povprečno kvadratno odstopanje, lahko z gotovostjo povemo, da glajenje za dva meseca daje bolj zanesljive rezultate kot z uporabo anti-aliasinga za tri mesece. To każe dejstvo, da so zgornji kazalniki za dvomesećno drsećo povprećje manj kot tri mesece.

    23. Merjenje zemljevidov v programu Microsoft Excel

  23. Tako bo predvideni prihodek podjetja za december znašal 990,4 tisoč rubljev. Kot lahko vidite, ta vrednost povsem sovpada s tistim, ki smo ga dobili z izračunom z orodji Analiza paketov .

Napovedani prihodki v programu Microsoft Excel

Lekcija: Čarovnik za funkcije v Excelu

Napoved smo izračunali z metodo gibajoče se povprečne vrednosti na dva načina. Kot lahko vidite, je ta postopek veliko lažje opraviti z orodji Paketa analize . Kljub temu nekateri uporabniki ne zaupajo avtomatskemu izračunu in raje uporabljajo funkcijo AVERAGE in spremljajoče operaterje za izračun, da preverijo najbolj zanesljivo različico. Čeprav, če se vse naredi pravilno, mora biti rezultat izračuna na izhodu popolnoma enak.