Përafrimi i funksionit duke përdorur metodën e katrorëve më të vegjël guap. Përafrimi i të dhënave eksperimentale

Programimi dinamik është aparate matematikore, projektuar për zgjidhje efektive disa kategori problemesh programimi matematikor. Kjo klasë karakterizohet nga mundësia e ndarjes natyrale (dhe nganjëherë artificiale) të të gjithë operacionit në një seri faza të ndërlidhura. Termi "dinamik" në emër të metodës me sa duket lindi sepse fazat supozohet se janë të ndara në kohë. Megjithatë, fazat mund të jenë elementë të një operacioni, jo në asnjë mënyrë mik i lidhur me një tregues tjetër të kohës. Sidoqoftë, metoda për zgjidhjen e problemeve të tilla shumëfazore është e njëjtë, dhe emri i saj është bërë përgjithësisht i pranuar, megjithëse në disa burime quhet programim me shumë faza.

Modelet programim dinamik mund të përdoret, për shembull, kur zhvillohen rregulla të menaxhimit të inventarit që përcaktojnë momentin e rimbushjes së stoqeve dhe madhësinë e urdhrit të rimbushjes; gjatë zhvillimit të parimeve planifikimin prodhimi dhe barazimi i punësimit në kushtet e luhatshmërisë së kërkesës për produkte; kur shpërndahen investimet e pakta kapitale ndërmjet zonave të reja të mundshme të përdorimit të tyre; gjatë përpilimit planet kalendarike rryma dhe riparimi i pajisjeve komplekse dhe zëvendësimi i tyre; gjatë zhvillimit rregullat afatgjata zëvendësimi i aseteve fikse të dekomisionuara etj.

Për të përcaktuar thelbin e programimit dinamik, merrni parasysh problemin:

Le të imagjinojmë një operacion O, i cili përbëhet nga një numër "hapash" ose fazash të njëpasnjëshme, për shembull, aktiviteti i një industrie gjatë një numri vitesh biznesi. Le të jetë numri i hapave m. Fitimi (efikasiteti i funksionimit) Z për të gjithë operacionin përbëhet nga fitimet në hapat individualë:

ku zi është fitimi në hapin e i-të.

Nëse Z e ka këtë veti, atëherë quhet kriter shtesë.

Operacioni O është një proces i kontrolluar, domethënë mund të zgjedhim disa parametra që ndikojnë në ecurinë dhe rezultatin e tij dhe në çdo hap zgjidhet një zgjidhje, nga e cila varet fitimi në këtë hap dhe fitimi për operacionin në tërësi. Këto vendime quhen vendime hapash.

Kompleti i të gjitha kontrolleve të hapave është kontrolli i operacionit në tërësi. Le ta shënojmë me shkronjën x, dhe hapi kontrollon me shkronjat x1, x2, ..., xm: x=x(x1, x2, ..., xm). Kërkohet të gjendet një kontroll x i tillë që fitimi Z të bëhet maksimal:

Kontrolli x* në të cilin arrihet kjo maksimum quhet kontroll optimal. Ai përbëhet nga një grup kontrollesh hapash optimale: x*=x*(x1*, x2*, ... , xm*).

Fitimi maksimal që arrihet me këtë kontroll do të shënohet me si më poshtë:
,

ku X është grupi i kontrolleve të pranueshme (të mundshme).

Mënyra më e lehtë për të zgjidhur detyrat - të përfunduara duke kaluar nëpër të gjitha opsionet. Kur numri i opsioneve është i vogël, kjo metodë është mjaft e pranueshme. Sidoqoftë, në praktikë, problemet me një numër të vogël opsionesh janë shumë të rralla, kështu që kërkimi shterues zakonisht është i papranueshëm për shkak të burimeve të tepërta llogaritëse. Prandaj, në raste të tilla, programimi dinamik vjen në shpëtim.

Programimi dinamik shpesh ndihmon në zgjidhjen e një problemi që do të kërkonte një kohë shumë të gjatë për t'u zgjidhur duke përdorur një algoritëm kërkimi shterues. Kjo metodë përdor idenë e optimizimit në rritje. Ka një hollësi themelore në këtë ide: çdo hap është optimizuar jo më vete, por me një "sy në të ardhmen", mbi pasojat e vendimit "hap" të marrë. Ai duhet të sigurojë përfitimin maksimal jo në këtë hap të veçantë, por në të gjithë grupin e hapave të përfshirë në operacion.

Metoda dinamike e programimit mund të përdoret vetëm për një klasë të caktuar problemesh. Këto detyra duhet të plotësojnë kërkesat e mëposhtme:

1. Problemi i optimizimit interpretohet si një proces kontrolli me n hapa.
2. Funksioni objektiv është i barabartë me shumën funksionet e synuaraçdo hap.
3. Kontrollo përzgjedhjen aktive hapi kth varet vetëm nga gjendja e sistemit në këtë hap, nuk ndikon në hapat e mëparshëm (nr reagime).
4. Gjendja sk pas hapit të kontrollit k-të varet vetëm nga gjendja e mëparshme sk-1 dhe kontrolli xk (pa efekt të mëvonshëm).
5. Në çdo hap, kontrolli i Xk varet nga numër i kufizuar variablat e kontrollit, dhe gjendja sk varet nga një numër i kufizuar parametrash.

Baza për zgjidhjen e të gjitha problemeve të programimit dinamik është "Parimi i optimizmit" i Bellman e cila duket si kjo:

Cilado qoftë gjendja e sistemit S si rezultat i çdo numri hapash, në hapin tjetër është e nevojshme të zgjidhet kontrolli në mënyrë që ai, së bashku me kontrollin optimal në të gjitha hapat pasues, të çojë në fitim optimal në të gjithë hapat e mbetur, duke përfshirë ky.

Ky parim u formulua për herë të parë nga R. Bellman në vitin 1953. Bellman gjithashtu formuloi qartë kushtet në të cilat parimi është i vërtetë. Bazat kërkesë-proces kontrolli duhet të jetë pa reagime, d.m.th. kontrolli në këtë hap nuk duhet të ndikojë në hapat e mëparshëm.

Parimi i optimalitetit thotë se për çdo proces me qark të hapur, kontrolli optimal është i tillë që është optimal për çdo nënproces në lidhje me gjendjen fillestare të atij nënprocesi. Prandaj, zgjidhja në çdo hap rezulton të jetë më e mira nga pikëpamja e menaxhimit në tërësi.

PUNA KURSI

disiplina: Shkenca Kompjuterike

Tema: Metoda e përafrimit të funksionit katrorët më të vegjël

Hyrje

1. Deklarata e problemit

2.Formulat e llogaritjes

Llogaritja duke përdorur tabela të bëra me mjete Microsoft Excel

Diagrami i algoritmit

Llogaritja në MathCad

Rezultatet e marra duke përdorur funksionin Linear

Paraqitja e rezultateve në formë grafiku

Hyrje

Qëllimi punë kursiështë të thellojë njohuritë në shkencën kompjuterike, të zhvillojë dhe të konsolidojë aftësitë për të punuar me procesorin e fletëllogarive të Microsoft Excel dhe produkt software MathCAD dhe aplikimi i tyre për zgjidhjen e problemeve duke përdorur një kompjuter nga fusha lëndore lidhur me kërkimin.

Përafrimi (nga latinishtja "approximare" - "për t'u afruar") është një shprehje e përafërt e çdo objekti matematikor (për shembull, numra ose funksione) përmes të tjerëve që janë më të thjeshtë, më të përshtatshëm për t'u përdorur ose thjesht më të njohur. Në kërkimin shkencor, përafrimi përdoret për të përshkruar, analizuar, përgjithësuar dhe përdorur më tej rezultatet empirike.

Siç dihet, mund të ketë një lidhje të saktë (funksionale) midis sasive, kur një vlerë specifike korrespondon me një vlerë të argumentit, dhe një lidhje më pak të saktë (korrelacion), kur një vlerë specifike e argumentit i korrespondon një vlere të përafërt ose një grup i caktuar vlerash funksioni, në një shkallë ose në një tjetër afër njëra-tjetrës. Gjatë kryerjes kërkimin shkencor Kur përpunohen rezultatet e një vëzhgimi ose eksperimenti, zakonisht duhet të merret me opsionin e dytë.

