Regresijska analiza je statistička metoda istraživanja koja omogućuje prikaz ovisnosti jednog ili više parametara o jednoj ili više nezavisnih varijabli. U pred-računalnom razdoblju njegova je upotreba bila prilično teška, pogotovo kad je riječ o velikim količinama podataka. Danas, učeći kako izgraditi regresiju u Excelu, možete riješiti složene statističke probleme doslovno za nekoliko minuta. U nastavku su navedeni konkretni primjeri iz područja ekonomije.
Vrste regresije
Ovaj je pojam uveden u matematiku Francis Galton 1886. godine. Regresija se događa:
linearno;
parabolični;
stupanj;
eksponencijalno;
hiperbolički;
indikativno;
logaritamska.
Primjer 1
Razmotriti zadatak utvrđivanja ovisnosti broja otpuštenih članova tima o prosječnoj plaći u 6 industrijskih poduzeća.
Zadaci. Šest tvrtki analiziralo je prosječnu mjesečnu plaću i broj zaposlenih koji su se sami povukli u mirovinu. U tabličnom obliku imamo:
A
B
C
1
43) Plaća
2
y
30000 rubalja
58) 3
1
60
35000 rubalja 73)
4
2
35
40000 rub. 88)
5
3
20
45000 rubalja
6
4
20
50000 rubalja
7
5
15
55000 rubalja
8
6
15
60000 rubalja
Za zadatak utvrđivanja ovisnosti broja viška radnika o prosječnoj plaći u 6 poduzeća, regresijski model ima oblik jednadžbe Y = a 0 + a 1 x 1 ++ a kxk, gdje na xi utječu varijable, ai - regresijski koeficijenti, ak - broj čimbenika. Za ovaj zadatak, Y je broj otpuštenih zaposlenika, a faktor je plaća koju označavamo X.
Koristeći mogućnosti procesora Excelova proračunskih tablica
, regresijskoj analizi u Excelu treba prethoditi aplikacija postojećih tabličnih podataka ugrađenih funkcija. Međutim, za te je svrhe bolje koristiti vrlo koristan dodatak "Analizni paket". Da biste je aktivirali, trebate:
na kartici Datoteka ići na odjeljak "Opcije";
u prozoru odaberite redak "Dodaci";
Kliknite na gumb "Idi" koji se nalazi ispod, desno od retka "Upravljanje";
stavite kvačicu pored imena "Analizni paket" i potvrdite svoje radnje klikom na "Ok".
Ako je sve učinjeno ispravno, desna tipka na desnoj strani kartice Podaci, koja se nalazi iznad radnog lista programa Excel, pojavit će se.
Linearna regresija u Excelu
Sada kada imamo sve potrebne virtualne alate za ekonometrijske izračune, možemo početi rješavati naš problem. uovo:
kliknite na gumb "Analiza podataka";
U prozoru pritisnite gumb "Regresija";
Na kartici koja se pojavila unesite raspon vrijednosti Y (broj viška radnika) i X (njihove plaće);
potvrdite naše postupke pritiskom na gumb "Ok".
Kao rezultat, program će automatski popuniti novi list procesora tablice s podacima iz regresijske analize. Obratite pozornost! Excel ima sposobnost da samostalno odredi mjesto koje želite u tu svrhu. Na primjer, to može biti isto slovo koje sadrži Y i X vrijednosti, ili čak novu knjigu posebno osmišljenu za pohranjivanje takvih podataka.
Analiza rezultata regresije R-kvadrata
U Excelu, podaci dobiveni tijekom obrade podataka razmatranog primjera imaju oblik:
Prije svega, trebate obratiti pozornost na vrijednost R-kvadrata. To je koeficijent određivanja. U ovom primjeru, R-kvadrat = 0755 (755%), tj. Izračunati parametri modela objašnjavaju ovisnost između razmatranih parametara za 755%. Što je viša vrijednost koeficijenta određivanja, odabrani model smatra se prikladnijim za određeni zadatak. Smatra se da ispravno opisuje stvarnu situaciju s vrijednošću R-kvadrata iznad 08. Ako R-kvadrat broja koeficijenata analize 641428 prikazuje vrijednost Y, ako su sve varijable xi u našem modelu resetirane. Drugim riječima, može se tvrditi da na vrijednosti analiziranog parametra utječu drugi čimbenici koji nisu opisani u određenom modelu. Sljedeći faktor -016285 koji se nalazi u ćeliji B18 je težinautjecaj varijable X na Y. To znači da prosječna mjesečna plaća zaposlenika u okviru razmatranog modela utječe na broj puštenih s težinom -016285 tj. stupanj njegovog utjecaja je vrlo mali. Znak "-" označava da je koeficijent negativan. To je očigledno, jer svatko zna da što je više plaće u poduzeću, manje ljudi izražava želju da raskine ugovor o radu ili da bude pušten na slobodu.
Višestruka regresija
Pod ovim izrazom je jednadžba komunikacije s nekoliko nezavisnih varijabli u obliku: y = f (x 1 + x 2 + xm) + ?, gdje je y produktivna osobina (zavisna varijabla), i x 1, x 2, xm su atributni faktori (nezavisne varijable).
Procjena parametara
Za višestruku regresiju (MP) provodi se metodom najmanjih kvadrata (MNC). Za linearne jednadžbe oblika Y = a + b 1 x 1 ++ b m x m +? Konstruiramo sustav normalnih jednadžbi (vidi dolje)
Da bi se razumjelo načelo metode, razmotrimo dva faktorska slučaja. Tada imamo situaciju opisanu formulom
Odavde dobivamo:
gdje? - je varijanca odgovarajućeg znaka koji se odražava u indeksu. MNC će se primijeniti na MR jednadžbu na standardiziranoj skali. U ovom slučaju dobivamo jednadžbu:
u kojoj t y, t x 1 t xm - standardizirane varijable za koje su srednje vrijednosti jednake 0; ? i - standardizirani regresijski koeficijenti, i srednja kvadratna devijacija - 1. Obratite pozornost na sve? i u ovom slučaju se daju kao normalizirani i centralizirani, stoga se njihova međusobna usporedba smatra ispravnom i dopuštenom. Osim toga,Prihvaćeno je izvršiti odbijanje čimbenika, odbacujući one koji imaju najmanje vrijednosti?
Zadaci pomoću jednadžbe linearne regresije
Na primjer, postoji tablica dinamike cijena za određeni proizvod N u posljednjih 8 mjeseci. Potrebno je odlučiti o svrsishodnosti kupnje njegove stranke po cijeni od 1850 rubalja po toni.
b
)
broj mjeseca
mjesečno ime
cijena robe N
2
1
siječanj
228) 1750 rubalja po toni
3
2
veljača
)
1755 rubalja po toni
4
3
ožujak
1767 rubalja po toni
5
4
) Travanj
1760 rubalja od i
6
5
svibanj
1770 rubalja po toni
7
6
lipanj 306)
1790 rubalja po toni
8
7
Srpanj
1810 rubalja po toni
9
8
335)
1840 rubalja po toni
350)
Da biste riješili ovaj problem u Excel procesoru, morate koristiti već Idoma ljudi prikazani su u gornjem primjeru alata „Analiza podataka.” Zatim odaberite odjeljak "Regresija" i pitajtepostavke. Treba imati na umu da se u polju Input interval Y mora unijeti raspon vrijednosti zavisne varijable (u ovom slučaju cijena proizvoda u pojedinim mjesecima godine), te u "Input interval X" za neovisni (broj mjeseca). Potvrdite radnje klikom na "Ok". Na novom listu (ako je navedeno) dobivamo podatke za regresiju. Konstruiramo linearnu jednadžbu oblika y = ax + b, gdje su koeficijenti linije s imenom broja mjeseca i koeficijenti i linija "Y-sjecište" iz slova s rezultatima regresijske analize djeluju kao parametri a i b. Prema tome, jednadžba linearne regresije (UR) zadatka 3 zapisana je kao: Cijena proizvoda N = 11714 * broj mjeseca + 172754. ili algebarska oznaka y = 11714 x + 172754
Analiza rezultata
Odlučiti je li adekvatno dobivena linearnu regresijsku jednadžbu, koeficijente višestruke korelacije (KMK) i određivanje, kao i Fisherov kriterij i Studentov kriterij. U tablici "Excel" s rezultatima regresije djeluju kao višestruki R R-kvadrati, F-statistika i t-statistika. KMK R daje mogućnost procjene bliskosti probabilističke veze između nezavisnih i zavisnih varijabli. Njegova visoka vrijednost ukazuje na prilično snažnu vezu između varijabli "Mjesec u mjesecu" i "Cijena robe u rubljima za 1 tonu". Međutim, priroda ove povezanosti ostaje nepoznata. Kvadrat koeficijenta određivanja R 2 (RI) je numerička karakteristika dijela ukupnog rasprostiranja i pokazuje da širenje bilo kojeg dijela eksperimentalnih podataka, odnosno vrijednosti zavisne varijable odgovarajednadžba linearne regresije. U razmatranom problemu ta je vrijednost jednaka 848%, odnosno statistički podaci s visokim stupnjem točnosti opisani su dobivenim SD. F-statistika, također nazvana Fisherovim kriterijem, korištena je za procjenu značaja linearne ovisnosti, pobijanje ili potvrđivanje hipoteze o njezinu postojanju. Vrijednost t-statistike (studentov kriterij) pomaže u procjeni značaja koeficijenta za nepoznatog ili slobodnog člana linearne ovisnosti. Ako je vrijednost kriterija t & gt; t cr, tada se odbacuje hipoteza o beznačajnosti slobodnog člana linearne jednadžbe. U razmatranom problemu za slobodnog člana koristeći alate Excela, nađeno je da je t = 16920903 i p = 289 E-12, tj. Da imamo nultu vjerojatnost da će ispravna hipoteza o beznačajnosti slobodnog termina biti odbačena. Za koeficijent s nepoznatim t = 579405 i p = 0001158. Drugim riječima, vjerojatnost odbijanja ispravne hipoteze o beznačajnosti koeficijenta ispod nepoznatog je 012%. Stoga se može tvrditi da je dobivena jednadžba linearne regresije adekvatna.
Svrha stjecanja dioničarstva
Višestruka regresija u Excelu izvodi se pomoću istog alata za analizu podataka. Razmotrite specifičan problem aplikacije. Uprava tvrtke "NNN" mora odlučiti o svrsishodnosti kupnje 20% udjela u JSC "MMM". Cijena paketa (JV) je 70 milijuna USD. Stručnjaci "NNN-a" prikupili su podatke o sličnim ugovorima. Odlučeno je procijeniti vrijednost bloka dionica prema parametrima izraženim umilijuni američkih dolara, kao:
obveze prema dobavljačima (VK);
godišnji promet (VO);
potraživanja (VD);
vrijednost dugotrajne imovine (SOF).
Osim toga, parametar je zaostatak plaća tvrtke (V3 P) tisuća američkih dolara.
Excelova rješenja za proračunske tablice
Prije svega, trebate sastaviti tablicu izlaznih podataka. Ima sljedeći oblik:
Sljedeći:
nazovite prozor "Analiza podataka";
izabrati odjeljak "regresija";
u prozoru "Input interval Y" unosi se raspon vrijednosti zavisnih varijabli stupca G;
klikne na ikonu s crvenom strelicom desno od prozora "Ulazni X interval" i dodjeljuje stupac vrijednosti za stupac B, C, D, F.
Označite "Novi radni list" i kliknite "U redu" , Nabavite regresijsku analizu za ovaj zadatak.
Rezultati istraživanja i zaključci
"Prikupljamo" iz zaokruženih podataka prikazanih na gornjoj tablici Excel tabličnog procesora, regresijske jednadžbe: SP = 0103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265844. U uobičajenijem matematičkom obliku može se pisati kao: y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265844 Podaci za JSC "MMM" prikazani su u tablici: 375) SOF, USD
VO, USD
VK, USD
VD, USD
VZP, USD
SP, USD
1025 402)
5355
452
415
2155
6472
Zamjenjujući ih u regresijske jednadžbe,brojka od 6472 milijuna američkih dolara. To znači da se dionice "MMM" dd ne bi trebale kupiti, jer je njihova vrijednost u 70 milijuna američkih dolara prilično precijenjena. Kao što možete vidjeti, korištenje Excel tablice i regresijske jednadžbe omogućilo nam je da donesemo razumnu odluku o izvedivosti potpuno specifične transakcije. Sada znate kakva je regresija. Primjeri u Excelu, koji su gore razmotreni, pomoći će vam u rješavanju praktičnih problema u području ekonometrije.
Da bi se razumjelo načelo metode, razmotrimo dva faktorska slučaja. Tada imamo situaciju opisanu formulom 
Odavde dobivamo: 
Sljedeći:
