Regresion i shumëfishtë në shtesën excel. Ndërtimi i një ekuacioni të regresionit të shumëfishtë në Excel

Përpunimi statistikor i të dhënave mund të kryhet gjithashtu duke përdorur një shtesë PAKETA E ANALIZËS(Fig. 62).

Nga artikujt e sugjeruar, zgjidhni artikullin " REGRESIONI" dhe klikoni mbi të me butonin e majtë të miut. Tjetra, klikoni OK.

Do të shfaqet një dritare siç tregohet në Fig. 63.

Mjeti i analizës " REGRESIONI» përdoret për të përshtatur një grafik në 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ë ndryshore të vetme të varur të vlerave të një ose më shumë variablave të pavarur. Për shembull, disa faktorë ndikojnë në performancën atletike të një atleti, duke përfshirë moshën, gjatësinë dhe peshën. Është e mundur të llogaritet shkalla në të cilën secili prej këtyre tre faktorëve ndikon në performancën e një atleti dhe më pas të përdoren 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 përshtatshëm për të dhënat e tabelës dalëse do të krijohen automatikisht.

Niveli i besueshmërisë Zgjidhni kutinë e kontrollit për të përfshirë një nivel shtesë në tabelën përmbledhëse të prodhimit. Në fushën përkatëse, vendosni nivelin e besimit që dëshironi të aplikoni, përveç nivelit të paracaktuar 95%.

Konstante - zero Zgjidhni kutinë e kontrollit për të detyruar vijën e regresionit të kalojë përmes origjinës.

Gama e daljes Futni referencën në qelizën e sipërme majtas të diapazonit të daljes. Jepni të paktën shtatë kolona për tabelën përmbledhëse të daljes, 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ë opsion për të hapur një fletë të re pune në librin e punës dhe për të ngjitur 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 që ndodhet përballë butonit përkatës të radios.

Libri i ri i punës Zgjidhni këtë opsion për të krijuar një libër të ri pune me rezultatet e shtuara në një fletë të re pune.

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

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

Skema e mbetur Zgjidhni kutinë e kontrollit për të vizatuar mbetjet për çdo variabël të pavarur.

Fit Plot Zgjidhni kutinë e kontrollit për të vizatuar vlerat e parashikuara kundrejt atyre të vëzhguara.

Grafik i probabilitetit normal Zgjidhni kutinë e zgjedhjes për të hartuar një grafik probabiliteti normal.

Funksioni LINEST

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

Funksioni LINEST llogarit statistikat për një seri duke përdorur metodën e katrorëve më të vegjël për të llogaritur vijën e drejtë që përafron më mirë të dhënat e 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ë. Për shkak se një grup vlerash është kthyer, funksioni duhet të specifikohet si një formulë grupi.

Ekuacioni për një vijë të drejtë është:

y=m 1 x 1 +m 2 x 2 +…+b (në rastin e disa vargjeve 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 grupin (mn;mn-1;…;m 1 ;b). LINEST gjithashtu mund të kthejë statistika shtesë të regresionit.

LINEST(vlerat_njohura_y; vlerat_njohura_x; konst; statistikat)

Vlerat_y_njohura - një grup vlerash y që janë tashmë të njohura për relacionin y=mx+b.

Nëse grupi Known_y_values ​​ka një kolonë, atëherë çdo kolonë në grupin Known_x_values ​​trajtohet si një variabël më vete.

Nëse grupi Known_y_values ​​ka një rresht, atëherë çdo rresht në grupin Known_x_values ​​trajtohet si një ndryshore e veçantë.

Vlerat e njohura_x janë një grup opsional i vlerave x që janë tashmë të njohura për marrëdhënien y=mx+b.

Vargu i njohur_x_vlerave mund të përmbajë një ose më shumë grupe variablash. Nëse përdoret vetëm një variabël, atëherë grupet e njohura_y_values ​​dhe të njohura_x_vlerave mund të kenë çdo formë - për sa kohë që kanë të njëjtin dimension. Nëse përdoret më shumë se një ndryshore, atëherë know_y_values ​​duhet të jetë një vektor (d.m.th., një interval një rresht i lartë ose një kolonë i gjerë).

Nëse array_known_x_values ​​hiqet, atëherë grupi (1;2;3;...) supozohet të jetë i njëjtë me madhësinë e array_known_values_y.

Const është një vlerë boolean që specifikon nëse konstanta b kërkohet të jetë e barabartë me 0.

Nëse argumenti "const" është TRUE ose i anashkaluar, atëherë konstanta b vlerësohet si zakonisht.

Nëse argumenti “const” është FALSE, atëherë vlera e b vendoset në 0 dhe vlerat e m zgjidhen në atë mënyrë që relacioni y=mx të plotësohet.

Statistikat - Një vlerë boolean që tregon nëse statistikat shtesë të regresionit duhet të kthehen.

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

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 const është FALSE).
r2	Koeficienti i determinizmit. Krahasohen vlerat aktuale të y dhe vlerat e marra nga ekuacioni i vijës; 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, d.m.th., nuk ka dallim midis vlerave aktuale dhe të vlerësuara. e y. Në rastin e kundërt, nëse koeficienti i përcaktimit është 0, nuk ka kuptim të përdoret ekuacioni i regresionit për të parashikuar vlerat e y. Për më shumë informacion se si të llogaritni r2, shihni "Shënimet" 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 midis një variabli të varur dhe të pavarur është për shkak të rastësisë.
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 besimit të modelit, krahasoni vlerat në tabelë me statistikën F të kthyer nga funksioni LINEST. Për më shumë informacion rreth llogaritjes së df, shihni "Shënimet" në fund të këtij seksioni. Më pas, Shembulli 4 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 rreth llogaritjes së ssreg dhe ssresid, shihni "Shënimet" në fund të këtij seksioni.

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

