Jednostavna linearna regresija u excel primjerima. Konrad Carlberg. Regresiona analiza u Microsoft Excel-u

Tema: KORELACIJSKA I REGRESIJSKA ANALIZA UEXCEL

LABORATORIJSKI RAD br.1

1. ODREĐIVANJE KOEFICIJENTA UPARENE KORELACIJE U PROGRAMUEXCEL

Korelacija- ovo je nepotpun, vjerovatnost odnos između indikatora, koji se manifestira samo u masi zapažanja.

Parna korelacija- ovo je odnos između dva indikatora, od kojih je jedan faktorski, a drugi rezultantni.

Višestruka korelacija proizilazi iz interakcije nekoliko faktora sa efektivnim indikatorom.

Neophodni uslovi za korišćenje korelacione analize:

1. Prisustvo dovoljno velikog broja zapažanja o vrijednosti proučavanog faktora i pokazatelja učinka.

2. Faktori koji se proučavaju moraju se kvantitativno mjeriti i odražavati u određenim izvorima informacija.

Upotreba korelacione analize nam omogućava da rešimo sledeće probleme:

1. Odredite promjenu pokazatelja učinka pod utjecajem jednog ili više faktora.

2. Uspostaviti relativni stepen zavisnosti indikatora učinka od svakog faktora.

Vježba 1.

Podaci su dostupni za 20 poljoprivrednih gazdinstava. Nađi koeficijent korelacije između prinosa žitarica i kvaliteta zemljišta i procijeniti njegov značaj. Podaci su prikazani u tabeli.

Table. Zavisnost prinosa zrna od kvaliteta zemljišta

Da biste pronašli koeficijent korelacije, koristite funkciju CORREL.

Značajnost koeficijenta korelacije se provjerava korištenjem kriterija Studentski test.

Za primjer koji se razmatra, r=0,99, n=18.

Da biste pronašli kvantil Studentove distribucije, koristite funkciju STUDISCOVER sa sljedećim argumentima: Vjerovatnoća –0,05, Stepeni sloboda –18.

Upoređujući vrijednost t-statistike sa kvantilom Studentove distribucije, izvući zaključke o značaju koeficijenta korelacije parova. Ako je izračunata vrijednost t-statistike veća od kvantila Studentove distribucije, tada je vrijednost koeficijenta korelacije značajna.

IZGRADNJA REGRESIJSKOG MODELA ODNOSA IZMEĐU DVIJE KOLIČINE

Zadatak 2.

Prema zadatku 1:

1) izgraditi jednačinu regresije (linearni model), koja karakteriše linearni odnos između kvaliteta zemljišta i produktivnosti;

2). provjeriti adekvatnost rezultirajućeg modela.

1 - th way.

1. Na Excel listu odaberite niz slobodnih ćelija koji se sastoji od pet redova i dvije kolone.

2. Pozovite funkciju LINEST.

