Ukuhlehla Excel: imizekelo equation. linear nokushiywa

Uhlalutyo ukuhlehla - indlela yokufunda-manani ukubonisa ukuxhomekeka kwe parameter enye okanye ukuzimela variables. Xa era pre-computer, ukusetyenziswa kwayo kuye kube kunokuba nzima, ingakumbi xa beza imithamo ezinkulu data. Namhlanje, ukufunda ukwakha indlela ukuhlehla Excel, uyakwazi ukucombulula iingxaki ezintsonkothileyo manani kwimizuzu nje embalwa. Ngezantsi kukho imizekelo ezithile ngezoqoqosho.

iintlobo nokushiywa

Lo mba yaziswa ootitshala beMathematika ngu Francis Galton ngo-1886. Nokushiywa ngu:

• linear;
• oluziphumelayo;
• amandla;
• kobudlelwano;
• cosine;
• kobudlelwano;
• lwe logarithm olusezantsi.

UMZEKELO 1

Cinga ingxaki yokufumanisa ukuxhomekeka inani abarhoxileyo lamalungu abasebenzi umvuzo kwi amashishini mveliso 6.

Task. iinkampani amathandathu sahlaziya umvuzo avareji ngenyanga kwaye nenani labasebenzi eyayeka ngokuzithandela. Xa uhlobo tabular esinayo:

	A	B	C
1	X	Inani emsebenzini	iholo
2		y	30000 engange
3	1	60	35000 engange
4	2	35	40000 engange
5	3	20	45000 engange
6	4	20	engange 50,000
7	5	15	55000 engange
8	6	15	60000 engange

Kuba ingxaki yokufumanisa ukuxhomekeka yabasebenzi mali esahlukanayo ukusuka elingu kumashishini 6 umfuziselo Isemantiki na uhlobo equation Y = ₀ + ₁ x ₁ + ... + a _k x _k, apho x _i - izinto eziphembelela, kukho _i - Isemantiki okuza, ak - eziliqela.

ukuba kumsebenzi onikiweyo Y - kuba sisalathisi emlilweni umqeshwa, kudlale indima - umvuzo, nto leyo luchazwa ngu X.

Kunye nokusetyenziswa amandla "Excel" spreadsheet

Uhlalutyo ukuhlehla Excel kufuneka lwandulelwe isicelo data itheyibhile ekhoyo eyakhelwe-ngaphakathi imisebenzi. Nangona kunjalo, kuba ezi njongo kulungile ukusebenzisa i "uhlalutyo ipakethe" luncedo kakhulu wokongeza i-ngaphakathi. Ukuze isebenze, kufuneka:

• kunye ithebhu "Wefayile" yiya kwi "Izilungiselelo";
• kwi window evulayo, khetha 'Yongeza-ons';
• klikha kwiqhosha "Yiya", sise ezantsi ekunene kumgca "ulawulo";
• ecaleni uphawu isheke "Uhlalutyo ToolPak" kwaye uqinisekise isenzo yakho ngokucinezela "Kulungile".

Ukuba yenziwe ngendlela eyiyo, kwicala lasekunene "Data" tab, ibekwe ngaphezu uxwebhu lokusebenzela "Excel", ibonisa olifunayo iqhosha.

Linear ukuhlehla Excel

Ngoku onalo esandleni zonke izixhobo ezifunekayo ukwenzela izibalo woqoqosho, singaqala ukusombulula ingxaki yethu. Ukuze wenze oku:

• iqhosha lasekunene linqakraziwe kwi "Uhlalutyo Data";
• klikha kwiqhosha "ukuhlehla" kwi window evulekileyo;
• ithebhu ebonakala ukwazisa uluhlu lwamaxabiso Y (inani labasebenzi esahlukanayo) kunye X (umvuzo wabo);
• uvuselele izenzo zabo ngokucinezela i «Kulungile» iqhosha.

Ngenxa yoko, le program iya kuthi ngokuzenzekelayo bazalise uhlalutyo entsha data nokubuy'umva sheet spreadsheet. Ziqapheleni! Ngo-Excel, kukho ithuba lokuba ukucwangcisa ndawo ukuba ukhetha le njongo. Umzekelo, kusenokuba iphepha enye, apho iinqobo Y no X, okanye incwadi entsha, esenzelwe ngokukodwa ukugcinwa kweenkcukacha ezinjalo.