Shënime:

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

Pjerrësia (m): Për të përcaktuar pjerrësinë e një vije, që zakonisht shënohet me m, duhet të merrni dy pika në vijë (x 1 ,y 1) dhe (x 2 ,y 2); pjerrësia do të jetë e barabartë me (y 2 -y 1)/(x 2 -x 1).

Ndërprerja Y (b): Ndërprerja y e një drejtëze, zakonisht e shënuar me b, është vlera y për pikën në të cilën vija kryqëzon boshtin y.

Ekuacioni i drejtëzës është y=mx+b. Nëse dihen vlerat e m dhe b, atëherë çdo pikë në vijë mund të llogaritet duke zëvendësuar vlerat e 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(vlera_y_të njohura; vlera_x_të njohura); 1)

Ndërprerja Y: INDEX(LINEST(vlera_y_të njohura; vlera_x_të njohura); 2)

Saktësia e përafrimit duke përdorur vijën e drejtë të llogaritur nga funksioni LINEST varet nga shkalla e shpërndarjes së të dhënave. Sa më afër të jenë të dhënat me një vijë të drejtë, aq më i saktë është modeli i përdorur nga funksioni LINEST. Funksioni LINEST përdor katrorët 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 LGRFPRIBL mund të llogarisin vijën e drejtë ose kurbën eksponenciale që i përshtatet më mirë të dhënave. Megjithatë, ata nuk i përgjigjen pyetjes se cili nga dy rezultatet është më i përshtatshëm për zgjidhjen e problemit. Ju gjithashtu mund të vlerësoni funksionin TREND(vlerat_y_njohura; vlerat_x_njohura) për një vijë të drejtë ose funksionin GROWTH(vlerat_y_njohura; vlerat_x_njohura) për një kurbë eksponenciale. Këto funksione, përveç nëse specifikohen new_x-values, kthejnë një grup vlerash y të llogaritura për vlerat aktuale x përgjatë një linje ose kurbe. Më pas mund të krahasoni vlerat e llogaritura me vlerat aktuale. Ju gjithashtu mund të krijoni tabela për krahasim vizual.

Kur kryen 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 vlera e këtij argumenti nuk specifikohet, shuma totale e katrorëve do të jetë e barabartë me shumën e katrorëve të diferencave 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 y (pa zbritur vlerën mesatare y nga vlera e pjesshme 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ë përcaktimit r2, që tregon se sa mirë ekuacioni i marrë duke përdorur analizën e regresionit shpjegon marrëdhëniet midis variablave. Koeficienti r2 është i barabartë me ssreg/sstotal.

Në disa raste, një ose më shumë kolona X (le vlerat Y dhe X të jenë në kolona) nuk kanë vlerë predikative shtesë në kolonat e tjera X. Me fjalë të tjera, heqja e një ose më shumë kolonave X mund të rezultojë në vlerat Y të llogaritura 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 të X mund të përfaqësohen si shuma e disa kolonave jo të tepërta. Funksioni LINEST kontrollon për kolinearitetin dhe heq çdo kolonë X të tepërt nga modeli i regresionit nëse i zbulon ato. Kolonat X të hequra 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 e 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, ndryshojnë edhe vlerat e sey dhe F. Nuk rekomandohet të përdoret shpesh kolineariteti. Megjithatë, duhet të përdoret nëse disa kolona X përmbajnë 0 ose 1 si një tregues që tregon nëse subjekti i eksperimentit i përket një grupi të veçantë. Nëse const = TRUE ose një vlerë për këtë argument nuk është specifikuar, LINEST fut një kolonë X shtesë për të modeluar pikën e kryqëzimit. Nëse ka një kolonë me vlerat 1 për burrat dhe 0 për gratë, dhe ka një kolonë me vlerat 1 për gratë dhe 0 për burrat, atëherë kolona e fundit hiqet sepse mund të merren vlerat e saj. nga kolona "treguesi mashkull".

Llogaritja e df për rastet kur X kolonat nuk hiqen nga modeli për shkak të kolinearitetit ndodh si më poshtë: nëse ka k kolona të njohura_x dhe vlera const = TRUE ose jo e specifikuar, atëherë df = n – k – 1. Nëse const = E rreme, 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 si argument, për shembull, Known_x_values, duhet të 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 mund të ndryshojnë në varësi të cilësimeve në dritaren Gjuha dhe Cilësimet në Panelin e Kontrollit.

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

Algoritmi bazë i përdorur në funksion LINEST, ndryshon nga algoritmi i funksionit kryesor PJERRJE Dhe SEGMENTI I LINJËS. Dallimi midis algoritmeve mund të çojë në rezultate të ndryshme me të dhëna të pasigurta dhe kolineare. Për shembull, nëse pikat e të dhënave të argumentit të njohur_y_vlerat janë 0 dhe pikat e të dhënave të argumentit të njohur_x_vlerat 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ë përshtatshme për të dhënat kolineare, dhe në këtë rast mund të gjendet të paktën një përgjigje.

Funksionet SLOPE dhe LINE kthejnë gabimin #DIV/0!. Algoritmi i funksioneve SLOPE dhe INTERCEPT përdoret për të gjetur vetëm një përgjigje, por 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 funksione të ndryshoreve x dhe y si seri të ndryshoreve x dhe y për LINEST. Për shembull, formula e mëposhtme:

LINEST(y_vlerat, x_vlerat^COLUMN($A:$C))

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

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

Formula mund të modifikohet për të llogaritur llojet e tjera të regresionit, por në disa raste vlerat e prodhimit dhe statistikat e tjera mund të kenë nevojë të rregullohen.

Është i njohur për të qenë i dobishëm në fusha të ndryshme të veprimtarisë, duke përfshirë një disiplinë të tillë si ekonometria, ku ky mjet softuerësh përdoret në punë. Në thelb, të gjitha veprimet e klasave praktike dhe laboratorike kryhen në Excel, gjë që lehtëson shumë punën duke ofruar shpjegime të hollësishme të veprimeve të caktuara. Kështu, një nga mjetet e analizës "Regresioni" përdoret për të zgjedhur një grafik për një grup vëzhgimesh duke përdorur metodën e katrorëve më të vegjël. Le të shohim se çfarë është ky mjet programi dhe cilat janë përfitimet e tij për përdoruesit. Më poshtë do të gjeni gjithashtu udhëzime të shkurtra, por të qarta për ndërtimin e një modeli regresioni.

Detyrat kryesore dhe llojet e regresionit

Regresioni përfaqëson marrëdhënien ndërmjet variablave të dhënë, duke bërë të mundur parashikimin e sjelljes së ardhshme të këtyre variablave. Variablat janë dukuri të ndryshme periodike, duke përfshirë sjelljen njerëzore. Ky lloj i analizës Excel përdoret për të analizuar ndikimin në një variabël të varur specifik të vlerave të një ose një numri variablash. Për shembull, shitjet në një dyqan ndikohen nga disa faktorë, duke përfshirë asortimentin, çmimet dhe vendndodhjen e dyqanit. Falë regresionit në Excel, ju mund të përcaktoni shkallën e ndikimit të secilit prej këtyre faktorëve bazuar në rezultatet e shitjeve ekzistuese, dhe më pas të aplikoni të dhënat e marra për të parashikuar shitjet për një muaj tjetër ose për një dyqan tjetër që ndodhet afër.

Në mënyrë tipike, regresioni paraqitet si një ekuacion i thjeshtë që zbulon marrëdhëniet dhe pikat e forta të marrëdhënieve midis dy grupeve të variablave, ku njëri grup është i varur ose endogjen dhe tjetri është i pavarur ose ekzogjen. Nëse ekziston një grup treguesish të ndërlidhur, ndryshorja e varur Y përcaktohet në bazë të logjikës së arsyetimit dhe pjesa tjetër veprojnë si ndryshore X të pavarura.

Detyrat kryesore të ndërtimit të një modeli regresioni janë si më poshtë:

1. Zgjedhja e variablave të pavarur të rëndësishëm (X1, X2, ..., Xk).
2. Zgjedhja e llojit të funksionit.
3. Ndërtimi i vlerësimeve për koeficientët.
4. Ndërtimi i intervaleve të besimit dhe funksioneve të regresionit.
5. Kontrollimi i rëndësisë së vlerësimeve të llogaritura dhe ekuacioni i ndërtuar i regresionit.

Ekzistojnë disa lloje të analizës së regresionit:

• çift ​​(1 variabla e varur dhe 1 e pavarur);
• të shumëfishta (disa ndryshore të pavarura).

Ekzistojnë dy lloje të ekuacioneve të regresionit:

1. Linear, që ilustron një marrëdhënie të rreptë lineare midis variablave.
2. Jolineare - Ekuacione që mund të përfshijnë fuqi, thyesa dhe funksione trigonometrike.

Udhëzime për ndërtimin e një modeli

Për të kryer një ndërtim të caktuar në Excel, duhet të ndiqni udhëzimet:

Për llogaritje të mëtejshme, përdorni funksionin "Linear()", duke specifikuar vlerat Y, Vlerat X, Konstit dhe Statistikat. Pas kësaj, përcaktoni grupin e pikave në vijën e regresionit duke përdorur funksionin "Trend" - Vlerat Y, Vlerat X, Vlerat e reja, Konst. Duke përdorur parametrat e dhënë, llogaritni vlerën e panjohur të koeficientëve, bazuar në kushtet e dhëna të problemit.

Analiza e regresionit është një metodë kërkimore statistikore që ju lejon të tregoni varësinë e një parametri të veçantë nga një ose më shumë variabla të pavarur. Në epokën para kompjuterit, përdorimi i tij ishte mjaft i vështirë, veçanërisht kur bëhej fjalë për vëllime të mëdha të dhënash. Sot, pasi keni mësuar se si të ndërtoni 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ë ky koncept u fut në matematikë në 1886. Regresioni ndodh:

• lineare;
• parabolike;
• qetësues;
• eksponenciale;
• hiperbolike;
• demonstrative;
• logaritmike.

Shembulli 1

Le të shqyrtojmë problemin e përcaktimit të varësisë së numrit të anëtarëve të ekipit që largohen nga paga mesatare në 6 ndërmarrje industriale.

Detyrë. Në gjashtë ndërmarrje u analizua paga mesatare mujore dhe numri i të punësuarve që largohen vullnetarisht. Në formë tabelare kemi:

		Numri i njerëzve që kanë lënë duhanin	Paga
			30,000 rubla
			35,000 rubla
			40,000 rubla
			45,000 rubla
			50,000 rubla
			55,000 rubla
			60,000 rubla

Për detyrën e përcaktimit të varësisë së numrit të punonjësve që largohen nga paga mesatare në 6 ndërmarrje, modeli i regresionit ka formën e ekuacionit Y = a 0 + a 1 x 1 +...+a k x k, ku x i janë variablat ndikues, a i janë koeficientët e regresionit dhe k është numri i faktorëve.

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

Përdorimi i aftësive të procesorit Excel

Analiza e regresionit në Excel duhet të paraprihet duke aplikuar funksione të integruara në të dhënat ekzistuese tabelare. Sidoqoftë, për këto qëllime është më mirë të përdorni shtesën shumë të dobishme "Analysis Pack". Për ta aktivizuar ju duhet:

• nga skeda "File" shkoni te seksioni "Opsionet";
• në dritaren që hapet, zgjidhni rreshtin "Shtesa";
• klikoni në butonin "Shko" që ndodhet më poshtë, në të djathtë të rreshtit "Menaxhimi";
• kontrolloni kutinë pranë emrit "Paketa e analizës" dhe konfirmoni veprimet tuaja duke klikuar "Ok".

Nëse gjithçka është bërë si duhet, butoni i kërkuar do të shfaqet në anën e djathtë të skedës "Të dhënat", e vendosur mbi fletën e punës Excel.

në Excel

Tani që kemi në dispozicion të gjitha mjetet e nevojshme virtuale për të kryer llogaritjet 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, futni gamën e vlerave për Y (numri i punonjësve që largohen nga puna) dhe për X (pagat e tyre);
• Ne konfirmojmë veprimet tona duke shtypur butonin "Ok".

Si rezultat, programi do të mbushë automatikisht një tabelë të re me të dhëna të analizës së regresionit. Shënim! Excel ju lejon të vendosni manualisht vendndodhjen që preferoni për këtë qëllim. Për shembull, kjo mund të jetë e njëjta fletë ku ndodhen vlerat Y dhe X, ose edhe një libër i ri pune i krijuar posaçërisht për të ruajtur të dhëna të tilla.

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

Në Excel, të dhënat e marra gjatë përpunimit të të dhënave në shembullin në shqyrtim kanë formën:

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%), d.m.th., parametrat e llogaritur të modelit shpjegojnë lidhjen midis parametrave në shqyrtim me 75.5%. Sa më e lartë të jetë vlera e koeficientit të përcaktimit, aq më i përshtatshëm është modeli i përzgjedhur për një detyrë specifike. Konsiderohet se përshkruan saktë situatën reale kur vlera e katrorit R është mbi 0.8. Nëse R-katror<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Analiza e Shanseve

