Regresija u Excelu

Statistička obrada podataka se može izvršiti i pomoću dodatka paketa Analiza u stavci menija "Servis". U Excel 2003, ako otvorite SERVIS, ne nalazimo karticu ANALIZA PODATAKA, a zatim klikom na lijevu tipku miša otvorite karticu NADGRADNJE i suprotna tačka PAKET ANALIZE klikom na levi taster miša stavite kvačicu (Sl. 17).

Rice. 17. Prozor NADGRADNJE

Nakon toga u meniju SERVIS pojavljuje se kartica ANALIZA PODATAKA.

U programu Excel 2007 za instalaciju PAKET ANALIZE potrebno je da kliknete na dugme URED u gornjem levom uglu lista (Sl. 18a). Zatim kliknite na dugme EXCEL PARAMETRI... U prozoru koji se pojavi EXCEL PARAMETRI kliknite levim tasterom miša na stavku NADGRADNJE i na desnoj strani padajuće liste izaberite stavku PAKET ANALIZE. Zatim kliknite na uredu.

Excel opcije Office dugme

Rice. 18. Instalacija PAKET ANALIZE u Excel 2007

Da biste instalirali Analysis Package, kliknite na dugme IDE, nalazi se na dnu otvorenog prozora. Prozor prikazan na sl. 12. Stavite kvačicu nasuprot PAKET ANALIZE. U kartici PODACI pojaviće se dugme ANALIZA PODATAKA(sl. 19).

Od predloženih stavki bira stavku " REGRESIJA„I kliknite na njega lijevim dugmetom miša. Zatim kliknite OK.

Prozor prikazan na sl. 21

Alat za analizu" REGRESIJA»Koristi se za uklapanje grafa za skup opažanja koristeći metodu najmanjih kvadrata. Regresija se koristi za analizu učinka na pojedinačnu zavisnu varijablu vrijednosti jedne ili više varijabli objašnjenja. Na primjer, nekoliko faktora utiče na atletske performanse sportiste, uključujući godine, visinu i težinu. Možete izračunati uticaj svakog od ova tri faktora na performanse jednog sportiste, a zatim koristiti te podatke da predvidite učinak drugog sportiste.

Alat Regresija koristi funkciju LINEST.

REGRESSION Dijaloški okvir

Oznake Označite potvrdni okvir ako prvi red ili prva kolona raspona unosa sadrži zaglavlja. Poništite ovaj potvrdni okvir ako nema naslova. U ovom slučaju će se automatski generirati odgovarajuća zaglavlja za podatke izlazne tablice.

Nivo pouzdanosti Potvrdite izbor u polju za potvrdu da biste uključili dodatni nivo u tabelu ukupnih rezultata. U odgovarajuće polje unesite nivo pouzdanosti za primenu, pored podrazumevanog nivoa od 95%.

Konstanta - nula Označite polje za potvrdu da linija regresije prolazi kroz ishodište.

Izlazni razmak Unesite referencu na gornju lijevu ćeliju izlaznog raspona. Odredite najmanje sedam kolona za tabelu ukupnih izlaznih rezultata, koja će uključivati: rezultate ANOVA, koeficijente, standardnu ​​grešku izračunavanja Y, standardne devijacije, broj opažanja, standardne greške za koeficijente.

Novi radni list Odaberite ovaj prekidač da otvorite novi radni list u radnoj knjizi i umetnete rezultate analize počevši od ćelije A1. Ako je potrebno, unesite naziv za novi list u polje nasuprot odgovarajućeg položaja prekidača.

Nova radna sveska Kliknite na prekidač na ovu poziciju da kreirate novu radnu svesku u kojoj će rezultati biti dodati na novi list.

Ostaci Označite potvrdni okvir da biste uključili ostatke u izlaznu tablicu.

Standardizirani ostaci Označite polje za potvrdu da biste uključili standardizirane ostatke u izlaznu tablicu.

Iscrtaj ostatke Označite polje za potvrdu za iscrtavanje reziduala za svaku nezavisnu varijablu.

Grafikon uklapanja Označite polje za potvrdu da biste nacrtali grafikon predviđenih vrijednosti naspram uočenih vrijednosti.

Grafikon normalne vjerovatnoće Označite polje za iscrtavanje normalnog grafika vjerovatnoće.

Funkcija LINEST

Da biste izvršili proračune, odaberite ćeliju u kojoj želimo da prikažemo prosječnu vrijednost pomoću kursora i pritisnite tipku = na tastaturi. Zatim u polju Ime naznačite željenu funkciju, na primjer PROSJEČNO(sl. 22).

Rice. 22 Pronalaženje funkcija u programu Excel 2003

Ako je na terenu NAME naziv funkcije se ne pojavljuje, zatim lijevom tipkom miša kliknite na trokut pored polja, nakon čega će se pojaviti prozor sa listom funkcija. Ako ove funkcije nema na listi, kliknite levim tasterom miša na stavku liste DRUGE FUNKCIJE, pojavit će se okvir za dijalog MAJSTOR FUNKCIJA, u kojem pomoću vertikalnog pomicanja odaberite željenu funkciju, odaberite je kursorom i kliknite na uredu(sl. 23).