3.Navedite sljedeće argumente za funkciju: Izv_value_y Produktivnost, c/ha;Izv_value_x- kolona vrijednosti indikatora Kvalitet zemljišta, ocjena; Konstanta –1, Stat – 1(omogućava vam da izračunate indikatore koji se koriste za provjeru adekvatnosti modela. Ako Stat – 0, onda se takvi pokazatelji neće računati.

4. Pritisnite kombinaciju tastera Ctrl- Shift- Enter.

Odabrane ćelije prikazuju koeficijente modela, kao i indikatore koji vam omogućavaju da provjerite adekvatnost modela (tabela 2).

a 1 , a 0 – koeficijenti modela;

S e 1 S e 0 – standardne greške koeficijenata. Što je model precizniji, to su ove vrijednosti manje.

R 2 - koeficijent odlučnosti. Što je veći, to je model precizniji.

F– statistika za testiranje značaja modela.

n- k-1 – broj stupnjeva slobode (n-veličina uzorka, k-broj ulaznih varijabli; u ovom primjeru n=20, k=1)

Q R– zbir kvadrata zbog regresije;

Q e– zbir grešaka na kvadrat.

5. Da biste provjerili adekvatnost modela, pronađite kvantil Fisherove distribucije F f . koristeći funkciju FDISCOVER. Da biste to učinili, unesite funkciju u bilo koju slobodnu ćeliju FDISCOVER sa sljedećim argumentima: Vjerovatnoća – 0,05, Stepeni_slobode _1–1, Stepeni_slobode _2–18. Ako je F> F f , tada je model adekvatan originalnim podacima

6. Provjeriti adekvatnost konstruiranog modela koristeći izračunati nivo značajnosti (P). Unesite funkciju FDIST sa sljedećim argumentima: X– statistička vrijednost F, Stepeni_slobode_1–1, Stepeni_slobode_2– 18. Ako je izračunati nivo značajnosti P<α =0,05, то модель адекватна исходным данным.

2. metoda.

Određivanje koeficijenata modela sa dobijanjem indikatora za provjeru njegove adekvatnosti i značajnosti koeficijenata.

Odaberite tim Usluga/analiza podataka/regresija. U dijaloškom okviru postavite: Interval unosaY– vrijednosti indikatora Produktivnost, c/ha,Interval unosaX– vrijednosti indikatora Kvalitet zemljišta, ocjena.

Potvrdni okvir Oznake. U području Izlazne opcije odaberite radio dugme Izlazni interval i označite ćeliju iz koje će početi rezultati. Da biste dobili rezultate, kliknite na OK.

Interpretacija rezultata.

Traženi koeficijenti modela su u koloni Odds:

Za ovaj primjer, jednačina modela je:

Y=2,53+0,5X

U ovom primeru, sa povećanjem kvaliteta zemljišta za jedan bod, prinos žitarica raste u proseku za 0,5 c/ha.

Provjera adekvatnosti modela izvršeno na izračunatom nivou značajnosti P naznačenom u koloni ZnačajF. Ako je izračunati nivo značajnosti manji od specificiranog nivoa značajnosti α = 0,05, tada je model adekvatan.

Testiranje statističke značajnosti koeficijenti modela se izvode korišćenjem izračunatih nivoa značajnosti P naznačenih u koloni P-značenje. Ako je izračunati nivo značajnosti manji od specificiranog nivoa značajnosti α = 0,05, tada je odgovarajući koeficijent modela statistički značajan.

VišestrukoR – koeficijent korelacije. Što je njegova vrijednost bliža 1, to je bliži odnos između proučavanih indikatora. Za ovaj primjer, R= 0,99. Ovo nam omogućava da zaključimo da je kvalitet zemljišta jedan od glavnih faktora od kojih zavisi prinos žitarica.

R-kvadrat – koeficijent odlučnosti. Dobija se kvadriranjem koeficijenta korelacije - R 2 =0,98. Pokazuje da 98% prinosa zrna zavisi od kvaliteta zemljišta, dok ostali faktori učestvuju sa 0,02%.

3rd method. GRAFIČKA METODA IZGRADNJE MODELA.

Sami napravite dijagram raspršenosti koji pokazuje odnos između prinosa i kvaliteta zemljišta.

Dobiti linearni model zavisnosti prinosa zrna od kvaliteta zemljišta.

Poznat je po tome što je koristan u različitim oblastima aktivnosti, uključujući i disciplinu kao što je ekonometrija, gde se ovaj softverski uslužni program koristi u radu. U osnovi, sve radnje praktične i laboratorijske nastave izvode se u Excelu, što uvelike olakšava rad davanjem detaljnih objašnjenja pojedinih radnji. Dakle, jedan od alata za analizu “Regresija” se koristi za odabir grafa za skup opažanja koristeći metodu najmanjih kvadrata. Pogledajmo šta je ovaj programski alat i koje su njegove prednosti za korisnike. U nastavku ćete također pronaći kratke, ali jasne upute za izgradnju regresijskog modela.

Glavni zadaci i vrste regresije

Regresija predstavlja odnos između datih varijabli, čime je moguće predvidjeti buduće ponašanje ovih varijabli. Varijable su različite periodične pojave, uključujući i ljudsko ponašanje. Ova vrsta Excel analize se koristi za analizu uticaja vrednosti jedne ili više varijabli na određenu zavisnu varijablu. Na primjer, na prodaju u trgovini utječe nekoliko faktora, uključujući asortiman, cijene i lokaciju trgovine. Zahvaljujući regresiji u Excel-u, možete odrediti stepen uticaja svakog od ovih faktora na osnovu rezultata postojeće prodaje, a zatim primeniti dobijene podatke za prognozu prodaje za još jedan mesec ili za drugu prodavnicu koja se nalazi u blizini.

Obično se regresija predstavlja kao jednostavna jednadžba koja otkriva odnose i snagu odnosa između dvije grupe varijabli, gdje je jedna grupa zavisna ili endogena, a druga nezavisna ili egzogena. Ako postoji grupa međusobno povezanih indikatora, zavisna varijabla Y se određuje na osnovu logike rasuđivanja, a ostali djeluju kao nezavisne X varijable.

Glavni zadaci izgradnje regresijskog modela su sljedeći:

1. Izbor značajnih nezavisnih varijabli (X1, X2, ..., Xk).
2. Odabir tipa funkcije.
3. Izrada procjena za koeficijente.
4. Konstrukcija intervala povjerenja i regresijskih funkcija.
5. Provjera značajnosti izračunatih procjena i konstruirane regresione jednačine.

Postoji nekoliko vrsta regresione analize:

• upareni (1 zavisna i 1 nezavisna varijabla);
• višestruki (nekoliko nezavisnih varijabli).

Postoje dvije vrste regresijskih jednačina:

1. Linearni, koji ilustruje strogi linearni odnos između varijabli.
2. Nelinearne – jednadžbe koje mogu uključivati ​​potencije, razlomke i trigonometrijske funkcije.

Upute za izradu modela

Da biste izvršili datu konstrukciju u Excelu, morate slijediti upute:

Za dalje izračunavanje, koristite funkciju “Linear()”, navodeći Y vrijednosti, X vrijednosti, konst i statistiku. Nakon toga odredite skup tačaka na liniji regresije koristeći funkciju "Trend" - Y vrijednosti, X vrijednosti, nove vrijednosti, konst. Koristeći date parametre, izračunajte nepoznatu vrijednost koeficijenata, na osnovu datih uslova zadatka.

Statistička obrada podataka može se izvršiti i pomoću dodatka PAKET ANALIZE(Sl. 62).

Od predloženih stavki odaberite stavku “ REGRESIJA" i kliknite na njega lijevim dugmetom miša. Zatim kliknite na OK.

Pojavit će se prozor kao što je prikazano na sl. 63.

Alat za analizu" REGRESIJA» se koristi za uklapanje grafa u skup opažanja koristeći metodu najmanjih kvadrata. Regresija se koristi za analizu učinka na jednu zavisnu varijablu vrijednosti jedne ili više nezavisnih varijabli. Na primjer, nekoliko faktora utiče na sportske performanse sportiste, uključujući godine, visinu i težinu. Moguće je izračunati stepen do kojeg svaki od ova tri faktora utiče na performanse jednog sportiste, a zatim koristiti te podatke za predviđanje učinka 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 naslove. Poništite ovo polje za potvrdu ako nema zaglavlja. U tom slučaju će se automatski kreirati odgovarajuća zaglavlja za podatke izlazne tablice.

Nivo pouzdanosti Označite polje za potvrdu da biste uključili dodatni nivo u tabelu sažetka izlaza. U odgovarajuće polje unesite nivo pouzdanosti koji želite da primenite, pored podrazumevanog nivoa od 95%.

Konstanta - nula Potvrdite izbor u polju za potvrdu kako biste prisilili liniju regresije da prođe kroz ishodište.

Izlazni opseg Unesite referencu na gornju lijevu ćeliju izlaznog raspona. Navedite najmanje sedam kolona za izlaznu zbirnu tabelu, koja će uključivati: rezultate ANOVA, koeficijente, standardnu ​​grešku izračunavanja Y, standardne devijacije, broj zapažanja, standardne greške za koeficijente.

Novi radni list Odaberite ovu opciju da otvorite novi radni list u radnoj knjizi i zalijepite rezultate analize, počevši od ćelije A1. Ako je potrebno, unesite naziv za novi list u polje koje se nalazi nasuprot odgovarajućeg radio dugmeta.

Nova radna sveska Izaberite ovu opciju da biste kreirali novu radnu svesku sa rezultatima dodatim novom radnom listu.

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

Standardizirani reziduali Označite potvrdni okvir da biste uključili standardizirane ostatke u izlaznu tablicu.

Residual Plot Označite potvrdni okvir za iscrtavanje reziduala za svaku nezavisnu varijablu.

Fit Plot Označite polje za potvrdu da biste nacrtali predviđene u odnosu na posmatrane vrijednosti.

Grafikon normalne vjerovatnoće Označite potvrdni okvir da nacrtate normalan graf vjerovatnoće.

Funkcija LINEST

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

Funkcija LINEST izračunava statistiku za seriju koristeći metodu najmanjih kvadrata da izračuna pravu liniju koja najbolje aproksimira dostupne podatke, a zatim vraća niz koji opisuje rezultirajuću ravnu liniju. Također možete kombinirati funkciju LINEST sa 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 niza.

Jednačina za pravu liniju je:

y=m 1 x 1 +m 2 x 2 +…+b (u slučaju nekoliko raspona x vrijednosti),

gdje je zavisna vrijednost y funkcija nezavisne 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(mn;mn-1;…;m 1 ;b). LINEST može također vratiti dodatnu statistiku regresije.

LINEST(poznate_vrijednosti_y; poznate_vrijednosti_x; konst; statistika)

Poznate_y_vrijednosti - skup y vrijednosti koje su već poznate za relaciju y=mx+b.

Ako niz known_y_values ​​ima jedan stupac, tada se svaki stupac u nizu known_x_values ​​tretira kao zasebna varijabla.

Ako niz known_y_values ​​ima jedan red, tada se svaki red u nizu known_x_values ​​tretira kao zasebna varijabla.

Poznate_x-vrijednosti su izborni skup x-vrijednosti koje su već poznate za odnos y=mx+b.

Niz poznatih_x_values ​​može sadržavati jedan ili više skupova varijabli. Ako se koristi samo jedna varijabla, tada nizovi known_y_values ​​i known_x_values ​​mogu imati bilo koji oblik - sve dok imaju istu dimenziju. Ako se koristi više od jedne varijable, tada poznate_y_vrijednosti moraju biti vektor (tj. interval visok jedan red ili širok jedan stupac).

Ako se array_known_x_values ​​izostavi, tada se pretpostavlja da je niz (1;2;3;...) iste veličine kao niz_poznate_vrijednosti_y.

Const je logička vrijednost koja određuje da li konstanta b mora biti jednaka 0.

Ako je argument "const" TRUE ili izostavljen, tada se konstanta b procjenjuje kao i obično.

Ako je argument “const” FALSE, tada se vrijednost b postavlja na 0, a vrijednosti m se biraju na takav način da je relacija y=mx zadovoljena.

Statistics - Boolean vrijednost koja pokazuje da li treba vratiti dodatnu statistiku regresije.

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 (Tabela 17)

Slika ispod prikazuje redoslijed po kojem se vraćaju dodatne statistike regresije (Slika 64).

napomene:

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

Nagib (m): Da biste odredili nagib prave, koji se obično označava sa m, potrebno je da uzmete dve tačke na pravoj (x 1 ,y 1) i (x 2 ,y 2); nagib će biti jednak (y 2 -y 1)/(x 2 -x 1).

Y-presjek (b): y-presjek prave, koji se obično označava sa b, je y-vrijednost za tačku u kojoj linija seče y-osu.

Jednačina prave linije je y=mx+b. Ako su poznate vrijednosti m i b, tada se bilo koja točka na pravoj može izračunati zamjenom vrijednosti y ili x u jednadžbu. 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(poznate_y_vrijednosti; poznate_x_vrijednosti); 1)