Numri 64.1428 tregon se cila do të jetë vlera e Y nëse të gjitha variablat xi në modelin që po shqyrtojmë rivendosen në zero. Me fjalë të tjera, mund të argumentohet se vlera e parametrit të analizuar ndikohet edhe nga faktorë të tjerë që nuk janë përshkruar në një model specifik.

Koeficienti tjetër -0,16285, i vendosur në qelizën B18, tregon peshën e ndikimit të ndryshores X në Y. Kjo do të thotë se paga mesatare mujore e punonjësve brenda modelit në shqyrtim ndikon në numrin e larguesve me peshë -0,16285, d.m.th. shkalla e ndikimit të saj është krejtësisht e vogël. Shenja "-" tregon se koeficienti është negativ. Kjo është e qartë, pasi të gjithë e dinë që sa më e lartë të jetë paga në ndërmarrje, aq më pak njerëz shprehin dëshirën për të ndërprerë kontratën e punës ose për t'u larguar.

Regresion i shumëfishtë

Ky term i referohet një ekuacioni të marrëdhënieve me disa variabla të pavarur të formës:

y=f(x 1 +x 2 +…x m) + ε, ku y është karakteristika rezultante (ndryshore e varur), dhe x 1, x 2,…x m janë karakteristika të faktorëve (ndryshore të pavarura).

Vlerësimi i parametrave

Për regresionin e shumëfishtë (MR), ai kryhet duke përdorur metodën e katrorëve më të vegjël (OLS). Për ekuacionet lineare të formës Y = a + b 1 x 1 +…+b m x m + ε ne ndërtojmë një sistem ekuacionesh normale (shih më poshtë)

Për të kuptuar parimin e metodës, merrni parasysh një rast me dy faktorë. Pastaj kemi një situatë të përshkruar nga formula

Nga këtu marrim:

ku σ është varianca e tiparit përkatës të pasqyruar në indeks.

OLS është i zbatueshëm për ekuacionin MR në një shkallë të standardizuar. Në këtë rast marrim ekuacionin:

në të cilat t y, t x 1, ... t xm janë variabla të standardizuara, për të cilat vlerat mesatare janë të barabarta me 0; β i janë koeficientët e standardizuar të regresionit, dhe devijimi standard është 1.

Ju lutemi vini re se të gjitha β i në këtë rast specifikohen si të normalizuara dhe të centralizuara, prandaj krahasimi i tyre me njëri-tjetrin konsiderohet i saktë dhe i pranueshëm. Përveç kësaj, është e zakonshme që të ekzaminohen faktorët duke hedhur poshtë ata me vlerat më të ulëta βi.

Problem me përdorimin e ekuacionit të regresionit linear

Supozoni se kemi një tabelë të dinamikës së çmimeve për një produkt specifik N gjatë 8 muajve të fundit. Është e nevojshme të merret një vendim për këshillueshmërinë e blerjes së një grupi të tij me një çmim prej 1850 rubla/t.

	numri i muajit	emri i muajit	çmimi i produktit N
			1750 rubla për ton
			1755 rubla për ton
			1767 rubla për ton
			1760 rubla për ton
			1770 rubla për ton
			1790 rubla për ton
			1810 rubla për ton
			1840 rubla për ton

Për të zgjidhur këtë problem në procesorin e spreadsheet Excel, duhet të përdorni mjetin "Analiza e të dhënave", e njohur tashmë nga shembulli i paraqitur më sipër. Tjetra, zgjidhni seksionin "Regresioni" dhe vendosni parametrat. Duhet mbajtur mend se në fushën "Input intervali Y" duhet të futet një sërë vlerash për variablin e varur (në këtë rast, çmimet për mallrat në muaj të caktuar të vitit), dhe në "Inputin interval X" - për variablin e pavarur (numri i muajit). Konfirmoni veprimin duke klikuar "Ok". Në një fletë të re (nëse tregohet kështu) marrim të dhëna për regresion.

