Regresioni në Excel

Përpunimi statistikor i të dhënave mund të kryhet gjithashtu duke përdorur shtesën e paketës Analiza në artikullin e menysë "Shërbimi". Në Excel 2003, nëse hapni SHËRBIMI, nuk e gjejmë skedën ANALIZA E TË DHËNAVE, pastaj duke klikuar butonin e majtë të miut hapni skedën SUPERSTRUKTURA dhe pika e kundërt PAKETA E ANALIZËS duke klikuar butonin e majtë të miut, vendosni një shenjë (Fig. 17).

Oriz. 17. Dritare SUPERSTRUKTURA

Pas kësaj në meny SHËRBIMI shfaqet një skedë ANALIZA E TË DHËNAVE.

Në Excel 2007 për të instaluar PAKETA E ANALIZËS duhet të klikoni në butonin OFFICE në këndin e sipërm të majtë të fletës (Fig. 18a). Tjetra, klikoni në butonin PARAMETRAT E EXCEL-it... Në dritaren që shfaqet PARAMETRAT E EXCEL-it kliko me të majtën mbi artikull SUPERSTRUKTURA dhe në anën e djathtë të listës rënëse, zgjidhni artikullin PAKETA E ANALIZËS. Tjetra, klikoni mbi Ne rregull.

Opsionet e Excel Butoni i zyrës

Oriz. 18. Instalimi PAKETA E ANALIZËS në Excel 2007

Për të instaluar Paketën e Analizës, klikoni në butonin SHKO, ndodhet në fund të dritares së hapur. Dritarja e paraqitur në Fig. 12. Vendos një tik-tak përballë PAKETA E ANALIZËS. Në skedën TË DHËNAT do të shfaqet një buton ANALIZA E TË DHËNAVE(fig. 19).

Nga artikujt e propozuar, ai zgjedh artikullin " REGRESIONI"Dhe klikoni mbi të me butonin e majtë të miut. Pastaj klikoni OK.

Dritarja e paraqitur në Fig. 21

Mjeti i analizës " REGRESIONI»Përdoret për të vendosur një grafik për një grup vëzhgimesh duke përdorur metodën e katrorëve më të vegjël. Regresioni përdoret për të analizuar efektin në një variabël të varur individual të vlerave të një ose më shumë variablave shpjegues. Për shembull, disa faktorë ndikojnë në performancën atletike të një atleti, duke përfshirë moshën, gjatësinë dhe peshën. Ju mund të llogarisni ndikimin e secilit prej këtyre tre faktorëve në performancën e një atleti dhe më pas t'i përdorni ato të dhëna për të parashikuar performancën e një atleti tjetër.

Vegla Regresioni përdor funksionin LINEST.

Kutia e dialogut REGRESSION

Etiketat Zgjidhni kutinë e kontrollit nëse rreshti i parë ose kolona e parë e diapazonit të hyrjes përmban tituj. Pastro këtë kuti të kontrollit nëse nuk ka tituj. Në këtë rast, titujt e duhur për të dhënat e tabelës dalëse do të gjenerohen automatikisht.

Niveli i besimit Zgjidhni kutinë e kontrollit për të përfshirë një nivel shtesë në tabelën totale të prodhimit. Në fushën përkatëse, vendosni nivelin e besueshmërisë për t'u aplikuar, përveç nivelit të paracaktuar 95%.

Konstante - zero Zgjidhni kutinë e zgjedhjes për të bërë që vija e regresionit të kalojë përmes origjinës.

Hapësira në dalje Futni një referencë në qelizën lart majtas të diapazonit të daljes. Alokoni të paktën shtatë kolona për tabelën totale të prodhimit, e cila do të përfshijë: rezultatet ANOVA, koeficientët, gabimin standard të llogaritjes Y, devijimet standarde, numrin e vëzhgimeve, gabimet standarde për koeficientët.

Fletë pune e re Zgjidhni këtë çelës për të hapur një fletë të re pune në librin e punës dhe për të futur rezultatet e analizës duke filluar në qelizën A1. Nëse është e nevojshme, vendosni një emër për fletën e re në fushën përballë pozicionit përkatës të çelësit.

Libri i ri i punës Klikoni butonin në këtë pozicion për të krijuar një libër të ri pune në të cilin rezultatet do të shtohen në një fletë të re.

Mbetjet Zgjidhni kutinë e zgjedhjes për të përfshirë mbetjet në tabelën e daljes.

Mbetjet e standardizuara Zgjidhni kutinë e zgjedhjes për të përfshirë mbetjet e standardizuara në tabelën e daljes.

Mbetjet e parcelës Zgjidhni kutinë e zgjedhjes për të vizatuar mbetjet për çdo variabël të pavarur.

Përshtatja e grafikut Zgjidhni kutinë e zgjedhjes për të paraqitur grafikun e vlerave të parashikuara kundrejt vlerave të vëzhguara.

Skema e probabilitetit normal Kontrolloni kutinë për të paraqitur grafikun normal të probabilitetit.

Funksioni LINEST

Për të kryer llogaritjet, zgjidhni qelizën në të cilën duam të shfaqim vlerën mesatare me kursorin dhe shtypni tastin = në tastierë. Më pas, në fushën Emri, tregoni funksionin e dëshiruar, për shembull MESATAR(fig. 22).

Oriz. 22 Gjetja e funksioneve në Excel 2003

Nëse në terren EMRI emri i funksionit nuk shfaqet, pastaj kliko me të majtën në trekëndëshin pranë fushës, pas së cilës do të shfaqet një dritare me një listë funksionesh. Nëse ky funksion nuk është në listë, atëherë kliko me të majtën mbi artikullin e listës FUNKSIONET E TJERA, do të shfaqet një kuti dialogu MJESHTRI I FUNKSIONIVE, në të cilën, duke përdorur lëvizjen vertikale, zgjidhni funksionin e dëshiruar, zgjidhni atë me kursorin dhe klikoni mbi Ne rregull(fig. 23).