Y-presjek: INDEX(LINEST(poznate_y_vrijednosti; poznate_x_vrijednosti); 2)

Preciznost aproksimacije pomoću prave linije izračunate funkcijom LINEST zavisi od stepena rasipanja podataka. Što su podaci bliži pravoj liniji, to je tačniji model koji koristi funkcija LINEST. Funkcija LINEST koristi najmanje kvadrate kako bi odredila najbolje uklapanje u podatke. 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 LGRFPRIBL mogu izračunati pravu liniju ili eksponencijalnu krivu koja najbolje odgovara podacima. Međutim, oni ne daju odgovor na pitanje koji je od dva rezultata pogodniji za rješavanje problema. Također možete procijeniti funkciju TREND(poznate_y_vrijednosti; poznate_x_vrijednosti) funkciju za pravu liniju ili funkciju GROWTH(poznate_y_vrijednosti; poznate_x_vrijednosti) funkciju za eksponencijalnu krivulju. Ove funkcije, osim ako nisu specificirane new_x-vrijednosti, vraćaju niz izračunatih y-vrijednosti za stvarne x-vrijednosti duž linije ili krive. Zatim možete uporediti izračunate vrijednosti sa stvarnim vrijednostima. Takođe možete kreirati grafikone za vizuelno poređenje.

