Dhaca ee Excel, tusaalooyin isla'egta. toosan dhaca

Falanqaynta dhaca - hab xisaabeed waxbarasho si ay u muujiyaan tiirsanaanta ee u dhimaya nabadgelyada ah mid ama in ka badan oo madax bannaan doorsoomayaasha. Ayado pre-computer ka, isticmaalkeeda ayaa halkii ay adag tahay, gaar ahaan marka ay u yimaadeen inay u tiro badan oo data. Maanta, barashada sida loo dhiso dhaca ah ee Excel, aad xal u heli karo dhibaatooyinka tirakoobka adag in dhawr daqiiqo. Hoos waxaa ku qoran tusaalooyinka gaarka ah ee dhaqaalaha.

noocyada dhaca

Aragtidani waxa ay la soo bandhigay in xisaabta by Francis Galton ee 1886. Dhaca waa:

• toosan;
• saxan;
• xooggiisa;
• jibbaaranaha;
• hyperbolic;
• jibbaaranaha;
• logardam ah.

TUSAALE 1

Tixgeli dhibaatada go'aaminta ku tiirsanaanta ee tirada iscasilaaddii xubnaha shaqaalaha ee mushaharka celcelis ahaan 6 shirkadaha warshadaha.

Task. Lix shirkadood ayaa la falanqeeyay celceliska mushaharka bil kasta iyo tirada shaqaalaha si iskood ah ka tago. In foomka tabular waxaan haysanaa:

	A	B	C
1	X	Number of iscasilaaddii	mushaar
2		y	30000 rubles
3	1	60	35000 rubles
4	2	35	40000 rubles
5	3	20	45000 rubles
6	4	20	50,000 rubles
7	5	15	55000 rubles
8	6	15	60000 rubles

Wixii dhibaato ah ee go'aaminta ku tiirsanaanta oo ka mid ah shaqaalaha kala lacagta ka celceliska mushaharka 6 shirkadaha model dhaca uu leeyahay qaab isla'eg Y = ₀ + ₁ x ₁ + ... + a _k x _k, halkaas oo x _i - doorsoomayaasha kasbashada, _i a - horgalaha u dhaca, qoryaha AK - tirada arrimood.

Y waayo hawl la siiyey - waa tilmaame in dab shaqaale, cunsur ka qayb qaata - mushaharka, taas oo muujinaysaa by X.

Faa'iidaysiga xoogga "Excel" isbiriidhshiit

Falanqaynta dhaca ee Excel waa in uu ka muuqadaa codsi u xogta miiska jira dhisay-in hawlaha. Si kastaba ha ahaatee, ujeeddooyinkan waxaa wanaagsan in la isticmaalo "falanqaynta baakadda" a dar-ku aad u faa'iido badan. Si aad u awood, waxaad u baahan tahay:

• la "File" tab inay tagaan si ay "Settings";
• furmo in furay, dooro 'dar-ons';
• riix badhanka "u tag", ku yaalaa xaq hoose ee khadka "maamulka";
• calaamad jeeg soo socda si ay "ToolPak Falanqaynta" iyo xaqiijiyo tallaabo adigoo riixaya "OK".

Haddii si sax ah u sameeyey, dhinaca midig ee ah "Data" tab, ku yaal kor ku xusan warqadda shaqada "Excel", waxay muujinaysaa button la doonayo.

Toosan dhaca ee Excel

Hadda oo aad leedahay on gacanta oo dhan qalabka dalwaddii lagama maarmaanka u ah xisaabinta econometric, waxaan bilaabi karaan in ay wax ka qabato dhibaatada our. Si arrintan loo sameeyo:

• button waxaa riixi on the "Saafidda Xogta";
• riix batoonka "dhaca" ee uu furmo suuqa furan;
• tab ah oo u muuqata inay soo bandhigto kala duwan oo ka mid ah qiyamka Y (tirada shaqaalaha kala) iyo X (mushaar);
• caddaysaan falalka ay adigoo riixaya ee «Waayahay» button.