Iziphumo zohlalutyo ukuhlehla ye R-square

Le data Excel afunyenwe kwi ingqalelo umzekelo data babe ifomu:

Kuqala kwinto zonke, simele sinikele ingqalelo ixabiso R-ophindwe kabini. Umele ukungavumelani kwe nokuzimisela. Kulo mzekelo, R-square = 0.755 (75,5%), m. E. Ipharamitha ebalwe yemodeli ukucacisa ubudlelwane phakathi kwe parameters ingqalelo yi-75,5%. Ukuba liphakamile ixabiso le ukungavumelani kwe nokuzimisela, imodeli ekhethiweyo uthathwa ukuba luncedo ngakumbi imisebenzi ethile. Kukholelwa ukuba uchaze ngokuchanekileyo imeko yokwenene ngokwexabiso R-square ngentla 0.8. Ukuba R-isikwere <0.5, ngoko uhlalutyo ukuhlehla Excel ayikwazi engamelanga ithathwe njenge ngqiqweni.

analysis ratio

Inani 64,1428 kubonisa uya kuba yintoni na ixabiso Y, ukuba zonke izinto xi somfuziselo lwethu luya kuba reset. Ngamanye amazwi, oku Kungaphikiswa ukuba ixabiso parameter zihlalutywe iphenjelelwa neminye imiba ngaphandle kwezo ezichazwe kwi imodeli ethile.

Le into elandelayo -0,16285 ibekwe iseli B18, ibonisa impembelelo ebalulekileyo X kwi Y. Oku kuthetha ukuba elingu yabasebenzi ngaphakathi imodeli kuchaphazela inani abarhoxileyo ukusuka ubunzima -0,16285, t. E. Ubungakanani nempembelelo yayo yonke encinane. Umqondiso "-" ibonisa ukuba inani lelikhabayo. Kucacile, ekubeni sonke siyazi ukuba yomvuzo ngakumbi kwishishini, abantu ngaphantsi baye babonisa umnqweno lokuphelisa isivumelwano sengqesho okanye sikhatywe.

nokushiywa multiple

Phantsi eli gama libhekisela inxaki kunxibelelwano variables ezizimeleyo ifomu ezininzi:

y = f (x ₁ + x ₂ + ... x _m) + ε, apho y - yinto amanqaku olulongezo (variable kuxhomekeke), kunye x _1, x _2, ... x _m - zimpawu zinto (variables ezizimeleyo).

ukulinganisa parameter

Kuba nokushiywa ezininzi (MR) oko kwenziwa usebenzisa izikwere indlela ubuncinane (LSM). Kuba quadratic linear zefom Y = a + b ₁ x _{1 + ... + b m x m +} ε ekwakheni inkqubo quadratic yesiqhelo (cm. Ngezantsi)

Ukuze uqonde umgaqo le ndlela, sicinga ityala factor-mbini. Emva koko ke le meko ichazwe yi kwifomula

Ngenxa yoko, nathi nakufumana nto noko;

apho σ - na umahluko lo msebenzi ochaphazelekayo, kubonakala kwisalathiso.

MNC usebenza inxaki MR ukuba standartiziruemom isikali. Kulo mzekelo, sifumana i equation:

kuyo t _y, t _{x 1, ...} t _xm - standartiziruemye eziguquguqukayo apho amaxabiso avareji 0; β _i - okuza nokushiywa ekumgangatho ecaleni - 1.

Kucelwa niqaphele ukuba bonke β _i kulo mzekelo zichazwa nganye ezinga malungu abantu kunye tsentraliziruemye, ngoko uthelekiso phakathi kuthathwa njengezisemthethweni kwaye kwamkelekile. Ukongeza, ukuba selikho, yamkeleka ukuphumeza ukuhlolwa zinto, ulahle kweno amaxabiso ophantsi βi.

Ingxaki usebenzisa yomgama ukuhlehla equation