Prilikom izvođenja regresijske analize, Microsoft Excel izračunava, za svaku tačku, kvadrat razlike između predviđene vrijednosti y i stvarne vrijednosti y. Zbir ovih kvadrata razlika naziva se rezidualni zbir kvadrata (ssresid). Microsoft Excel zatim izračunava ukupan zbir kvadrata (sstotal). Ako const = TRUE ili vrijednost ovog argumenta nije navedena, ukupan zbroj kvadrata će biti jednak zbroju kvadrata razlika između stvarnih vrijednosti y i prosječnih y vrijednosti. Kada je const = FALSE, ukupan zbir kvadrata će biti jednak zbiru kvadrata realnih y vrijednosti (bez oduzimanja prosječne vrijednosti y od djelimične y vrijednosti). 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 determinacije r2, što pokazuje koliko dobro jednačina dobijena regresionom analizom objašnjava odnose između varijabli. Koeficijent r2 je jednak 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 X stupcima. Drugim riječima, uklanjanje jednog ili više X stupaca može rezultirati Y vrijednostima izračunatim sa istu preciznost. U ovom slučaju, redundantni X stupci će biti isključeni iz regresijskog modela. Ovaj fenomen se naziva "kolinearnost" jer se redundantni stupci X mogu predstaviti kao zbir nekoliko neredundantnih kolona. Funkcija LINEST provjerava kolinearnost i uklanja sve redundantne X stupce iz regresijskog modela ako ih otkrije. Uklonjeni 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 vrijednost df 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 u nastavku. Kada se df promijeni zbog uklanjanja suvišnih kolona, ​​mijenjaju se i vrijednosti sey i F. Ne preporučuje se često korištenje kolinearnosti. Međutim, treba ga koristiti ako neki X stupci sadrže 0 ili 1 kao indikator koji pokazuje da li subjekt eksperimenta pripada posebnoj grupi. Ako const = TRUE ili vrijednost za ovaj argument nije navedena, LINEST umeće dodatni X stupac za modeliranje točke presjeka. Ako postoji kolona sa vrijednostima 1 za muškarce i 0 za žene, a postoji kolona sa vrijednostima 1 za žene i 0 za muškarce, onda se posljednja kolona uklanja jer se njene vrijednosti mogu dobiti iz kolone "muški indikator".