Duke i përdorur ato ndërtojmë një ekuacion linear të formës y=ax+b, ku parametrat a dhe b janë koeficientët e drejtëzës me emrin e numrit të muajit dhe koeficientët dhe drejtëzat “Y-prerja” nga fleta me rezultatet e analizës së regresionit. Kështu, ekuacioni i regresionit linear (LR) për detyrën 3 shkruhet si:

Çmimi i produktit N = 11.714* numri i muajit + 1727.54.

ose në shënimin algjebrik

y = 11,714 x + 1727,54

Analiza e rezultateve

Për të vendosur nëse ekuacioni i regresionit linear që rezulton është adekuat, përdoren koeficientët e korrelacionit të shumëfishtë (MCC) dhe përcaktimit, si dhe testi Fisher dhe testi Student t. Në tabelën e Excel-it me rezultatet e regresionit, ato quhen respektivisht R të shumëfishta, R-katrore, F-statistikë dhe t-statistika.

KMC R bën të mundur vlerësimin e afërsisë së marrëdhënies probabilistike midis variablave të pavarur dhe të varur. Vlera e tij e lartë tregon një lidhje mjaft të fortë midis variablave "Numri i muajit" dhe "Çmimi i produktit N në rubla për 1 ton". Megjithatë, natyra e kësaj marrëdhënieje mbetet e panjohur.

Katrori i koeficientit të përcaktimit R2 (RI) është karakteristikë numerike e proporcionit të shpërndarjes totale dhe tregon shpërndarjen e cilës pjesë të të dhënave eksperimentale, d.m.th. vlerat e ndryshores së varur korrespondojnë me ekuacionin e regresionit linear. Në problemin në shqyrtim, kjo vlerë është e barabartë me 84.8%, d.m.th., të dhënat statistikore përshkruhen me një shkallë të lartë saktësie nga SD që rezulton.

Statistikat F, të quajtura edhe testi i Fisher-it, përdoren për të vlerësuar rëndësinë e një marrëdhënieje lineare, duke hedhur poshtë ose konfirmuar hipotezën e ekzistencës së saj.

(Testi i studentit) ndihmon për të vlerësuar rëndësinë e koeficientit me një term të panjohur ose të lirë të marrëdhënies lineare. Nëse vlera e testit t > tcr, atëherë hipoteza për parëndësinë e termit të lirë të ekuacionit linear hidhet poshtë.

Në problemin në shqyrtim për termin e lirë, duke përdorur mjetet e Excel-it, u arrit që t = 169.20903, dhe p = 2.89E-12, d.m.th., kemi probabilitet zero që hipoteza e saktë për parëndësinë e termit të lirë të hidhet poshtë. . Për koeficientin për të panjohurën t=5,79405, dhe p=0,001158. Me fjalë të tjera, probabiliteti që hipoteza e saktë për parëndësinë e koeficientit për një të panjohur të hidhet poshtë është 0.12%.

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

Problemi i fizibilitetit të 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 aplikimi.

Menaxhmenti i kompanisë NNN duhet të vendosë për këshillueshmërinë e blerjes së 20% të aksioneve në MMM SHA. Kostoja e paketës (PS) është 70 milionë dollarë amerikanë. Specialistët e NNN kanë mbledhur të dhëna për transaksione të ngjashme. Është vendosur që vlera e bllokut të aksioneve të vlerësohet sipas parametrave 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 (COF).

Përveç kësaj, përdoret parametri i pagave të prapambetura të ndërmarrjes (V3 P) në mijëra dollarë amerikanë.

Zgjidhja duke përdorur procesorin e fletëllogaritjes Excel

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

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

Shënoni artikullin "Fleta e re e punës" dhe klikoni "Ok".

Merrni një analizë regresioni për një problem 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ë tabelën e 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 MMM SHA janë paraqitur në tabelë:

Duke i zëvendësuar ato në ekuacionin e regresionit, marrim një shifër prej 64.72 milion dollarë amerikanë. Kjo do të thotë se aksionet e MMM sh.a. nuk ia vlen të blihen, pasi vlera e tyre prej 70 milionë dollarësh është mjaft e fryrë.

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

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

Analiza e regresionit është një nga metodat më të njohura 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. Microsoft Excel ka mjete të dizajnuara për të kryer këtë lloj analize. Le të shohim 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, fillimisht 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 "Cilësimet".
3. Hapet dritarja e opsioneve të Excel. Shkoni te nënseksioni "Shtesa".
4. Në fund të dritares që hapet, zhvendoseni çelësin në bllokun "Menaxhimi" në pozicionin "Excel Add-ins", nëse është në një pozicion tjetër. Klikoni në butonin "Shko".
5. Hapet një dritare e shtesave të disponueshme të Excel. Kontrolloni kutinë pranë "Paketa e analizës". Klikoni në butonin "OK".

Tani, kur shkojmë te skeda "Të dhënat", në shiritin në bllokun 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;
• qetësues;
• logaritmike;
• eksponenciale;
• demonstrative;
• hiperbolike;
• regresionit linear.

Më vonë do të flasim për kryerjen 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 klientëve të dyqaneve për ditën përkatëse të punës. Le të zbulojmë duke përdorur analizën e regresionit saktësisht se si kushtet e motit në formën e temperaturës së ajrit mund të ndikojnë në frekuentimin e një ndërmarrje me pakicë.

Ekuacioni i përgjithshëm i regresionit linear është si vijon: Y = a0 + a1x1 +…+ akhk. Në këtë formulë, Y nënkupton variablin mbi të cilin po përpiqemi të studiojmë ndikimin e faktorëve. Në rastin tonë, ky është numri i blerësve. Vlera e x është faktorë të ndryshëm që ndikojnë në ndryshore. Parametrat a janë koeficientët e regresionit. Domethënë, janë ata që përcaktojnë rëndësinë e një faktori të caktuar. 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 koeficient ë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 rreshtit "Kryqëzimi Y" dhe kolonës "Koeficientët". Kjo 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ë, ky është niveli i varësisë së numrit të klientëve të dyqanit nga temperatura. Një koeficient prej 1.31 konsiderohet një tregues mjaft i lartë i ndikimit.