Sidaas darteed, barnaamijka si toos ah ka buuxin doonaa falanqaynta xogta dhaca sheet isbiriidhshiit cusub. Waa inaad taxadar! In Excel, waxaa jirta fursad ay si dhigay meeshii aad jeceshahay ujeedadaas. Tusaale ahaan, waxaa laga yaabaa in sheet isla, halkaas oo qiimaha Y iyo X, ama xataa buug cusub, oo si gaar ah loogu talagalay kaydinta xogta sida.

Natiijooyinka falanqaynta dhaca for R-square

Xogta Excel helay in tusaale ahaan xogta loo arkaa leeyihiin foomka:

First of dhan, waa in aan ku bixino qiimaha R-gees dareenka. Waxay ka dhigan tahay Wehliyaha ee go'aan. Tusaalahaan, R-square = 0.755 (75,5%), m. E. beegyada uu xisaabiyaa of model ah si ay u sharxaan xidhiidhka ka dhexeeya oo xuduudaheedu ku tixgeliyo by 75,5%. Qiimaha sare ee Wehliyaha ee go'aanka, model ee la doortay waxaa loo arkaa inay ka badan waxtar u leh hawlaha gaar ah. Waxaa la rumeysan yahay in ay si sax ah u qeexo xaaladda dhabta ah ee qiimaha R-square kor 0.8 ah. Haddii R-square <0.5, ka dibna falanqaynta dhaca ee Excel ah ma la oran karo waa macquul.

falanqaynta ratio

Number 64,1428 muujinaysaa waxa ay noqon doontaa qiimaha Y ah, haddii dhammaan xi doorsoomayaasha ku ku jira model noqon doonaa keddibna. In si kale loo dhigo, waxaa lagu murmi karaa in qiimaha dhimaya ee la falanqeeyay waxaa saameeya arrimo kale oo aan ahayn kuwa lagu tilmaamay in model gaarka ah.

factor xigta -0,16285 ku yaalla B18 cell, ayaa muujinaysa saamaynta muhiimka ah ee variable X in Y. Taas macnaheedu waa in celceliska mushaharka shaqaalaha model gudahood saameeyaa tirada iscasilaaddii ka miisaanka -0,16285, t. E. degree ee saamaynta ay dhammaan yar. Calaamadda "-" waxay muujinaysaa in Wehliyaha waa negative. Waa wax iska cad, tan iyo markii aynu wada ognahay in mushaar ka badan ee shirkadu, dadka ka yar ayaa sheegay inuu doonayo inuu joojiyo heshiiska shaqada ama xilka.

dhaca badan

Under in eraygani uu tilmaamayo isla'egta isgaarsiinta dhowr doorsoome madax banaan ee foomka ah:

y = f (x ₁ + x ₂ + ... x _m) + ε, halkaas oo y - waa score feature a (variable ku tiirsan), iyo x _{1, 2 x,} ... x _m - waxaa ugu sugan calaamooyin arrimood (Variables madax bannaan).

qiimaysay dhimaya

Waayo, dhaca badan (MR) oofiyey tahay adeegsiga habka god ugu yaraan (LSM). Waayo, isleegyo qumman foomka Y = a + b ah ₁ x _{1 + ... + b m x m +} ε dhisidda nidaam isleegyo caadiga ah (cm Hoos.)

Si loo fahmo mabda'a of habka, waxaanu ka fiirsan kiiska laba-factor ee. Haddaba, waxaan ku xaaladda ku tilmaamay by caanaha

Sidaa awgeed, waxaan ka heli:

halkaas oo σ - waa cadow ee feature ku kala, ka muuqataa index ah.

MNC waa mid ku habboon in isla'egta MR si standartiziruemom qiyaasta. Xaaladdan oo kale, aan ka helno isla'egta:

diidanyihiin t _y, t _{x 1, ...} t _XM - standartiziruemye doorsoomayaasha kaas oo qiimaha celcelis ahaan waa 0; _i β - horgalaha u dhaca heerarka u dejisan iyo weecasho caadiga ah - 1.