Masithi unayo itafile Dynamics ixabiso lemveliso N ethile kwiinyanga 8 nyakenye. Kuyimfuneko ukuba isigqibo sokuba nokufunyanwa iqela lakhe amaxabiso ka-1850 engange. / T.

	A	B	C
1	ngenyanga	igama lenyanga	Price N
2	1	January	1750 engange ngetoni nganye
3	2	February	1755 engange ngetoni nganye
4	3	March	1767 engange ngetoni nganye
5	4	April	1760 engange ngetoni nganye
6	5	May	1770 engange ngetoni nganye
7	6	June	1790 engange ngetoni nganye
8	7	July	1810 engange ngetoni nganye
9	8	August	1840 engange ngetoni nganye

Ukusombulula le ngxaki kumqhubekekisi tabular "Excel" kufuneka ukuba usebenzise sele eyaziwa umzekelo isixhobo "Uhlalutyo Data" uchazwe apha ngasentla. Okulandelayo, khetha icandelo 'ukuhlehla' yaye ubeke parameters. Simele sikhumbule ukuba i "Igalelo uluhlu Y» kufuneka anikwe uluhlu amaxabiso variable oxhomekeke (kule meko ixabiso leempahla kwiinyanga ezithile unyaka wonke) kwi "Igalelo lekhefu X» - ukuba ezimeleyo (ngenyanga). Siza kuqinisekisa isenzo ngokucofa «Kulungile». Xa lokusebenzela elitsha (ukuba kuboniswe oko), siya ufumane iinkcukacha ukuze ukuqhubeka kwaba bafundi.

Sakha kubo equation linear zefom y = izembe + b, apho njengoko parameters a kunye b ke okuza ukusuka kwinani umgca kwinyanga kunye negama okuza kunye Y-ekudibaneni 'umgca wetshati kunye neziphumo zohlalutyo parameter nganye ye «. Ngoko ke, i parameter nganye equation yomgama (EQ) 3 ngxaki angabhalwa ngolu hlobo:

Ixabiso lempahla N = 11,714 * 1727.54 ngenyanga Inombolo +.

okanye kwamanani ngokwe-aljibra

y = 11.714 x + 1727,54

Uhlalutyo lweziphumo

Ukuba isigqibo sokuba i equation ngokwaneleyo yomgama parameter nganye usebenzisa i okuza ezininzi ukuhlanganisa (CMC) kunye nokuzimisela kwakunye uvavanyo kunye Fisher kaThixo t-uvavanyo ezifunyenweyo. Kwitheyibhile "Excel 'ukuhlehla kunye neziphumo baya kusebenza phantsi amagama multiple R, R-Square, F-t-manani kunye manani, ngokulandelelana kwazo.

KMC R yenza ukuba ukuqikelela ukumanyana ulwalamano probabilistic phakathi kwezinto elizimeleyo zixhomekeke. ixabiso layo eliphezulu ibonisa uqhagamshelwano ngokwaneleyo phakathi "Number of kwinyanga 'elitshintshayo womelele" ixabiso Product N e engange ngetoni 1 nganye. " Nangona kunjalo, uhlobo lo ubuhlobo ayaziwa.

Isikweri ukungavumelani kwe nokuzimisela R ² (RI) luphawu yamanani komyinge we agcwayele zizonke yaye kubonisa Wazibhangisa inxenye data uvavanyo, ngamanye amazwi, amaxabiso variable ixhomekeke ehambelana yabaxwa i equation yomgama. Kule ngxaki, eli xabiso yi 84,8%, MP. E. manani ngezinga eliphezulu okuchaneka wafumana zichazwe SD.

F-manani, okwabizwa ngokuba Fisher njengenqobo kusetyenziswa ukuvavanya ukubaluleka ukuxhomekeka yomgama okanye disproving hypothesis engqina ubukho bayo.

Ixabiso t-manani (Student kaThixo t test) inceda ukuvavanya intsingiselo ngumlingani ngalo naliphi unknown ilungu ukuxhomekeka yomgama free. Ukuba ixabiso t-test> t _cr, hypothesis ka i'quadratic akabalulekanga linear kwekota free yaliwe.

