Plotting
Regresiona analiza
jednadžba regresije Y od X naziva funkcionalna zavisnost y=f(x), a njegov dijagram je regresijska linija.
Excel vam omogućava da kreirate grafikone i grafikone prilično prihvatljivog kvaliteta. Excel ima poseban alat - Čarobnjak za grafikone, pod čijim vodstvom korisnik prolazi kroz sve četiri faze procesa konstruisanja grafikona ili grafikona.
U pravilu, izgradnja grafa počinje odabirom raspona koji sadrži podatke na kojima bi se trebao graditi. Takav početak pojednostavljuje dalji tok zapleta. Međutim, raspon sa početnim podacima može se podijeliti iu drugoj fazi dijaloga sa MAJSTOR DIJAGRAMA. U programu Excel 2003 MAJSTOR DIJAGRAMA je u meniju u obliku dugmeta ili se dijagram može kreirati klikom na karticu INSERT i na listi koja se otvori pronađite stavku DIJAGRAM. U programu Excel 2007 nalazimo i karticu INSERT(Sl. 31).
Rice. 31. MAJSTOR DIJAGRAMA u Excel 2007
Najlakši način da odaberete raspon izvornih podataka u kojem se ti podaci nalaze u susjednim redovima (kolonama ili redovima) je da kliknete na gornju lijevu ćeliju raspona, a zatim povučete pokazivač miša na donju desnu ćeliju raspona. Prilikom odabira podataka u redovima koji nisu susjedni, prevucite pokazivač miša preko odabranih redova 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 dijagram raspršenosti (slika 30). Kada ga crta, Excel tretira prvi red odabranog raspona izvornih podataka kao skup vrijednosti za argument funkcija čiji grafovi treba da se nacrtaju (isti skup za sve funkcije). Sljedeći redovi se percipiraju kao skupovi vrijednosti samih funkcija (svaki red sadrži vrijednosti jedne od funkcija koje odgovaraju datim vrijednostima argumenata koje se nalaze u prvom redu odabranog raspona).
U programu Excel 2007, naslovi osa se postavljaju na karticu menija LAYOUT(Sl. 32).
Rice. 32. Postavljanje imena osa grafikona u Excel 2007
Da biste dobili matematički model, potrebno je nacrtati liniju trenda na grafikonu. U programu Excel 2003 i 2007 kliknite desnim tasterom miša na tačke grafikona. Zatim će se u Excelu 2003 pojaviti kartica sa listom stavki iz kojih biramo DODAJ LINIJU TRENDA(Sl. 33).
Rice. 33. DODAJ LINIJU TRENDA
Nakon klika na stavku DODAJ LINIJU TRENDA pojaviće se prozor TREND LINE(Sl. 34). U kartici TYPE možete odabrati sljedeće vrste linija: linearne, logaritamske, eksponencijalne, eksponencijalne, polinomske, linearno filtriranje.
Rice. 34. Prozor TREND LINE u Excel 2003
U kartici OPCIJE(Sl. 35) označite kvadratić pored stavki PRIKAŽI JEDNAČINU NA DIJAGRAMU, tada će se na grafikonu pojaviti matematički model ove zavisnosti. Stavljamo i kvačicu ispred stavke PRIKAŽITE NA DIJAGRAMU VRIJEDNOST POUZDANOSTI APROKSIMACIJE (R^2).Što je vrijednost pouzdanosti aproksimacije bliža 1, odabrana kriva se bliže približava tačkama na grafikonu. Zatim kliknite na dugme uredu. Na grafikonu će se pojaviti linija trenda, jednačina koja joj odgovara i vrijednost pouzdanosti aproksimacije.
Rice. 35. Tab OPCIJE
U programu Excel 2007, nakon što kliknemo desnim tasterom miša na tačke grafikona, pojaviće se lista stavki menija iz kojih ODABERITE DODAVANJE LINIJE TRENDA(Sl. 36).
Rice. 36. DODAJ LINIJU TRENDA
Rice. 37. Tab PARAMETRI LINIJE TRENDA
Postavite potrebne potvrdne okvire i kliknite na dugme CLOSE.
Na grafikonu će se pojaviti linija trenda, jednačina koja joj odgovara i vrijednost pouzdanosti aproksimacije.
Gledajući bilo koji skup podataka raspoređenih tokom vremena (vremenske serije), možemo vizualno identificirati uspone i padove indikatora koje sadrži. Obrazac uspona i padova naziva se trend, koji može pokazati da li se naši podaci povećavaju ili smanjuju.
Možda ću seriju članaka o predviđanju započeti 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 sa linearnom funkcijom još od škole, ona je samo u osnovi trenda:
Y(t) = a0 + a1*t + E
Y je obim prodaje, varijabla koju ćemo objasniti vremenom i od koje zavisi, odnosno Y (t);
t je broj perioda (redni broj mjeseca), koji objašnjava plan prodaje Y;
a0 je nulti koeficijent regresije, koji pokazuje vrijednost Y(t), u odsustvu uticaja faktora objašnjenja (t=0);
a1 je koeficijent regresije, koji pokazuje koliko proučavani pokazatelj prodaje Y zavisi od faktora uticaja t;
E su slučajne perturbacije koje odražavaju uticaj drugih faktora koji nisu uzeti u obzir u modelu, osim vremena t.
Izgradnja modela
Dakle, znamo obim prodaje za proteklih 9 mjeseci. Evo kako izgleda naša tabla:
Sljedeće što treba da uradimo je da odredimo koeficijente a0 i a1 prognozirati obim prodaje za 10. mjesec.
Određivanje koeficijenata modela
Pravimo grafikon. Horizontalno vidimo mjesece na čekanju, vertikalno obim prodaje:
U Google Sheets odaberite Editor dijagrama -> Dodatno i označite polje pored linije trenda. U podešavanjima izaberite Label — Jednačina i Prikaži R^2.
Ako sve radite u MS Excel-u, kliknite desnim tasterom miša na grafikon i odaberite "Dodaj liniju trenda" u padajućem meniju.
Po defaultu, linearna funkcija je izgrađena. Na desnoj strani odaberite "Prikaži jednačinu na dijagramu" i "R^2 aproksimacijske vrijednosti pouzdanosti".
Evo šta se dogodilo:
Na grafikonu vidimo jednačinu funkcije:
y = 4856*x + 105104
Opisuje obim prodaje u zavisnosti od broja meseca za koji želimo da predvidimo ove prodaje. U blizini vidimo koeficijent determinacije R^2, koji ukazuje na kvalitet 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, on kaže da model ne uzima u obzir druge značajne faktore osim vremena t, na primjer, može biti sezonalnost.
Predviđamo
y = 4856*10 + 105104
Imamo 153664 prodaje u narednih mjesec dana. Ako dodamo novu tač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 faktora, vaša prognoza će ležati na liniji trenda i neće biti informativna koliko biste željeli. Osim toga, dugoročna prognoza napravljena na ovaj način bit će vrlo približna.
Možete povećati točnost modela dodavanjem sezonalnosti funkciji trenda, što ćemo učiniti u sljedećem članku.
Trend linija se koristi za vizualnu ilustraciju kretanja cijena. Element tehničke analize je geometrijski prikaz prosječnih vrijednosti analiziranog indikatora.
Pogledajmo kako dodati liniju trenda grafikonu u Excelu.
Dodavanje linije trenda na grafikon
Na primjer, uzmimo prosječne cijene nafte od 2000. godine iz otvorenih izvora. Podatke za analizu unećemo u tabelu:
Linija trenda u Excelu je graf aproksimativne funkcije. Zašto je to potrebno - praviti prognoze na osnovu statističkih podataka. U tu svrhu potrebno je produžiti liniju i odrediti njene vrijednosti.
Ako je R2 = 1, onda je greška aproksimacije nula. U našem primjeru, odabir linearne aproksimacije dao je nisko povjerenje i loš rezultat. Prognoza će biti netačna.
Pažnja!!! Linija trenda se ne može dodati sljedećim tipovima grafikona i grafikona:
- latica;
- circular;
- površina;
- prstenasto;
- volumen;
- sa akumulacijom.
Jednadžba linije trenda u Excelu
U gornjem primjeru, linearna aproksimacija je odabrana samo da bi se ilustrovao algoritam. Kao što pokazuje vrijednost pouzdanosti, izbor nije bio sasvim uspješan.
Trebali biste odabrati tip prikaza koji najpreciznije ilustruje trend u unosu korisnika. Hajde da pogledamo opcije.
Linearna aproksimacija
Njegov geometrijski prikaz je prava linija. Stoga se linearna aproksimacija koristi za ilustraciju indikatora koji se povećava ili smanjuje konstantnom brzinom.
Uzmimo u obzir uslovni broj ugovora koje je sklopio menadžer na 10 mjeseci:
Na osnovu podataka u Excel tabeli, napravićemo dijagram raspršenosti (to će pomoći da se ilustruje linearni tip):
Odaberite grafikon - "dodaj liniju trenda". U parametrima odaberite linearni tip. Dodajemo vrijednost pouzdanosti aproksimacije i jednadžbu linije trenda u Excelu (samo označite kućice na dnu prozora "Parametri").
Dobijamo rezultat:
Bilješka! Kod linearnog tipa aproksimacije, tačke podataka se nalaze što bliže pravoj liniji. Ovaj pogled koristi sljedeću jednačinu:
y = 4,503x + 6,1333
- gdje je 4,503 indikator nagiba;
- 6.1333 - pomaci;
- y je niz vrijednosti,
- x je broj perioda.
Prava linija na grafikonu pokazuje stalni porast kvaliteta rada menadžera. Vrijednost pouzdanosti aproksimacije je 0,9929, što ukazuje na dobro slaganje između izračunate prave linije i početnih podataka. Prognoze moraju biti tačne.
Da biste predvidjeli broj ugovora sklopljenih, na primjer, u 11. periodu, potrebno je da u jednačinu umjesto x zamijenite broj 11. U toku proračuna saznajemo da će u 11. periodu ovaj menadžer sklopiti 55-56 ugovora.
Eksponencijalna linija trenda
Ovaj tip će biti koristan ako se ulazne vrijednosti mijenjaju stalno rastućom brzinom. Eksponencijalna aproksimacija se ne primjenjuje u prisustvu nulte ili negativnih karakteristika.
Napravimo eksponencijalnu liniju trenda u Excelu. Uzmimo za primjer uvjetne vrijednosti korisne opskrbe električnom energijom u regiji X:
Pravimo grafikon. Dodajte eksponencijalnu liniju.
Jednačina 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 - kriva odgovara podacima, greška je minimalna, prognoze će biti tačne.
Zabilježite liniju trenda u Excelu
Koristi se za sljedeće promjene indikatora: prvo brzo povećanje ili smanjenje, zatim relativnu stabilnost. Optimizirana kriva se dobro prilagođava ovom "ponašanju" količine. Logaritamski trend je pogodan za predviđanje prodaje novog proizvoda koji se tek uvodi na tržište.
U početnoj fazi, zadatak proizvođača je povećati bazu kupaca. Kada proizvod ima svog kupca, mora se zadržati, servirati.
Napravimo graf i dodajmo logaritamsku liniju trenda da predvidimo prodaju uslovnog proizvoda:
R2 je po vrijednosti blizu 1 (0,9633), što ukazuje na minimalnu grešku aproksimacije. Obim prodaje ćemo prognozirati u narednim periodima. Da biste to učinili, trebate zamijeniti broj perioda u jednačini umjesto x.
Na primjer:
| Period | 14 | 15 | 16 | 17 | 18 | 19 | 20 |
| Prognoza | 1005,4 | 1024,18 | 1041,74 | 1058,24 | 1073,8 | 1088,51 | 1102,47 |
Za izračunavanje cifara prognoze korišćena je sledeća formula: =272,14*LN(B18)+287,21. Gdje je B18 broj perioda.
Polinomska linija trenda u Excelu
Ova kriva ima rastuće i opadajuće varijable. Za polinome (polinome) se određuje stepen (brojem maksimalnih i minimalnih vrijednosti). Na primjer, jedan ekstrem (minimum i maksimum) je drugi stepen, dva ekstrema su treći stepen, tri su četvrti.
Polinomski trend u Excelu se koristi za analizu velikog skupa podataka o nestabilnoj vrijednosti. Pogledajmo primjer prvog skupa vrijednosti (cijene nafte).
Da bih dobio takvu vrijednost pouzdanosti aproksimacije (0,9256), morao sam staviti 6. stepen.
Ali takav trend vam omogućava da napravite manje ili više točne prognoze.
Što učiniti ako ne postoje mjerenja vremena za određene količine/veličine proizvoda? Ili je broj mjerenja nedovoljan, a dodatna zapažanja se ne mogu napraviti u bliskoj budućnosti? Najbolji način za rješavanje ovog problema je izgradnja izračunatih ovisnosti (regresionih jednačina) korištenjem linija trenda u MS Excel-u.
Razmotrimo stvarnu situaciju: u skladištu, kako bi se utvrdio iznos troškova rada za komisioniranje narudžbe u kutijama, izvršena su hronometrijska promatranja. Rezultati ovih zapažanja prikazani su u tabeli 1 ispod.
Nakon toga, postalo je potrebno utvrditi vrijeme utrošeno na komisioniranje 0,6 i 0,9 m3 robe/porudžbine. Zbog nemogućnosti sprovođenja dodatnih hronometrijskih studija, vrijeme utrošeno na odabir ovih volumena naloga je izračunato pomoću regresionih jednačina u MS Excel-u. Da biste to učinili, tabela 1 je pretvorena u tabelu 2.
Izbor dijagrama raspršenja, sl. jedan
Sljedeći korak: kursor miša je postavljen na jednu od tačaka grafikona i desnom tipkom miša je pozvan kontekstni meni u kojem je odabrana stavka: "dodaj liniju trenda" (Sl. 2).
Dodavanje linije trenda, sl. 2
U prozoru za podešavanje formata linije trenda koji se pojavio (sl. 3) su sekvencijalno odabrani: tip linije linearni/potenzivni zakon i potvrdni okviri su postavljeni za sljedeće stavke: “prikaži jednačinu 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 zavisnost, sl. četiri
Izračunata zavisnost snage, sl. 5
Vizuelna analiza grafova jasno ukazuje na bliskost dobijenih zavisnosti. Osim toga, vrijednost pouzdanosti aproksimacije (R^2), koja se još naziva i koeficijent determinacije, u slučaju obje zavisnosti je ista vrijednost od 0,97. Poznato je da što je koeficijent determinacije bliži 1, to više linija trenda odgovara stvarnosti. Takođe se može konstatovati da je promjena vremena utrošenog na obradu narudžbe 97% zbog promjene količine robe. Dakle, u ovom slučaju nije bitno: koju izračunatu zavisnost izabrati kao glavnu za naknadni obračun vremenskih troškova.
Uzmimo kao glavnu - linearnu izračunatu zavisnost. Tada će se vrijednosti utrošenog vremena ovisno o količini robe odrediti formulom: y = 54,511x + 0,1489. Rezultati ovih proračuna za količinu robe, za koju su prethodno vršena hronometrijska posmatranja, prikazani su u tabeli 3 ispod.
Odredimo prosječno odstupanje vremenskih troškova izračunatih regresionom jednačinom od vremenskih troškova izračunatih prema podacima hronometrijskih posmatranja: (-0,05+0,10-0,05+0,01)/4=0,0019. Dakle, vremenski troškovi izračunati prema regresijskoj jednadžbi razlikuju se od vremenskih troškova izračunatih prema podacima kronometrijskih posmatranja samo za 0,19%. Nepodudarnost podataka je zanemarljiva.
Prema formuli: y = 54,511x + 0,1489, postavićemo vremenske troškove za količinu robe za koju nisu prethodno vršena hronometrijska posmatranja (tabela 4).
Dakle, konstrukcija izračunatih zavisnosti korišćenjem linija trenda u MS Excel-u - ovo je odličan način za određivanje vremena utrošenog na operacije koje iz različitih razloga nisu bile pokrivene hronometrijskim posmatranjima.
Izvođenje zadataka za crtanje linije trenda razlikuje se po tome što 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.
Zbog toga 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, iscrtavanje grafikona, odabir linije trenda i njenih parametara.
Počnimo sa unosom podataka.
1. Kreirajte tabelu u Excelu sa izvornim podacima.
(Slika 1)
2. Odaberite ćelije B3:B17 i idite na karticu "Insert" i odaberite "Chart".
(Slika 2)
3. Nakon što je grafikon napravljen, možete dodati natpise i naslov.
Prvo kliknite lijevom tipkom miša na ivicu grafikona da biste ga odabrali.
Zatim idite na karticu "Dizajner" i odaberite "Izgled 1".
(Slika 3)
4. Pređimo na izgradnju linije trenda. Da biste to učinili, ponovo odaberite grafikon i idite na karticu "Izgled".
(Slika 4)
5. Kliknite na dugme "Linija trenda" i odaberite "linearnu aproksimaciju" ili "eksponencijalnu aproksimaciju".
(Slika 5)
Tako smo izgradili primarnu liniju trenda, koja možda ne odgovara stvarnosti.
Ovo je naš međurezultat.
(Slika 6)
I zato će biti potrebno podesiti parametre naše linije trenda ili odabrati drugu funkciju.
Profesionalna verzija: odabir linije trenda i podešavanja parametara
6. Kliknite na dugme "Linija trenda" i odaberite "Dodatni parametri i linije trenda".
(Slika 7)
7. U prozoru "Format linije trenda" označimo polje pored "stavite na grafikon vrijednost aproksimacijske pouzdanosti R ^ 2 i kliknite na dugme" zatvori ".
Na dijagramu vidimo koeficijent R^2= 0,6442
(Slika 8)
8. Vratite promjene. Odaberite grafikon, kliknite na karticu "Layout", dugme "trend line" i odaberite "None".
9. Idite na prozor "Format linije trenda", ali da biste odabrali "Polinomsku" liniju trenda, promijenite stepen, postižući koeficijent R ^ 2 = 0,8321
(Slika 9)
Prognoza
Ukoliko treba da pretpostavimo koji podaci bi se mogli dobiti u sledećem merenju, u prozoru „Format linije trenda“ označavamo broj perioda za koje se prognoza pravi.
(Slika 10)
Na osnovu prognoze možemo pretpostaviti da bi 25. januara broj osvojenih bodova bio od 60 do 70.
Zaključak
I u zaključku, ako vas zanima formula po kojoj se trend gradi, u polju “Format linije trenda” označite kvadratić pored “prikaži jednačinu na grafikonu”.
Sada znate kako da dovršite zadatak i nacrtate liniju trenda, čak iu programu kao što je excel 2010.
Postavljajte pitanja, ne stidite se.
