Regresija u programu Excel: jednadžba, primjeri. Linearna regresija

Regresijska analiza je statistička metoda istraživanja, koja omogućuje prikaz ovisnosti parametra na jednu ili više nezavisnih varijabli. U doba predračunala, njegova upotreba bila je prilično teško, osobito kada je riječ o velikim količinama podataka. Danas, nakon što ste saznali kako stvoriti regresiju u Excelu, možete riješiti složene statističke probleme u samo nekoliko minuta. U nastavku su navedeni specifični primjeri iz područja ekonomije.

Vrste regresije

Ovaj sam pojam uveden je u matematiku Francis Galton 1886. Regresija se događa:

• linearan;
• parabolični;
• moć prava;
• eksponencijalna;
• hiperbolički;
• eksponencijalna;
• logaritamska.

Primjer 1

Razmotrimo problem određivanja ovisnosti broja umirovljenih članova kolektiva na prosječnoj plaći u 6 industrijskih poduzeća.

Zadatak. Šest poduzeća analiziralo je prosječnu mjesečnu plaću i broj zaposlenika koji su odustali od svoje volje. U tabličnom obliku imamo:

Za problem utvrđivanja ovisnosti broja zaposlenika koji su napustili prosječnu plaću u 6 poduzeća, regresijski model ima oblik jednadžbe Y = a₀ + i₁x₁ +hellip- + a_kx_k, gdje x_ja - utjecaj varijabli, a_ja - koeficijenti regresije i k - broj čimbenika.

Za ovaj zadatak, Y je pokazatelj zaposlenika koji su otišli, a faktor koji utječe na plaću označava X.

Korištenje Excel tabličnog procesora

Analiza regresije u programu Excel treba prethoditi primjenom ugrađenih funkcija dostupnim tabličnim podacima. Međutim, za ove je svrhe bolje koristiti vrlo korisnu dodatak "Analitički paket". Da biste ga aktivirali, trebate:

• Na kartici "Datoteka" otvorite odjeljak "Opcije";
• u otvorenom prozoru odaberite redak "Dodaci";
• Kliknite gumb "Idi", koji se nalazi ispod, desno od retka "Uprava";
• Stavite kvačicu pokraj naziva "Analiza paketa" i potvrdite svoje radnje klikom na "Ok".

Ako je ispravno učinjeno, desni gumb se pojavljuje na desnoj strani kartice Podaci koji se nalazi iznad radnog lista programa Excel.

Linearna regresija u programu Excel

Sada kada imamo sve potrebne virtualne instrumente da bismo implementirali ekonometrijske izračune, možemo početi rješavati naš problem. Da biste to učinili:

• kliknite gumb "Analiza podataka";
• u otvorenom prozoru kliknite gumb "Regresija";
• na kartici koja se pojavljuje, unosimo raspon vrijednosti za Y (broj zaposlenih koji su otišli) i X (njihova plaća);
• potvrđujemo naše radnje klikom na gumb "Ok".

Zbog toga će program automatski popuniti novi list procesora tablice s podacima regresijske analize. Obratite pažnju! U programu Excel postoji mogućnost da samostalno postavite mjesto koje želite za tu svrhu. Na primjer, to može biti isti list kao i Y i X vrijednosti ili čak nova knjiga posebno dizajnirana za pohranu takvih podataka.

Analiza rezultata regresije za R-kvadrat

U programu Excel, podaci dobiveni tijekom obrade podataka u predmetnom primjeru imaju oblik:

Prije svega, treba obratiti pozornost na vrijednost R-kvadrata. To je koeficijent odlučnosti. U ovom primjeru, R-kvadrat = 0,755 (75,5%), m. E. izračunati parametri modela objasniti odnos između parametrima koje razmatra 75,5%. Što je veća vrijednost koeficijenta određivanja, odabrani model smatra se primjenjivijim za određeni zadatak. Smatra se da ispravno opisuje stvarnu situaciju s R-kvadratnom vrijednošću iznad 0,8. Ako je R-kvadrat<0,5, tada se takva analiza regresije u Excelu ne može smatrati razumnom.

Analiza koeficijenata

