Regresija v Excelu: enačba, primeri. Linearna regresija

Regresijska analiza je statistična metoda preiskovanja, ki omogoča prikazovanje odvisnosti parametra na eni ali več neodvisnih spremenljivkah. V pretekmerni dobi je bila njegova uporaba precej težavna, še posebej, ko gre za vprašanje velikih količin podatkov. Danes, ko se naučite, kako zgraditi regresijo v Excelu, lahko rešite zapletene statistične probleme v samo nekaj minutah. Spodaj so konkretni primeri s področja ekonomije.

Vrste regresije

Ta koncept je bil uveden v matematiko Francis Galton leta 1886. Dogodek se zgodi:

• linearno;
• parabolični;
• zakonodaja o oblasti;
• eksponentni;
• hiperbolični;
• okvirno;
• logaritmično.

Primer 1

Razmislimo o problemu ugotavljanja odvisnosti števila upokojenih članov kolektiva od povprečne plače pri šestih industrijskih podjetjih.

Naloga. Šest podjetij je analiziralo povprečno mesečno plačo in število zaposlenih, ki so zapustili svojo volijo. V tabeli imamo:

Za problem ugotavljanja odvisnosti števila zaposlenih, ki so zapustili povprečno plačo pri 6 podjetjih, ima regresijski model obliko enačbe Y = a₀ + a₁x₁ +hellip- + a_kx_k, kjer je x_i - vplivajo na spremenljivke, a_i - koeficienti regresije in k - število dejavnikov.

Za to nalogo je Y indikator zaposlenih, ki je zapustil, faktor, ki vpliva, je plača, ki jo označuje X.

Uporaba Excelovega namiznega procesorja

Pred analizo regresije v Excelu je treba uporabiti vgrajene funkcije za dostopne tabele. Vendar je za te namene bolje uporabiti zelo uporaben dodatek "paket analize". Če jo želite aktivirati, potrebujete:

• Na kartici »Datoteka« odprite razdelek »Možnosti«;
• v odprtem oknu izberite vrstico "Add-ins";
• Kliknite na gumb "Pojdi", ki se nahaja spodaj, desno od vrstice "Upravljanje";
• Postavite kljukico poleg imena »Analitični paket« in potrdite dejanja s klikom na »Ok«.

Če je pravilno storjeno, se na desni strani zavihka Podatki, ki se nahaja nad delovnim listom programa Excel, pojavi desni gumb.

Linearna regresija v Excelu

Zdaj, ko imamo vse potrebne virtualne instrumente pri izvajanju ekonometričnih izračunov, lahko začnemo reševati naš problem. Za to:

• kliknite na gumb "Analiza podatkov";
• v odprtem oknu kliknite na gumb "Regresija";
• v jezičku, ki se pojavi, vnesemo obseg vrednosti za Y (število zaposlenih, ki so zapustili) in za X (njihova plača);
• Potrdimo naše dejanje s klikom na gumb »Ok«.

Zato bo program samodejno izpolnil nov list obdelovalca podatkov z regresijsko analizo podatkov. Bodite pozorni! V Excelu obstaja možnost samostojnega določanja mesta, ki vam je všeč v ta namen. Na primer, to je lahko isti listek kot vrednosti Y in X ali celo nova knjiga, posebej zasnovana za shranjevanje takih podatkov.

Analiza rezultatov regresije za R-kvadrat

V Excelu so podatki, pridobljeni med obdelavo podatkov zadevnega primera, naslednji:

Prvič, bodite pozorni na vrednost R-kvadrata. To je koeficient določljivosti. V tem primeru R-kvadrat = 0,755 (75,5%), tj. Izračunani parametri modela, pojasnjujejo odvisnost med parametri, ki jih štejemo za 75,5%. Višja je vrednost koeficienta določanja, se izbrani model šteje bolj uporaben za določeno nalogo. Verjame se, da pravilno opisuje dejansko stanje z vrednostjo R kvadratov nad 0,8. Če je R-kvadrat<0,5, potem takšne analize regresije v Excelu ni mogoče razumeti.

Analiza koeficientov

