Përcaktoni koeficientin e përcaktimit të këtij ekuacioni në excel. Si të bëni një ekuacion regresioni në excel

Paketa MS Excel ju lejon të bëni shumicën e punës shumë shpejt kur ndërtoni një ekuacion linear regresioni. Është e rëndësishme të kuptohet se si të interpretohen rezultatet.

Kërkon një shtesë për të punuar Paketa e analizës, i cili duhet të aktivizohet në artikullin e menysë Shërbimi\Shtesa

Në Excel 2007, për të aktivizuar Paketën e Analizës, klikoni Shko te Blloko Opsionet e Excel, duke klikuar butonin në këndin e sipërm të majtë dhe më pas " Opsionet e Excel» në fund të dritares:

Për të ndërtuar një model regresioni, zgjidhni artikullin Shërbimi\Analiza e të Dhënave\Regresioni. (Në Excel 2007, kjo mënyrë është në Të dhënat/Analiza e të dhënave/Regresioni). Do të shfaqet një kuti dialogu që duhet të plotësohet:

1) Intervali i hyrjes Y¾ përmban një lidhje me qelizat që përmbajnë vlerat e atributit që rezulton y. Vlerat duhet të jenë në një kolonë;

2) Intervali i hyrjes X¾ përmban një lidhje me qelizat që përmbajnë vlerat e faktorëve. Vlerat duhet të jenë në kolona;

3) Nënshkruani Etiketat vendos nëse qelizat e para përmbajnë tekst shpjegues (etiketat e të dhënave);

4) Niveli i besueshmërisë¾ është niveli i besimit, i cili supozohet të jetë 95% si parazgjedhje. Nëse kjo vlerë nuk ju përshtatet, atëherë duhet ta aktivizoni këtë veçori dhe të vendosni vlerën e kërkuar;

5) Nënshkruani Zero konstante përfshihet nëse është e nevojshme të ndërtohet një ekuacion në të cilin ndryshorja e lirë ;

6) Opsionet e daljes përcaktoni se ku duhet të vendosen rezultatet. Modaliteti i parazgjedhur i ndërtimit Fletë pune e re;

7) Blloko Mbetet ju lejon të përfshini daljen e mbetjeve dhe ndërtimin e grafikëve të tyre.

Si rezultat, shfaqet informacioni që përmban të gjithë informacionin e nevojshëm dhe grupohet në tre blloqe: Statistikat e regresionit, Analiza e variancës, Tërheqja e bilancit. Le t'i shqyrtojmë ato në mënyrë më të detajuar.

1. Statistikat e regresionit:

të shumëfishta R përcaktohet me formulën ( Koeficienti i korrelacionit Pearson);

R (koeficienti i përcaktimit);

Normalizuar R-katrori llogaritet me formulë (përdoret për regresion të shumëfishtë);

gabim standard S llogaritur me formulë ;

Vëzhgimet ¾ është sasia e të dhënave n.

2. Analiza e variancës, linjë Regresioni:

Parametri df barazohet m(numri i grupeve të faktorëve x);

Parametri SS përcaktohet nga formula;

Parametri ZNJ përcaktohet nga formula;

Statistikat F përcaktohet nga formula;

Rëndësia F. Nëse numri që rezulton tejkalon , atëherë hipoteza pranohet (nuk ka lidhje lineare), përndryshe hipoteza pranohet (ka një marrëdhënie lineare).

3. Analiza e variancës, linjë Pjesa e mbetur:

Parametri df barazohet ;

Parametri SS përcaktohet nga formula ;

Parametri ZNJ përcaktohet nga formula.

4. Analiza e variancës, linjë Total përmban shumën e dy kolonave të para.

5. Analiza e variancës, linjë Kryqëzimi Y përmban vlerën e koeficientit , gabim standard dhe t-statistikat.

P-vlera ¾ është vlera e niveleve të rëndësisë që korrespondojnë me të llogaritura t- statisticienët. Përcaktuar nga STUDISTI( t-statistikat; ). Nese nje P-vlera tejkalon, atëherë ndryshorja përkatëse është statistikisht e parëndësishme dhe mund të përjashtohet nga modeli.