Siç mund ta shihni, duke përdorur Microsoft Excel është mjaft e lehtë të krijoni një tabelë të analizës së regresionit. Por vetëm një person i trajnuar mund të punojë me të dhënat dalëse 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 përshkruar thelbin e problemit në detaje. Specialistët tanë do të përpiqen të përgjigjen sa më shpejt që të jetë e mundur.

A ju ndihmoi ky artikull?

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

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

x - ndryshore e pavarur,

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

b është pjerrësia e vijës së drejtë.

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

Figura e mësipërme tregon vijën e përshkruar me ekuacionin ŷ =2+0.5x. Prerja y është pika në të cilën vija pret 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ë që vija ngrihet nga e majta në të djathtë. Nëse b = 0, vija është horizontale, që do të thotë se nuk ka lidhje midis variablave të varur dhe të pavarur. Me fjalë të tjera, ndryshimi i vlerës së x nuk ndikon në vlerën e y.

ŷ dhe y shpesh ngatërrohen. Grafiku tregon 6 çifte pikash të renditura dhe një vijë, sipas ekuacionit të dhënë

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 vërtetojmë me ekuacionin e mëposhtëm:

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

Vlera y përfaqëson pikën aktuale dhe vlera ŷ është vlera e pritur e y duke përdorur një ekuacion linear për një vlerë të dhënë të x.

Hapi tjetër është përcaktimi i ekuacionit linear që përputhet më mirë 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 OpsioneExcel shkoni te skeda Shtesa. Në fushë Kontrolli zgjidhni ShtesaExcel dhe klikoni Shkoni. Në dritaren që shfaqet, kontrolloni kutinë pranë Paketa e analizës, klikoni 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 një artikull i mëparshëm, ku një djalë dhe një vajzë ndajnë një tavolinë në banjë. Futni të dhënat nga shembulli i vaskës sonë 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 në figurë dhe klikoni OK.

Vendosni parametrat e nevojshëm 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ë duke bërë llogaritjet tona 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 të veçantë nga një ose më shumë variabla të pavarur. Në epokën para kompjuterit, përdorimi i tij ishte mjaft i vështirë, veçanërisht kur bëhej fjalë për vëllime të mëdha të dhënash. Sot, pasi keni mësuar se si të ndërtoni 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ë ky koncept u fut në matematikë nga Francis Galton në 1886. Regresioni ndodh:

• lineare;
• parabolike;
• qetësues;
• eksponenciale;
• hiperbolike;
• demonstrative;
• logaritmike.

Shembulli 1

Le të shqyrtojmë problemin e përcaktimit të varësisë së numrit të anëtarëve të ekipit që largohen nga paga mesatare në 6 ndërmarrje industriale.

Detyrë. Në gjashtë ndërmarrje u analizua paga mesatare mujore dhe numri i të punësuarve që largohen vullnetarisht. Në formë tabelare kemi:

Për detyrën e përcaktimit të varësisë së numrit të punëtorëve që largohen nga paga mesatare në 6 ndërmarrje, modeli i regresionit ka formën e ekuacionit Y = a0 + a1×1 +…+аkxk, ku хi janë variablat 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 Excel

Analiza e regresionit në Excel duhet të paraprihet duke aplikuar funksione të integruara në të dhënat ekzistuese tabelare. Sidoqoftë, për këto qëllime është më mirë të përdorni shtesën shumë të dobishme "Analysis Pack". Për ta aktivizuar ju duhet:

• nga skeda "File" shkoni te seksioni "Opsionet";
• në dritaren që hapet, zgjidhni rreshtin "Shtesa";
• klikoni në butonin "Shko" që ndodhet më poshtë, në të djathtë të rreshtit "Menaxhimi";
• kontrolloni kutinë pranë emrit "Paketa e analizës" dhe konfirmoni veprimet tuaja duke klikuar "Ok".

Nëse gjithçka është bërë si duhet, butoni i kërkuar do të shfaqet në anën e djathtë të skedës "Të dhënat", 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 të kryer llogaritjet 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, futni gamën e vlerave për Y (numri i punonjësve që largohen nga puna) dhe për X (pagat e tyre);
• Ne konfirmojmë veprimet tona duke shtypur butonin "Ok".

Si rezultat, programi do të mbushë automatikisht një tabelë të re me të dhëna të analizës së regresionit. Shënim! Excel ju lejon të vendosni manualisht vendndodhjen që preferoni për këtë qëllim. Për shembull, kjo mund të jetë e njëjta fletë ku ndodhen vlerat Y dhe X, ose edhe një libër i ri pune i krijuar posaçërisht për të ruajtur të dhëna të tilla.

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

Në Excel, të dhënat e marra gjatë përpunimit të të dhënave në shembullin në shqyrtim kanë formën:

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%), d.m.th., parametrat e llogaritur të modelit shpjegojnë lidhjen midis parametrave në shqyrtim me 75.5%. Sa më e lartë të jetë vlera e koeficientit të përcaktimit, aq më i përshtatshëm është modeli i përzgjedhur për një detyrë specifike. Konsiderohet se përshkruan saktë situatën reale kur vlera e katrorit R është mbi 0.8. Nëse R-katrori është tcr, atëherë hipoteza për parëndësinë e termit të lirë të ekuacionit linear hidhet poshtë.

Në problemin në shqyrtim për termin e lirë, duke përdorur mjetet e Excel-it, u arrit që t = 169.20903, dhe p = 2.89E-12, d.m.th., kemi probabilitet zero që hipoteza e saktë për parëndësinë e termit të lirë të hidhet poshtë. . Për koeficientin për të panjohurën t=5,79405, dhe p=0,001158. Me fjalë të tjera, probabiliteti që hipoteza e saktë për parëndësinë e koeficientit për një të panjohur të hidhet poshtë është 0.12%.

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

