LSM për një funksion të dy ndryshoreve. Përafrimi i të dhënave eksperimentale. Metoda me katrorin më të vogël. Zbatimi praktik i LSM për varësinë lineare nga një kalkulator i pa programueshëm

Shembull.

Të dhëna eksperimentale për vlerat e variablave X Dhe në janë dhënë në tabelë.

Si rezultat i shtrirjes së tyre, funksioni

Duke përdorur metoda me katrorin më të vogël, përafroni këto të dhëna me një varësi lineare y=sëpatë+b(gjeni opsione A Dhe b). Gjeni se cila nga dy rreshtat është më e mirë (në kuptimin e metodës së katrorëve më të vegjël) përafron të dhënat eksperimentale. Bëni një vizatim.

Thelbi i metodës së katrorëve më të vegjël (LSM).

Problemi është gjetja e koeficientëve linearë të varësisë për të cilat funksioni i dy ndryshoreve A Dhe b merr vlerën më të vogël. Kjo është, duke pasur parasysh të dhënat A Dhe b shuma e devijimeve në katror të të dhënave eksperimentale nga drejtëza e gjetur do të jetë më e vogla. Kjo është e gjithë pika e metodës së katrorëve më të vegjël.

Kështu, zgjidhja e shembullit reduktohet në gjetjen e ekstremit të një funksioni të dy ndryshoreve.

Nxjerrja e formulave për gjetjen e koeficientëve.

Përpilohet dhe zgjidhet një sistem me dy ekuacione me dy të panjohura. Gjetja e derivateve të pjesshme të një funksioni në lidhje me variablat A Dhe b, ne i barazojmë këto derivate me zero.

Ne zgjidhim sistemin rezultues të ekuacioneve me çdo metodë (për shembull metoda e zëvendësimit ose ) dhe merrni formulat për gjetjen e koeficientëve duke përdorur metodën e katrorëve më të vegjël (LSM).

Me të dhëna A Dhe b funksionin merr vlerën më të vogël. Dëshmia e këtij fakti është dhënë.

Kjo është e gjithë metoda e katrorëve më të vegjël. Formula për gjetjen e parametrit a përmban shumat , , , dhe parametrin n- sasia e të dhënave eksperimentale. Vlerat e këtyre shumave rekomandohet të llogariten veçmas. Koeficient b gjetur pas llogaritjes a.

Është koha për të kujtuar shembullin origjinal.

Zgjidhje.

Në shembullin tonë n=5. Plotësojmë tabelën për lehtësinë e llogaritjes së shumave që përfshihen në formulat e koeficientëve të kërkuar.

Vlerat në rreshtin e katërt të tabelës merren duke shumëzuar vlerat e rreshtit të dytë me vlerat e rreshtit të tretë për çdo numër i.

Vlerat në rreshtin e pestë të tabelës merren duke kuadruar vlerat e rreshtit të dytë për çdo numër i.

Vlerat e kolonës së fundit të tabelës janë shumat e vlerave nëpër rreshta.

Ne përdorim formulat e metodës së katrorëve më të vegjël për të gjetur koeficientët A Dhe b. Ne zëvendësojmë në to vlerat përkatëse nga kolona e fundit e tabelës:

Prandaj, y=0,165x+2,184është drejtëza e dëshiruar e përafërt.

Mbetet për të gjetur se cila nga rreshtat y=0,165x+2,184 ose përafron më mirë të dhënat origjinale, pra për të bërë një vlerësim duke përdorur metodën e katrorëve më të vegjël.

Vlerësimi i gabimit të metodës së katrorëve më të vegjël.

Për ta bërë këtë, duhet të llogaritni shumat e devijimeve në katror të të dhënave origjinale nga këto rreshta Dhe , një vlerë më e vogël i korrespondon një rreshti që përafron më mirë të dhënat origjinale për sa i përket metodës së katrorëve më të vegjël.

Që atëherë, linja y=0,165x+2,184 përafron më mirë të dhënat origjinale.

Ilustrim grafik i metodës së katrorëve më të vegjël (LSM).

Gjithçka duket e mrekullueshme në tabela. Vija e kuqe është vija e gjetur y=0,165x+2,184, vija blu është , pikat rozë janë të dhënat origjinale.

Për çfarë është, për çfarë janë të gjitha këto përafrime?