Gjatë studimit të varësive sasiore të treguesve të ndryshëm, vlerat e të cilëve përcaktohen në mënyrë empirike, si rregull, ekziston një ndryshueshmëri. Pjesërisht përcaktohet nga heterogjeniteti i objekteve të studiuara të natyrës së pajetë dhe veçanërisht të gjallë, dhe pjesërisht përcaktohet nga gabimi i vëzhgimit dhe përpunimit sasior të materialeve. Komponenti i fundit nuk mund të eliminohet gjithmonë plotësisht, ai mund të minimizohet vetëm me përzgjedhje të kujdesshme të një metode adekuate kërkimore dhe punë të kujdesshme. Prandaj, gjatë kryerjes së ndonjë pune kërkimore, lind problemi i identifikimit të natyrës së vërtetë të varësisë së treguesve të studiuar, në këtë apo atë shkallë të maskuar nga mosmarrja parasysh e ndryshueshmërisë: vlerave. Për këtë qëllim, përdoret përafrimi - një përshkrim i përafërt i varësisë së korrelacionit të variablave nga një ekuacion i përshtatshëm i varësisë funksionale që përcjell tendencën kryesore të varësisë (ose "trendin" e saj).

Kur zgjedh një përafrim, duhet të vazhdohet nga detyrë specifike kërkimore. Në mënyrë tipike, sa më i thjeshtë të jetë ekuacioni i përdorur për përafrim, aq më i përafërt është përshkrimi që rezulton i marrëdhënies. Prandaj, është e rëndësishme të lexoni se sa domethënëse dhe çfarë shkakton devijimet e vlerave specifike nga tendenca që rezulton. Kur përshkruhet varësia e vlerave të përcaktuara empirikisht, është e mundur të arrihet saktësi shumë më e madhe duke përdorur disa më komplekse, shumë ekuacioni parametrik. Sidoqoftë, nuk ka kuptim të përpiqemi të përcjellim devijime të rastësishme të vlerave në seri specifike të të dhënave empirike me saktësi maksimale. Është shumë më e rëndësishme të kuptojmë modelin e përgjithshëm që në këtë rast shprehet më logjikisht dhe me saktësi të pranueshme pikërisht nga ekuacioni me dy parametra funksioni i fuqisë. Kështu, kur zgjedh një metodë të përafrimit, studiuesi bën gjithmonë një kompromis: ai vendos se në çfarë mase në këtë rast është e këshillueshme dhe e përshtatshme të "sakrifikohen" detajet dhe, në përputhje me rrethanat, sa në përgjithësi duhet të shprehet varësia e variablave të krahasuar. Së bashku me identifikimin e modeleve të maskuara nga devijimet e rastësishme të të dhënave empirike nga model i përgjithshëm, përafrimi ju lejon gjithashtu të zgjidhni shumë probleme të tjera të rëndësishme: zyrtarizoni varësinë e gjetur; gjeni vlera të panjohura të ndryshores së varur me interpolim ose, nëse është e përshtatshme, ekstrapolim.

Në secilën detyrë, formulohen kushtet e problemit, të dhënat fillestare, formulari për nxjerrjen e rezultateve dhe tregohen varësitë kryesore matematikore për zgjidhjen e problemit. Në përputhje me metodën për zgjidhjen e problemit, zhvillohet një algoritëm i zgjidhjes, i cili paraqitet në formë grafike.

1. Deklarata e problemit

1. Duke përdorur metodën e katrorëve më të vegjël, përafroni funksionin e dhënë në tabelë:

a) një polinom i shkallës së parë;

b) një polinom i shkallës së dytë;

c) varësia eksponenciale.

Për secilën varësi, llogaritni koeficientin e determinizmit.

Llogaritni koeficientin e korrelacionit (vetëm në rastin a).

Për çdo varësi, vizatoni një vijë trendi.

Përdorni funksionin LINEST për të llogaritur karakteristikat numerike në varësi të.

Krahasoni llogaritjet tuaja me rezultatet e marra duke përdorur funksionin LINEST.

Përfundoni se cila nga formulat që rezultojnë në mënyrën më të mirë të mundshme përafron funksionin.

Shkruani një program në një nga gjuhët e programimit dhe krahasoni rezultatet e llogaritjes me ato të marra më sipër.

Opsioni 3. Funksioni është dhënë në tabelë. 1.

Tabela 1.

xyxyxyxy0.281.052.349.113.3329.434.2386.445.55187.540.872.872.6516.863.4137.454.8390.856.323276. .444.9299.066.66212.971.998.962.8318.993.8556.945.14120.457.13275.742.088.083.0623.754.01723.085. 657. 25321.43

2. Formulat e llogaritjes

Shpesh, kur analizohen të dhënat empirike, lind nevoja për të gjetur një marrëdhënie funksionale midis madhësive x dhe y, të cilat përftohen si rezultat i përvojës ose matjeve.

Xi ( ndryshore e pavarur) vendoset nga eksperimentuesi, dhe yi , quhet empirik ose vlerat eksperimentale të marra si rezultat i përvojës.

Forma analitike e marrëdhënies funksionale që ekziston midis vlerave x dhe y zakonisht është e panjohur, prandaj lind praktikisht detyrë e rëndësishme- gjeni formulën empirike

(ku janë parametrat), vlerat e të cilave do të ndryshonin pak nga vlerat eksperimentale.

Sipas metodës së katrorëve më të vegjël, koeficientët më të mirë janë ata për të cilët shuma e devijimeve në katror të gjetjeve funksion empirik nga vendos vlerat funksionet do të jenë minimale.

Duke përdorur kusht i nevojshëm ekstremi i një funksioni të disa ndryshoreve - derivatet e pjesshëm janë të barabartë me zero, gjeni një grup koeficientësh që ofrojnë minimumin e funksionit të përcaktuar me formulën (2) dhe merrni një sistem normal për përcaktimin e koeficientëve:

Kështu, gjetja e koeficientëve reduktohet në zgjidhjen e sistemit (3).

Lloji i sistemit (3) varet nga cila klasë e formulave empirike kërkojmë varësinë (1). Në rast varësia lineare sistemi (3) do të marrë formën:

Në rastin e një varësie kuadratike, sistemi (3) do të marrë formën:

Në disa raste, një funksion në të cilin koeficientët e pasigurt hyjnë në mënyrë jolineare merret si një formulë empirike. Në këtë rast, ndonjëherë problemi mund të linearizohet, d.m.th. reduktohet në lineare. Varësi të tilla përfshijnë varësinë eksponenciale

ku a1 dhe a2 janë koeficientë të papërcaktuar.

Linearizimi arrihet duke marrë logaritmin e barazisë (6), pas të cilit fitojmë relacionin

Le të shënojmë dhe, përkatësisht, me dhe, atëherë varësia (6) mund të shkruhet në formën, e cila na lejon të zbatojmë formulat (4) duke zëvendësuar a1 me dhe me.

Grafiku i varësisë funksionale të rindërtuar y(x) bazuar në rezultatet e matjes (xi, yi), i=1,2,…,n quhet kurbë regresioni. Për të kontrolluar përputhjen e lakores së regresionit të ndërtuar me rezultatet eksperimentale, zakonisht paraqiten këto karakteristika numerike: koeficienti i korrelacionit (varësia lineare), raporti i korrelacionit dhe koeficienti i përcaktimit.

Koeficienti i korrelacionit është një masë e marrëdhënies lineare ndërmjet të varurve variablat e rastësishëm: Tregon se sa mirë, mesatarisht, njëra nga madhësitë mund të përfaqësohet si funksion linear i tjetrës.

Koeficienti i korrelacionit llogaritet duke përdorur formulën:

ku është mesatarja vlera aritmetike përkatësisht në x, y.

Koeficienti i korrelacionit ndërmjet variablave të rastësishëm sipas vlerë absolute nuk kalon 1. Sa më afër 1, aq më afër lidhje lineare ndërmjet x dhe y.

Në rastin e jolineare lidhje korrelacioni vlerat mesatare të kushtëzuara janë të vendosura rreth vijës së lakuar. Në këtë rast, rekomandohet përdorimi i një raporti korrelacioni si një karakteristikë e forcës së lidhjes, interpretimi i të cilit nuk varet nga lloji i varësisë që studiohet.

Raporti i korrelacionit llogaritet duke përdorur formulën:

ku a numëruesi karakterizon shpërndarjen e mesatareve të kushtëzuara rreth mesatares së pakushtëzuar.

