Regresija u Excel: primjera jednadžbi. linearna regresija

Regresijska analiza - statistička metoda kako bi se pokazalo ovisnost parametar jednim ili više nezavisnih varijabli. U pred-računalnog doba, njegova uporaba je vrlo teško, pogotovo kada je riječ o velikim količinama podataka. Danas, učenje kako izgraditi regresije u Excelu, možete riješiti složene statističke probleme u samo nekoliko minuta. Ispod su specifični primjeri ekonomije.

vrste regresije

Ovaj koncept je uveden matematike Francis Galton 1886. godine. Regresija je:

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

Primjer 1

On smatra da je problem određivanja ovisnosti o broju ostavkama članova osoblja prosječne plaće u 6 industrijskih poduzeća.

Zadatak. Šest tvrtke su analizirali prosječne mjesečne plaće i broj zaposlenih koji prestanu dobrovoljno. U tabličnom obliku imamo:

		B	C
1	X	Broj ostavki	plata
2		y	30000 rubalja
3	1	60	35000 rubalja
4	2	35	40000 rubalja
5	3	20	45000 rubalja
6	4	20	50.000 rubalja
7	5	15	55000 rubalja
8	6	15	60000 rubalja

Za problem određivanja ovisnost iznos rastave radnika od prosječne plaće 6 poduzeća regresijski model ima oblik jednadžbe y = A ₀ + a ₁ x ₁ + ... + _K x _k, pri čemu x _i - utječu varijable, a _i - regresije koeficijenti, ak - broj faktora.

Y za danu zadaću - to je pokazatelj da se vatra zaposlenika, faktor - plaća, koji je označen s X.

Iskorištavanje moć „Excel” tablicu

Regresijska analiza u Excelu treba prethoditi zahtjeva za postojeće tablice podataka ugrađenih funkcija. Međutim, za ove namjene je bolje koristiti vrlo korisno dodati-in „paketa analize”. Da ga omogućite, potrebno je:

• s kartice „File”, idite na „Settings”;
• u prozoru koji se otvori odaberite „Add-ons”;
• kliknite na gumb „Go”, koji se nalazi u donjem desnom kutu linije „upravljanje”;
• stavi kvačicu pored „Alati za analizu” i potvrdite postupak pritiskom na „OK”.

Ako se pravilno obavljeno, desna strana kartici „Data”, koja se nalazi iznad radnog lista „Excel”, pokazuje željenu tipku.

Linearna regresija u Excelu

Sada kada imate pri ruci sve potrebne virtualne alate za ekonometrijskih izračunima, možemo početi rješavati naše probleme. U tu svrhu:

• se klikne gumb na „analizu podataka”;
• kliknite na gumb „regresije” u otvorenom prozoru;
• kartica koji se pojavljuje uvesti niz vrijednosti Y (broj radnika rastave) i X (je njihova plaća);
• potvrditi svoje postupke pritiskom na «OK» tipku.

Kao rezultat toga, program će automatski popuniti novi list proračunske tablice regresijske analize podataka. Obratite pozornost! U programu Excel, postoji mogućnost da postavite mjesto koje želite za tu svrhu. Na primjer, to može biti isti list, gdje su vrijednosti Y i X, ili čak nova knjiga, posebno dizajniran za spremanje takvih podataka.

Rezultati regresijske analize za R-kvadrat

Excel podaci dobiveni u smatraju primjer podataka ima oblik:

Prije svega, treba obratiti pozornost na vrijednost od R-kvadratna. On predstavlja koeficijent determinacije. 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 determinacije, odabrani model smatra se korisnijim za pojedine poslove. Smatra se da pravilno opisati stvarnu situaciju na R-kvadratna vrijednost iznad 0,8. Ako je R-kvadrat <0,5, a zatim i regresijska analiza u Excelu ne može se smatrati razumno.

analiza omjer

Broj 64,1428 pokazuje što će se vrijednost Y, ako sve varijable xi u našem modelu će se vratiti. Drugim riječima, može se reći da je vrijednost promatranog parametra je pod utjecajem drugih čimbenika od onih opisanih u konkretnom 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 „-” znači da je koeficijent je negativan. Očito je, jer svi znamo da je više plaća u poduzeću, manje ljudi su izrazili želju da raskine ugovor o radu ili odbaciti.

višestruka regresija

Pod ovim pojmom se odnosi na komunikacijske jednadžbi s nekoliko nezavisnih varijabli u obliku:

y = f (x ₁ + x ₂ + ... x _m) + ε, gdje je y - je značajka rezultat (zavisna varijabla) i x _1, x _2, ... _m x - znakovi su faktori (nezavisne varijable).

procjena parametra

Multiple regresije (MR) kako se izvodi korištenjem metode najmanjih kvadrata (LSM). Linearnih jednadžbi oblika Y = a + b _{+ 1} x _{1 m ... + b x m +} ε izgradnja sustava normalnih jednadžbi (cm, ispod)

Da bismo razumjeli princip metode, smatramo dvije-faktor slučaj. Onda smo se situacija opisana formulom

Dakle, dobivamo:

gdje je σ - varijanca dotičnog lice, koje se ogledaju u indeksu.

MNC se odnosi na jednadžba MR da standartiziruemom razmjera. U tom slučaju, dobiva se izraz:

gdje je t _y, t _{x 1, ...} t _xm - standartiziruemye varijable koje su srednje vrijednosti 0; P _ja - standardizirane regresijskih koeficijenata i standardnu devijaciju - 1.

