Excel

Orodja napovedovanja v programu Microsoft Excel

Napovedovanje je zelo pomemben element na skoraj vseh področjih dejavnosti, od gospodarstva do inženiringa. Obstaja velika količina programske opreme, ki je prav posebna v tej smeri. Na žalost vsi uporabniki ne vedo, da ima običajna Excelova razpredelnica v svojih arzenalnih orodjih za napovedovanje, ki s svojo učinkovitostjo niso precej slabše od profesionalnih programov. Ugotovimo, kaj so ta orodja in kako v praksi napovedati.

Postopek napovedovanja

Cilj vsake napovedi je ugotoviti trenutni trend in določiti pričakovani rezultat v zvezi s preučevanim objektom v določeni točki v prihodnosti.

Metoda 1: Trendline

Ena izmed najbolj priljubljenih vrst grafičnih napovedi v Excelu je ekstrapolacija, ki se izvaja z izgradnjo linije trendov.

Poskusimo napovedati dobiček podjetja v treh letih na podlagi podatkov o tem kazalniku za preteklih 12 let.

  1. Zasnovamo odvisnost na podlagi tabelarnih podatkov, ki vsebujejo argumente in vrednosti funkcije. Če želite to narediti, izberite področje tabele in nato na kartici »Vstavi« kliknite ikono želene vrste diagrama, ki je v bloku »Diagrami« . Nato izberite ustrezno vrsto za določeno situacijo. Najbolje je, da izberete bitno sliko. Izberete lahko drugačen pogled, vendar pa boste za pravilno prikazovanje podatkov morali opraviti urejanje, še posebej odstranite linijo argumentov in izberite drugo lestvico za vodoravno os.

    2. Plotiranje v Microsoft Excelu

  2. Zdaj moramo zgraditi trendno linijo. Z desno miškino tipko kliknite katero koli točko. V aktiviranem kontekstnem meniju izberite »Dodaj trendno vrstico« .

    3. Dodajanje trendne vrstice v Microsoft Excel

  3. Odpre se okno za oblikovanje linije trenda. Izbere eno izmed šestih vrst približevanja:
    • Linearni ;
    • Logaritmični ;
    • Eksponentni ;
    • Stopnja moči ;
    • Polinom ;
    • Linearno filtriranje .

    Najprej izberite linearno približevanje.

    V nastavitvah "Napoved" v polju "Posreduj v" nastavite številko "3.0" , ker moramo napovedati tri leta naprej. Poleg tega lahko preverite nastavitve »Pokaži enačbo v diagramu« in »Postavite vrednost približne natančnosti (R ^ 2) na diagram« . Zadnji kazalnik odraža kakovost linije trendov. Ko so nastavitve narejene, kliknite gumb "Zapri" .

    4. Trendline parametri v programu Microsoft Excel

  4. Linija trendov je zgrajena in na njej lahko določimo približni znesek dobička v treh letih. Kot bomo videli, do takrat bi moral presegati 4500 tisoč rubljev. Koeficient R2 , kot je že omenjen zgoraj, odraža kakovost linije trendov. V našem primeru R2 znaša 0,89 . Višji je koeficient, večja je zanesljivost linije. Njegova najvišja vrednost je lahko enaka 1 . Na splošno velja, da je s koeficientom nad 0,85 trendna linija zanesljiva.

    5. Linija trendov je zgrajena v programu Microsoft Excel

  5. Če niste zadovoljni s stopnjo zanesljivosti, se lahko vrnete v okno oblikovanja trendne vrstice in izberete katero koli drugo vrsto približevanja. Preizkusite lahko vse razpoložljive možnosti, da najdete najbolj natančne podatke.

    Izbira druge vrste približevanja v programu Microsoft Excel

    Treba je opozoriti, da se lahko učinkovita napoved z uporabo ekstrapolacije skozi linijo trendov, če predvideno obdobje ne presega 30% osnove za analizirano obdobje. To pomeni, da pri analizi dvanajstih let ne moremo narediti učinkovite napovedi za več kot 3-4 leta. Toda tudi v tem primeru bo relativno zanesljivo, če v tem času ne bo prišlo do višje sile ali nasprotno izjemno ugodnih okoliščin, ki niso bile v prejšnjih obdobjih.

Lekcija: Kako zgraditi trendno vrstico v Excelu