Broj 64.1428 pokazuje koliko će vrijednost Y biti ako se resetiraju sve varijable xi u modelu koji se razmatra. Drugim riječima, može se tvrditi da na vrijednost analiziranog parametra utječu i drugi čimbenici koji nisu opisani u određenom modelu.

Sljedeći faktor -,16285 nalazi u staničnoj B18, pokazuje značajan utjecaj varijable X na Y. To znači da je prosječna plaća zaposlenih u modelu utječe na broj ostavki od težine -0.16285, t. E. Stupanj njegovog utjecaja na sve mali. ";" znak označava da koeficijent ima negativnu vrijednost. To je očito, jer svi znaju da što je veća plaća u poduzeću, manje ljudi izražava želju za raskidom ugovora o radu ili napuštanju.

Višestruka regresija

Ovim pojmom podrazumijevamo jednadžbu povezanosti s nekoliko nezavisnih varijabli oblika:

y = f (x₁+x₂+hellip-x_m) + epsilon-, gdje y je rezultatni atribut (zavisna varijabla), i x₁, x₂, hellip-x_m - to su znakovi - čimbenici (nezavisne varijable).

Procjena parametara

Za višestruku regresiju (MP), provodi se pomoću metode najmanje kvadrata (OLS). Za linearne jednadžbe oblika Y = a + b₁x₁ +hellip- + b_mx_m+ epsilon-konstruiramo sustav normalnih jednadžbi (vidi dolje)

Da biste razumjeli načelo metode, razmotrite slučaj s dva faktora. Zatim imamo situaciju opisanu formulom

Stoga dobivamo:

gdje sigma - je varijanta odgovarajuće karakteristike koja se odražava u indeksu.

OLS se primjenjuje na MP jednadžbu na standardiziranoj skali. U ovom slučaju dobivamo jednadžbu:

u kojem t_y, t_{x1 hellip-}t_XM - standardizirane varijable za koje su srednje vrijednosti 0- beta_ja - standardizirani regresijski koeficijenti, a standardna devijacija je 1.

Imajte na umu da sve beta_ja u ovom se slučaju daju kao normalizirani i centralizirani, pa se njihova međusobna usporedba smatra ispravnom i dopuštenima. Osim toga, uobičajeno je otkriti čimbenike koji odbacuju one koji imaju najniže vrijednosti beta-i.

Problem pomoću jednadžbe linearne regresije

Pretpostavimo da postoji tablica dinamike cijena određene robe N u posljednjih 8 mjeseci. Potrebno je donijeti odluku o svrhovitosti kupnje njegove serije po cijeni od 1.850 rubalja po toni.

Da biste riješili taj problem u Excel tabličnom procesoru, trebate upotrijebiti već poznati alat "Analiza podataka". Zatim odaberite odjeljak "Regresija" i odredite parametre. Moramo se sjetiti da je u „rasponu unosa Y» bi trebalo uvesti u rasponu vrijednosti zavisne varijable (u ovom slučaju cijena robe u pojedinim mjesecima u godini) i u „Input Interval X» - za neovisan (mjesec). Potvrdite akciju klikom na "Ok". Na novom listu (ako je navedeno) dobivamo podatke za regresiju.

Gradimo ih linearnu jednadžbu oblika y = ax + b, gdje je kao parametri A i B su koeficijenti iz linije broj mjeseca i ime koeficijenata i «Y raskrižju” liniju lista s rezultatima regresijske analize. Dakle, linearna regresijska jednadžba (VR) za problem 3 napisana je kao:

Cijena robe N = 11.714 * broj mjeseca + 1727,54.

ili u algebarskoj notaciji

y = 11.714 x + 1727.54

Analiza rezultata

Odlučiti da li je dobila adekvatno linearne regresijske jednadžbe pomoću višestruke koeficijenta korelacije (CMC) i odlučnost, kao i za testiranje i Fisherov t-testa. U tablici Excel s rezultatima regresije pojavljuju se pod imenima više R, R kvadratnih, F-statistika i t-statistika.

KMC R omogućuje procjenu nepropusnosti probabilističkog odnosa između nezavisnih i ovisnih varijabli. Njegova visoka vrijednost ukazuje na prilično jak odnos između varijabli "Broj mjeseca" i "Cijena robe N u rublima po 1 tone". Međutim, priroda tog odnosa ostaje nepoznata.