Fadlan la soco in dhammaan _i β kiiskan lagu qeexaa sida ugu caadi iyo tsentraliziruemye, sidaas darteed marka la barbardhigo u dhexeeya a tixgelin doonaa mid ansax ah iyo mid la aqbali karo. Intaa waxaa dheer, waxaa loo aqbalay si ay u fuliyaan baadhista arrimood, qubaan kuwa in ay leeyihiin qiimaha ugu hooseeya ee βi.

Dhibaatadu waxay la isticmaalayo isleegta dhaca toosan

Ka soo qaad inaad qabto miis oo ka mid ah dhaqdhaqaaqa of qiimaha N ah wax soo saarka gaar ah 8 bilood ee lasoo dhaafay. Waxaa lagama maarmaan ah in ay go'aan ka gaarto in afku uu xisbiga ugu qiimaha 1850 rubles. / T.

	A	B	C
1	bisheedii	magacaabi ee bisha	Price N
2	1	January	1750 rubles per ton
3	2	February	1755 rubles per ton
4	3	March	1767 rubles per ton
5	4	April	1760 rubles per ton
6	5	May	1770 rubles per ton
7	6	June	1790 rubles per ton
8	7	July	1810 rubles per ton
9	8	August	1840 rubles per ton

Si ay u xaliyaan dhibaatadan in processor ka tabular "Excel" looga baahan yahay in ay isticmaalaan hore u ogaan lahaa tusaale ahaan qalab "Saafidda Xogta" kor ku soo bandhigay. Next, dooran qaybta "dhaca" iyo xuduudaheedu dhigay. Waa in aan xasuusnaano in ee "duwan Input Y» waa in la soo bandhigay in kala duwan oo ka mid ah qiyamka variable ku tiirsan (haddii ay taasi qiimaha alaabta bilihii gaarka ah ee sanadka) iyo "Input bareeg X» - waayo madax-bannaan ah (bishii). Waxaan xaqiijin talaabo adigoo gujinaya «Waayahay». In warqadda cusub ah (haddii ay sidaas tahay tilmaamay), waxaan aad u hesho xogta ee dhaca ah.

Waxa aanu dhisaynaa iyaga on isla'egta toosan ee foomka y = faaska + b, halkaas oo sida la koobin iyo b yihiin horgalaha ka tirada line of bisha iyo magaca horgalaha iyo «Y-isgoyska" line of xaashida la natiijooyinku falanqaynta dhaca ah. Sayidka, isla'egta toosan dhaca (Equatorial) 3 dhibaatada waxaa loo qori karaa sidan:

Qiimaha badeecadaha N = 11,714 * 1727.54 bishii tirada +.

ama qormo aljabrada

y = x + 11.714 1727,54

Falanqaynta of natiijooyinka

Si aad go'aan ka gaarto in ka helay isla'eg u dhaca ku filan toosan isticmaalaya horgalaha badan xiriir (CMC) iyo go'aan iyo sidoo kale baaritaanka iyo Fisher ee imtixaanka t. In miiska "Excel" dhaca la natiijooyinkii ay u dhaqmaan hoos magacyada badan R, R-Square, F-t-tirakoobka iyo tirakoobka, siday u kala horreeyaan.

KMC R u saamaxaaya in la qiyaaso xiriir soke ee probabilistic dhexeeya doorsoomayaasha madax banaan oo ku tiirsan. Its qiimaha sare muujinaysaa a xiriir adag oo u dhexeeya "Number of bishii" ayuu variable iyo "Qiimaha Product N in rubles halkii 1 ton." Si kastaba ha ahaatee, dabeecadda xiriirka this waa la garanayn.

square ee Wehliyaha ee go'aanka R ² (RI) waa dabeecad u tiro ka mid ah saamiga of total kala firdhiso oo ay muujinaysaa kala firdhiso oo ah qayb xogta tijaabo ah, i.e., qiimaha variable ku tiirsan u dhiganta isla'egta a dhaca toosan. In dhibaatadan, qiimaha ay tani tahay 84,8%, xildhibaan. E. Statistics leh Heer sare ah ee saxsanaanta helay waxa lagu sharaxay SD.

