Regresija u Excelu: jednadžba, primjeri. Linearna regresija

Regresijska analiza je statistička tehnikaistraživanje koje vam omogućuje da pokažete ovisnost parametra o jednoj ili više neovisnih varijabli. U doba prije računala, njegova primjena je bila prilično teška, osobito kada je riječ o velikim količinama podataka. Danas, nakon što ste naučili kako izgraditi regresiju u Excelu, možete riješiti složene statističke probleme u samo nekoliko minuta. U nastavku su navedeni konkretni primjeri iz područja ekonomije.

Vrste regresije

Sam koncept u matematiku je uveo Francis Galton 1886. godine. Regresija se događa:

• linearni;
• parabolični;
• zakon moći;
• eksponencijalni;
• hiperbolički;
• indikativno;
• logaritamski.

Primjer 1

Razmotrimo problem utvrđivanja ovisnosti broja zaposlenih koji su napustili posao o prosječnoj plaći u 6 industrijskih poduzeća.

Zadatak. Šest poduzeća analiziralo je prosječnu mjesečnu plaću i broj zaposlenih koji su dobrovoljno dali otkaz. U tabličnom obliku imamo:

Za problem određivanja ovisnosti broja napuštenih radnika o prosječnoj plaći u 6 poduzeća, regresijski model ima oblik jednadžbe Y = a₀ + a₁s₁ + ... + a_ks_kgdje je x_i - utjecajne varijable, a_i Jesu koeficijenti regresije, a k je broj faktora.

Za ovaj zadatak Y je pokazatelj zaposlenika koji su dali otkaz, a faktor utjecaja je plaća koju označavamo s X.

Korištenje mogućnosti procesora tablica Excel

Regresijskoj analizi u Excelu mora prethoditiPrimjena ugrađenih funkcija na postojeće tablične podatke. Međutim, za te je svrhe bolje koristiti vrlo koristan dodatak "Paket analize". Da biste ga aktivirali potrebno vam je:

• s kartice "Datoteka" idite na odjeljak "Parametri";
• u prozoru koji se otvori odaberite redak "Dodaci";
• kliknite na gumb "Idi" koji se nalazi ispod, desno od retka "Kontrola";
• stavite kvačicu pored naziva "Paket analize" i potvrdite svoje radnje klikom na "U redu".

Ako je sve učinjeno ispravno, na desnoj strani kartice "Podaci" koja se nalazi iznad "Excel" radnog lista pojavit će se potrebni gumb.

Linearna regresija u Excelu

Sada kada imamo pri ruci sve potrebne virtualne alate za izvođenje ekonometrijskih izračuna, možemo početi rješavati naš problem. Za ovo:

• kliknite na gumb "Analiza podataka";
• u prozoru koji se otvori kliknite na gumb "Regresija";
• na kartici koja se pojavi unesite raspon vrijednosti za Y (broj zaposlenika koji su dali otkaz) i za X (njihove plaće);
• potvrđujemo naše radnje pritiskom na tipku "U redu".

Kao rezultat toga, program će se automatski popunitinovu proračunsku tablicu s podacima regresijske analize. Bilješka! Excel ima mogućnost samostalnog definiranja željene lokacije u tu svrhu. Na primjer, to može biti isti list koji sadrži vrijednosti Y i X, ili čak nova radna knjiga posebno dizajnirana za pohranu takvih podataka.

Analiza rezultata regresije za R-kvadrat

U Excelu su podaci dobiveni tijekom obrade podataka predmetnog primjera sljedeći:

Prije svega, trebate obratiti pažnju naR-kvadrat vrijednost. Predstavlja koeficijent determinacije. U ovom primjeru R-kvadrat = 0,755 (75,5%), odnosno izračunati parametri modela objašnjavaju odnos između razmatranih parametara za 75,5%. Što je veća vrijednost koeficijenta determinacije, to se odabrani model više smatra primjenjivijim za određeni zadatak. Vjeruje se da ispravno opisuje stvarnu situaciju kada je vrijednost R-kvadrata iznad 0,8. Ako je R-kvadrat <0,5, tada se takva regresijska analiza u Excelu ne može smatrati razumnom.

