Primjer korištenja regresione analize. Korelaciona i regresiona analiza u Excelu: uputstva za izvođenje. Hiperbolički, linearni i logaritamski

6. Glavni indikator koji odražava kvalitet regresione analize je koeficijent determinacije (R 2).

Koeficijent determinacije pokazuje koliki je udio zavisne varijable " at" uzima se u obzir u analizi i uzrokovan je uticajem na njega faktora uključenih u analizu.

Koeficijent determinacije (R 2) uzima vrijednosti u intervalu . Jednačina regresije je kvalitativna ako R 2 ≥0,8.

Koeficijent determinacije jednak je kvadratu koeficijenta korelacije, tj.

Primjer 6.1. Koristeći sljedeće podatke, konstruirajte i analizirajte jednadžbu regresije:

Rješenje.

1) Izračunajte koeficijent korelacije: . Odnos između znakova je direktan i umjeren.

2) Konstruirajte uparenu jednačinu linearne regresije.

2.1) Kreirajte proračunsku tabelu.

,

Uparena jednadžba linearne regresije: y x =25,17+0,087x.

3) Pronađite teorijske vrijednosti " y x"zamjenom stvarnih vrijednosti u jednadžbu regresije" X».

4) Napravite grafikone stvarnih " y" i teorijske vrijednosti" y x"efikasna karakteristika (slika 6.1):r xy =0,47) i mali broj zapažanja.

7) Izračunajte koeficijent determinacije: R 2=(0,47) 2 =0,22. Konstruisana jednačina je lošeg kvaliteta.

Jer proračuni pri izvođenju regresione analize su prilično opsežni, preporučuje se korištenje posebnih programa (Statistica 10, SPSS, itd.).

Na slici 6.2 prikazana je tabela sa rezultatima regresione analize izvršene pomoću programa Statistica 10.

Slika 6.2. Rezultati regresione analize rađene pomoću programa Statistica 10

5. Literatura:

1. Gmurman V.E. Teorija vjerovatnoće i matematička statistika: Udžbenik. priručnik za univerzitete / V.E. Gmurman. - M.: Viša škola, 2003. - 479 str.

2. Koychubekov B.K. Biostatistika: Udžbenik. - Almaty: Evero, 2014. - 154 str.

3. Lobotskaya N.L. Viša matematika. / N.L. Lobotskaya, Yu.V. Morozov, A.A. Dunaev. - Mn.: Viša škola, 1987. - 319 str.

4. Medić V.A., Tokmačev M.S., Fishman B.B. Statistika u medicini i biologiji: Vodič. U 2 toma / Ed. Yu.M. Komarova. T. 1. Teorijska statistika. - M.: Medicina, 2000. - 412 str.

5. Primjena metoda statističke analize za proučavanje javnog zdravlja i zdravstvene zaštite: udžbenik / ur. Kucherenko V.Z. - 4. izd., revidirano. i dodatne – M.: GEOTAR - Mediji, 2011. - 256 str.

Svrha regresione analize je mjerenje odnosa između zavisne varijable i jedne (parna regresiona analiza) ili više (više) nezavisnih varijabli. Nezavisne varijable se takođe nazivaju faktorske, eksplanatorne, determinantne, regresorske i prediktorske varijable.

Zavisna varijabla se ponekad naziva određena, objašnjena ili “odgovorna” varijabla. Izuzetno rasprostranjena upotreba regresione analize u empirijskim istraživanjima nije samo zbog činjenice da je ona pogodan alat za testiranje hipoteza. Regresija, posebno višestruka regresija, je efikasan metod za modeliranje i predviđanje.

Počnimo objašnjavati principe rada s regresijskom analizom jednostavnijom - metodom parova.

Uparena regresijska analiza