Problemi i fizibilitetit të 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 aplikimi.

Menaxhmenti i kompanisë NNN duhet të vendosë për këshillueshmërinë e blerjes së 20% të aksioneve në MMM SHA. Kostoja e paketës (PS) është 70 milionë dollarë amerikanë. Specialistët e NNN kanë mbledhur të dhëna për transaksione të ngjashme. Është vendosur që vlera e bllokut të aksioneve të vlerësohet sipas parametrave 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 (COF).

Përveç kësaj, përdoret parametri i pagave të prapambetura të ndërmarrjes (V3 P) në mijëra dollarë amerikanë.

Zgjidhja duke përdorur procesorin e fletëllogaritjes Excel

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

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

Shënoni artikullin "Fleta e re e punës" dhe klikoni "Ok".

Merrni një analizë regresioni për një problem 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ë tabelën e 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 MMM SHA janë paraqitur në tabelë:

Duke i zëvendësuar ato në ekuacionin e regresionit, marrim një shifër prej 64.72 milion dollarë amerikanë. Kjo do të thotë se aksionet e MMM sh.a. nuk ia vlen të blihen, pasi vlera e tyre prej 70 milionë dollarësh është mjaft e fryrë.

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

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

Kjo është mënyra më e zakonshme për të treguar varësinë e disa variablave nga të tjerët, për shembull, si funksionon Niveli i PBB-së nga madhësia investimet e huaja ose nga Norma e kreditimit të Bankës Kombëtare ose nga çmimet për burimet kryesore të energjisë.

Modelimi ju lejon të tregoni madhësinë e kësaj varësie (koeficientët), falë të cilave mund të bëni një parashikim të drejtpërdrejtë dhe të kryeni një lloj planifikimi bazuar në këto parashikime. Gjithashtu, bazuar në analizën e regresionit, është e mundur të merren vendime menaxheriale që synojnë stimulimin e shkaqeve prioritare që ndikojnë në rezultatin përfundimtar; vetë modeli do të ndihmojë në identifikimin e këtyre faktorëve prioritare.

Pamje e përgjithshme e modelit të regresionit linear:

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

Ku a - parametrat e regresionit (koeficientët), x - faktorët ndikues, k - numri i faktorëve të modelit.

Të dhënat fillestare

Ndër të dhënat fillestare, ne kemi nevojë për një grup të caktuar të dhënash që do të përfaqësojnë disa vlera të njëpasnjëshme ose të ndërlidhura të parametrit përfundimtar Y (për shembull, GDP) dhe të njëjtin numër vlerash të treguesve ndikimin e të cilëve po studiojmë ( për shembull, investimet e huaja).

Figura e mësipërme tregon një tabelë me të njëjtat të dhëna fillestare, Y është një tregues i popullsisë ekonomikisht aktive, dhe numri i ndërmarrjeve, sasia e investimeve në kapital dhe të ardhurat e familjes janë faktorë ndikues, domethënë X-të.

Bazuar në figurë, mund të bëhet gjithashtu një përfundim i gabuar se modelimi mund të bëhet vetëm për seritë kohore, domethënë seritë momentale të regjistruara në mënyrë sekuenciale në kohë, por nuk është kështu; me të njëjtin sukses, mund të modelohet për nga struktura. , për shembull, vlerat e treguara në tabelë mund të ndahen jo sipas vitit, por sipas rajonit.

Për të ndërtuar modele lineare adekuate, është e dëshirueshme që të dhënat e burimit të mos kenë rënie ose kolapse të forta; në raste të tilla, këshillohet të kryhet zbutja, por për zbutjen do të flasim herën tjetër.

Paketa e analizës

Parametrat e një modeli të regresionit linear mund të llogariten gjithashtu manualisht duke përdorur metodën e zakonshme të katrorëve më të vegjël (OLS), por kjo kërkon mjaft kohë. Kjo mund të llogaritet pak më shpejt duke përdorur të njëjtën metodë duke përdorur formulat në Excel, ku vetë programi do të bëjë llogaritjet, por përsëri do të duhet t'i futni formulat me dorë.

Excel ka një shtesë Paketa e analizës, i cili është një mjet mjaft i fuqishëm për të ndihmuar analistin. Kjo paketë veglash, ndër të tjera, mund të llogarisë parametrat e regresionit duke përdorur të njëjtën metodë të katrorëve më të vegjël, në vetëm disa klikime.Në fakt, mënyra e përdorimit të këtij mjeti do të diskutohet më tej.

Aktivizoni Paketën e Analizës

Si parazgjedhje, kjo shtesë është e çaktivizuar dhe nuk do ta gjeni në menynë e skedave, kështu që ne do të hedhim një vështrim hap pas hapi se si ta aktivizojmë atë.

Në Excel, në krye të majtë, aktivizoni skedën Skedari, në menynë që hapet, kërkoni artikullin Opsione dhe klikoni mbi të.

Në dritaren që hapet, në të majtë, kërkoni artikullin Shtesa dhe aktivizojeni, në këtë skedë në fund do të ketë një listë kontrolli rënëse, ku si parazgjedhje do të shkruhet Shtesat në Excel, do të ketë një buton në të djathtë të listës rënëse Shkoni, duhet të klikoni mbi të.

Një dritare kërcyese do t'ju kërkojë të zgjidhni shtesat e disponueshme; në të duhet të kontrolloni kutinë Paketa e analizës dhe në të njëjtën kohë, për çdo rast, Gjetja e një zgjidhjeje(gjithashtu një gjë e dobishme), dhe më pas konfirmoni zgjedhjen tuaj duke klikuar në butonin Ne rregull.

