nadmašiti

Alati za predviđanje u programu Microsoft Excel

Predviđanje je vrlo važan element u gotovo svim područjima djelatnosti, od gospodarstva do inženjeringa. Postoji velika količina softvera koji se specijalizirao upravo u tom smjeru. Nažalost, svi korisnici ne znaju da uobičajena Excelova proračunska tablica ima u arsenalnim alatima za predviđanje, što, po njihovoj učinkovitosti, nije mnogo niža od profesionalnih programa. Doznajemo što su ti alati i kako predviđati u praksi.

Postupak predviđanja

Cilj bilo kojeg predviđanja je identificirati trenutni trend i odrediti očekivani rezultat s obzirom na predmet koji se studira u nekom trenutku u budućnosti.

1. način: Trendline

Jedna od najpopularnijih vrsta grafičkih predviđanja u Excelu je izrada ekstrapolacije izgradnjom trend linije.

Pokušajmo predvidjeti dobit poduzeća za 3 godine na temelju podataka o ovom pokazatelju za prethodnih 12 godina.

  1. Pripisamo ovisnost na temelju tabelarnih podataka koji se sastoje od argumenata i vrijednosti funkcije. Da biste to učinili, odaberite područje tablice, a zatim na kartici "Umetni" kliknite ikonu željene vrste dijagrama u bloku "Dijagrami" . Zatim odaberite odgovarajuću vrstu za određenu situaciju. Najbolje je odabrati bitmapa. Možete odabrati drugi prikaz, ali kako biste ispravno prikazali podatke, morat ćete izvršiti uređivanje, posebno uklonite liniju argumenata i odaberite drugu ljestvicu za vodoravnu os.

    2. Ucrtavanje u programu Microsoft Excel

  2. Sada moramo izgraditi trend linije. Desnom tipkom miša kliknite bilo koju od točaka grafikona. U aktiviranom kontekstnom izborniku odaberite "Add trend line" .

    3. Dodavanje linije trendova u Microsoft Excel

  3. Otvara se prozor za formatiranje trendline. Može odabrati jednu od šest vrsta aproksimacije:
    • Linearno ;
    • Logaritamski ;
    • Eksponencijalni ;
    • Stupanj moći ;
    • Polinom ;
    • Linearno filtriranje .

    Prvo odaberite linearnu aproksimaciju.

    U polju "Predviđanja" u polju "Proslijedi", postavite broj "3.0" , budući da trebamo napraviti prognozu za tri godine unaprijed. Osim toga, možete provjeriti postavke "Prikaži jednadžbu u dijagramu" i "Postavite vrijednost točnosti približavanja (R ^ 2) na dijagramu" . Posljednji pokazatelj odražava kvalitetu trendline. Nakon podešavanja kliknite gumb "Zatvori" .

    4. Parametri trendova u programu Microsoft Excel

  4. Trendska linija je izgrađena i na njemu možemo odrediti približan iznos dobiti u tri godine. Kao što vidimo, do tog vremena trebalo bi prelaziti 4500 tisuća rubalja. Koeficijent R2 , kao što je već spomenuto, odražava kvalitetu trendline. U našem slučaju R2 je 0,89 . Što je veći koeficijent, to je veća pouzdanost linije. Maksimalna vrijednost može biti jednaka 1 . Općenito je prihvaćeno da je koeficijent iznad 0,85 trend linija pouzdan.

    5. Trendska linija izrađena je u Microsoft Excelu

  5. Ako niste zadovoljni razinom pouzdanosti, možete se vratiti na prozor formata trend linije i odabrati bilo koju drugu vrstu aproksimacije. Možete isprobati sve dostupne opcije kako biste pronašli najtočnije.

    Odabir druge vrste aproksimacije u programu Microsoft Excel

    Valja napomenuti da je učinkovita prognoza pomoću ekstrapolacije kroz trend linija može biti ako razdoblje predviđanja ne prelazi 30% analizirane baze razdoblja. To jest, kada se analizira razdoblje od 12 godina, ne možemo napraviti učinkovitu prognozu za više od 3-4 godine. No, čak iu ovom slučaju, bit će relativno pouzdano, ako za to vrijeme neće biti više sile, ili suprotno iznimno povoljne okolnosti koje nisu bile u ranijim razdobljima.

Pouka: Kako izgraditi trend linije u programu Excel