Analiza izgleda

Broj 64.1428 pokazuje kolika će biti vrijednost Y,ako su sve varijable xi u modelu koji razmatramo jednake nuli. Drugim riječima, može se tvrditi da na vrijednost analiziranog parametra utječu drugi čimbenici koji nisu opisani u pojedinom modelu.

Sljedeći koeficijent je -0,16285, smješten ućelija B18, pokazuje značajnost utjecaja varijable X na Y. To znači da prosječna mjesečna plaća zaposlenih unutar modela koji se razmatra utječe na broj onih koji odustaju s težinom od -0,16285, odnosno na stupanj njenog utjecaja. je dosta malen. Znak "-" označava da je koeficijent negativan. To je očito, budući da svi znaju da što je veća plaća u poduzeću, to manje ljudi izražava želju za raskidom ugovora o radu ili odlaskom.

Višestruka regresija

Ovaj se pojam shvaća kao jednadžba ograničenja s nekoliko neovisnih varijabli oblika:

y = f (x₁+ x₂+… X_m) + ε, gdje je y rezultantna karakteristika (ovisna varijabla), a x₁, x₂,… X_m - to su znakovi-faktori (nezavisne varijable).

Procjena parametara

Za višestruku regresiju (MR) provodi se metodom najmanjih kvadrata (OLS). Za linearne jednadžbe oblika Y = a + b₁s₁ + ... + b_ms_m+ ε konstruiramo sustav normalnih jednadžbi (vidi dolje)

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

Odavde dobijamo:

gdje je σ varijanca odgovarajuće značajke koja se odražava u indeksu.

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

gdje t_g, t_{s1, …}t_xm - standardizirane varijable za koje je srednja vrijednost 0; β_i Jesu li standardizirani koeficijenti regresije, a standardna devijacija je 1.

Imajte na umu da su svi β_i u ovom slučaju su dani kao normalizirani icentralizirano, pa se njihova usporedba smatra ispravnom i valjanom. Osim toga, uobičajeno je filtrirati faktore, odbacujući one od njih s najmanjim vrijednostima βi.

Problem Korištenje jednadžbe linearne regresije

Pretpostavimo da imate tablicu dinamike cijena za određenu robu N tijekom posljednjih 8 mjeseci. Potrebno je donijeti odluku o preporučljivosti kupnje njegove serije po cijeni od 1850 rubalja / t.

Za rješavanje ovog problema u tabličnom procesoruExcel treba koristiti alat za analizu podataka koji je već poznat iz gornjeg primjera. Zatim odaberite odjeljak "Regresija" i postavite parametre. Treba imati na umu da u polje "Input interval Y" morate unijeti raspon vrijednosti za zavisnu varijablu (u ovom slučaju cijenu proizvoda u određenim mjesecima u godini), a u "Input interval X" - za nezavisnu varijablu (broj mjeseca). Radnje potvrđujemo klikom na "U redu". Na novom listu (ako je tako naznačeno) dobivamo podatke za regresiju.

Koristeći ih, konstruiramo linearnu jednadžbu oblika y = ax + b, gdje jekao parametri a i b su koeficijenti pravca s nazivom broja mjeseca te koeficijenti i linije "Y-presjek" iz lista s rezultatima regresijske analize. Dakle, jednadžba linearne regresije (RB) za problem 3 je zapisana kao:

Cijena proizvoda N = 11.714 * broj mjeseca + 1727.54.

ili u algebarskom zapisu

y = 11,714 x + 1727,54

Analiza rezultata

Odlučiti je li dobivena jednadžba adekvatnakoriste se linearna regresija, koeficijenti višestruke korelacije i determinacije te Fisherov test i Studentov test. U Excel tablici s rezultatima regresije nazivaju se višestruki R, R-kvadrat, F-statistika i t-statistika.

