MS Excel paket vam omogućava da većinu posla obavite vrlo brzo prilikom konstruisanja jednačine linearne regresije. Važno je razumjeti kako interpretirati dobijene rezultate.
Za rad je potreban dodatak Paket analiza, koji mora biti omogućen u stavci menija Usluga\Dodaci
U programu Excel 2007, da biste omogućili paket za analizu, morate kliknuti idi na blokiranje Excel opcije klikom na dugme u gornjem levom uglu, a zatim na dugme " Excel opcije"na dnu prozora:
Da biste izgradili regresijski model, morate odabrati stavku Usluga\Analiza podataka\Regresija. (U Excelu 2007 ovaj način je u bloku Podaci/analiza podataka/regresija). Pojavit će se dijaloški okvir koji trebate popuniti:
1) Interval unosa Y¾ sadrži vezu do ćelija koje sadrže vrijednosti rezultirajuće karakteristike y. Vrijednosti moraju biti raspoređene u stupac;
2) Interval unosa X¾ sadrži vezu do ćelija koje sadrže vrijednosti faktora. Vrijednosti moraju biti raspoređene u kolone;
3) Potpis Oznake postaviti ako prve ćelije sadrže tekst objašnjenja (oznake podataka);
4) Nivo pouzdanosti¾ je nivo pouzdanosti, koji se podrazumevano smatra 95%. Ako niste zadovoljni ovom vrijednošću, tada morate omogućiti ovu zastavicu i unijeti traženu vrijednost;
5) Potpis Konstanta-nula uključuje se ako je potrebno konstruirati jednačinu u kojoj je slobodna varijabla ;
6) Izlazne opcije odredite gdje se nalaze rezultati. Po defaultu pravi način rada Novi radni list;
7) Blokirati Ostaci omogućava vam da uključite izlaz reziduala i konstrukciju njihovih grafova.
Kao rezultat, prikazuju se informacije koje sadrže sve potrebne informacije i grupirane u tri bloka: Statistika regresije, Analiza varijanse, Povlačenje bilansa. Pogledajmo ih pobliže.
1. Statistika regresije:
višestruko R određuje se formulom ( Pearsonov koeficijent korelacije);
R 
(koeficijent odlučnosti);
Normalizovano R-kvadrat se izračunava po formuli 
(koristi se za višestruku regresiju);
Standardna greška S izračunato po formuli 
;
Zapažanja ¾ je količina podataka n.
2. Analiza varijanse, linija Regresija:
Parametar df jednaki m(broj skupova faktora x);
Parametar SS određuje se formulom ;
Parametar GOSPOĐA određuje se formulom ;
Statistika F određuje se formulom ;
Značaj F. Ako rezultirajući broj premašuje , tada se hipoteza prihvaća (nema linearne veze), u suprotnom hipoteza se prihvaća (postoji linearni odnos).
3. Analiza varijanse, linija Ostatak:
Parametar df jednak ;
Parametar SS određuje se formulom 
;
Parametar GOSPOĐA određuje se formulom.
4. Analiza varijanse, linija Ukupno sadrži zbir prva dva stupca.
5. Analiza varijanse, linija Y-raskrsnica sadrži koeficijent, standardnu grešku i t-statistika.
P-vrijednost ¾ je vrijednost nivoa značajnosti koji odgovaraju izračunatom t-statičari. Određeno funkcijom STUDIST( t-statistika; ). Ako P-vrijednost prelazi , tada je odgovarajuća varijabla statistički beznačajna i može se isključiti iz modela.
donjih 95% I Top 95%¾ su donja i gornja granica intervala povjerenja od 95 posto za koeficijente teorijske jednačine linearne regresije. Ako je vrijednost vjerovatnoće pouzdanosti u bloku za unos podataka ostavljena na zadanoj vrijednosti, tada će posljednje dvije kolone duplicirati prethodne. Ako je korisnik unio vrijednost pouzdanosti, posljednje dvije kolone sadrže donju i gornju graničnu vrijednost za navedeni nivo pouzdanosti.
6. Analiza varijanse, linije sadrže vrijednosti koeficijenata, standardne greške, t- statističar, P-vrijednosti i intervali povjerenja za odgovarajuće .
7. Blokirajte Povlačenje bilansa sadrži predviđene vrijednosti y(u našoj notaciji ovo je ) i ostaci .
Ovo je najčešći način da se pokaže zavisnost neke varijable od drugih, na primjer, kako nivo BDP-a od veličine strana ulaganja ili od Aktivna stopa Narodne banke ili od cijene ključnih energenata.
Modeliranje vam omogućava da pokažete veličinu ove zavisnosti (koeficijenata), zahvaljujući čemu možete napraviti direktnu prognozu i izvršiti neku vrstu planiranja na osnovu ovih prognoza. Takođe, na osnovu regresione analize moguće je donositi upravljačke odluke koje imaju za cilj stimulisanje prioritetnih uzroka koji utiču na konačni rezultat, a sam model će pomoći u identifikaciji ovih faktora prioriteta.
Opšti pogled na model linearne regresije:
Y=a 0 +a 1 x 1 +...+a k x k
Gdje a - regresijski parametri (koeficijenti), x - faktori uticaja, k - broj faktora modela.
Početni podaci
Među početnim podacima potreban nam je određeni skup podataka koji bi predstavljao nekoliko uzastopnih ili međusobno povezanih vrijednosti konačnog parametra Y (npr. BDP) i isti broj vrijednosti indikatora čiji utjecaj proučavamo ( na primjer, strane investicije).
Na slici iznad prikazana je tabela sa istim tim početnim podacima, Y je indikator ekonomski aktivnog stanovništva, a broj preduzeća, visina ulaganja u kapital i prihodi domaćinstva su uticajni faktori, odnosno X-ovi.
Na osnovu slike takođe se može doneti pogrešan zaključak da se modeliranje može raditi samo o vremenskim serijama, odnosno serijama trenutaka snimljenim sekvencijalno u vremenu, ali to nije slučaj; sa istim uspehom se može modelirati u smislu strukture , na primjer, vrijednosti navedene u tabeli mogu se raščlaniti ne po godini, već po regiji.
Za izgradnju adekvatnih linearnih modela poželjno je da izvorni podaci nemaju jake padove ili kolapse; u takvim slučajevima je preporučljivo izvršiti izravnavanje, ali o glađivanju ćemo sljedeći put.
Paket analiza
Parametri linearnog regresijskog modela mogu se izračunati i ručno koristeći metodu uobičajenih najmanjih kvadrata (OLS), ali to oduzima dosta vremena. Ovo se može izračunati malo brže koristeći istu metodu koristeći formule u Excelu, gdje će program sam obaviti proračune, ali ćete i dalje morati ručno unositi formule.
Excel ima dodatak Paket analiza, što je prilično moćan alat za pomoć analitičaru. Ovaj komplet alata, između ostalog, može izračunati parametre regresije koristeći istu metodu najmanjih kvadrata, u samo nekoliko klikova.U stvari, kako koristiti ovaj alat će se dalje raspravljati.
Aktivirajte paket za analizu
Prema zadanim postavkama, ovaj dodatak je onemogućen i nećete ga pronaći u meniju kartica, pa ćemo pogledati korak po korak kako ga aktivirati.
U Excelu, u gornjem lijevom kutu, aktivirajte karticu File, u meniju koji se otvori potražite stavku Opcije i kliknite na njega.
U prozoru koji se otvori, na lijevoj strani, potražite stavku Dodaci i aktivirajte ga, u ovoj kartici na dnu će se nalaziti padajući kontrolni popis, gdje će po defaultu biti napisano Excel dodaci, nalaziće se dugme desno od padajuće liste Idi, potrebno je da kliknete na njega.
Iskačući prozor će od vas tražiti da odaberete dostupne dodatke; u njemu morate označiti okvir Paket analiza a istovremeno, za svaki slučaj, Pronalaženje rješenja(takođe korisna stvar), a zatim potvrdite svoj izbor klikom na dugme uredu.
Upute za pronalaženje parametara linearne regresije pomoću paketa za analizu
Nakon aktiviranja dodatka Analysis Pack, on će uvijek biti dostupan na kartici glavnog izbornika Podaci ispod linka Analiza podataka
U aktivnom prozoru alata Analiza podataka sa liste mogućnosti koje tražimo i biramo Regresija
Zatim će se otvoriti prozor za postavljanje i odabir izvornih podataka za izračunavanje parametara regresijskog modela. Ovdje trebate navesti intervale početnih podataka, odnosno parametar koji se opisuje (Y) i faktore koji na njega utječu (X), kao što je prikazano na donjoj slici; preostale parametre, u principu, nije potrebno konfigurirati.
Nakon što odaberete izvorne podatke i kliknete na dugme U redu, Excel proizvodi proračune na novom listu aktivne radne knjige (osim ako nije drugačije podešeno u postavkama), ovi proračuni izgledaju ovako:
Ključne ćelije su popunjene žutom bojom, to su one na koje prije svega treba obratiti pažnju, bitni su i ostali značajni parametri, ali njihova detaljna analiza vjerovatno zahtijeva poseban post.
dakle, 0,865 - Ovo R 2- koeficijent determinacije, koji pokazuje da 86,5% izračunatih parametara modela, odnosno samog modela objašnjava zavisnost i promjene parametra koji se proučava - Y od proučavanih faktora - X. Ako je preterano, onda ovo je pokazatelj kvaliteta modela i što je veći, to bolje. Jasno je da ne može biti veći od 1 i da se smatra dobrim kada je R2 iznad 0,8, a ako je manji od 0,5, onda se razumnost takvog modela može sa sigurnošću dovesti u pitanje.
Sada idemo na koeficijenti modela:
2079,85
- Ovo a 0- koeficijent koji pokazuje koliki će biti Y ako su svi faktori korišteni u modelu jednaki 0, podrazumijeva se da je to ovisnost o drugim faktorima koji nisu opisani u modelu;
-0,0056
- a 1- koeficijent koji pokazuje težinu uticaja faktora x 1 na Y, odnosno broj preduzeća u okviru datog modela utiče na indikator ekonomski aktivnog stanovništva sa težinom od samo -0,0056 (prilično mali stepen uticaja). ). Znak minus pokazuje da je ovaj uticaj negativan, odnosno što je više preduzeća, manje je ekonomski aktivno stanovništvo, ma koliko to bilo paradoksalno u značenju;
-0,0026
- a 2- koeficijent uticaja obima ulaganja u kapital na veličinu ekonomski aktivnog stanovništva, prema modelu i ovaj uticaj je negativan;
0,0028
- a 3- koeficijent uticaja dohotka stanovništva na veličinu ekonomski aktivnog stanovništva, ovde je uticaj pozitivan, odnosno, prema modelu, povećanje dohotka će doprineti povećanju veličine ekonomski aktivnog stanovništva.
Sakupimo izračunate koeficijente u model:
Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3
Zapravo, ovo je model linearne regresije, koji za početne podatke korištene u primjeru izgleda upravo ovako.
Model procjene i prognoza
Kao što smo već napomenuli, model je izgrađen ne samo da pokaže veličinu zavisnosti parametra koji se proučava od uticajnih faktora, već i tako da je, poznavajući ove faktore uticaja, moguće napraviti predviđanje. Izrada ove prognoze je prilično jednostavna; samo trebate zamijeniti vrijednosti faktora utjecaja umjesto odgovarajućih X u rezultirajućoj jednadžbi modela. Na slici ispod, ovi proračuni su napravljeni u Excelu u posebnoj koloni.
Stvarne vrijednosti (one koje su se dogodile u stvarnosti) i izračunate vrijednosti prema modelu na istoj slici su prikazane u obliku grafikona kako bi se prikazala razlika, a samim tim i greška modela.
Ponavljam još jednom, da bi se napravila prognoza korištenjem modela, potrebno je da postoje poznati faktori utjecaja, a ako je riječ o vremenskoj seriji i, shodno tome, prognozi za budućnost, npr. godine ili mjeseca, onda nije uvijek moguće saznati koji će faktori biti uticajni u ovoj budućnosti. U takvim slučajevima potrebno je napraviti i prognozu uticajnih faktora, najčešće se to radi pomoću autoregresivnog modela – modela u kojem su faktori utjecaja predmet proučavanja i vrijeme, odnosno ovisnost indikatora. po uzoru na ono što je bilo u prošlosti.
Kako izgraditi autoregresivni model ćemo pogledati u sljedećem članku, ali sada pretpostavimo da znamo koje će vrijednosti faktora utjecaja biti u budućem periodu (u primjeru 2008.) i zamjenom ovih vrijednosti u proračune ćemo dobiti našu prognozu za 2008.
Regresiona analiza je jedna od najpopularnijih metoda statističkog istraživanja. Može se koristiti za utvrđivanje stepena uticaja nezavisnih varijabli na zavisnu varijablu. Microsoft Excel ima alate dizajnirane za obavljanje ove vrste analize. Pogledajmo šta su i kako ih koristiti.
Povezivanje paketa za analizu
Ali, da biste koristili funkciju koja vam omogućava da izvršite regresijsku analizu, prvo morate aktivirati paket analize. Tek tada će se alati potrebni za ovu proceduru pojaviti na Excel traci.
- Pređite na karticu "Datoteka".
- Idite na odjeljak "Postavke".
- Otvara se prozor sa opcijama programa Excel. Idite na pododjeljak "Dodaci".
- Na samom dnu prozora koji se otvori, pomaknite prekidač u bloku “Upravljanje” na poziciju “Excel dodaci”, ako je u drugom položaju. Kliknite na dugme „Idi“.
- Otvara se prozor dostupnih Excel dodataka. Označite okvir pored “Paket analize”. Kliknite na dugme “OK”.
Sada, kada odemo na karticu "Podaci", na traci u bloku alata "Analiza" vidjet ćemo novo dugme - "Analiza podataka".
Vrste regresijske analize
Postoji nekoliko vrsta regresije:
- parabolični;
- sedate;
- logaritamski;
- eksponencijalni;
- demonstrativna;
- hiperbolično;
- linearna regresija.
O izvođenju posljednje vrste regresione analize u Excel-u ćemo detaljnije govoriti kasnije.
Linearna regresija u Excelu
Ispod je, kao primjer, tabela koja prikazuje prosječnu dnevnu temperaturu zraka napolju i broj kupaca trgovine za odgovarajući radni dan. Hajde da saznamo pomoću regresione analize kako tačno vremenske prilike u vidu temperature vazduha mogu uticati na posećenost maloprodajnog objekta.
Opšta jednačina linearne regresije je sljedeća: Y = a0 + a1x1 +…+ akhk. U ovoj formuli, Y označava varijablu na koju pokušavamo da proučavamo uticaj faktora. U našem slučaju to je broj kupaca. Vrijednost x su različiti faktori koji utiču na varijablu. Parametri a su koeficijenti regresije. Odnosno, oni su ti koji određuju značaj određenog faktora. Indeks k označava ukupan broj ovih istih faktora.
Analiza rezultata analize
Rezultati regresione analize se prikazuju u obliku tabele na mestu navedenom u podešavanjima.
Jedan od glavnih indikatora je R-kvadrat. To ukazuje na kvalitet modela. U našem slučaju ovaj koeficijent iznosi 0,705 ili oko 70,5%. Ovo je prihvatljiv nivo kvaliteta. Zavisnost manja od 0,5 je loša.
Još jedan važan indikator nalazi se u ćeliji na raskrsnici reda „Y-presjek” i kolone „Koeficijenti”. Ovo pokazuje koju će vrijednost imati Y, a u našem slučaju to je broj kupaca, sa svim ostalim faktorima jednakim nuli. U ovoj tabeli ova vrijednost je 58,04.
Vrednost na preseku kolona „Varijabla X1” i „Koeficijenti” pokazuje nivo zavisnosti Y od X. U našem slučaju, to je nivo zavisnosti broja kupaca prodavnice od temperature. Koeficijent od 1,31 smatra se prilično visokim indikatorom uticaja.
Kao što vidite, koristeći Microsoft Excel prilično je lako napraviti tabelu regresijske analize. Ali samo obučena osoba može raditi s izlaznim podacima i razumjeti njihovu suštinu.
Drago nam je da smo mogli da Vam pomognemo da rešite problem.
Postavite svoje pitanje u komentarima, detaljno opišite suštinu problema. Naši stručnjaci će pokušati odgovoriti što je prije moguće.
Da li vam je ovaj članak pomogao?
Metoda linearne regresije nam omogućava da opišemo pravu liniju koja najbolje odgovara nizu uređenih parova (x, y). Jednačina za pravu liniju, poznata kao linearna jednačina, data je u nastavku:
ŷ - očekivana vrijednost y za datu vrijednost x,
x - nezavisna varijabla,
a - segment na y-osi za pravu liniju,
b je nagib prave linije.
Slika ispod grafički ilustruje ovaj koncept:
Na slici iznad prikazana je linija opisana jednadžbom ŷ =2+0,5x. Y-presjek je tačka u kojoj prava siječe y-osu; u našem slučaju, a = 2. Nagib prave, b, odnos uspona prave i dužine prave, ima vrijednost 0,5. Pozitivan nagib znači da se linija diže s lijeva na desno. Ako je b = 0, linija je horizontalna, što znači da nema veze između zavisnih i nezavisnih varijabli. Drugim riječima, promjena vrijednosti x ne utiče na vrijednost y.
ŷ i y se često brkaju. Na grafikonu je prikazano 6 uređenih parova tačaka i prava, prema datoj jednačini
Ova slika prikazuje tačku koja odgovara uređenom paru x = 2 i y = 4. Imajte na umu da je očekivana vrijednost y prema liniji na X= 2 je ŷ. To možemo potvrditi sljedećom jednačinom:
ŷ = 2 + 0,5h =2 +0,5(2) =3.
Vrijednost y predstavlja stvarnu tačku, a vrijednost ŷ je očekivana vrijednost y pomoću linearne jednačine za datu vrijednost x.
Sljedeći korak je određivanje linearne jednačine koja najbolje odgovara skupu uređenih parova, o tome smo govorili u prethodnom članku, gdje smo metodom najmanjih kvadrata odredili oblik jednačine.
Korištenje Excela za definiranje linearne regresije
Da biste koristili alat za regresijsku analizu ugrađen u Excel, morate aktivirati dodatak Paket analiza. Možete ga pronaći klikom na karticu Datoteka -> Opcije(2007+), u dijaloškom okviru koji se pojavi OpcijeExcel idite na karticu Dodaci. Na terenu Kontrola izabrati DodaciExcel i kliknite Idi. U prozoru koji se pojavi označite polje pored Paket analiza, kliknite UREDU.
U kartici Podaci u grupi Analiza pojaviće se novo dugme Analiza podataka.
Da bismo demonstrirali kako funkcioniše dodatak, koristimo podatke iz prethodnog članka, gdje momak i djevojka dijele sto u kupatilu. Unesite podatke iz našeg primjera kade u kolone A i B praznog lista.
Idite na karticu podaci, u grupi Analiza kliknite Analiza podataka. U prozoru koji se pojavi Analiza podataka izaberite Regresija kao što je prikazano na slici i kliknite na OK.
Postavite potrebne parametre regresije u prozoru Regresija, kao što je prikazano na slici:
Kliknite UREDU. Na slici ispod prikazani su dobijeni rezultati:
Ovi rezultati su u skladu s onima koje smo dobili vlastitim proračunima u prethodnom članku.
Regresiona analiza je statistička metoda istraživanja koja vam omogućava da pokažete ovisnost određenog parametra o jednoj ili više nezavisnih varijabli. U predkompjuterskoj eri, njegova upotreba je bila prilično teška, posebno kada su u pitanju velike količine podataka. Danas, nakon što ste naučili kako izgraditi regresiju u Excelu, možete riješiti složene statističke probleme za samo nekoliko minuta. U nastavku su dati konkretni primjeri iz oblasti ekonomije.
Vrste regresije
Ovaj koncept je u matematiku uveo Francis Galton 1886. Regresija se dešava:
- linearno;
- parabolični;
- sedate;
- eksponencijalni;
- hiperbolično;
- demonstrativna;
- logaritamski.
Primjer 1
Razmotrimo problem utvrđivanja zavisnosti broja članova tima koji su odustali od prosječne plate u 6 industrijskih preduzeća.
Zadatak. U šest preduzeća analizirana je prosječna mjesečna plata i broj zaposlenih koji su dobrovoljno dali otkaz. U tabelarnom obliku imamo:
Za zadatak utvrđivanja zavisnosti broja radnika koji napuštaju rad od prosječne plate u 6 preduzeća, regresijski model ima oblik jednačine Y = a0 + a1×1 +…+akxk, gdje su hi uticajne varijable, ai su koeficijenti regresije, a k je broj faktora.
Za ovaj zadatak Y je indikator zaposlenih koji su dali otkaz, a faktor koji utiče je plata koju označavamo sa X.
Korištenje mogućnosti Excel procesora proračunskih tablica
Regresionoj analizi u Excelu mora prethoditi primjena ugrađenih funkcija na postojeće tabelarne podatke. Međutim, u ove svrhe bolje je koristiti vrlo koristan dodatak „Analysis Pack“. Da biste ga aktivirali potrebno vam je:
- sa kartice “Datoteka” idite na odjeljak “Opcije”;
- u prozoru koji se otvori odaberite redak "Dodaci";
- kliknite na dugme „Idi“ koje se nalazi ispod, desno od linije „Upravljanje“;
- označite polje pored naziva „Paket analize“ i potvrdite svoje radnje klikom na „U redu“.
Ako je sve urađeno kako treba, potrebno dugme će se pojaviti na desnoj strani kartice „Podaci“, koja se nalazi iznad Excel radnog lista.
Linearna regresija u Excelu
Sada kada imamo sve potrebne virtuelne alate pri ruci za izvođenje ekonometrijskih proračuna, možemo početi rješavati naš problem. Za ovo:
- Kliknite na dugme „Analiza podataka“;
- u prozoru koji se otvori kliknite na dugme "Regresija";
- na kartici koja se pojavi unesite raspon vrijednosti za Y (broj zaposlenih koji su napustili) i za X (njihove plate);
- Svoje radnje potvrđujemo pritiskom na dugme "OK".
Kao rezultat, program će automatski popuniti novu tabelu podacima regresione analize. Bilješka! Excel vam omogućava da ručno postavite željenu lokaciju u tu svrhu. Na primjer, ovo može biti isti list na kojem se nalaze Y i X vrijednosti, ili čak nova radna knjiga posebno dizajnirana za pohranjivanje takvih podataka.
Analiza rezultata regresije za R-kvadrat
U Excelu podaci dobijeni tokom obrade podataka u razmatranom primjeru imaju oblik:
Prije svega, obratite pažnju na vrijednost R-kvadrata. Predstavlja koeficijent determinacije. U ovom primjeru R-kvadrat = 0,755 (75,5%), odnosno izračunati parametri modela objašnjavaju odnos između parametara koji se razmatraju za 75,5%. Što je veća vrijednost koeficijenta determinacije, to je odabrani model pogodniji za određeni zadatak. Smatra se da ispravno opisuje stvarnu situaciju kada je vrijednost R-kvadrata iznad 0,8. Ako je R-kvadrat tcr, onda se hipoteza o beznačajnosti slobodnog člana linearne jednačine odbacuje.
U zadatku koji se razmatra za slobodni termin, korišćenjem Excel alata, dobijeno je da je t = 169,20903, a p = 2,89E-12, odnosno da imamo nultu verovatnoću da će tačna hipoteza o beznačajnosti slobodnog člana biti odbačena . Za koeficijent za nepoznatu t=5,79405, i p=0,001158. Drugim riječima, vjerovatnoća da će tačna hipoteza o beznačajnosti koeficijenta za nepoznatu biti odbačena je 0,12%.
Stoga se može tvrditi da je rezultirajuća jednačina linearne regresije adekvatna.
Problem izvodljivosti kupovine paketa akcija
Višestruka regresija u Excelu se izvodi pomoću istog alata za analizu podataka. Razmotrimo konkretan problem aplikacije.
Menadžment kompanije NNN mora odlučiti o preporučljivosti kupovine 20% udjela u MMM ad. Cijena paketa (SP) je 70 miliona američkih dolara. Stručnjaci NNN-a prikupili su podatke o sličnim transakcijama. Odlučeno je da se vrijednost paketa dionica procijeni prema takvim parametrima, izraženim u milionima američkih dolara, kao što su:
- obaveze prema dobavljačima (VK);
- godišnji obim prometa (VO);
- potraživanja (VD);
- trošak osnovnih sredstava (COF).
Pored toga, koristi se parametar zaostalih plata preduzeća (V3 P) u hiljadama američkih dolara.
Rješenje pomoću Excel procesora proračunskih tablica
Prije svega, trebate kreirati tabelu izvornih podataka. izgleda ovako:
- pozovite prozor „Analiza podataka“;
- odaberite odjeljak "Regresija";
- U polje „Input interval Y“ unesite raspon vrijednosti zavisnih varijabli iz stupca G;
- Kliknite na ikonu sa crvenom strelicom desno od prozora „Input interval X“ i označite raspon svih vrijednosti iz kolona B, C, D, F na listu.
Označite stavku „Novi radni list“ i kliknite na „U redu“.
Nabavite regresionu analizu za dati problem.
Studija rezultata i zaključaka
"Skupljamo" jednadžbu regresije iz zaokruženih podataka prikazanih gore u Excel tabeli:
SP = 0,103*SOF + 0,541*VO – 0,031*VK +0,405*VD +0,691*VZP – 265,844.
U poznatijem matematičkom obliku, može se napisati kao:
y = 0,103*x1 + 0,541*x2 – 0,031*x3 +0,405*x4 +0,691*x5 – 265,844
Podaci za MMM dd prikazani su u tabeli:
Zamijenivši ih u jednadžbu regresije, dobijamo cifru od 64,72 miliona američkih dolara. To znači da se akcije MMM ad ne vrede kupovati, jer je njihova vrednost od 70 miliona američkih dolara prilično naduvana.
Kao što vidite, upotreba Excel tabele i regresione jednačine omogućila je donošenje informisane odluke u vezi izvodljivosti vrlo specifične transakcije.
Sada znate šta je regresija. Primjeri u Excelu o kojima smo gore govorili pomoći će vam da riješite praktične probleme u oblasti ekonometrije.
MS Excel paket vam omogućava da većinu posla obavite vrlo brzo prilikom konstruisanja jednačine linearne regresije. Važno je razumjeti kako interpretirati dobijene rezultate. Da biste napravili regresijski model, morate odabrati Tools\Data Analysis\Regression (u Excel 2007 ovaj način je u bloku Podaci/Analiza podataka/Regresija). Zatim kopirajte rezultate u blok za analizu.
Regresijska i korelaciona analiza su statističke metode istraživanja. Ovo su najčešći načini da se pokaže zavisnost parametra od jedne ili više nezavisnih varijabli.
U nastavku ćemo, koristeći konkretne praktične primjere, razmotriti ove dvije vrlo popularne analize među ekonomistima. Navest ćemo i primjer dobijanja rezultata pri njihovom kombinovanju.
Regresiona analiza u Excelu
Pokazuje uticaj nekih vrednosti (nezavisnih, nezavisnih) na zavisnu varijablu. Na primjer, kako broj ekonomski aktivnog stanovništva zavisi od broja preduzeća, plata i drugih parametara. Ili: kako strane investicije, cijene energije itd. utiču na nivo BDP-a.
Rezultat analize vam omogućava da istaknete prioritete. I na osnovu glavnih faktora predvidjeti, planirati razvoj prioritetnih oblasti i donijeti upravljačke odluke.
Regresija se dešava:
- linearni (y = a + bx);
- parabolični (y = a + bx + cx 2);
- eksponencijalni (y = a * exp(bx));
- snaga (y = a*x^b);
- hiperbolično (y = b/x + a);
- logaritamski (y = b * 1n(x) + a);
- eksponencijalni (y = a * b^x).
Pogledajmo primjer izgradnje regresijskog modela u Excelu i interpretacije rezultata. Uzmimo linearni tip regresije.
Zadatak. U 6 preduzeća analizirana je prosječna mjesečna plata i broj zaposlenih koji su otpustili. Potrebno je utvrditi zavisnost broja zaposlenih koji napuštaju rad od prosječne plate.
Model linearne regresije izgleda ovako:
Y = a 0 + a 1 x 1 +…+a k x k.
Gdje su a koeficijenti regresije, x su utjecajne varijable, k je broj faktora.
U našem primjeru, Y je indikator napuštanja zaposlenika. Faktor uticaja su plate (x).
Excel ima ugrađene funkcije koje vam mogu pomoći da izračunate parametre modela linearne regresije. Ali dodatak “Paket analize” će to učiniti brže.
Aktiviramo moćan analitički alat:
Kada se aktivira, dodatak će biti dostupan na kartici Podaci.
Sada uradimo samu regresijsku analizu.
Prije svega, obraćamo pažnju na R-kvadrat i koeficijente.
R-kvadrat je koeficijent determinacije. U našem primjeru – 0,755, odnosno 75,5%. To znači da izračunati parametri modela objašnjavaju 75,5% odnosa između proučavanih parametara. Što je veći koeficijent determinacije, to je model bolji. Dobro - iznad 0,8. Loše – manje od 0,5 (ovakva analiza se teško može smatrati razumnom). U našem primjeru – “nije loše”.
Koeficijent 64,1428 pokazuje koliki će biti Y ako su sve varijable u modelu koji se razmatraju jednake 0. Odnosno, na vrijednost analiziranog parametra utiču i drugi faktori koji nisu opisani u modelu.
Koeficijent -0,16285 pokazuje težinu varijable X na Y. Odnosno, prosječna mjesečna plata u okviru ovog modela utiče na broj onih koji odustaju sa ponderom od -0,16285 (ovo je mali stepen uticaja). Znak “-” ukazuje na negativan uticaj: što je veća plata, manje ljudi daje otkaz. Što je pošteno.
Analiza korelacije u Excel-u
Korelaciona analiza pomaže da se utvrdi da li postoji veza između indikatora u jednom ili dva uzorka. Na primjer, između vremena rada mašine i troškova popravki, cijene opreme i trajanja rada, visine i težine djece itd.
Ako postoji veza, da li povećanje jednog parametra dovodi do povećanja (pozitivna korelacija) ili smanjenja (negativna) drugog. Korelaciona analiza pomaže analitičaru da utvrdi da li se vrednost jednog indikatora može koristiti za predviđanje moguće vrednosti drugog.
Koeficijent korelacije je označen sa r. Varira od +1 do -1. Klasifikacija korelacija za različita područja bit će različita. Kada je koeficijent 0, ne postoji linearna veza između uzoraka.
Pogledajmo kako pronaći koeficijent korelacije koristeći Excel.
Za pronalaženje uparenih koeficijenata koristi se CORREL funkcija.
Cilj: Utvrditi postoji li veza između vremena rada tokarilice i troškova njenog održavanja.
Postavite kursor u bilo koju ćeliju i pritisnite dugme fx.
- U kategoriji “Statistički” odaberite funkciju CORREL.
- Argument “Niz 1” - prvi raspon vrijednosti – vrijeme rada mašine: A2:A14.
- Argument "Niz 2" - drugi raspon vrijednosti - cijena popravke: B2:B14. Kliknite OK.
Da biste odredili vrstu veze, potrebno je pogledati apsolutni broj koeficijenta (svako polje aktivnosti ima svoju skalu).
Za korelacione analize nekoliko parametara (više od 2) pogodnije je koristiti „Analizu podataka“ (dodatak „Paket analize“). Potrebno je da izaberete korelaciju sa liste i odredite niz. Sve.
Dobijeni koeficijenti će biti prikazani u korelacionoj matrici. Volim ovo:
Korelaciona i regresiona analiza
U praksi se ove dvije tehnike često koriste zajedno.
primjer:
Sada su podaci regresione analize postali vidljivi.
