Regresija u Excelu: jednadžba primjera. linearne regresije

Regresija analiza - statistička metoda studija koja pokazuje zavisnost parametar jedne ili više nezavisnih varijabli. U pred-kompjuter doba, njegova upotreba je dosta teško, pogotovo kada je u pitanju velike količine podataka. Danas, učenje kako izgraditi regresije u Excel, možete riješiti složene statističke probleme u samo nekoliko minuta. U nastavku su specifični primjeri ekonomije.

vrste regresija

Ovaj koncept je predstavljen matematike Francis Galton 1886. godine. Regresije je:

• linearna;
• parabolični;
• moć;
• eksponencijalna;
• hiperbolični;
• eksponencijalna;
• logaritamska.

PRIMJER 1

Razmotrimo problem određivanja zavisnost broja ostavki članova osoblja prosječne plate u 6 industrijskim preduzećima.

Zadatak. Šest kompanija su analizirali prosječne mjesečne plate i broj zaposlenih koji je napustio dobrovoljno. U tabelarnom obliku imamo:

	A	B	C
1	X	Broj ostavki	zarada
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 zavisnost radnika iznos razdvajanje od prosječne plaće za 6 preduzeća regresije model ima oblik jednadžbe Y = a ₀ + a ₁ x ₁ + ... + a _k x _k, gdje je x _i - utiču varijable, a _i - regresija koeficijenti, ak - broj faktora.

Y za dati zadatak - to je pokazatelj da se otpustiti zaposlenog, a faktor - zarade, koja se označava sa X.

Koristeći moć "Excel" proračunske tablice

regresijska analiza u Excel treba prethoditi zahtjev za postojeće tablice podataka ugrađene funkcije. Međutim, za ove namjene je bolje koristiti vrlo koristan dodatak "analize paket". Da biste omogućili to, potrebno je da:

• s karticu "Datoteka" idite na "Settings";
• u prozoru koji se otvori, izaberite 'Add-ons';
• kliknite na dugme "Go", koji se nalazi u donjem desnom linije "upravljanje";
• zatim označite pored "Analiza ToolPak" i potvrdite akciju pritiskom na "OK".

Ako se uradi ispravno, s desne strane na kartici "Data", koji se nalazi iznad radni list "Excel", prikazuje željenu tipku.

Linearna regresija u Excel

Sada kada imate pri ruci sve potrebne virtualnih alata za ekonometrijskih proračuna, možemo početi da se bave naš problem. Da bi se to učiniti:

• dugme se klikne na "Analiza podataka";
• kliknite na dugme "regresija" u otvoren prozor;
• karticu koja se pojavljuje uvesti niz Y vrijednosti (broj razdvajanja radnika) i X (platu);
• potvrđuju njihove akcije pritiskom na «U redu» dugme.

Kao rezultat toga, program će automatski popuniti novi list Tablice podataka regresije analize. Obrati pažnju! U programu Excel, postoji mogućnost da podesite mjesto koje želite za tu svrhu. Na primjer, to može biti isti list, gdje vrijednosti Y i X, ili čak i novu knjigu, posebno dizajniran za čuvanje tih podataka.

Rezultati regresije analize za R-kvadrat

Excel podatke dobijene u smatraju primjer podacima imaju oblik:

Prije svega, treba obratiti pažnju na vrijednost R-kvadrat. To predstavlja koeficijent determinacije. U ovom primjeru, R-kvadrat = 0.755 (75,5%), m. E. izračunatih parametara modela da objasni odnos između parametara razmatra 75,5%. Što je veća vrijednost koeficijenta determinacije, izabrani model se smatra više koristan za određene zadatke. Vjeruje se da se ispravno opisuju stvarnu situaciju na vrijednost R-kvadrat iznad 0,8. Ako je R-kvadrat <0,5, a zatim i regresijska analiza u Excel ne može se smatrati razumno.

odnos analiza

Broj 64,1428 pokazuje kakva će biti vrijednost Y, ako sve varijable xi u našem modelu će se resetovati. Drugim riječima, može se reći da je vrijednost analiziranog parametra utječu i drugi faktori osim onih opisanih u konkretnog modela.

