Metoda e përcaktimit të koeficientëve të katrorëve më të vegjël. Përafrimi i të dhënave eksperimentale

Tabela 4

x n

y n

Tabela 5

y i

0,705

0,495

0,426

0,357

0,368

0,406

0,549

0,768

Tabela 6

№0

x

0,1

0,2

0,3

0,4

0,5

0,6

0,7

0,8

y

3,030

3,142

3,358

3,463

3,772

3,251

3,170

3,665

№ 1

3,314

3,278

3,262

3,292

3,332

3,397

3,487

3,563

№ 2

1,045

1,162

1,264

1,172

1,070

0,898

0,656

0,344

№ 3

6,715

6,735

6,750

6,741

6,645

6,639

6,647

6,612

№ 4

2,325

2,515

2,638

2,700

2,696

2,626

2,491

2,291

№ 5

1.752

1,762

1,777

1,797

1,821

1,850

1,884

1,944

№ 6

1,924

1,710

1,525

1,370

1,264

1,190

1,148

1,127

№ 7

1,025

1,144

1,336

1,419

1,479

1,530

1,568

1,248

№ 8

5,785

5,685

5,605

5,545

5,505

5,480

5,495

5,510

№ 9

4,052

4,092

4,152

4,234

4,338

4,468

4,599

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 parametrat 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ë 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.

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.

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ë prirje 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;

caktoni pikën e kryqëzimit të vijës së prirjes me boshtin Y, për të cilën duhet të aktivizoni kutinë e zgjedhjes Ndërprerja 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, LGRFINB, 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);

4) klikoni "Ok" dhe analizoni rezultatet.

Thelbi i metodës qëndron në faktin se kriteri për cilësinë e zgjidhjes në shqyrtim është shuma e gabimeve në katror, ​​e cila kërkohet të minimizohet. Për ta zbatuar këtë, kërkohet të kryhen sa më shumë matje të një ndryshoreje të rastësishme të panjohur (sa më shumë - sa më e lartë të jetë saktësia e zgjidhjes) dhe një grup i caktuar zgjidhjesh të pritshme, nga të cilat kërkohet të zgjidhet më e mira. . Nëse grupi i zgjidhjeve është i parametrizuar, atëherë duhet gjetur vlera optimale e parametrave.

Pse janë minimizuar katrorët e gabimeve dhe jo vetë gabimet? Fakti është se në shumicën e rasteve gabimet ndodhin në të dy drejtimet: vlerësimi mund të jetë më i madh se matja ose më pak se ai. Nëse shtojmë gabime me shenja të ndryshme, atëherë ato do të anulojnë njëri-tjetrin dhe si rezultat, shuma do të na japë një ide të gabuar për cilësinë e vlerësimit. Shpesh, në mënyrë që vlerësimi përfundimtar të ketë të njëjtin dimension me vlerat e matura, rrënja katrore merret nga shuma e gabimeve në katror.

LSM përdoret në matematikë, në veçanti - në teorinë e probabilitetit dhe statistikat matematikore. Kjo metodë ka aplikimin më të madh në problemet e filtrimit, kur është e nevojshme të ndahet sinjali i dobishëm nga zhurma e mbivendosur mbi të.

Përdoret gjithashtu në analizën matematikore për një paraqitje të përafërt të një funksioni të caktuar me funksione më të thjeshta. Një fushë tjetër e aplikimit të LSM është zgjidhja e sistemeve të ekuacioneve me më pak të panjohura se numri i ekuacioneve.

Kam ardhur me disa aplikacione të tjera shumë të papritura të LSM, për të cilat do të doja të flisja në këtë artikull.

MNC dhe gabime shtypi

Gabimet drejtshkrimore dhe drejtshkrimore janë plagë e përkthyesve automatikë dhe motorëve të kërkimit. Në të vërtetë, nëse fjala ndryshon vetëm me 1 shkronjë, programi e konsideron atë si një fjalë tjetër dhe e përkthen/kërkon gabimisht ose nuk e përkthen/nuk e gjen fare.