Kule ngxaki ikota simahla ngokusebenzisa izixhobo "Excel" kwafunyaniswa ukuba t = 169,20903, kunye p = 2,89E-12, t. E. Yiba namathuba zero ukuba abathembekileyo izakwaliwa hypothesis ye akalutho kwekota free. Kuba lomlingani ayikaziwa t = 5,79405, kunye p = 0,001158. Ngamanye amazwi, lo kungenzeka ukuba hypothesis oluchanekileyo ulicekisile ziya akalutho ngumlingani ngenxa ezingaziwayo, yi 0.12%.

Ngenxa yoko, Kungaphikiswa ukuba yomgama parameter nganye equation zifumaneka ngokwaneleyo.

Ingxaki yokucebisana kokuthenga izabelo

nokushiywa Multiple senziwa Excel usebenzisa efanayo "Uhlalutyo Data" isixhobo. Cinga isicelo esithile.

Guide inkampani «NNN» kufuneka enze isigqibo sokuba ngaba ukuthenga-20% yezabelo of JSC «MMM». ixabiso Package (SP) million 70 US zeerandi. Iingcali ze «NNN» waqokelela data kwiintengiselwano efanayo. Kwagqitywa ukuvavanya ixabiso lezabelo kwi ezinjalo parameters, ochazwa izigidi zeerandi US, ezifana:

• ezihlawulwayo (VK);
• imali yonyaka umthamo (IVOTI);
• kufunyanwa (vd);
• ixabiso ezingahambisekiyo (SOF).

Ukongeza, sebenzisa Amatyala umvuzo kwamashishini (V3 U) e amawaka eerandi US.

Le theyibhile Isigqibo iprosesa Excel iindlela

Okokuqala kufuneka ukwenza itheyibhile data igalelo. Oko ngolu hlobo lulandelayo:

Next:

• ibhokisi call "uhlalutyo lweenkcukacha";
• ekhethiweyo "nokushiywa" candelo;
• i window "Igalelo lokuphumla uluhlu Y» ezilawulwa amaxabiso zixhomekeke eziguquguqukayo ukusuka kumhlathi G;
• nqakraza kwi icon kunye notolo ebomvu ukuya ekunene kwe window "Igalelo lokuphumla X» kwaye bodwa kwi kuluhlu sheet yazo zonke amaxabiso lomxholo B, C, D, F.

Phawula le ngongoma "lokusebenzela Entsha" uklikhe "Kulungile".

Fumana uhlalutyo Isemantiki lo msebenzi.

Iziphumo zokufunda kunye nezigqibo

"Qokelela 'elirhangqiweyo ukusuka data thaca ngentla kuxwebhu kwetafile Excel iprosesa parameter nganye lenxaki:

SD = 0,103 * SOF + 0,541 * IVOTI - 0.031 * VK + 0,405 * vd + 0,691 * VZP - 265.844.

Kule fomu ngakumbi njengesiqhelo zezibalo ngayo angabhalwa ngolu hlobo:

y = 0.103 * x1 + 0,541 * x2 - 0.031 * x3 + 0,405 * x4 + 0,691 * x5 - 265.844

Idatha ye «MMM» JSC lucaciswa kule theyibhile ilapha ngezantsi:

SOF, USD	IVOTI, USD	VK, USD	Vd, USD	VZP, USD	JV, USD
102,5	535,5	45,2	41,5	21,55	64,72

Kufakwe kwelo quadratic parameter nganye, wafumana oyintsobi yalowo million 64,72 US zeerandi. Oku kuthetha ukuba izabelo JSC «MMM» kufuneka uthenge, kuba iindleko yabo kakhulu overpriced kwi-70 ezigidi US zeerandi.

Njengoko ubona, ukusetyenziswa spreadsheet "Excel" kwaye i equation parameter nganye kuvunyelwa ukuba benze izigqibo benolwazi malunga awushiye intengiselwano kakhulu ezithile.

Ngoku uyazi ukuba yintoni yabaxwa. Imizekelo ku Excel, kuxoxwe ngasentla, kuya kukunceda ekusombululeni iingxaki bobuqili oqosho.