Kvadrat koeficijenta određivanja R²(RI) je numerička karakteristika frakcije ukupnog raspona i pokazuje raspršivanje dijela eksperimentalnih podataka, tj. Vrijednosti zavisne varijable odgovaraju linearnoj regresijskoj jednadžbi. U problemu koji se razmatra, ova vrijednost iznosi 84,8%, tj. Statistički podaci su opisani s visokim stupnjem točnosti primljenih SD.

F-statistika, također nazvana Fisherovim kriterijem, koristi se za procjenu značenja linearne ovisnosti, odbacujući ili potvrđujući hipotezu postojanja.

Vrijednost t-statistike (Studentov test) pomaže u procjeni značaja koeficijenta za nepoznat ili slobodan pojam linearne ovisnosti. Ako je vrijednost t-testa> t_cr, tada se odbacuje hipoteza o neznatnosti slobodnog pojma linearne jednadžbe.

U ovom problemu za slobodnu pojam kroz instrumente „Excel” utvrđeno je da je t = 169,20903, p = 2,89E-12, t. E. imati nultu vjerojatnost da će vjerni biti odbijen hipotezu o beznačajnosti slobodnog izraza. Za koeficijent s nepoznatim t = 5.79405 i p = 0.001158. Drugim riječima, vjerojatnost odbijanja ispravne hipoteze o neznatnosti koeficijenata nepoznatog je 0,12%.

Stoga se može tvrditi da je dobivena linearna regresijska jednadžba adekvatna.

Problem praktičnosti kupnje blok dionica

Višestruka regresija u programu Excel izvodi se pomoću istog alata "Analiza podataka". Razmotrimo određeni primijenjeni problem.

Društvo za upravljanje "NNN" trebalo bi donijeti odluku o prikladnosti kupnje 20% udjela u JSC "MMM". Cijena paketa (SP) iznosi 70 milijuna američkih dolara. Stručnjaci "NNN" prikupljaju podatke o sličnim transakcijama. Odlučeno je procijeniti vrijednost uloga u takvim parametrima, izražena u milijunima američkih dolara, kao:

• obveze prema dobavljačima (VK);
• obujam godišnjeg prometa (VO);
• potraživanja (VD);
• vrijednost stalnih sredstava (SOF).

Osim toga, parametar unazad plaće poduzećima (V3 P) koristi se u tisućama američkih dolara.

Rješenje pomoću Excel proračunske tablice

Prije svega, morate stvoriti tablicu ulaznih podataka. Ima sljedeći oblik:

Sljedeća:

• Nazovite prozor "Analiza podataka";
• odaberite odjeljak "Regresija";
• U okviru "Ulazni interval Y" unesite raspon vrijednosti zavisnih varijabli iz stupca G;
• kliknite ikonu s crvenom strelicom desno od prozora "Input interval X" i odaberite raspon svih vrijednosti iz stupaca B, C, D, F na listi.

Označite stavku "Novi radni list" i kliknite "Ok".

Za ovaj zadatak dobivena je regresijska analiza.

Rezultati istraživanja i zaključci

"Prikupljanje" iz zaobljenih podataka prikazanih iznad na radnom listu Excel proračunske tablice, regresijska jednadžba:

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

U više poznati matematički oblik, može se zapisati kao:

y = 0,103 * x1 + 0,541 x x2 - 0,031x3 + 0,40x x 4 + 0,691x5 - 265,844

Podaci za MMM su sljedeći:

Zamjenjujući ih u jednadžbi regresije, dobit će lik od 64,72 milijuna američkih dolara. To znači da dionice MMM-a ne bi trebale biti kupljene, jer njihova vrijednost od 70 milijuna američkih dolara prilično je precijenjena.

Kao što vidite, upotreba Excel tabličnog procesora i regresijske jednadžbe omogućila je donošenje informirane odluke o izvedivosti vrlo specifične transakcije.

Sada znate što je regresija. Primjeri u programu Excel, gore navedeni, pomoći će vam u rješavanju praktičnih problema s područja ekonometrije.