Unë kisha një problem të ngjashëm: kishte dy baza të dhënash me adresat e shtëpive të Moskës, dhe ato duhej të kombinoheshin në një. Por adresat janë shkruar në një stil tjetër. Në një bazë të dhënash ishte standardi KLADR (klasifikuesi i adresave gjithë-ruse), për shembull: "BABUSHKINA PILOT UL., D10K3". Dhe në një bazë të dhënash tjetër kishte një stil postar, për shembull: "Shën. Pilot Babushkin, shtëpia 10 ndërtesa 3. Duket se nuk ka gabime në të dyja rastet, dhe automatizimi i procesit është tepër i vështirë (çdo bazë e të dhënave ka 40,000 regjistrime!). Edhe pse kishte mjaft gabime shtypi... Si ta bëni kompjuterin të kuptojë se 2 adresat e mësipërme i përkasin të njëjtës shtëpi? Kjo është ajo ku MNC erdhi në ndihmë për mua.

Cfare kam bere? Pasi gjeta letrën tjetër në adresën e parë, kërkova të njëjtën letër në adresën e dytë. Nëse ata ishin të dy në të njëjtin vend, atëherë supozova se gabimi për atë shkronjë ishte 0. Nëse ata ishin në pozicione ngjitur, atëherë gabimi ishte 1. Nëse kishte një zhvendosje me 2 pozicione, gabimi ishte 2, e kështu me radhë Nëse nuk kishte fare një shkronjë të tillë në adresën tjetër, atëherë gabimi supozohej të ishte n+1, ku n është numri i shkronjave në adresën e parë. Kështu, unë llogarita shumën e gabimeve në katror dhe lidha ato rekorde në të cilat kjo shumë ishte minimale.

Natyrisht, numri i shtëpive dhe ndërtesave u përpunua veçmas. Nuk e di nëse kam shpikur një "biçikletë" tjetër, apo ishte me të vërtetë, por problemi u zgjidh shpejt dhe me efikasitet. Pyes veten nëse kjo metodë përdoret në motorët e kërkimit? Ndoshta përdoret, pasi çdo motor kërkimi që respekton veten, kur takon një fjalë të panjohur, ofron një zëvendësim nga fjalët e njohura ("ndoshta keni dashur të thoni ..."). Megjithatë, ata mund ta bëjnë këtë analizë disi ndryshe.

OLS dhe kërkoni sipas fotografive, fytyrave dhe hartave

Kjo metodë mund të aplikohet edhe për të kërkuar me anë të fotografive, vizatimeve, hartave, madje edhe nga fytyrat e njerëzve.

Foto:

Tani të gjithë motorët e kërkimit, në vend që të kërkojnë sipas imazheve, në fakt përdorin kërkimin sipas titrave të imazhit. Ky është padyshim një shërbim i dobishëm dhe i përshtatshëm, por unë propozoj ta plotësoj atë me një kërkim të vërtetë imazhi.

Prezantohet një foto mostër dhe bëhet një vlerësim për të gjitha imazhet nga shuma e devijimeve në katror të pikave karakteristike. Përcaktimi i këtyre pikave shumë karakteristike është në vetvete një detyrë jo e parëndësishme. Sidoqoftë, është mjaft e zgjidhshme: për shembull, për fytyrat, këto janë cepat e syve, buzët, maja e hundës, vrimat e hundës, skajet dhe qendrat e vetullave, bebëzat, etj.

Duke krahasuar këto parametra, mund të gjeni një fytyrë që është më e ngjashme me mostrën. Unë kam parë tashmë faqe ku funksionon një shërbim i tillë dhe ju mund të gjeni një personazh të famshëm që është më i ngjashëm me foton që sugjeruat, madje të kompozoni një animacion që ju kthen në personazh të famshëm dhe mbrapa. Me siguri e njëjta metodë funksionon edhe në bazat e Ministrisë së Punëve të Brendshme, me imazhe identike të kriminelëve.

Foto: pixabay.com

Po, dhe gjurmët e gishtërinjve mund të kërkohen në të njëjtën mënyrë. Kërkimi i hartës fokusohet në parregullsitë natyrore të objekteve gjeografike - kthesat e lumenjve, vargmalet malore, skicat e brigjeve, pyjeve dhe fushave.