Gjithmonë. Barazia = korrespondon me vlera të rastësishme të pakorreluara; = nëse dhe vetëm nëse ekziston një lidhje e saktë funksionale midis x dhe y. Në rastin e një varësie lineare të y nga x, raporti i korrelacionit përkon me katrorin e koeficientit të korrelacionit. Vlera përdoret si tregues i devijimit të regresionit nga linear.

Raporti i korrelacionit është një masë e korrelacionit midis y dhe x në çdo formë, por nuk mund të japë një ide për shkallën e përafrimit të të dhënave empirike në një formë të veçantë. Për të zbuluar se sa saktë kurba e ndërtuar pasqyron të dhënat empirike, futet një karakteristikë tjetër - koeficienti i përcaktimit.

ku Sres = - shuma e mbetur e katrorëve, që karakterizon devijimin e të dhënave eksperimentale nga ato teorike.gjithsej - shumën e plotë katrore, ku vlera mesatare është yi.

Shuma e regresionit të katrorëve që karakterizojnë përhapjen e të dhënave.

Sa më e vogël të jetë shuma e mbetur e katrorëve në krahasim me shumën totale të katrorëve, aq më shumë vlerë koeficienti i përcaktimit r2, i cili tregon se sa i mirë është ekuacioni i marrë duke përdorur analiza e regresionit, shpjegon marrëdhëniet ndërmjet variablave. Nëse është e barabartë me 1, atëherë ka një korrelacion të plotë me modelin, d.m.th. nuk ka asnjë ndryshim midis vlerave aktuale dhe të vlerësuara të y. Në rastin e kundërt, nëse koeficienti i përcaktimit është 0, atëherë ekuacioni i regresionit është i pasuksesshëm në parashikimin e vlerave të y.

Koeficienti i determinizmit nuk e kalon gjithmonë raportin e korrelacionit. Në rastin kur barazia plotësohet, mund të supozojmë se formula e ndërtuar empirike pasqyron më saktë të dhënat empirike.

3. Llogaritja duke përdorur tabela të bëra duke përdorur Microsoft Excel

Për të kryer llogaritjet, këshillohet të rregulloni të dhënat në formën e tabelës 2, duke përdorur mjetet procesor tavoline Microsoft Excel.

Tabela 2

ABCDEFGHI10,281,050,07840,2940,0219520,0061470,082320,048790,01366120,872,870,75692,49690,65850230210 5131,656,432,722510,60954,4921257,41200617,505681,8609753,07060841 , 998,963,960117,83047,88059915,6823935,48252,192774,36361352,088,084,326416,80648,998912183425 2 ,349,115,475621,317412,812929,982249,882722,2093735,16993272,6516 , 867,022544,67918,6096349,31551118,39942,8249447,48610182,7717,977,672949,776921,2539351,87820 3 18,998,008953,741722,6651964,14248152,0892,9439138, 331272103,0623,759,363672,67528,6526287,677222,38553,1675839,692803113,3329,4311,088998,0026287,677226 01511,26211123,4137,4511,6281127,704539,65182135,2127435 , 47233,62300712,35445133,5542,4412,6025150,66244,73888158,823534,85013,74809113,30572143,81426 , 7065843,99324,04199815,56169154,0175,0816,0801301,070864 , 4812258,56961207,2944,31855417,3174164,2386,4417,8929365,641275,68697320,15591546,6624,485945 3 8.8055112.6786544.23762119.4314.5092121.77948184.9299.0624, 2064487,3752119,0955585, 94982397,8864,59572622,61097195,14120,4526,4196619,113135,7967697,99533182,2414,79123524,6269525 143,0557748,18113819,8324,93913925,8317215, 55187,5430,80251040, 847170,9539948,7945776,7015,23399229,04866226,32200,4539,94241266,844252,4361595,3958006,4545,30 4,35561418,38295,40831967,4199446,4125, 36115135,70527247,13275 ,7450,83691966,026362,46712584,3914017,775,61945840,06674257,25321,4352,56252330,368381,07812726 652695,932089,99453,310511850,652417,56813982, 9971327.3490.97713415.0797 S U M M S Le të shpjegojmë se si është përpiluar Tabela 2.

Hapi 1. Në qelizat A1:A25 futim vlerat xi.

Hapi 2. Në qelizat B1:B25 futim vlerat e yi.

Hapi 3. Në qelizën C1, futni formulën = A1^2.

Hapi 4. Kjo formulë kopjohet në qelizat C1:C25.

Hapi 5. Në qelizën D1 fut formulën = A1 * B1.

Hapi 6. Kjo formulë kopjohet në qelizat D1:D25.

Hapi 7. Në qelizën F1, futni formulën = A1^4.

Hapi 8. Kjo formulë kopjohet në qelizat F1:F25.

Hapi 9. Në qelizën G1, futni formulën = A1^2*B1.

Hapi 10. Kjo formulë kopjohet në qelizat G1:G25.

Hapi 11. Në qelizën H1, futni formulën = LN(B1).

Hapi 12. Kjo formulë kopjohet në qelizat H1:H25.

Hapi 13. Në qelizën I1 fut formulën = A1*LN(B1).

Hapi 14. Kjo formulë kopjohet në qelizat I1:I25.

Ne kryejmë hapat e mëtejshëm duke përdorur përmbledhjen automatike S .

Hapi 15. Në qelizën A26, futni formulën = SUM(A1:A25).

Hapi 16. Në qelizën B26, futni formulën = SUM(B1:B25).

Hapi 17. Në qelizën C26, futni formulën = SUM(C1:C25).

Hapi 18. Në qelizën D26, futni formulën = SUM(D1:D25).

Hapi 19. Në qelizën E26, futni formulën = SUM(E1:E25).

Hapi 20. Në qelizën F26, futni formulën = SUM(F1:F25).

Hapi 21. Në qelizën G26, futni formulën = SUM(G1:G25).

Hapi 22. Në qelizën H26, futni formulën = SUM(H1:H25).

Hapi 23. Në qelizën I26, futni formulën = SUM(I1:I25).

Le të përafrojmë funksionin funksion linear. Për të përcaktuar koeficientët do të përdorim sistemin (4). Duke përdorur totalet e tabelës 2, të vendosura në qelizat A26, B26, C26 dhe D26, ne shkruajmë sistemin (4) në formën

duke zgjidhur të cilat, marrim dhe.

Sistemi u zgjidh duke përdorur metodën e Cramer. Thelbi i së cilës është si më poshtë. Konsideroni një sistem prej n algjebrike ekuacionet lineare me n të panjohura:

Përcaktori i sistemit është përcaktuesi i matricës së sistemit:

Le të shënojmë - përcaktorin që fitohet nga përcaktorja e sistemit Δ duke zëvendësuar kolonën j-të me kolonën

Kështu, përafrim linear duket si

Ne zgjidhim sistemin (11) duke përdorur Microsoft Excel. Rezultatet janë paraqitur në tabelën 3.

Tabela 3

ABCDE282595.932089.992995.93453.310511850.653031 Matrica e anasjelltë320.212802-0.04503a1=-88.9208133-0.045030.0294=0.045030.0294

Në tabelën 3, në qelizat A32:B33 shkruhet formula (=MOBR(A28:B29)).

Në qelizat E32:E33 shkruhet formula (=MULTIPLE(A32:B33),(C28:C29)).

Më pas përafrojmë funksionin funksion kuadratik. Për të përcaktuar koeficientët a1, a2 dhe a3, ne përdorim sistemin (5). Duke përdorur totalet e tabelës 2, të vendosura në qelizat A26, B26, C26, D26, E26, F26, G26, ne shkruajmë sistemin (5) në formën

duke e zgjidhur atë, marrim a1=10.663624, dhe

Kështu, përafrimi kuadratik ka formën

Ne zgjidhim sistemin (16) duke përdorur Microsoft Excel. Rezultatet janë paraqitur në tabelën 4.

Tabela 4

ABCDEF362595,93453,31052089,993795,93453,31052417,56811850,65538453,31052417,56813982,9971327,31403,31403x3 ,033846a1=10.66362442-0.314390,184534-0.021712a2=-18, 924512430.033846-0.021710.002728a3=8.02723

Në tabelën 4, në qelizat A41:C43 shkruhet formula (=MOBR(A36:C38)).

Në qelizat F41:F43 formula shkruhet (=MULTIPLE(A41:C43),(D36:D38)).

Tani le të përafrojmë funksionin funksioni eksponencial. Për të përcaktuar koeficientët dhe, marrim logaritmin e vlerave dhe, duke përdorur totalet e tabelës 2, të vendosura në qelizat A26, C26, H26 dhe I26, marrim sistemin

Pasi kemi zgjidhur sistemin (18), marrim dhe.

Pas fuqizimit marrim.

Kështu, përafrimi eksponencial ka formën

Ne zgjidhim sistemin (18) duke përdorur Microsoft Excel. Rezultatet janë paraqitur në tabelën 5.

Tabela 5

BCDEF462595.9390.977134795.93453.3105415.07974849 Matrica e anasjelltë=0.667679 500.212802-0.04503a2=0.774360

Në qelizat A50:B51 formula shkruhet (=MOBR(A46:B47)).

Në qelizën E51 shkruhet formula =EXP(E49).

Le të llogarisim mesataren aritmetike duke përdorur formulat:

Rezultatet e llogaritjes duke përdorur Microsoft Excel janë paraqitur në Tabelën 6.

Tabela 6

BC54Xav=3.837255Yav=83.5996

Në qelizën B54 shkruhet formula = A26/25.

Në qelizën B55 formula shkruhet = B26/25

Tabela 7

ABJKLMNO10,281,05293,645412,653676814,4365987,97624,444081,88177520,872,87239,54098,804276652717,260 1, 656,43168,78534,7838445955,147448,035726,395820,32073741, 998,96137,87433,4121485571,0770,7358817,368220,02062652,088,08132,7033,0877525703,2112,1383914,220 52582,2416085548,70151,488211,4985887,99584272,6516, 8679,233251,4094444454,174178,5730,000622,83382582,7717,9770,039911,1389164307,244311,46313,4777 4791,0144524174,4373,4915,7914362,382273103,0623,7546 , 515110,604043581,975620,344117,375498,423061113,3329,4327,474820,2572522934,346983,819852,249 1 10,18252129,786725,90914,090409102,2541133,5542,4411, 821040, 0824841694,113797,89844,861044143,3219143,8556,94-0,341240,000164710,7343741,750,04143,94-0,341240,000164710,7343741,750,042314 0,02986 72.58358265.3212126.0007996.9257164.2386.441, 1157090,1542928 ,067872219,6288148,75781214,778174,8390,857,1981970,98565252,56831397,703245,695876,64891184,625 241103,718163,9776121,868195,14120,4548,00871, 6972881357, 952471,908425,17881258,6007205,23139,6578,0671,9398923141,64743,1629470,45155769,9408215,55187,000 725,38421200,5291951,06226,32200,45290,11626,16429613654, 0227 ,28786126,28273577,409236,66212,97365,18687,968216736,76,038755767,788515795,87247,13275,746326 7565,1469344766,92257,25321,43811,667611,647256563,37121, 842677, 966445516,82695,932089,93830,94585,207919964427404,823786,286115678,1C u m m Sasitë e mbeturaXY lineare.katërsh.ekspozim

Le të shpjegojmë se si është përpiluar.

Qelizat A1:A26 dhe B1:B26 janë mbushur tashmë.

Hapi 1. Në qelizën J1 fut formulën = (A1-$B$54)*(B1-$B$55).

Hapi 2. Kjo formulë kopjohet në qelizat J2:J25.

Hapi 3. Në qelizën K1, futni formulën = (A1-$B$54)^2.

Hapi 4. Kjo formulë kopjohet në qelizat k2:K25.

Hapi 5. Në qelizën L1, futni formulën = (B1-$B$55)^2.

Hapi 6. Kjo formulë kopjohet në qelizat L2:L25.

Hapi 7. Në qelizën M1, futni formulën = ($E$32+$E$33*A1-B1)^2.

Hapi 8. Kjo formulë kopjohet në qelizat M2:M25.

Hapi 9. Në qelizën N1, futni formulën = ($F$41+$F$42*A1+$F$43*A1^2-B1)^2.

Hapi 10. Kjo formulë kopjohet në qelizat N2:N25.

Hapi 11. Në qelizën O1, futni formulën = ($E$51*EXP($E$50*A1)-B1)^2.

Hapi 12. Kjo formulë kopjohet në qelizat O2:O25.

Ne kryejmë hapat e ardhshëm duke përdorur përmbledhjen automatike S .

Hapi 13. Në qelizën J26, futni formulën = SUM(J1:J25).

Hapi 14. Në qelizën K26, futni formulën = SUM(K1:K25).

Hapi 15. Në qelizën L26, futni formulën = SUM(L1:L25).

Hapi 16. Në qelizën M26, futni formulën = SUM(M1:M25).

Hapi 17. Në qelizën N26, futni formulën = SUM(N1:N25).

Hapi 18. Në qelizën O26, vendosni formulën = SUM(O1:O25).

Tani le të llogarisim koeficientin e korrelacionit duke përdorur formulën (8) (vetëm për përafrim linear) dhe koeficientin e përcaktimit duke përdorur formulën (10). Rezultatet e llogaritjeve duke përdorur Microsoft Excel janë paraqitur në Tabelën 8.

Tabela 8

AB57 Koeficienti i korrelacionit 0,92883358 Koeficienti i përcaktimit (përafrim linear) 0,8627325960 Koeficienti i determinizmit (përafrim kuadratik) 0,9810356162 Koeficienti i determinizmit (përafrim eksponencial 086425) Në qelizën E57 formula shkruhet =J26/(K26*L26)^(1/2).

Në qelizën E59 shkruhet formula = 1-M26/L26.

Në qelizën E61 shkruhet formula = 1-N26/L26.

Në qelizën E63 shkruhet formula = 1-O26/L26.

Analiza e rezultateve të llogaritjes tregon se përafrimi kuadratik përshkruan më së miri të dhënat eksperimentale.

Diagrami i algoritmit

Oriz. 1. Diagrami algoritmik për programin e llogaritjes.

5. Llogaritja në MathCad

Regresioni linear

· vijë (x, y) - vektor i dy elementeve (b, a) koeficientëve regresioni linear b + sëpatë;

· x është vektori i të dhënave reale të argumentit;

· y është një vektor i vlerave reale të të dhënave të së njëjtës madhësi.

Figura 2.

Regresioni polinomial nënkupton përafrimin e të dhënave (x1, y1) me një polinom shkalla kth Për k=i polinomi është drejtëz, për k=2 është parabolë, për k=3 është parabolë kubike etj. Si rregull, në praktikë k<5.

· regresi (x,y,k) - vektor i koeficientëve për ndërtimin e regresionit polinomial të të dhënave;

· interp (s,x,y,t) - rezultat i regresionit polinomial;

· s=regres(x,y,k);

· x është një vektor i të dhënave reale të argumentit, elementët e të cilit janë renditur në rend rritës;

· y është një vektor i vlerave reale të të dhënave të së njëjtës madhësi;

· k - shkalla e polinomit të regresionit (numër i plotë pozitiv);

· t është vlera e argumentit të polinomit të regresionit.

Figura 3

Përveç atyre të diskutuara, disa lloje të tjera të regresionit me tre parametra janë ndërtuar në Mathcad, zbatimi i tyre ndryshon disi nga opsionet e mësipërme të regresionit në atë që për ta, përveç grupit të të dhënave, është e nevojshme të specifikohen disa vlera fillestare; të koeficientëve a, b, c. Përdorni llojin e duhur të regresionit nëse keni një ide të mirë se çfarë lloj varësie përshkruan grupin tuaj të të dhënave. Kur një lloj regresioni nuk pasqyron mirë një sekuencë të dhënash, rezultati është shpesh i pakënaqshëm dhe madje shumë i ndryshëm në varësi të zgjedhjes së vlerave fillestare. Secili prej funksioneve prodhon një vektor të parametrave të rafinuar a, b, c.

Rezultatet e marra duke përdorur funksionin LINEST

Le të shohim qëllimin e funksionit LINEST.

Ky funksion përdor katrorët më të vegjël për të llogaritur vijën e drejtë që i përshtatet më mirë të dhënave të disponueshme.

Funksioni kthen një grup që përshkruan linjën që rezulton. Ekuacioni për një vijë të drejtë është:

M1x1 + m2x2 + ... + b ose y = mx + b,

algoritmi i tabelës softueri i Microsoft

Për të marrë rezultatet, duhet të krijoni një formulë tabelare që do të zërë 5 rreshta dhe 2 kolona. Ky interval mund të vendoset kudo në fletën e punës. Gjatë këtij intervali, duhet të futni funksionin LINEST.

Si rezultat, të gjitha qelizat e intervalit A65:B69 duhet të mbushen (siç tregohet në Tabelën 9).