KMK R omogućuje procjenu nepropusnostivjerojatnosni odnos između nezavisnih i zavisnih varijabli. Njegova visoka vrijednost ukazuje na prilično jak odnos između varijabli "Broj mjeseca" i "Cijena proizvoda N u rubljama po toni". Međutim, priroda ove veze ostaje nepoznata.

Kvadrat koeficijenta determinacije R²(RI) je numerička karakteristikaudio ukupnog raspršenja i prikazuje raspršivanje kojeg dijela eksperimentalnih podataka, t.j. vrijednosti zavisne varijable odgovaraju jednadžbi linearne regresije. U problemu koji se razmatra ova vrijednost iznosi 84,8%, odnosno statistički podaci su opisani s visokim stupnjem točnosti dobivenim SD.

F-statistika, također nazvana Fisherov test, koristi se za procjenu značaja linearnog odnosa, pobijajući ili potvrđujući hipotezu o njegovom postojanju.

Vrijednost t-statistike (Studentov test) pomaže u procjeni značajnosti koeficijenta s nepoznatim ili slobodnim članom linearne veze. Ako je vrijednost t-testa > t_kr, tada se odbacuje hipoteza o beznačajnosti slobodnog člana linearne jednadžbe.

U problemu koji se razmatra za slobodni terminpomoću Excel alata dobiveno je da je t = 169,20903, a p = 2,89E-12, odnosno da imamo nultu vjerojatnost da će točna hipoteza o beznačajnosti slobodnog člana biti odbačena. Za koeficijent kod nepoznatog t = 5,79405, a p = 0,001158. Drugim riječima, vjerojatnost da će točna hipoteza o beznačajnosti koeficijenta s nepoznatom biti odbačena je 0,12%.

Stoga se može tvrditi da je rezultirajuća jednadžba linearne regresije prikladna.

Problem svrsishodnosti kupnje paketa dionica

Višestruka regresija u Excelu se izvodi pomoću istog alata za analizu podataka. Razmotrimo konkretan primijenjen zadatak.

Uprava tvrtke "NNN" mora donijeti odlukuo uputnosti kupnje 20% udjela u dd "MMM". Cijena paketa (JV) je 70 milijuna američkih dolara. Stručnjaci NNN-a prikupili su podatke o sličnim transakcijama. Odlučeno je da se vrijednost paketa dionica procijeni takvim parametrima, izraženim u milijunima američkih dolara, kao što su:

• obveze prema dobavljačima (VK);
• obujam godišnjeg prometa (VO);
• potraživanja (VD);
• trošak dugotrajne imovine (SOF).

Dodatno, parametar su zaostale plaće poduzeća (V3 P) u tisućama američkih dolara.

Excel rješenje za proračunske tablice

Prije svega, trebate stvoriti tablicu početnih podataka. izgleda ovako:

Unaprijediti:

• pozovite prozor "Analiza podataka";
• odaberite odjeljak "Regresija";
• raspon vrijednosti zavisnih varijabli iz stupca G upisuje se u okvir "Input interval Y";
• kliknite na ikonu s crvenom strelicom desno od prozora "Input interval X" i odaberite na listu raspon svih vrijednosti iz stupaca B, C, D, F.

Označite stavku "Novi radni list" i kliknite "U redu".

Dobijte regresijsku analizu za zadani zadatak.

Proučavanje rezultata i zaključaka

"Skupljamo" jednadžbu regresije iz zaokruženih podataka prikazanih gore na tablici proračunske tablice Excel:

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 dd "MMM" prikazani su u tablici:

Zamjenjujući ih u jednadžbu regresije, dobivamobrojku od 64,72 milijuna američkih dolara. To znači da dionice JSC "MMM" ne treba kupovati, jer je njihova vrijednost od 70 milijuna američkih dolara prilično precijenjena.

Kao što možete vidjeti, korištenje Excel procesora proračunskih tablica i regresijske jednadžbe omogućili su donošenje informirane odluke o preporučljivosti vrlo specifične transakcije.

Sada znate što je regresija. Gore navedeni primjeri u Excelu pomoći će vam u rješavanju praktičnih problema u području ekonometrije.