Regresija u Excelu

Statistička obrada podataka može se izvesti i pomoću dodatka paketa Analiza u točki izbornika "Usluga". U Excelu 2003, ako otvorite SERVIS, ne nalazimo karticu ANALIZA PODATAKA, a zatim klikom lijeve tipke miša otvorite karticu NADGRADNJE i suprotna točka PAKET ANALIZE klikom na lijevu tipku miša stavite kvačicu (slika 17).

Riža. 17. Prozor NADGRADNJE

Nakon toga u izborniku SERVIS pojavljuje se kartica ANALIZA PODATAKA.

U programu Excel 2007 za instalaciju PAKET ANALIZE potrebno je kliknuti na gumb URED u gornjem lijevom kutu lista (slika 18a). Zatim kliknite na gumb EXCEL PARAMETRI... U prozoru koji se pojavi EXCEL PARAMETRI kliknite lijevom tipkom miša na stavku NADGRADNJE i na desnoj strani padajućeg popisa odaberite stavku PAKET ANALIZE. Zatim kliknite na u redu.

Excel opcije Uredski gumb

Riža. 18. Instalacija PAKET ANALIZE u Excelu 2007

Da biste instalirali paket za analizu, kliknite na gumb IĆI, koji se nalazi na dnu otvorenog prozora. Prozor prikazan na sl. 12. Stavite kvačicu nasuprot PAKET ANALIZE. U kartici PODACI pojavit će se gumb ANALIZA PODATAKA(slika 19).

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

Prozor prikazan na sl. 21

Alat za analizu" REGRESIJA»Upotrebljava se za uklapanje grafa za skup opažanja metodom najmanjih kvadrata. Regresija se koristi za analizu učinka vrijednosti jedne ili više varijabli objašnjenja na pojedinačnu ovisnu varijablu. Na primjer, nekoliko čimbenika utječe na atletski učinak sportaša, uključujući dob, visinu i težinu. Možete izračunati utjecaj svakog od ova tri čimbenika na izvedbu sportaša, a zatim upotrijebiti te podatke za predviđanje učinka drugog sportaša.

Alat Regresija koristi tu funkciju LINEST.

Dijaloški okvir REGRESSION

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

Razina pouzdanosti Odaberite potvrdni okvir za uključivanje dodatne razine u tablicu ukupnih rezultata. U odgovarajuće polje unesite razinu pouzdanosti koju želite primijeniti, uz zadanu razinu od 95%.

Konstanta - nula Označite potvrdni okvir kako bi linija regresije prolazila kroz ishodište.

Izlazni razmak Unesite referencu na gornju lijevu ćeliju izlaznog raspona. Dodijelite najmanje sedam stupaca za tablicu ukupnih izlaznih vrijednosti, koja će uključivati: rezultate ANOVA, koeficijente, standardnu ​​pogrešku izračuna Y, standardne devijacije, broj opažanja, standardne pogreške za koeficijente.

Novi radni list Odaberite ovaj prekidač da biste otvorili novi radni list u radnoj knjizi i umetnuli 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 knjiga Pritisnite prekidač na ovu poziciju da biste stvorili novu radnu knjigu u kojoj će se rezultati dodati na novi list.

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

Standardizirani ostaci Označite potvrdni okvir za uključivanje standardiziranih ostataka u izlaznu tablicu.

Iscrtaj ostatke Odaberite potvrdni okvir za iscrtavanje reziduala za svaku neovisnu varijablu.

Grafikon uklapanja Označite potvrdni okvir za crtanje grafikona predviđenih vrijednosti naspram promatranih vrijednosti.

Grafikon normalne vjerojatnosti Označite okvir za crtanje normalnog grafa vjerojatnosti.

Funkcija LINEST

Za izračune odaberite ćeliju u kojoj želimo prikazati prosječnu vrijednost kursorom i pritisnite tipku = na tipkovnici. Zatim u polju Naziv navedite željenu funkciju, na primjer PROSJEČAN(slika 22).

Riža. 22 Pronalaženje funkcija u Excelu 2003