Imajte na umu da svi P _ja u ovom slučaju, kao što je definirano i normalizirane tsentraliziruemye stoga usporedba između valjan i prihvatljiv. Osim toga, on je prihvatio provesti screening faktora, odbacujući one koji imaju najniže vrijednosti βi.

Problem s korištenjem linearne regresijske jednadžbe

Pretpostavimo da imate tablicu dinamike cijena određenog proizvoda N u posljednjih 8 mjeseci. Potrebno je da odluči hoće li stjecanje njegove stranke po cijeni od 1850 rubalja. / T.

		B	C
1	mjesec	naziv mjeseca	Cijena N
2	1	siječanj	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 po toni
6	5	svibanj	1770 rubalja po toni
7	6	lipanj	1790 rubalja po toni
8	7	srpanj	1810 rubalja po toni
9	8	kolovoz	1840 rubalja po toni

Kako bi riješio taj problem u tabličnom procesor „Excel” potrebnu za korištenje već poznato, na primjer alata „Analiza podataka” koja je ovdje prikazana. Zatim izaberite poglavlje „Regresijska” i postavite 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). Mi potvrditi akciju klikom na «OK». U novom radnom listu (ako je tako naznačeno), 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 jednadžba regresije (EQ) 3 za problem se može napisati kao:

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

ili algebarski zapis

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” regresije s rezultatima oni djeluju pod imenima višestruki R, R-kvadrat, F-t-statistika i statistika, odnosno.

KMC R omogućuje procjenu vjerojatnosti blizinu odnos između nezavisne i zavisne varijable. Njegova visoka vrijednost ukazuje dovoljno jaku vezu između varijabilnog „Broj mjesecu” i „N cijene proizvoda u rubalja po 1 toni.” Međutim, priroda tog odnosa je nepoznat.

Kvadrat koeficijenta determinacije ^R2 (R) je brojčana karakteristika udio ukupnog raspršiti i pokazuje raspršiti eksperimentalnog dijela podataka, tj Vrijednosti zavisne varijable odgovaraju linearne regresijske jednadžbe. U ovom problemu, ova vrijednost je 84,8%, tal. Su E. Statistika s visokim stupnjem točnosti dobivenih opisani SD.

F-statistika, također poznat kao Fisher kriterij koristi za procjenu značaja linearne ovisnosti ili opovrgavajući tezu potvrđuje svoje postojanje.

Vrijednost t-statistike (Studentov t test) pomaže procijeniti značaj koeficijenta u svakom slobodnom nepoznatog linearnog člana ovisnosti. Ako vrijednosti t-testa> t _cr, hipoteza o linearnoj jednadžbi beznačajnosti slobodan termin je odbijen.

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 nepoznati koeficijent u t = 5,79405 i p = 0,001158. Drugim riječima, vjerojatnost da je odbacio ispravna hipoteza će zanemarivosti koeficijenta za nepoznato, je 0,12%.

Dakle, može se reći da je dobivena linearne regresije jednadžbu na odgovarajući način.

Problem uputnosti kupnju dionica

Multipla regresija je provedena u Excelu koristeći istu „Analiza podataka” alat. Razmislite specifičnu primjenu.

Vodič za poduzeće «NNN» mora odlučiti hoće li kupiti 20% dionica JSC «MMM». Cijena paketa (SP) je 70 milijuna američkih dolara. Stručnjaci «NNN» prikupljaju podatke o sličnim transakcijama. Odlučeno je da se procijeniti vrijednost dionica na takvim parametrima, izražen u milijunima američkih dolara, kao što su:

• obveze (VK);
• Volumen godišnji promet (VO);
• potraživanja (VD);
• vrijednost dugotrajne imovine (SOF).

Osim toga, koristite dugove plaća za poduzeća (V3 u) U tisuća dolara.

Na odluku stolni procesor Excel sredstva

Prvo morate stvoriti tablicu ulaznih podataka. To je kao što slijedi:

Sljedeća:

• kutija poziv „analiza podataka”;
• Izabrani dio „regresija”;
• prozor „unos interval zavisne vrijednosti varijabli Y» daju raspon od stupac G;
• kliknite na ikonu s crvenom strelicom na desno od prozora „Ulaz Interval X» i izolira se na niz listova svih vrijednosti stupca B, C, D, F.

Označite točku „Novi list” i kliknite na „OK”.

Nabavite regresijske analize za ovaj zadatak.

Rezultati istraživanja i zaključci

„Pokupi” zaokruženo na temelju podataka iznesenih na listu tablice Excel procesor regresijske jednadžbe:

SD = 0,103 * + SOF 0541 * VO - 0.031 * VK + 0405 * 0691 * + VD VZP - 265.844.

U više uobičajenih matematički oblik se može zapisati kao:

y = 0,103 * x1 + 0541 * x2 - 0.031 * x3 + 0405 * 0691 * X4 + x5 - 265.844

Podaci za «MMM» dd predstavljeni u tablici u nastavku:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45.2	41,5	21.55	64.72

Uvrštavanjem ih u regresijskoj jednadžbi, dobiveni lik 64.72 milijuna dolara. To znači da su dionice JSC «MMM» ne bi trebao kupiti, jer je njihova cijena je prilično precijenjen na 70 milijuna dolara.

Kao što možete vidjeti, uporaba tablice „Excel”, a jednadžba regresije dozvoljeno napraviti jedan informirane odluke o svrhovitost sasvim specifičnu transakciju.

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