Udhëzime për gjetjen e parametrave të regresionit linear duke përdorur Paketën e Analizës

Pas aktivizimit të shtesës së Paketës së Analizës, ajo do të jetë gjithmonë e disponueshme në skedën e menysë kryesore Të dhënat nën lidhjen Analiza e të dhënave

Në dritaren e mjetit aktiv Analiza e të dhënave nga lista e mundësive që kërkojmë dhe zgjedhim Regresioni

Më pas, do të hapet një dritare për konfigurimin dhe zgjedhjen e të dhënave burimore për llogaritjen e parametrave të modelit të regresionit. Këtu ju duhet të tregoni intervalet e të dhënave fillestare, përkatësisht parametrin që përshkruhet (Y) dhe faktorët që ndikojnë në të (X), siç tregohet në figurën më poshtë; parametrat e mbetur, në parim, janë opsionale për t'u konfiguruar.

Pasi të keni zgjedhur të dhënat e burimit dhe të klikoni butonin OK, Excel prodhon llogaritjet në një fletë të re të librit aktiv të punës (përveç nëse është vendosur ndryshe në cilësimet), këto llogaritje duken kështu:

Qelizat kryesore janë të mbushura me të verdhë; këto janë ato që duhet t'i kushtoni vëmendje para së gjithash; parametrat e tjerë të rëndësishëm janë gjithashtu të rëndësishëm, por analiza e tyre e detajuar ndoshta kërkon një postim të veçantë.

Kështu që, 0,865 - Kjo R 2- koeficienti i përcaktimit, që tregon se 86.5% e parametrave të llogaritur të modelit, pra vetë modeli, shpjegojnë varësinë dhe ndryshimet në parametrin që studiohet - Y nga faktorët e studiuar - X-të. Nëse ekzagjerohet, atëherë ky është një tregues i cilësisë së modelit dhe sa më i lartë të jetë, aq më mirë. Është e qartë se nuk mund të jetë më shumë se 1 dhe konsiderohet e mirë kur R 2 është mbi 0.8, dhe nëse është më pak se 0.5, atëherë arsyeshmëria e një modeli të tillë mund të vihet në dyshim.

Tani le të kalojmë tek koeficientët e modelit:
2079,85 - Kjo a 0- një koeficient që tregon se cili do të jetë Y nëse të gjithë faktorët e përdorur në model janë të barabartë me 0, kuptohet që kjo është një varësi nga faktorë të tjerë që nuk përshkruhen në model;
-0,0056 - a 1- një koeficient që tregon peshën e ndikimit të faktorit x 1 në Y, domethënë, numri i ndërmarrjeve brenda një modeli të caktuar ndikon në treguesin e popullsisë ekonomikisht aktive me një peshë prej vetëm -0,0056 (një shkallë mjaft e vogël ndikimi ). Shenja minus tregon se ky ndikim është negativ, pra sa më shumë ndërmarrje, aq më pak popullsi ekonomikisht aktive, sado paradoksale të jetë kjo në kuptim;
-0,0026 - a 2- koeficienti i ndikimit të vëllimit të investimeve në kapital në madhësinë e popullsisë ekonomikisht aktive; sipas modelit edhe ky ndikim është negativ;
0,0028 - a 3- Koeficienti i ndikimit të të ardhurave të popullsisë në madhësinë e popullsisë ekonomikisht aktive, këtu ndikimi është pozitiv, domethënë, sipas modelit, rritja e të ardhurave do të kontribuojë në një rritje të madhësisë së popullsisë ekonomikisht aktive.

Le të mbledhim koeficientët e llogaritur në model:

Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3

Në fakt, ky është një model regresioni linear, i cili për të dhënat fillestare të përdorura në shembull duket saktësisht kështu.

Vlerësimet dhe parashikimet e modelit

Siç e kemi diskutuar më lart, modeli është ndërtuar jo vetëm për të treguar madhësinë e varësisë së parametrit që studiohet nga faktorët ndikues, por edhe në mënyrë që, duke njohur këta faktorë ndikues, të jetë e mundur të bëhet një parashikim. Bërja e këtij parashikimi është mjaft e thjeshtë; ju vetëm duhet të zëvendësoni vlerat e faktorëve ndikues në vend të X-ve përkatëse në ekuacionin e modelit që rezulton. Në figurën më poshtë, këto llogaritje janë bërë në Excel në një kolonë të veçantë.

Vlerat aktuale (ato që kanë ndodhur në realitet) dhe vlerat e llogaritura sipas modelit në të njëjtën figurë shfaqen në formën e grafikëve për të treguar ndryshimin, dhe për rrjedhojë gabimin e modelit.

E përsëris edhe një herë, për të bërë një parashikim duke përdorur një model, është e nevojshme që të njihen faktorë ndikues, dhe nëse flasim për një seri kohore dhe, në përputhje me rrethanat, një parashikim për të ardhmen, për shembull, për të ardhmen. vit apo muaj, atëherë nuk është gjithmonë e mundur të zbulohet se cilët do të jenë faktorët ndikues në këtë të ardhme. Në raste të tilla, është gjithashtu e nevojshme të bëhet një parashikim për faktorët ndikues; më shpesh kjo bëhet duke përdorur një model autoregresiv - një model në të cilin faktorët ndikues janë objekti në studim dhe koha, domethënë varësia e treguesit. është modeluar mbi atë që ishte në të kaluarën.

Ne do të shikojmë se si të ndërtojmë një model autoregresiv në artikullin vijues, por tani le të supozojmë se ne e dimë se cilat do të jenë vlerat e faktorëve ndikues në periudhën e ardhshme (në shembullin, 2008) dhe duke zëvendësuar këto vlera në llogaritjet do të marrim parashikimin tonë për vitin 2008.