Unë personalisht përdor për të zgjidhur problemet e zbutjes së të dhënave, problemet e interpolimit dhe ekstrapolimit (në shembullin origjinal, mund t'ju kërkohet të gjeni vlerën e vlerës së vëzhguar y në x=3 ose kur x=6 sipas metodës MNC). Por ne do të flasim më shumë për këtë më vonë në një seksion tjetër të faqes.

Dëshmi.

Kështu që kur të gjendet A Dhe b funksioni merr vlerën më të vogël, është e nevojshme që në këtë pikë matrica e formës kuadratike të diferencialit të rendit të dytë për funksionin. ishte pozitive definitive. Le ta tregojmë.

Ka shumë aplikime, pasi lejon një paraqitje të përafërt të një funksioni të caktuar nga funksione të tjera më të thjeshta. LSM mund të jetë jashtëzakonisht i dobishëm në përpunimin e vëzhgimeve, dhe përdoret në mënyrë aktive për të vlerësuar disa sasi nga rezultatet e matjeve të të tjerave që përmbajnë gabime të rastësishme. Në këtë artikull, do të mësoni se si të zbatoni llogaritjet e katrorëve më të vegjël në Excel.

Deklarata e problemit në një shembull specifik

Supozoni se ka dy tregues X dhe Y. Për më tepër, Y varet nga X. Meqenëse OLS është me interes për ne nga pikëpamja e analizës së regresionit (në Excel, metodat e tij zbatohen duke përdorur funksione të integruara), duhet të vazhdojmë menjëherë për të shqyrtuar një problem specifik.

Pra, le të jetë X zona e shitjes së një dyqani ushqimor, e matur në metra katrorë, dhe Y të jetë qarkullimi vjetor, i përcaktuar në miliona rubla.

Kërkohet të bëhet një parashikim se çfarë xhiro (Y) do të ketë dyqani nëse ka një ose një tjetër hapësirë me pakicë. Natyrisht, funksioni Y = f (X) po rritet, pasi hipermarketi shet më shumë mallra sesa tezga.

Disa fjalë për saktësinë e të dhënave fillestare të përdorura për parashikim

Le të themi se kemi një tabelë të ndërtuar me të dhëna për n dyqane.

Sipas statistikave matematikore, rezultatet do të jenë pak a shumë të sakta nëse shqyrtohen të dhënat për të paktën 5-6 objekte. Gjithashtu, rezultatet "anormale" nuk mund të përdoren. Në veçanti, një butik i vogël elitar mund të ketë një xhiro shumë herë më të madhe se xhiroja e pikave të mëdha të klasës "masmarket".

Thelbi i metodës

Të dhënat e tabelës mund të shfaqen në rrafshin kartezian si pika M 1 (x 1, y 1), ... M n (x n, y n). Tani zgjidhja e problemit do të reduktohet në zgjedhjen e një funksioni të përafërt y = f (x), i cili ka një grafik që kalon sa më afër pikave M 1, M 2, .. M n .

Sigurisht, mund të përdorni një polinom të shkallës së lartë, por ky opsion nuk është vetëm i vështirë për t'u zbatuar, por thjesht i pasaktë, pasi nuk do të pasqyrojë prirjen kryesore që duhet të zbulohet. Zgjidhja më e arsyeshme është kërkimi i vijës së drejtë y = ax + b, e cila përafron më së miri të dhënat eksperimentale, dhe më saktë, koeficientët - a dhe b.

Rezultati i saktësisë

Për çdo përafrim, vlerësimi i saktësisë së tij është i një rëndësie të veçantë. Shënoni me e i ndryshimin (devijimin) midis vlerave funksionale dhe eksperimentale për pikën x i, d.m.th. e i = y i - f (x i).

Natyrisht, për të vlerësuar saktësinë e përafrimit, mund të përdorni shumën e devijimeve, d.m.th., kur zgjidhni një vijë të drejtë për një paraqitje të përafërt të varësisë së X nga Y, preferenca duhet t'i jepet asaj që ka vlerën më të vogël të shuma e i në të gjitha pikat në shqyrtim. Sidoqoftë, jo gjithçka është aq e thjeshtë, pasi së bashku me devijimet pozitive, praktikisht do të ketë edhe negative.

Ju mund ta zgjidhni problemin duke përdorur modulet e devijimit ose katrorët e tyre. Metoda e fundit është më e përdorura. Përdoret në shumë fusha, duke përfshirë analizën e regresionit (në Excel, zbatimi i tij kryhet duke përdorur dy funksione të integruara), dhe prej kohësh është provuar të jetë efektiv.

Metoda me katrorin më të vogël

Në Excel, siç e dini, ekziston një funksion i integruar automatik që ju lejon të llogaritni vlerat e të gjitha vlerave të vendosura në intervalin e zgjedhur. Kështu, asgjë nuk do të na pengojë të llogarisim vlerën e shprehjes (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Në shënimin matematikor, kjo duket si:

Meqenëse fillimisht u mor vendimi për të përafruar duke përdorur një vijë të drejtë, ne kemi:

Kështu, detyra për të gjetur një vijë të drejtë që përshkruan më së miri një marrëdhënie specifike midis X dhe Y arrin në llogaritjen e minimumit të një funksioni të dy variablave:

Kjo kërkon barazimin me zero derivatet e pjesshme në lidhje me variablat e rinj a dhe b, dhe zgjidhjen e një sistemi primitiv të përbërë nga dy ekuacione me 2 të panjohura të formës:

Pas transformimeve të thjeshta, duke përfshirë pjesëtimin me 2 dhe manipulimin e shumave, marrim:

Duke e zgjidhur atë, për shembull, me metodën e Cramer-it, marrim një pikë të palëvizshme me koeficientë të caktuar a * dhe b * . Ky është minimumi, pra për të parashikuar se çfarë qarkullimi do të ketë dyqani për një zonë të caktuar, është e përshtatshme vija e drejtë y = a * x + b *, e cila është një model regresioni për shembullin në fjalë. Sigurisht, nuk do t'ju lejojë të gjeni rezultatin e saktë, por do t'ju ndihmojë të merrni një ide nëse blerja e një dyqani me kredi për një zonë të caktuar do të paguajë.

Si të zbatoni metodën e katrorëve më të vegjël në Excel

Excel ka një funksion për llogaritjen e vlerës së katrorëve më të vegjël. Ka formën e mëposhtme: TREND (vlera të njohura Y; vlera të njohura X; vlera të reja X; konstante). Le të zbatojmë formulën për llogaritjen e OLS në Excel në tabelën tonë.

Për ta bërë këtë, në qelizën në të cilën duhet të shfaqet rezultati i llogaritjes duke përdorur metodën e katrorëve më të vegjël në Excel, futni shenjën "=" dhe zgjidhni funksionin "TREND". Në dritaren që hapet, plotësoni fushat e duhura, duke theksuar:

diapazoni i vlerave të njohura për Y (në këtë rast të dhëna për qarkullimin);
diapazoni x 1, …x n, d.m.th. madhësia e hapësirës me pakicë;
dhe vlerat e njohura dhe të panjohura të x, për të cilat duhet të zbuloni madhësinë e qarkullimit (për informacion rreth vendndodhjes së tyre në fletën e punës, shihni më poshtë).

Përveç kësaj, ekziston një variabël logjik "Const" në formulë. Nëse vendosni 1 në fushën që korrespondon me të, atëherë kjo do të thotë që llogaritjet duhet të kryhen, duke supozuar se b \u003d 0.

Nëse duhet të dini parashikimin për më shumë se një vlerë x, atëherë pasi të keni futur formulën, nuk duhet të shtypni "Enter", por duhet të shkruani kombinimin "Shift" + "Control" + "Enter" ("Enter" ) në tastierë.

Disa Karakteristika

Analiza e regresionit mund të jetë e aksesueshme edhe për dummies. Formula Excel për parashikimin e vlerës së një grupi variablash të panjohur - "TREND" - mund të përdoret edhe nga ata që nuk kanë dëgjuar kurrë për metodën e katrorëve më të vegjël. Mjafton vetëm të njihni disa veçori të punës së tij. Veçanërisht:

Nëse vendosni gamën e vlerave të njohura të ndryshores y në një rresht ose kolonë, atëherë çdo rresht (kolona) me vlera të njohura të x do të perceptohet nga programi si një ndryshore më vete.
Nëse diapazoni me x të njohur nuk është specifikuar në dritaren TREND, atëherë në rast të përdorimit të funksionit në Excel, programi do ta konsiderojë atë si një grup të përbërë nga numra të plotë, numri i të cilave korrespondon me diapazonin me vlerat e dhëna. të ndryshores y.
Për të nxjerrë një grup vlerash "të parashikuara", shprehja e trendit duhet të futet si një formulë grupi.
Nëse nuk specifikohen vlera të reja x, atëherë funksioni TREND i konsideron ato të barabarta me ato të njohura. Nëse ato nuk janë të specifikuara, atëherë vargu 1 merret si argument; 2; 3; 4;…, e cila është në përpjesëtim me diapazonin me parametrat e dhënë tashmë y.
Gama që përmban vlerat e reja x duhet të ketë të njëjtat ose më shumë rreshta ose kolona si diapazoni me vlerat e dhëna y. Me fjalë të tjera, ai duhet të jetë proporcional me variablat e pavarur.
Një grup me vlera të njohura x mund të përmbajë variabla të shumta. Sidoqoftë, nëse po flasim vetëm për një, atëherë kërkohet që vargjet me vlerat e dhëna x dhe y të jenë proporcionale. Në rastin e disa variablave, është e nevojshme që diapazoni me vlerat e dhëna y të përshtatet në një kolonë ose një rresht.

Funksioni PARASHIKIMI

Zbatohet duke përdorur disa funksione. Njëri prej tyre quhet "PARASHIKIMI". Është i ngjashëm me TREND, d.m.th. jep rezultatin e llogaritjeve duke përdorur metodën e katrorëve më të vegjël. Megjithatë, vetëm për një X, për të cilin vlera e Y është e panjohur.

Tani i njihni formulat e Excel për dummies që ju lejojnë të parashikoni vlerën e vlerës së ardhshme të një treguesi sipas një tendence lineare.

Metoda e katrorëve më të vegjël është një nga më të zakonshmet dhe më të zhvilluarat për shkak të saj thjeshtësia dhe efikasiteti i metodave për vlerësimin e parametrave të lineare. Në të njëjtën kohë, duhet të kihet kujdes gjatë përdorimit të tij, pasi modelet e ndërtuara duke e përdorur atë mund të mos plotësojnë një sërë kërkesash për cilësinë e parametrave të tyre dhe, si rezultat, jo "mirë" pasqyrojnë modelet e zhvillimit të procesit.

Le të shqyrtojmë më në detaje procedurën për vlerësimin e parametrave të një modeli ekonometrik linear duke përdorur metodën e katrorëve më të vegjël. Një model i tillë në formë të përgjithshme mund të përfaqësohet nga ekuacioni (1.2):

y t = a 0 + a 1 x 1 t +...+ a n x nt + ε t .

Të dhënat fillestare kur vlerësohen parametrat a 0, a 1,..., a n është vektori i vlerave të ndryshores së varur y= (y 1 , y 2 , ... , y T)" dhe matricën e vlerave të variablave të pavarur

në të cilën kolona e parë, e përbërë nga një, korrespondon me koeficientin e modelit.

Metoda e katrorëve më të vegjël mori emrin e saj bazuar në parimin bazë që vlerësimet e parametrave të marra në bazë të saj duhet të plotësojnë: shuma e katrorëve të gabimit të modelit duhet të jetë minimale.

Shembuj të zgjidhjes së problemave me metodën e katrorëve më të vegjël

Shembulli 2.1. Ndërmarrja tregtare ka një rrjet të përbërë nga 12 dyqane, informacioni mbi aktivitetet e të cilave është paraqitur në tabelë. 2.1.

Menaxhmenti i kompanisë do të donte të dinte se si madhësia e vjetorit varet nga zona e shitjeve të dyqanit.

Tabela 2.1

Numri i dyqanit	Qarkullimi vjetor, milion rubla	Zone tregtare mije m 2

Zgjidhja e katrorëve më të vegjël. Le të caktojmë - qarkullimin vjetor të dyqanit -të, milion rubla; - siperfaqja e shitjes se dyqanit, mije m 2.

Fig.2.1. Scatterplot për shembullin 2.1

Të përcaktojë formën e marrëdhënies funksionale ndërmjet variablave dhe të ndërtojë një grafik shpërhapjeje (Fig. 2.1).

Bazuar në diagramin e shpërndarjes, mund të konkludojmë se qarkullimi vjetor është pozitivisht i varur nga zona e shitjes (d.m.th., y do të rritet me rritjen e ). Forma më e përshtatshme e lidhjes funksionale është − lineare.

Informacioni për llogaritjet e mëtejshme është paraqitur në Tabelën. 2.2. Duke përdorur metodën e katrorëve më të vegjël, ne vlerësojmë parametrat e modelit linear ekonometrik me një faktor

Tabela 2.2

Kështu,

Prandaj, me një rritje të zonës së tregtimit me 1 mijë m 2, duke qenë të barabarta gjërat e tjera, qarkullimi mesatar vjetor rritet me 67.8871 milion rubla.

Shembulli 2.2. Menaxhmenti i ndërmarrjes vuri re se qarkullimi vjetor varet jo vetëm nga zona e shitjes së dyqanit (shih shembullin 2.1), por edhe nga numri mesatar i vizitorëve. Informacioni përkatës është paraqitur në tabelë. 2.3.

Tabela 2.3

Zgjidhje. Shënoni - numrin mesatar të vizitorëve në dyqanin e th në ditë, mijëra njerëz.

Të përcaktojë formën e marrëdhënies funksionale ndërmjet variablave dhe të ndërtojë një grafik shpërhapjeje (Fig. 2.2).

Bazuar në diagramin e shpërndarjes, mund të konkludojmë se qarkullimi vjetor lidhet pozitivisht me numrin mesatar të vizitorëve në ditë (d.m.th., y do të rritet me rritjen e ). Forma e varësisë funksionale është lineare.

Oriz. 2.2. Scatterplot për shembull 2.2

Tabela 2.4

Në përgjithësi, është e nevojshme të përcaktohen parametrat e modelit ekonometrik me dy faktorë

y t \u003d a 0 + a 1 x 1 t + a 2 x 2 t + ε t

Informacioni i kërkuar për llogaritjet e mëtejshme është paraqitur në Tabelën. 2.4.

Le të vlerësojmë parametrat e një modeli ekonometrik linear me dy faktorë duke përdorur metodën e katrorëve më të vegjël.

Kështu,

Vlerësimi i koeficientit = 61.6583 tregon se, duke qenë të njëjtat gjëra të tjera, me një rritje të zonës së tregtimit me 1 mijë m 2, xhiroja vjetore do të rritet mesatarisht me 61.6583 milion rubla.

Metoda me katrorin më të vogël

Metoda me katrorin më të vogël ( MNK, OLS, Sheshet më të vogla të zakonshme) - një nga metodat bazë të analizës së regresionit për vlerësimin e parametrave të panjohur të modeleve të regresionit nga të dhënat e mostrës. Metoda bazohet në minimizimin e shumës së katrorëve të mbetjeve të regresionit.

Duhet të theksohet se vetë metoda e katrorëve më të vegjël mund të quhet metodë për zgjidhjen e një problemi në çdo fushë, nëse zgjidhja përbëhet ose plotëson një kriter të caktuar për minimizimin e shumës së katrorëve të disa funksioneve të ndryshoreve të panjohura. Prandaj, metoda e katrorëve më të vegjël mund të përdoret gjithashtu për një paraqitje të përafërt (përafrim) të një funksioni të caktuar me funksione të tjera (më të thjeshta), kur gjendet një grup sasish që plotësojnë ekuacionet ose kufizimet, numri i të cilave e kalon numrin e këtyre sasive. , etj.

Thelbi i MNC

Lëreni një model (parametrik) të varësisë probabilistike (regresioni) midis ndryshores (e shpjeguar) y dhe shumë faktorë (variabla shpjegues) x

ku është vektori i parametrave të modelit të panjohur

- Gabim i rastësishëm i modelit.

Le të ketë gjithashtu vëzhgime të mostrave të vlerave të variablave të treguar. Le të jetë numri i vëzhgimit (). Pastaj janë vlerat e variablave në vëzhgimin e -të. Pastaj, për vlerat e dhëna të parametrave b, është e mundur të llogariten vlerat teorike (modele) të ndryshores së shpjeguar y:

Vlera e mbetjeve varet nga vlerat e parametrave b.

Thelbi i LSM (i zakonshëm, klasik) është gjetja e parametrave të tillë b për të cilët shuma e katrorëve të mbetjeve (eng. Shuma e mbetur e katrorëve) do të jetë minimale:

Në rastin e përgjithshëm, ky problem mund të zgjidhet me metoda numerike të optimizimit (minimizimit). Në këtë rast, flitet për katrorët më të vegjël jolinearë(NLS ose NLLS - Anglisht. Sheshet më të vogla jo lineare). Në shumë raste, mund të merret një zgjidhje analitike. Për të zgjidhur problemin e minimizimit, është e nevojshme të gjenden pikat stacionare të funksionit duke e diferencuar atë në lidhje me parametrat e panjohur b, duke barazuar derivatet me zero dhe duke zgjidhur sistemin e ekuacioneve që rezulton:

Nëse gabimet e rastësishme të modelit shpërndahen normalisht, kanë të njëjtën variancë dhe nuk janë të ndërlidhura me njëri-tjetrin, vlerësimet e parametrave të katrorëve më të vegjël janë të njëjta me vlerësimet e metodës së gjasave maksimale (MLM).

LSM në rastin e një modeli linear

Le të jetë lineare varësia e regresionit:

Le y- vektori i kolonës së vëzhgimeve të ndryshores së shpjeguar, dhe - matrica e vëzhgimeve të faktorëve (rreshtat e matricës - vektorët e vlerave të faktorëve në një vëzhgim të caktuar, sipas kolonave - vektori i vlerave të një faktori të caktuar në të gjitha vëzhgimet) . Paraqitja matricore e modelit linear ka formën:

Atëherë vektori i vlerësimeve të variablit të shpjeguar dhe vektori i mbetjeve të regresionit do të jetë i barabartë me

në përputhje me rrethanat, shuma e katrorëve të mbetjeve të regresionit do të jetë e barabartë me

Duke e diferencuar këtë funksion në lidhje me vektorin e parametrave dhe duke barazuar derivatet me zero, marrim një sistem ekuacionesh (në formë matrice):

Zgjidhja e këtij sistemi ekuacionesh jep formulën e përgjithshme për vlerësimet e katrorëve më të vegjël për modelin linear:

Për qëllime analitike, paraqitja e fundit e kësaj formule rezulton të jetë e dobishme. Nëse të dhënat në modelin e regresionit të përqendruar, atëherë në këtë paraqitje matrica e parë ka kuptimin e matricës së mostrës së kovariancës së faktorëve, dhe e dyta është vektori i kovariancave të faktorëve me variabël të varur. Nëse, përveç kësaj, të dhënat janë gjithashtu normalizuar në SKO (d.m.th., në fund të fundit të standardizuara), atëherë matrica e parë ka kuptimin e matricës së korrelacionit të mostrës së faktorëve, vektori i dytë - vektori i korrelacioneve të mostrës së faktorëve me variablin e varur.

Një veti e rëndësishme e vlerësimeve LLS për modelet me një konstante- vija e regresionit të ndërtuar kalon nëpër qendrën e gravitetit të të dhënave të mostrës, domethënë përmbushet barazia:

Në veçanti, në rastin ekstrem kur regresori i vetëm është një konstante, gjejmë se vlerësimi OLS i një parametri të vetëm (vetë konstanta) është i barabartë me vlerën mesatare të ndryshores që shpjegohet. Kjo do të thotë, mesatarja aritmetike, e njohur për vetitë e saj të mira nga ligjet e numrave të mëdhenj, është gjithashtu një vlerësim i katrorëve më të vegjël - ai plotëson kriterin për shumën minimale të devijimeve në katror prej tij.

Shembull: regresion i thjeshtë (në çift).

Në rastin e regresionit linear të çiftuar, formulat e llogaritjes janë thjeshtuar (mund të bëni pa algjebër matricë):

Vetitë e vlerësimeve të OLS

Para së gjithash, vërejmë se për modelet lineare, vlerësimet e katrorëve më të vegjël janë vlerësime lineare, siç vijon nga formula e mësipërme. Për vlerësimet e paanshme OLS, është e nevojshme dhe e mjaftueshme të përmbushet kushti më i rëndësishëm i analizës së regresionit: pritshmëria matematikore e një gabimi të rastësishëm të kushtëzuar nga faktorët duhet të jetë e barabartë me zero. Ky kusht plotësohet, veçanërisht nëse

pritshmëria matematikore e gabimeve të rastësishme është zero, dhe
faktorët dhe gabimet e rastësishme janë variabla të rastësishme të pavarura.

Kushti i dytë - gjendja e faktorëve ekzogjenë - është thelbësor. Nëse kjo pronë nuk është e kënaqur, atëherë mund të supozojmë se pothuajse çdo vlerësim do të jetë jashtëzakonisht i pakënaqshëm: ato as nuk do të jenë të qëndrueshme (d.m.th., edhe një sasi shumë e madhe e të dhënave nuk lejon marrjen e vlerësimeve cilësore në këtë rast). Në rastin klasik, bëhet një supozim më i fortë për determinizmin e faktorëve, në ndryshim nga një gabim i rastësishëm, që automatikisht do të thotë se kushti ekzogjen është i plotësuar. Në rastin e përgjithshëm, për konsistencën e vlerësimeve, mjafton të plotësohet kushti i ekzogjenitetit së bashku me konvergjencën e matricës me një matricë jo të vetme me një rritje të madhësisë së mostrës deri në pafundësi.

Në mënyrë që, përveç konsistencës dhe paanshmërisë, vlerësimet e LSM (të zakonshme) të jenë gjithashtu efektive (më të mirat në klasën e vlerësimeve lineare të paanshme), është e nevojshme të plotësohen vetitë shtesë të një gabimi të rastësishëm:

Këto supozime mund të formulohen për matricën e kovariancës së vektorit të gabimit të rastësishëm

Një model linear që plotëson këto kushte quhet klasike. Vlerësuesit e katrorëve më të vegjël për regresionin linear klasik janë vlerësues të paanshëm, të qëndrueshëm dhe më efikas në klasën e të gjithë vlerësuesve linearë të paanshëm (shkurtesa blu (Vlerësuesi më i mirë linear i pabazuar) është vlerësimi më i mirë linear i paanshëm; në literaturën vendase, më shpesh citohet teorema Gauss-Markov). Siç është e lehtë të tregohet, matrica e kovariancës së vektorit të vlerësimit të koeficientit do të jetë e barabartë me:

Katroret më të vegjël të përgjithësuar

Metoda e katrorëve më të vegjël lejon një përgjithësim të gjerë. Në vend që të minimizohet shuma e katrorëve të mbetjeve, mund të minimizohet një formë kuadratike pozitive e caktuar e vektorit të mbetur, ku është një matricë simetrike pozitive e peshës së caktuar. Katroret më të vegjël të zakonshëm janë një rast i veçantë i kësaj qasjeje, kur matrica e peshës është proporcionale me matricën e identitetit. Siç dihet nga teoria e matricave (ose operatorëve) simetrike, ka një dekompozim për matrica të tilla. Prandaj, funksioni i specifikuar mund të përfaqësohet si më poshtë, domethënë, ky funksional mund të përfaqësohet si shuma e katrorëve të disa "mbetjeve" të transformuara. Kështu, ne mund të dallojmë një klasë të metodave të katrorëve më të vegjël - metodat LS (Katroret më të vegjël).

Është vërtetuar (teorema e Aitken) se për një model të përgjithësuar të regresionit linear (në të cilin nuk vendosen kufizime në matricën e kovariancës së gabimeve të rastit), më efektive (në klasën e vlerësimeve lineare të paanshme) janë vlerësimet e të ashtuquajturave. OLS e përgjithësuar (OMNK, GLS - katrorët më të vegjël të përgjithësuar)- Metoda LS me matricë peshe të barabartë me matricën e kovariancës së anasjelltë të gabimeve të rastit: .

Mund të tregohet se formula për GLS-vlerësimet e parametrave të modelit linear ka formën

Matrica e kovariancës së këtyre vlerësimeve, përkatësisht, do të jetë e barabartë me

Në fakt, thelbi i OLS qëndron në një transformim të caktuar (linear) (P) të të dhënave origjinale dhe aplikimin e katrorëve më të vegjël të zakonshëm në të dhënat e transformuara. Qëllimi i këtij transformimi është që për të dhënat e transformuara, gabimet e rastësishme tashmë plotësojnë supozimet klasike.

Sheshet më të vogla të peshuara

Në rastin e një matrice të peshës diagonale (dhe rrjedhimisht matricës së kovariancës së gabimeve të rastit), kemi të ashtuquajturat katrorët më të vegjël të ponderuar (WLS - Katroret më të vogla të ponderuara). Në këtë rast, shuma e ponderuar e katrorëve të mbetjeve të modelit minimizohet, domethënë çdo vëzhgim merr një "peshë" që është në përpjesëtim të zhdrejtë me variancën e gabimit të rastit në këtë vëzhgim: . Në fakt, të dhënat transformohen duke peshuar vëzhgimet (duke pjesëtuar me një sasi proporcionale me devijimin standard të supozuar të gabimeve të rastit), dhe katrorët më të vegjël normalë aplikohen në të dhënat e ponderuara.

Disa raste të veçanta të aplikimit të LSM në praktikë

Përafrim linear

Konsideroni rastin kur, si rezultat i studimit të varësisë së një sasie të caktuar skalare nga një sasi e caktuar skalare (Kjo mund të jetë, për shembull, varësia e tensionit nga forca aktuale: , ku është një vlerë konstante, rezistenca e përcjellësit ), u matën këto sasi, si rezultat i të cilave vlerat dhe vlerat e tyre përkatëse. Të dhënat e matjes duhet të regjistrohen në një tabelë.

Tabela. Rezultatet e matjes.

Matja Nr.
1
2
3
4
5
6

Pyetja tingëllon si kjo: cila vlerë e koeficientit mund të zgjidhet për të përshkruar më së miri varësinë? Sipas katrorëve më të vegjël, kjo vlerë duhet të jetë e tillë që shuma e devijimeve në katror të vlerave nga vlerat

ishte minimale

Shuma e devijimeve në katror ka një ekstrem - një minimum, i cili na lejon të përdorim këtë formulë. Le të gjejmë vlerën e koeficientit nga kjo formulë. Për ta bërë këtë, ne transformojmë anën e saj të majtë si më poshtë:

Formula e fundit na lejon të gjejmë vlerën e koeficientit , i cili kërkohej në problem.

Histori

Deri në fillim të shekullit XIX. shkencëtarët nuk kishin rregulla të caktuara për zgjidhjen e një sistemi ekuacionesh në të cilin numri i të panjohurave është më i vogël se numri i ekuacioneve; Deri në atë kohë përdoreshin metoda të veçanta, varësisht nga lloji i ekuacioneve dhe nga zgjuarsia e kalkulatorëve, dhe për këtë arsye kalkulatorë të ndryshëm, duke u nisur nga të njëjtat të dhëna vëzhguese, dolën në përfundime të ndryshme. Gauss (1795) vlerësohet me aplikimin e parë të metodës, dhe Lezhandre (1805) në mënyrë të pavarur e zbuloi dhe e publikoi atë me emrin e tij modern (fr. Method des moindres quarres ) . Laplace e lidhi metodën me teorinë e probabilitetit, dhe matematikani amerikan Adrain (1808) shqyrtoi aplikimet e saj probabilistike. Metoda është e përhapur dhe e përmirësuar nga kërkimet e mëtejshme nga Encke, Bessel, Hansen dhe të tjerë.

Përdorimi alternativ i MNC-ve

Ideja e metodës së katrorëve më të vegjël mund të përdoret edhe në raste të tjera që nuk lidhen drejtpërdrejt me analizën e regresionit. Fakti është se shuma e katrorëve është një nga matjet më të zakonshme të afërsisë për vektorët (metrika Euklidiane në hapësirat me dimensione të fundme).

Një aplikim është "zgjidhja" e sistemeve të ekuacioneve lineare në të cilat numri i ekuacioneve është më i madh se numri i ndryshoreve.

ku matrica nuk është katrore, por drejtkëndore.

Një sistem i tillë ekuacionesh, në rastin e përgjithshëm, nuk ka zgjidhje (nëse rangu është në të vërtetë më i madh se numri i ndryshoreve). Prandaj, ky sistem mund të "zgjidhet" vetëm në kuptimin e zgjedhjes së një vektori të tillë në mënyrë që të minimizohet "distanca" midis vektorëve dhe . Për ta bërë këtë, mund të aplikoni kriterin për minimizimin e shumës së diferencave në katror të pjesëve të majta dhe të djathta të ekuacioneve të sistemit, domethënë . Është e lehtë të tregohet se zgjidhja e këtij problemi të minimizimit çon në zgjidhjen e sistemit të mëposhtëm të ekuacioneve

Shembull.

Të dhëna eksperimentale për vlerat e variablave X Dhe në janë dhënë në tabelë.

Si rezultat i shtrirjes së tyre, funksioni

Thelbi i metodës së katrorëve më të vegjël (LSM).

Kështu, zgjidhja e shembullit reduktohet në gjetjen e ekstremit të një funksioni të dy ndryshoreve.

Nxjerrja e formulave për gjetjen e koeficientëve.

Përpilohet dhe zgjidhet një sistem me dy ekuacione me dy të panjohura. Gjetja e derivateve të pjesshme të funksioneve sipas variablave A Dhe b, ne i barazojmë këto derivate me zero.

Ne zgjidhim sistemin rezultues të ekuacioneve me çdo metodë (për shembull metoda e zëvendësimit ose Metoda e Cramer-it) dhe merrni formulat për gjetjen e koeficientëve duke përdorur metodën e katrorëve më të vegjël (LSM).

Me të dhëna A Dhe b funksionin merr vlerën më të vogël. Dëshmia e këtij fakti është dhënë poshtë tekstit në fund të faqes.

Kjo është e gjithë metoda e katrorëve më të vegjël. Formula për gjetjen e parametrit a përmban shumat ,,, dhe parametrin n- sasia e të dhënave eksperimentale. Vlerat e këtyre shumave rekomandohet të llogariten veçmas. Koeficient b gjetur pas llogaritjes a.

Është koha për të kujtuar shembullin origjinal.

Zgjidhje.

Në shembullin tonë n=5. Plotësojmë tabelën për lehtësinë e llogaritjes së shumave që përfshihen në formulat e koeficientëve të kërkuar.

Vlerat në rreshtin e katërt të tabelës merren duke shumëzuar vlerat e rreshtit të dytë me vlerat e rreshtit të tretë për çdo numër i.

Vlerat në rreshtin e pestë të tabelës merren duke kuadruar vlerat e rreshtit të dytë për çdo numër i.

Vlerat e kolonës së fundit të tabelës janë shumat e vlerave nëpër rreshta.

Ne përdorim formulat e metodës së katrorëve më të vegjël për të gjetur koeficientët A Dhe b. Ne zëvendësojmë në to vlerat përkatëse nga kolona e fundit e tabelës:

Prandaj, y=0,165x+2,184është drejtëza e dëshiruar e përafërt.

Mbetet për të gjetur se cila nga rreshtat y=0,165x+2,184 ose përafron më mirë të dhënat origjinale, pra për të bërë një vlerësim duke përdorur metodën e katrorëve më të vegjël.

Vlerësimi i gabimit të metodës së katrorëve më të vegjël.

Që atëherë, linja y=0,165x+2,184 përafron më mirë të dhënat origjinale.

Ilustrim grafik i metodës së katrorëve më të vegjël (LSM).

Gjithçka duket e mrekullueshme në tabela. Vija e kuqe është vija e gjetur y=0,165x+2,184, vija blu është , pikat rozë janë të dhënat origjinale.

Në praktikë, kur modeloni procese të ndryshme - në veçanti, ekonomike, fizike, teknike, sociale - përdoret gjerësisht një ose një metodë tjetër e llogaritjes së vlerave të përafërta të funksioneve nga vlerat e tyre të njohura në disa pika fikse.

Shpesh lindin probleme të përafrimit të funksioneve të këtij lloji:

kur ndërtoni formula të përafërta për llogaritjen e vlerave të sasive karakteristike të procesit në studim sipas të dhënave tabelare të marra si rezultat i eksperimentit;

në integrimin numerik, diferencimin, zgjidhjen e ekuacioneve diferenciale etj.;

nëse është e nevojshme të llogariten vlerat e funksioneve në pikat e ndërmjetme të intervalit të konsideruar;

kur përcaktohen vlerat e sasive karakteristike të procesit jashtë intervalit në shqyrtim, në veçanti, kur bëhet parashikimi.

Nëse, për të modeluar një proces të caktuar të specifikuar nga një tabelë, ndërtohet një funksion që përshkruan përafërsisht këtë proces bazuar në metodën e katrorëve më të vegjël, ai do të quhet funksion përafrues (regresion), dhe vetë detyra e ndërtimit të funksioneve të përafërta do të të jetë një problem përafrimi.

Ky artikull diskuton mundësitë e paketës MS Excel për zgjidhjen e problemeve të tilla, përveç kësaj, jepen metoda dhe teknika për ndërtimin (krijimin) e regresioneve për funksionet e dhëna në mënyrë tabelare (që është baza e analizës së regresionit).

Ekzistojnë dy mundësi për ndërtimin e regresioneve në Excel.

Shtimi i regresioneve (vijave të prirjes) të zgjedhur në një grafik të ndërtuar mbi bazën e një tabele të dhënash për karakteristikat e procesit të studiuar (e disponueshme vetëm nëse është ndërtuar një grafik);

Përdorimi i funksioneve statistikore të integruara të fletës së punës Excel, e cila ju lejon të merrni regresione (linjat e trendit) direkt nga tabela e të dhënave burimore.

Shtimi i trendeve në një grafik

Për një tabelë të dhënash që përshkruan një proces të caktuar dhe të përfaqësuar nga një diagram, Excel ka një mjet efektiv të analizës së regresionit që ju lejon të:

ndërtoni mbi bazën e metodës së katrorëve më të vegjël dhe shtoni në diagram pesë lloje regresionesh që modelojnë procesin në studim me shkallë të ndryshme saktësie;

shtoni një ekuacion të regresionit të ndërtuar në diagram;

përcaktoni shkallën e përputhshmërisë së regresionit të zgjedhur me të dhënat e shfaqura në grafik.

Bazuar në të dhënat e grafikut, Excel ju lejon të merrni lloje të regresioneve lineare, polinomiale, logaritmike, eksponenciale, eksponenciale, të cilat jepen nga ekuacioni:

y = y(x)

ku x është një ndryshore e pavarur, e cila shpesh merr vlerat e një sekuence numrash natyrorë (1; 2; 3; ...) dhe prodhon, për shembull, një numërim mbrapsht të kohës së procesit në studim (karakteristikat) .

1 . Regresioni linear është i mirë në modelimin e veçorive që rriten ose ulen me një ritëm konstant. Ky është modeli më i thjeshtë i procesit në studim. Është ndërtuar sipas ekuacionit:

y=mx+b

ku m është tangjentja e pjerrësisë së regresionit linear në boshtin x; b - koordinata e pikës së prerjes së regresionit linear me boshtin y.

2 . Një linjë prirje polinomiale është e dobishme për përshkrimin e karakteristikave që kanë disa ekstreme të dallueshme (të larta dhe të ulëta). Zgjedhja e shkallës së polinomit përcaktohet nga numri i ekstremeve të karakteristikës në studim. Kështu, një polinom i shkallës së dytë mund të përshkruajë mirë një proces që ka vetëm një maksimum ose minimum; polinomi i shkallës së tretë - jo më shumë se dy ekstreme; polinomi i shkallës së katërt - jo më shumë se tre ekstreme, etj.

Në këtë rast, linja e trendit ndërtohet në përputhje me ekuacionin:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

ku koeficientët c0, c1, c2,... c6 janë konstante vlerat e të cilave përcaktohen gjatë ndërtimit.

3 . Linja e tendencës logaritmike përdoret me sukses në karakteristikat e modelimit, vlerat e të cilave fillimisht ndryshojnë me shpejtësi dhe më pas stabilizohen gradualisht.

y = c ln(x) + b

4 . Linja e trendit të energjisë jep rezultate të mira nëse vlerat e varësisë së studiuar karakterizohen nga një ndryshim i vazhdueshëm në shkallën e rritjes. Një shembull i një varësie të tillë mund të shërbejë si një grafik i lëvizjes së përshpejtuar në mënyrë uniforme të makinës. Nëse ka vlera zero ose negative në të dhëna, nuk mund të përdorni një linjë të trendit të energjisë.

Është ndërtuar në përputhje me ekuacionin:

y = cxb

ku koeficientët b, c janë konstante.

5 . Një linjë trendi eksponenciale duhet të përdoret nëse shkalla e ndryshimit të të dhënave është vazhdimisht në rritje. Për të dhënat që përmbajnë vlera zero ose negative, ky lloj përafrimi gjithashtu nuk zbatohet.

Është ndërtuar në përputhje me ekuacionin:

y=cebx

ku koeficientët b, c janë konstante.

Kur zgjedh një linjë trendi, Excel llogarit automatikisht vlerën e R2, e cila karakterizon saktësinë e përafrimit: sa më afër të jetë vlera R2 me një, aq më e besueshme linja e trendit përafron procesin në studim. Nëse është e nevojshme, vlera e R2 mund të shfaqet gjithmonë në diagram.

Përcaktohet nga formula:

Për të shtuar një linjë trendi në një seri të dhënash:

aktivizoni grafikun e ndërtuar në bazë të serisë së të dhënave, d.m.th., klikoni brenda zonës së grafikut. Artikulli Grafik do të shfaqet në menynë kryesore;

pasi të klikoni mbi këtë artikull, në ekran do të shfaqet një meny, në të cilën duhet të zgjidhni komandën Shto linjën e trendit.

Të njëjtat veprime zbatohen lehtësisht nëse rri pezull mbi grafikun që korrespondon me një nga seritë e të dhënave dhe kliko me të djathtën; në menynë e kontekstit që shfaqet, zgjidhni komandën Shto linjën e trendit. Kutia e dialogut Trendline do të shfaqet në ekran me skedën Type të hapur (Fig. 1).

Pas kësaj ju duhet:

Në skedën Lloji, zgjidhni llojin e kërkuar të linjës së trendit (Linear zgjidhet si parazgjedhje). Për llojin Polynomial, në fushën Degree, specifikoni shkallën e polinomit të zgjedhur.

1 . Fusha Built on Series liston të gjitha seritë e të dhënave në grafikun në fjalë. Për të shtuar një linjë trendi në një seri specifike të dhënash, zgjidhni emrin e saj në fushën Ndërtuar në seri.

Nëse është e nevojshme, duke shkuar te skeda Parametrat (Fig. 2), mund të vendosni parametrat e mëposhtëm për linjën e trendit:

ndryshoni emrin e linjës së prirjes në Emrin e fushës së kurbës së përafërt (të zbutur).

caktoni numrin e periudhave (përpara ose prapa) për parashikimin në fushën Parashikimi;

shfaqni ekuacionin e linjës së prirjes në zonën e grafikut, për të cilën duhet të aktivizoni kutinë e zgjedhjes për të shfaqur ekuacionin në grafik;

shfaq vlerën e besueshmërisë së përafrimit R2 në zonën e diagramit, për të cilën duhet të aktivizoni kutinë e zgjedhjes vendos vlerën e besueshmërisë së përafrimit (R^2) në diagram;

vendosni pikën e kryqëzimit të vijës së prirjes me boshtin Y, për të cilin duhet të aktivizoni kutinë e kontrollit për kryqëzimin e kurbës me boshtin Y në një pikë;

klikoni butonin OK për të mbyllur kutinë e dialogut.

Ka tre mënyra për të filluar redaktimin e një linjë trendi të ndërtuar tashmë:

përdorni komandën Selected trend line nga menyja Format, pasi të zgjidhni linjën e trendit;

zgjidhni komandën Format Trendline nga menyja e kontekstit, e cila thirret duke klikuar me të djathtën në vijën e trendit;

duke klikuar dy herë në vijën e trendit.

Kutia e dialogut Format Trendline do të shfaqet në ekran (Fig. 3), e cila përmban tre skeda: View, Type, Parametrat dhe përmbajtja e dy të fundit përputhet plotësisht me skedat e ngjashme të kutisë së dialogut Trendline (Fig. 1-2 ). Në skedën View, mund të vendosni llojin e linjës, ngjyrën dhe trashësinë e saj.

Për të fshirë një linjë tendence të ndërtuar tashmë, zgjidhni linjën e tendencës që do të fshihet dhe shtypni butonin Delete.

Përparësitë e mjetit të analizës së regresionit të konsideruar janë:

lehtësia relative e vizatimit të një linje trendi në grafikët pa krijuar një tabelë të dhënash për të;

një listë mjaft e gjerë e llojeve të linjave të tendencave të propozuara, dhe kjo listë përfshin llojet më të përdorura të regresionit;

mundësia e parashikimit të sjelljes së procesit në studim për një numër arbitrar (brenda sensit të përbashkët) hapash përpara, si dhe prapa;

mundësia e marrjes së ekuacionit të linjës së trendit në një formë analitike;

mundësia, nëse është e nevojshme, për të marrë një vlerësim të besueshmërisë së përafrimit.

Disavantazhet përfshijnë pikat e mëposhtme:

ndërtimi i një linje trendi kryhet vetëm nëse ekziston një grafik i ndërtuar mbi një seri të dhënash;

procesi i gjenerimit të serive të të dhënave për karakteristikën në studim bazuar në ekuacionet e linjës së trendit të marra për të është disi i rrëmujshëm: ekuacionet e dëshiruara të regresionit përditësohen me çdo ndryshim në vlerat e serisë së të dhënave origjinale, por vetëm brenda zonës së grafikut. , ndërkohë që seria e të dhënave e formuar në bazë të tendencës së ekuacionit të linjës së vjetër, mbetet e pandryshuar;

Në raportet e PivotChart, kur ndryshoni pamjen e grafikut ose raportin e lidhur me PivotTable, linjat e tendencës ekzistuese nuk ruhen, që do të thotë se përpara se të vizatoni linjat e prirjeve ose të formatoni ndryshe një raport PivotChart, duhet të siguroheni që paraqitja e raportit i plotëson kërkesat tuaja.

Linjat e tendencës mund të shtohen në seritë e të dhënave të paraqitura në grafikët si grafiku, histogrami, grafikët e zonave të sheshta jo të normalizuara, shiritat, shpërndarjet, flluska dhe grafikët e aksioneve.

Nuk mund të shtoni linja trendi në seritë e të dhënave në grafikët 3-D, Standard, Radar, Pie dhe Donut.

Përdorimi i funksioneve të integruara të Excel

Excel ofron gjithashtu një mjet të analizës së regresionit për vizatimin e vijave të trendit jashtë zonës së grafikut. Për këtë qëllim mund të përdoren një sërë funksionesh statistikore të fletës së punës, por të gjitha ato ju lejojnë të ndërtoni vetëm regresione lineare ose eksponenciale.

Excel ka disa funksione për ndërtimin e regresionit linear, në veçanti:

TRENDI;

SHPJERI dhe PRERJE.

Si dhe disa funksione për ndërtimin e një linje trendi eksponencial, në veçanti:

LGRFPafërsisht.

Duhet të theksohet se teknikat për ndërtimin e regresioneve duke përdorur funksionet TREND dhe GROWTH janë praktikisht të njëjta. E njëjta gjë mund të thuhet për çiftin e funksioneve LINEST dhe LGRFPRIBL. Për këto katër funksione, gjatë krijimit të një tabele vlerash, përdoren veçori të Excel-it si formulat e grupeve, të cilat rrëmojnë disi procesin e ndërtimit të regresioneve. Vëmë re gjithashtu se ndërtimi i një regresioni linear, për mendimin tonë, është më i lehtë për t'u zbatuar duke përdorur funksionet SLOPE dhe INTERCEPT, ku i pari prej tyre përcakton pjerrësinë e regresionit linear dhe i dyti përcakton segmentin e prerë nga regresioni. në boshtin y.

Përparësitë e veglës së funksioneve të integruara për analizën e regresionit janë:

një proces mjaft i thjeshtë i të njëjtit lloj formimi i serive të të dhënave të karakteristikës në studim për të gjitha funksionet statistikore të integruara që përcaktojnë linjat e trendit;

një teknikë standarde për ndërtimin e linjave të trendit bazuar në seritë e gjeneruara të të dhënave;

aftësia për të parashikuar sjelljen e procesit në studim për numrin e kërkuar të hapave përpara ose prapa.

Dhe disavantazhet përfshijnë faktin se Excel nuk ka funksione të integruara për krijimin e llojeve të tjera (përveç lineare dhe eksponenciale) të linjave të trendit. Kjo rrethanë shpesh nuk lejon zgjedhjen e një modeli mjaft të saktë të procesit në studim, si dhe marrjen e parashikimeve afër realitetit. Përveç kësaj, kur përdorni funksionet TREND dhe GROW, ekuacionet e linjave të trendit nuk dihen.

Duhet të theksohet se autorët nuk e vendosën qëllimin e artikullit për të paraqitur kursin e analizës së regresionit me shkallë të ndryshme të plotësisë. Detyra e tij kryesore është të tregojë aftësitë e paketës Excel në zgjidhjen e problemeve të përafrimit duke përdorur shembuj specifikë; demonstroni se çfarë mjetesh efektive ka Excel për ndërtimin e regresioneve dhe parashikimit; ilustrojnë se sa relativisht lehtë mund të zgjidhen probleme të tilla edhe nga një përdorues që nuk ka njohuri të thella të analizës së regresionit.

Shembuj të zgjidhjes së problemeve specifike

Konsideroni zgjidhjen e problemeve specifike duke përdorur mjetet e listuara të paketës Excel.

Detyra 1

Me një tabelë të dhënash për fitimin e një ndërmarrje transporti automobilistik për vitet 1995-2002. ju duhet të bëni sa më poshtë.

Ndërtoni një tabelë.

Shtoni linjat e trendit linear dhe polinom (kuadratik dhe kub) në grafik.

Duke përdorur ekuacionet e linjës së trendit, merrni të dhëna tabelare mbi fitimin e ndërmarrjes për secilën linjë trendi për 1995-2004.

Bëni një parashikim të fitimit për ndërmarrjen për 2003 dhe 2004.

Zgjidhja e problemit

Në rangun e qelizave A4:C11 të fletës së punës Excel, ne futim fletën e punës të paraqitur në Fig. 4.

Pasi kemi zgjedhur gamën e qelizave B4:C11, ndërtojmë një tabelë.

Aktivizojmë grafikun e ndërtuar dhe, duke përdorur metodën e përshkruar më sipër, pasi kemi zgjedhur llojin e linjës së trendit në kutinë e dialogut të linjës së trendit (shih Fig. 1), shtojmë në mënyrë alternative linjat e trendit linear, kuadratik dhe kub në grafik. Në të njëjtën kuti dialogu, hapni skedën Parametrat (shih Fig. 2), në emrin e fushës së kurbës së përafërt (të zbutur), shkruani emrin e trendit që do të shtohet dhe në fushën Parashikimi përpara për: periudhat, vendosni vlera 2, pasi është planifikuar të bëhet një parashikim fitimi për dy vitet e ardhshme. Për të shfaqur ekuacionin e regresionit dhe vlerën e besueshmërisë së përafrimit R2 në zonën e diagramit, aktivizoni kutitë e kontrollit Shfaq ekuacionin në ekran dhe vendos vlerën e besueshmërisë së përafrimit (R^2) në diagram. Për perceptim më të mirë vizual, ne ndryshojmë llojin, ngjyrën dhe trashësinë e linjave të prirjeve të vizatuara, për të cilat përdorim skedën View në kutinë e dialogut Formati i linjës së tendencës (shih Fig. 3). Grafiku që rezulton me linjat e tendencave të shtuara është paraqitur në fig. 5.

Për të marrë të dhëna tabelare mbi fitimin e ndërmarrjes për çdo linjë trendi për vitet 1995-2004. Le të përdorim ekuacionet e linjave të prirjes të paraqitura në fig. 5. Për ta bërë këtë, në qelizat e diapazonit D3:F3, futni informacione tekstuale për llojin e linjës së tendencës së zgjedhur: Trendi linear, Trendi kuadratik, trendi kub. Më pas, futni formulën e regresionit linear në qelizën D4 dhe, duke përdorur shënuesin e mbushjes, kopjoni këtë formulë me referenca relative në gamën e qelizave D5:D13. Duhet të theksohet se çdo qelizë me një formulë regresioni linear nga diapazoni i qelizave D4:D13 ka si argument një qelizë përkatëse nga diapazoni A4:A13. Në mënyrë të ngjashme, për regresionin kuadratik, diapazoni i qelizave E4:E13 është i mbushur, dhe për regresionin kub, diapazoni i qelizave F4:F13 është i mbushur. Kështu, u bë një parashikim për fitimin e ndërmarrjes për vitet 2003 dhe 2004. me tre tendenca. Tabela rezultuese e vlerave është paraqitur në fig. 6.

Detyra 2

Ndërtoni një tabelë.

Shtoni linjat e tendencës logaritmike, eksponenciale dhe eksponenciale në grafik.

Nxirrni ekuacionet e linjave të prirjeve të marra, si dhe vlerat e besueshmërisë së përafrimit R2 për secilën prej tyre.

Duke përdorur ekuacionet e linjës së trendit, merrni të dhëna tabelare mbi fitimin e ndërmarrjes për secilën linjë trendi për 1995-2002.

Bëni një parashikim fitimi për biznesin për 2003 dhe 2004 duke përdorur këto linja prirje.

Zgjidhja e problemit

Duke ndjekur metodologjinë e dhënë në zgjidhjen e problemit 1, marrim një diagram me linja prirje logaritmike, eksponenciale dhe eksponenciale të shtuara (Fig. 7). Më tej, duke përdorur ekuacionet e marra të linjës së trendit, ne plotësojmë tabelën e vlerave për fitimin e ndërmarrjes, duke përfshirë vlerat e parashikuara për 2003 dhe 2004. (Fig. 8).

Në fig. 5 dhe fig. shihet se modeli me prirje logaritmike korrespondon me vlerën më të ulët të besueshmërisë së përafrimit

R2 = 0,8659

Vlerat më të larta të R2 korrespondojnë me modelet me një prirje polinomiale: kuadratike (R2 = 0,9263) dhe kub (R2 = 0,933).

Detyra 3

Me një tabelë të dhënash mbi fitimin e një ndërmarrje transporti motorik për vitet 1995-2002, të dhënë në detyrën 1, duhet të kryeni hapat e mëposhtëm.

Merrni seritë e të dhënave për linjat e prirjeve lineare dhe eksponenciale duke përdorur funksionet TREND dhe GROW.

Duke përdorur funksionet TREND dhe RRITJE, bëni një parashikim të fitimit për ndërmarrjen për 2003 dhe 2004.

Për të dhënat fillestare dhe seritë e të dhënave të marra, ndërtoni një diagram.

Zgjidhja e problemit

Le të përdorim fletën e punës së detyrës 1 (shih Fig. 4). Le të fillojmë me funksionin TREND:

zgjidhni gamën e qelizave D4:D11, të cilat duhet të plotësohen me vlerat e funksionit TREND që korrespondojnë me të dhënat e njohura për fitimin e ndërmarrjes;

thirrni komandën Funksion nga menyja Insert. Në kutinë e dialogut Function Wizard që shfaqet, zgjidhni funksionin TREND nga kategoria Statistikore dhe më pas klikoni butonin OK. I njëjti veprim mund të kryhet duke shtypur butonin (funksioni Insert) në shiritin standard të veglave.

Në kutinë e dialogut "Argumentet e funksionit" që shfaqet, futni gamën e qelizave C4:C11 në fushën Vlerat e_njohura_y; në fushën Vlerat_njohura_x - diapazoni i qelizave B4:B11;

për ta bërë formulën e futur një formulë grupi, përdorni kombinimin e tastit + + .

Formula që kemi futur në shiritin e formulave do të duket si: =(TREND(C4:C11;B4:B11)).

Si rezultat, diapazoni i qelizave D4:D11 është i mbushur me vlerat përkatëse të funksionit TREND (Fig. 9).

Për të bërë një parashikim të fitimit të kompanisë për 2003 dhe 2004. nevojshme:

zgjidhni gamën e qelizave D12:D13, ku do të futen vlerat e parashikuara nga funksioni TREND.

thirrni funksionin TREND dhe në kutinë e dialogut Argumentet e funksionit që shfaqet, futni në fushën Vlerat_y_njohura - gamën e qelizave C4:C11; në fushën Vlerat_njohura_x - diapazoni i qelizave B4:B11; dhe në fushën New_values_x - diapazoni i qelizave B12:B13.

kthejeni këtë formulë në një formulë grupi duke përdorur shkurtoren e tastierës Ctrl + Shift + Enter.

Formula e futur do të duket si: =(TREND(C4:C11;B4:B11;B12:B13)), dhe diapazoni i qelizave D12:D13 do të plotësohet me vlerat e parashikuara të funksionit TREND (shih Fig. 9).

Në mënyrë të ngjashme, një seri e të dhënave plotësohet duke përdorur funksionin GROWTH, i cili përdoret në analizën e varësive jolineare dhe funksionon saktësisht njësoj si homologu i tij linear TREND.

Figura 10 tregon tabelën në modalitetin e shfaqjes së formulës.

Për të dhënat fillestare dhe seritë e të dhënave të marra, diagrami i paraqitur në fig. njëmbëdhjetë.

Detyra 4

Me një tabelë të dhënash për marrjen e aplikacioneve për shërbime nga shërbimi dispeçer i një ndërmarrje transporti automobilistik për periudhën nga data 1 deri në 11 të muajit aktual, duhet të kryhen veprimet e mëposhtme.

Merrni seritë e të dhënave për regresionin linear: duke përdorur funksionet SLOPE dhe INTERCEPT; duke përdorur funksionin LINEST.

Merrni një seri të dhënash për regresion eksponencial duke përdorur funksionin LYFFPRIB.

Duke përdorur funksionet e mësipërme, bëni një parashikim për marrjen e aplikacioneve në shërbimin e dërgimit për periudhën nga data 12 deri në 14 të muajit aktual.

Për serinë e të dhënave origjinale dhe të marra, ndërtoni një diagram.

Zgjidhja e problemit

Vini re se, ndryshe nga funksionet TREND dhe GROW, asnjë nga funksionet e listuara më sipër (PJERRJE, PËRGJIM, LINEST, LGRFPRIB) nuk është regresion. Këto funksione luajnë vetëm një rol ndihmës, duke përcaktuar parametrat e nevojshëm të regresionit.

Për regresionet lineare dhe eksponenciale të ndërtuara duke përdorur funksionet SLOPE, INTERCEPT, LINEST, LGRFPRIB, pamja e ekuacioneve të tyre është gjithmonë e njohur, në ndryshim nga regresionet lineare dhe eksponenciale që korrespondojnë me funksionet TREND dhe GROWTH.

1 . Le të ndërtojmë një regresion linear që ka ekuacionin:

y=mx+b

duke përdorur funksionet SLOPE dhe INTERCEPT, ku pjerrësia e regresionit m përcaktohet nga funksioni SLOPE, dhe termi konstant b - nga funksioni INTERCEPT.

Për ta bërë këtë, ne kryejmë veprimet e mëposhtme:

futni tabelën burimore në rangun e qelizave A4:B14;

vlera e parametrit m do të përcaktohet në qelizën C19. Zgjidhni nga kategoria Statistikore funksionin Slope; futni gamën e qelizave B4:B14 në fushën e vlerave_y_njohur dhe gamën e qelizave A4:A14 në fushën e vlerave_x_njohur. Formula do të futet në qelizën C19: =SLOPE(B4:B14;A4:A14);

duke përdorur një metodë të ngjashme, përcaktohet vlera e parametrit b në qelizën D19. Dhe përmbajtja e tij do të duket kështu: = INTERCEPT(B4:B14;A4:A14). Kështu, vlerat e parametrave m dhe b, të nevojshme për ndërtimin e një regresioni linear, do të ruhen, përkatësisht, në qelizat C19, D19;

atëherë futim formulën e regresionit linear në qelizën C4 në formën: = $ C * A4 + $ D. Në këtë formulë, qelizat C19 dhe D19 shkruhen me referenca absolute (adresa e qelizës nuk duhet të ndryshojë me kopjimin e mundshëm). Shenja e referencës absolute $ mund të shtypet ose nga tastiera ose duke përdorur tastin F4, pasi të vendosni kursorin në adresën e qelizës. Duke përdorur dorezën e mbushjes, kopjoni këtë formulë në gamën e qelizave C4:C17. Marrim serinë e dëshiruar të të dhënave (Fig. 12). Për shkak të faktit se numri i kërkesave është një numër i plotë, duhet të vendosni formatin e numrit në skedën "Numri" të dritares "Formati i qelizës" me numrin e numrave dhjetorë në 0.

2 . Tani le të ndërtojmë një regresion linear të dhënë nga ekuacioni:

y=mx+b

duke përdorur funksionin LINEST.

Për këtë:

futni funksionin LINEST si formulë grupi në gamën e qelizave C20:D20: =(LINEST(B4:B14;A4:A14)). Si rezultat, marrim vlerën e parametrit m në qelizën C20 dhe vlerën e parametrit b në qelizën D20;

shkruani formulën në qelizën D4: =$C*A4+$D;

kopjoni këtë formulë duke përdorur shënuesin e mbushjes në gamën e qelizave D4:D17 dhe merrni serinë e dëshiruar të të dhënave.

3 . Ne ndërtojmë një regresion eksponencial që ka ekuacionin:

me ndihmën e funksionit LGRFPRIBL, kryhet në mënyrë të ngjashme:

në rangun e qelizave C21:D21, futni funksionin LGRFPRIBL si formulë grupi: =( LGRFPRIBL (B4:B14;A4:A14)). Në këtë rast, vlera e parametrit m do të përcaktohet në qelizën C21, dhe vlera e parametrit b do të përcaktohet në qelizën D21;

formula futet në qelizën E4: =$D*$C^A4;

duke përdorur shënuesin mbushës, kjo formulë kopjohet në diapazonin e qelizave E4:E17, ku do të vendoset seria e të dhënave për regresionin eksponencial (shih Fig. 12).

Në fig. 13 tregon një tabelë ku mund të shohim funksionet që përdorim me vargjet e nevojshme të qelizave, si dhe formulat.

Vlera R 2 thirrur koeficienti i përcaktimit.

Detyra e ndërtimit të një varësie regresioni është gjetja e vektorit të koeficientëve m të modelit (1) në të cilin koeficienti R merr vlerën maksimale.

Për të vlerësuar rëndësinë e R, përdoret testi F Fisher, i llogaritur me formulën

Ku n- madhësia e mostrës (numri i eksperimenteve);

k është numri i koeficientëve të modelit.

Nëse F tejkalon një vlerë kritike për të dhënat n Dhe k dhe niveli i pranuar i besimit, atëherë vlera e R konsiderohet e rëndësishme. Tabelat e vlerave kritike të F janë dhënë në librat e referencës mbi statistikat matematikore.

Kështu, rëndësia e R përcaktohet jo vetëm nga vlera e tij, por edhe nga raporti midis numrit të eksperimenteve dhe numrit të koeficientëve (parametrave) të modelit. Në të vërtetë, raporti i korrelacionit për n=2 për një model të thjeshtë linear është 1 (nëpërmjet 2 pikave në plan, gjithmonë mund të vizatoni një vijë të vetme të drejtë). Megjithatë, nëse të dhënat eksperimentale janë variabla të rastësishme, një vlerë e tillë e R duhet t'i besohet me shumë kujdes. Zakonisht, për të marrë një regresion të rëndësishëm R dhe të besueshëm, synohet të sigurohet që numri i eksperimenteve të tejkalojë ndjeshëm numrin e koeficientëve të modelit (n>k).

Për të ndërtuar një model të regresionit linear, duhet:

1) përgatit një listë me n rreshta dhe m kolona që përmbajnë të dhënat eksperimentale (kolona që përmban vlerën e daljes Y duhet të jetë i pari ose i fundit në listë); për shembull, le të marrim të dhënat e detyrës së mëparshme, duke shtuar një kolonë të quajtur "numri i periudhës", duke numëruar numrat e periudhave nga 1 në 12. (këto do të jenë vlerat X)

2) shkoni te menyja Të dhënat/Analiza e të dhënave/Regresioni

Nëse artikulli "Analiza e të dhënave" në menynë "Mjetet" mungon, atëherë duhet të shkoni te artikulli "Shtesa" të së njëjtës meny dhe të kontrolloni kutinë "Paketa e analizës".

3) në kutinë e dialogut "Regresion", vendosni:

intervali i hyrjes Y;

intervali i hyrjes X;

intervali i daljes - qeliza e sipërme e majtë e intervalit në të cilin do të vendosen rezultatet e llogaritjes (rekomandohet ta vendosni në një fletë pune të re);