Prvi koraci pri korištenju regresione analize bit će gotovo identični onima koje smo poduzeli pri izračunavanju koeficijenta korelacije. Za višestruku regresiju relevantna su i tri glavna uslova za efikasnost korelacione analize korišćenjem Pirsonove metode – normalna distribucija varijabli, intervalno merenje varijabli, linearni odnos između varijabli. Shodno tome, u prvoj fazi se konstruišu dijagrami raspršenja, vrši se statistička i deskriptivna analiza varijabli i izračunava regresijska linija. Kao iu okviru korelacione analize, regresijske linije se konstruišu metodom najmanjih kvadrata.

Da bismo jasnije ilustrovali razlike između ove dve metode analize podataka, okrenimo se već diskutovanom primeru sa varijablama „Podrška SPS“ i „udeo ruralnog stanovništva“. Izvorni podaci su identični. Razlika u dijagramima raspršenja će biti u tome što je u regresionoj analizi ispravno iscrtati zavisnu varijablu – u našem slučaju, “SPS podršku” na Y-osi, dok u korelacionoj analizi to nije bitno. Nakon čišćenja odstupanja, dijagram raspršenosti izgleda ovako:

Osnovna ideja regresijske analize je da je, imajući opći trend za varijable - u obliku regresijske linije - moguće predvidjeti vrijednost zavisne varijable, s obzirom na vrijednosti nezavisne.

Zamislimo običnu matematičku linearnu funkciju. Svaka prava linija u Euklidskom prostoru može se opisati formulom:

gdje je a konstanta koja specificira pomak duž ordinatne ose; b je koeficijent koji određuje ugao nagiba linije.

Poznavajući nagib i konstantu, možete izračunati (predvidjeti) vrijednost y za bilo koji x.

Ova najjednostavnija funkcija činila je osnovu modela regresione analize uz upozorenje da nećemo tačno predviđati vrijednost y, već unutar određenog intervala povjerenja, tj. otprilike.

Konstanta je tačka preseka linije regresije i y-ose (F-presek, koji se obično označava kao „presretač” u statističkim paketima). U našem primjeru sa glasanjem za Savez desnih snaga, njegova zaokružena vrijednost će biti 10,55. Ugaoni koeficijent b će biti približno -0,1 (kao u korelacionoj analizi, znak pokazuje vrstu veze - direktnu ili inverznu). Tako će rezultujući model imati oblik SP C = -0,1 x Sel. nas. + 10.55.

Dakle, za slučaj „Republike Adigeje“ sa udelom ruralnog stanovništva od 47%, predviđena vrednost će biti 5,63:

ATP = -0,10 x 47 + 10,55 = 5,63.

Razlika između originalne i predviđene vrijednosti naziva se ostatak (s tim pojmom, koji je fundamentalan za statistiku, već smo se susreli pri analizi kontingentnih tablica). Dakle, za slučaj "Republike Adigeje" ostatak će biti jednak 3,92 - 5,63 = -1,71. Što je veća modularna vrijednost ostatka, to je manje uspješno predviđena vrijednost.

Analiza omjera početnih i predviđenih vrijednosti služi za procjenu kvaliteta rezultirajućeg modela i njegove prediktivne sposobnosti. Jedan od glavnih pokazatelja statistike regresije je koeficijent višestruke korelacije R - koeficijent korelacije između originalne i predviđene vrijednosti zavisne varijable. U parnoj regresionoj analizi jednak je uobičajenom Pirsonovom koeficijentu korelacije između zavisnih i nezavisnih varijabli, u našem slučaju - 0,63. Za smisleno tumačenje višestrukog R, mora se pretvoriti u koeficijent determinacije. To se radi na isti način kao u korelacionoj analizi - kvadriranjem. Koeficijent determinacije R-kvadrat (R 2) pokazuje proporciju varijacije zavisne varijable koja je objašnjena nezavisnom varijablom(ama).

U našem slučaju, R 2 = 0,39 (0,63 2); to znači da varijabla „udio ruralnog stanovništva“ objašnjava otprilike 40% varijacije u varijabli „SPS podrška“. Što je veći koeficijent determinacije, to je veći kvalitet modela.