Këtu është një metodë kaq e mrekullueshme dhe e gjithanshme OLS. Jam i sigurt se ju, të dashur lexues, do të jeni në gjendje të gjeni shumë aplikime të pazakonta dhe të papritura të kësaj metode për veten tuaj.

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.

Katroret më të vegjël është një procedurë matematikore për ndërtimin e një ekuacioni linear që i përshtatet më së miri një grupi çiftesh të renditura duke gjetur vlerat për a dhe b, koeficientët në ekuacionin e vijës së drejtë. Qëllimi i metodës së katrorëve më të vegjël është të minimizojë gabimin total në katror ndërmjet vlerave y dhe ŷ. Nëse për secilën pikë përcaktojmë gabimin ŷ, metoda e katrorëve më të vegjël minimizon:

ku n = numri i çifteve të renditura rreth vijës. më të rëndësishmet për të dhënat.

Ky koncept është ilustruar në figurë

Duke gjykuar nga figura, vija që i përshtatet më së miri të dhënave, vija e regresionit, minimizon gabimin total në katror të katër pikave në grafik. Unë do t'ju tregoj se si ta përcaktoni këtë duke përdorur metodën e katrorëve më të vegjël në shembullin e mëposhtëm.

Imagjinoni një çift të ri që kohët e fundit jetojnë së bashku dhe ndajnë një tavolinë tualeti. I riu filloi të vinte re se gjysma e tryezës së tij po tkurej në mënyrë të pandalshme, duke humbur terren nga shkuma e flokëve dhe komplekset e sojës. Gjatë muajve të fundit, djali ka monitoruar nga afër shkallën me të cilën po rritet numri i artikujve në pjesën e saj të tabelës. Tabela më poshtë tregon numrin e artikujve që vajza ka në tavolinën e banjës që janë grumbulluar gjatë muajve të fundit.

Meqenëse qëllimi ynë është të zbulojmë nëse numri i artikujve rritet me kalimin e kohës, "Muaji" do të jetë ndryshorja e pavarur dhe "Numri i artikujve" do të jetë ndryshorja e varur.

Duke përdorur metodën e katrorëve më të vegjël, ne përcaktojmë ekuacionin që i përshtatet më mirë të dhënave duke llogaritur vlerat e a, segmentit në boshtin y dhe b, pjerrësinë e vijës:

a = y cf - bx cf

ku x cf është vlera mesatare e x, ndryshores së pavarur, y cf është vlera mesatare e y, ndryshores së pavarur.

Tabela e mëposhtme përmbledh llogaritjet e nevojshme për këto ekuacione.

Kurba e efektit për shembullin tonë të vaskës do të jepet nga ekuacioni i mëposhtëm:

Meqenëse ekuacioni ynë ka një pjerrësi pozitive prej 0,976, djali ka prova se numri i artikujve në tryezë po rritet me kalimin e kohës me një normë mesatare prej 1 artikulli në muaj. Grafiku tregon lakoren e efektit me çifte të renditura.

Numri i pritshëm i zërave për gjashtëmujorin e ardhshëm (muaji 16) do të llogaritet si më poshtë:

ŷ = 5,13 + 0,976x = 5,13 + 0,976(16) ~ 20,7 = 21 artikuj

Pra, është koha që heroi ynë të ndërmarrë disa veprime.

Funksioni TREND në Excel

Siç mund ta keni marrë me mend, Excel ka një funksion për të llogaritur një vlerë Metoda e katrorëve më të vegjël. Kjo veçori quhet TREND. Sintaksa e tij është si më poshtë:

TREND (vlera të njohura Y; vlera të njohura X; vlera të reja X; konst)

vlerat e njohura të Y - një grup variablash të varur, në rastin tonë, numri i artikujve në tabelë

vlerat e njohura të X - një grup variablash të pavarur, në rastin tonë është një muaj

vlerat e reja X - vlerat e reja X (mujore) për të cilat Funksioni TREND kthen vlerën e pritur të variablave të varur (numrin e artikujve)

const - opsionale. Një vlerë Boolean që specifikon nëse konstanta b kërkohet të jetë 0.

Për shembull, figura tregon funksionin TREND që përdoret për të përcaktuar numrin e pritur të artikujve në tavolinën e banjës për muajin e 16-të.