Način 2: PREDIZANJE RADA

Ekstrapolacija za tablične podatke može se izvršiti kroz standardnu Exxel PRESCASE funkciju. Ovaj argument pripada kategoriji statističkih alata i ima sljedeću sintaksu:

=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

"X" je argument čija funkcijska vrijednost treba utvrditi. U našem slučaju, argumentacija će poslužiti kao godina predviđanja.

"Poznate vrijednosti y" su osnova poznatih vrijednosti funkcije. U našem slučaju, uloga dobiti za prethodna razdoblja je u njezinoj ulozi.

"Poznate vrijednosti x" su argumenti kojima odgovaraju poznate vrijednosti funkcije. U svojoj ulozi, godinama smo numerirani, za koje smo prikupljali informacije o dobiti prethodnih godina.

Naravno, vremenska linija ne mora nužno biti argument. Na primjer, može biti temperatura, a funkcionalna vrijednost može biti razina širenja vode kada se zagrijava.

Pri izračunavanju ove metode koristi se metoda linearne regresije.

Pogledajmo nijanse korištenja operatera PRESCASE za određeni primjer. Uzmite istu tablicu. Morat ćemo saznati prognozu dobiti za 2018. godinu.

  1. Odaberite prazan ćeliju na listu na kojem želite ispisati rezultat obrade. Kliknite gumb "Umetni funkciju" .

    2. Idite na čarobnjak za funkcije u programu Microsoft Excel

  2. Otvara se Čarobnjak za funkcije . U kategoriji "Statističko" odaberite naziv "PRESCASE" , a zatim kliknite gumb "OK" .

    3. Prebacivanje na argumente PRESET funkcije u programu Microsoft Excel

  3. Prozor argumenata počinje. U polju "X" određujemo vrijednost argumenta za koji želimo pronaći vrijednost funkcije. U našem slučaju, ovo je 2018. godine. Stoga unosimo unos "2018" . No, bolje je odrediti ovaj pokazatelj u ćeliji na listi, a na polju "X" jednostavno dati vezu s njom. To će u budućnosti omogućiti automatizaciju izračuna i, ako je potrebno, lako mijenjati godinu.

    U polju "Poznate vrijednosti y" označavamo koordinate stupca "Dobit poduzeća" . To možete učiniti postavljanjem kursora u polje, a zatim držite lijevu tipku miša i odabirom odgovarajućeg stupca na listi.

    Slično tome, u polju "Poznate vrijednosti x" unosimo adresu stupca "Godina" s podacima za proteklo razdoblje.

    Nakon što unesete sve podatke, kliknite gumb "OK" .

    4. Argumenti za FORTRESS funkciju u programu Microsoft Excel

  4. Operater izračunava na temelju unesenih podataka i prikazuje rezultat na zaslonu. Za 2018. godinu planira se dobiti oko 4564,7 tisuća rubalja. Na temelju rezultirajuće tablice, možemo grafikon grafikona pomoću alata za grafikon koji su gore diskutirani.

    5. Rezultat PREDICT funkcije u Microsoft Excelu

  5. Ako promijenite godinu u ćeliji koja je upotrijebljena za unos argumenta, rezultat će se promijeniti u skladu s tim, a grafikon će se automatski ažurirati. Na primjer, prema prognozama u 2019, iznos dobiti će biti 4637,8 tisuća rubalja.

Promjena argumenata PRESET funkcije u programu Microsoft Excel

Ali nemojte zaboraviti da, kao u konstrukciji trendline, vremenski interval u predviđenom razdoblju ne bi smio premašiti 30% cjelokupnog razdoblja za koje je baza podataka akumulirana.

Pouka: Ekstrapolacija u programu Excel

3. metoda: TRENDOVI operatera

Za predviđanje možete koristiti još jednu funkciju - TRENDS . Ona također pripada kategoriji statističkih operatora. Njegova sintaksa na više načina podsjeća na sintaksu alata PRESCASE i izgleda ovako:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Kao što možete vidjeti, argumenti "poznate vrijednosti y" i "poznate vrijednosti x" u potpunosti odgovaraju sličnim elementima operatera PRESCASE , a argument "nove vrijednosti x" odgovara argumentu "X" prethodnog alata. Osim toga, TRENDS imaju dodatni argument "Constant" , ali nije obvezan i upotrebljava se samo ako postoje konstantni čimbenici.