Narednih faktor -,16285 nalazi u ćeliji B18, pokazuje značajan utjecaj varijabla X Y. To znači da je prosječna plaća zaposlenih u modelu utječe na broj ostavki iz mase -,16285, t. E. Stepen njen uticaj na svim mali. Znak "-" označava da je koeficijent negativan. Očito je, jer svi znamo da što više zarade u preduzeću, manje ljudi izrazili želju da raskine ugovor o radu ili odbija.

višestruke regresije

Pod ovim pojmom se odnosi na komunikaciju jednadžbe s više nezavisnih varijabli obrasca:

y = f (x ₁ + x ₂ + ... x _m) + ε, gdje je y - je funkcija rezultat (zavisna varijabla), a x _1, x _2, ... x _m - znaci faktori (nezavisne varijable).

parametar procjene

Za višestruke regresije (MR) se vrši pomoću metode najmanjih kvadrata (LSM). Za linearne jednadžbe u obliku Y = a + b ₁ x _{1 + ... + b m x m +} ε izgradnji sistema normalnih jednadžbi (cm. Ispod)

Da bi se shvatio princip metode, smatramo da je dva-faktor slučaj. Onda imamo situaciju opisao formuli

Stoga, dobijamo:

gdje σ - je varijance odgovarajućih karakteristika, koja se ogleda u indeks.

MNK se primjenjuje na jednadžbu MR da standartiziruemom razmjera. U ovom slučaju, dobijamo jednadžbu:

pri čemu t _y, t _{x 1, ...} t _xm - standartiziruemye varijable za koje prosječne vrijednosti su 0; ß _i - standardizovani koeficijenata regresije i standardna devijacija - 1.

Imajte na umu da su svi beta _i u ovom slučaju definira se kao normalizovano i tsentraliziruemye, stoga poređenje između smatra važi i prihvatljivo. Osim toga, prihvaćeno je da izvrši skrining faktora, odbacujući one koje imaju najniži vrijednosti βi.

Problem sa korištenjem linearne regresije jednadžbe

Pretpostavimo da imate sto dinamike je cijena određenog proizvoda N za zadnjih 8 mjeseci. Neophodno je da odluči da li kupovinom njegova stranka po cijeni od 1850 rubalja. / T.

	A	B	C
1	mjeseca	Naziv meseca	Cijena N
2	1	siječanj	1750 rubalja po toni
3	2	februar	1755 rubalja po toni
4	3	marš	1767 rubalja po toni
5	4	april	1760 rubalja po toni
6	5	maj	1770 rubalja po toni
7	6	jun	1790 rubalja po toni
8	7	juli	1810 rubalja po toni
9	8	avgust	1840 rubalja po toni

Za rješavanje ovog problema u tabelarnom procesor "Excel" potrebno da biste koristili već poznata na primjer funkcije "Analiza podataka" gore navedene. Dalje, odaberite sekciji "regresija" i podesite parametre. Moramo zapamtiti da je u "Raspon ulazne Y» treba uvesti na niz vrijednosti zavisne varijable (u ovom slučaju cijena robe u određenim mjeseci u godini) i u "Input interval X» - za nezavisnu (mjesec). Mi smo potvrdili akciju klikom «U redu». Na novom radnom (ako je tako naznačeno), dobijamo podatke za regresije.

Gradimo na njima linearne jednadžbe u obliku y = ax + b, gdje je kao parametri a i b su koeficijenti iz broja linije meseca i naziv koeficijenata i "Y-raskrsnica" linija list sa rezultatima analize regresije. Dakle, linearne regresije jednadžbe (EQ) 3 za problem se može pisati kao:

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

ili u algebarskom notacijom

y = 11,714 x + 1727,54

Analiza rezultata

Da odluči da li dobili adekvatno linearne regresije jednadžbe pomoću multiple korelacije koeficijenti (CMC) i odlučnost, kao i test i t-test Fisher-a. U tablici "Excel" regresija sa rezultatima oni djeluju pod imenima više R, R-kvadrat, F-t-statistika i statistika, respektivno.