Rice. 23. Čarobnjak za funkcije

Za traženje funkcije u Excelu 2007 može se otvoriti bilo koja kartica u meniju, a zatim za proračune odabrati ćeliju u kojoj želimo da prikažemo prosječnu vrijednost i pritisnite tipku = na tastaturi. Zatim u polju Ime navedite funkciju PROSJEČNO... Prozor za izračunavanje funkcije sličan je onom prikazanom u programu Excel 2003.

Također možete odabrati karticu Formule i kliknuti lijevom tipkom miša na dugme u meniju " INSERT FUNCTION”(Sl. 24), pojavit će se prozor MAJSTOR FUNKCIJA, čiji je tip sličan Excelu 2003. Također u meniju možete odmah odabrati kategoriju funkcija (nedavno korištene, finansijske, logičke, tekstualne, datum i vrijeme, matematičke, druge funkcije), u kojima ćemo tražiti željenu funkciju.

Ostale funkcije Reference i nizovi Matematički

Rice. 24 Odabir funkcije u programu Excel 2007

Funkcija LINEST izračunava statistiku za niz koristeći najmanje kvadrate za izračunavanje prave linije koja najbolje odgovara dostupnim podacima, a zatim vraća niz koji opisuje rezultirajuću ravnu liniju. Također možete kombinirati funkciju LINEST s drugim funkcijama za izračunavanje drugih vrsta modela koji su linearni u nepoznatim parametrima (čiji su nepoznati parametri linearni), uključujući polinomske, logaritamske, eksponencijalne i nizove stepena. Pošto se vraća niz vrijednosti, funkcija mora biti navedena kao formula niza.

Jednačina za pravu liniju je sljedeća:

(u slučaju višestrukih raspona x vrijednosti),

gdje je zavisna vrijednost y funkcija nezavisne vrijednosti x, m vrijednosti su koeficijenti koji odgovaraju svakoj nezavisnoj x varijabli, a b je konstanta. Imajte na umu da y, x i m mogu biti vektori. Funkcija LINEST vraća niz . LINEST može također vratiti dodatnu statistiku regresije.

LINEST(poznati_y; poznati_x; konst; statistika)

Poznati_y su skup y-vrijednosti koje su već poznate za odnos.

Ako poznati_y ima jedan stupac, onda se svaki stupac u poznatom_x tumači kao zasebna varijabla.

Ako poznati_y ima jedan red, onda se svaki red u poznatim_x tumači kao zasebna varijabla.

Poznati_x su izborni skup x vrijednosti koje su već poznate za odnos.

Poznati_x mogu sadržavati jedan ili više skupova varijabli. Ako se koristi samo jedna varijabla, tada poznati_y i poznati_x mogu biti bilo kojeg oblika, sve dok imaju istu dimenziju. Ako se koristi više od jedne varijable, poznati_y mora biti vektor (to jest, jedan red visok ili jedan stupac širok).

Ako je niz_ poznatih_x izostavljen, onda se pretpostavlja da je ovaj niz (1; 2; 3; ...) iste veličine kao niz_ poznati_y.

Const je Boolean vrijednost koja pokazuje da li konstanta b mora biti 0.

Ako je const TRUE ili je izostavljena, konstanta b se procjenjuje na uobičajeni način.

Ako je argument "const" FALSE, tada se vrijednost b postavlja na 0, a vrijednosti m se biraju na način da relacija vrijedi.

Statistics je Boolean vrijednost koja pokazuje da li želite da vratite dodatne statistike za regresiju.

Ako je statistika TRUE, LINEST vraća dodatnu statistiku regresije. Vraćeni niz će izgledati ovako: (mn; mn-1; ...; m1; b: sen; sen-1; ...; se1; seb: r2; sey: F; df: ssreg; ssresid).

Ako je statistika FALSE ili je izostavljena, LINEST vraća samo koeficijente m i konstantu b.

Dodatna statistika regresije.

