Regresijska analiza je statistična metoda raziskovanja, ki omogoča prikaz odvisnosti enega ali drugega parametra od ene ali več neodvisnih spremenljivk. V obdobju pred računalnikom je bila njegova uporaba precej težka, zlasti ko je šlo za velike količine podatkov. Danes, se naučite, kako zgraditi regresijo v Excelu, lahko rešite kompleksne statistične probleme dobesedno v nekaj minutah. Spodaj so navedeni konkretni primeri s področja ekonomije.
Vrste regresije
Ta koncept je v matematiko uvedel Francis Galton leta 1886. Regresija se zgodi:
linearno;
parabolični;
stopnja;
eksponentno;
hiperbolični;
okvirno;
logaritemska.
Primer 1
Razmislite o določitvi odvisnosti števila odpuščenih članov skupine od povprečne plače v 6 industrijskih podjetjih.
Naloge. Šest družb je analiziralo povprečno mesečno plačo in število zaposlenih, ki so se upokojili sami. V tabelarni obliki imamo:
A
B
C
1
X
43) Plača
2
y
30000 rubljev
58) 3
1
60
35000 rubljev 73)
4
2
35
40000 rubljev 88)
5
3
20
45000 rubljev
6
4
20
50000 rubljev
7
5
15
55000 rubljev
8
6
15
60000 rubljev
Za določitev odvisnosti števila presežnih delavcev od povprečne plače v 6 podjetjih ima regresijski model obliko enačbe Y = a 0 + a 1 x 1 ++ a kxk, kjer na xi vplivajo spremenljivke, ai - regresijski koeficienti, ak - število dejavnikov. Za to nalogo je Y število odpuščenih delavcev, faktor pa je plača, ki jo označujemo X.
Z uporabo zmožnosti procesorja Excelovih preglednic
je treba pred regresijsko analizo v Excelu uporabiti obstoječe podatke tabel vgrajenih funkcij. Vendar je za te namene bolje uporabiti zelo koristen dodatek "Analizni paket". Za aktiviranje potrebujete:
na zavihku Datoteka, pojdite na razdelek »Možnosti«;
v oknu izberite vrstico "Dodatki";
Kliknite na gumb "Pojdi", ki se nahaja spodaj, desno od vrstice "Upravljanje";
postavite kljukico poleg imena "Analizni paket" in potrdite dejanja s klikom na "Ok".
Če je vse narejeno pravilno, se bo pojavil desni gumb na desni strani zavihka Podatki, ki se nahaja nad delovnim listom Excel.
Linearna regresija v Excelu
Zdaj, ko imamo na voljo vsa potrebna virtualna orodja za ekonometrične izračune, lahko začnemo reševati naš problem. Zato:
kliknite na gumb "Analiza podatkov";
V oknu pritisnite gumb "Regresija";
Na zavihku, ki se je pojavil, vnesite obseg vrednosti Y (število presežnih delavcev) in X (njihove plače);
potrdite naša dejanja s pritiskom na gumb "Ok".
Zato bo program samodejno izpolnil nov list procesorja tabele s podatki iz regresijske analize. Bodite pozorni! Excel ima možnost, da samostojno določi mesto, ki ga želite za ta namen. Lahko je na primer ista črka, ki vsebuje vrednosti Y in X, ali celo novo knjigo, ki je posebej zasnovana za shranjevanje takih podatkov.
Analiza rezultatov regresije R kvadrata
V Excelu so podatki, pridobljeni med obdelavo podatkov obravnavanega primera, v obliki:
Najprej morate paziti na vrednost R kvadrata. To je koeficient določanja. V tem primeru je R-kvadrat = 0755 (755%), torej izračunani parametri modela pojasnjujejo odvisnost med upoštevanimi parametri za 755%. Višja kot je koeficient determinacije, je izbrani model bolj primeren za določeno nalogo. Šteje se, da pravilno opisuje dejansko stanje z vrednostjo kvadrata R nad 08. Če R-kvadrat številke analize koeficientov 641428 prikazuje vrednost Y, če so vse spremenljivke xi v našem modelu resetirane. Z drugimi besedami, lahko trdimo, da na vrednosti analiziranega parametra vplivajo drugi dejavniki, ki niso opisani v določenem modelu. Naslednji faktor, ki se nahaja v celici B18, je težaVpliv spremenljivke X na Y. To pomeni, da povprečna mesečna plača zaposlenih v okviru obravnavanega modela vpliva na število sproščenih s težo -016285, tj. stopnja njenega vpliva je zelo majhna. Znak "-" pomeni, da ima koeficient negativno vrednost. To je očitno, saj vsi vedo, da več plače v podjetju, manj ljudi izrazi željo po prekinitvi pogodbe o zaposlitvi ali izpustitvi.
Večkratna regresija
Pod tem izrazom je enačba komunikacije z več neodvisnimi spremenljivkami v obliki: y = f (x 1 + x 2 + xm) + ?, pri čemer je y produktivna lastnost (odvisna spremenljivka), in x 1, x 2, xm so atributni faktorji (neodvisne spremenljivke).
Ocena parametrov
Za multiplo regresijo (MP) se izvaja z metodo najmanjših kvadratov (MNC). Za linearne enačbe oblike Y = a + b 1 x 1 ++ b m x m +? Konstruiramo sistem normalnih enačb (glej spodaj)
Da bi razumeli načelo metode, razmislite o dvostopenjskem primeru. Potem imamo situacijo, opisano s formulo
Od tukaj dobimo:
kje? - je varianca ustreznega znaka, ki se odraža v indeksu. MNC se bodo uporabljale za enačbo MR na standardizirani lestvici. V tem primeru dobimo enačbo:
v katerem t y, t x 1 t xm - standardizirane spremenljivke, pri katerih so srednje vrednosti enake 0; ? i - standardizirani regresijski koeficienti in srednji kvadratni odklon - 1. Upoštevajte vse? i v tem primeru so podane kot normalizirane in centralizirane, zato je njihova primerjava med seboj pravilna in dovoljena. Poleg tegaSprejemljivo je, da se izločijo dejavniki, ki zavračajo tiste, ki imajo najmanjše vrednosti?
Naloge z uporabo linearne regresijske enačbe
Na primer, v zadnjih 8 mesecih obstaja tabela gibanja cen za določen izdelek N. Treba je odločiti o primernosti nakupa njegove stranke po ceni 1850 rubljev na tono.
b
)
številka meseca
ime meseca
cena blaga N
2
1
januar
228) 1750 rubljev na tono
3
2
februar
)
1755 rubljev na tono
4
3
march
1767 rubljev za tono
5
4
april
1760 rubljev iz in
6
5
maj
1770 rubljev na tono
7
6
junij 306)
1790 rubljev na tono
8
7
Julij
1810 rubljev na tono
9
8
335)
1840 rubljev na tono
350)
Za rešitev te težave v Excelovem procesorju morate uporabiti že Idoma ljudi, predstavljene v zgornji primer orodje "Data Analysis." Nato izberite razdelek "Regresija" in vprašajteparametrov Ne smemo pozabiti, da je v polju Input interval Y potrebno vnesti obseg vrednosti odvisne spremenljivke (v tem primeru cena izdelka v določenih mesecih leta) in v "Interval vnosa X" za neodvisno (številko meseca). Potrdite dejanja s klikom na "Ok". Na novem listu (če je bilo navedeno) dobimo podatke za regresijo. Konstruiramo linearno enačbo oblike y = ax + b, kjer koeficienti črte z imenom števila meseca in koeficienti ter črta "Y-presečišče" iz črke z rezultati regresijske analize delujejo kot parametri a in b. Tako je linearna regresijska enačba (UR) v nalogi 3 zapisana kot: Cena izdelka N = 11714 * številka meseca + 172754. ali algebrska oznaka y = 11714 x + 172754
Analiza rezultatov
Odločiti se je, ali je ustrezno linearna regresijska enačba, koeficienti večkratne korelacije (KMK) in določanje, Fisherjev kriterij in Studentov kriterij. V tabeli "Excel" z rezultati regresije delujejo kot več R-kvadratov, F-statistika in t-statistika. KMK R daje priložnost za oceno bližine verjetnostne povezave med neodvisnimi in odvisnimi spremenljivkami. Njegova visoka vrednost kaže precej močno povezavo med spremenljivkami "Mesec meseca" in "Cena blaga v rubljih za 1 tono". Vendar pa narava te povezave ostaja neznana. Kvota koeficienta določitve R 2 (RI) je številčna značilnost deleža skupnega razpona in kaže, da se širjenje katerega koli dela eksperimentalnih podatkov, to je vrednosti odvisne spremenljivke, ujema.linearna regresijska enačba. V obravnavanem problemu je ta vrednost enaka 848%, tj. Statistični podatki z visoko stopnjo natančnosti so opisani s prejetim SD. F-statistika, imenovana tudi Fisherjev kriterij, se uporablja za vrednotenje pomena linearne odvisnosti, zavračanje ali potrjevanje hipoteze o njenem obstoju. Vrednost t-statistike (študentov kriterij) pomaga oceniti pomen koeficienta za neznanega ali prostega člana linearne odvisnosti. Če je vrednost merila t & gt; t cr, potem se zavrne hipoteza o nepomembnosti prostega člana linearne enačbe. V obravnavanem problemu za prostega člana, ki uporablja orodja Excela, je bilo ugotovljeno, da je t = 16920903 in p = 289 E-12, torej imamo ničelno verjetnost, da bo pravilna hipoteza o nepomembnosti prostega termina zavrnjena. Za koeficient z neznanim t = 579405 in p = 0001158. Z drugimi besedami, verjetnost zavrnitve pravilne hipoteze o neznatnosti koeficienta pod neznanim je 012%. Tako lahko trdimo, da je dobljena linearna regresijska enačba ustrezna.
Namen pridobitve deleža
Večkratna regresija v Excelu se izvede z uporabo istega orodja za analizo podatkov. Razmislite o posebnem problemu uporabe. Vodstvo podjetja "NNN" se mora odločiti o primernosti nakupa 20% deleža v JSC "MMM". Stroški paketa (JV) znašajo 70 milijonov USD. Strokovnjaki "NNN" so zbirali podatke o podobnih sporazumih. Sklenjeno je bilo, da se vrednost bloka delnic oceni v skladu s parametri, izraženimi v. \ Tmilijonov ameriških dolarjev, kot so:
obveznosti do dobaviteljev (VK);
letni promet (VO);
terjatve (VD);
vrednost osnovnih sredstev (SOF).
Poleg tega je parameter zaostala plačila podjetja (V3 P) na tisoče ameriških dolarjev.
Excelove rešitve za preglednice
Najprej morate sestaviti tabelo izhodnih podatkov. Ima naslednjo obliko:
Naslednji:
pokličite okno "Analiza podatkov";
izberite oddelek "Regresija";
v oknu "Interval vnosa Y" vnesemo obseg vrednosti odvisnih spremenljivk v stolpcu G;
klikne na ikono z rdečo puščico desno od okna "Vhod X interval" in dodeli stolpec vrednosti za stolpec B, C, D, F.
Označite "Nov delovni list" in kliknite "V redu" . Pridobite regresijsko analizo za to nalogo.
Rezultati študije in zaključki
"Zbiramo" iz zgoraj prikazanih zaokroženih podatkov na listu obdelovalca tabel v Excelu, regresijska enačba: SP = 0103 * SOF + 0541 * VO - 0031 * VK + 0405 * VD + 0691 * VZP - 265844. V bolj običajni matematični obliki ga lahko zapišemo kot: y = 0103 * x1 + 0541 * x2 - 0031 * x3 + 0405 * x4 + 0691 * x5 - 265844 Podatki za JSC "MMM" so predstavljeni v tabeli: 375) SOF, USD
VO, USD
VK, USD
VD, USD
VZP, USD
SP, USD
1025
5355
452
415
2155
6472
Zamenjava v regresijske enačbe6472 milijonov ameriških dolarjev. To pomeni, da se delnic "MMM" JSC ne sme kupiti, saj je njihova vrednost v 70 milijonov ameriških dolarjev precej precenjena. Kot lahko vidite, nam je uporaba Excelove preglednice in regresijske enačbe omogočila razumno odločitev o izvedljivosti popolnoma specifične transakcije. Zdaj veste, kakšna je regresija. Zgoraj opisani primeri v Excelu vam bodo pomagali rešiti praktične probleme na področju ekonometrije.
