Metoda katrorët më të vegjël(LSM) bazohet në minimizimin e shumës së devijimeve në katror të funksionit të zgjedhur nga të dhënat në studim. Në këtë artikull ne do të përafrojmë të dhënat e disponueshme duke përdorur një funksion lineary = a x + b .

Metoda me katrorin më të vogël(anglisht) E zakonshme Më së paku Sheshe , O.L.S.) është një nga metodat bazë të analizës së regresionit për sa i përket vlerësimit të parametrave të panjohur modelet e regresionit sipas të dhënave të mostrës.

Le të shqyrtojmë përafrimin sipas funksioneve që varen vetëm nga një ndryshore:

• Linear: y=ax+b (ky artikull)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

shënim: Rastet e përafrimit me një polinom nga shkalla e 3-të në të 6-të janë shqyrtuar në këtë artikull. Këtu merret parasysh përafrimi me një polinom trigonometrik.

Varësia lineare

Ne jemi të interesuar për lidhjen midis 2 variablave X Dhe y. Ekziston një supozim se y varet nga X sipas ligjit linear y = sëpatë + b. Për të përcaktuar parametrat e kësaj marrëdhënieje, studiuesi bëri vëzhgime: për secilën vlerë të x i, u bë një matje e y i (shih skedarin e shembullit). Prandaj, le të ketë 20 çifte vlerash (x i; y i).

Shënim: Nëse hapi i ndryshimit është X është konstante, pastaj për të ndërtuar shpërndajnë parcela mund të përdoret, nëse jo, atëherë duhet të përdorni llojin e grafikut Vend .

Nga diagrami është e qartë se marrëdhënia ndërmjet variablave është afër lineare. Për të kuptuar se cila nga shumë vija të drejta përshkruan më "korrekt" marrëdhënien midis variablave, është e nevojshme të përcaktohet kriteri me të cilin do të krahasohen vijat.

Si kriter i tillë përdorim shprehjen:

Ku ŷ i = a * x i + b ; n – numri i çifteve të vlerave (në rastin tonë n=20)

Shprehja e mësipërme është shuma e distancave në katror midis vlerave të vëzhguara të y i dhe ŷ i dhe shpesh shënohet si SSE ( Shuma e Në katror Gabimet (Mbetjet), shuma e gabimeve në katror (të mbetura)) .

Metoda me katrorin më të vogëlështë të zgjidhni një linjë të tillë ŷ = sëpatë + b, për të cilën shprehja e mësipërme merr vlerën minimale.

Shënim:Çdo linjë në hapësirën dy-dimensionale përcaktohet në mënyrë unike nga vlerat e 2 parametrave: a (pjerrësi) dhe b (ndërrim).

Besohet se sa më e vogël të jetë shuma e distancave në katror, ​​aq më mirë linja përkatëse përafron të dhënat e disponueshme dhe mund të përdoret më tej për të parashikuar vlerat e y nga ndryshorja x. Është e qartë se edhe nëse në realitet nuk ka lidhje midis variablave ose marrëdhënia është jolineare, atëherë OLS do të zgjedhë përsëri linjën "më të mirë". Kështu, metoda e katrorëve më të vegjël nuk thotë asgjë për praninë e një marrëdhënieje reale midis variablave; metoda thjesht ju lejon të zgjidhni parametra të tillë funksioni a Dhe b , për të cilën shprehja e mësipërme është minimale.

Duke kryer operacione matematikore jo shumë komplekse (për më shumë detaje, shihni), mund të llogaritni parametrat a Dhe b :

Siç shihet nga formula, parametri a përfaqëson raportin e kovariancës dhe , prandaj në MS EXCEL për të llogaritur parametrin A Ju mund të përdorni formulat e mëposhtme (shih Shembulli i skedarit të fletës lineare):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) ose

= KOVARIANCA.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Gjithashtu për të llogaritur parametrin A mund të përdorni formulën = TILT(C26:C45;B26:B45). Për parametrin b përdorni formulën = LEG(C26:C45;B26:B45) .

Së fundi, funksioni LINEST() ju lejon të llogaritni të dy parametrat në të njëjtën kohë. Për të futur një formulë LINEST(C26:C45;B26:B45) Ju duhet të zgjidhni 2 qeliza në një rresht dhe të klikoni CTRL + SHIFT + HYN(shih artikullin rreth). Vlera do të kthehet në qelizën e majtë A , ne te djathte - b .

shënim: Për të shmangur ngatërrimin me hyrjen formulat e vargjeve do t'ju duhet të përdorni gjithashtu funksionin INDEX(). Formula = INDEX(LINEST(C26:C45,B26:B45),1) ose thjesht = LINEST(C26:C45;B26:B45) do të kthejë parametrin përgjegjës për pjerrësinë e linjës, d.m.th. A . Formula = INDEX(LINEST(C26:C45,B26:B45),2) do të kthejë parametrin përgjegjës për kryqëzimin e linjës me boshtin Y, d.m.th. b .

Pas llogaritjes së parametrave, diagrami i shpërndarjes mund të vizatoni vijën përkatëse.

Një mënyrë tjetër për të vizatuar një vijë të drejtë duke përdorur metodën e katrorëve më të vegjël është mjeti grafik Linja e trendit. Për ta bërë këtë, zgjidhni diagramin, zgjidhni nga menyja Skeda e paraqitjes, V Analiza në grup klikoni Linja e trendit, pastaj Përafrim linear .

Duke kontrolluar kutinë "shfaq ekuacionin në diagram" në kutinë e dialogut, mund të siguroheni që parametrat e gjetur më sipër përkojnë me vlerat në diagram.

shënim: Në mënyrë që parametrat të përputhen, lloji i diagramit duhet të jetë . Çështja është se kur ndërtohet një diagram Orari Vlerat e boshtit X nuk mund të specifikohen nga përdoruesi (përdoruesi mund të specifikojë vetëm etiketat që nuk ndikojnë në vendndodhjen e pikave). Në vend të vlerave X, përdoret sekuenca 1; 2; 3; ... (për kategoritë e numërimit). Prandaj, nëse ndërtoni linjë trendi në një diagram tip Orari, atëherë në vend të vlerave aktuale të X do të përdoren vlerat e kësaj sekuence, e cila do të çojë në një rezultat të pasaktë (përveç nëse, sigurisht, vlerat aktuale X nuk përputhet me sekuencën 1; 2; 3; ...).

Ka shumë përdorime pasi mundëson përfaqësim të përafërt funksioni i dhënë të tjerat janë 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 është 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 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 statistika 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ë analiza e regresionit(në Excel zbatimi i tij kryhet duke përdorur dy funksione të integruara), dhe ka dëshmuar prej kohësh efektivitetin e tij.

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ë shpërblehet.

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ë në këtë rast të dhënat 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 tashmë parametrat e dhënë 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

Zbatuar 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 (LSM)

Sistemi m ekuacionet 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 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 - kthehet matricë e anasjelltë për një matricë të ruajtur në një 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 i 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.

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 ishte prezantuar formulën 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.

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.

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ë ndërtoni një marrëdhënie 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 mjetit të analizës së opsioneve: Gjetja e një zgjidhjeje.

Me interes të rëndësishëm është zbatimi në MS Excel i përzgjedhjes së parametrave të një marrëdhënie funksionale duke përdorur metodën e katrorëve më të vegjël duke përdorur mjetin e analizës së opsioneve: Kërko për një zgjidhje. 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ë

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 formatoni pamjen e grafikëve që rezultojnë.

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