Tabela 9.

АВ6544,95997-88,9208663,73946615,92346670,86273234,5183168144,55492369172239,227404,82

Le të shpjegojmë qëllimin e disa prej sasive të vendosura në tabelën 9.

Vlerat e vendosura në qelizat A65 dhe B65 karakterizojnë përkatësisht koeficientin e përcaktimit - numrin e shkallëve të lirisë - shumën e mbetur të katrorëve.

Paraqitja e rezultateve në formë grafiku

Oriz. 4. Grafiku i përafrimit linear

Oriz. 5. Grafiku i përafrimit kuadratik

Oriz. 6. Grafiku i përafrimit eksponencial

konkluzione

Le të nxjerrim përfundime bazuar në rezultatet e të dhënave të marra.

Analiza e rezultateve të llogaritjes tregon se përafrimi kuadratik përshkruan më së miri të dhënat eksperimentale, sepse vija e trendit për të pasqyron më saktë sjelljen e funksionit në këtë fushë.

Duke krahasuar rezultatet e marra duke përdorur funksionin LINEST, shohim se ato përkojnë plotësisht me llogaritjet e kryera më sipër. Kjo tregon se llogaritjet janë të sakta.

Rezultatet e marra duke përdorur programin MathCad përputhen plotësisht me vlerat e dhëna më sipër. Kjo tregon saktësinë e llogaritjeve.

Lista e literaturës së përdorur

1. B.P. Demidovich, I.A. Maroon. Bazat e matematikës llogaritëse. M: Shtëpia Botuese Shtetërore e Letërsisë Fizike Matematikore.
2. Shkenca Kompjuterike: Teksti mësimor, bot. prof. N.V. Makarova. M: Financa dhe Statistikat, 2007.
3. Informatikë: Punëtori për teknologjinë kompjuterike, ed. prof. N.V. Makarova. M: Financa dhe Statistikat, 2010.
4. V.B. Komyagin. Programimi në Excel duke përdorur Visual Basic. M: Radio dhe komunikim, 2007.
5. N. Nicole, R. Albrecht. Excel. Spreadsheets. M: Ed. "ECOM", 2008.
6. Udhëzime për përfundimin e lëndëve në shkenca kompjuterike (për studentët me korrespondencë të të gjitha specialiteteve), ed. Zhurova G. N., Instituti Shtetëror Hidrologjik i Shën Petersburgut (TU), 2011.

Që gjen aplikimin më të gjerë në fusha të ndryshme të shkencës dhe veprimtarisë praktike. Kjo mund të jetë fizika, kimia, biologjia, ekonomia, sociologjia, psikologjia, e kështu me radhë, e kështu me radhë. Me vullnetin e fatit, shpesh më duhet të merrem me ekonominë, dhe për këtë arsye sot do të organizoj për ju një udhëtim në një vend të mahnitshëm të quajtur Ekonometria=) ...Si nuk e deshiron?! Është shumë mirë atje - thjesht duhet të vendosni! ...Por ajo që me siguri dëshironi patjetër është të mësoni se si t'i zgjidhni problemet Metoda e katrorëve më të vegjël. Dhe veçanërisht lexuesit e zellshëm do të mësojnë t'i zgjidhin ato jo vetëm me saktësi, por edhe SHUME SHPEJTË ;-) Por së pari deklaratë e përgjithshme e problemit+ shembulli shoqërues:

Supozoni se në një fushë të caktuar lëndore studiohen tregues që kanë një shprehje sasiore. Në të njëjtën kohë, ka çdo arsye për të besuar se treguesi varet nga treguesi. Ky supozim mund të jetë ose një hipotezë shkencore ose i bazuar në sensin bazë të përbashkët. Megjithatë, le ta lëmë mënjanë shkencën dhe të eksplorojmë zona më të shijshme - domethënë, dyqanet ushqimore. Le të shënojmë me:

– zona me pakicë e një dyqani ushqimor, m2,
- qarkullimi vjetor i një dyqani ushqimor, milion rubla.

Është absolutisht e qartë se sa më e madhe të jetë sipërfaqja e dyqanit, aq më i madh në shumicën e rasteve do të jetë qarkullimi i tij.

Le të supozojmë se pas kryerjes së vëzhgimeve/eksperimenteve/llogaritjeve/valleve me një dajre kemi në dispozicion të dhëna numerike:

Me dyqanet ushqimore, mendoj se gjithçka është e qartë: - kjo është zona e dyqanit të parë, - qarkullimi vjetor i tij, - zona e dyqanit të dytë, - xhiroja vjetore e tij, etj. Nga rruga, nuk është aspak e nevojshme të kesh akses në materialet e klasifikuara - një vlerësim mjaft i saktë i qarkullimit tregtar mund të merret me anë të statistika matematikore. Sidoqoftë, le të mos shpërqendrohemi, kursi i spiunazhit tregtar tashmë është paguar =)

Të dhënat tabelare gjithashtu mund të shkruhen në formën e pikave dhe të përshkruhen në formën e njohur Sistemi kartezian .

Le t'i përgjigjemi një pyetjeje të rëndësishme: Sa pikë nevojiten për një studim cilësor?

Sa me shume aq me mire. Seti minimal i pranueshëm përbëhet nga 5-6 pikë. Përveç kësaj, kur sasia e të dhënave është e vogël, rezultatet "anormale" nuk mund të përfshihen në mostër. Kështu, për shembull, një dyqan i vogël elitar mund të fitojë urdhra të madhësisë më shumë se "kolegët e tij", duke shtrembëruar kështu modelin e përgjithshëm që duhet të gjeni!

Për ta thënë shumë thjesht, duhet të zgjedhim një funksion, orarin i cili kalon sa më afër pikave . Ky funksion quhet të përafërt (përafrim - përafrim) ose funksioni teorik . Në përgjithësi, këtu shfaqet menjëherë një "pretendues" i dukshëm - një polinom i shkallës së lartë, grafiku i të cilit kalon nëpër TË GJITHA pikat. Por ky opsion është i ndërlikuar dhe shpesh thjesht i pasaktë. (pasi grafiku do të "lak" gjatë gjithë kohës dhe do të pasqyrojë dobët tendencën kryesore).

Kështu, funksioni i kërkuar duhet të jetë mjaft i thjeshtë dhe në të njëjtën kohë të pasqyrojë në mënyrë adekuate varësinë. Siç mund ta merrni me mend, quhet një nga metodat për gjetjen e funksioneve të tilla Metoda e katrorëve më të vegjël. Së pari, le të shohim thelbin e tij në terma të përgjithshëm. Lërini disa funksione të përafrojnë të dhënat eksperimentale:


Si të vlerësohet saktësia e këtij përafrimi? Le të llogarisim edhe diferencat (devijimet) midis vlerave eksperimentale dhe funksionale (ne studiojmë vizatimin). Mendimi i parë që vjen në mendje është të vlerësojmë se sa e madhe është shuma, por problemi është se diferencat mund të jenë negative (Për shembull, ) dhe devijimet si rezultat i një përmbledhjeje të tillë do të anulojnë njëra-tjetrën. Prandaj, si një vlerësim i saktësisë së përafrimit, kërkon të merret shuma modulet devijimet:

ose i shembur: (në rast se dikush nuk e di: - kjo është ikona e shumës dhe - një ndryshore ndihmëse "counter", e cila merr vlera nga 1 në ).

Duke përafruar pikat eksperimentale me funksione të ndryshme, do të marrim vlera të ndryshme, dhe padyshim, ku kjo shumë është më e vogël, ai funksion është më i saktë.

Një metodë e tillë ekziston dhe quhet metoda e modulit më të vogël. Megjithatë, në praktikë është bërë shumë më e përhapur Metoda e katrorëve më të vegjël, në të cilën vlerat e mundshme negative eliminohen jo nga moduli, por duke kuadruar devijimet:

, pas së cilës përpjekjet synojnë të zgjedhin një funksion të tillë që shuma e devijimeve në katror ishte sa më i vogël. Në fakt, nga këtu vjen emri i metodës.

Dhe tani kthehemi në një pikë tjetër të rëndësishme: siç u përmend më lart, funksioni i zgjedhur duhet të jetë mjaft i thjeshtë - por ka edhe shumë funksione të tilla: lineare , hiperbolike, eksponenciale, logaritmike, kuadratike etj. Dhe, natyrisht, këtu do të doja menjëherë të "zvogëloja fushën e veprimtarisë". Cilën klasë funksionesh duhet të zgjedh për kërkime? Një teknikë primitive por efektive:

- Është më e lehtë të përshkruash pikat në vizatim dhe analizoni vendndodhjen e tyre. Nëse ata priren të vrapojnë në një vijë të drejtë, atëherë duhet të kërkoni ekuacioni i një linje me vlera optimale dhe . Me fjalë të tjera, detyra është të gjejmë koeficientë të tillë në mënyrë që shuma e devijimeve në katror të jetë më e vogla.

Nëse pikat janë të vendosura, për shembull, përgjatë hiperbolë, atëherë është e qartë se funksioni linear do të japë një përafrim të dobët. Në këtë rast, ne jemi duke kërkuar për koeficientët më "të favorshëm" për ekuacionin e hiperbolës – ato që japin shumën minimale të katrorëve .

Tani vini re se në të dyja rastet po flasim funksionet e dy variablave, argumentet e të cilit janë parametrat e varësisë së kërkuar:

Dhe në thelb ne duhet të zgjidhim një problem standard - të gjejmë funksioni minimal i dy variablave.

Le të kujtojmë shembullin tonë: supozoni se pikat e "magazinimit" priren të vendosen në një vijë të drejtë dhe ka çdo arsye për të besuar praninë varësia lineare qarkullim nga hapësira me pakicë. Le të gjejmë koeficientë të tillë "a" dhe "të jenë" të tillë që shuma e devijimeve në katror ishte më i vogli. Gjithçka është si zakonisht - së pari Derivatet e pjesshme të rendit të parë. Sipas rregulli i linearitetit Ju mund të dalloni pikërisht nën ikonën e shumës:

Nëse dëshironi ta përdorni këtë informacion për një ese ose punim terminor, do t'ju jem shumë mirënjohës për lidhjen në listën e burimeve, do të gjeni llogaritjet e tilla të detajuara në disa vende:

Le të krijojmë një sistem standard:

Ne zvogëlojmë çdo ekuacion me "dy" dhe, përveç kësaj, "ndajmë" shumat:

Shënim : analizoni në mënyrë të pavarur pse "a" dhe "be" mund të hiqen përtej ikonës së shumës. Nga rruga, zyrtarisht kjo mund të bëhet me shumën

Le ta rishkruajmë sistemin në formën e "aplikuar":

pas së cilës fillon të shfaqet algoritmi për zgjidhjen e problemit tonë:

A i dimë koordinatat e pikave? ne e dimë. Shumat mund ta gjejmë? Lehtësisht. Le të bëjmë më të thjeshtën sistemi i dy ekuacioneve lineare në dy të panjohura("a" dhe "të jetë"). Ne e zgjidhim sistemin, për shembull, Metoda e Cramer-it, si rezultat i së cilës marrim një pikë të palëvizshme. Duke kontrolluar kusht i mjaftueshëm për një ekstrem, mund të verifikojmë se në këtë pikë funksioni arrin saktësisht minimale. Kontrolli përfshin llogaritje shtesë dhe për këtë arsye ne do ta lëmë atë në prapaskenë (nëse është e nevojshme, korniza që mungon mund të shihet). Ne nxjerrim përfundimin përfundimtar:

Funksioni në mënyrën më të mirë të mundshme (të paktën krahasuar me çdo funksion tjetër linear) afron pikat eksperimentale . Përafërsisht, grafiku i tij kalon sa më afër këtyre pikave. Në traditë ekonometria quhet edhe funksioni i përafërt që rezulton ekuacioni i regresionit linear të çiftëzuar .

Problemi në shqyrtim ka një rëndësi të madhe praktike. Në situatën e shembullit tonë, barazimi. ju lejon të parashikoni se çfarë qarkullimi tregtar ("Igrek") dyqani do të ketë në një ose një tjetër vlerë të zonës së shitjes (një ose një kuptim tjetër i "x"). Po, parashikimi që rezulton do të jetë vetëm një parashikim, por në shumë raste do të dalë mjaft i saktë.

Unë do të analizoj vetëm një problem me numrat "realë", pasi nuk ka vështirësi në të - të gjitha llogaritjet janë në nivelin e kurrikulës së shkollës së klasës 7-8. Në 95 për qind të rasteve, do t'ju kërkohet të gjeni vetëm një funksion linear, por në fund të artikullit do të tregoj se nuk është më e vështirë të gjesh ekuacionet e hiperbolës optimale, eksponenciale dhe disa funksione të tjera.

Në fakt, gjithçka që mbetet është të shpërndani të mirat e premtuara - në mënyrë që të mësoni të zgjidhni shembuj të tillë jo vetëm me saktësi, por edhe shpejt. Ne studiojmë me kujdes standardin:

Detyrë

Si rezultat i studimit të marrëdhënies midis dy treguesve, u morën çiftet e mëposhtme të numrave:

Duke përdorur metodën e katrorëve më të vegjël, gjeni funksionin linear që përafron më mirë atë empirik (me eksperience) të dhëna. Bëni një vizatim mbi të cilin do të ndërtohen pika eksperimentale dhe një grafik i funksionit të përafërt në një sistem koordinativ drejtkëndor kartezian . Gjeni shumën e devijimeve në katror ndërmjet vlerave empirike dhe teorike. Zbuloni nëse funksioni do të ishte më i mirë (nga pikëpamja e metodës së katrorëve më të vegjël) afrojnë pikat eksperimentale.

Ju lutemi vini re se kuptimet "x" janë të natyrshme dhe kjo ka një kuptim karakteristik kuptimor, për të cilin do të flas pak më vonë; por ato, natyrisht, mund të jenë edhe të pjesshme. Për më tepër, në varësi të përmbajtjes së një detyre të caktuar, vlerat "X" dhe "lojë" mund të jenë plotësisht ose pjesërisht negative. Epo, na është dhënë një detyrë "pa fytyrë" dhe ne e fillojmë atë zgjidhje:

Ne gjejmë koeficientët e funksionit optimal si zgjidhje për sistemin:

Për qëllime të regjistrimit më kompakt, ndryshorja "kundër" mund të hiqet, pasi tashmë është e qartë se përmbledhja kryhet nga 1 në .

Është më i përshtatshëm për të llogaritur shumat e kërkuara në formë tabelare:


Llogaritjet mund të kryhen në një mikrollogaritës, por është shumë më mirë të përdorni Excel - më shpejt dhe pa gabime; shikoni një video të shkurtër:

Kështu, marrim sa vijon sistemi:

Këtu mund të shumëzoni ekuacionin e dytë me 3 dhe Zbrisni të 2-tin nga ekuacioni i 1-rë termi me term. Por ky është fat - në praktikë, sistemet shpesh nuk janë dhuratë, dhe në raste të tilla kursen Metoda e Cramer-it:
, që do të thotë se sistemi ka një zgjidhje unike.

Le të kontrollojmë. Unë e kuptoj që ju nuk dëshironi, por pse të kaloni gabimet ku ato absolutisht nuk mund të mungojnë? Le ta zëvendësojmë zgjidhjen e gjetur në anën e majtë të secilit ekuacion të sistemit:

Janë marrë anët e djathta të ekuacioneve përkatëse, që do të thotë se sistemi është zgjidhur saktë.

Kështu, funksioni i dëshiruar përafrues: – nga të gjitha funksionet lineareËshtë ajo që përafron më së miri të dhënat eksperimentale.

Ndryshe nga e drejtpërdrejtë varësia e xhiros së dyqanit nga zona e tij, varësia e gjetur është e kundërta (parimi "sa më shumë, aq më pak"), dhe ky fakt zbulohet menjëherë nga negativi shpat. Funksioni na tregon se me një rritje të një treguesi të caktuar me 1 njësi, vlera e treguesit të varur zvogëlohet mesatarisht me 0.65 njësi. Siç thonë ata, sa më i lartë të jetë çmimi i hikërrorit, aq më pak shitet.

Për të vizatuar funksionin e përafërt, le të gjejmë dy vlerat e tij:

dhe ekzekutoni vizatimin:


Drejtëza e ndërtuar quhet linjë trendi (domethënë, një linjë trendi lineare, d.m.th. në rastin e përgjithshëm, një trend nuk është domosdoshmërisht një vijë e drejtë). Të gjithë e njohin shprehjen “të jesh në trend” dhe mendoj se ky term nuk ka nevojë për komente shtesë.