Številka 64.1428 prikazuje, kakšna bo vrednost Y, če se vse spremenljivke xi v obravnavanem modelu ponastavijo. Z drugimi besedami, lahko trdimo, da na vrednost analiziranega parametra vplivajo drugi dejavniki, ki niso opisani v določenem modelu.

Naslednji dejavnik -,16285 nahaja v celici B18, prikazuje pomemben vpliv spremenljivke X Y. To pomeni, da je povprečna plača zaposlenih v modelu vpliva na število odstopov od teže -0.16285, t. E. Stopnja njenega vpliva na vse majhna. Znak ";" pomeni, da ima koeficient negativno vrednost. To je očitno, saj vsi vedo, da je višja plača v podjetju, manj ljudi izrazi željo, da prekine pogodbo o zaposlitvi ali odide.

Več regresij

S tem izrazom označujemo enačbo povezave z več neodvisnimi spremenljivkami oblike:

y = f (x₁+x₂+hellip-x_m) + epsilon-, kjer je y posledični atribut (odvisna spremenljivka) in x₁, x₂, hellip-x_m - to so znaki-dejavniki (neodvisne spremenljivke).

Vrednotenje parametrov

Za več regresij (MP) se izvede z uporabo metode najmanjših kvadratov (OLS). Za linearne enačbe oblike Y = a + b₁x₁ +hellip- + b_mx_m+ epsilon - gradimo sistem normalnih enačb (glej spodaj)

Za razumevanje načela metode upoštevajte dvomestni primer. Potem imamo situacijo, ki jo opisuje formula

Zato dobimo:

kjer sigma- je varianca ustrezne karakteristike, ki se odraža v indeksu.

OLS se uporablja za MP enačbo v standardizirani lestvici. V tem primeru dobimo enačbo:

v katerem t_y, t_{x1, hellip-}t_xm - standardizirane spremenljivke, za katere so povprečne vrednosti 0- beta-_i - standardizirani regresijski koeficienti, standardni odklon pa je 1.

Upoštevajte, da je vse beta-_i v tem primeru se navedejo kot normalizirane in centralizirane, zato se njihova primerjava med seboj šteje kot pravilna in dopustna. Poleg tega je običajno prikazati dejavnike, ki zavržejo tiste, ki imajo najnižje vrednosti beta-i.

Problem z uporabo linearne regresijske enačbe

Predpostavimo, da je v zadnjih 8 mesecih tabela dinamike cen določenega blaga N. Treba je sprejeti odločitev o primernosti nakupa svoje serije po ceni 1.850 rubljev na tono.

Če želite rešiti to težavo v Excelovem miznem procesorju, morate uporabiti že znano orodje "Analiza podatkov". Nato izberite razdelek »Regresija« in določite parametre. Ne smemo pozabiti, da je v "mora območju Vhodni Y" se je predstavil na območju vrednosti odvisne spremenljivke (v tem primeru je cena blaga v določenih mesecih leta) in v "Input interval X» - za neodvisno (mesec). Potrdite dejanje tako, da kliknete »V redu«. Na novem listu (če je tako navedeno), dobimo podatke za regresijo.

Na njih gradimo linearno enačbo oblike y = ax + b, kjer so parametri a in b koeficienti črte z imenom številke meseca in koeficienti ter črke "Y-križišče" iz lista z rezultati regresijske analize. Tako je linearna regresijska enačba (VR) za problem 3 zapisana kot:

Cena blaga N = 11.714 * številka meseca + 1727,54.

ali v algebrski zapisi

y = 11,714 x + 1727,54

Analiza rezultatov