Oriz. 23. Funksioni Wizard

Për të kërkuar një funksion në Excel 2007, çdo skedë mund të hapet në meny, më pas për llogaritjet, zgjidhni qelizën në të cilën duam të shfaqim vlerën mesatare dhe shtypni tastin = në tastierë. Më pas, në fushën Emri, specifikoni funksionin MESATAR... Dritarja për llogaritjen e funksionit është e ngjashme me atë të treguar në Excel 2003.

Ju gjithashtu mund të zgjidhni skedën Formulat dhe të klikoni me të majtën në butonin në meny " INSERT FUNKSIONIN”(Fig. 24), do të shfaqet një dritare MJESHTRI I FUNKSIONIVE, lloji i të cilit është i ngjashëm me Excel 2003. Gjithashtu në meny mund të zgjidhni menjëherë një kategori funksionesh (të përdorura së fundi, financiare, logjike, tekst, datë dhe orë, matematikore, funksione të tjera), në të cilat do të kërkojmë funksionin e dëshiruar.

Funksione të tjera Referencat dhe vargjeve Matematikore

Oriz. 24 Zgjedhja e një funksioni në Excel 2007

Funksioni LINEST llogarit statistikat për një seri duke përdorur katrorët më të vegjël për të llogaritur vijën e drejtë që i përshtatet më mirë të dhënave të disponueshme dhe më pas kthen një grup që përshkruan vijën e drejtë që rezulton. Ju gjithashtu mund të kombinoni funksionin LINEST me funksione të tjera për të llogaritur lloje të tjera modelesh që janë lineare në parametra të panjohur (parametrat e panjohur të të cilëve janë linearë), duke përfshirë seritë polinomiale, logaritmike, eksponenciale dhe të fuqisë. Meqenëse një grup vlerash është kthyer, funksioni duhet të specifikohet si një formulë grupi.

Ekuacioni për një vijë të drejtë është si më poshtë:

(në rast të vargjeve të shumta të vlerave x),

ku vlera e varur y është një funksion i vlerës së pavarur x, vlerat m janë koeficientët që korrespondojnë me çdo ndryshore të pavarur x dhe b është një konstante. Vini re se y, x dhe m mund të jenë vektorë. Funksioni LINEST kthen një grup . LINEST gjithashtu mund të kthejë statistika shtesë të regresionit.

LINEST(të njohura_y; të njohura_x; konst; statistika)

Known_y janë grupi i vlerave y që tashmë njihen për marrëdhënien.

Nëse know_y's ka një kolonë, atëherë çdo kolonë në Know_x's interpretohet si një variabël e veçantë.

Nëse know_y's ka një rresht të vetëm, atëherë çdo rresht nëknown_x's interpretohet si një variabël e veçantë.

Known_x janë një grup opsional i vlerave x që janë tashmë të njohura për marrëdhënien.

Known_x mund të përmbajë një ose më shumë grupe variablash. Nëse përdoret vetëm një variabël, atëherë know_y dhe Known_x mund të jenë të çdo forme, përderisa kanë të njëjtin dimension. Nëse përdoret më shumë se një ndryshore, know_y's duhet të jetë një vektor (d.m.th., një rresht i lartë ose një kolonë i gjerë).

Nëse array_known_x's hiqet, atëherë ky varg (1; 2; 3; ...) supozohet të jetë i njëjtë me madhësinë e vargut_known_y's.

Const është një vlerë Boolean që tregon nëse konstanta b kërkohet të jetë 0.

Nëse const është TRUE ose i anashkaluar, konstanta b vlerësohet në mënyrën e zakonshme.

Nëse argumenti "const" është FALSE, atëherë vlera e b vendoset e barabartë me 0 dhe vlerat e m zgjidhen në atë mënyrë që relacioni të qëndrojë.

Statistics është një vlerë Boolean që tregon nëse dëshironi të ktheni statistika shtesë për regresionin.

Nëse statistikat janë të vërteta, LINEST kthen statistika shtesë të regresionit. Vargu i kthyer do të duket kështu: (mn; mn-1; ...; m1; b: sen; sen-1; ...; se1; seb: r2; sey: F; df: ssreg; ssresid).

Nëse statistikat janë FALSE ose janë lënë jashtë, LINEST kthen vetëm koeficientët m dhe konstanten b.

Statistikat shtesë të regresionit.