Le të llogarisim shumën e devijimeve në katror midis vlerave empirike dhe teorike. Gjeometrikisht, kjo është shuma e katrorëve të gjatësisë së segmenteve të "mjedrës". (dy prej të cilave janë aq të vogla sa që as nuk duken).

Le të përmbledhim llogaritjet në një tabelë:


Përsëri, ato mund të bëhen me dorë për çdo rast, unë do të jap një shembull për pikën e parë:

por është shumë më efektive ta bësh atë në mënyrën e njohur tashmë:

E përsërisim edhe një herë: Cili është kuptimi i rezultatit të marrë? Nga të gjitha funksionet lineare y funksion treguesi është më i vogli, domethënë në familjen e tij është përafrimi më i mirë. Dhe këtu, meqë ra fjala, pyetja përfundimtare e problemit nuk është e rastësishme: po sikur funksioni eksponencial i propozuar a do të ishte më mirë të afroheshin pikat eksperimentale?

Le të gjejmë shumën përkatëse të devijimeve në katror - për t'i dalluar, unë do t'i tregoj ato me shkronjën "epsilon". Teknika është saktësisht e njëjtë:


Dhe përsëri, për çdo rast, llogaritjet për pikën 1:

Në Excel përdorim funksionin standard EXP (Sintaksa mund të gjendet në Excel Help).

konkluzioni: , që do të thotë se funksioni eksponencial i përafron pikat eksperimentale më keq se një drejtëz .

Por këtu duhet theksuar se "më keq" është nuk do të thotë akoma, e cila është e keqe. Tani kam ndërtuar një grafik të këtij funksioni eksponencial - dhe ai gjithashtu kalon afër pikave - aq sa pa hulumtime analitike është e vështirë të thuhet se cili funksion është më i saktë.

Kjo e mbyll zgjidhjen, dhe unë kthehem te çështja e vlerave natyrore të argumentit. Në studime të ndryshme, zakonisht ekonomike ose sociologjike, "X" natyrore përdoren për të numëruar muaj, vite ose intervale të tjera të barabarta kohore. Konsideroni, për shembull, problemin e mëposhtëm.

Shembull.

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

Si rezultat i shtrirjes së tyre, fitohet funksioni

Duke përdorur Metoda e katrorëve më të vegjë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 (në kuptimin e metodës së katrorëve më të vegjël) i përafron më mirë të dhënat eksperimentale. Bëni një vizatim.

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

Detyra është të gjejmë koeficientët e varësisë lineare në të cilat funksionojnë dy ndryshore A Dhe b merr vlerën më të vogël. Kjo është, e dhënë 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 zbret në gjetjen e ekstremit të një funksioni të dy ndryshoreve.

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

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

Ne zgjidhim sistemin rezultues të ekuacioneve duke përdorur çdo metodë (për shembull me metodën 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).

E dhënë A Dhe b funksionin merr vlerën më të vogël. Dëshmia e këtij fakti është dhënë më poshtë në tekstin 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. Ne rekomandojmë llogaritjen e vlerave të këtyre shumave veç e veç. Koeficienti 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 fitohen duke kuadruar vlerat në rreshtin e dytë për çdo numër i.

Vlerat në kolonën e 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ë vlerat përkatëse nga kolona e fundit e tabelës në to:

Prandaj, y = 0,165x+2,184- vijën e drejtë të përafërt të dëshiruar.

Mbetet për të gjetur se cila nga rreshtat y = 0,165x+2,184 ose përafron më mirë të dhënat origjinale, domethënë vlerësimet 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 shumën 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 në kuptimin e metodës së katrorëve më të vegjël.

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

Ilustrimi grafik i metodës së katrorëve më të vegjël (LS).

Gjithçka është qartë e dukshme në grafikët. Vija e kuqe është vija e drejtë 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ë pika të caktuara fikse.

Ky lloj problemi i përafrimit të funksionit shpesh lind:

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

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

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

kur përcaktohen vlerat e sasive karakteristike të një procesi jashtë intervalit të konsideruar, veçanërisht kur parashikohet.

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

Ky artikull diskuton aftësitë e paketës MS Excel për zgjidhjen e këtij lloj problemi, përveç kësaj, ai ofron metoda dhe teknika për ndërtimin (krijimin) e regresioneve për funksionet e tabeluara (që është baza e analizës së regresionit).

Excel ka dy opsione për ndërtimin e regresioneve.

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

Përdorimi i funksioneve statistikore të integruara të fletës së punës Excel, duke ju lejuar të merrni regresione (linjat e trendit) drejtpërdrejt bazuar në tabelën e të dhënave burimore.

Shtimi i linjave të tendencës në një grafik

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

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

shtoni në diagram ekuacionin e ndërtuar të regresionit;

përcaktoni shkallën e korrespondencës 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 lineare, polinomiale, logaritmike, fuqie, eksponenciale të regresioneve, të cilat specifikohen nga ekuacioni:

y = y(x)

ku x është një variabël i pavarur që 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ë për modelimin e karakteristikave, vlerat e të cilave rriten ose ulen me një ritëm konstant. Ky është modeli më i thjeshtë për t'u ndërtuar për procesin në studim. Është ndërtuar në përputhje me ekuacionin:

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 e ordinatave.

2 . Një linjë prirje polinomiale është e dobishme për të përshkruar karakteristikat që kanë disa ekstreme të dallueshme (maksimum dhe minimum). Zgjedhja e shkallës polinomiale 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 prirjes logaritmike përdoret me sukses gjatë modelimit të karakteristikave, vlerat e të cilave fillimisht ndryshojnë me shpejtësi dhe më pas stabilizohen gradualisht.

y = c ln(x) + b

4 . Linja e prirjes së ligjit të fuqisë jep rezultate të mira nëse vlerat e marrëdhënies në studim karakterizohen nga një ndryshim i vazhdueshëm në normën e rritjes. Një shembull i një varësie të tillë është grafiku i lëvizjes së njëtrajtshme të përshpejtuar të një makine. Nëse ka vlera zero ose negative në të dhëna, nuk mund të përdorni një linjë të prirjes së energjisë.

E ndërtuar në përputhje me ekuacionin:

y = c xb

ku koeficientët b, c janë konstante.

5 . Një linjë trendi eksponenciale duhet të përdoret kur 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 është i zbatueshëm.

E ndërtuar në përputhje me ekuacionin:

y = c ebx

ku koeficientët b, c janë konstante.

Kur zgjedh një linjë trendi, Excel llogarit automatikisht vlerën e R2, e cila karakterizon besueshmërinë e përafrimit: sa më afër unitetit të jetë vlera R2, aq më e besueshme është linja e tendencës përafërsisht procesin në studim. Nëse është e nevojshme, vlera R2 mund të shfaqet gjithmonë në grafik.

Përcaktohet nga formula:

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

aktivizoni një grafik bazuar në një seri të dhënash, p.sh. klikoni brenda zonës së grafikut. Artikulli Diagram 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 mund të zbatohen lehtësisht duke lëvizur treguesin e miut mbi grafikun që korrespondon me një nga seritë e të dhënave dhe duke klikuar 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:

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

1 . Fusha e ndërtuar në seri rendit 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ë trendit në zonën e grafikut, për të cilën duhet të aktivizoni kutinë e zgjedhjes "shfaq ekuacionin në grafik";

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

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

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

Për të filluar redaktimin e një linje tendence të vizatuar tashmë, ekzistojnë tre mënyra:

përdorni komandën Selected trend line nga menyja Format, pasi keni zgjedhur më parë linjën e trendit;

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

klikoni dy herë në vijën e trendit.