KMC R omogućava da se procijeni bliskost probabilističkog odnos između nezavisnih i zavisnih varijabli. Njegova visoke vrijednosti ukazuje dovoljno jaka veza između varijable "Broj u mjesecu" i "N cijenu proizvoda u rubalja po 1 toni." Međutim, priroda ovog odnosa je nepoznat.

Kvadrat koeficijenta determinacije R ² (RI) je numerički karakteristika udio ukupnog rasipanja i pokazuje rasipanja eksperimentalnih dijela podataka, i.e., vrijednosti zavisne varijable odgovara linearne regresije jednadžbe. U ovaj problem, ova vrijednost je 84,8%, mp. E. Statistika s visokim stupnjem preciznosti dobijeni su opisani SD.

F-statistika, također poznat kao Fisher kriterij koriste se za procjenu značaja linearne zavisnosti ili opovrgnuo hipoteza potvrđuje njegovo postojanje.

Vrijednost t-statistika (t test Student) pomaže procijeniti značaj koeficijenta na bilo koji slobodan nepoznati linearne član ovisnost. Ako je vrijednost t-testa> t _CR, hipoteza linearne jednadžbe beznačajnosti besplatno pojam odbijen.

U ovom problem za slobodan termin kroz instrumente "Excel" utvrđeno je da je t = 169,20903, a p = 2,89E-12, t. E. Have a nula vjerojatnost da će vjerni biti odbijen hipotezu o beznačajnosti slobodnog termina. Iz nepoznatih koeficijent na t = 5,79405, a p = 0,001158. Drugim riječima, vjerojatnost da će odbijen ispravna hipoteza beznačajnosti koeficijenta za nepoznatog, je 0,12%.

Dakle, može se tvrditi da je dobila linearne regresije jednadžbe na adekvatan način.

Problem u potrebu kupnje dionica

Višestruke regresije je izvedena u Excelu koristeći istu "Analiza podataka" alat. Uzmite u obzir specifičnu primjenu.

Vodič za kompaniju «BBB» mora odlučiti da li da kupi 20% dionica AD «MMM». Cijena paketa (SP) je 70 miliona američkih dolara. Specijalisti «BBB» prikupljenih podataka na slične transakcije. Odlučeno je da se procijeni vrijednost dionica na takvim parametrima, izražen u milionima dolara, kao što su:

• obaveze (VK);
• obim godišnjeg prometa (VO);
• potraživanja (VD);
• vrijednost osnovnih sredstava (SOF).

Osim toga, koristite dugovi plate preduzeća (V3 U) u hiljadama američkih dolara.

Odluci sto procesor Excel sredstva

Prvo je potrebno kreirati tablicu ulaznih podataka. To je kako slijedi:

Sljedeće:

• govornica "analizu podataka";
• Odabrani dio "regresija";
• prozor "Input interval Y» upravlja spektar zavisna varijabla vrijednosti iz kolone G;
• kliknite na ikonu sa crvenom strelicom desno od prozora "Input interval X» i izolirani na niz stanja svih vrijednosti kolone B, C, D, F.

Označite point "Novi radni list" i kliknite na "OK".

Nabavite regresijsku analizu za ovaj zadatak.

Studija rezultate i zaključke

"Collect" okruglo iz navedeni podaci na listu sto Excel procesor regresije jednadžbe:

SD = 0.103 * SOF + 0541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265.844.

U više uobičajene matematičke obliku se može pisati kao:

y = 0,103 * x1 + 0541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265844

Podaci za «MMM» dd prikazana u tabeli:

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

Zamjenom ih u regresije jednadžbe, dobio cifru od 64.720.000 američkih dolara. To znači da je dionica AD «MMM» ne bi trebalo da kupuju, jer je njihov trošak je prilično precenjeno po 70 miliona američkih dolara.

Kao što možete vidjeti, korištenje proračunske tablice "Excel" i regresija jednadžbe dozvoljeno da donesu informiranu odluku o uputnosti sasvim konkretne transakcije.

Sada znaš šta nazadovanje. Primjeri u Excel, je već rečeno, pomoći će vam u rješavanju praktičnih problema ekonometrije.