fundi 95% dhe 95% e lartë¾ janë kufijtë e poshtëm dhe të sipërm të intervaleve 95 përqind të besimit për koeficientët e ekuacionit teorik të regresionit linear. Nëse në bllokun e hyrjes së të dhënave vlera e probabilitetit të besueshmërisë është lënë si parazgjedhje, atëherë dy kolonat e fundit do të kopjojnë ato të mëparshmet. Nëse përdoruesi ka futur një vlerë të personalizuar të besimit, atëherë dy kolonat e fundit përmbajnë vlerat e kufirit të poshtëm dhe të sipërm për nivelin e specifikuar të besimit.

6. Analiza e variancës, rreshtat përmbajnë vlerat e koeficientëve, gabimet standarde, t- statisticien, P-vlerat dhe intervalet e besimit për ato përkatëse.

7. Blloko Tërheqja e bilancit përmban vlerat e parashikuara y(në shënimin tonë është ) dhe mbetjet .

Kjo është mënyra më e zakonshme për të treguar varësinë e disa ndryshoreve nga të tjerët, për shembull, si Niveli i PBB-së nga vlera investimet e huaja ose nga norma e kredisë së Bankës Popullore 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 ju mund të bëni drejtpërdrejt një parashikim 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 dhe vetë modeli do të ndihmojë në nxjerrjen në pah të këtyre faktorëve prioritarë.

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

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

ku a - parametrat (koeficientët) e regresionit, x - faktorët ndikues, k është 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ërbëhej nga 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, ndikimi i 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 investimit në kapital dhe të ardhurat e popullsisë janë faktorë ndikues, domethënë x-të.

Nga figura mund të nxirret gjithashtu një përfundim i gabuar se në modelim mund të flasim vetëm për seri dinamike, pra për seri momentesh të fiksuara në mënyrë sekuenciale në kohë, por kjo nuk është kështu, me të njëjtin sukses mund të modelohet në konteksti i strukturës, për shembull, vlerat e specifikuara në tabelë mund të ndahen jo sipas viteve, por sipas rajoneve.

Për të ndërtuar modele lineare adekuate, është e dëshirueshme që të dhënat fillestare të mos kenë rënie ose kolapse të forta, në raste të tilla është e dëshirueshme 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 katrorëve më të vegjël (OLS), por kjo kërkon mjaft kohë. Pak më shpejt, kjo mund të llogaritet duke përdorur të njëjtën metodë duke përdorur formulat në Excel, ku programi do të bëjë vetë llogaritjet, por ju ende duhet t'i vendosni 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, është në gjendje të llogarisë parametrat e regresionit, sipas të njëjtës OLS, në vetëm disa klikime, në fakt do të diskutohet më tej se si të përdoret ky mjet.

Aktivizimi i Paketës së Analizës

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

Në Excel, lart majtas, 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ë rënëse të kontrolleve, ku si parazgjedhje do të shkruhet Shtesat në Excel, në të djathtë të listës rënëse do të ketë një buton 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ë pranë 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 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, kërkoni dhe zgjidhni Regresioni

Më pas do të hapet një dritare për konfigurimin dhe zgjedhjen e të dhënave fillestare për llogaritjen e parametrave të modelit të regresionit. Këtu duhet të specifikoni intervalet e të dhënave fillestare, përkatësisht parametrin e përshkruar (Y) dhe faktorët që ndikojnë në të (X), pasi në figurën më poshtë, parametrat e mbetur, në parim, janë opsionale për vendosje.

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

I mbusha qelizat kryesore me të verdhë, janë ato që duhet t'i kushtoni vëmendje para së gjithash, parametrat e tjerë janë gjithashtu të rëndësishëm, por analiza e tyre e hollësishme kërkon ndoshta një postim të veçantë.

Kështu që, 0,865 - kjo është R2- 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 e studiuar - Y nga faktorët e studiuar - X-të. Nëse ekzagjerohet, atëherë ë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 është 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 këtij modeli 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ë 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, ky ndikim është gjithashtu 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ë rritjen e madhësisë së popullsisë ekonomikisht aktive.

Ne 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 duket pikërisht kështu për të dhënat fillestare të përdorura në shembull.

Modeli i vlerave të vlerësuara dhe parashikimi

Siç e diskutuam më lart, modeli është ndërtuar jo vetëm për të treguar madhësinë e varësisë së parametrit të studiuar 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. Për ta bërë këtë parashikim është mjaft i thjeshtë, thjesht duhet të zëvendësoni vlerat e faktorëve ndikues në vendin e x-ve përkatës 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ë ndodhën në realitet) dhe vlerat e llogaritura për modelin në të njëjtën figurë shfaqen në formën e grafikëve për të treguar ndryshimin, dhe si rrjedhim gabimin e modelit.

E përsëris edhe një herë, për të bërë një parashikim mbi modelin, ë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ë dihet 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ë së shpeshti 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 nga ajo që ishte në të kaluarën është modeluar.

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

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. Funksionaliteti i Microsoft Excel ka mjete të krijuara për të kryer 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ë bëni 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 "Cilësimet".
3. Hapet dritarja e opsioneve të Excel. Shkoni te nënseksioni "Shtesa".
4. Në pjesën e poshtme të dritares që hapet, ne e riorganizojmë çelësin në bllokun "Management" në pozicionin "Excel Add-ins", nëse është në një pozicion tjetër. Klikoni në butonin "Shko".
5. Hapet dritarja e shtesave në Excel. Kontrolloni kutinë pranë "Paketës së Analizës". Klikoni në butonin "OK".

Tani, kur shkojmë në skedën "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;
• fuqia;
• logaritmike;
• eksponenciale;
• demonstrim;
• hiperbolike;
• regresionit linear.

Për zbatimin e llojit të fundit të analizës së regresionit në Excel do të flasim më në detaje më vonë.

Regresioni linear në Excel

Më poshtë, si shembull, është një tabelë që tregon temperaturën mesatare ditore të ajrit në rrugë dhe numrin e klientëve 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 një ndërmarrje me pakicë.

Ekuacioni i përgjithshëm i regresionit linear duket kështu: Y = a0 + a1x1 + ... + axk. 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. Kjo do të thotë, ata përcaktojnë rëndësinë e një faktori të veçantë. 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ë. Një marrëdhënie 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 linjës "Kryqëzimi Y" dhe kolonës "Koeficientët". Këtu tregohet 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 kolonës "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, ë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ë lumtur që mundëm t'ju ndihmojmë në zgjidhjen e problemit.

Bëni pyetjen tuaj në komente, duke përshkruar në detaje 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?

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

ŷ është 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ë.

Në figurën më poshtë, ky koncept është paraqitur grafikisht:

Figura e mësipërme tregon një vijë të 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 rritjes së vijës me gjatësinë e vijës, ka një vlerë prej 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 ndërmjet 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 ë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ë 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ë dialogun që shfaqet Opsioneshkëlqejnë shkoni te skeda Shtesa. Në fushë Kontrolli zgjidhni shtesashkëlqejnë dhe klikoni Shkoni. Në dritaren që shfaqet, kontrolloni kutinë pranë paketa e analizës, klikoni NE RREGULL.

Në skedën Të dhënat në një 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ë banjës në kolonat A dhe B të një flete bosh.

Shkoni te skeda Të dhënat, në një 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 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 llogaritjet e pavarura 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, përdorimi 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 prezantua në matematikë nga Francis Galton në 1886. Regresioni ndodh:

• lineare;
• parabolike;
• fuqia;
• eksponenciale;
• hiperbolike;
• demonstrative;
• logaritmike.

Shembulli 1

Merrni parasysh problemin e përcaktimit të varësisë së numrit të anëtarëve të ekipit në pension nga paga mesatare në 6 ndërmarrje industriale.

Një detyrë. Në gjashtë ndërmarrje kemi analizuar pagën mesatare mujore dhe numrin e punonjësve që janë larguar me vullnetin e tyre. Në formë tabelare kemi:

Për problemin e përcaktimit të varësisë së numrit të punëtorëve të pushuar nga paga mesatare në 6 ndërmarrje, modeli i regresionit ka formën e ekuacionit Y = a0 + a1 × 1 + ... + akxk, 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ë janë larguar dhe faktori ndikues është paga, të cilën e shënojmë me X.

Përdorimi i aftësive të tabelës "Excel"

