Metoda e katrorëve më të vegjël (OLS) i përket fushës së analizës së regresionit. 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 të bazuara në 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 duke përdorur një shembull specifik

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

Pra, le të jetë X hapësira e shitjes me pakicë e një dyqani ushqimor, e matur në metra katrorë, dhe Y të jetë qarkullimi vjetor, i matur në miliona rubla.

Kërkohet të bëhet një parashikim se çfarë xhiro (Y) do të ketë dyqani nëse ka këtë apo atë 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 duke përdorur 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. Për më tepër, rezultatet "anormale" nuk mund të përdoren. Në veçanti, një butik i vogël elitar mund të ketë një qarkullim që është disa herë më i madh se qarkullimi i pikave të mëdha të shitjes me pakicë të klasës "masmarket".

Thelbi i metodës

Të dhënat e tabelës mund të përshkruhen në një plan kartezian në formën e pikave 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 që mund të përdorni një polinom shkallë të lartë, por ky opsion nuk është vetëm i vështirë për t'u zbatuar, por edhe thjesht i pasaktë, pasi nuk do të pasqyrojë tendencën kryesore që duhet të zbulohet. Zgjidhja më e arsyeshme është kërkimi i drejtëzës y = ax + b, e cila përafron më së miri të dhënat eksperimentale, ose më saktë, koeficientët a dhe b.

Vlerësimi i saktësisë

Me çdo përafrim, vlerësimi i saktësisë së tij është i një rëndësie të veçantë. Le të shënojmë 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, duhet t'i jepni përparësi asaj me 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 do të ketë edhe ato negative.

Problemi mund të zgjidhet 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 (e zbatuar në Excel duke përdorur dy funksione të integruara), dhe ka provuar prej kohësh efektivitetin e saj.

Metoda me katrorin më të vogël

Excel, siç e dini, ka një funksion të integruar AutoSum 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 vijën e drejtë që përshkruan më së miri varësinë specifike të sasive X dhe Y zbret në llogaritjen e minimumit të një funksioni të dy variablave:

Për ta bërë këtë, ju duhet të barazoni derivatet e pjesshme në lidhje me ndryshoret e reja a dhe b me zero, dhe të zgjidhni një sistem primitiv të përbërë nga dy ekuacione me 2 të panjohura të formës:

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

Duke e zgjidhur atë, për shembull, duke përdorur metodën e Cramer, 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ë një dyqan 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ë zone të caktuar me kredi në dyqan do të paguajë.

Si të zbatoni katrorët më të vegjël në Excel

Excel ka një funksion për llogaritjen e vlerave duke përdorur katrorët 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ë, futni shenjën "=" 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 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 tregtar);
• diapazoni x 1, …x n, d.m.th. madhësia e hapësirës me pakicë;
• vlerat e njohura dhe të panjohura të x, për të cilat duhet të zbuloni madhësinë e qarkullimit (për informacion në lidhje me vendndodhjen e tyre në fletën e punës, shihni më poshtë).

Për më tepër, formula përmban variablin logjik "Const". Nëse futni 1 në fushën përkatëse, kjo do të thotë që ju duhet të kryeni llogaritjet, duke supozuar se b = 0.

Nëse duhet të zbuloni 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" në tastierë.

Disa veçori

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 katrorët më të vegjël. Mjafton vetëm të njohësh disa nga veçoritë e punës së tij. Veçanërisht:

• Nëse rregulloni 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 një varg me x të njohur nuk është specifikuar në dritaren TREND, atëherë kur përdorni funksionin në Excel, programi do ta trajtojë 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ë ndryshorja y.
• Për të nxjerrë një grup vlerash "të parashikuara", shprehja për llogaritjen e trendit duhet të futet si formulë e grupit.
• Nëse vlerat e reja të x nuk janë specifikuar, 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 specifikuar tashmë y.
• Gama që përmban vlerat e reja x duhet të ketë të njëjtat ose më shumë rreshta ose kolona si diapazoni që përmban 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

Analiza e regresionit në Excel zbatohet duke përdorur disa funksione. Njëri prej tyre quhet "PARASHIKIMI". Është e ngjashme 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 ju i njihni formulat në Excel për dummies që ju lejojnë të parashikoni vlerën e ardhshme të një treguesi të veçantë sipas një tendence lineare.

Metoda e katrorëve më të vegjël është një procedurë matematikore për ndërtimin e një ekuacioni linear që do të përshtatet më saktë me një grup prej dy serish numrash. Qëllimi i përdorimit të kësaj metode është të minimizohet gabimi total katror. Excel ka mjete që mund t'ju ndihmojnë të aplikoni këtë metodë në llogaritjet tuaja. Le të kuptojmë se si bëhet kjo.

· Përdorimi i metodës në Excel

o Aktivizimi i shtesës “Solution Search”.

o Kushtet problemore

o Zgjidhje

Duke përdorur metodën në Excel

Metoda e katrorëve më të vegjël (LSM) është një përshkrim matematikor i varësisë së një ndryshore nga një tjetër. Mund të përdoret për parashikim.

Aktivizimi i shtesës Find Solution

Për të përdorur MNC në Excel, duhet të aktivizoni shtesën "Gjeni një zgjidhje", i cili është i çaktivizuar si parazgjedhje.

1. Shkoni te skeda "Dosja".

2. Klikoni mbi emrin e seksionit "Opsione".

3. Në dritaren që hapet, zgjidhni nënseksionin "Shtesa".

4. Në bllok "Kontrolli", e cila ndodhet në fund të dritares, vendosni çelësin në pozicion "Shtesa në Excel"(nëse ka një vlerë të ndryshme) dhe klikoni në butonin "Shko...".

5. Hapet një dritare e vogël. Ne vendosim një shenjë pranë parametrit "Gjeni një zgjidhje". Klikoni në butonin "NE RREGULL".

Tani funksioni Gjetja e një zgjidhjeje në Excel aktivizohet dhe veglat e tij shfaqen në shirit.

Mësim: Gjetja e një zgjidhjeje në Excel

Kushtet e problemit

Le të përshkruajmë përdorimin e LSM duke përdorur një shembull specifik. Kemi dy rreshta numrash x Dhe y, sekuenca e së cilës tregohet në imazhin më poshtë.

Kjo varësi mund të përshkruhet më saktë nga funksioni:

Njëkohësisht dihet se kur x=0 y gjithashtu të barabartë 0 . Prandaj, ky ekuacion mund të përshkruhet nga varësia y=nx.

Duhet të gjejmë shumën minimale të katrorëve të diferencës.

Zgjidhje

Le të kalojmë në një përshkrim të aplikimit të drejtpërdrejtë të metodës.

1. Në të majtë të vlerës së parë x vendosni një numër 1 . Kjo do të jetë një vlerë e përafërt e vlerës së koeficientit të parë n.

2. Në të djathtë të kolonës y shtoni një kolonë tjetër - nx. Në qelizën e parë të kësaj kolone shkruajmë formulën e shumëzimit të koeficientit n për qelizë të ndryshores së parë x. Në të njëjtën kohë, lidhjen me fushën e bëjmë me koeficientin absolut, pasi kjo vlerë nuk do të ndryshojë. Klikoni në butonin Hyni.

3. Duke përdorur shënuesin e mbushjes, kopjoni këtë formulë në të gjithë gamën e tabelës në kolonën më poshtë.

4. Në një qelizë të veçantë, llogaritni shumën e diferencave midis katrorëve të vlerave y Dhe nx. Për ta bërë këtë, klikoni në butonin "Futni funksionin".

5. Në të hapur "Magjistari i funksionit" duke kërkuar një hyrje "SUMMKVARNA". Zgjidhni atë dhe shtypni butonin "NE RREGULL".

6. Hapet dritarja e argumenteve. Në fushë "Array_x" y. Në fushë "Array_y" futni gamën e qelizave të kolonës nx. Për të futur vlerat, thjesht vendosni kursorin në fushë dhe zgjidhni diapazonin përkatës në fletë. Pasi të keni hyrë, klikoni në butonin "NE RREGULL".

7. Shkoni te skeda "Të dhënat". Në shiritin në kutinë e veglave "Analiza" klikoni në butonin "Gjeni një zgjidhje".

8. Hapet dritarja e parametrave për këtë mjet. Në fushë "Optimizo funksionin objektiv" tregoni adresën e qelizës me formulën "SUMMKVARNA". Në parametrin "Para" sigurohuni që ta vendosni çelësin në pozicion "Minimumi". Në fushë "Ndryshimi i qelizave" tregoni adresën me vlerën e koeficientit n. Klikoni në butonin "Gjej nje zgjidhje".

9. Zgjidhja do të shfaqet në qelizën e koeficientit n. Kjo vlerë do të jetë katrori më i vogël i funksionit. Nëse rezultati e kënaq përdoruesin, atëherë klikoni në butonin "NE RREGULL" në një dritare shtesë.

Siç mund ta shihni, aplikimi i metodës së katrorëve më të vegjël është një procedurë matematikore mjaft komplekse. Ne e treguam atë në veprim duke përdorur një shembull të thjeshtë, por ka raste shumë më komplekse. Sidoqoftë, mjetet e Microsoft Excel janë krijuar për të thjeshtuar llogaritjet sa më shumë që të jetë e mundur.

http://multitest.semico.ru/mnk.htm

Dispozitat e përgjithshme