Izračunavanje df za slučajeve kada X stupaca nije uklonjeno iz modela zbog kolinearnosti se dešava na sljedeći način: ako postoji k poznatih_x stupaca i vrijednost const = TRUE ili nije navedena, onda je df = n – k – 1. Ako je const = NETAČNO, 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.

Prilikom unosa niza konstanti kao argumenta, na primjer, poznate_x_vrijednosti, trebali biste koristiti tačku i zarez da odvojite vrijednosti u istom redu i dvotočku za razdvajanje redaka. Znakovi za razdvajanje mogu se razlikovati u zavisnosti od postavki u prozoru Jezik i postavke 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.

Osnovni algoritam koji se koristi u funkciji LINEST, razlikuje se od algoritma glavne funkcije INCLINE I LINIJSKI SEGMENT. Razlika između algoritama može dovesti do različitih rezultata s nesigurnim i kolinearnim podacima. Na primjer, ako su argumentirane podatkovne točke poznate_y_values ​​0, a argumentirane podatkovne točke poznate_x_values ​​1, tada:

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

Funkcije SLOPE i LINE vraćaju grešku #DIV/0!. Algoritam funkcija SLOPE i INTERCEPT se koristi za pronalaženje samo jednog odgovora, ali u 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 varijabli x i y kao niza varijabli 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 kocke (polinom 3. stepena) sljedećeg oblika:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