Madhësia Përshkrim se1, se2, ..., sen Vlerat standarde të gabimit për koeficientët m1, m2, ..., mn. seb Vlera standarde e gabimit për konstantën b (seb = # N / A nëse konstiti është FALSE). r2 Koeficienti i determinizmit. Vlerat aktuale y krahasohen me vlerat e marra nga ekuacioni i vijës së drejtë; bazuar në rezultatet e krahasimit, llogaritet koeficienti i determinizmit, i normalizuar nga 0 në 1. Nëse është i barabartë me 1, atëherë ka një korrelacion të plotë me modelin, domethënë nuk ka dallim midis vlerave aktuale dhe të vlerësuara ​nga y. Përndryshe, nëse koeficienti i determinizmit është 0, nuk ka kuptim të përdoret një ekuacion regresioni për të parashikuar vlerat y. Për më shumë informacion se si llogaritet r2, shihni "Vërejtje" në fund të këtij seksioni. sey Gabim standard për vlerësimin e y. F F-statistikë ose vlerë F-vëzhguar. Statistika F përdoret për të përcaktuar nëse marrëdhënia e vëzhguar ndërmjet variablave të varur dhe të pavarur është e rastësishme. df Shkallët e lirisë. Shkallët e lirisë janë të dobishme për gjetjen e vlerave F-kritike në një tabelë statistikore. Për të përcaktuar nivelin e besueshmërisë së modelit, krahasoni vlerat në tabelë me statistikën F të kthyer nga LINEST. Për më shumë informacion mbi llogaritjen e df, shihni Vërejtje në fund të këtij seksioni. Shembulli 4 më poshtë tregon përdorimin e vlerave F dhe df. ssreg Shuma e regresionit të katrorëve. ssresid Shuma e mbetur e katrorëve. Për më shumë informacion mbi llogaritjen e vlerave ssreg dhe ssresid, shihni Vërejtje në fund të këtij seksioni.

Figura më poshtë tregon rendin në të cilin kthehen statistikat shtesë të regresionit.

Shënime:

Çdo vijë e drejtë mund të përshkruhet nga pjerrësia e saj dhe kryqëzimi me boshtin y:

Pjerrësia (m): Për të përcaktuar pjerrësinë e një vije të drejtë, që zakonisht shënohet me m, duhet të merrni dy pika të vijës së drejtë dhe; pjerrësia do të jetë .

Kryqëzimi Y (b): Kryqëzimi y i një drejtëze, zakonisht i shënuar me b, është vlera y e pikës në të cilën vija pret boshtin y.

Ekuacioni drejtvizor ka formën. Nëse i dini vlerat e m dhe b, mund të llogaritni çdo pikë në vijë duke zëvendësuar vlerat y ose x në ekuacion. Ju gjithashtu mund të përdorni funksionin TREND.

Nëse ka vetëm një ndryshore të pavarur x, mund të merrni pjerrësinë dhe ndërprerjen y drejtpërdrejt duke përdorur formulat e mëposhtme:

Pjerrësia: INDEX (LINEST (të njohura_y; të njohura_x); 1)

Kryqëzimi Y: INDEX (LINEST (të njohura_y; të njohura_x); 2)

Saktësia e përafrimit të linjës LINEST varet nga shkalla e shpërndarjes në të dhëna. Sa më afër të jenë të dhënat me një vijë të drejtë, aq më i saktë është modeli LINEST. LINEST përdor metodën e katrorëve më të vegjël për të përcaktuar përshtatjen më të mirë me të dhënat. Kur ka vetëm një ndryshore të pavarur x, m dhe b llogariten duke përdorur formulat e mëposhtme:

ku x dhe y janë mesatare të mostrës, për shembull x = MESATAR (të njohura_x) dhe y = MESATAR (të njohura_y).

Funksionet e përshtatjes LINEST dhe LOGEST mund të llogarisin kurbën e drejtë ose eksponenciale që përshkruan më së miri të dhënat. Megjithatë, ata nuk i përgjigjen pyetjes se cili nga dy rezultatet është më i përshtatshëm për zgjidhjen e detyrës në fjalë. Ju gjithashtu mund të llogarisni TREND (njohur_y; njohur_x) për një vijë të drejtë, ose RRITJE (njohur_y; të njohur_x) për një kurbë eksponenciale. Këto funksione, nëse nuk specifikoni new_x_values, kthejnë një grup vlerash y të llogaritura për vlerat aktuale x përgjatë një vije të drejtë ose kurbë. Vlerat e llogaritura më pas mund të krahasohen me vlerat aktuale. Ju gjithashtu mund të ndërtoni tabela për krahasim vizual.

Me analizën e regresionit, Microsoft Excel llogarit, për çdo pikë, katrorin e diferencës midis vlerës së parashikuar y dhe vlerës aktuale y. Shuma e këtyre diferencave në katror quhet shuma e mbetur e katrorëve (ssresid). Microsoft Excel më pas llogarit shumën totale të katrorëve (sstotal). Nëse const = TRUE ose hiqet, shuma totale e katrorëve është e barabartë me shumën e katrorëve të diferencës midis vlerave aktuale y dhe vlerave mesatare y. Kur const = FALSE, shuma totale e katrorëve do të jetë e barabartë me shumën e katrorëve të vlerave reale të y (pa zbritur vlerën mesatare të y nga vlera e herësit të y). Shuma e regresionit të katrorëve pastaj mund të llogaritet si më poshtë: ssreg = sstotal - ssresid. Sa më e vogël të jetë shuma e mbetur e katrorëve, aq më e madhe është vlera e koeficientit të determinizmit r2, që tregon se sa mirë ekuacioni i marrë duke përdorur analizën e regresionit shpjegon marrëdhënien midis variablave. Koeficienti r2 është ssreg / sstotal.

Në disa raste, një ose më shumë kolona X (le vlerat Y dhe X të jenë në kolonat) nuk kanë vlerë predikative shtesë në kolonat e tjera X. Me fjalë të tjera, fshirja e një ose më shumë kolonave X mund të rezultojë në vlera Y llogaritur me të njëjtën saktësi. Në këtë rast, kolonat e tepërta X do të përjashtohen nga modeli i regresionit. Ky fenomen quhet "kolinearitet" sepse kolonat e tepërta X mund të përfaqësohen si shuma e shumë kolonave jo të tepërta. LINEST kontrollon për kolinearitet dhe heq çdo kolonë X të tepërt nga modeli i regresionit nëse i gjen. Kolonat X të fshira mund të identifikohen në daljen LINEST me një faktor 0 dhe një vlerë se 0. Heqja e një ose më shumë kolonave si të tepërta ndryshon vlerën df sepse varet nga numri i kolonave X të përdorura aktualisht për qëllime parashikuese. Për më shumë informacion mbi llogaritjen e df, shihni shembullin 4 më poshtë. Kur df ndryshon për shkak të heqjes së kolonave të tepërta, sey dhe F gjithashtu ndryshojnë. Kolineariteti shpesh dekurajohet. Megjithatë, duhet të përdoret nëse disa nga kolonat X përmbajnë 0 ose 1 si një tregues që tregon nëse subjekti i eksperimentit është në një grup të veçantë. Nëse const = TRUE ose hiqet, LINEST fut një kolonë X shtesë për të simuluar pikën e kryqëzimit. Nëse ka një kolonë me vlerat 1 për meshkujt dhe 0 për femrat, dhe ka gjithashtu një kolonë me vlerat 1 për femrat dhe 0 për meshkujt, atëherë kolona e fundit hiqet sepse vlerat e saj mund të jenë marrë nga kolona me “treguesin e gjinisë mashkullore”.

Llogaritja e df për rastet kur kolonat e X nuk hiqen nga modeli për shkak të kolinearitetit është si vijon: nëse ka k kolona të njohura_x dhe vlera e const = TRUE ose jo e specifikuar, atëherë df = n - k - 1. Nëse const = FALSE, pastaj df = n - k. Në të dyja rastet, heqja e kolonave X për shkak të kolinearitetit rrit vlerën df me 1.

Formulat që kthejnë vargje duhet të futen si formula vargjesh.

Kur futni një grup konstantesh për, për shembull, Known_x's, përdorni një pikëpresje për të ndarë vlerat në të njëjtën linjë dhe një pikëpresje për të ndarë linjat. Karakteret ndarëse ndryshojnë në varësi të opsioneve të vendosura në dritaren Gjuha dhe Standardet në panelin e kontrollit.

Duhet të theksohet se vlerat y të parashikuara nga ekuacioni i regresionit mund të mos jenë të sakta nëse janë jashtë gamës së vlerave y që janë përdorur për të përcaktuar ekuacionin.

Algoritmi kryesor i përdorur në funksion LINEST, ndryshon nga algoritmi kryesor i funksioneve PJERRJE dhe SEKSIONI... Dallimet midis algoritmeve mund të çojnë në rezultate të ndryshme për të dhëna të papërcaktuara dhe kolineare. Për shembull, nëse pikat e të dhënave të njohura_y janë 0 dhe pikat e të dhënave të njohura_x janë 1, atëherë:

Funksioni LINEST kthen një vlerë të barabartë me 0. Algoritmi i funksionit LINEST përdoret për të kthyer vlera të vlefshme për të dhënat kolineare, në të cilin rast mund të gjendet të paktën një përgjigje.

Funksionet SLOPE dhe INTERCEPT kthejnë gabimin # DIV / 0! Algoritmi i funksionit SLOPE dhe INTERCEPT përdoret për të kërkuar vetëm një përgjigje, dhe në këtë rast mund të ketë disa.

Përveç llogaritjes së statistikave për llojet e tjera të regresionit, LINEST mund të përdoret për të llogaritur intervalet për llojet e tjera të regresionit duke futur funksionet e x dhe y si seri x dhe y për LINEST. Për shembull, formula e mëposhtme:

LINEST (y-vlera, x-vlera ^ KOLONA ($ A: $ C))

funksionon duke pasur një kolonë me vlera Y dhe një kolonë me vlera X për të llogaritur një përafrim me një kub (polinom të shkallës 3) të formës së mëposhtme:

Formula mund të ndryshohet për të llogaritur llojet e tjera të regresionit, por në disa raste kërkohen rregullime në vlerat e prodhimit dhe statistika të tjera.

Tregon efektin e disa vlerave (të pavarura, të pavarura) në variablin e varur. Për shembull, se si numri i popullsisë ekonomikisht aktive varet nga numri i ndërmarrjeve, madhësia e pagave dhe parametra të tjerë. Ose: si ndikojnë në nivelin e PBB-së investimet e huaja, çmimet e energjisë etj.

Rezultati i analizës ju lejon të jepni përparësi. Dhe bazuar në faktorët kryesorë, parashikoni, planifikoni zhvillimin e fushave prioritare, merrni vendime menaxheriale.

Regresioni ndodh:

Linear (y = a + bx);

Parabolike (y = a + bx + cx 2);

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

Fuqia (y = a * x ^ b);

Hiperbolik (y = b / x + a);

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

Eksponenciale (y = a * b ^ x).

Le të shohim një shembull të ndërtimit të një modeli regresioni në Excel dhe interpretimit të rezultateve. Le të marrim një lloj regresioni linear.

Detyrë. Në 6 ndërmarrje u analizua paga mesatare mujore dhe numri i punonjësve që largohen nga puna. Është e nevojshme të përcaktohet varësia e numrit të punonjësve që largohen nga paga mesatare.

Modeli i regresionit linear është si më poshtë:

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

Ku a - koeficientët e regresionit, x - variablat ndikues, k - numri i faktorëve.

Në shembullin tonë, Y është treguesi i punonjësve që largohen. Faktori ndikues janë pagat (x).

Excel ka funksione të integruara që mund t'i përdorni për të llogaritur parametrat e një modeli të regresionit linear. Por shtesa e Paketës së Analizës do ta bëjë atë më shpejt.

Ne aktivizojmë një mjet të fuqishëm analitik:

1. Shtypni butonin "Office" dhe shkoni te skeda "Opsionet e Excel". "Shtesa".

2. Në fund, nën listën rënëse, në fushën "Control" do të ketë një mbishkrim "Excel Add-ins" (nëse nuk është atje, klikoni në kutinë e kontrollit në të djathtë dhe zgjidhni). Dhe butoni "Shko". Ne shtypim.

3. Hapet një listë e shtesave të disponueshme. Zgjidhni "Paketa e analizës" dhe klikoni OK.

Pas aktivizimit, shtesa do të jetë e disponueshme në skedën Të dhëna.

Tani le të kalojmë drejtpërdrejt në analizën e regresionit.

1. Hapni menynë e veglave "Analiza e të dhënave". Ne zgjedhim "Regresion".

2. Do të hapet një menu për zgjedhjen e vlerave të hyrjes dhe parametrave të daljes (ku të shfaqet rezultati). Në fushat për të dhënat fillestare, ne tregojmë gamën e parametrit të përshkruar (Y) dhe faktorin që ndikon në të (X). Pjesa tjetër mund të lihet bosh.

3. Pasi të klikoni OK, programi do të shfaqë llogaritjet në një fletë të re (mund të zgjidhni intervalin për t'u shfaqur në fletën aktuale ose t'i caktoni daljet një libri të ri).

Para së gjithash, kushtojini vëmendje katrorit R dhe koeficientëve.

R-katrori është koeficienti i përcaktimit. Në shembullin tonë - 0,755, ose 75,5%. Kjo do të thotë se parametrat e llogaritur të modelit shpjegojnë lidhjen midis parametrave të studiuar me 75.5%. Sa më i lartë të jetë koeficienti i përcaktimit, aq më i mirë është modeli. Mirë - mbi 0.8. E keqe - më pak se 0.5 (një analizë e tillë vështirë se mund të konsiderohet e arsyeshme). Në shembullin tonë - "jo keq".

Koeficienti 64,1428 tregon se çfarë do të jetë Y nëse të gjitha variablat në modelin në shqyrtim janë të barabartë me 0. Pra, në vlerën e parametrit të analizuar ndikojnë edhe faktorë të tjerë që nuk janë përshkruar në model.

Koeficienti -0,16285 tregon peshën e ndryshores X në Y. Kjo do të thotë, paga mesatare mujore brenda këtij modeli ndikon në numrin e personave që largohen nga puna me një peshë prej -0,16285 (kjo është një shkallë e vogël ndikimi). Shenja "-" tregon një ndikim negativ: sa më e lartë të jetë paga, aq më pak largohen. E cila është e drejtë.

Ndërtimi i një regresioni linear, vlerësimi i parametrave të tij dhe rëndësia e tyre mund të kryhet shumë më shpejt duke përdorur paketën e analizës Excel (Regresion). Merrni parasysh interpretimin e rezultateve të marra në rastin e përgjithshëm ( k variablat shpjegues) sipas shembullit 3.6.

Ne tavoline statistikat e regresionit jepen vlerat:

Të shumëfishta R - koeficienti i korrelacionit të shumëfishtë;

R- katrore- koeficienti i përcaktimit R 2 ;

Normalizuar R - katrore- rregulluar R 2 korrigjuar për numrin e shkallëve të lirisë;

Gabim standard- gabim standard i regresionit S;

Vëzhgimet - numri i vëzhgimeve n.

Ne tavoline ANOVA janë dhënë:

1. Kolona df - numri i shkallëve të lirisë, i barabartë me

për vargun Regresioni df = k;

për vargun Pjesa e mbeturdf = n – k – 1;

për vargun Totaldf = n– 1.

2. Kolona SS - shuma e katrorëve të devijimeve të barabarta me

për vargun Regresioni ;

për vargun Pjesa e mbetur ;

për vargun Total .

3. Kolona ZNJ variancat e përcaktuara nga formula ZNJ = SS/df:

për vargun Regresioni- varianca faktoriale;

për vargun Pjesa e mbetur- varianca e mbetur.

4. Kolona F - vlera e llogaritur F-kriteri i llogaritur me formulë

F = ZNJ(regresion)/ ZNJ(mbetja).

5. Kolona Rëndësia F - vlera e nivelit të rëndësisë që korrespondon me atë të llogaritur F-statistikat .

Rëndësia F= FDIST ( F- statistikat, df(regresioni), df(mbetja)).

Nëse rëndësia F < стандартного уровня значимости, то R 2 është statistikisht i rëndësishëm.

	Koeficientët	Gabim standard	t-statistika	P-vlera	fundi 95%	95% e lartë
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

Kjo tabelë tregon:

1. Shanset- vlerat e koeficientit a, b.

2. Gabim standard–Gabimet standarde të koeficientëve të regresionit S a, S b.

3. t- statistikat- vlerat e llogaritura t - kriteret e llogaritura me formulën:

t-statistika = Koeficientët / Gabim standard.

4.R-vlera (rëndësia t) A është vlera e nivelit të rëndësisë që korrespondon me atë të llogaritur t- statistikat.

R-vlera = TDIST(t- statistikat, df(mbetja)).

Nëse R-kuptim< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5... 95% e poshtme dhe 95% e lartë- kufijtë e poshtëm dhe të sipërm të intervaleve të besimit 95% për koeficientët e ekuacionit teorik të regresionit linear.

MBETET TERHEQJA
Vrojtim	Parashikuar y	Mbetet 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

Ne tavoline MBETET TERHEQJA treguar:

në kolonë Vrojtim- numri i vëzhgimit;

në kolonë E parashikuara y - vlerat e llogaritura të ndryshores së varur;

në kolonë Mbetjet e - diferenca midis vlerave të vëzhguara dhe të llogaritura të ndryshores së varur.

Shembulli 3.6. Ka të dhëna (njësi konvencionale) për kostot e ushqimit y dhe të ardhurat për frymë x për nëntë grupe familjesh:

x
y

Duke përdorur rezultatet e paketës së analizës Excel (Regresioni), le të analizojmë varësinë e kostove të ushqimit nga sasia e të ardhurave për frymë.

Është e zakonshme të shkruani rezultatet e analizës së regresionit në formën:

ku gabimet standarde të koeficientëve të regresionit tregohen në kllapa.

Koeficientët e regresionit a = 65,92 dhe b= 0,107. Drejtimi i komunikimit ndërmjet y dhe x përcakton shenjën e koeficientit të regresionit b= 0,107, d.m.th. lidhja është e drejtpërdrejtë dhe pozitive. Koeficient b= 0,107 tregon se me një rritje të të ardhurave për frymë me 1 konv. njësi kostot e ushqimit rriten me 0,107 konv. njësi

Le të vlerësojmë rëndësinë e koeficientëve të modelit që rezulton. Rëndësia e koeficientëve ( a, b) kontrollohet nga t-test:

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

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

prandaj, koeficientët ( a, b) janë të rëndësishme në nivelin 1%, dhe aq më tepër në nivelin 5% të rëndësisë. Kështu, koeficientët e regresionit janë të rëndësishëm dhe modeli është adekuat me të dhënat origjinale.

Rezultatet e vlerësimit të regresionit janë të pajtueshme jo vetëm me vlerat e marra të koeficientëve të regresionit, por edhe me një pjesë të grupit të tyre (intervali i besimit). Me një probabilitet prej 95%, intervalet e besimit për koeficientët janë (38,16 - 93,68) për a dhe (0,0728 - 0,142) për b.

Cilësia e modelit vlerësohet nga koeficienti i përcaktimit R 2 .

Madhësia R 2 = 0,884 do të thotë se 88,4% e variacionit (përhapjes) në shpenzimet ushqimore mund të shpjegohet me faktorin e të ardhurave për frymë.

Rëndësia R 2 kontrollohet nga F- test: rëndësi F = 0,00016 < 0,01 < 0,05, следовательно, R 2 është i rëndësishëm në nivelin 1%, dhe aq më tepër në nivelin 5% të rëndësisë.

Në rastin e regresionit linear të çiftuar, koeficienti i korrelacionit mund të përkufizohet si ... Vlera e përftuar e koeficientit të korrelacionit tregon se lidhja ndërmjet shpenzimeve ushqimore dhe të ardhurave për frymë është shumë e ngushtë.

Analiza e regresionit është një nga metodat më të kërkuara të kërkimit statistikor. Mund të përdoret për të përcaktuar shkallën e ndikimit të variablave të pavarur në variablin e varur. Në funksionalitetin e Microsoft Excel, ka mjete të dizajnuara për këtë lloj analize. Le të hedhim një vështrim se çfarë janë dhe si t'i përdorim ato.

Lidhja e paketës së analizës

Por, për të përdorur funksionin që ju lejon të kryeni analizën e regresionit, para së gjithash, duhet të aktivizoni Paketën e Analizës. Vetëm atëherë mjetet e nevojshme për këtë procedurë do të shfaqen në shiritin e Excel.

1. Kaloni në skedën "File".
2. Shkoni te seksioni "Parametrat".
3. Hapet dritarja e opsioneve të Excel. Shkoni te nënseksioni "Shtesa".
4. Në fund të dritares që hapet, zhvendoseni çelësin në bllokun "Control" në pozicionin "Excel Add-ins", nëse është në një pozicion tjetër. Klikoni në butonin "Shko".
5. Hapet dritarja e disponueshme e shtesave të Excel. Vendosim një shenjë pranë artikullit "Paketa e analizës". Klikoni në butonin "OK".

Tani, kur shkojmë në skedën "Të dhënat", në shiritin në kutinë e veglave "Analiza" do të shohim një buton të ri - "Analiza e të dhënave".

Llojet e analizës së regresionit

Ekzistojnë disa lloje të regresioneve:

• parabolike;
• fuqia e ligjit;
• logaritmike;
• eksponenciale;
• tregues;
• hiperbolike;
• regresionit linear.

Do të flasim më në detaje për performancën e llojit të fundit të analizës së regresionit në Excel.

Regresioni linear në Excel

Më poshtë, si shembull, është një tabelë që tregon temperaturën mesatare ditore të ajrit jashtë dhe numrin e blerësve të dyqaneve për ditën përkatëse të punës. Le të zbulojmë me ndihmën e analizës së regresionit saktësisht se si kushtet e motit në formën e temperaturës së ajrit mund të ndikojnë në frekuentimin e prizës.

Ekuacioni i përgjithshëm i regresionit linear duket si vijon: Y = a0 + a1x1 +… + akhk. Në këtë formulë, Y nënkupton variablin, ndikimin e faktorëve mbi të cilët po përpiqemi të studiojmë. Në rastin tonë, ky është numri i blerësve. Vlera x është faktorë të ndryshëm që ndikojnë në ndryshore. Parametrat a janë koeficientët e regresionit. Kjo do të thotë, janë ata që përcaktojnë rëndësinë e këtij apo atij faktori. Indeksi k tregon numrin total të këtyre faktorëve të njëjtë.

Analiza e rezultateve të analizës

Rezultatet e analizës së regresionit shfaqen në formën e një tabele në vendin e specifikuar në cilësimet.

Një nga treguesit kryesorë është R-katrori. Ai tregon cilësinë e modelit. Në rastin tonë, ky raport është 0,705 ose rreth 70,5%. Ky është një nivel i pranueshëm i cilësisë. Varësia më pak se 0.5 është e keqe.

Një tregues tjetër i rëndësishëm ndodhet në qelizën në kryqëzimin e vijës "Kryqëzimi Y" dhe kolonës "Koeficientët". Ai tregon se çfarë vlere do të ketë Y, dhe në rastin tonë, ky është numri i blerësve, me të gjithë faktorët e tjerë të barabartë me zero. Në këtë tabelë, kjo vlerë është 58.04.

Vlera në kryqëzimin e kolonave "Variable X1" dhe "Koeficientët" tregon nivelin e varësisë së Y nga X. Në rastin tonë, është niveli i varësisë së numrit të klientëve të dyqanit nga temperatura. Një raport prej 1.31 konsiderohet një tregues mjaft i lartë i ndikimit.

Siç mund ta shihni, është mjaft e lehtë të krijoni një tabelë analize të regresionit duke përdorur Microsoft Excel. Por, vetëm një person i trajnuar mund të punojë me të dhënat e marra në dalje dhe të kuptojë thelbin e tyre.

Jemi të kënaqur që ishim në gjendje t'ju ndihmojmë në zgjidhjen e problemit.

Bëni pyetjen tuaj në komente, duke detajuar thelbin e problemit. Ekspertët tanë do të përpiqen të përgjigjen sa më shpejt që të jetë e mundur.

A ju ndihmoi ky artikull?

Regresioni linear na lejon të përshkruajmë vijën e drejtë që përputhet më mirë me një seri çiftesh të renditura (x, y). Ekuacioni për një vijë të drejtë, i njohur si ekuacioni linear, është paraqitur më poshtë:

ŷ është vlera e pritur e y për një vlerë të dhënë të x,

x është ndryshorja e pavarur,

a - segment në boshtin y për një vijë të drejtë,

b - pjerrësia e një vije të drejtë.

Figura më poshtë ilustron këtë koncept grafikisht:

Fotografia e mësipërme tregon vijën e përshkruar nga ekuacioni ŷ = 2 + 0,5x. Segmenti në boshtin y është pika e prerjes së drejtëzës me boshtin y; në rastin tonë a = 2. Pjerrësia e vijës, b, raporti i ngritjes së vijës me gjatësinë e vijës, ka vlerën 0,5. Një pjerrësi pozitive do të thotë se vija po ngjitet nga e majta në të djathtë. Nëse b = 0, vija është horizontale, që do të thotë se nuk ka lidhje ndërmjet variablave të varur dhe të pavarur. Me fjalë të tjera, ndryshimi i vlerës x nuk ndikon në vlerën y.

Ŷ dhe y shpesh ngatërrohen. Grafiku tregon 6 çifte pikash të renditura dhe një vijë sipas këtij ekuacioni

Kjo figurë tregon pikën që i korrespondon çiftit të renditur x = 2 dhe y = 4. Vini re se vlera e pritshme e y sipas vijës në X= 2 është ŷ. Këtë mund ta konfirmojmë me ekuacionin e mëposhtëm:

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

Vlera y është pika aktuale, dhe vlera është vlera y e pritur duke përdorur një ekuacion linear për një vlerë x të dhënë.

Hapi tjetër është përcaktimi i ekuacionit linear që përputhet më shumë me grupin e çifteve të renditura, kemi folur për këtë në artikullin e mëparshëm, ku kemi përcaktuar formën e ekuacionit duke përdorur metodën e katrorëve më të vegjël.

Përdorimi i Excel për të përcaktuar regresionin linear

Për të përdorur mjetin e analizës së regresionit të integruar në Excel, duhet të aktivizoni shtesën Paketa e analizës... Mund ta gjeni duke klikuar në skedën Skedari -> Opsionet(2007+), në kutinë e dialogut që shfaqet ParametratExcel shkoni te skeda Shtesa. Në fushë Kontrolli zgjidhni ShtesaExcel dhe klikoni Shkoni. Në dritaren që shfaqet, vendosni një shenjë përballë Paketa e analizës, shtypim NE RREGULL.

Në skedën Të dhënat në grup Analiza do të shfaqet një buton i ri Analiza e të dhënave.

Për të demonstruar se si funksionon shtesa, le të përdorim të dhënat nga artikulli i mëparshëm, ku një djalë dhe një vajzë ndajnë një tavolinë në banjë. Futni të dhënat për shembullin tonë të vaskës në kolonat A dhe B të fletës së zbrazët.

Shkoni te skeda Të dhënat, në grup Analiza klikoni Analiza e të dhënave. Në dritaren që shfaqet Analiza e të dhënave zgjidhni Regresioni siç tregohet dhe klikoni OK.

Vendosni parametrat e kërkuar të regresionit në dritare Regresioni, siç tregohet në foto:

Klikoni NE RREGULL. Figura më poshtë tregon rezultatet e marra:

Këto rezultate janë në përputhje me ato që kemi marrë nga vetë-llogaritja në artikullin e mëparshëm.

Analiza e regresionit është një metodë kërkimore statistikore që ju lejon të tregoni varësinë e një parametri nga një ose më shumë variabla të pavarur. Në epokën para kompjuterit, aplikimi i tij ishte mjaft i vështirë, veçanërisht kur bëhej fjalë për sasi të mëdha të dhënash. Sot, pasi keni mësuar se si të ndërtoni një regresion në Excel, mund të zgjidhni probleme komplekse statistikore në vetëm disa minuta. Më poshtë janë shembuj specifikë nga fusha e ekonomisë.

Llojet e regresionit

Vetë koncepti u fut në matematikë nga Francis Galton në 1886. Regresioni ndodh:

• lineare;
• parabolike;
• fuqia e ligjit;
• eksponenciale;
• hiperbolike;
• tregues;
• logaritmike.

Shembulli 1

Le të shqyrtojmë problemin e përcaktimit të varësisë së numrit të punonjësve që braktisin punën nga paga mesatare në 6 ndërmarrje industriale.

Detyrë. Gjashtë ndërmarrje kanë analizuar pagën mesatare mujore dhe numrin e punonjësve që janë larguar vullnetarisht. Në formë tabelare kemi:

Për problemin e përcaktimit të varësisë së numrit të punonjësve të larguar nga paga mesatare në 6 ndërmarrje, modeli i regresionit ka formën e ekuacionit Y = a0 + a1 × 1 +… + akxk, ku xi janë variabla ndikues, ai janë koeficientët e regresionit, dhe k është numri i faktorëve.

Për këtë detyrë, Y është treguesi i punonjësve që largohen dhe faktori ndikues është paga, të cilën e shënojmë me X.

Përdorimi i aftësive të procesorit të tabelave Excel

Analiza e regresionit në Excel duhet të paraprihet nga aplikimi i funksioneve të integruara në të dhënat ekzistuese tabelare. Megjithatë, për këto qëllime është më mirë të përdorni shtesën shumë të dobishme "Paketa e Analizës". Për ta aktivizuar ju duhet:

• nga skeda "File" shkoni te seksioni "Parametrat";
• në dritaren që hapet, zgjidhni rreshtin "Shtesa";
• klikoni në butonin "Shko" që ndodhet më poshtë, në të djathtë të rreshtit "Control";
• vendosni një shenjë pranë emrit "Paketa e analizës" dhe konfirmoni veprimet tuaja duke klikuar "OK".

Nëse gjithçka është bërë në mënyrë korrekte, butoni i kërkuar do të shfaqet në anën e djathtë të skedës "Data", e vendosur mbi fletën e punës "Excel".

Regresioni linear në Excel

Tani që kemi në dispozicion të gjitha mjetet e nevojshme virtuale për kryerjen e llogaritjeve ekonometrike, mund të fillojmë të zgjidhim problemin tonë. Për këtë:

• klikoni në butonin "Analiza e të dhënave";
• në dritaren që hapet, klikoni në butonin "Regresion";
• në skedën që shfaqet, vendosni gamën e vlerave për Y (numri i punonjësve që largohen) dhe për X (pagat e tyre);
• ne konfirmojmë veprimet tona duke shtypur butonin "Ok".

Si rezultat, programi do të plotësojë automatikisht fletën e re të procesorit të fletëllogaritjes me të dhënat e analizës së regresionit. Shënim! Excel ka aftësinë të përcaktojë në mënyrë të pavarur vendndodhjen që preferoni për këtë qëllim. Për shembull, mund të jetë e njëjta fletë që përmban vlerat Y dhe X, apo edhe një libër i ri pune i krijuar posaçërisht për të ruajtur këtë lloj të dhënash.

Analiza e rezultateve të regresionit për R-Square

Në Excel, të dhënat e marra gjatë përpunimit të të dhënave të shembullit në fjalë janë si më poshtë:

Para së gjithash, duhet t'i kushtoni vëmendje vlerës së katrorit R. Ai përfaqëson koeficientin e përcaktimit. Në këtë shembull, R-katror = 0.755 (75.5%), domethënë parametrat e llogaritur të modelit shpjegojnë lidhjen midis parametrave të konsideruar me 75.5%. Sa më e lartë të jetë vlera e koeficientit të përcaktimit, aq më shumë modeli i zgjedhur konsiderohet të jetë më i zbatueshëm për një detyrë specifike. Besohet se përshkruan saktë situatën reale kur vlera e katrorit R është mbi 0.8. Nëse katrori R është tcr, atëherë hipoteza e parëndësisë së termit të lirë të ekuacionit linear hidhet poshtë.

Në problemin e shqyrtuar për një term të lirë duke përdorur mjetet e Excel, u mor se t = 169.20903, dhe p = 2.89E-12, domethënë, kemi një probabilitet zero që hipoteza e saktë për parëndësinë e termit të lirë do të jetë refuzuar. Për koeficientin në të panjohur t = 5,79405, dhe p = 0,001158. Me fjalë të tjera, probabiliteti që hipoteza e saktë për parëndësinë e koeficientit me të panjohurën të hidhet poshtë është 0.12%.

Kështu, mund të argumentohet se ekuacioni i regresionit linear që rezulton është adekuat.

Problemi i përshtatshmërisë së blerjes së një blloku aksionesh

Regresioni i shumëfishtë në Excel kryhet duke përdorur të njëjtin mjet Analiza e të Dhënave. Le të shqyrtojmë një problem specifik të aplikuar.

Menaxhmenti i kompanisë "NNN" duhet të vendosë për këshillueshmërinë e blerjes së 20% të aksioneve në SHA "MMM". Kostoja e paketës (JV) është 70 milion dollarë. Specialistët e NNN kanë mbledhur të dhëna për transaksione të ngjashme. U vendos që vlera e bllokut të aksioneve të vlerësohej me parametra të tillë, të shprehur në miliona dollarë amerikanë, si:

• llogaritë e pagueshme (VK);
• vëllimi i qarkullimit vjetor (VO);
• llogaritë e arkëtueshme (VD);
• kostoja e aseteve fikse (SOF).

Për më tepër, parametri është paga e prapambetur e ndërmarrjes (V3 P) në mijëra dollarë amerikanë.

Zgjidhja e tabelave të Excel

Para së gjithash, ju duhet të krijoni një tabelë të të dhënave fillestare. Duket kështu:

• telefononi dritaren "Analiza e të dhënave";
• zgjidhni seksionin "Regresioni";
• diapazoni i vlerave të variablave të varur nga kolona G futet në kutinë "Input interval Y";
• klikoni në ikonën me një shigjetë të kuqe në të djathtë të dritares "Input interval X" dhe zgjidhni në fletë gamën e të gjitha vlerave nga kolonat B, C, D, F.

Kontrolloni artikullin "Fletë e re e punës" dhe klikoni "Ok".

Merrni një analizë regresioni për një detyrë të caktuar.

Studimi i rezultateve dhe përfundimeve

Ne "mbledhim" ekuacionin e regresionit nga të dhënat e rrumbullakosura të paraqitura më sipër në fletën e tabelës Excel:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

Në një formë matematikore më të njohur, mund të shkruhet si:

y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265,844

Të dhënat për SHA "MMM" janë paraqitur në tabelë:

Duke i zëvendësuar ato në ekuacionin e regresionit, shifra është 64.72 milionë dollarë amerikanë. Kjo do të thotë se aksionet e SHA "MMM" nuk duhet të blihen, pasi vlera e tyre prej 70 milionë dollarësh është mjaft e mbivlerësuar.

Siç mund ta shihni, përdorimi i procesorit Excel dhe ekuacioni i regresionit bënë të mundur marrjen e një vendimi të informuar në lidhje me këshillueshmërinë e një transaksioni shumë specifik.

Tani e dini se çfarë është regresioni. Shembujt në Excel të diskutuar më sipër do t'ju ndihmojnë të zgjidhni problemet praktike në fushën e ekonometrisë.