Ako na terenu IME naziv funkcije se ne pojavljuje, zatim lijevom tipkom miša kliknite na trokut pored polja, nakon čega će se pojaviti prozor s popisom funkcija. Ako ove funkcije nema na popisu, kliknite lijevom tipkom miša na stavku popisa OSTALE FUNKCIJE, pojavit će se dijaloški okvir MAJSTOR FUNKCIJA, u kojem pomoću okomitog pomicanja odaberite željenu funkciju, odaberite je kursorom i kliknite na u redu(slika 23).

Riža. 23. Čarobnjak za funkcije

Za traženje funkcije u Excelu 2007 u izborniku se može otvoriti bilo koja kartica, zatim za izračune odabrati ćeliju u kojoj želimo prikazati prosječnu vrijednost i pritisnuti tipku = na tipkovnici. Zatim u polju Naziv navedite funkciju PROSJEČAN... Prozor za izračun funkcije sličan je onom prikazanom u Excelu 2003.

Također možete odabrati karticu Formule i kliknuti lijevom tipkom na gumb u izborniku " INSERT FUNCTION”(slika 24), pojavit će se prozor MAJSTOR FUNKCIJA, čija je vrsta slična Excelu 2003. Također u izborniku možete odmah odabrati kategoriju funkcija (nedavno korištene, financijske, logičke, tekstualne, datum i vrijeme, matematičke, druge funkcije), u kojima ćemo tražiti željenu funkciju.

Ostale funkcije Reference i nizovi Matematički

Riža. 24 Odabir funkcije u Excelu 2007

Funkcija LINEST izračunava statistiku za niz pomoću najmanjih kvadrata za izračunavanje ravne 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. Budući da se vraća niz vrijednosti, funkcija mora biti navedena kao formula polja.

Jednadžba za ravnu liniju je sljedeća:

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

gdje je zavisna vrijednost y funkcija neovisne vrijednosti x, m vrijednosti su koeficijenti koji odgovaraju svakoj nezavisnoj varijabli x, 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; const; statistika)

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

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

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

Poznati_x su izborni skup vrijednosti x 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, visok jedan redak ili širina jednog stupca).

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

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

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

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