Sa më i vogël të jetë numri në vlerë absolute, aq më e mirë është drejtëza e zgjedhur (2). Si karakteristikë e saktësisë së zgjedhjes së drejtëzës (2), mund të marrim shumën e katrorëve

Kushtet minimale për S do të jenë

	(6)
	(7)

Ekuacionet (6) dhe (7) mund të shkruhen si më poshtë:

	(8)
	(9)

Nga ekuacionet (8) dhe (9) është e lehtë të gjesh a dhe b nga vlerat eksperimentale të xi dhe y i. Drejtëza (2), e përcaktuar nga ekuacionet (8) dhe (9), quhet një vijë e përftuar me metodën e katrorëve më të vegjël (ky emër thekson se shuma e katrorëve S ka një minimum). Ekuacionet (8) dhe (9), nga të cilat përcaktohet drejtëza (2), quhen ekuacione normale.

Ju mund të tregoni një mënyrë të thjeshtë dhe të përgjithshme për të hartuar ekuacione normale. Duke përdorur pikat eksperimentale (1) dhe ekuacionin (2), mund të shkruajmë një sistem ekuacionesh për a dhe b

y 1 =ax 1 +b,
y 2 =ax 2 +b, ...		(10)
y n = sëpatë n + b,

Le të shumëzojmë anën e majtë dhe të djathtë të secilit prej këtyre ekuacioneve me koeficientin e të panjohurës së parë a (d.m.th. me x 1, x 2, ..., x n) dhe të shtojmë ekuacionet që rezultojnë, duke rezultuar në ekuacionin e parë normal (8) .

Le të shumëzojmë anën e majtë dhe të djathtë të secilit prej këtyre ekuacioneve me koeficientin e të panjohurës së dytë b, d.m.th. me 1, dhe shtoni ekuacionet rezultuese, rezultati është ekuacioni i dytë normal (9).

Kjo metodë e marrjes së ekuacioneve normale është e përgjithshme: është e përshtatshme, për shembull, për funksionin

ka një vlerë konstante dhe ajo duhet të përcaktohet nga të dhënat eksperimentale (1).

Sistemi i ekuacioneve për k mund të shkruhet:

Gjeni vijën e drejtë (2) duke përdorur metodën e katrorëve më të vegjël.

Zgjidhje. Ne gjejme:

X i =21, y i =46.3, x i 2 =91, x i y i =179.1.

Shkruajmë ekuacionet (8) dhe (9)91a+21b=179.1,

21a+6b=46.3, nga këtu gjejmë
a=0.98 b=4.3.

Metoda me katrorin më të vogël përdoret për të vlerësuar parametrat e ekuacionit të regresionit.

Një nga metodat për studimin e marrëdhënieve stokastike midis karakteristikave është analiza e regresionit.
Analiza e regresionit është nxjerrja e një ekuacioni të regresionit, me ndihmën e të cilit gjendet vlera mesatare e një ndryshoreje të rastësishme (atributi i rezultatit) nëse dihet vlera e një ndryshoreje tjetër (ose të tjera) (faktor-atribute). Ai përfshin hapat e mëposhtëm:

1. përzgjedhja e formës së lidhjes (lloji i ekuacionit të regresionit analitik);
2. vlerësimi i parametrave të ekuacionit;
3. vlerësimi i cilësisë së ekuacionit të regresionit analitik.

Më shpesh, një formë lineare përdoret për të përshkruar marrëdhënien statistikore të veçorive. Fokusi në marrëdhëniet lineare shpjegohet nga interpretimi i qartë ekonomik i parametrave të tij, variacioni i kufizuar i variablave dhe fakti që në shumicën e rasteve format jolineare të marrëdhënieve konvertohen (me logaritëm ose zëvendësim të variablave) në një formë lineare për të kryer llogaritjet. .
Në rastin e një lidhjeje lineare dyshe, ekuacioni i regresionit do të marrë formën: y i =a+b·x i +u i . Parametrat a dhe b të këtij ekuacioni janë vlerësuar nga të dhënat e vrojtimit statistikor x dhe y. Rezultati i një vlerësimi të tillë është ekuacioni: , ku , janë vlerësimet e parametrave a dhe b , është vlera e atributit rezultues (variabli) i marrë nga ekuacioni i regresionit (vlera e llogaritur).

Më shpesh përdoret për të vlerësuar parametrat metoda e katrorëve më të vogël (LSM).
Metoda e katrorëve më të vegjël siguron vlerësimet më të mira (të qëndrueshme, efikase dhe të paanshme) të parametrave të ekuacionit të regresionit. Por vetëm nëse plotësohen supozime të caktuara në lidhje me termin e rastësishëm (u) dhe variablin e pavarur (x) (shih supozimet OLS).