Drugi pokazatelj kvaliteta modela je standardna greška procjene. Ovo je mjera koliko su tačke „rasute“ oko regresijske linije. Mjera širenja za intervalne varijable je standardna devijacija. Prema tome, standardna greška procjene je standardna devijacija distribucije reziduala. Što je veća njegova vrijednost, veći je raspršivanje i lošiji je model. U našem slučaju, standardna greška je 2,18. Upravo za taj iznos će naš model „prosečno grešiti” prilikom predviđanja vrednosti varijable „SPS podrška”.

Regresijska statistika također uključuje analizu varijanse. Uz njegovu pomoć saznajemo: 1) koliki je udio varijacije (disperzije) zavisne varijable objašnjen nezavisnom varijablom; 2) koliki deo varijanse zavisne varijable čine ostaci (neobjašnjivi deo); 3) koliki je omjer ove dvije veličine (/"-odnos). Statistika disperzije je posebno važna za studije uzorka - pokazuje koliko je vjerovatno da postoji veza između nezavisnih i zavisnih varijabli u populaciji. Međutim, za kontinuirane studije (kao u našem primjeru) rezultati studije analize varijanse nisu korisni. U ovom slučaju provjeravaju da li je identificirani statistički obrazac uzrokovan kombinacijom slučajnih okolnosti, koliko je karakterističan za kompleks uslova u kojima se populacija koja se ispituje locira, odnosno utvrđuje se da dobijeni rezultat nije tačan za neki širi opšti agregat, već stepen njegove pravilnosti, oslobođenosti od slučajnih uticaja.

U našem slučaju, ANOVA statistika je sljedeća:

F-razmjer od 54,29 je značajan na nivou od 0,0000000001. U skladu s tim, sa sigurnošću možemo odbaciti nultu hipotezu (da je odnos koji smo otkrili posljedica slučajnosti).

Kriterij t ima sličnu funkciju, ali u odnosu na koeficijente regresije (ugaoni i F-presjek). Koristeći / kriterij testiramo hipotezu da su u općoj populaciji koeficijenti regresije jednaki nuli. U našem slučaju, opet možemo sa sigurnošću odbaciti nultu hipotezu.

Višestruka regresiona analiza

Model višestruke regresije je skoro identičan modelu uparene regresije; jedina razlika je u tome što je nekoliko nezavisnih varijabli sekvencijalno uključeno u linearnu funkciju:

Y = b1X1 + b2X2 + …+ bpXp + a.

Ako postoji više od dvije nezavisne varijable, ne možemo dobiti vizualnu predstavu o njihovom odnosu; u tom smislu, višestruka regresija je manje „vizualna“ od regresije u paru. Kada imate dvije nezavisne varijable, može biti korisno prikazati podatke u 3D dijagramu raspršenja. U profesionalnim statističkim softverskim paketima (na primjer, Statistica) postoji mogućnost rotiranja trodimenzionalnog grafikona, što vam omogućava da vizualno dobro predstavite strukturu podataka.

Kod rada sa višestrukom regresijom, za razliku od parne regresije, potrebno je odrediti algoritam analize. Standardni algoritam uključuje sve dostupne prediktore u konačnom regresijskom modelu. Algoritam korak po korak uključuje sekvencijalno uključivanje (isključivanje) nezavisnih varijabli na osnovu njihove objašnjavajuće „težine“. Stepwise metoda je dobra kada postoji mnogo nezavisnih varijabli; “čisti” model od iskreno slabih prediktora, čineći ga kompaktnijim i konciznijim.

Dodatni uslov za ispravnost višestruke regresije (uz interval, normalnost i linearnost) je odsustvo multikolinearnosti – prisustvo jakih korelacija između nezavisnih varijabli.

Interpretacija statistike višestruke regresije uključuje sve elemente koje smo razmotrili za slučaj parne regresije. Pored toga, postoje i druge važne komponente statistike višestruke regresione analize.

Rad ćemo ilustrovati višestrukom regresijom na primjeru testiranja hipoteza koje objašnjavaju razlike u nivou izborne aktivnosti u ruskim regijama. Specifične empirijske studije sugeriraju da na nivo izlaznosti birača utiču:

Nacionalni faktor (varijabla “Rusko stanovništvo”; operacionalizovano kao udio ruskog stanovništva u konstitutivnim entitetima Ruske Federacije). Pretpostavlja se da povećanje udjela ruskog stanovništva dovodi do smanjenja izlaznosti birača;

Faktor urbanizacije (varijabla “urbano stanovništvo”; operacionalizovano kao udio gradskog stanovništva u konstitutivnim entitetima Ruske Federacije; sa ovim faktorom smo već radili u sklopu korelacione analize). Pretpostavlja se da povećanje udjela gradskog stanovništva dovodi i do smanjenja izlaznosti birača.

Zavisna varijabla - "intenzitet izborne aktivnosti" ("aktivan") operacionalizovana je kroz podatke o prosječnom odzivu po regionima na saveznim izborima od 1995. do 2003. godine. Početna tabela podataka za dvije nezavisne i jednu zavisnu varijablu bit će sljedeća:

itd. (nakon čišćenja emisija ostaje 83 od 88 slučajeva)

Statistike koje opisuju kvalitet modela:

1. Višestruki R = 0,62; L-kvadrat = 0,38. Shodno tome, nacionalni faktor i faktor urbanizacije zajedno objašnjavaju oko 38% varijacije varijable „izborna aktivnost“.

2. Prosječna greška je 3,38. Upravo toliko je „prosječno pogrešan“ konstruirani model kada se predviđa nivo izlaznosti.

3. /l-odnos objašnjene i neobjašnjive varijacije je 25,2 na nivou 0,000000003. Odbacuje se nulta hipoteza o slučajnosti identifikovanih veza.

4. Kriterijum / za konstantne i regresijske koeficijente varijabli „gradsko stanovništvo“ i „rusko stanovništvo“ je značajan na nivou od 0,0000001; 0,00005 i 0,007 respektivno. Nul hipoteza da su koeficijenti slučajni se odbacuje.

Dodatne korisne statistike u analizi odnosa između originalne i predviđene vrijednosti zavisne varijable su Mahalanobisova udaljenost i Cookova udaljenost. Prvi je mjera jedinstvenosti slučaja (pokazuje koliko kombinacija vrijednosti svih nezavisnih varijabli za dati slučaj odstupa od prosječne vrijednosti za sve nezavisne varijable istovremeno). Druga je mjera uticaja slučaja. Različita opažanja imaju različite efekte na nagib linije regresije, a Cookova udaljenost se može koristiti za njihovo upoređivanje na ovom indikatoru. Ovo može biti korisno pri čišćenju izvanrednih vrijednosti (odvojeni slučaj se može smatrati pretjerano utjecajnim slučajem).

U našem primjeru, jedinstveni i utjecajni slučajevi uključuju Dagestan.

Sam regresijski model ima sljedeće parametre: Y-presjek (konstanta) = 75,99; b (horizontalno) = -0,1; Kommersant (rus. nas.) = -0,06. Konačna formula:

Aktivan, = -0,1 x Hor. us.n+- 0,06 x Rus. us.n + 75,99.

Možemo li uporediti „snagu objašnjenja“ prediktora na osnovu vrijednosti koeficijenta 61. U ovom slučaju, da, pošto obje nezavisne varijable imaju isti format procenta. Međutim, najčešće se višestruka regresija bavi varijablama mjerenim na različitim skalama (na primjer, nivo prihoda u rubljama i starost u godinama). Stoga, u opštem slučaju, nije ispravno porediti prediktivne sposobnosti varijabli koristeći koeficijent regresije. U statistici višestruke regresije postoji poseban beta koeficijent (B) za ovu svrhu, koji se posebno izračunava za svaku nezavisnu varijablu. Predstavlja parcijalni (izračunat nakon uzimanja u obzir uticaja svih ostalih prediktora) koeficijent korelacije između faktora i odgovora i pokazuje nezavisni doprinos faktora predviđanju vrednosti odgovora. U parnoj regresionoj analizi, beta koeficijent je razumljivo jednak koeficijentu parne korelacije između zavisne i nezavisne varijable.

U našem primjeru, beta (Highland populacija) = -0,43, beta (Rusko stanovništvo) = -0,28. Dakle, oba faktora negativno utiču na nivo izborne aktivnosti, dok je značaj faktora urbanizacije znatno veći od značaja nacionalnog faktora. Kombinovani uticaj oba faktora određuje oko 38% varijacije varijable „izborna aktivnost” (vidi vrednost L-kvadrata).

Regresiona analiza

Regresija (linearno) analiza- statistička metoda za proučavanje uticaja jedne ili više nezavisnih varijabli na zavisnu varijablu. Nezavisne varijable se inače nazivaju regresori ili prediktori, a zavisne varijable se nazivaju kriterijumske varijable. Terminologija zavisan I nezavisni varijable odražava samo matematičku zavisnost varijabli ( vidi Lažna korelacija), a ne uzročno-posledične veze.

Ciljevi regresijske analize

1. Određivanje stepena determinacije varijacije kriterijumske (zavisne) varijable pomoću prediktora (nezavisnih varijabli)
2. Predviđanje vrijednosti zavisne varijable pomoću nezavisne varijable(e)
3. Određivanje doprinosa pojedinačnih nezavisnih varijabli varijaciji zavisne varijable

Regresionom analizom se ne može utvrditi da li postoji veza između varijabli, jer je postojanje takve veze preduslov za primenu analize.

Matematička definicija regresije

Strogo regresijski odnos može se definirati na sljedeći način. Neka su , slučajne varijable sa datom zajedničkom distribucijom vjerovatnoće. Ako je za svaki skup vrijednosti definirano uvjetno matematičko očekivanje

tada se poziva funkcija regresija vrijednosti Y po vrijednostima, a njegov graf je regresijska linija od , ili jednadžba regresije.

Ovisnost o manifestuje se u promjeni prosječnih vrijednosti Y s promjenom . Iako, za svaki fiksni skup vrijednosti, vrijednost ostaje slučajna varijabla sa određenim raspršenjem.

Da bi se razjasnilo pitanje koliko precizno regresiona analiza procjenjuje promjenu Y pri promjeni, koristi se prosječna vrijednost disperzije Y za različite skupove vrijednosti (u stvari, govorimo o mjeri disperzije zavisne varijable oko linije regresije).

Metoda najmanjih kvadrata (izračunavanje koeficijenata)

U praksi se linija regresije najčešće traži u obliku linearne funkcije (linearna regresija) koja najbolje aproksimira željenu krivu. Ovo se radi korištenjem metode najmanjih kvadrata, kada je zbir kvadrata odstupanja stvarno promatranih od njihovih procjena minimiziran (što znači procjene koristeći pravu liniju koja navodno predstavlja željeni regresijski odnos):

(M - veličina uzorka). Ovaj pristup se zasniva na dobro poznatoj činjenici da iznos koji se pojavljuje u gornjem izrazu poprima minimalnu vrijednost upravo za slučaj kada .

Da bi se riješio problem regresione analize metodom najmanjih kvadrata, uvodi se koncept rezidualne funkcije:

Minimalni uvjet za rezidualnu funkciju:

Rezultirajući sistem je sistem linearnih jednačina sa nepoznatim

Ako slobodne članove na lijevoj strani jednadžbe predstavimo kao matricu

a koeficijenti za nepoznate na desnoj strani su matrica

tada dobijamo matričnu jednačinu: , koja se lako rješava Gaussovom metodom. Rezultirajuća matrica će biti matrica koja sadrži koeficijente jednadžbe regresijske linije:

Da bi se dobile najbolje procjene, potrebno je ispuniti preduslove OLS-a (Gauss–Markov uvjeti). U engleskoj literaturi takve procjene se nazivaju BLUE (Best Linear Unbiased Estimatori).

Interpretacija parametara regresije

Parametri su parcijalni koeficijenti korelacije; tumači se kao udio varijanse Y objašnjenog fiksiranjem utjecaja preostalih prediktora, odnosno mjeri individualni doprinos objašnjenju Y. U slučaju koreliranih prediktora, javlja se problem nesigurnosti u procjenama, koji postaju zavisni od redosleda kojim su prediktori uključeni u model. U takvim slučajevima potrebno je koristiti metode korelacijske i postupne regresione analize.

Kada se govori o nelinearnim modelima regresione analize, važno je obratiti pažnju na to da li je riječ o nelinearnosti u nezavisnim varijablama (sa formalne tačke gledišta, lako svedenoj na linearnu regresiju), ili o nelinearnosti u procijenjenim parametrima (koja uzrokuje ozbiljne računske poteškoće). U slučaju nelinearnosti prvog tipa, sa suštinske tačke gledišta, važno je istaknuti pojavu u modelu pojmova oblika , , što ukazuje na prisustvo interakcija između karakteristika itd. (vidi Multikolinearnost).

vidi takođe

Linkovi

• www.kgafk.ru - Predavanje na temu “Regresiona analiza”
• www.basegroup.ru - metode za odabir varijabli u regresijskim modelima

Književnost

• Norman Draper, Harry Smith Primijenjena regresiona analiza. Višestruka regresija = Primijenjena regresijska analiza. - 3. izd. - M.: "Dijalektika", 2007. - P. 912. - ISBN 0-471-17082-8
• Robusne metode za procjenu statističkih modela: Monografija. - K.: PP "Sansparel", 2005. - P. 504. - ISBN 966-96574-0-7, UDK: 519.237.5:515.126.2, BBK 22.172+22.152
• Radčenko Stanislav Grigorijevič, Metodologija regresione analize: Monografija. - K.: "Korniychuk", 2011. - P. 376. - ISBN 978-966-7599-72-0

Wikimedia Foundation. 2010.

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.

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.

Sada kada pređemo na karticu "Podaci", na vrpci u kutiji s alatima "analiza" videć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, uticaj faktora na kojima pokušavamo da proučavamo. U našem slučaju to je broj kupaca. Značenje x su različiti faktori koji utiču na varijablu. Opcije a su koeficijenti regresije. Odnosno, oni su ti koji određuju značaj određenog faktora. Indeks k označava ukupan broj ovih 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 linije "Y-raskrsnica" i kolona "Šanse". 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.

Vrijednost na presjeku grafa "Varijabla X1" I "Šanse" prikazuje nivo zavisnosti Y od X. U našem slučaju, ovo 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.

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 uveden u matematiku 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 = a 0 + a 1 x 1 +...+a k x k, gdje su x i uticajne varijable, a i su koeficijenti regresije, a k je broj faktora.

Za ovaj problem Y je indikator otpuštanja zaposlenih, a faktor uticaja 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.

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<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Analiza kvota

Broj 64.1428 pokazuje kolika će biti vrijednost Y ako se sve varijable xi u modelu koji razmatramo resetujemo na nulu. Drugim riječima, može se tvrditi da na vrijednost analiziranog parametra utiču i drugi faktori koji nisu opisani u konkretnom modelu.

Sledeći koeficijent -0,16285, koji se nalazi u ćeliji B18, pokazuje težinu uticaja varijable X na Y. To znači da prosečna mesečna plata zaposlenih u okviru modela koji se razmatra utiče na broj onih koji odustaju sa ponderom od -0,16285, tj. stepen njenog uticaja je potpuno mali. Znak "-" označava da je koeficijent negativan. To je očigledno, jer svi znaju da što je veća plata u preduzeću, manje ljudi izražava želju za raskidom ugovora o radu ili otkazom.

Višestruka regresija

Ovaj termin se odnosi na jednadžbu odnosa sa nekoliko nezavisnih varijabli oblika:

y=f(x 1 +x 2 +…x m) + ε, gdje je y rezultantna karakteristika (zavisna varijabla), a x 1, x 2,…x m su faktorske karakteristike (nezavisne varijable).

Procjena parametara

Za višestruku regresiju (MR) provodi se metodom najmanjih kvadrata (OLS). Za linearne jednadžbe oblika Y = a + b 1 x 1 +…+b m x m + ε konstruiramo sistem normalnih jednačina (vidi dolje)

Da biste razumjeli princip metode, razmotrite slučaj sa dva faktora. Tada imamo situaciju opisanu formulom

Odavde dobijamo:

gdje je σ varijansa odgovarajuće karakteristike prikazane u indeksu.

OLS je primjenjiv na MR jednačinu na standardiziranoj skali. U ovom slučaju dobijamo jednačinu:

u kojima su t y, t x 1, … t xm standardizirane varijable, za koje su prosječne vrijednosti jednake 0; β i su standardizirani koeficijenti regresije, a standardna devijacija je 1.

Imajte na umu da su svi β i u ovom slučaju specificirani kao normalizirani i centralizirani, stoga se njihovo međusobno poređenje smatra ispravnim i prihvatljivim. Osim toga, uobičajeno je da se faktori odstrane odbacivanjem onih s najnižim vrijednostima βi.

Problem Korištenje jednadžbe linearne regresije

Pretpostavimo da imamo tabelu dinamike cijena za određeni proizvod N u posljednjih 8 mjeseci. Potrebno je donijeti odluku o preporučljivosti kupovine njegove serije po cijeni od 1850 rubalja/t.

Da biste riješili ovaj problem u Excel procesoru proračunskih tablica, trebate koristiti alat „Analiza podataka“, već poznat iz gore prikazanog primjera. Zatim odaberite odjeljak "Regresija" i postavite parametre. Treba imati na umu da se u polje "Input interval Y" mora unijeti raspon vrijednosti za zavisnu varijablu (u ovom slučaju cijene robe u određenim mjesecima u godini), au "Input interval X" - za nezavisnu varijablu (broj mjeseca). Potvrdite radnju klikom na “OK”. Na novom listu (ako je tako naznačeno) dobijamo podatke za regresiju.

Koristeći ih, konstruišemo linearnu jednačinu oblika y=ax+b, gde su parametri a i b koeficijenti prave sa nazivom broja meseca i koeficijenti i prave „Y-presek“ sa lista sa rezultate regresione analize. Dakle, jednadžba linearne regresije (LR) za zadatak 3 se piše kao:

Cijena proizvoda N = 11.714* broj mjeseca + 1727.54.

ili u algebarskoj notaciji

y = 11,714 x + 1727,54

Analiza rezultata

Da bi se odlučilo da li je rezultirajuća jednačina linearne regresije adekvatna, koriste se koeficijenti višestruke korelacije (MCC) i determinacije, kao i Fisherov test i Student t test. U Excel tabeli sa rezultatima regresije oni se nazivaju višestruki R, R-kvadrat, F-statistika i t-statistika, respektivno.

KMC R omogućava procjenu bliskosti vjerovatnoće veze između nezavisnih i zavisnih varijabli. Njegova visoka vrijednost ukazuje na prilično jaku vezu između varijabli „Broj mjeseci“ i „Cijena proizvoda N u rubljama po 1 toni“. Međutim, priroda ovog odnosa ostaje nepoznata.

Kvadrat koeficijenta determinacije R2 (RI) je numerička karakteristika udjela ukupnog rasipa i pokazuje čiji dio eksperimentalnih podataka, tj. vrijednosti zavisne varijable odgovaraju jednadžbi linearne regresije. U problemu koji se razmatra ova vrijednost je jednaka 84,8%, tj. statistički podaci se opisuju sa visokim stepenom tačnosti rezultujućim SD.

F-statistika, koja se naziva i Fišerov test, koristi se za procenu značaja linearnog odnosa, pobijajući ili potvrđujući hipotezu o njegovom postojanju.

(Studentov test) pomaže da se proceni značaj koeficijenta sa nepoznatim ili slobodnim članom linearne veze. Ako je vrijednost t-testa > tcr, hipoteza o beznačajnosti slobodnog člana linearne jednačine se 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 jednadžbe omogućila je donošenje informirane 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.