Analiza e regresionit në Excel duhet të paraprihet nga aplikimi i funksioneve të integruara në të dhënat tabelare të disponueshme. Megjithatë, për këto qëllime, është më mirë të përdorni shtesën shumë të dobishme "Analysis Toolkit". 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 në fund, 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ë në mënyrë korrekte, butoni i dëshiruar 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ë dorë 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 një fletë të re të tabelës me të dhëna të analizës së regresionit. Shënim! Excel ka aftësinë për të vendosur manualisht vendndodhjen që preferoni për këtë qëllim. Për shembull, mund të jetë e njëjta fletë ku janë 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 të shembullit të konsideruar duken kështu:

Para së gjithash, duhet t'i kushtoni vëmendje vlerës së katrorit R. Është koeficienti i përcaktimit. Në këtë shembull, R-katror = 0,755 (75,5%), d.m.th., 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ë i zbatueshëm është modeli i zgjedhur për një detyrë të caktuar. Besohet se përshkruan saktë situatën reale me një vlerë R-katrore 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 në shqyrtim për anëtarin e lirë, duke përdorur mjetet e Excel-it, u përftua se t = 169.20903, dhe p = 2.89E-12, pra kemi një probabilitet zero që hipoteza e saktë për parëndësinë e anëtarit të lirë 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 për 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. Konsideroni një problem specifik të aplikuar.

Menaxhmenti i NNN duhet të marrë një vendim për këshillueshmërinë e blerjes së 20% të aksioneve në MMM SA. Kostoja e paketës (JV) është 70 milionë dollarë amerikanë. Specialistët e NNN mblodhën 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);
• qarkullim vjetor (VO);
• llogaritë e arkëtueshme (VD);
• kostoja e aseteve fikse (SOF).

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 spreadsheet 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";
• në kutinë "Intervali i hyrjes Y" shkruani 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 zgjidhni gamën e të gjitha vlerave nga kolonat B, C, D, F në fletë.

Zgjidhni "Fletën e re të punës" dhe klikoni "Ok".

Merrni analizën e regresionit për problemin e dhënë.

Ekzaminimi i rezultateve dhe përfundimeve

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

SP \u003d 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 në ekuacionin e regresionit, ata marrin një shifër prej 64.72 milion dollarë amerikanë. Kjo do të thotë se aksionet e sh.a. 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 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 në Excel të diskutuar më sipër do t'ju ndihmojnë të zgjidhni problemet praktike nga fusha e ekonometrisë.

Paketa MS Excel ju lejon të bëni shumicën e punës shumë shpejt kur ndërtoni një ekuacion linear regresioni. Është e rëndësishme të kuptohet se si të interpretohen rezultatet. Për të ndërtuar një model regresioni, zgjidhni Tools\Data Analysis\Regresion (në Excel 2007, ky modalitet ndodhet në seksionin Data/Data Analysis/Regresion). Pastaj kopjoni rezultatet e marra në një bllok për analizë.

Të dhënat fillestare:

Rezultatet e analizës

Përfshi në raport
Llogaritja e parametrave të ekuacionit të regresionit
Materiali teorik
Ekuacioni i regresionit në një shkallë standarde
Koeficienti i korrelacionit të shumëfishtë (Indeksi i korrelacionit të shumëfishtë)
Koeficientët e pjesshëm të elasticitetit
Vlerësimi krahasues i ndikimit të faktorëve të analizuar në tiparin efektiv (d - koeficientët e përcaktimit të veçantë)

Kontrollimi i cilësisë së ekuacionit të regresionit të ndërtuar
Rëndësia e koeficientëve të regresionit b i (statistika t. T-testi i studentit)
Rëndësia e ekuacionit në tërësi (F-statistika. Kriteri i Fisher-it). Koeficienti i përcaktimit
Kriteret e pjesshme F

Niveli i rëndësisë 0.005 0.01 0.025 0.05 0.1 0.25 0.4

Analiza e regresionit dhe korrelacionit - metodat e hulumtimit statistikor. Këto janë mënyrat më të zakonshme për të treguar varësinë e një parametri nga një ose më shumë variabla të pavarur.

Më poshtë, duke përdorur shembuj konkretë praktikë, do të shqyrtojmë këto dy analiza shumë të njohura në mesin e ekonomistëve. Ne gjithashtu do të japim një shembull të marrjes së rezultateve kur ato kombinohen.

Analiza e regresionit në Excel

Tregon ndikimin 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, pagat 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ë, për të parashikuar, planifikuar zhvillimin e fushave prioritare, për të marrë vendime menaxheriale.

Regresioni ndodh:

• lineare (y = a + bx);
• parabolike (y = a + bx + cx 2);
• eksponencial (y = a * exp(bx));
• fuqia (y = a*x^b);
• hiperbolike (y = b/x + a);
• logaritmike (y = b * 1n(x) + a);
• eksponencial (y = a * b^x).

Konsideroni shembullin e ndërtimit të një modeli regresioni në Excel dhe interpretimin e rezultateve. Le të marrim një tip linear të regresionit.

Një detyrë. Në 6 ndërmarrje u analizua paga mesatare mujore dhe numri i punonjësve të larguar. Është e nevojshme të përcaktohet varësia e numrit të punonjësve në pension nga paga mesatare.

Modeli i regresionit linear ka formën e mëposhtme:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

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

Në shembullin tonë, Y është treguesi i largimit të punëtorëve. Faktori ndikues është paga (x).

Excel ka funksione të integruara që mund të përdoren për të llogaritur parametrat e një modeli të regresionit linear. Por shtesa Analysis ToolPak do ta bëjë atë më shpejt.

Aktivizoni një mjet të fuqishëm analitik:

Pasi të aktivizohet, shtesa do të jetë e disponueshme nën skedën Të dhëna.

Tani do të merremi drejtpërdrejt me analizën e regresionit.

Para së gjithash, ne i kushtojmë vëmendje katrorit R dhe koeficientëve.

R-katrori është koeficienti i përcaktimit. Në shembullin tonë, është 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. Dobët - 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ë gjithë variablat në modelin në shqyrtim janë të barabartë me 0. Domethënë, 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 të larguarve 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 doli. E cila është e drejtë.



Analiza e korrelacionit në Excel

Analiza e korrelacionit ndihmon për të përcaktuar nëse ka një lidhje midis treguesve në një ose dy mostra. Për shembull, midis kohës së funksionimit të makinës dhe kostos së riparimeve, çmimit të pajisjeve dhe kohëzgjatjes së funksionimit, lartësisë dhe peshës së fëmijëve, etj.

Nëse ka një marrëdhënie, atëherë nëse një rritje në një parametër çon në një rritje (korrelacion pozitiv) ose një ulje (negative) në tjetrin. Analiza e korrelacionit e ndihmon analistin të përcaktojë nëse vlera e një treguesi mund të parashikojë vlerën e mundshme të një tjetri.

Koeficienti i korrelacionit shënohet r. Ndryshon nga +1 në -1. Klasifikimi i korrelacioneve për fusha të ndryshme do të jetë i ndryshëm. Kur vlera e koeficientit është 0, nuk ka lidhje lineare midis mostrave.

Konsideroni se si të përdorni Excel për të gjetur koeficientin e korrelacionit.

Funksioni CORREL përdoret për të gjetur koeficientët e çiftuar.

Detyra: Përcaktoni nëse ka një lidhje midis kohës së funksionimit të një torno dhe kostos së mirëmbajtjes së saj.

Vendosni kursorin në çdo qelizë dhe shtypni butonin fx.

1. Në kategorinë "Statistikore", zgjidhni funksionin CORREL.
2. Argumenti "Array 1" - diapazoni i parë i vlerave - koha e makinës: A2: A14.
3. Argumenti "Array 2" - diapazoni i dytë i vlerave - kostoja e riparimeve: B2:B14. Klikoni OK.

Për të përcaktuar llojin e lidhjes, duhet të shikoni numrin absolut të koeficientit (çdo fushë e veprimtarisë ka shkallën e vet).

Për analizën e korrelacionit të disa parametrave (më shumë se 2), është më i përshtatshëm të përdoret "Analiza e të dhënave" (shtesa "Paketa e Analizës"). Në listë, ju duhet të zgjidhni një korrelacion dhe të caktoni një grup. Të gjitha.

Koeficientët rezultues do të shfaqen në matricën e korrelacionit. Si ky:

Analiza e korrelacionit-regresionit

Në praktikë, këto dy teknika shpesh përdoren së bashku.

Shembull:

Tani të dhënat e analizës së regresionit janë të dukshme.