Kutia e dialogut Formati i linjës së tendencës do të shfaqet në ekran (Fig. 3), që përmban tre skeda: Pamja, Lloji, Parametrat dhe përmbajtja e dy të fundit përputhet plotësisht me skedat e ngjashme të kutisë së dialogut të linjës së trendit (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 që është tërhequr tashmë, zgjidhni vijë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 ndërtimit 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;

aftësia për të parashikuar sjelljen e procesit në studim nga një numër arbitrar (brenda kufijve të sensit të përbashkët) hapash përpara dhe gjithashtu prapa;

aftësia për të marrë ekuacionin e linjës së trendit në 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ë si më poshtë:

ndërtimi i një linje trendi kryhet vetëm nëse ekziston një diagram 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 kërkuara 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, ndryshimi i pamjes së grafikut ose raportit të PivotTable shoqërues nuk ruan linjat ekzistuese të trendit, që do të thotë se përpara se të vizatoni vija trendi ose të formatoni ndryshe një raport PivotChart, duhet të siguroheni që paraqitja e raportit plotëson kërkesat e kërkuara.

Linjat e tendencës mund të përdoren për të plotësuar seritë e të dhënave të paraqitura në grafikët si grafikët, histogramet, grafikët e zonave të sheshta jo të standardizuara, grafikët me shtylla, grafikët e shpërndarjes, grafikët me flluska dhe grafikët e aksioneve.

Ju nuk mund të shtoni linja prirje në seritë e të dhënave në grafikët 3D, të normalizuar, radar, byrek dhe donut.

Përdorimi i funksioneve të integruara të Excel

Excel ka gjithashtu një mjet për analizën e regresionit për vizatimin e linjave të tendencës jashtë zonës së grafikut. Ekzistojnë një sërë funksionesh statistikore të fletës së punës që mund të përdoren për këtë qëllim, por të gjitha ato lejojnë 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:

LGRFPRIBL.

Duhet të theksohet se teknikat për ndërtimin e regresioneve duke përdorur funksionet TREND dhe GROWTH janë pothuajse 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, krijimi i një tabele vlerash përdor veçori të Excel-it si formulat e grupeve, të cilat disi rrëmujnë procesin e ndërtimit të regresioneve. Vini re gjithashtu se ndërtimi i regresionit linear, sipas mendimit tonë, realizohet më lehtë 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 ndërprerë nga regresioni në y. -bosht.

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

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

metodologji 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 me numrin e kërkuar të hapave përpara ose prapa.

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 që janë afër realitetit. Përveç kësaj, kur përdorni funksionet TREND dhe RRITJE, ekuacionet e linjave të trendit nuk dihen.

Duhet të theksohet se autorët nuk u përpoqën të paraqisnin kursin e analizës së regresionit me ndonjë shkallë të plotësisë. Detyra e tij kryesore është të tregojë, duke përdorur shembuj specifikë, aftësitë e paketës Excel kur zgjidh problemet e përafrimit; demonstroni se çfarë mjetesh efektive ka Excel për ndërtimin e regresioneve dhe parashikimit; ilustrojnë se si probleme të tilla mund të zgjidhen relativisht lehtë edhe nga një përdorues që nuk ka njohuri të gjera për analizën e regresionit.

Shembuj të zgjidhjes së problemeve specifike

Le të shqyrtojmë zgjidhjen e problemeve specifike duke përdorur mjetet e listuara në paketën Excel.

Problemi 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ë diagram.

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

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

Bëni një parashikim për fitimin e ndërmarrjes për 2003 dhe 2004.

Zgjidhja e problemit

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

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

Aktivizojmë diagramin e ndërtuar dhe, sipas metodës së përshkruar më sipër, pasi kemi zgjedhur llojin e linjës së trendit në kutinë e dialogut të linjës së tendencës (shih Fig. 1), shtojmë në mënyrë alternative në diagram linjat e trendit linear, kuadratik dhe kub. 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ë po 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 ekuacionin e shfaqjes në kutitë e kontrollit të ekranit dhe vendosni 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ë tendencave të ndërtuara, për të cilat përdorim skedën View në kutinë e dialogut Formati i linjës së trendit (shih Fig. 3). Diagrami që rezulton me linjat e tendencës së shtuar është paraqitur në Fig. 5.

Për të marrë të dhëna tabelare mbi fitimet e ndërmarrjeve për çdo linjë trendi për 1995-2004.

Le të përdorim ekuacionet e linjës së trendit të paraqitur në Fig. 5. Për ta bërë këtë, në qelizat e diapazonit D3:F3, futni informacionin e tekstit 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ë diapazonin 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, plotësoni gamën e qelizave E4:E13, dhe për regresionin kub, plotësoni gamën e qelizave F4:F13. Kështu, është përpiluar një parashikim për fitimin e ndërmarrjes për vitet 2003 dhe 2004. duke përdorur tre tendenca. Tabela që rezulton e vlerave është paraqitur në Fig. 6.

Ndërtoni një diagram.

Problemi 2

Shtoni linjat e tendencës logaritmike, fuqisë 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ë prirjes, merrni të dhëna tabelare mbi fitimin e ndërmarrjes për secilën linjë trendi për 1995-2002.

Zgjidhja e problemit

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

Në Fig. 5 dhe fig. mund të shihet se modeli me prirje logaritmike i korrespondon vlerës 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).

Problemi 3

Me tabelën e të dhënave për 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 të ndërmarrjes për 2003 dhe 2004.

Ndërtoni një diagram për të dhënat origjinale dhe seritë e të dhënave që rezultojnë.

Zgjidhja e problemit

Le të përdorim fletën e punës për problemin 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 klikuar butonin (Insert Function) 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 e_njohura_x - diapazoni i qelizave B4:B11;

Për ta bërë formulën e futur të bëhet 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ë ndërmarrjes 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 e_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 kombinimin e tastit 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).

Seria e të dhënave plotësohet në mënyrë të ngjashme duke përdorur funksionin GROWTH, i cili përdoret në analizën e varësive jolineare dhe funksionon saktësisht në të njëjtën mënyrë 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. 11.

Problemi 4

Me tabelën e të dhënave për marrjen e aplikacioneve për shërbime nga shërbimi dispeçer i një ndërmarrje transporti motorik për periudhën nga data 1 deri në 11 të muajit aktual, duhet të kryeni 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 regresionin eksponencial duke përdorur funksionin LGRFPRIBL.

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.

Krijo një diagram për serinë e të dhënave origjinale dhe të marra.

Zgjidhja e problemit

Vini re se, ndryshe nga funksionet TREND dhe GROWTH, asnjë nga funksionet e listuara më sipër (SHPJETËSIA, INTERCEPT, LINEST, LGRFPRIB) nuk janë regresioni. Këto funksione luajnë vetëm një rol mbështetës, duke përcaktuar parametrat e nevojshëm të regresionit.

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

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

y = mx+b

duke përdorur funksionet SLOPE dhe INTERCEPT, me pjerrësinë e regresionit m të përcaktuar nga funksioni SLOPE, dhe termin e lirë b nga funksioni INTERCEPT.

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

futni tabelën origjinale në diapazonin e qelizave A4:B14;

vlera e parametrit m do të përcaktohet në qelizën C19. Zgjidhni funksionin Slope nga kategoria Statistikore; 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);

Më pas, futni 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ë gjatë kopjimit të 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.

2 Duke përdorur dorezën e mbushjes, kopjoni këtë formulë në gamën e qelizave C4:C17. Ne marrim seritë e kërkuara 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 numrave me numrin e numrave dhjetorë në 0 në skedën Number në dritaren e Formatit të qelizës.

y = mx+b

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

duke përdorur funksionin LINEST.

Për ta bërë këtë:

Futni funksionin LINEST si një formulë grupi në diapazonin 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;

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

. Ne ndërtojmë një regresion eksponencial me ekuacionin:

duke përdorur funksionin LGRFPRIBL kryhet në mënyrë të ngjashme:

Në rangun e qelizave C21:D21 ne futim 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. Figura 13 tregon një tabelë ku mund të shihni funksionet që përdorim me diapazonin e kërkuar të qelizave, si dhe formulat. Madhësia 2 R 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 duke përdorur formulën n Ku

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

k është numri i koeficientëve të modelit. n Dhe Nëse F tejkalon një vlerë kritike për të dhënat k

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ë i barabartë me 1 (një vijë e vetme e drejtë mund të vizatohet gjithmonë përmes 2 pikave në një plan). 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, ata përpiqen të sigurojnë që numri i eksperimenteve të tejkalojë ndjeshëm numrin e koeficientëve të modelit (n>k).

Për të ndërtuar një lineare modeli i regresionit nevojshme:

1) përgatit një listë me n rreshta dhe m kolona që përmbajnë të dhëna 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 nga detyra e mëparshme, duke shtuar një kolonë të quajtur "Nr. Periudha", numëroni numrat e periudhës nga 1 në 12. (këto do të jenë vlerat X)

2) shkoni te menyja Data/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" në të njëjtën meny dhe të kontrolloni kutinë e kontrollit "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 vendosja e tyre në një fletë pune të re);

4) klikoni "Ok" dhe analizoni rezultatet.

Artikulli i mëparshëm: Sa është shpejtësia e dritës Artikulli vijues: Lëkundjet harmonike Formula e fizikës së frekuencës së lëkundjeve