F-statistics, sidoo kale loo yaqaano kala bixiyc Fisher isticmaalaa in lagu qiimeeyo muhiimadda tiirsanaanta toosan ama disproving la xiqiijiyay in xaqiijinaya jiritaankeeda.

Qiimaha t-tiro (imtixaanka t Ardayga) ka caawisaa qiimeeyo muhiimadda Wehliyaha ee xubin kasta oo ku tiirsanaanta free la garanayn toosan. Haddii qiimaha-imtixaanka t> _{Cr t,} xiqiijiyay of insignificance ah isla'egta toosan muddada free la diido.

In dhibaatada this muddo free marayo alaabtii "Excel" waxaa laga dhex helay in t = 169,20903, iyo p = 2,89E-12, E. t. Hayso eber jaaniska ah in kuwa aaminka ah waa la diidi doonaa xiqiijiyay of insignificance muddada free. Waayo, Wehliyaha oo aan la garaneyn at t = 5,79405, iyo p = 0,001158. In si kale loo dhigo, jaaniska in a la xiqiijiyay diiday sax insignificance doonaa Wehliyaha ee aan la garanayn, waa 0,12%.

Sidaas darteed, waxaa lagu murmi karaa in ka helay isla'egta dhaca toosan ku filan.

Dhibaatada of advisability ee iibsato saamiyo

dhaca Multiple ayaa la sameeyaa iyadoo la isticmaalayo Excel "Saafidda Xogta" isla qalab. Tixgeli codsiga gaar ah.

Shirkadda Guide «NNN» waa inay go'aamiyaan in si aad u iibsato 20% saamiga of JSC «Bisha». qiimaha Package (SP) waa 70 milyan oo doolar. Khubaro of «NNN» aruurisay xog ku saabsan macaamil ganacsi la mid ah. Waxaa la go'aansaday in la qiimeeyo qiimaha saamiyada on-beegyada uu sida, muujiyeen in malaayiin doolarka Maraykanka, sida:

• payables (VK);
• mugga sannadkii (vo);
• receivables (VD);
• qiimaha hantida ma guurtada ah (SOF).

Intaa waxaa dheer, u isticmaal deynta ah mushaar ah shirkadaha (V3 U) ee kun oo doolarka Maraykanka.

The processor miiska go'aan hab Excel

First aad u baahan tahay si ay u abuuraan miis xogta aqbasho. Waa sida soo socota:

Next:

• sanduuqa call "data falanqaynta";
• qaybta soo xulay "dhaca";
• daaqadda "Input Y» maamulo kala duwan oo qiimaha ku tiirsan variable bareeg ka column G;
• riix icon la arrow cas si xaq ah u furmo suuqa "Input X bareeg» iyo go'doonsan kala duwan sheet dhammaan qiyamka column B, C, D, F. a

Mark barta "warqadda New" iyo guji "Waayahay".

Hel falanqaynta dhaca hawshan ah.

Natiijooyinka daraasadda iyo gunaanad

"Ururi" ka xogta kor ku soo bandhigay on miiska sheet isla'egta Excel processor dhaca ku soo koobay:

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

In foomka xisaabta ayaa in ka badan sida caadiga ah waxaa loo qori karaa sidan:

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

Data for «Bisha» JSC soo bandhigay shaxda hoos ku qoran:

SOF, USD	Vo, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535,5	45.2	41,5	21,55	64,72

iyaga Badalaa kusoo biirtay loolanka dhaca ah, helay tiradaasi ah 64,72 million oo doolarka Maraykanka ah. Taas macnaheedu waa in saamiyada ee JSC «Bisha» waa in aan soo iibsan, maxaa yeelay, kharashka waa arrin duldhigay at 70 million oo doolarka Maraykanka ah.

Sida aad arki karto, isticmaalka isbiriidhshiit "Excel" oo isla'eg u dhaca ee loo ogol yahay inuu sameeyo go'aan ku wargeliyay ku saabsan advisability ee macaamil arrin gaar ah.

Haatan waad garanaysaan waxa dhaca ah. Tusaale ahaan in Excel, kor looga hadlay, ayaa kaa caawin doona xalinta dhibaatooyinka la taaban karo ee econometrics.