Formula se može modificirati kako bi se izračunale druge vrste regresije, ali u nekim slučajevima će možda biti potrebno prilagoditi izlazne vrijednosti i druge statistike.

Regresijska i korelaciona analiza su statističke metode istraživanja. Ovo su najčešći načini da se pokaže zavisnost parametra od jedne ili više nezavisnih varijabli.

U nastavku ćemo, koristeći konkretne praktične primjere, razmotriti ove dvije vrlo popularne analize među ekonomistima. Navest ćemo i primjer dobijanja rezultata pri njihovom kombinovanju.

Regresiona analiza u Excelu

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

Rezultat analize vam omogućava da istaknete prioritete. I na osnovu glavnih faktora predvidjeti, planirati razvoj prioritetnih oblasti i donijeti upravljačke odluke.

Regresija se dešava:

• linearni (y = a + bx);
• parabolični (y = a + bx + cx 2);
• eksponencijalni (y = a * exp(bx));
• snaga (y = a*x^b);
• hiperbolično (y = b/x + a);
• logaritamski (y = b * 1n(x) + a);
• eksponencijalni (y = a * b^x).

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

Zadatak. U 6 preduzeća analizirana je prosječna mjesečna plata i broj zaposlenih koji su otpustili. Potrebno je utvrditi zavisnost broja zaposlenih koji napuštaju rad od prosječne plate.

Model linearne regresije izgleda ovako:

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

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

U našem primjeru, Y je indikator napuštanja zaposlenika. Faktor uticaja su plate (x).

Excel ima ugrađene funkcije koje vam mogu pomoći da izračunate parametre modela linearne regresije. Ali dodatak “Paket analize” će to učiniti brže.

Aktiviramo moćan analitički alat:

Kada se aktivira, dodatak će biti dostupan na kartici Podaci.

Sada uradimo samu regresijsku analizu.