Taj se operator najdjelotvornije koristi u prisutnosti linearne zavisnosti funkcije.

Pogledajmo kako će ovaj alat funkcionirati s istim nizom podataka. Da bismo usporedili dobivene rezultate, odredit ćemo točku prognoziranja 2019. godine.

  1. Označavamo ćeliju za izlaz rezultata i pokrenemo čarobnjak za funkcije na uobičajeni način. U kategoriji "Statistička" nalazimo i odaberemo naziv "TRENDOVI" . Kliknite gumb "OK" .

    2. Prijelaz na argumente TREND funkcije u programu Microsoft Excel

  2. Otvara okvir za argument TENDENCY operator. U polju "Poznate vrijednosti y" upisujemo koordinate stupca "Enterprise Profit " na gore opisani način. U polju "Poznate vrijednosti x" unesite adresu stupca "Godina" . U polju "Nove x vrijednosti" upisujemo referencu na ćeliju u kojoj se nalazi broj godine na koji želite odrediti predviđanje. U našem slučaju ovo je 2019. Polje "Konstantno" ostaje prazno. Kliknemo na gumb "OK" .

    3. Argumenti za funkciju TREND u programu Microsoft Excel

  3. Operater obrađuje podatke i prikazuje rezultat na zaslonu. Kao što vidite, zbroj predviđene dobiti za 2019, izračunat metodom linearne ovisnosti, kao i prethodnog načina izračuna, čini 4637,8 tisuća rubalja.

Rezultat funkcije TREND u programu Microsoft Excel

Metoda 4: Operator RAST

Još jedna funkcija pomoću koje možete napraviti predviđanja u programu Excel, je operator RAST. Također se odnosi na statističku skupinu alata, ali, za razliku od prethodnih, metoda se ne primjenjuje metodom linearne ovisnosti već eksponencijalnom metodom. Sintaksa ovog alata izgleda ovako:

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Kao što možete vidjeti, argumenti ove funkcije točno ponavljaju argumente operatera TENDENCY , tako da ih drugi put nećemo raspravljati, ali odmah ćemo se obratiti primjeni ovog alata u praksi.

  1. Odaberite izlaznu izlaznu ćeliju i pozovite Čarobnjak za funkcije na uobičajeni način. Na popisu statističkih operatora potražite stavku "RAST" , odaberite ga i kliknite gumb "OK" .

    2. Prebacivanje na argumente funkcije ROST u programu Microsoft Excel

  2. Aktiviran je argumentni prozor gornje funkcije. Unosimo podatke u polja ovog prozora na isti način kao što smo ih unijeli u argument prozoru TENDENCY operatora. Nakon što unesete podatke, kliknite gumb "OK" .

    3. Argumenti za funkciju ROST u programu Microsoft Excel

  3. Rezultat obrade podataka prikazan je na monitoru u prethodno navedenoj ćeliji. Kao što možete vidjeti, ovaj put rezultat je 4682,1 tisuća rubalja. Razlike od rezultata obrade podataka od strane operatora TENDENCY su beznačajne, ali su dostupne. To je zbog činjenice da ovi alati koriste različite metode izračuna: metoda linearne ovisnosti i metoda eksponencijalne ovisnosti.

Rezultat funkcije RAST u Microsoft Excelu

5. način: LINEST operator