Da bi ugotovili, ali je posledična linearna regresijska enačba ustrezna, se uporabijo koeficienti večkratne korelacije (KMC) in določitev, pa tudi kriterij Fisher in Student`s test. V tabeli Excel z rezultati regresije se pojavijo pod imeni več R, R-squared, F-statistike in t-statistike.

KMC R omogoča, da se oceni tesnost verjetnostnega razmerja med neodvisnimi in odvisnimi spremenljivkami. Njegova visoka vrednost kaže razmeroma močno razmerje med spremenljivkami "Številka meseca" in "Ceno blaga N v rubrikah na 1 tono." Vendar narava tega razmerja še ni znana.

Kvadrat koeficienta določanja R²(RI) je numerična značilnost frakcije celotnega razpona in prikazuje raztresenost katerega dela eksperimentalnih podatkov, tj. Vrednosti odvisne spremenljivke ustrezajo linearni regresijski enačbi. V obravnavanem problemu je ta vrednost 84,8%, to pomeni, da so statistični podatki s sprejemljivo SD opisani z visoko stopnjo točnosti.

F-statistika, imenovana tudi Fisherjev kriterij, se uporablja za oceno pomembnosti linearne odvisnosti, ki nasprotuje ali potrjuje hipotezo o njenem obstoju.

Vrednost t-statistike (Student`s test) pomaga oceniti pomen koeficienta za neznan ali brezplačen izraz linearne odvisnosti. Če je vrednost t-testa> t_cr, potem se zavrne hipoteza o pomanjkanju brezplačnega izraza linearne enačbe.

Na ta problem za brezplačno izraza prek instrumentov "Excel" je bilo ugotovljeno, da je t = 169,20903 in p = 2,89E-12, t. E. imeti ničelno verjetnost, da se bo zvesti zavrnil hipotezo o nepomembnosti prostega izraza. Za koeficient z neznanim t = 5,79405 in p = 0,001158. Z drugimi besedami, je verjetnost, da bo zavrnjen pravilna hipoteza nepomembnosti koeficienta za neznano, je 0,12%.

Tako je mogoče trditi, da je posledična linearna regresijska enačba zadostna.

Težava pri nakupu delnic

Večkratna regresija v Excelu se izvaja z uporabo istega orodja za analizo podatkov. Razmislimo o specifičnem uporabljenem problemu.

Družba za upravljanje "NNN" bi morala sprejeti odločitev o primernosti nakupa 20% delež v JSC "MMM". Stroški paketa (SP) znašajo 70 milijonov ameriških dolarjev. Strokovnjaki "NNN" so zbirali podatke o podobnih transakcijah. Odločeno je bilo oceniti vrednost deleža v takšnih parametrih, izraženih v milijonih ameriških dolarjev, kot:

• obveznosti do dobaviteljev (VK);
• obseg letnega prometa (VO);
• terjatve do kupcev (VD);
• vrednost osnovnih sredstev (SOF).

Poleg tega se uporablja parameter za plače v podjetju (V3 P) v tisočih ameriških dolarjih.

Rešitev z uporabo preglednice Excel

Najprej morate ustvariti tabelo vhodnih podatkov. Ima naslednjo obliko:

Naprej:

• Pokličite okno "Analiza podatkov";
• izberite razdelek »Regresija«;
• V polju »Vhodni interval Y« vnesite obseg vrednosti odvisnih spremenljivk iz stolpca G;
• kliknite ikono z rdečo puščico desno od okna »Input interval X« in izberite izbrano vrsto vseh vrednosti iz stolpcev B, C, D, F na listu.

Označite element »Novi delovni list« in kliknite »V redu«.

Za to nalogo dobimo regresijsko analizo.

Rezultati študije in sklepi

"Zbiranje" iz zaokroženih podatkov, predstavljenih zgoraj na preglednici Excelovega preglednice, regresijska enačba:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

V bolj znani matematični obliki se lahko zapiše kot:

y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265,844

Podatki za MMM so naslednji:

Če jih nadomestimo v enačbo regresije, dobimo vrednost 64,72 milijona ameriških dolarjev. To pomeni, da se deleži MMM ne smejo kupiti, saj je njihova vrednost 70 milijonov ameriških dolarjev precej precenjena.

Kot lahko vidite, je uporaba Excelovega namiznega procesorja in enačbe regresije omogočila sprejemljivo odločitev o izvedljivosti zelo specifične transakcije.

Sedaj veste, kaj je regresija. Primeri v Excelu, obravnavani zgoraj, vam bodo pomagali pri reševanju praktičnih problemov s področja ekonometrije.