Prije svega, obraćamo 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 75,5% odnosa 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 (ovakva 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. Odnosno, prosječna mjesečna plata u okviru ovog modela utiče na broj onih koji odustaju sa ponderom od -0,16285 (ovo je mali stepen uticaja). Znak “-” ukazuje na negativan uticaj: što je veća plata, manje ljudi daje otkaz. Što je pošteno.

Analiza korelacije u Excel-u

Korelaciona analiza pomaže da se utvrdi da li postoji veza između indikatora u jednom ili dva uzorka. Na primjer, između vremena rada mašine i troškova popravki, cijene opreme i trajanja rada, visine i težine djece itd.

Ako postoji veza, da li povećanje jednog parametra dovodi do povećanja (pozitivna korelacija) ili smanjenja (negativna) drugog. Korelaciona analiza pomaže analitičaru da utvrdi da li se vrednost jednog indikatora može koristiti za predviđanje moguće vrednosti drugog.

Koeficijent korelacije je označen sa r. Varira od +1 do -1. Klasifikacija korelacija za različita područja bit će različita. Kada je koeficijent 0, ne postoji linearna veza između uzoraka.

Pogledajmo kako pronaći koeficijent korelacije koristeći Excel.

Za pronalaženje uparenih koeficijenata koristi se CORREL funkcija.

Cilj: Utvrditi postoji li veza između vremena rada tokarilice i troškova njenog održavanja.

Postavite kursor u bilo koju ćeliju i pritisnite dugme fx.

1. U kategoriji “Statistički” odaberite funkciju CORREL.
2. Argument “Niz 1” - prvi raspon vrijednosti – vrijeme rada mašine: A2:A14.
3. Argument "Niz 2" - drugi raspon vrijednosti - cijena popravke: B2:B14. Kliknite OK.

Da biste odredili vrstu veze, potrebno je pogledati apsolutni broj koeficijenta (svako polje aktivnosti ima svoju skalu).

Za korelacione analize nekoliko parametara (više od 2) pogodnije je koristiti „Analizu podataka“ (dodatak „Paket analize“). Potrebno je da izaberete korelaciju sa liste i odredite niz. Sve.

Dobijeni koeficijenti će biti prikazani u korelacionoj matrici. Volim ovo:

Korelaciona i regresiona analiza

U praksi se ove dvije tehnike često koriste zajedno.

primjer:

Sada su podaci regresione analize postali vidljivi.

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

Rezultat analize vam omogućava da istaknete prioritete. I na osnovu glavnih faktora predvidjeti, planirati razvoj prioritetnih oblasti i donijeti upravljačke odluke.

Regresija se dešava:

linearni (y = a + bx);

· parabolični (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);

· eksponencijalni (y = a * b^x).

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

Zadatak. U 6 preduzeća analizirana je prosječna mjesečna plata i broj zaposlenih koji su otpustili. Potrebno je utvrditi zavisnost broja zaposlenih koji napuštaju rad od prosječne plate.

Model linearne regresije izgleda ovako:

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

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

U našem primjeru, Y je indikator napuštanja zaposlenika. Faktor uticaja su plate (x).

Excel ima ugrađene funkcije koje vam mogu pomoći da izračunate parametre modela linearne regresije. Ali dodatak “Paket analize” će to učiniti brže.

Aktiviramo moćan analitički alat:

1. Kliknite na dugme “Office” i idite na karticu “Opcije Excel”. "Dodaci".

2. Na dnu, ispod padajuće liste, u polju „Upravljanje“ nalaziće se natpis „Excel dodaci“ (ako ga nema, kliknite na kvadratić sa desne strane i izaberite). I dugme „Idi“. Kliknite.

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

Kada se aktivira, dodatak će biti dostupan na kartici Podaci.

Sada uradimo samu regresijsku analizu.

1. Otvorite meni alata „Analiza podataka“. Odaberite "Regresija".

2. Otvorit će se meni za odabir ulaznih vrijednosti i izlaznih opcija (gdje prikazati rezultat). U poljima za početne podatke označavamo opseg opisanog parametra (Y) i faktor koji na njega utiče (X). Ostatak nije potrebno popunjavati.

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 radnoj knjizi).

Prije svega, obraćamo 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 75,5% odnosa 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 (ovakva 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. Odnosno, prosječna mjesečna plata u okviru ovog modela utiče na broj onih koji odustaju sa ponderom od -0,16285 (ovo je mali stepen uticaja). Znak “-” ukazuje na negativan uticaj: što je veća plata, manje ljudi daje otkaz. Što je pošteno.