Statistika je Booleova vrijednost koja pokazuje želite li vratiti dodatnu statistiku 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 pogreške za koeficijente m1, m2, ..., mn. seb Standardna vrijednost pogreške za konstantu b (seb = # N / A ako je const FALSE). r2 Koeficijent determinizma. Stvarne y-vrijednosti uspoređuju se s vrijednostima dobivenim iz jednadžbe ravne linije; na temelju rezultata usporedbe izračunava se koeficijent determinizma, normaliziran od 0 do 1. Ako je jednak 1, postoji potpuna korelacija s modelom, odnosno nema razlike između stvarne i procijenjene vrijednosti ​od god. 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 pogreška za procjenu y. F F-statistička ili F-opažena vrijednost. F statistika se koristi za određivanje je li promatrani odnos između ovisnih i nezavisnih varijabli slučajan. df Stupnjevi slobode. Stupnjevi slobode korisni su za pronalaženje F-kritičnih vrijednosti u statističkoj tablici. Da biste odredili razinu pouzdanosti modela, usporedite vrijednosti u tablici s F-statistikom koju vraća LINEST. Za više informacija o izračunavanju df, pogledajte Napomene na kraju ovog odjeljka. Primjer 4 u nastavku prikazuje korištenje vrijednosti F i df. ssreg Regresijski zbroj kvadrata. ssresid Preostali zbroj kvadrata. Za više informacija o izračunavanju vrijednosti ssreg i ssresid, pogledajte Napomene na kraju ovog odjeljka.

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

Bilješke:

Svaka ravna linija može se opisati njezinim nagibom i sjecištem s y-osom:

Nagib (m): Za određivanje nagiba ravne, koja se obično označava s m, trebate uzeti dvije točke ravne crte i; nagib će biti .

Y-presjek (b): y-presjek pravca, obično označen s b, je y-vrijednost točke u kojoj pravac siječe y-os.

Jednadžba ravne linije ima oblik. Ako znate vrijednosti m i b, možete izračunati bilo koju toč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 izravno dobiti nagib i y-presjek koristeći sljedeće formule:

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

Y-raskrižje: INDEX (LINEST (poznati_y; poznati_x); 2)

Točnost aproksimacije LINEST linije ovisi o stupnju raspršenosti podataka. Što su podaci bliži pravoj liniji, to je LINEST model točniji. LINEST koristi metodu najmanjih kvadrata kako bi odredio što najbolje odgovara podacima. 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 = PROSJEČAN (poznati_x) i y = PROSJEČNI (poznati_y).

Funkcije uklapanja LINEST i LOGEST mogu izračunati ravnu ili eksponencijalnu krivulju koja najbolje opisuje podatke. Međutim, oni ne daju odgovor na pitanje koji je od dva rezultata prikladniji za rješavanje zadatka. Također možete izračunati TREND (poznati_y; poznati_xovi) za ravnu liniju ili RAST (poznati_y; poznati_xovi) za eksponencijalnu krivulju. Ove funkcije, ako ne navedete new_x_values, vraćaju niz izračunatih y-vrijednosti za stvarne x-vrijednosti duž ravne linije ili krivulje. Izračunate vrijednosti se zatim mogu usporediti sa stvarnim vrijednostima. Također možete izraditi grafikone za vizualnu usporedbu.

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

U nekim slučajevima, jedan ili više X stupaca (neka vrijednosti Y i X budu u stupcima) nemaju dodatnu predikativnu vrijednost u drugim stupcima X. Drugim riječima, brisanje jednog ili više X stupaca može rezultirati Y vrijednostima ​izračunati s istom preciznošću. U ovom slučaju, redundantni X stupci bit će isključeni iz regresijskog modela. Taj se fenomen naziva "kolinearnost" jer se redundantni X stupci mogu predstaviti kao zbroj više neredundantnih stupaca. 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 vrijednosti 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 također se mijenjaju. Kolinearnost se često obeshrabruje. Međutim, treba ga koristiti ako neki od stupaca X sadrže 0 ili 1 kao indikator koji pokazuje je li subjekt eksperimenta u zasebnoj skupini. Ako je const = TRUE ili je izostavljen, LINEST umeće dodatni X stupac za simulaciju točke presjeka. Ako postoji stupac s vrijednostima 1 za muškarce i 0 za žene, a postoji i stupac s vrijednostima 1 za žene i 0 za muškarce, tada se zadnji stupac uklanja jer se njegove vrijednosti mogu dobiveno iz stupca s “indikatorom muškog spola”.

Izračun df za slučajeve kada stupci X nisu uklonjeni iz modela zbog kolinearnosti je sljedeći: ako postoji k stupaca poznatih_x i vrijednost const = TRUE ili nije navedena, tada 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 polja.

Prilikom unosa niza konstanti za, na primjer, poznati_x, upotrijebite točku i zarez za odvajanje vrijednosti u istom retku i dvotočku za razdvajanje redaka. Znakovi za razdvajanje razlikuju se ovisno o opcijama postavljenim u prozoru Jezik i standardi na upravljačkoj ploči.

Treba napomenuti da y-vrijednosti predviđene jednadžbom regresije možda neće biti toč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 NAGIB i ODJELJAK... Razlike između algoritama mogu dovesti do različitih rezultata za nedefinirane i kolinearne podatke. Na primjer, ako su podatkovne točke poznatih_y 0, a podatkovne točke poznatih_x 1, tada:

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

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

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

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

radi tako da ima jedan stupac vrijednosti Y i jedan stupac vrijednosti X za izračunavanje aproksimacije kocki (polinom 3. stupnja) sljedećeg oblika:

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

Pokazuje učinak nekih vrijednosti (neovisno, neovisno) na ovisnu varijablu. Na primjer, kako broj ekonomski aktivnog stanovništva ovisi o broju poduzeća, veličini plaća i drugim parametrima. Ili: kako strane investicije, cijene energije itd. utječu na razinu BDP-a.

Rezultat analize omogućuje vam određivanje prioriteta. I na temelju glavnih čimbenika predviđajte, planirajte razvoj prioritetnih područja, donosite upravljačke odluke.

Regresija se događa:

Linearni (y = a + bx);

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

Eksponencijalno (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 poduzeća analizirana je prosječna mjesečna plaća i broj zaposlenih koji su dali otkaz. Potrebno je utvrditi ovisnost broja zaposlenih koji su dali otkaz o prosječnoj plaći.

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 pokazatelj zaposlenika koji su dali otkaz. Faktor utjecaja su plaće (x).

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

Aktiviramo moćan analitički alat:

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

2. Pri dnu, ispod padajućeg popisa, u polju "Kontrola" bit će natpis "Excel dodaci" (ako ga nema, kliknite na potvrdni okvir s desne strane i odaberite). I gumb "Idi". Pritišćemo.

3. Otvara se popis dostupnih dodataka. Odaberite "Paket analize" i kliknite U redu.

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

Idemo sada izravno na regresijsku analizu.

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

2. Otvorit će se izbornik za odabir ulaznih vrijednosti i izlaznih parametara (gdje prikazati rezultat). U poljima za početne podatke označavamo raspon opisanog parametra (Y) i faktor koji na njega utječe (X). Ostatak se može ostaviti praznim.

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

Prije svega obratite pozornost 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 75,5% objašnjavaju odnos između proučavanih parametara. Što je veći koeficijent determinacije, to je model bolji. Dobro - iznad 0,8. Loše - manje od 0,5 (takva se analiza 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 razmatra jednake 0. Odnosno, na vrijednost analiziranog parametra utječu i drugi čimbenici koji nisu opisani u modelu.

Koeficijent -0,16285 pokazuje težinu varijable X na Y. To jest, prosječna mjesečna plaća unutar ovog modela utječe na broj ljudi koji odlaze s težinom od -0,16285 (ovo je mali stupanj utjecaja). Znak "-" označava negativan utjecaj: što je veća plaća, to je manje onih koji odustaju. Što je pošteno.

Izgradnja linearne regresije, procjena njezinih parametara i njihove važnosti može se izvesti puno brže pomoću paketa za analizu programa Excel (Regression). Razmotrimo tumačenje rezultata dobivenih u općem slučaju ( k eksplanatorne varijable) prema primjeru 3.6.

U stolu regresijska statistika vrijednosti su date:

Višestruko R - koeficijent višestruke korelacije;

R- kvadrat- koeficijent odlučnosti R 2 ;

Normalizirano R - kvadrat- prilagođen R 2 ispravljeno za broj stupnjeva slobode;

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

Zapažanja - broj zapažanja n.

U stolu ANOVA daju se:

1. Stupac df - broj stupnjeva slobode, jednak

za niz Regresija df = k;

za niz Ostatakdf = n – k – 1;

za niz Ukupnodf = n– 1.

2. Stupac SS - zbroj kvadrata odstupanja jednak

za niz Regresija ;

za niz Ostatak ;

za niz Ukupno .

3. Stupac MS varijance određene formulom MS = SS/df:

za niz Regresija- faktorijalna varijansa;

za niz Ostatak- zaostala varijansa.

4. Stupac F - izračunata vrijednost F-kriterij izračunat po formuli

F = MS(regresija)/ MS(ostatak).

5. Stupac Značaj F - vrijednost razine značajnosti koja odgovara izračunatoj 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 pogreš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 tablica pokazuje:

1. Izgledi- vrijednosti koeficijenta a, b.

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

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

t-statistic = koeficijenti / standardna pogreška.

4.R-vrijednost (značaj t) Odgovara li vrijednost razine značajnosti izračunatoj t- statistika.

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

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

5... Donjih 95% i gornjih 95%- donja i gornja granica 95% intervala povjerenja za koeficijente teorijske linearne regresijske jednadžbe.

POVLAČENJE PREOSTALO
Promatranje	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 stolu POVLAČENJE PREOSTALO naznačeno:

u koloni Promatranje- broj promatranja;

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

u koloni Ostaci e - razlika između promatrane i izračunate vrijednosti zavisne varijable.

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

x
y

Koristeći rezultate Excel paketa analize (Regresija), analizirajmo ovisnost troškova hrane o visini dohotka po stanovniku.

Uobičajeno je rezultate regresijske analize pisati u obliku:

gdje su standardne pogreške regresijskih koeficijenata 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 izravna i pozitivna. Koeficijent b= 0,107 pokazuje da s povećanjem dohotka po stanovniku za 1 konv. jedinice troškovi hrane rastu za 0,107 konv. jedinice

Procijenimo značaj koeficijenata dobivenog 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) značajni su na razini od 1%, a još više na razini značajnosti od 5%. Dakle, koeficijenti regresije su značajni i model je adekvatan izvornim podacima.

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

Kvaliteta modela ocjenjuje se 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 razini od 1%, a još više na razini značajnosti od 5%.

U slučaju uparene linearne regresije, koeficijent korelacije se može definirati kao ... Dobivena vrijednost koeficijenta korelacije pokazuje da je odnos između izdataka za hranu i dohotka po stanovniku vrlo blizak.

Regresijska analiza jedna je od najtraženijih metoda statističkog istraživanja. Može se koristiti za utvrđivanje stupnja utjecaja nezavisnih varijabli na zavisnu varijablu. U funkcionalnosti Microsoft Excela postoje alati dizajnirani za ovu vrstu analize. Pogledajmo što su i kako ih koristiti.

Povezivanje paketa analize

No, da biste koristili funkciju koja vam omogućuje provođenje regresijske analize, prije svega morate aktivirati Paket analize. Tek tada će se alati potrebni za ovaj postupak pojaviti na vrpci programa Excel.

1. Prijeđite na karticu "Datoteka".
2. Idite na odjeljak "Parametri".
3. Otvara se prozor s opcijama programa Excel. Idite na pododjeljak "Dodaci".
4. Na samom dnu prozora koji se otvori, pomaknite prekidač u bloku "Control" u položaj "Excel Add-ins", ako je u drugom položaju. Kliknite na gumb "Idi".
5. Otvara se prozor dostupnih Excelovih dodataka. Stavljamo kvačicu pored stavke "Paket analize". Kliknite na gumb "OK".

Sada, kada odemo na karticu "Podaci", na vrpci u alatnom okviru "Analysis" vidjet ćemo novi gumb - "Analiza podataka".

Vrste regresijske analize

Postoji nekoliko vrsta regresije:

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

Detaljnije ćemo govoriti o izvedbi posljednje vrste regresijske analize u Excelu.

Linearna regresija u Excelu

U nastavku, kao primjer, nalazi se tablica koja prikazuje prosječnu dnevnu temperaturu zraka vani, te broj kupaca trgovine za odgovarajući radni dan. Doznajmo uz pomoć regresijske analize kako točno vremenski uvjeti u vidu temperature zraka mogu utjecati na posjećenost prodajnog mjesta.

Opća jednadžba linearne regresije je sljedeća: Y = a0 + a1x1 +… + akhk. U ovoj formuli Y označava varijablu, utjecaj čimbenika na koje pokušavamo proučavati. U našem slučaju to je broj kupaca. X-vrijednost su različiti čimbenici koji utječu na varijablu. Parametri a su koeficijenti regresije. To jest, oni su ti koji određuju značaj ovog ili onog čimbenika. Indeks k označava ukupan broj tih istih čimbenika.

Analiza rezultata analize

Rezultati regresijske analize prikazuju se u obliku tablice na mjestu navedenom u postavkama.

Jedan od glavnih pokazatelja je R-kvadrat. To ukazuje na kvalitetu modela. U našem slučaju taj omjer iznosi 0,705 ili oko 70,5%. Ovo je prihvatljiva razina kvalitete. Ovisnost manja od 0,5 je loša.

Još jedan važan pokazatelj nalazi se u ćeliji na sjecištu linije "Y-presjek" i stupca "Koeficijenti". Označava koju će vrijednost imati Y, a u našem slučaju to je broj kupaca, uz sve ostale faktore jednake nuli. U ovoj tablici ova vrijednost je 58,04.

Vrijednost na sjecištu stupaca "Varijabla X1" i "Koeficijenti" pokazuje razinu ovisnosti Y o X. U našem slučaju to je razina ovisnosti broja kupaca trgovine o temperaturi. Omjer od 1,31 smatra se prilično visokim pokazateljem učinka.

Kao što možete vidjeti, vrlo je jednostavno izraditi tablicu regresijske analize pomoću programa Microsoft Excel. Ali samo obučena osoba može raditi s podacima dobivenim na izlazu i razumjeti njihovu bit.

Drago nam je da smo Vam mogli pomoći da riješite problem.

Postavite svoje pitanje u komentarima, detaljno opišite bit problema. Naši stručnjaci će pokušati odgovoriti što je prije moguće.

Je li vam ovaj članak pomogao?

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

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

x je nezavisna varijabla,

a - segment na y-osi za ravnu liniju,

b - nagib ravne linije.

Slika ispod grafički ilustrira ovaj koncept:

Na gornjoj slici prikazana je linija opisana jednadžbom ŷ = 2 + 0,5x. Segment na y-osi je točka presjeka pravca s y-osi; u našem slučaju a = 2. Nagib pravca, b, omjer uspona pravca i duljine pravca, 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 utječe na vrijednost y.

Ŷ i y su često zbunjeni. Na grafikonu je prikazano 6 uređenih parova točaka i pravac prema ovoj jednadžbi

Ova slika prikazuje toč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 jednadžbom:

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

Y-vrijednost je stvarna točka, a-vrijednost je očekivana y-vrijednost pomoću linearne jednadžbe za danu 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 analize... Možete ga pronaći klikom na karticu Datoteka -> Opcije(2007+), u dijaloškom okviru koji se pojavi ParametriExcel idite na karticu Dodaci. U polju Kontrolirati birati DodaciExcel i kliknite Ići. U prozoru koji se pojavi stavite kvačicu nasuprot Paket analize, pritisnemo U REDU.

U kartici Podaci u grupi Analiza pojavit će se novi gumb Analiza podataka.

Kako bismo demonstrirali kako dodatak radi, poslužimo se podacima iz prethodnog članka, gdje momak i djevojka dijele stol u kupaonici. Unesite podatke za naš primjer kade u stupce A i B prazne ploče.

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

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

Klik U REDU. Slika ispod prikazuje dobivene rezultate:

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

Regresijska analiza je statistička metoda istraživanja koja vam omogućuje da pokažete ovisnost parametra o jednoj ili više neovisnih varijabli. U doba predračunala, njegova primjena je bila prilično teška, osobito kada se radilo 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 tabelarnom obliku imamo:

Za problem određivanja ovisnosti broja zaposlenih koji su otkazali prosječnu plaću u 6 poduzeća, regresijski model ima oblik jednadžbe Y = a0 + a1 × 1 +… + akxk, gdje su xi utjecajne varijable, ai su 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 prethoditi primjena 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 trebate:

• 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 ispravno napravljeno, na desnoj strani kartice "Podaci" koja se nalazi iznad radnog lista "Excel" 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 automatski ispuniti novi list procesora proračunskih tablica 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 ove vrste podataka.

Analiza rezultata regresije za R-kvadrat

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

Prije svega, obratite pozornost 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, onda se hipoteza o beznačajnosti slobodnog člana linearne jednadžbe odbacuje.

U razmatranom zadatku za slobodni termin korištenjem 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 pojma biti odbijeno. 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 problem.

Uprava tvrtke "NNN" mora odlučiti o svrsishodnosti kupnje 20% udjela u JSC "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 prema 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:

• 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:

Zamijenivši ih u regresijsku jednadžbu, brojka je 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.