2. način: operater PREDMET

Ekstrapolacija tabelarnih podatkov se lahko opravi s standardno funkcijo Exxel PRESCASE . Ta argument spada v kategorijo statističnih orodij in ima naslednjo skladnjo:

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

"X" je argument, katerega vrednost funkcije je treba določiti. V našem primeru bo kot argument utemeljilo leto, ko naj bi se napovedovanje izvajalo.

"Znane vrednosti y" so osnova znanih vrednosti funkcije. V našem primeru je vloga dobička za prejšnja obdobja v njeni vlogi.

"Znane vrednosti x" so argumenti, na katere ustrezajo znane vrednosti funkcije. V svoji vlogi smo oštevilčeni že leta, za katere smo zbrali informacije o dobičku iz preteklih let.

Seveda časovna vrstica ni nujno argument. Na primer, lahko je temperatura, vrednost funkcije pa lahko raven razširitve vode pri segrevanju.

Pri izračunu te metode se uporablja metoda linearne regresije.

Oglejmo si nianse uporabe operaterja PRESCASE za določen primer. Vzemi isto mizo. Ugotoviti je treba, kakšna bo napoved dobička za leto 2018.

  1. Izberite prazno celico na listu, na kateri želite natisniti rezultat obdelave. Kliknite na gumb "Vstavi funkcijo" .

    2. Odprite čarovnika za funkcije v programu Microsoft Excel

  2. Odpre se čarovnik za funkcije . V kategoriji "Statistični podatki" izberite ime "PRESCASE" in kliknite gumb "OK" .

    3. Preklop na argumente funkcije PRESET v programu Microsoft Excel

  3. Začne se okno argumentov. V polju "X" določimo vrednost argumenta, na katerega želimo najti vrednost funkcije. V našem primeru je to 2018. Zato vnesemo vnos "2018" . Ampak bolje je določiti ta indikator v celici na listu, v polju "X" pa samo povezati z njim. To bo v prihodnosti omogočilo avtomatizacijo izračunov in, če je potrebno, enostavno spremeniti leto.

    V polju "Znane vrednosti y" navajemo koordinate stolpca "Dobiček podjetja" . To lahko storite tako, da v kazalcu nastavite kazalko in nato držite levi gumb miške in izberete ustrezen stolpec na listu.

    Podobno v polju »Znane vrednosti x« vpišemo naslov stolpca »Leto« s podatki za preteklo obdobje.

    Ko so vse informacije vnesene, kliknite na gumb "V redu" .

    4. Argumenti za funkcijo FORTRESS v programu Microsoft Excel

  4. Operater izračuna na podlagi vnesenih podatkov in prikaže rezultat na zaslonu. Za leto 2018 je načrtovan dobiček v višini okoli 4564,7 tisoč rubljev. Na podlagi dobljene tabele lahko graf grafiramo z uporabo grafičnih orodij, ki so opisana zgoraj.

    5. Rezultat funkcije PREDICT v programu Microsoft Excel

  5. Če spremenite leto v celico, ki je bila uporabljena za vnos argumenta, se bo rezultat ustrezno spremenil, graf pa bo samodejno posodobljen. Na primer, glede na napovedi leta 2019, bo znesek dobička 4637,8 tisoč rubljev.

Spreminjanje argumenta funkcije PRESET v programu Microsoft Excel

Toda ne pozabite, da časovni interval do predvidenega obdobja, tako kot pri izgradnji linije trendov, ne sme preseči 30% celotnega obdobja, za katerega je bila zbirka podatkov nakopičena.

Lekcija: Ekstrapolacija v Excelu

Metoda 3: TRENDI operaterja

Za napovedovanje lahko uporabite še eno funkcijo - TRENDI . Spada tudi v kategorijo statističnih operaterjev. Njegova sintaksa na več načinov spominja na sintakso orodja PRESCASE in izgleda takole:

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

Kot lahko vidite, argumenti "Znane vrednosti y" in "Znane vrednosti x" v celoti ustrezajo podobnim elementom operaterja PRESCASE , argument "Nove vrednosti x" pa ustreza argumentu "X" prejšnjega orodja. Poleg tega imajo TRENDI dodaten argument "Konstanta" , vendar ni obvezen in se uporablja samo, če obstajajo stalni dejavniki.