Problemi i vlerësimit të parametrave të një ekuacioni të çiftit linear duke përdorur metodën e katrorëve më të vegjëlështë si më poshtë: për të marrë vlerësime të tilla të parametrave , në të cilat shuma e devijimeve në katror të vlerave aktuale të karakteristikës rezultante - y i nga vlerat e llogaritura - është minimale.
Formalisht Kriteri OLS mund të shkruhet kështu: .

Klasifikimi i metodave të katrorëve më të vegjël

1. Metoda me katrorin më të vogël.
2. Metoda e gjasave maksimale (për një model normal klasik të regresionit linear, është postuluar normaliteti i mbetjeve të regresionit).
3. Metoda e përgjithësuar e katrorëve më të vegjël OLS përdoret në rastin e autokorrelacionit të gabimeve dhe në rastin e heteroskedasticitetit.
4. Metoda e katrorëve më të vegjël të ponderuar (një rast i veçantë i OLS me mbetje heteroskedastike).

Le të ilustrojmë çështjen metoda klasike e katrorëve më të vegjël në mënyrë grafike. Për ta bërë këtë, ne do të ndërtojmë një grafik shpërndarjeje bazuar në të dhënat e vëzhgimit (x i, y i, i=1;n) në një sistem koordinativ drejtkëndor (një grafik i tillë shpërndarjeje quhet fushë korrelacioni). Le të përpiqemi të zgjedhim një vijë të drejtë që është më afër pikave të fushës së korrelacionit. Sipas metodës së katrorëve më të vegjël, vija zgjidhet në mënyrë që shuma e katrorëve të distancave vertikale ndërmjet pikave të fushës së korrelacionit dhe kësaj linje të jetë minimale.

Shënimi matematikor për këtë problem: .
Vlerat e y i dhe x i =1...n janë të njohura për ne; këto janë të dhëna vëzhgimi. Në funksionin S ato paraqesin konstante. Variablat në këtë funksion janë vlerësimet e kërkuara të parametrave - , . Për të gjetur minimumin e një funksioni të dy ndryshoreve, është e nevojshme të llogariten derivatet e pjesshme të këtij funksioni për secilin prej parametrave dhe t'i barazojmë me zero, d.m.th. .
Si rezultat, marrim një sistem prej 2 ekuacionesh normale lineare:
Duke zgjidhur këtë sistem, gjejmë vlerësimet e kërkuara të parametrave:

Korrektësia e llogaritjes së parametrave të ekuacionit të regresionit mund të kontrollohet duke krahasuar shumat (mund të ketë disa mospërputhje për shkak të rrumbullakimit të llogaritjeve).
Për të llogaritur vlerësimet e parametrave, mund të ndërtoni Tabelën 1.
Shenja e koeficientit të regresionit b tregon drejtimin e marrëdhënies (nëse b >0, marrëdhënia është e drejtpërdrejtë, nëse b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalisht, vlera e parametrit a është vlera mesatare e y me x e barabartë me zero. Nëse atributi-faktor nuk ka dhe nuk mund të ketë një vlerë zero, atëherë interpretimi i mësipërm i parametrit a nuk ka kuptim.

Vlerësimi i afërsisë së marrëdhënies ndërmjet karakteristikave kryhet duke përdorur koeficientin e korrelacionit të çiftit linear - r x,y. Mund të llogaritet duke përdorur formulën: . Përveç kësaj, koeficienti i korrelacionit të çiftit linear mund të përcaktohet përmes koeficientit të regresionit b: .
Gama e vlerave të pranueshme të koeficientit të korrelacionit të çiftit linear është nga -1 në +1. Shenja e koeficientit të korrelacionit tregon drejtimin e marrëdhënies. Nëse r x, y >0, atëherë lidhja është e drejtpërdrejtë; nëse r x, y<0, то связь обратная.
Nëse ky koeficient është afër unitetit në madhësi, atëherë marrëdhënia midis karakteristikave mund të interpretohet si një linjë mjaft e ngushtë. Nëse moduli i tij është i barabartë me një ê r x, y ê =1, atëherë lidhja ndërmjet karakteristikave është funksionale lineare. Nëse tiparet x dhe y janë linearisht të pavarura, atëherë r x,y është afër 0.
Për të llogaritur r x, y, mund të përdorni gjithashtu tabelën 1.

Për të vlerësuar cilësinë e ekuacionit të regresionit që rezulton, llogaritni koeficientin teorik të përcaktimit - R 2 yx:

,
ku d 2 është varianca e y e shpjeguar nga ekuacioni i regresionit;
e 2 - varianca e mbetur (e pashpjegueshme nga ekuacioni i regresionit) të y;
s 2 y - varianca totale (totali) e y.
Koeficienti i përcaktimit karakterizon proporcionin e variacionit (dispersionit) të atributit rezultant y të shpjeguar me regresion (dhe, rrjedhimisht, faktorin x) në variacionin total (dispersion) y. Koeficienti i përcaktimit R 2 yx merr vlera nga 0 në 1. Prandaj, vlera 1-R 2 yx karakterizon proporcionin e variancës y të shkaktuar nga ndikimi i faktorëve të tjerë që nuk merren parasysh në model dhe gabimet e specifikimit.
Me regresion linear të çiftuar, R 2 yx =r 2 yx.

Metoda e katrorëve më të vegjël (LSM)

Një sistem m ekuacionesh lineare me n të panjohura ka formën:

Tre raste janë të mundshme: m n. Rasti kur m=n është shqyrtuar në paragrafët e mëparshëm. Kur m

Nëse m>n dhe sistemi është konsistent, atëherë matrica A ka të paktën m - n rreshta të varur linearisht. Këtu zgjidhja mund të merret duke zgjedhur n çdo ekuacion linear të pavarur (nëse ato ekzistojnë) dhe duke zbatuar formulën X = A -1 CV, domethënë duke e reduktuar problemin në një problem të zgjidhur më parë. Në këtë rast, zgjidhja që rezulton gjithmonë do të kënaqë ekuacionet e mbetura m - n.

Sidoqoftë, kur përdorni një kompjuter, është më i përshtatshëm të përdorni një qasje më të përgjithshme - metoda e katrorëve më të vegjël.

Metoda algjebrike e katrorëve më të vegjël

Metoda e katrorëve më të vegjël algjebrikë është një metodë për zgjidhjen e sistemeve të ekuacioneve lineare

duke minimizuar normën Euklidiane

Sëpatë? b? >inf. (1.2)

Analiza e të dhënave eksperimentale

Le të shqyrtojmë disa eksperimente gjatë të cilave në momente të kohës

Për shembull, matet temperatura Q(t). Lërini rezultatet e matjes të specifikohen nga një grup

Le të supozojmë se kushtet eksperimentale janë të tilla që matjet kryhen me një gabim të njohur. Në këto raste, ligji i ndryshimit të temperaturës Q(t) kërkohet duke përdorur një polinom të caktuar

P(t) = + + + ... +,

përcaktimi i koeficientëve të panjohur, ..., nga konsideratat që vlera E(, ...,), e përcaktuar nga barazia

Gauss algjebrike exel përafrim

mori vlerën minimale. Meqenëse shuma e katrorëve është minimizuar, kjo metodë quhet përafrim i katrorëve më të vegjël me të dhënat.

Nëse zëvendësojmë P(t) me shprehjen e tij, marrim

Le të vendosim detyrën e përcaktimit të një grupi në mënyrë që vlera të jetë minimale, d.m.th. Le të përcaktojmë grupin duke përdorur metodën e katrorëve më të vegjël. Për ta bërë këtë, ne barazojmë derivatet e pjesshme me zero:

Nëse futni matricën m × n A = (), i = 1, 2..., m; j = 1, 2, ..., n, ku

I = 1, 2..., m; j = 1, 2, ..., n,

atëherë barazia e shkruar do të marrë formën

Le të rishkruajmë barazinë e shkruar për sa i përket veprimeve me matricat. Nga përkufizimi i shumëzimit të një matrice me një kolonë, ne kemi

Për një matricë të transpozuar, një marrëdhënie e ngjashme duket kështu

Le të prezantojmë shënimin: do të shënojmë komponentin i-të të vektorit Ax Në përputhje me barazitë e matricës së shkruar, do të kemi

Në formën e matricës kjo barazi mund të rishkruhet si

A T x=A T B (1.3)

Këtu A është një matricë m×n drejtkëndore. Për më tepër, në problemet e përafrimit të të dhënave, si rregull, m > n. Ekuacioni (1.3) quhet ekuacion normal.

Ishte e mundur që në fillim, duke përdorur normën Euklidiane të vektorëve, për të shkruar problemin në formën e një matrice ekuivalente:

Qëllimi ynë është të minimizojmë këtë funksion në x. Në mënyrë që të arrihet një minimum në një pikë zgjidhjeje, derivatet e parë në lidhje me x në këtë pikë duhet të jenë të barabarta me zero. Derivatet e këtij funksioni janë

2A T B + 2A T Ax

prandaj zgjidhja duhet të kënaqë sistemin e ekuacioneve lineare

(A T A)x = (A T B).

Këto ekuacione quhen ekuacione normale. Nëse A është një matricë m× n, atëherë A>A - n × n është një matricë, d.m.th. Matrica e një ekuacioni normal është gjithmonë një matricë simetrike katrore. Për më tepër, ajo ka vetinë e definicionit pozitiv në kuptimin që (A>Ax, x) = (Ax, Ax) ? 0.

Komentoni. Ndonjëherë zgjidhja e një ekuacioni të formës (1.3) quhet zgjidhje e sistemit Ax = B, ku A është një matricë drejtkëndore m × n (m > n) duke përdorur metodën e katrorëve më të vegjël.

Problemi i katrorëve më të vegjël mund të interpretohet grafikisht si minimizimi i distancave vertikale nga pikat e të dhënave në një kurbë modeli (shih Figurën 1.1). Kjo ide bazohet në supozimin se të gjitha gabimet në përafrim korrespondojnë me gabimet në vëzhgimet. Nëse ka edhe gabime në variablat e pavarur, atëherë mund të jetë më e përshtatshme të minimizohet distanca Euklidiane nga të dhënat në model.

MNC në Excel

Algoritmi i mëposhtëm për zbatimin e OLS në Excel supozon se të gjitha të dhënat fillestare janë tashmë të njohura. Ne i shumëzojmë të dyja anët e ekuacionit të matricës AЧX=B të sistemit në të majtë me matricën e transpozuar të sistemit А Т:

A T AX=A T B

Pastaj i shumëzojmë të dyja anët e ekuacionit në të majtë me matricën (A T A) -1. Nëse kjo matricë ekziston, atëherë definohet sistemi. Duke marrë parasysh atë

(A T A) -1 *(A T A)=E, marrim

X=(A T A) -1 A T B.

Ekuacioni i matricës që rezulton është një zgjidhje e një sistemi m ekuacionesh lineare me n të panjohura për m>n.

Le të shqyrtojmë zbatimin e algoritmit të mësipërm duke përdorur një shembull specifik.

Shembull. Le të jetë e nevojshme për të zgjidhur sistemin

Në Excel, fleta e zgjidhjes në modalitetin e shfaqjes së formulës për këtë problem duket si kjo:

Rezultatet e llogaritjes:

Vektori i kërkuar X ndodhet në intervalin E11:E12.

Gjatë zgjidhjes së një sistemi të caktuar ekuacionesh lineare, u përdorën funksionet e mëposhtme:

1. MOBR - kthen matricën e kundërt për matricën e ruajtur në grup.

Sintaksa: MOBR (array).

Vargu është një grup numerik me numër të barabartë rreshtash dhe kolonash.

2. MULTIPULT - kthen produktin e matricave (matricat ruhen në vargje). Rezultati është një grup me të njëjtin numër rreshtash si grupi1 dhe të njëjtin numër kolonash si grupi2.

Sintaksa: MULTIPLE(array1,array2).

Array1, array2 janë vargje të shumëfishueshme.

Pasi të keni futur një funksion në qelizën e sipërme majtas të një vargu grupi, zgjidhni grupin, duke filluar me qelizën që përmban formulën, shtypni F2 dhe më pas shtypni CTRL+SHIFT+ENTER.

3. TRANSPORT - konverton një grup vertikal të qelizave në një horizontal, ose anasjelltas. Si rezultat i përdorimit të këtij funksioni, shfaqet një grup me numrin e rreshtave të barabartë me numrin e kolonave të grupit origjinal dhe numrin e kolonave të barabartë me numrin e rreshtave të grupit fillestar.

4.1. Përdorimi i funksioneve të integruara

Llogaritja koeficientët e regresionit kryhet duke përdorur funksionin

LINEST(Vlerat_y; x-vlerat; Konst; statistikat),

Vlerat_y- grup vlerash y,

x-vlerat- grup opsional vlerash x, nëse grupi Xështë lënë jashtë, supozohet se ky është një grup (1;2;3;...) me të njëjtën madhësi si Vlerat_y,

Konst- një vlerë boolean që tregon nëse kërkohet konstanta b ishte e barabartë me 0. Nëse Konst ka kuptimin E VËRTETË ose të lënë pas dore b llogaritet në mënyrën e zakonshme. Nëse argumenti Konstështë E rreme, atëherë b supozohet të jetë 0 dhe vlerat a zgjidhen në mënyrë që relacioni të përmbushet y=sëpatë.

Statistikatështë një vlerë boolean që tregon nëse kërkohen të kthehen statistika shtesë të regresionit. Nëse argumenti Statistikat ka kuptimin E VËRTETË, pastaj funksioni LINEST kthen statistika shtesë të regresionit. Nëse argumenti Statistikat ka kuptimin GENJEJTJE ose është lënë jashtë, atëherë funksioni LINEST kthen vetëm koeficientin a dhe konstante b.

Duhet mbajtur mend se rezultati i funksioneve LINEST()është një grup vlerash - një grup.

Për llogaritjen koeficienti i korrelacionit përdoret funksioni

KORREL(Vargu 1;Array2),

duke kthyer vlerat e koeficientit të korrelacionit, ku Vargu 1- grup vlerash y, Array2- grup vlerash x. Vargu 1 Dhe Array2 duhet të ketë të njëjtën madhësi.

SHEMBULL 1. Varësia y(x) është paraqitur në tabelë. Ndërtoni vija e regresionit dhe llogarisni koeficienti i korrelacionit.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Le të fusim një tabelë vlerash në një fletë MS Excel dhe të ndërtojmë një komplot shpërndarjeje. Fleta e punës do të marrë formën e treguar në Fig. 2.

Për të llogaritur vlerat e koeficientëve të regresionit A Dhe b zgjidhni qelizat A7:B7, Le të shkojmë te magjistari i funksionit dhe në kategori Statistikore zgjidhni një funksion LINEST. Le të plotësojmë kutinë e dialogut që shfaqet siç tregohet në Fig. 3 dhe shtypni Ne rregull.

Si rezultat, vlera e llogaritur do të shfaqet vetëm në qelizë A6(Fig. 4). Në mënyrë që vlera të shfaqet në qelizë B6 duhet të futni modalitetin e redaktimit (kyç F2), dhe më pas shtypni kombinimin e tastit CTRL+SHIFT+ENTER.

Për të llogaritur vlerën e koeficientit të korrelacionit në një qelizë C6 u prezantua formula e mëposhtme:

C7=CORREL(B3:J3;B2:J2).

Njohja e koeficientëve të regresionit A Dhe b le të llogarisim vlerat e funksionit y=sëpatë+b për të dhënë x. Për ta bërë këtë, ne prezantojmë formulën

B5=$A$7*B2+$B$7

dhe kopjojeni atë në varg C5: J5(Fig. 5).

Le të vizatojmë vijën e regresionit në diagram. Zgjidhni pikat eksperimentale në grafik, klikoni me të djathtën dhe zgjidhni komandën Të dhënat fillestare. Në dialog box-in që shfaqet (Fig. 5), zgjidhni skedën Rreshti dhe klikoni në butonin Shtoni. Le të plotësojmë fushat e hyrjes siç tregohet në Fig. 6 dhe shtypni butonin Ne rregull. Një linjë regresioni do të shtohet në grafikun e të dhënave eksperimentale. Si parazgjedhje, grafiku i tij do të vizatohet si pika që nuk lidhen me vija zbutëse.

Oriz. 6

Për të ndryshuar pamjen e vijës së regresionit, kryeni hapat e mëposhtëm. Klikoni me të djathtën në pikat që përshkruajnë grafikun e linjës dhe zgjidhni komandën Lloji i grafikut dhe vendosni llojin e diagramit të shpërndarjes, siç tregohet në Fig. 7.

Lloji, ngjyra dhe trashësia e linjës mund të ndryshohen si më poshtë. Zgjidhni një rresht në diagram, kliko me të djathtën dhe zgjidhni komandën në menynë e kontekstit Formati i serisë së të dhënave... Tjetra, bëni cilësimet, për shembull, siç tregohet në Fig. 8.

Si rezultat i të gjitha transformimeve, marrim një grafik të të dhënave eksperimentale dhe një linjë regresioni në një zonë grafike (Fig. 9).

4.2. Përdorimi i një linje trendi.

Ndërtimi i varësive të ndryshme përafruese në MS Excel zbatohet si një veçori grafiku - linjë trendi.

SHEMBULL 2. Si rezultat i eksperimentit, u përcaktua një varësi e caktuar tabelare.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Zgjidhni dhe ndërtoni një varësi të përafërt. Ndërtoni grafikë të varësive analitike tabelare dhe të zgjedhura.

Zgjidhja e problemit mund të ndahet në fazat e mëposhtme: futja e të dhënave fillestare, ndërtimi i një grafiku të shpërndarjes dhe shtimi i një linje trendi në këtë grafik.

Le ta shohim këtë proces në detaje. Le të fusim të dhënat fillestare në fletën e punës dhe të vizatojmë të dhënat eksperimentale. Më pas, zgjidhni pikat eksperimentale në grafik, kliko me të djathtën dhe përdor komandën Shtoni l linjë trendi(Fig. 10).

Kutia e dialogut që shfaqet ju lejon të krijoni një varësi të përafërt.

Skeda e parë (Fig. 11) e kësaj dritareje tregon llojin e varësisë së përafërt.

Në të dytën (Fig. 12) përcaktohen parametrat e ndërtimit:

· emri i varësisë së përafërt;

· parashikimi përpara (prapa) nga n njësi (ky parametër përcakton se sa njësi përpara (prapa) duhet të zgjerohet linja e trendit);

nëse do të tregohet pika e prerjes së një lakore me një vijë të drejtë y=konst;

· të tregojë funksionin e përafërt në diagram ose jo (opsioni për të treguar ekuacionin në diagram);

· nëse do të vendoset vlera e devijimit standard në diagram apo jo (opsioni për të vendosur vlerën e besueshmërisë së përafrimit në diagram).

Le të zgjedhim një polinom të shkallës së dytë si një varësi të përafërt (Fig. 11) dhe të shfaqim ekuacionin që përshkruan këtë polinom në një grafik (Fig. 12). Diagrami që rezulton është paraqitur në Fig. 13.

Në mënyrë të ngjashme duke përdorur linjat e trendit ju mund të zgjidhni parametrat e varësive të tilla si

lineare y=a∙x+b,

logaritmike y=a∙ln(x)+b,

· eksponenciale y=a∙e b,

· qetësues y=a∙x b,

polinom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d dhe kështu me radhë, deri në një polinom të shkallës së 6-të përfshirëse,

· filtrim linear.

4.3. Përdorimi i një blloku zgjidhës

Me interes të rëndësishëm është zbatimi në MS Excel i përzgjedhjes së parametrave duke përdorur metodën e katrorëve më të vegjël duke përdorur një bllok zgjidhës. Kjo teknikë ju lejon të zgjidhni parametrat e një funksioni të çdo lloji. Le ta shqyrtojmë këtë mundësi duke përdorur si shembull problemin e mëposhtëm.

SHEMBULL 3. Si rezultat i eksperimentit u përftua varësia z(t), e paraqitur në tabelë

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Zgjidhni koeficientët e varësisë Z(t)=Në 4 +Bt 3 +Ct 2 +Dt+K Metoda e katrorëve më të vegjël.

Ky problem është i barabartë me problemin e gjetjes së minimumit të një funksioni prej pesë variablash

Le të shqyrtojmë procesin e zgjidhjes së problemit të optimizimit (Fig. 14).

Lërini vlerat A, NË, ME, D Dhe TE të ruajtura në qeliza A7:E7. Le të llogarisim vlerat teorike të funksionit Z(t)=Në 4 + Bt 3 + Ct 2 + Dt + K për të dhënë t(B2: J2). Për ta bërë këtë, në qelizë B4 shkruani vlerën e funksionit në pikën e parë (qeliza B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Le ta kopjojmë këtë formulë në diapazonin C4: J4 dhe merrni vlerën e pritur të funksionit në pikat, abshisat e të cilave ruhen në qeliza B2: J2.

Në qelizë B5 Le të prezantojmë një formulë që llogarit katrorin e diferencës midis pikave eksperimentale dhe të llogaritura:

B5=(B4-B3)^2,

dhe kopjojeni atë në varg C5: J5. Në një qeli F7 do të ruajmë gabimin total në katror (10). Për ta bërë këtë, futni formulën:

F7 = SUM(B5:J5).

Le të përdorim komandën Service®Kërko një zgjidhje dhe zgjidhni problemin e optimizimit pa kufizime. Le të plotësojmë në përputhje me rrethanat fushat e hyrjes në kutinë e dialogut të paraqitur në Fig. 14 dhe shtypni butonin Ekzekutoni. Nëse gjendet një zgjidhje, dritarja e treguar në Fig. 15.

Rezultati i bllokut të vendimit do të dalë në qeliza A7:E7vlerat e parametrave funksione Z(t)=Në 4 + Bt 3 + Ct 2 + Dt + K. Në qeliza B4: J4 marrim vlera e pritur e funksionit në pikat e fillimit. Në një qeli F7 do të ruhen gabim total katror.

Ju mund të shfaqni pika eksperimentale dhe një vijë të përshtatur në një zonë grafike duke zgjedhur një diapazon B2: J4, telefononi Magjistari i grafikut dhe më pas format pamjen grafikët e marrë.

Oriz. 17 shfaq fletën e punës MS Excel pasi të jenë kryer llogaritjet.

5. REFERENCAT

1. Alekseev E.R., Chesnokova O.V., Zgjidhja e problemeve të matematikës llogaritëse në paketat Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 f. :il. – (Tutorial)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, zgjidhja e problemeve inxhinierike dhe matematikore. –M., BINOM, 2008.–260 f.

3. Berezin I.S., Zhidkov N.P., Metodat llogaritëse – M.: Nauka, 1966. – 632 f.

4. Garnaev A.Yu., Përdorimi i MS EXCEL dhe VBA në ekonomi dhe financa. – Shën Petersburg: BHV - Petersburg, 1999.–332 f.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Metodat numerike të analizës.– M.: Nauka, 1967. – 368 f.

6. Korn G., Korn T., Manual i matematikës për shkencëtarët dhe inxhinierët.- M., 1970, 720 f.

7. Alekseev E.R., Chesnokova O.V. Udhëzime për kryerjen e punëve laboratorike në MS EXCEL. Për studentët e të gjitha specialiteteve. Donetsk, DonNTU, 2004. 112 f.