LINEST operator koristi metodu linearne aproksimacije. Ne smije se miješati s metodom linearne ovisnosti koju koristi alat TRENDS . Njegova sintaksa je ovo:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Zadnja dva argumenta su neobvezna. S prva dva smo upoznati s prethodnim metodama. Ali vjerojatno ste primijetili da u toj funkciji nema argumenata koji upućuju na nove vrijednosti. Činjenica je da ovaj alat određuje samo promjenu iznosa prihoda po jedinici razdoblja, što u našem slučaju iznosi jednu godinu, no ukupna se vrijednost izračunava zasebno, dodajući da zadnja stvarna vrijednost profita bude rezultat izračuna LINEST operatora pomnožen s brojem godina.

  1. Odabiremo ćeliju u kojoj će se izvršiti proračun i pokrenuti čarobnjak za funkcije. Odaberite naziv "LINEST" u kategoriji "Statistički" i kliknite gumb "OK" .

    2. Idite na argumente LINEST funkcije u programu Microsoft Excel

  2. U polju "Poznate vrijednosti y" , otvorenog prozora argumenta unesite koordinate stupca "Poduzetnička dobit". U polju "Poznate vrijednosti x" unosimo adresu stupca "Godina" . Preostala polja ostaju prazna. Zatim kliknite gumb "OK" .

    3. Argumenti za LINEST funkciju u programu Microsoft Excel

  3. Program izračunava i prikazuje vrijednost linearnog trenda u odabranoj ćeliji.

    4. Rezultat funkcije LINEST u programu Microsoft Excel

  4. Sada moramo saznati vrijednost predviđene dobiti za 2019. Postavite znak "=" na bilo koju praznu ćeliju na listu. Kliknemo na ćeliju koja sadrži stvarni iznos dobiti za prošlu godinu studirao (2016). Stavite znak "+" . Zatim kliknite na ćeliju koja sadrži prethodno izračunati linearni trend. Stavili smo znak "*" . Budući da je između prošle godine studijskog razdoblja (2016.) i godine za koju se predviđa (2019.) razdoblje tri godine, tada u ćeliji postavimo broj "3" . Da biste napravili izračun, kliknite gumb Enter .

Završni izračun LINEST funkcije u programu Microsoft Excel

Kao što možemo vidjeti, predviđena dobit, izračunata po metodi linearne aproksimacije, 2019. godine iznosit će 4614,9 tisuća rubalja.

Metoda 6: operator LGRFPRIBL

Posljednji alat koji ćemo razmotriti bit će LGRF . Ovaj operator obavlja izračune na osnovi metode eksponencijalne aproksimacije. Njegova sintaksa ima sljedeću strukturu:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Kao što možete vidjeti, svi argumenti potpuno ponavljaju odgovarajuće elemente prethodne funkcije. Algoritam za izračunavanje prognoze malo će se promijeniti. Funkcija će izračunati eksponencijalni trend koji će pokazati koliko će puta iznos prihoda za jedno razdoblje promijeniti, to jest, godinu dana. Morat ćemo pronaći razliku u dobiti između posljednjeg razdoblja i prvog planiranog, množenjem brojem planiranih razdoblja (3) i dodavanjem rezultata zbroj posljednjeg stvarnog razdoblja.

  1. Na popisu operatora čarobnjaka funkcija odaberite naziv "LGRFI" . Kliknite gumb "U redu" .

    2. Prijelaz na argumente funkcije LGRFIBRIBL u Microsoft Excelu

  2. Prozor argumenata počinje. U njemu unosimo podatke točno onako kako smo učinili s LINEST funkcijom. Kliknemo na gumb "OK" .

    3. Argumenti funkcije LGRFDRIBL u Microsoft Excelu

  3. Rezultat eksponencijalnog trenda je izračunat i izlaz na određenu ćeliju.

    4. Rezultat LGRF funkcije u programu Microsoft Excel

  4. Stavite "=" u praznu ćeliju. Otvorite zagrade i odaberite ćeliju koja sadrži vrijednost prihoda za posljednje stvarno razdoblje. Stavite znak "*" i odaberite ćeliju koja sadrži eksponencijalni trend. Stavili smo znak minus i ponovno kliknite element na kojem se nalazi prihod od posljednjeg razdoblja. Zatvorimo zagrada i vozimo znakove "* 3 +" bez navodnika. Ponovno kliknite na istu ćeliju koju ste odabrali posljednji put. Da biste izvršili izračun, pritisnite tipku Enter .

Završni izračun funkcije LGRFPRIBL u Microsoft Excelu

Predviđena dobit u 2019, koja je izračunata metodom eksponencijalne približnosti, iznosit će 4.639,2 tisuća rubalja, što se opet ne razlikuje mnogo od rezultata dobivenih u izračunu prethodnih metoda.

Pouka: Ostale statističke funkcije u programu Excel

Naučili smo na koji način je moguće napraviti predviđanja u Excel programu. Grafički, to se može obaviti pomoću linije trenda i analitičkog - pomoću brojnih ugrađenih statističkih funkcija. Kao rezultat obrade identičnih podataka tih operatora, može doći do različitih rezultata. No, to ne iznenađuje, jer svi koriste različite metode izračuna. Ako je fluktuacija mala, sve te opcije, primjenjive na određeni slučaj, mogu se smatrati relativno pouzdanim.