Ta operater se najbolj učinkovito uporablja v prisotnosti linearne odvisnosti funkcije.

Oglejmo si, kako bo to orodje delalo z istim nizom podatkov. Za primerjavo dobljenih rezultatov bomo določili točko napovedovanja leta 2019.

  1. Celico imenujemo za rezultat rezultata in običajno uporabimo Čarovnika za funkcije . V kategoriji "Statistični podatki" najdemo in izberemo ime "TRENDI" . Kliknite gumb "V redu" .

    2. Prehod na argumente funkcije TREND v programu Microsoft Excel

  2. Odpre pogovorno okno operaterja TENDENCY. V polju "Znane vrednosti y" vnesemo koordinate stolpca "Profit podjetja " na zgoraj opisani način. V polju »Znane vrednosti x« vnesite naslov stolpca »Leto« . V polju »Nove x vrednosti« vnesemo referenco na celico, kjer se nahaja številka leta, na katero želite določiti napoved. V našem primeru je to 2019. Polje »Konstantno« ostane prazno. Kliknemo na gumb "OK" .

    3. Argumenti za funkcijo TREND v programu Microsoft Excel

  3. Operater obdeluje podatke in prikaže rezultat na zaslonu. Kot lahko vidite, se bo vsota predvidenega dobička za leto 2019, izračunana po metodi linearne odvisnosti, kot v prejšnjem načinu izračuna, znašala 4637,8 tisoč rubljev.

Rezultat funkcije TREND v programu Microsoft Excel

Metoda 4: Operater RAST

Druga funkcija, s katero lahko v Excelu napišete napoved, je operater RAST. Prav tako se nanaša na statistično skupino orodij, vendar se za razliko od prejšnjih metod uporablja metoda linearne odvisnosti, vendar z eksponentno metodo. Sintaksa tega orodja je videti takole:

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

Kot lahko vidite, argumenti te funkcije natanko ponavljajo argumente operaterja TENDENCY , zato jih ne bomo razpravljali že drugič, vendar pa se bomo v praksi takoj obrnili na uporabo tega orodja.

  1. Izberite izhodno izhodno celico in na običajen način pokličite Čarovnika za funkcije . Na seznamu statističnih operaterjev poiščite postavko »RAST« , jo izberite in kliknite gumb »V redu« .

    2. Preklop na argumente funkcije ROST v programu Microsoft Excel

  2. Vključeno je okno argumentov zgoraj navedene funkcije. Podatke v poljih tega okna vnesemo na enak način, kot smo jih vnesli v okno argumentov operaterja TENDENCY. Ko so podatki vneseni, kliknite gumb »V redu« .

    3. Argumenti za funkcijo ROST v programu Microsoft Excel

  3. Rezultat obdelave podatkov je prikazan na monitorju v predhodno določeni celici. Kot lahko vidite, je tokrat rezultat 4682,1 tisoč rubljev. Razlike med rezultati obdelave podatkov s strani operaterja TENDENCY so zanemarljive, vendar so na voljo. To je posledica dejstva, da ta orodja uporabljajo različne metode izračuna: metodo linearne odvisnosti in metodo eksponentne odvisnosti.

Rezultat funkcije GROWTH v programu Microsoft Excel

Metoda 5: Operater LINEST

Operater LINEST uporablja metodo linearnega približevanja. Ne smemo ga zamenjati z metodo linearne odvisnosti, ki jo uporablja orodje TRENDS . Njena sintaksa je naslednja:

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