Veličina Opis se1, se2, ..., sen Standardne vrijednosti greške za koeficijente m1, m2, ..., mn. seb Standardna vrijednost greške za konstantu b (seb = # N / A ako je const FALSE). r2 Koeficijent determinizma. Stvarne y-vrijednosti se upoređuju sa vrijednostima dobijenim iz jednačine prave linije; na osnovu rezultata poređenja izračunava se koeficijent determinizma, normalizovan sa 0 na 1. Ako je jednak 1, postoji potpuna korelacija sa modelom, odnosno nema razlike između stvarnog i procenjenog vrijednosti y. Inače, ako je koeficijent determinizma 0, nema smisla koristiti jednadžbu regresije za predviđanje y vrijednosti. Za više informacija o tome kako se izračunava r2, pogledajte "Napomene" na kraju ovog odjeljka. sey Standardna greška za procjenu y. F F-statistička ili F-opažena vrijednost. F statistika se koristi za određivanje da li je posmatrani odnos između zavisnih i nezavisnih varijabli slučajan. df Stepeni slobode. Stupnjevi slobode su korisni za pronalaženje F-kritičnih vrijednosti u statističkoj tabeli. Da biste odredili nivo pouzdanosti modela, uporedite vrijednosti u tabeli sa F-statistikom koju vraća LINEST. Za više informacija o izračunavanju df, pogledajte Napomene na kraju ovog odjeljka. Primjer 4 ispod pokazuje korištenje vrijednosti F i df. ssreg Regresijski zbir kvadrata. ssresid Preostali zbir kvadrata. Za više informacija o izračunavanju vrijednosti ssreg i ssresid, pogledajte Napomene na kraju ovog odjeljka.

Slika ispod pokazuje redoslijed kojim se vraćaju dodatne statistike regresije.

napomene:

Bilo koja prava linija može se opisati svojim nagibom i presjekom sa y-osom:

Nagib (m): Da biste odredili nagib prave linije, koja se obično označava sa m, trebate uzeti dvije tačke prave linije i; nagib će biti .

Y-presek (b): Y-presek prave, koji se obično označava sa b, je y-vrednost tačke u kojoj prava seče y-osu.

Jednačina prave linije ima oblik. Ako znate vrijednosti m i b, možete izračunati bilo koju tačku na liniji zamjenom vrijednosti y ili x u jednadžbi. Također možete koristiti funkciju TREND.

Ako postoji samo jedna nezavisna varijabla x, možete dobiti nagib i y-presjek direktno koristeći sljedeće formule:

Nagib: INDEX (LINEST (poznati_y; poznati_x); 1)

Y-presjek: INDEX (LINEST (poznati_y; poznati_x); 2)

Preciznost aproksimacije LINEST linije zavisi od stepena raspršenosti podataka. Što su podaci bliži pravoj liniji, to je LINEST model precizniji. LINEST koristi metodu najmanjih kvadrata za određivanje najboljeg uklapanja podataka. Kada postoji samo jedna nezavisna varijabla x, m i b se izračunavaju pomoću sljedećih formula:

gdje su x i y uzorci, na primjer x = PROSJEK (poznati_x) i y = PROSJEK (poznati_y).

Funkcije uklapanja LINEST i LOGEST mogu izračunati ravnu ili eksponencijalnu krivu koja najbolje opisuje podatke. Međutim, oni ne daju odgovor na pitanje koji je od dva rezultata pogodniji za rješavanje zadatka. Također možete izračunati TREND (poznati_y; poznati_x) za pravu liniju ili RAST (poznati_y; poznati_x) za eksponencijalnu krivu. Ove funkcije, ako ne navedete new_x_values, vraćaju niz izračunatih y-vrijednosti za stvarne x-vrijednosti duž prave linije ili krivulje. Izračunate vrijednosti se zatim mogu uporediti sa stvarnim vrijednostima. Takođe možete napraviti grafikone za vizuelno poređenje.

Sa regresijskom analizom, Microsoft Excel izračunava, za svaku tačku, kvadrat razlike između predviđene y-vrijednosti i stvarne y-vrijednosti. Zbir ovih kvadrata razlika naziva se rezidualni zbir kvadrata (ssresid). Microsoft Excel zatim izračunava ukupan zbir kvadrata (sstotal). Ako je const = TRUE ili izostavljeno, ukupan zbir kvadrata jednak je zbroju kvadrata razlike između stvarnih vrijednosti y i srednjih vrijednosti y. Kada je const = FALSE, ukupan zbir kvadrata će biti jednak zbiru kvadrata stvarnih vrijednosti y (bez oduzimanja srednje vrijednosti y od kvocijenta vrijednosti y). Regresijski zbir kvadrata se tada može izračunati na sljedeći način: ssreg = sstotal - ssresid. Što je manji rezidualni zbir kvadrata, to je veća vrijednost koeficijenta determinizma r2, što pokazuje koliko dobro jednačina dobijena regresionom analizom objašnjava odnos između varijabli. Koeficijent r2 je ssreg / sstotal.

U nekim slučajevima, jedan ili više X stupaca (neka Y i X vrijednosti budu u kolonama) nemaju dodatnu predikativnu vrijednost u drugim stupcima X. Drugim riječima, brisanje jednog ili više X stupaca može rezultirati Y vrijednostima ​izračunato sa istom preciznošću. U ovom slučaju, redundantni X stupci će biti isključeni iz regresijskog modela. Ovaj fenomen se naziva "kolinearnost" jer se redundantni X kolone mogu predstaviti kao zbir više neredundantnih kolona. LINEST provjerava kolinearnost i uklanja sve suvišne X stupce iz regresijskog modela ako ih pronađe. Izbrisani X stupci mogu se identificirati u LINEST izlazu faktorom 0 i se vrijednošću 0. Uklanjanje jednog ili više stupaca kao suvišnih mijenja df vrijednost jer ovisi o broju X stupaca koji se stvarno koriste u svrhe predviđanja. Za više informacija o izračunavanju df, pogledajte primjer 4. Kada se df promijeni zbog uklanjanja suvišnih stupaca, sey i F se također mijenjaju. Kolinearnost se često obeshrabruje. Međutim, treba ga koristiti ako neka od kolona X sadrži 0 ili 1 kao indikator koji pokazuje da li je subjekt eksperimenta u zasebnoj grupi. Ako je const = TRUE ili izostavljeno, LINEST umeće dodatni X stupac za simulaciju točke presjeka. Ako postoji kolona sa vrijednostima 1 za muškarce i 0 za žene, a postoji i kolona sa vrijednostima 1 za žene i 0 za muškarce, tada se posljednja kolona uklanja jer se njene vrijednosti mogu dobijeno iz kolone sa “indikatorom muškog pola”.

Proračun df za slučajeve kada stupci X nisu uklonjeni iz modela zbog kolinearnosti je sljedeći: ako postoji k kolona poznatih_x i vrijednost const = TRUE ili nije navedena, onda je df = n - k - 1. Ako const = FALSE, tada je df = n - k. U oba slučaja, uklanjanje X stupaca zbog kolinearnosti povećava df vrijednost za 1.

Formule koje vraćaju nizove moraju se unijeti kao formule niza.

Kada unesete niz konstanti za, na primjer, poznati_x, koristite tačku i zarez da odvojite vrijednosti u istom redu i dvotočku da odvojite redove. Znakovi za razdvajanje razlikuju se u zavisnosti od opcija postavljenih u prozoru Jezik i standardi na kontrolnoj tabli.

Treba napomenuti da y-vrijednosti predviđene jednadžbom regresije možda neće biti tačne ako su izvan raspona y-vrijednosti koje su korištene za definiranje jednadžbe.

Glavni algoritam koji se koristi u funkciji LINEST, razlikuje se od glavnog algoritma funkcija INCLINE i ODJELJAK... Razlike između algoritama mogu dovesti do različitih rezultata za nedefinirane i kolinearne podatke. Na primjer, ako su točke podataka poznatih_y 0 i točke podataka poznatih_x 1, tada:

Funkcija LINEST vraća vrijednost jednaku 0. Algoritam funkcije LINEST se koristi za vraćanje valjanih vrijednosti za kolinearne podatke, u kom slučaju se može pronaći barem jedan odgovor.

Funkcije SLOPE i INTERCEPT vraćaju grešku # DIV / 0!. Algoritam funkcija SLOPE i INTERCEPT se koristi za traženje samo jednog odgovora, au ovom slučaju može biti nekoliko.

Pored izračunavanja statistike za druge vrste regresije, LINEST se može koristiti za izračunavanje raspona za druge vrste regresije unosom funkcija x i y kao serije x i y za LINEST. Na primjer, sljedeća formula:

LINEST (y-vrijednosti, x-vrijednosti ^ COLUMN ($ A: $ C))

radi tako što ima jednu kolonu Y vrijednosti i jednu kolonu X vrijednosti za izračunavanje aproksimacije kocki (polinom 3. stepena) sljedećeg oblika:

Formula se može promijeniti kako bi se izračunale druge vrste regresije, ali u nekim slučajevima su potrebna prilagođavanja izlaznih vrijednosti i druge statistike.

Pokazuje učinak nekih vrijednosti (nezavisnih, nezavisnih) na zavisnu varijablu. Na primjer, kako broj ekonomski aktivnog stanovništva zavisi od broja preduzeća, veličine plata i drugih parametara. Ili: kako strane investicije, cijene energije itd. utiču na nivo BDP-a.

Rezultat analize vam omogućava da odredite prioritete. I na osnovu glavnih faktora predviđajte, planirajte razvoj prioritetnih oblasti, donosite upravljačke odluke.

Regresija se dešava:

Linearni (y = a + bx);

Parabolično (y = a + bx + cx 2);

Eksponencijalni (y = a * exp (bx));

Snaga (y = a * x ^ b);

Hiperbolički (y = b / x + a);

Logaritamski (y = b * 1n (x) + a);

Eksponencijalno (y = a * b ^ x).

Pogledajmo primjer izgradnje regresijskog modela u Excelu i interpretacije rezultata. Uzmimo tip linearne regresije.

Zadatak. U 6 preduzeća analizirana je prosječna mjesečna plata i broj zaposlenih koji su dali otkaz. Potrebno je utvrditi zavisnost broja zaposlenih koji su dali otkaz od prosječne plate.

Model linearne regresije je sljedeći:

Y = a 0 + a 1 x 1 + ... + a k x k.

Gdje je a - koeficijenti regresije, x - utjecajne varijable, k - broj faktora.

U našem primjeru, Y je indikator zaposlenih koji su dali otkaz. Faktor uticaja su plate (x).

Excel ima ugrađene funkcije koje možete koristiti za izračunavanje parametara modela linearne regresije. Ali dodatak Analysis Package će to učiniti brže.

Aktiviramo moćan analitički alat:

1. Pritisnite dugme "Office" i idite na karticu "Opcije Excel". "Dodaci".

2. Pri dnu, ispod padajuće liste, u polju "Kontrola" biće natpis "Excel dodaci" (ako ga nema, kliknite na kvadratić sa desne strane i izaberite). I dugme "Idi". Pritiskamo.

3. Otvara se lista dostupnih dodataka. Odaberite "Paket analize" i kliknite OK.

Nakon aktivacije, dodatak će biti dostupan na kartici "Podaci".

Sada idemo direktno na regresionu analizu.

1. Otvorite meni alata "Analiza podataka". Odabiremo "Regresija".

2. Otvoriće se meni za izbor ulaznih vrednosti i izlaznih parametara (gde prikazati rezultat). U poljima za početne podatke označavamo opseg opisanog parametra (Y) i faktor koji na njega utiče (X). Ostatak se može ostaviti praznim.

3. Nakon što kliknete na OK, program će prikazati proračune na novom listu (možete odabrati interval za prikaz na trenutnom listu ili dodijeliti izlaz novoj knjizi).

Prije svega obratite pažnju na R-kvadrat i koeficijente.

R-kvadrat je koeficijent determinacije. U našem primjeru - 0,755, odnosno 75,5%. To znači da izračunati parametri modela objašnjavaju odnos između proučavanih parametara za 75,5%. Što je veći koeficijent determinacije, to je model bolji. Dobro - iznad 0,8. Loše - manje od 0,5 (takva analiza se teško može smatrati razumnom). U našem primjeru - "nije loše".

Koeficijent 64,1428 pokazuje koliki će biti Y ako su sve varijable u modelu koji se razmatraju jednake 0. Odnosno, na vrijednost analiziranog parametra utiču i drugi faktori koji nisu opisani u modelu.

Koeficijent -0,16285 pokazuje težinu varijable X na Y. To jest, prosječna mjesečna plata u okviru ovog modela utiče na broj ljudi koji odustaju sa težinom od -0,16285 (ovo je mali stepen uticaja). Znak “-” ukazuje na negativan uticaj: što je veća plata, manje je onih koji odustaju. Što je pošteno.

Izgradnja linearne regresije, procjena njenih parametara i njihovog značaja može se izvršiti mnogo brže korištenjem Excel paketa za analizu (Regression). Razmotrimo tumačenje rezultata dobijenih u opštem slučaju ( k objašnjavajuće varijable) prema primjeru 3.6.

U tabeli regresijska statistika date su vrijednosti:

Višestruko R - koeficijent višestruke korelacije;

R- kvadrat- koeficijent odlučnosti R 2 ;

Normalizovano R - kvadrat- prilagođeno R 2 korigirano za broj stupnjeva slobode;

Standardna greška- standardna greška regresije S;

Zapažanja - broj zapažanja n.

U tabeli ANOVA daju se:

1. Kolona df - broj stepeni slobode, jednak

za niz Regresija df = k;

za niz Ostatakdf = n – k – 1;

za niz Ukupnodf = n– 1.

2. Kolona SS - zbir kvadrata odstupanja jednak

za niz Regresija ;

za niz Ostatak ;

za niz Ukupno .

3. Kolona GOSPOĐA varijanse određene formulom GOSPOĐA = SS/df:

za niz Regresija- faktorska varijansa;

za niz Ostatak- rezidualna varijansa.

4. Kolona F - izračunata vrijednost F-kriterijum izračunat po formuli

F = GOSPOĐA(regresija)/ GOSPOĐA(ostatak).

5. Kolona Značaj F - vrijednost nivoa značajnosti koji odgovara izračunatom F-statistika .

Značaj F= FDIST ( F- statistika, df(regresija), df(ostatak)).

Ako je značaj F < стандартного уровня значимости, то R 2 je statistički značajno.

	Koeficijenti	Standardna greška	t-statistika	P-vrijednost	donjih 95%	Top 95%
Y	65,92	11,74	5,61	0,00080	38,16	93,68
X	0,107	0,014	7,32	0,00016	0,0728	0,142

Ova tabela pokazuje:

1. Odds- vrijednosti koeficijenata a, b.

2. Standardna greška–Standardne greške regresijskih koeficijenata S a, S b.

3. t- statistika- izračunate vrijednosti t -kriterijumi izračunati po formuli:

t-statistic = koeficijenti / standardna greška.

4.R-vrijednost (značaj t) Da li vrijednost nivoa značajnosti odgovara izračunatoj t- statistika.

R-vrijednost = TDIST(t-statistika, df(ostatak)).

Ako R-značenje< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5... Donjih 95% i gornjih 95%- donje i gornje granice 95% intervala povjerenja za koeficijente teorijske jednačine linearne regresije.

PREOSTALO POVLAČENJE
Opservacija	Predviđeno y	Ostaje e
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

U tabeli PREOSTALO POVLAČENJE naznačeno:

u koloni Opservacija- broj posmatranja;

u koloni Predviđeno y - izračunate vrijednosti zavisne varijable;

u koloni Ostaci e - razlika između posmatrane i izračunate vrednosti zavisne varijable.

Primjer 3.6. Postoje podaci (konvencionalne jedinice) o troškovima hrane y i dohodak po glavi stanovnika x za devet grupa porodica:

x
y

Koristeći rezultate Excel paketa analize (Regresija), analizirajmo zavisnost troškova hrane od visine dohotka po glavi stanovnika.

Uobičajeno je rezultate regresione analize pisati u obliku:

gdje su standardne greške koeficijenata regresije naznačene u zagradama.

Regresijski koeficijenti a = 65,92 i b= 0,107. Smjer komunikacije između y i x određuje predznak koeficijenta regresije b= 0,107, tj. veza je direktna i pozitivna. Koeficijent b= 0,107 pokazuje da sa povećanjem dohotka po glavi stanovnika za 1 konv. jedinice troškovi hrane rastu za 0,107 konv. jedinice

Procijenimo značaj koeficijenata rezultirajućeg modela. Značaj koeficijenata ( a, b) provjerava t-test:

P-vrijednost ( a) = 0,00080 < 0,01 < 0,05

P-vrijednost ( b) = 0,00016 < 0,01 < 0,05,

dakle, koeficijenti ( a, b) su značajni na nivou od 1%, a još više na nivou značajnosti od 5%. Dakle, koeficijenti regresije su značajni i model je adekvatan originalnim podacima.

Rezultati procjene regresije su kompatibilni ne samo s dobivenim vrijednostima regresijskih koeficijenata, već i sa nekim njihovim skupom (interval pouzdanosti). Sa vjerovatnoćom od 95%, intervali povjerenja za koeficijente su (38,16 - 93,68) za a i (0,0728 - 0,142) za b.

Kvaliteta modela se ocjenjuje koeficijentom determinacije R 2 .

Veličina R 2 = 0,884 znači da se 88,4% varijacije (rasprostiranja) u izdacima za hranu može objasniti faktorom dohotka po glavi stanovnika.

Značaj R 2 provjerava F- test: značaj F = 0,00016 < 0,01 < 0,05, следовательно, R 2 je značajan na nivou od 1%, a još više na nivou značajnosti od 5%.

U slučaju uparene linearne regresije, koeficijent korelacije se može definirati kao ... Dobijena vrijednost koeficijenta korelacije ukazuje da je veza između izdataka za hranu i dohotka po glavi stanovnika veoma bliska.

Regresiona analiza je jedna od najtraženijih metoda statističkog istraživanja. Može se koristiti za utvrđivanje stepena uticaja nezavisnih varijabli na zavisnu varijablu. U funkcionalnosti Microsoft Excel-a postoje alati dizajnirani za ovu vrstu analize. Hajde da pogledamo šta su i kako ih koristiti.

Povezivanje paketa analize

Ali, da biste koristili funkciju koja vam omogućava da izvršite regresijsku analizu, prije svega morate aktivirati paket analize. Tek tada će se alati potrebni za ovu proceduru pojaviti na Excel traci.

1. Pređite na karticu "Datoteka".
2. Idite na odjeljak "Parametri".
3. Otvara se prozor sa opcijama programa Excel. Idite na pododjeljak "Dodaci".
4. Na samom dnu prozora koji se otvori, pomaknite prekidač u bloku "Kontrola" na poziciju "Excel dodaci", ako je u drugom položaju. Kliknite na dugme "Idi".
5. Otvara se prozor dostupnih Excel dodataka. Stavljamo kvačicu pored stavke "Paket analize". Kliknite na dugme "OK".

Sada, kada odemo na karticu "Podaci", na traci u okviru alata "Analiza" vidjet ćemo novo dugme - "Analiza podataka".

Vrste regresione analize

Postoji nekoliko vrsta regresije:

• parabolični;
• zakon o moći;
• logaritamski;
• eksponencijalni;
• indikativno;
• hiperbolično;
• linearna regresija.

Pričaćemo detaljnije o performansama poslednje vrste regresione analize u Excel-u.

Linearna regresija u Excelu

U nastavku, kao primjer, nalazi se tabela koja prikazuje prosječnu dnevnu temperaturu zraka napolju, te broj kupaca trgovine za odgovarajući radni dan. Otkrijmo uz pomoć regresione analize kako tačno vremenske prilike u vidu temperature zraka mogu uticati na posjećenost izlaza.

Opšta jednačina linearne regresije izgleda ovako: Y = a0 + a1x1 +… + akhk. U ovoj formuli, Y označava varijablu, uticaj faktora na koje pokušavamo da proučavamo. U našem slučaju to je broj kupaca. X-vrijednost su različiti faktori koji utiču na varijablu. Parametri a su koeficijenti regresije. Odnosno, oni su ti koji određuju značaj ovog ili onog faktora. Indeks k označava ukupan broj ovih istih faktora.

Analiza rezultata analize

Rezultati regresione analize su prikazani u obliku tabele na mestu navedenom u podešavanjima.

Jedan od glavnih indikatora je R-kvadrat. To ukazuje na kvalitetu modela. U našem slučaju ovaj omjer je 0,705 ili oko 70,5%. Ovo je prihvatljiv nivo kvaliteta. Zavisnost manja od 0,5 je loša.

Još jedan važan indikator nalazi se u ćeliji na raskrsnici linije "Y-presjek" i kolone "Koeficijenti". Pokazuje koju će vrijednost imati Y, a u našem slučaju to je broj kupaca, sa svim ostalim faktorima jednakim nuli. U ovoj tabeli ova vrijednost je 58,04.

Vrednost na preseku kolona „Varijabla X1“ i „Koeficijenti“ pokazuje nivo zavisnosti Y od X. U našem slučaju, to je nivo zavisnosti broja kupaca prodavnice od temperature. Omjer od 1,31 smatra se prilično visokim indikatorom uticaja.

Kao što vidite, prilično je lako napraviti tabelu regresijske analize koristeći Microsoft Excel. Ali, samo obučena osoba može raditi sa podacima dobijenim na izlazu i razumjeti njihovu suštinu.

Drago nam je da smo mogli da Vam pomognemo da rešite problem.

Postavite svoje pitanje u komentarima, detaljno opišite suštinu problema. Naši stručnjaci će se potruditi da odgovore u najkraćem mogućem roku.

Da li vam je ovaj članak pomogao?

Linearna regresija nam omogućava da opišemo pravu liniju koja najbolje odgovara nizu uređenih parova (x, y). Jednadžba za pravu liniju, poznata kao linearna jednačina, prikazana je u nastavku:

ŷ je očekivana vrijednost y za datu vrijednost x,

x je nezavisna varijabla,

a - segment na y-osi za pravu liniju,

b - nagib prave linije.

Slika ispod grafički ilustruje ovaj koncept:

Na gornjoj slici prikazana je linija opisana jednadžbom ŷ = 2 + 0,5x. Segment na y-osi je tačka preseka prave sa y-osom; u našem slučaju a = 2. Nagib prave, b, odnos uspona prave i dužine prave, ima vrijednost 0,5. Pozitivan nagib znači da linija ide gore s lijeva na desno. Ako je b = 0, linija je horizontalna, što znači da ne postoji veza između zavisnih i nezavisnih varijabli. Drugim riječima, promjena vrijednosti x ne utiče na vrijednost y.

Ŷ i y su često zbunjeni. Grafikon prikazuje 6 uređenih parova tačaka i pravu prema ovoj jednačini

Ova slika prikazuje tačku koja odgovara uređenom paru x = 2 i y = 4. Imajte na umu da je očekivana vrijednost y prema liniji na X= 2 je ŷ. To možemo potvrditi sljedećom jednačinom:

ŷ = 2 + 0,5x = 2 +0,5 (2) = 3.

Y-vrijednost je stvarna tačka, a-vrijednost je očekivana y-vrijednost koristeći linearnu jednadžbu za datu x-vrijednost.

Sljedeći korak je određivanje linearne jednadžbe koja najviše odgovara skupu uređenih parova, o tome smo govorili u prethodnom članku, gdje smo metodom najmanjih kvadrata odredili oblik jednadžbe.

Korištenje Excela za definiranje linearne regresije

Da biste koristili alat za regresijsku analizu ugrađen u Excel, morate aktivirati dodatak Paket analiza... Možete ga pronaći klikom na karticu Datoteka -> Opcije(2007+), u dijaloškom okviru koji se pojavi ParametriExcel idite na karticu Dodaci. Na terenu Kontrola izabrati DodaciExcel i kliknite Idi. U prozoru koji se pojavi stavite kvačicu nasuprot Paket analiza, pritisnemo UREDU.

U kartici Podaci u grupi Analiza pojaviće se novo dugme Analiza podataka.

Da bismo demonstrirali kako funkcioniše dodatak, upotrijebimo podatke iz prethodnog članka, gdje momak i djevojka dijele sto u kupatilu. Unesite podatke za naš primjer kade u kolone A i B prazne ploče.

Idite na karticu podaci, u grupi Analiza kliknite Analiza podataka. U prozoru koji se pojavi Analiza podataka izabrati Regresija kao što je prikazano i kliknite na OK.

Postavite potrebne parametre regresije u prozoru Regresija, kao što je prikazano na slici:

Kliknite UREDU. Slika ispod prikazuje dobijene rezultate:

Ovi rezultati su u skladu s onima koje smo dobili samoproračunom u prethodnom članku.

Regresiona analiza je statistička metoda istraživanja koja vam omogućava da pokažete ovisnost parametra o jednoj ili više nezavisnih varijabli. U predkompjuterskoj eri, njegova primjena je bila prilično teška, posebno kada se radilo o velikim količinama podataka. Danas, nakon što ste naučili kako napraviti regresiju u Excelu, možete riješiti složene statističke probleme za samo nekoliko minuta. U nastavku su dati konkretni primjeri iz oblasti ekonomije.

Tipovi regresije

Sam koncept je u matematiku uveo Francis Galton 1886. Regresija se dešava:

• linearno;
• parabolični;
• zakon o moći;
• eksponencijalni;
• hiperbolično;
• indikativno;
• logaritamski.

Primjer 1

Razmotrimo problem utvrđivanja zavisnosti broja zaposlenih koji su napustili posao od prosječne plate u 6 industrijskih preduzeća.

Zadatak. Šest preduzeća je analiziralo prosječnu mjesečnu platu i broj zaposlenih koji su dobrovoljno napustili posao. U tabelarnom obliku imamo:

Za problem određivanja zavisnosti broja napuštenih radnika od prosečne plate u 6 preduzeća, regresioni model ima oblik jednačine Y = a0 + a1 × 1 +… + akxk, gde su xi uticajne varijable, ai su koeficijenti regresije, a k je broj faktora.

Za ovaj zadatak Y je indikator zaposlenih koji su dali otkaz, a faktor koji utiče je plata koju označavamo sa X.

Korištenje mogućnosti Excel procesora tablica

Regresionoj analizi u Excel-u mora prethoditi primjena ugrađenih funkcija na postojeće tabelarne podatke. Međutim, za ove svrhe bolje je koristiti vrlo koristan dodatak "Paket analize". Da biste ga aktivirali potrebno vam je:

• sa kartice "Datoteka" idite na odjeljak "Parametri";
• u prozoru koji se otvori odaberite redak "Dodaci";
• kliknite na dugme "Idi" koje se nalazi ispod, desno od linije "Kontrola";
• stavite kvačicu pored naziva "Paket analize" i potvrdite svoje radnje klikom na "OK".

Ako je sve urađeno kako treba, potrebno dugme će se pojaviti na desnoj strani kartice "Podaci", koja se nalazi iznad "Excel" radnog lista.

Linearna regresija u Excelu

Sada kada imamo pri ruci sve potrebne virtuelne alate za izvođenje ekonometrijskih proračuna, možemo pristupiti rješavanju našeg problema. Za ovo:

• kliknite na dugme "Analiza podataka";
• u prozoru koji se otvori kliknite na dugme "Regresija";
• u kartici koja se pojavi unesite raspon vrijednosti za Y (broj zaposlenih koji su dali otkaz) i za X (njihove plate);
• potvrđujemo naše radnje pritiskom na dugme "OK".

Kao rezultat, program će automatski popuniti novi list procesora proračunskih tablica podacima regresione analize. Bilješka! Excel ima mogućnost samostalnog definiranja lokacije koju preferirate u tu svrhu. Na primjer, to može biti isti list koji sadrži Y i X vrijednosti, ili čak nova radna knjiga posebno dizajnirana za pohranjivanje ove vrste podataka.

Analiza rezultata regresije za R-kvadrat

U Excelu, podaci dobijeni tokom obrade podataka predmetnog primjera su sljedeći:

Prije svega, obratite pažnju na vrijednost R-kvadrata. 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 tcr, hipoteza o beznačajnosti slobodnog člana linearne jednačine se odbacuje.

U razmatranom zadatku za slobodan termin pomoću Excel alata dobijeno je da je t = 169,20903, a p = 2,89E-12, odnosno da imamo nultu vjerovatnoću da će tačna hipoteza o beznačajnosti slobodnog člana biti odbijeno. Za koeficijent kod nepoznate t = 5,79405, i p = 0,001158. Drugim riječima, vjerovatnoća da će tačna hipoteza o beznačajnosti koeficijenta sa nepoznatom biti odbačena je 0,12%.

Stoga se može tvrditi da je rezultirajuća jednačina linearne regresije adekvatna.

Problem svrsishodnosti kupovine paketa akcija

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

Menadžment kompanije "NNN" mora odlučiti o svrsishodnosti kupovine 20% udjela u AD "MMM". Cijena paketa (JV) je 70 miliona američkih dolara. Stručnjaci NNN-a prikupili su podatke o sličnim transakcijama. Odlučeno je da se vrijednost paketa dionica procijeni po takvim parametrima, izraženim u milionima američkih dolara, kao što su:

• obaveze prema dobavljačima (VK);
• obim godišnjeg prometa (VO);
• potraživanja (VD);
• trošak osnovnih sredstava (SOF).

Pored toga, parametar su zaostale plate preduzeća (V3 P) u hiljadama američkih dolara.

Excel rješenje za tabelarne proračune

Prije svega, potrebno je kreirati tabelu početnih podataka. izgleda ovako:

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

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

Dobijte regresijsku analizu za dati zadatak.

Studija rezultata i zaključaka

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

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 AD "MMM" prikazani su u tabeli:

Ako ih zamijenimo u regresionu jednačinu, cifra je 64,72 miliona američkih dolara. To znači da akcije AD "MMM" ne treba kupovati, jer je njihova vrijednost od 70 miliona američkih dolara prilično precijenjena.

Kao što možete vidjeti, korištenje Excel procesora proračunskih tablica i jednadžbe regresije omogućilo je donošenje informirane odluke u pogledu preporučljivosti vrlo specifične transakcije.

Sada znate šta je regresija. Gore navedeni primjeri u Excel-u pomoći će vam da riješite praktične probleme u oblasti ekonometrije.