Plotanje
Regresijska analiza
regresijska jednadžba Y iz x naziva se funkcionalna ovisnost y=f(x), a njegov prikaz je regresijska linija.
Excel vam omogućuje izradu dijagrama i grafikona prilično prihvatljive kvalitete. Excel ima poseban alat - Chart Wizard, pod čijim vodstvom korisnik prolazi kroz sve četiri faze procesa konstruiranja grafikona ili grafikona.
U pravilu, konstrukcija grafa počinje odabirom raspona koji sadrži podatke na temelju kojih se treba graditi. Takav početak pojednostavljuje daljnji tijek crtanja. Međutim, raspon s početnim podacima također se može podijeliti u drugoj fazi dijaloga s MAJSTOR DIJAGRAMA. U Excelu 2003 MAJSTOR DIJAGRAMA nalazi se u izborniku u obliku gumba ili se dijagram može kreirati klikom na karticu UMETNUTI i na popisu koji se otvori pronađite stavku DIJAGRAM. U Excelu 2007 također nalazimo karticu UMETNUTI(Slika 31).
Riža. 31. MAJSTOR DIJAGRAMA u Excelu 2007
Najlakši način odabira izvornog raspona podataka u kojem se ti podaci nalaze u susjednim retcima (stupcima ili retcima) je da kliknete na gornju lijevu ćeliju raspona i zatim povučete pokazivač miša u donju desnu ćeliju raspona. Prilikom odabira podataka u recima koji nisu susjedni, povucite pokazivač miša preko odabranih redaka držeći pritisnutu tipku Ctrl. Ako jedna od serija podataka ima ćeliju s naslovom, druga odabrana serija također mora imati odgovarajuću ćeliju, čak i ako je prazna.
Za regresijsku analizu najbolje je koristiti raspršeni dijagram (slika 30). Prilikom iscrtavanja, Excel tretira prvi redak odabranog raspona izvornih podataka kao skup vrijednosti za argument funkcija čiji se grafikoni trebaju iscrtati (isti skup za sve funkcije). Sljedeći redovi percipiraju se kao skupovi vrijednosti samih funkcija (svaki red sadrži vrijednosti jedne od funkcija koje odgovaraju zadanim vrijednostima argumenata koji se nalaze u prvom retku odabranog raspona).
U programu Excel 2007 naslovi osi smješteni su u karticu izbornika IZGLED(Slika 32).
Riža. 32. Postavljanje naziva osi grafikona u Excelu 2007
Za dobivanje matematičkog modela potrebno je nacrtati liniju trenda na grafikonu. U Excelu 2003 i 2007 desnom tipkom miša kliknite točke grafikona. Tada će se u Excelu 2003 pojaviti kartica s popisom stavki iz kojih biramo DODAJTE LINIJU TRENDA(Slika 33).
Riža. 33. DODAJTE LINIJU TRENDA
Nakon klika na stavku DODAJTE LINIJU TRENDA pojavit će se prozor LINIJA TRENDA(Slika 34). U kartici TYPE možete odabrati sljedeće vrste linija: linearne, logaritamske, eksponencijalne, eksponencijalne, polinomne, linearno filtriranje.
Riža. 34. Prozor LINIJA TRENDA u Excelu 2003
U kartici OPCIJE(Sl. 35) potvrdite okvir pokraj stavki PRIKAŽI JEDNADŽBU NA DIJAGRAMU, tada će se na grafikonu pojaviti matematički model ove ovisnosti. Stavili smo i kvačicu ispred stavke PRIKAŽI NA DIJAGRAMU VRIJEDNOST APROKSIMACIJSKE POUZDANOSTI (R^2).Što je vrijednost pouzdanosti aproksimacije bliža 1, to se odabrana krivulja više približava točkama na grafikonu. Zatim kliknite na gumb u redu. Na grafikonu će se pojaviti linija trenda, jednadžba koja joj odgovara i vrijednost pouzdanosti aproksimacije.
Riža. 35. Tab OPCIJE
U Excelu 2007, nakon što desnom tipkom miša kliknemo na točke grafikona, pojavit će se popis stavki izbornika iz kojih ODABERITE DODAJ LINIJU TRENDA(Slika 36).
Riža. 36. DODAJTE LINIJU TRENDA
Riža. 37. Tab PARAMETRI LINIJE TRENDA
Postavite potrebne potvrdne okvire i kliknite gumb ZATVORITI.
Na grafikonu će se pojaviti linija trenda, jednadžba koja joj odgovara i vrijednost pouzdanosti aproksimacije.
Gledajući bilo koji skup podataka raspoređenih tijekom vremena (vremenske serije), možemo vizualno prepoznati uspone i padove pokazatelja koje sadrži. Obrazac uspona i padova naziva se trend, koji može pokazati rastu li naši podaci ili opadaju.
Možda ću seriju članaka o predviđanju započeti s najjednostavnijom stvari - izgradnjom funkcije trenda. Na primjer, uzmimo podatke o prodaji i izgradimo model koji opisuje ovisnost prodaje o vremenu.
Osnovni koncepti
Mislim da su svi upoznati s linearnom funkcijom još od škole, ona je samo temelj trenda:
Y(t) = a0 + a1*t + E
Y je obujam prodaje, varijabla koju ćemo objasniti vremenom i o kojoj ovisi, odnosno Y (t);
t je broj razdoblja (redni broj mjeseca), koji objašnjava plan prodaje Y;
a0 je nulti regresijski koeficijent, koji pokazuje vrijednost Y(t), bez utjecaja eksplanatornog faktora (t=0);
a1 je regresijski koeficijent koji pokazuje koliko promatrani pokazatelj prodaje Y ovisi o utjecajnom faktoru t;
E su slučajne perturbacije koje odražavaju utjecaj drugih faktora koji nisu uzeti u obzir u modelu, osim vremena t.
Izgradnja modela
Dakle, znamo količinu prodaje za proteklih 9 mjeseci. Ovako izgleda naša ploča:
Sljedeće što trebamo napraviti je odrediti koeficijente a0 i a1 prognozirati obim prodaje za 10. mjesec.
Određivanje koeficijenata modela
Gradimo grafikon. Horizontalno vidimo mjesece na čekanju, okomito opseg prodaje:
U Google tablicama odaberite Uređivač dijagrama -> Dodatni i potvrdite okvir pored trend linije. U postavkama odaberite Označiti — Jednadžba i Prikaži R^2.
Ako sve radite u MS Excelu, kliknite desnom tipkom miša na grafikon i u padajućem izborniku odaberite "Dodaj liniju trenda".
Prema zadanim postavkama, izgrađena je linearna funkcija. S desne strane odaberite "Prikaži jednadžbu na dijagramu" i "R^2 vrijednost pouzdanosti aproksimacije".
Evo što se dogodilo:
Na grafu vidimo jednadžbu funkcije:
y = 4856*x + 105104
Opisuje obujam prodaje ovisno o broju mjeseca za koji želimo predvidjeti tu prodaju. U blizini vidimo koeficijent determinacije R^2, koji označava kvalitetu modela i koliko dobro opisuje našu prodaju (Y). Što bliže 1 to bolje.
Imam R^2 = 0,75. Ovo je prosječan pokazatelj, govori da model ne uzima u obzir druge značajne faktore osim vremena t, na primjer, to može biti sezonalnost.
Predviđamo
y = 4856*10 + 105104
Imamo 153664 prodaje u sljedećem mjesecu. Ako dodamo novu točku na grafikon, odmah vidimo da se R^2 poboljšao.
Dakle, možete predvidjeti podatke za nekoliko mjeseci unaprijed, ali bez uzimanja u obzir drugih čimbenika, vaša prognoza će ležati na liniji trenda i neće biti informativna koliko biste željeli. Osim toga, ovako napravljena dugoročna prognoza bit će vrlo približna.
Možete povećati točnost modela dodavanjem sezonalnosti funkciji trenda, što ćemo učiniti u sljedećem članku.
Linija trenda koristi se za vizualnu ilustraciju kretanja cijena. Element tehničke analize je geometrijski prikaz prosječnih vrijednosti analiziranog pokazatelja.
Pogledajmo kako dodati liniju trenda na grafikon u Excelu.
Dodavanje linije trenda na grafikon
Za primjer, uzmimo prosječne cijene nafte od 2000. godine iz otvorenih izvora. Podatke za analizu unijet ćemo u tablicu:
Linija trenda u Excelu je grafikon aproksimativne funkcije. Zašto je to potrebno - napraviti prognoze na temelju statističkih podataka. U tu svrhu potrebno je produžiti liniju i odrediti njezine vrijednosti.
Ako je R2 = 1, tada je pogreška aproksimacije nula. U našem primjeru, odabir linearne aproksimacije dao je nisku pouzdanost i loš rezultat. Prognoza će biti netočna.
Pažnja!!! Linija trenda ne može se dodati sljedećim vrstama grafikona i dijagrama:
- latica;
- kružni;
- površinski;
- prstenasti;
- volumen;
- s akumulacijom.
Trendline Equation u Excelu
U gornjem primjeru, linearna aproksimacija odabrana je samo za ilustraciju algoritma. Kao što pokazuje vrijednost pouzdanosti, izbor nije bio posve uspješan.
Trebali biste odabrati vrstu prikaza koja najtočnije prikazuje trend u korisničkom unosu. Pogledajmo opcije.
Linearna aproksimacija
Njegov geometrijski prikaz je ravna linija. Stoga se linearna aproksimacija koristi za ilustraciju pokazatelja koji se povećava ili smanjuje konstantnom stopom.
Razmotrite uvjetni broj ugovora koje je upravitelj sklopio na 10 mjeseci:
Na temelju podataka u Excel tablici izgradit ćemo dijagram raspršenosti (pomoći će u ilustriranju linearnog tipa):
Odaberite grafikon - "dodaj liniju trenda". U parametrima odaberite linearni tip. Dodajemo vrijednost aproksimacijske pouzdanosti i jednadžbu linije trenda u Excelu (samo označite kućice na dnu prozora "Parametri").
Dobijamo rezultat:
Bilješka! S linearnom vrstom aproksimacije, podatkovne točke nalaze se što bliže ravnoj liniji. Ovaj prikaz koristi sljedeću jednadžbu:
y = 4,503x + 6,1333
- gdje je 4,503 indikator nagiba;
- 6.1333 - pomaci;
- y je niz vrijednosti,
- x je broj razdoblja.
Ravna crta na grafikonu pokazuje stalan porast kvalitete rada menadžera. Vrijednost aproksimacijske pouzdanosti je 0,9929, što ukazuje na dobro slaganje između izračunate ravne linije i izvornih podataka. Prognoze moraju biti točne.
Da biste predvidjeli broj sklopljenih ugovora, na primjer, u 11. razdoblju, trebate umjesto x u jednadžbu zamijeniti broj 11. Tijekom izračuna saznajemo da će u 11. razdoblju ovaj upravitelj sklopiti 55-56 ugovora.
Eksponencijalna linija trenda
Ova vrsta će biti korisna ako se ulazne vrijednosti mijenjaju kontinuirano rastućom brzinom. Eksponencijalna aproksimacija se ne primjenjuje u prisutnosti nulte ili negativne karakteristike.
Izgradimo eksponencijalnu liniju trenda u Excelu. Uzmimo za primjer uvjetne vrijednosti korisne opskrbe električnom energijom u regiji X:
Gradimo grafikon. Dodajte eksponencijalnu liniju.
Jednadžba ima sljedeći oblik:
y = 7,6403e^-0,084x
- gdje su 7,6403 i -0,084 konstante;
- e je baza prirodnog logaritma.
Indeks pouzdanosti aproksimacije bio je 0,938 - krivulja odgovara podacima, pogreška je minimalna, prognoze će biti točne.
Zabilježite liniju trenda u Excelu
Koristi se za sljedeće promjene indikatora: prvo, brzo povećanje ili smanjenje, zatim relativna stabilnost. Optimizirana krivulja dobro se prilagođava ovakvom "ponašanju" količine. Logaritamski trend pogodan je za predviđanje prodaje novog proizvoda koji se tek predstavlja na tržištu.
U početnoj fazi, zadatak proizvođača je povećati bazu kupaca. Kad proizvod ima svog kupca, mora se zadržati, servirati.
Izgradimo grafikon i dodajmo logaritamsku liniju trenda kako bismo predvidjeli prodaju uvjetovanog proizvoda:
R2 ima vrijednost blizu 1 (0,9633), što označava minimalnu pogrešku aproksimacije. Predviđat ćemo količine prodaje u narednim razdobljima. Da biste to učinili, trebate zamijeniti broj razdoblja u jednadžbi umjesto x.
Na primjer:
| Razdoblje | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| Prognoza | 1005,4 | 1024,18 | 1041,74 | 1058,24 | 1073,8 | 1088,51 | 1102,47 |
Za izračun prognoziranih brojki korištena je sljedeća formula: =272,14*LN(B18)+287,21. Gdje je B18 broj razdoblja.
Polinomna linija trenda u Excelu
Ova krivulja ima rastuće i padajuće varijable. Za polinome (polinome) stupanj se određuje (brojem maksimalnih i minimalnih vrijednosti). Na primjer, jedan ekstrem (minimum i maksimum) je drugi stupanj, dva ekstrema su treći stupanj, tri su četvrti.
Polinomski trend u Excelu koristi se za analizu velikog skupa podataka o nestabilnoj vrijednosti. Pogledajmo primjer prvog skupa vrijednosti (cijene nafte).
Da bih dobio takvu vrijednost aproksimacijske pouzdanosti (0,9256), morao sam staviti 6. stupanj.
Ali takav trend omogućuje vam da napravite više ili manje točne prognoze.
Što učiniti ako nema vremenskih mjerenja za određene količine/veličine proizvoda? Ili je broj mjerenja nedovoljan, pa se dodatna promatranja ne mogu napraviti u bliskoj budućnosti? Najbolji način za rješavanje ovog problema je izrada izračunatih ovisnosti (regresijske jednadžbe) pomoću linija trenda u MS Excelu.
Razmotrimo stvarnu situaciju: u skladištu, kako bi se odredio iznos troškova rada za komisioniranje narudžbe u kutiji, provedena su kronometrijska promatranja. Rezultati ovih opažanja prikazani su u tablici 1 u nastavku.
Naknadno je bilo potrebno utvrditi vrijeme utrošeno na komisioniranje 0,6 i 0,9 m3 robe/narudžbe. Zbog nemogućnosti provođenja dodatnih kronometrijskih studija, vrijeme utrošeno na odabir ovih volumena narudžbe izračunato je pomoću regresijskih jednadžbi u MS Excelu. Da bismo to učinili, tablica 1 je pretvorena u tablicu 2.
Odabir dijagrama raspršenosti, sl. jedan
Sljedeći korak: pokazivač miša postavljen je na jednu od točaka grafikona i desnom tipkom miša pozvan je kontekstni izbornik u kojem je odabrana stavka: "dodaj liniju trenda" (slika 2).
Dodavanje linije trenda, sl. 2
U prozoru za postavljanje formata linije trenda koji se pojavio (slika 3) odabrani su sekvencijalno: tip linije linearno / snaga i postavljeni su sljedeći potvrdni okviri: "prikaži jednadžbu na dijagramu" i "stavi na dijagram vrijednost pouzdanosti aproksimacije (R ^ 2)" (koeficijent odlučnosti).
Format linije trenda, sl. 3
Kao rezultat toga, grafikoni prikazani na sl. 4 i 5.
Linearna izračunata ovisnost, sl. četiri
Izračunata ovisnost snage, sl. 5
Vizualna analiza grafova jasno ukazuje na bliskost dobivenih ovisnosti. Osim toga, vrijednost aproksimacijske pouzdanosti (R^2), koja se naziva i koeficijent determinacije, u slučaju obje ovisnosti je ista vrijednost od 0,97. Poznato je da što je koeficijent determinacije bliži 1, to linija trenda više odgovara stvarnosti. Također se može ustvrditi da je promjena vremena utrošenog na obradu narudžbe 97% zbog promjene količine robe. Stoga u ovom slučaju nije bitno: koju izračunatu ovisnost odabrati kao glavnu za naknadni obračun vremenskih troškova.
Uzmimo kao glavnu - linearnu izračunatu ovisnost. Tada će vrijednosti utrošenog vremena ovisno o količini robe biti određene formulom: y = 54,511x + 0,1489. Rezultati ovih izračuna za količinu robe, za koju su prethodno provedena kronometrijska promatranja, prikazani su u tablici 3 u nastavku.
Odredimo prosječno odstupanje troškova vremena izračunatih regresijskom jednadžbom od troškova vremena izračunatih iz podataka kronometrijskih promatranja: (-0,05+0,10-0,05+0,01)/4=0,0019. Dakle, troškovi vremena izračunati prema regresijskoj jednadžbi razlikuju se od troškova vremena izračunatih prema podacima kronometrijskih promatranja samo za 0,19%. Odstupanje podataka je zanemarivo.
Prema formuli: y = 54,511x + 0,1489 postavit ćemo vremenske troškove za količinu robe za koju prethodno nisu provedena kronometrijska promatranja (tablica 4).
Dakle, konstrukcija izračunatih ovisnosti pomoću linija trenda u MS Excelu - ovo je izvrstan način za određivanje vremena utrošenog na operacije koje iz raznih razloga nisu obuhvaćene kronometrijskim promatranjima.
Izvedba zadataka za iscrtavanje linije trenda ističe se činjenicom da početni podaci mogu biti skup brojeva koji nisu međusobno povezani.
Predviđanje po redovnom rasporedu je nemoguće, jer će njegov koeficijent determinizma (R^2) biti blizu nule.
Zato se koriste posebne funkcije.
Sada ćemo ih izgraditi, konfigurirati i analizirati.
Lagana verzija
Proces izgradnje linije trenda sastoji se od tri faze: unos izvornih podataka u excel, crtanje grafikona, odabir linije trenda i njenih parametara.
Počnimo s unosom podataka.
1. Napravite tablicu u Excelu s izvornim podacima.
(Slika 1)
2. Odaberite ćelije B3:B17 i idite na karticu "Insert" i odaberite "Chart".
(Slika 2)
3. Nakon što je grafikon izgrađen, možete dodati opise i naslov.
Najprije lijevom tipkom miša kliknite obrub grafikona da biste ga odabrali.
Zatim idite na karticu "Dizajner" i odaberite "Izgled 1".
(Slika 3)
4. Prijeđimo na izgradnju linije trenda. Da biste to učinili, ponovno odaberite grafikon i idite na karticu "Izgled".
(Slika 4)
5. Pritisnite gumb "Linija trenda" i odaberite "linearna aproksimacija" ili "eksponencijalna aproksimacija".
(Slika 5)
Ovako smo izgradili primarnu liniju trenda, koja možda ne odgovara stvarnosti.
Ovo je naš srednji rezultat.
(Slika 6)
Stoga će biti potrebno prilagoditi parametre naše linije trenda ili odabrati drugu funkciju.
Profesionalna verzija: odabir linije trenda i postavke parametara
6. Kliknite na gumb "Linija trenda" i odaberite "Dodatni parametri i linije trenda".
(Slika 7)
7. U prozoru "Format linije trenda" označimo okvir pored "stavite na grafikon vrijednost pouzdanosti aproksimacije R ^ 2 i kliknite gumb" zatvori ".
Na dijagramu vidimo koeficijent R^2= 0,6442
(Slika 8)
8. Vratite promjene. Odaberite grafikon, kliknite karticu "Izgled", gumb "crta trenda" i odaberite "Ništa".
9. Idite na prozor "Format linije trenda", ali da biste odabrali liniju trenda "Polinom", promijenite stupanj, postižući koeficijent R ^ 2 = 0,8321
(Slika 9)
Prognoza
Ako trebamo pogoditi koji bi se podaci mogli dobiti u sljedećem mjerenju, u prozoru "Format linije trenda" označavamo broj razdoblja za koje se radi prognoza.
(Slika 10)
Na temelju prognoze možemo pretpostaviti da će 25. siječnja broj osvojenih bodova biti od 60 do 70.
Zaključak
I na kraju, ako vas zanima formula po kojoj se gradi trend, u okviru "Format linije trenda" označite okvir pored "prikaži jednadžbu na grafikonu".
Sada znate kako izvršiti zadatak i iscrtati liniju trenda, čak i u programu kao što je excel 2010.
Postavljajte pitanja, nemojte se sramiti.