Zadnja dva argumenta sta neobvezna. S prvima dvema smo seznanjeni s prejšnjimi metodami. Toda verjetno ste opazili, da v tej funkciji ni nobenega argumenta, ki kaže na nove vrednote. Dejstvo je, da to orodje določa le spremembo zneska prihodka na enoto, ki je v našem primeru eno leto, vendar se skupni znesek izračuna ločeno, pri čemer se zadnjemu dejanskemu dobičku dodajo rezultati izračuna operaterja LINEST , pomnožene s številom let.

  1. Izberemo celico, v kateri se bo izračun izvedel, in zaženemo Čarovnika za funkcije. Izberite ime »LINEST« v kategoriji »Statistični podatki« in kliknite gumb »V redu« .

    2. Pojdite na argumente funkcije LINEST v programu Microsoft Excel

  2. V polju »Znane vrednosti y« , odprto okno argumentov, vnesite koordinate stolpca »Profit podjetja«. V polju »Znane vrednosti x« vnesemo naslov stolpca »Leto« . Preostala polja ostanejo prazna. Nato kliknite gumb "OK" .

    3. Argumenti za funkcijo LINEST v programu Microsoft Excel

  3. Program izračuna in prikaže vrednost linearnega trenda v izbrani celici.

    4. Rezultat funkcije LINEST v programu Microsoft Excel

  4. Zdaj moramo ugotoviti vrednost napovedanega dobička za leto 2019. Znak "=" nastavite na poljubno prazno celico na listu. Kliknemo na celico, ki vsebuje dejanski znesek dobička za zadnje leto, ki smo ga preučili (2016). Postavite znak "+" . Nato kliknite celico, ki vsebuje predhodno izračunani linearni trend. Postavili smo znak "*" . Ker je med zadnjim letom študijskega obdobja (2016) in letom, za katero je treba napovedati (2019), obdobje traja tri leta, potem v celici določimo številko »3« . Če želite izračun, kliknite gumb Enter .

Končni izračun funkcije LINEST v programu Microsoft Excel

Kot lahko vidimo, bo napovedani dobiček, izračunan po metodi linearnega približevanja leta 2019, 4614,9 tisoč rubljev.

Metoda 6: operator LGRFPRIBL

Zadnje orodje, ki ga bomo upoštevali, bo LGRF . Ta izvajalec opravi izračune na podlagi eksponentne metode približevanja. Sintaksa ima naslednjo strukturo:

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

Kot lahko vidite, vsi argumenti povsem ponovijo ustrezne elemente prejšnje funkcije. Algoritem za izračun napovedi se bo nekoliko spremenil. Funkcija bo izračunala eksponentni trend, ki bo pokazal, kolikokrat se bo znesek prihodkov za eno obdobje spremenil, to je za eno leto. Najti bomo razliko v dobičku med zadnjim dejanskim obdobjem in prvo načrtovano in jo pomnožili s številom načrtovanih obdobij (3) in dodali rezultatu vsoto zadnjega dejanskega obdobja.

  1. Na seznamu operaterjev čarovnikov funkcij izberite ime "LGRFI" . Kliknite gumb »V redu« .

    2. Prehod na argumente funkcije LGRFIBRIBL v programu Microsoft Excel

  2. Začne se okno argumentov. V njej vnesemo podatke točno tako, kot smo naredili s funkcijo LINEST . Kliknemo na gumb "OK" .

    3. Argumenti funkcije LGRFDRIBL v programu Microsoft Excel

  3. Izračun eksponencialnega trenda se izračuna in odda v označeno celico.

    4. Rezultat funkcije LGRF v programu Microsoft Excel

  4. Postavite "=" v prazno celico. Odprite oklepaje in izberite celico, ki vsebuje vrednost prihodka za zadnje dejansko obdobje. Postavite znak "*" in izberite celico, ki vsebuje eksponentni trend. Postavili smo znak minus in ponovno kliknite na element, v katerem se nahaja znesek prihodkov za zadnje obdobje. Zapisnik zapremo in poganjamo znake "* 3 +" brez citatov. Spet kliknite na isto celico, ki je bila zadnja izbrana. Če želite izvesti izračun, pritisnite gumb Enter .

Končni izračun funkcije LGRFPRIBL v programu Microsoft Excel

Predvideni znesek dobička leta 2019, ki je bil izračunan po metodi eksponentnega približevanja, bo znašal 4.639,2 tisoč rubljev, kar se znova ne razlikuje veliko od rezultatov, dobljenih pri izračunu po prejšnjih metodah.

Lekcija: Druge statistične funkcije v Excelu

Ugotovili smo, na kakšen način je mogoče predvideti v programu Excel. Grafično se to lahko naredi z uporabo linije trendov in analitično - z uporabo številnih vgrajenih statističnih funkcij. Zaradi obdelave identičnih podatkov s strani teh operaterjev lahko pride do različnih rezultatov. Ampak to ni presenetljivo, saj vsi uporabljajo različne metode izračuna. Če je nihanje majhno, se lahko vse te možnosti, ki veljajo za določen primer, štejejo za relativno zanesljive.