Jinsi ya kufanya makadirio. Mstari wa mwelekeo katika Excel kwenye chati tofauti

Hitilafu ya wastani ya kukadiria- kupotoka wastani wa maadili yaliyohesabiwa kutoka kwa halisi:

Ambapo y x ni thamani iliyohesabiwa kutoka Eq.

Hitilafu ya wastani ya kukadiria ya hadi 15% inaonyesha muundo wa mlinganyo uliotoshea vizuri.

Kwa maeneo saba ya mkoa wa Ural kwa 199X, maadili ya sifa mbili yanajulikana.

Inahitajika:
1. Ili kubainisha utegemezi wa y kwa x, hesabu vigezo vya kazi zifuatazo:
a) mstari;
b) nguvu;
c) maonyesho;
d) hyperbola equilateral (unahitaji pia kujua jinsi ya kuweka mstari wa mfano huu).
2. Tathmini kila mfano kupitia hitilafu ya wastani ya kukadiria Jaribio la cf na F la Fisher.

Tunatatua tatizo kwa kutumia kikokotoo cha mtandaoni cha mlinganyo wa urejeshaji wa mstari.
a) usawa wa urejeshi wa mstari;
Kwa kutumia njia ya picha.
Njia hii hutumiwa kuibua kuonyesha fomu ya unganisho kati ya viashiria vya uchumi vilivyosomwa. Ili kufanya hivyo, grafu hutolewa katika mfumo wa kuratibu wa mstatili, maadili ya mtu binafsi ya tabia ya Y yanapangwa pamoja na mhimili wa kuratibu, na maadili ya mtu binafsi ya kipengele cha X yanapangwa kando ya mhimili wa abscissa.
Seti ya pointi za matokeo na sifa za kipengele huitwa uwanja wa uwiano.

Kulingana na uwanja wa uunganisho, tunaweza kudhania (kwa idadi ya watu) kwamba uhusiano kati ya maadili yote yanayowezekana ya X na Y ni ya mstari.
Mlinganyo wa urejeshi wa mstari ni y = bx + a + ε
Hapa ε ni kosa la nasibu (kupotoka, usumbufu).
Sababu za kuwepo kwa hitilafu ya nasibu:
1. Kushindwa kujumuisha vigezo muhimu vya maelezo katika muundo wa urejeshaji;
2. Mkusanyiko wa vigezo. Kwa mfano, kazi ya jumla ya matumizi ni jaribio la kueleza kwa ujumla jumla ya maamuzi ya matumizi ya mtu binafsi. Hii ni makadirio tu ya mahusiano ya mtu binafsi ambayo yana vigezo tofauti.
3. Maelezo yasiyo sahihi ya muundo wa mfano;
4. Ufafanuzi usio sahihi wa kazi;
5. Makosa ya kipimo.
Kwa kuwa kupotoka ε i kwa kila uchunguzi maalum ni nasibu na maadili yao kwenye sampuli haijulikani, basi:
1) kutoka kwa uchunguzi x i na y i makadirio ya vigezo α na β pekee yanaweza kupatikana.
2) Makadirio ya vigezo α na β vya modeli ya urejeshaji ni maadili a na b, mtawaliwa, ambayo ni ya nasibu kwa asili, kwa sababu. yanahusiana na sampuli ya nasibu;
Kisha equation ya kukadiria regression (iliyoundwa kutoka kwa data ya sampuli) itakuwa na fomu y = bx + a + ε, ambapo e i ni maadili yaliyozingatiwa (makadirio) ya makosa ε i , na a na b ni, mtawaliwa, makadirio ya vigezo α na β ya modeli regression ambayo inapaswa kupatikana.
Ili kukadiria vigezo α na β - njia ya angalau mraba (njia ya mraba ndogo) hutumiwa.

Tunapata b = -0.35, a = 76.88
Mlinganyo wa kurudi nyuma:
y = -0.35 x + 76.88

x	y	x 2	y 2	x y	Y(x)	(y i -y cp) 2	(y-y(x)) 2	\|y -y x \|:y
45,1	68,8	2034,01	4733,44	3102,88	61,28	119,12	56,61	0,1094
59	61,2	3481	3745,44	3610,8	56,47	10,98	22,4	0,0773
57,2	59,9	3271,84	3588,01	3426,28	57,09	4,06	7,9	0,0469
61,8	56,7	3819,24	3214,89	3504,06	55,5	1,41	1,44	0,0212
58,8	55	3457,44	3025	3234	56,54	8,33	2,36	0,0279
47,2	54,3	2227,84	2948,49	2562,96	60,55	12,86	39,05	0,1151
55,2	49,3	3047,04	2430,49	2721,36	57,78	73,71	71,94	0,172
384,3	405,2	21338,41	23685,76	22162,34	405,2	230,47	201,71	0,5699

Kumbuka: maadili ya y(x) yanapatikana kutoka kwa usawa wa rejista unaosababishwa:
y(45.1) = -0.35*45.1 + 76.88 = 61.28
y(59) = -0.35*59 + 76.88 = 56.47
... ... ...

Hitilafu ya kukadiria
Wacha tutathmini ubora wa mlinganyo wa rejista kwa kutumia hitilafu ya ukadiriaji kabisa. Hitilafu ya wastani ya kukadiria- kupotoka wastani wa maadili yaliyohesabiwa kutoka kwa halisi:

Kwa kuwa kosa ni chini ya 15%, mlinganyo huu unaweza kutumika kama rejista.

F-takwimu. Kigezo cha wavuvi.

3. Thamani iliyoonyeshwa imebainishwa kutoka kwa jedwali la usambazaji wa Fisher kwa kiwango fulani cha umuhimu, kwa kuzingatia kwamba idadi ya digrii za uhuru kwa jumla ya miraba (tofauti kubwa) ni 1 na idadi ya digrii za uhuru kwa mabaki. jumla ya miraba (tofauti ndogo) katika urejeshaji wa mstari ni n-2 .
4. Ikiwa thamani halisi ya mtihani wa F ni chini ya thamani ya meza, basi wanasema kwamba hakuna sababu ya kukataa hypothesis isiyofaa.
Vinginevyo, dhana potofu inakataliwa na dhana mbadala kuhusu umuhimu wa takwimu wa mlingano kwa ujumla inakubaliwa kwa uwezekano (1-α).

< Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).

b) kupungua kwa nguvu;
Suluhisho linafanywa kwa kutumia huduma ya Nonlinear Regression. Wakati wa kuchagua, taja Nguvu y = shoka b
c) kurudi nyuma kwa kasi;
d) mfano wa hyperbola equilateral.
Mfumo wa milinganyo ya kawaida.

Kwa data yetu, mfumo wa milinganyo una fomu
7a + 0.1291b = 405.2
0.1291a + 0.0024b = 7.51
Kutoka kwa equation ya kwanza tunaelezea a na kuibadilisha katika equation ya pili
Tunapata b = 1054.67, a = 38.44
Mlinganyo wa kurudi nyuma:
y = 1054.67 / x + 38.44
Hitilafu ya kukadiria.
Wacha tutathmini ubora wa mlinganyo wa rejista kwa kutumia hitilafu ya ukadiriaji kabisa.

Kwa kuwa kosa ni chini ya 15%, mlinganyo huu unaweza kutumika kama rejista.

Kigezo cha wavuvi.
Kujaribu umuhimu wa modeli ya urejeshi hufanywa kwa kutumia jaribio la Fisher's F, thamani iliyohesabiwa ambayo hupatikana kama uwiano wa tofauti ya mfululizo wa awali wa uchunguzi wa kiashirio kinachosomwa na makadirio yasiyo na upendeleo ya tofauti ya mlolongo wa mabaki. kwa mfano huu.
Ikiwa thamani iliyohesabiwa yenye digrii k1=(m) na k2=(n-m-1) ya uhuru ni kubwa kuliko thamani iliyoorodheshwa katika kiwango fulani cha umuhimu, basi kielelezo kinachukuliwa kuwa muhimu.

ambapo m ni idadi ya mambo katika mfano.
Umuhimu wa takwimu wa urejeshaji wa mstari uliooanishwa hutathminiwa kwa kutumia algoriti ifuatayo:
1. Dhana potofu inawekwa mbele kwamba mlinganyo kwa ujumla wake hauna maana kitakwimu: H 0: R 2 =0 katika kiwango cha umuhimu α.
2. Kisha, tambua thamani halisi ya kigezo cha F:

ambapo m=1 kwa urejeshaji wa jozi.
Thamani ya jedwali ya kigezo chenye viwango vya uhuru k1=1 na k2=5, Fkp = 6.61
Kwa kuwa thamani halisi ya F< Fkp, то коэффициент детерминации статистически не значим (Найденная оценка уравнения регрессии статистически не надежна).

Kumbuka kuwa uchanganuzi wa rejista ni aina ya uchanganuzi wa takwimu unaotumika kwa utabiri. Uchanganuzi wa urekebishaji hukuruhusu kukadiria nguvu ya uhusiano kati ya vigeu kwa kutoa utaratibu wa kuhesabu thamani inayotarajiwa ya kigezo kutoka kwa maadili kadhaa ambayo tayari yanajulikana.

Mistari ya mwenendo inaweza kukamilisha mfululizo wa data unaowasilishwa katika chati za eneo zisizo za kawaida, chati za pau, histogramu, grafu, chati za hisa, chati za kutawanya na chati za viputo. Matumizi ya mstari wa mwelekeo wa aina moja au nyingine hutambuliwa na aina ya data. Huwezi kuongeza mitindo kwenye mfululizo wa data katika chati za 3-D, chati za kawaida, chati za rada, chati za pai au chati za donati.

Curve iliyolainishwa inaonyesha muundo katika uundaji wa data kwa uwazi zaidi. Imejengwa juu ya pointi za wastani wa kusonga, ambapo wastani wa kusonga unamaanisha mlolongo wa nambari za wastani, ambayo kila mmoja huhesabiwa kutoka kwa kikundi fulani cha mfululizo wa data.

Kuongeza mwelekeo au kusonga wastani kwa mfululizo wa data

Excel hutumia aina sita tofauti za mistari ya mwelekeo (kufaa na kulainisha) inayoweza kuongezwa kwenye chati (Mchoro 18.11):

Ukadiriaji wa mstari(Linear) ni mstari ulionyooka unaofafanua vyema seti ya data. Mlinganyo wa mstari ulionyooka ni y=ax+b, ambapo a ni tanjiti ya pembe ya mwelekeo, b ni hatua ya makutano ya mstari wa moja kwa moja na mhimili y. Ukadiriaji wa mstari hutumiwa kwa vigezo vinavyoongezeka au kupungua kwa kasi isiyobadilika.
Ukadiriaji wa logarithmic(Logarithmic) inaelezea vizuri idadi chanya na hasi, ambayo mwanzoni huongezeka au kupungua kwa kasi, na kisha utulivu hatua kwa hatua. Ukadiriaji wa logarithmic hutumia mlingano y=c*lnx+b, ambapo c na b ni viunga, In ni logariti asilia.
Ukadiriaji wa polynomial(Polynomial) hutumiwa kuelezea idadi ambayo huongezeka na kupungua. Inashauriwa kuitumia kwa kuchambua seti kubwa ya data ya ukubwa usio na utulivu. Kiwango cha polynomial kinatambuliwa na idadi ya extrema (maxima na minima) ya curve. Digrii ya pili ya polynomial inaweza kuelezea kiwango cha juu au cha chini kimoja pekee. Polynomial ya shahada ya tatu ina extrema moja au mbili. Polynomial ya daraja la nne haiwezi kuwa na extrema isiyozidi tatu. Ukadiriaji wa polinomia unafafanuliwa na mlinganyo y=a+ciXi+C2X2++Cigx18, ambapo a, Cj-Cjg ni viunga. Kiwango kinachohitajika cha polynomial kinatajwa katika uwanja wa Shahada (Mchoro). Thamani ya juu ya digrii ni 18.
Ukadiriaji wa nguvu(Nguvu) hutoa matokeo mazuri ikiwa utegemezi ulio katika data unaonyeshwa na kasi ya ukuaji wa mara kwa mara. Mfano wa uhusiano kama huo ni grafu ya kuongeza kasi ya gari. Ikiwa data ina thamani sifuri au hasi, ukadiriaji wa sheria-nguvu hauwezi kutumika. Ukadiriaji wa sheria-nguvu unafafanuliwa na mlinganyo y=a * xn, ambapo a na n ni viambatisho.
Ukadiriaji wa kielelezo(Exponential) inapaswa kutumika ikiwa kiwango cha mabadiliko ya data kinaendelea kuongezeka. Hata hivyo, kwa data iliyo na thamani sifuri au hasi, aina hii ya ukadiriaji haitumiki. Ukadiriaji wa kielelezo unaelezewa na mlinganyo y = a ebx, ambapo a na b ni viunga.
Uchujaji wa mstari(Wastani wa kusonga) hukuruhusu kurekebisha mabadiliko ya data na kwa hivyo kuonyesha wazi zaidi asili ya utegemezi. Mstari wa mwelekeo kama huu hutengenezwa kutoka kwa idadi fulani ya pointi (hubainishwa na kigezo cha Kipindi). Vipengee vya data vinakadiriwa, na matokeo yake hutumiwa kama thamani ya wastani ya kukadiria. Kwa hivyo, ikiwa kigezo cha Tonki ni sawa. hadi 2, hatua ya kwanza ya curve ya kulainisha inafafanuliwa kama wastani wa vipengele viwili vya kwanza vya data, hatua ya pili - kama wastani wa vipengele viwili vinavyofuata, nk Ili kuhesabu wastani wa kusonga, equation y = (Aj+ Aj_i++Aj_n+i)/n inatumika.

Kuongeza mwelekeo wa mfululizo wa data

Ili kuongeza mwelekeo kwenye mfululizo wa data, fuata hatua hizi:

chagua mfululizo wa data ambao ungependa kuongeza mstari wa mwenendo au wastani wa kusonga;
chagua timu Ongeza mstari wa mwenendo(Ongeza Mstari wa Mwenendo) kwenye menyu Mchoro(Chati). Kwenye kichupo Aina(Aina) chagua aina inayotakiwa ya mstari wa mwenendo wa regression au mstari wa wastani wa kusonga (Mchoro 18.11);
wakati wa kuchagua aina Polynomial(Polynomial) ingia kwenye uwanja Shahada(Agizo) shahada ya juu zaidi kwa kutofautiana huru;
wakati wa kuchagua aina Kusonga wastani(Wastani wa Kusonga) ingiza kwenye uwanja Pointi(Kipindi) idadi ya pointi zinazotumiwa kukokotoa wastani wa kusonga.

Mchele. 18.11. Kuchagua mstari wa mwenendo

UTEGEMEZI

Excel ina zana zinazokuwezesha kutabiri michakato. Shida ya ukaribu hutokea wakati inahitajika kuelezea kwa uchanganuzi matukio ambayo hufanyika katika maisha na hutolewa kwa namna ya meza zilizo na maadili ya hoja (hoja) na kazi. Ikiwa utegemezi unaweza kupatikana, inawezekana kufanya utabiri kuhusu tabia ya mfumo chini ya utafiti katika siku zijazo na, ikiwezekana, kuchagua mwelekeo bora kwa maendeleo yake. Kazi hiyo ya uchanganuzi (pia inaitwa mwelekeo) inaweza kuwa na aina tofauti na viwango tofauti vya utata kulingana na utata wa mfumo na usahihi unaohitajika wa uwakilishi.

10.1. Urejeshaji wa mstari

Rahisi na maarufu zaidi ni ukadiriaji wa mstari wa moja kwa moja - urejeshaji wa mstari.

Hebu tuwe na taarifa halisi kuhusu viwango vya faida Y kulingana na ukubwa wa uwekezaji X - Y(X). Katika Mtini. Kielelezo 10.1-1 kinaonyesha pointi nne kama hizo M(Y,X). Hebu pia tuwe na sababu ya kudhani kuwa utegemezi huu ni wa mstari, i.e. inaonekana kama Y=A+BX. Iwapo tungeweza kupata viambajengo A na B na kuvitumia kuunda mstari ulionyooka (kwa mfano, kama ule ulio kwenye mchoro), katika siku zijazo tungeweza kutoa mawazo kuhusu mienendo ya biashara na uwezekano wa hali ya kibiashara. ya biashara katika siku zijazo. Kwa wazi, tungeridhika na mstari wa moja kwa moja ulio karibu iwezekanavyo kwa pointi zinazojulikana M (Y, X), i.e. kuwa na jumla ya kiwango cha chini cha kupotoka au jumla ya makosa (katika takwimu, kupotoka huonyeshwa kwa mistari yenye alama). Inajulikana kuwa kuna mstari mmoja tu kama huo.

Ili kutatua tatizo hili, njia ndogo ya makosa ya mraba hutumiwa. Tofauti (kosa) kati ya thamani inayojulikana Y1 ya uhakika M1(Y1,X1) na thamani Y(X1) inayokokotolewa kwa kutumia mlingano wa mstari ulionyooka kwa thamani sawa X1 itakuwa.

D1 = Y1 – A – B X1.

Tofauti sawa

kwa X=X2 itakuwa D2 = Y2 - A - B X2;

kwa X=X3 D3 = Y3 – A – B X3;

na kwa X=X4 D4 = Y4 – A – B X4.

Wacha tuandike usemi kwa jumla ya miraba ya makosa haya

Ф(A,В)=(Y1–A–B X1) 2 +(Y2–A–B X2) 2 +(Y3–A–B X3) 2 +(Y4–A–B X4) 2

au kifupi Ф(B,A) = å(Yi – A – BXi) 2.

Hapa tunajua X na Y zote na coefficients zisizojulikana A na B. Hebu tuchore mstari wa moja kwa moja unaohitajika kwa namna (yaani, chagua A na B vile) kwamba jumla hii ya makosa ya mraba Ф (A, B) ni ndogo. Masharti ya uchache ni mahusiano yanayojulikana

¶Ф(A,B)/¶A=0 na ¶Ф(A,B)/¶B=0.

Wacha tupate misemo hii (tunaacha maandishi kwenye ishara ya jumla):

¶[å(Yi–A–B Xi) 2 ]/¶A = å(Yi–A–B Xi)(–1)

¶[å(Yi–A–B Xi) 2 ]/¶B = å(Yi–A–B Xi)(–Xi).

Wacha tubadilishe fomula zinazosababishwa na kuzilinganisha na sifuri

Taarifa za kinadharia

Kwa mazoezi, wakati wa kuiga michakato mbalimbali - haswa, kiuchumi, kimwili, kiufundi, kijamii - njia moja au nyingine ya kuhesabu takriban maadili ya kazi kutoka kwa maadili yao yanayojulikana katika pointi fulani maalum hutumiwa sana.

Aina hii ya shida ya kukadiria utendakazi mara nyingi hutokea:

wakati wa kuunda fomula takriban za kuhesabu maadili ya idadi ya tabia ya mchakato unaosomwa kwa kutumia data ya jedwali iliyopatikana kama matokeo ya jaribio;
katika ujumuishaji wa nambari, utofautishaji, utatuzi wa usawa tofauti, nk;
ikiwa ni lazima, hesabu maadili ya kazi katika sehemu za kati za muda unaozingatiwa;
wakati wa kuamua maadili ya idadi ya tabia ya mchakato nje ya muda unaozingatiwa, haswa wakati wa utabiri.

Ikiwa, kwa mfano wa mchakato fulani ulioainishwa na jedwali, tunaunda kazi ambayo takriban inaelezea mchakato huu kulingana na njia ndogo ya mraba, itaitwa kazi ya kukadiria (regression), na shida ya kuunda kazi zinazokaribia yenyewe itaitwa. tatizo la makadirio.

Nakala hii inajadili uwezo wa kifurushi cha MS Excel kwa kutatua aina hii ya shida, kwa kuongeza, inatoa njia na mbinu za kuunda (kuunda) regressions kwa kazi zilizowekwa (ambayo ndio msingi wa uchambuzi wa urekebishaji).

Excel ina chaguzi mbili za kujenga regressions.

Kuongeza rejeshi zilizochaguliwa (mielekeo) kwenye mchoro uliojengwa kwa misingi ya jedwali la data kwa sifa ya mchakato unaochunguzwa (inapatikana tu ikiwa mchoro umeundwa);
Kwa kutumia vitendaji vya takwimu vilivyojumuishwa vya lahakazi ya Excel, hukuruhusu kupata rejeshi (mistari ya mwelekeo) moja kwa moja kutoka kwa jedwali la data ya chanzo.

Kuongeza mienendo kwenye chati

Kwa jedwali la data linaloelezea mchakato na kuwakilishwa na mchoro, Excel ina zana bora ya uchanganuzi wa rejista ambayo hukuruhusu:

jenga kwa msingi wa njia ndogo ya mraba na uongeze aina tano za kurudi nyuma kwenye mchoro, ambayo ni mfano wa mchakato unaosomwa na viwango tofauti vya usahihi;
ongeza equation ya regression iliyojengwa kwenye mchoro;
bainisha kiwango cha mawasiliano cha urejeshaji uliochaguliwa kwa data iliyoonyeshwa kwenye chati.

Kulingana na data ya chati, Excel hukuruhusu kupata urejeshaji wa mstari, polynomial, logarithmic, nguvu, aina za kielelezo za rejeshi, ambazo zimebainishwa na mlinganyo:

y = y(x)

ambapo x ni kigezo cha kujitegemea ambacho mara nyingi huchukua maadili ya mlolongo wa nambari asilia (1; 2; 3; ...) na hutoa, kwa mfano, hesabu ya muda wa mchakato unaojifunza (tabia).

1 . Urejeshaji wa mstari ni mzuri kwa sifa za uundaji ambazo maadili yake huongezeka au kupungua kwa kasi isiyobadilika. Huu ndio muundo rahisi zaidi wa kuunda kwa mchakato unaojifunza. Yeye

y = mx + b

ambapo m ni tanjiti ya mteremko wa regression wa mstari kwa mhimili wa x; b - kuratibu hatua ya makutano ya regression ya mstari na mhimili wa kuratibu.

2 . Mstari wa mwelekeo wa polynomial ni muhimu kwa kuelezea sifa ambazo zina viwango tofauti tofauti (maxima na minima). Uchaguzi wa shahada ya polynomial imedhamiriwa na idadi ya extrema ya tabia inayosomwa. Kwa hivyo, polynomial ya shahada ya pili inaweza kuelezea vizuri mchakato ambao una kiwango cha juu au cha chini; polynomial ya shahada ya tatu - si zaidi ya mbili extrema; polynomial ya shahada ya nne - si zaidi ya tatu extrema, nk.

Katika kesi hii, mstari wa mwenendo unaundwa kwa mujibu wa equation:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

ambapo coefficients c0, c1, c2,... c6 ni vidhibiti ambavyo maadili yake huamuliwa wakati wa ujenzi.

3 . Mstari wa mwelekeo wa logarithmic hutumiwa kwa mafanikio wakati wa kuiga sifa ambazo maadili yake mwanzoni hubadilika haraka na kisha kutulia polepole.

Imeundwa kwa mujibu wa equation:

y = c ln(x) + b

4 . Mstari wa mwelekeo wa sheria-nguvu hutoa matokeo mazuri ikiwa maadili ya uhusiano unaochunguzwa yanaonyeshwa na mabadiliko ya mara kwa mara katika kiwango cha ukuaji. Mfano wa utegemezi kama huo ni grafu ya mwendo wa kasi wa gari. Ikiwa kuna thamani sifuri au hasi katika data, huwezi kutumia mstari wa mwelekeo wa nguvu.

Imeundwa kwa mujibu wa equation:

y = c xb

ambapo mgawo b, c ni viunga.

5 . Mstari wa mwelekeo wa kielelezo unapaswa kutumika wakati kasi ya mabadiliko katika data inaendelea kuongezeka. Kwa data iliyo na thamani sifuri au hasi, aina hii ya ukadiriaji pia haitumiki.

Imeundwa kwa mujibu wa equation:

y = c ebx

ambapo mgawo b, c ni viunga.

Wakati wa kuchagua mstari wa mwelekeo, Excel huhesabu kiotomati thamani ya R2, ambayo inaashiria kuegemea kwa makadirio: kadiri thamani ya R2 inavyokaribiana na umoja, ndivyo mstari wa mwelekeo unavyokaribia mchakato unaosomwa kwa uhakika zaidi. Ikiwa ni lazima, thamani ya R2 inaweza kuonyeshwa kila wakati kwenye chati.

Imedhamiriwa na formula:

Ili kuongeza mwelekeo kwenye mfululizo wa data:

washa chati kulingana na mfululizo wa data, yaani, bofya ndani ya eneo la chati. Kipengee cha Mchoro kitaonekana kwenye orodha kuu;
baada ya kubofya kipengee hiki, menyu itaonekana kwenye skrini ambayo unapaswa kuchagua amri ya mstari wa Kuongeza.

Vitendo sawa vinaweza kutekelezwa kwa urahisi kwa kusonga pointer ya panya juu ya grafu inayolingana na moja ya mfululizo wa data na kubofya kulia; Katika menyu ya muktadha inayoonekana, chagua amri ya mstari wa Kuongeza. Sanduku la mazungumzo la Mwenendo litaonekana kwenye skrini na kichupo cha Aina kufunguliwa (Mchoro 1).

Baada ya hii unahitaji:

Chagua aina ya mstari wa mwelekeo unaohitajika kwenye kichupo cha Aina (aina ya Mstari huchaguliwa kwa chaguomsingi). Kwa aina ya Polynomial, katika uwanja wa Shahada, taja kiwango cha polynomial iliyochaguliwa.

1 . Sehemu ya Built on series huorodhesha mfululizo wote wa data kwenye chati inayohusika. Ili kuongeza mwelekeo kwenye mfululizo mahususi wa data, chagua jina lake katika sehemu ya Imejengwa kwenye mfululizo.

Ikiwa ni lazima, kwa kwenda kwenye kichupo cha Vigezo (Mchoro 2), unaweza kuweka vigezo vifuatavyo kwa mstari wa mwenendo:

badilisha jina la mstari wa mwelekeo katika Jina la uga wa curve inayokaribia (iliyolainishwa).
weka idadi ya vipindi (mbele au nyuma) kwa utabiri katika uwanja wa Utabiri;
onyesha equation ya mstari wa mwenendo katika eneo la mchoro, ambayo unapaswa kuwezesha usawa wa maonyesho kwenye kisanduku cha kuteua cha mchoro;
onyesha thamani ya kukadiria ya kutegemewa R2 katika eneo la mchoro, ambayo unapaswa kuwezesha Weka thamani ya kutegemewa ya kukadiria kwenye kisanduku cha kuteua cha mchoro (R^2);
weka sehemu ya makutano ya mstari wa mwelekeo na mhimili wa Y, ambayo unapaswa kuwezesha kisanduku cha kuteua kwa makutano ya curve na mhimili wa Y kwa uhakika;
Bofya kitufe cha OK ili kufunga sanduku la mazungumzo.

Ili kuanza kuhariri mtindo uliochorwa tayari, kuna njia tatu:

tumia amri iliyochaguliwa ya mstari wa mwelekeo kutoka kwa menyu ya Umbizo, ukiwa umechagua mstari wa mwenendo hapo awali;

chagua amri ya mstari wa mwelekeo wa Umbizo kutoka kwa menyu ya muktadha, ambayo inaitwa kwa kubofya kulia kwenye mstari wa mwenendo;

bonyeza mara mbili kwenye mstari wa mwenendo.

Kisanduku cha mazungumzo cha Umbizo la Mstari Mwelekeo kitaonekana kwenye skrini (Kielelezo 3), kilicho na vichupo vitatu: Tazama, Aina, Vigezo, na yaliyomo katika mbili za mwisho sanjari kabisa na vichupo sawa vya kisanduku cha mazungumzo cha Mstari Mwenendo (Kielelezo 1). -2). Kwenye kichupo cha Tazama, unaweza kuweka aina ya mstari, rangi yake na unene.

Ili kufuta mwelekeo ambao tayari umechorwa, chagua mwelekeo utakaofutwa na ubonyeze kitufe cha Futa.

Faida za zana inayozingatiwa ya uchambuzi wa urejeshi ni:

urahisi wa jamaa wa kujenga mstari wa mwenendo kwenye chati bila kuunda meza ya data kwa ajili yake;
orodha pana ya aina za mistari ya mwelekeo iliyopendekezwa, na orodha hii inajumuisha aina zinazotumiwa sana za urejeshaji;
uwezo wa kutabiri tabia ya mchakato chini ya utafiti na kiholela (ndani ya mipaka ya akili ya kawaida) idadi ya hatua mbele na pia nyuma;
uwezo wa kupata equation ya mstari wa mwenendo katika fomu ya uchambuzi;
uwezekano, ikiwa ni lazima, wa kupata tathmini ya kuaminika kwa makadirio.

Hasara ni pamoja na zifuatazo:

ujenzi wa mstari wa mwenendo unafanywa tu ikiwa kuna mchoro uliojengwa kwenye mfululizo wa data;

mchakato wa kutoa mfululizo wa data kwa sifa inayochunguzwa kulingana na milinganyo ya mwenendo iliyopatikana kwa ajili yake imechanganyikiwa kwa kiasi fulani: milinganyo inayohitajika ya urekebishaji inasasishwa na kila mabadiliko katika maadili ya mfululizo wa data asilia, lakini ndani ya eneo la chati tu. , wakati mfululizo wa data ulioundwa kwa misingi ya mwelekeo wa mlinganyo wa mstari wa zamani haujabadilika;

Katika ripoti za PivotChart, kubadilisha mwonekano wa chati au ripoti husika ya PivotTable haihifadhi mitindo iliyopo, kumaanisha kuwa kabla ya kuchora mienendo au vinginevyo kuunda ripoti ya PivotChart, unapaswa kuhakikisha kuwa mpangilio wa ripoti unakidhi mahitaji yanayohitajika.

Mistari ya mwelekeo inaweza kutumika kuongezea mfululizo wa data unaowasilishwa kwenye chati kama vile grafu, histogram, chati bapa za eneo zisizo na sanifu, chati za pau, chati za kutawanya, chati za viputo na chati za hisa.

Huwezi kuongeza mitindo kwenye mfululizo wa data katika 3D, chati za kawaida, rada, pai na donuts.

Kutumia vitendaji vilivyojumuishwa vya Excel

Excel pia ina zana ya kuchanganua urejeshi kwa kupanga mistari ya mienendo nje ya eneo la chati. Kuna idadi ya vitendakazi vya karatasi za takwimu unazoweza kutumia kwa madhumuni haya, lakini zote hukuruhusu tu kuunda urejeleaji wa mstari au wa kielelezo.

Excel ina kazi kadhaa za kuunda urejeshaji wa mstari, haswa:

MWENENDO;
LINEST;
Mteremko na KATA.

Pamoja na kazi kadhaa za kuunda mstari wa mwelekeo wa kielelezo, haswa:

UREFU;
LGRFPRIBL.

Ikumbukwe kwamba mbinu za kujenga regressions kwa kutumia kazi za TREND na GROWTH ni karibu sawa. Vile vile vinaweza kusemwa kuhusu jozi ya kazi LINEST na LGRFPRIBL. Kwa kazi hizi nne, kuunda jedwali la maadili hutumia vipengee vya Excel kama vile fomula za safu, ambayo kwa kiasi fulani huchanganya mchakato wa kujenga rejista. Kumbuka pia kuwa ujenzi wa urejeshaji wa mstari, kwa maoni yetu, unakamilishwa kwa urahisi zaidi kwa kutumia kazi za SLOPE na INTERCEPT, ambapo ya kwanza huamua mteremko wa urejeshaji wa mstari, na ya pili huamua sehemu iliyoingiliwa na rejista kwenye y. -mhimili.

Faida za zana ya utendakazi iliyojengewa ndani ya uchanganuzi wa urekebishaji ni:

mchakato rahisi, sawia wa kuzalisha mfululizo wa data wa sifa inayochunguzwa kwa vipengele vyote vya takwimu vilivyojengewa ndani ambavyo vinafafanua mienendo;
mbinu ya kawaida ya kuunda mistari ya mwenendo kulingana na mfululizo wa data uliozalishwa;
uwezo wa kutabiri tabia ya mchakato chini ya utafiti kwa idadi inayotakiwa ya hatua mbele au nyuma.

Ubaya ni pamoja na ukweli kwamba Excel haina vitendaji vilivyojumuishwa vya kuunda aina zingine (isipokuwa za mstari na za kielelezo) za mistari ya mitindo. Hali hii mara nyingi hairuhusu kuchagua mfano sahihi wa kutosha wa mchakato unaojifunza, pamoja na kupata utabiri ambao uko karibu na ukweli. Kwa kuongeza, unapotumia kazi za TREND na GROWTH, usawa wa mistari ya mwenendo haujulikani.

Ikumbukwe kwamba waandishi hawakujitolea kuwasilisha kozi ya uchanganuzi wa urejeshi kwa kiwango chochote cha ukamilifu. Kazi yake kuu ni kuonyesha, kwa kutumia mifano maalum, uwezo wa mfuko wa Excel wakati wa kutatua matatizo ya makadirio; onyesha ni zana gani madhubuti za Excel inazo za kujenga rejeshi na utabiri; onyesha jinsi matatizo kama hayo yanaweza kutatuliwa kwa urahisi hata na mtumiaji ambaye hana ujuzi wa kina wa uchanganuzi wa urejeshi.

Mifano ya kutatua matatizo maalum

Hebu tuangalie kutatua matatizo maalum kwa kutumia zana zilizoorodheshwa za Excel.

Tatizo 1

Na jedwali la data juu ya faida ya biashara ya usafiri wa magari kwa 1995-2002. unahitaji kufanya yafuatayo:

Jenga mchoro.
Ongeza mistari ya mwelekeo ya mstari na ya polynomial (quadratic na cubic) kwenye chati.
Kwa kutumia milinganyo ya mwelekeo, pata data ya jedwali kuhusu faida za biashara kwa kila mtindo wa 1995-2004.
Fanya utabiri wa faida ya biashara kwa 2003 na 2004.

Suluhisho la tatizo

Katika safu ya seli A4:C11 za lahakazi ya Excel, ingiza laha ya kazi iliyoonyeshwa kwenye Mtini. 4.
Baada ya kuchagua anuwai ya seli B4: C11, tunaunda mchoro.
Tunawasha mchoro uliojengwa na, kwa mujibu wa njia iliyoelezwa hapo juu, baada ya kuchagua aina ya mstari wa mwenendo katika sanduku la mazungumzo la Mstari wa Mwelekeo (tazama Mchoro 1), tunaongeza mistari ya mwenendo wa mstari, wa quadratic na cubic kwenye mchoro. Katika kisanduku cha mazungumzo sawa, fungua kichupo cha Vigezo (ona Mtini. 2), katika Jina la uga wa curve inayokaribia (iliyolaini), weka jina la mwelekeo unaoongezwa, na katika Utabiri wa mbele kwa: uga wa vipindi, weka thamani 2, kwani imepangwa kufanya utabiri wa faida kwa miaka miwili mbele. Ili kuonyesha mlingano wa urejeshi na utegemezi wa ukadiriaji wa thamani R2 katika eneo la mchoro, washa mlingano wa onyesho kwenye visanduku vya kuteua vya skrini na uweke thamani ya ukadiriaji wa kuaminika (R^2) kwenye mchoro. Kwa mtazamo bora wa kuona, tunabadilisha aina, rangi na unene wa mistari ya mwenendo iliyojengwa, ambayo tunatumia kichupo cha Tazama cha sanduku la mazungumzo la Format Line Trend (ona Mchoro 3). Mchoro unaotokana na mistari iliyoongezwa ya mwenendo unaonyeshwa kwenye Mtini. 5.
Ili kupata data ya jedwali juu ya faida za biashara kwa kila mstari wa mwenendo wa 1995-2004. Wacha tutumie milinganyo ya mwelekeo inayowasilishwa kwenye Mtini. 5. Ili kufanya hivyo, katika visanduku vya safu D3:F3, ingiza maelezo ya maandishi kuhusu aina ya mtindo uliochaguliwa: Mwelekeo wa mstari, mwelekeo wa Quadratic, Mwelekeo wa ujazo. Ifuatayo, weka fomula ya urejeshi wa mstari katika kisanduku cha D4 na, kwa kutumia kiashiria cha kujaza, nakili fomula hii kwa marejeleo yanayohusiana na safu ya kisanduku D5:D13. Ikumbukwe kwamba kila seli iliyo na fomula ya urejeshaji mstari kutoka safu ya visanduku D4:D13 ina kama kihoja kisanduku kinacholingana kutoka masafa A4:A13. Vile vile, kwa urejeshaji wa quadratic, jaza safu ya seli E4:E13, na kwa urejeshaji wa ujazo, jaza safu ya seli F4:F13. Kwa hivyo, utabiri wa faida ya biashara kwa 2003 na 2004 umeundwa. kwa kutumia mitindo mitatu. Jedwali la matokeo la maadili linaonyeshwa kwenye Mtini. 6.

Tatizo 2

Jenga mchoro.
Ongeza mistari ya logarithmic, nguvu na kielelezo kwenye chati.
Pata hesabu za mistari ya mwenendo iliyopatikana, na pia maadili ya kuegemea ya takriban R2 kwa kila moja yao.
Kwa kutumia milinganyo ya mwelekeo, pata data ya jedwali kuhusu faida ya biashara kwa kila mtindo wa 1995-2002.
Toa utabiri wa faida ya kampuni kwa 2003 na 2004 ukitumia njia hizi za mitindo.

Suluhisho la tatizo

Kufuatia mbinu iliyotolewa katika kutatua tatizo la 1, tunapata mchoro wenye mistari ya logarithmic, nguvu na kielelezo kilichoongezwa kwake (Mchoro 7). Ifuatayo, kwa kutumia milinganyo ya mwenendo iliyopatikana, tunajaza jedwali la maadili kwa faida ya biashara, pamoja na maadili yaliyotabiriwa ya 2003 na 2004. (Mchoro 8).

Katika Mtini. 5 na mtini. inaweza kuonekana kuwa modeli iliyo na mwelekeo wa logarithmic inalingana na dhamana ya chini kabisa ya utegemezi wa kukadiria.

R2 = 0.8659

Maadili ya juu zaidi ya R2 yanahusiana na mifano yenye mwelekeo wa polynomial: quadratic (R2 = 0.9263) na ujazo (R2 = 0.933).

Tatizo 3

Na jedwali la data juu ya faida ya biashara ya usafiri wa magari kwa 1995-2002, iliyotolewa katika kazi 1, lazima ufanyie hatua zifuatazo.

Pata mfululizo wa data kwa mistari ya mienendo ya mstari na ya kielelezo kwa kutumia vipengele vya TREND na GROW.
Kwa kutumia vipengele vya TREND na GROWTH, fanya utabiri wa faida ya biashara kwa 2003 na 2004.
Tengeneza mchoro wa data asili na mfululizo wa data unaotokana.

Suluhisho la tatizo

Hebu tumia laha ya kazi kwa Tatizo la 1 (tazama Mchoro 4). Wacha tuanze na kitendakazi cha TREND:

chagua safu ya seli D4: D11, ambazo zinapaswa kujazwa na maadili ya kazi ya TREND inayolingana na data inayojulikana juu ya faida ya biashara;
Piga amri ya Kazi kutoka kwa menyu ya Ingiza. Katika sanduku la mazungumzo la Mchawi wa Kazi inayoonekana, chagua kazi ya TREND kutoka kwa kitengo cha Takwimu, na kisha bofya OK kifungo. Operesheni sawa inaweza kufanywa kwa kubofya kitufe cha (Ingiza Kazi) kwenye upau wa zana wa kawaida.
Katika kisanduku cha mazungumzo cha Hoja za Kazi inayoonekana, ingiza safu ya seli C4:C11 katika sehemu ya Known_values_y; katika Inayojulikana_values_x uga - safu ya seli B4:B11;
Ili kufanya fomula iliyoingizwa kuwa fomula ya safu, tumia mchanganyiko muhimu + + .

Fomula tuliyoingiza kwenye upau wa fomula itaonekana kama: =(TREND(C4:C11,B4:B11)).

Kama matokeo, anuwai ya seli D4: D11 imejazwa na maadili yanayolingana ya kazi ya TREND (Mchoro 9).

Kufanya utabiri wa faida ya biashara kwa 2003 na 2004. muhimu:

chagua safu ya visanduku D12:D13 ambapo thamani zilizotabiriwa na chaguo za kukokotoa za TREND zitawekwa.
piga kitendakazi cha TREND na katika kisanduku cha mazungumzo cha Hoja za Kazi kinachoonekana, ingiza katika Uga Unaojulikana_values_y - safu ya seli C4:C11; katika Inayojulikana_values_x uga - safu ya seli B4:B11; na katika sehemu ya New_values_x - safu ya visanduku B12:B13.
geuza fomula hii kuwa fomula ya safu kwa kutumia mchanganyiko muhimu Ctrl + Shift + Enter.
Fomula iliyoingizwa itaonekana kama: =(TREND(C4:C11;B4:B11;B12:B13)), na safu ya seli D12:D13 itajazwa na thamani zilizotabiriwa za chaguo la kukokotoa la TREND (ona Mtini. 9).

Msururu wa data vile vile hujazwa kwa kutumia kipengele cha kukokotoa cha GROWTH, ambacho hutumika katika uchanganuzi wa vitegemezi visivyo vya mstari na hufanya kazi kwa njia sawa kabisa na kilinganishi cha TREND.

Kielelezo cha 10 kinaonyesha jedwali katika hali ya onyesho la fomula.

Kwa data ya awali na mfululizo wa data uliopatikana, mchoro umeonyeshwa kwenye Mtini. kumi na moja.

Tatizo 4

Na jedwali la data juu ya upokeaji wa maombi ya huduma kwa huduma ya kupeleka ya biashara ya usafirishaji wa gari kwa kipindi cha 1 hadi 11 ya mwezi wa sasa, lazima ufanye vitendo vifuatavyo.

Pata mfululizo wa data kwa urejeshaji wa mstari:kutumia kazi za SLOPE na CUT; kwa kutumia kitendakazi cha LINEST.
Pata mfululizo wa data kwa urejeshaji wa kielelezo kwa kutumia kitendakazi cha LGRFPRIBL.
Kwa kutumia vipengele vilivyo hapo juu, fanya utabiri kuhusu upokeaji wa maombi kwa huduma ya kutuma kwa kipindi cha kuanzia tarehe 12 hadi 14 ya mwezi wa sasa.
Unda mchoro wa mfululizo wa data asilia na uliopokelewa.

Suluhisho la tatizo

Kumbuka kuwa, tofauti na chaguo za kukokotoa za TREND na GROWTH, hakuna chaguo mojawapo kati ya vitendakazi vilivyoorodheshwa hapo juu (Mteremko, INTERCEPT, LINEST, LGRFPRIB) ni rejista. Kazi hizi zina jukumu la kusaidia tu, kuamua vigezo muhimu vya urejeshaji.

Kwa urejeshaji wa mstari na kielelezo uliojengwa kwa kutumia chaguo za kukokotoa SLOPE, INTERCEPT, LINEST, LGRFPRIB, mwonekano wa milinganyo yao inajulikana kila wakati, tofauti na urejeshaji wa mstari na kielelezo sambamba na utendaji wa TREND na GROWTH.

1 . Wacha tujenge urejeshaji wa mstari na equation:

y = mx+b

kwa kutumia kazi za SLOPE na INTERCEPT, na mteremko wa regression m iliyoamuliwa na kazi ya SLOPE, na neno la bure b kwa kazi ya INTERCEPT.

Ili kufanya hivyo, tunafanya vitendo vifuatavyo:

ingiza jedwali la asili kwenye safu ya seli A4:B14;
thamani ya parameter m itajulikana katika kiini C19. Chagua kazi ya Mteremko kutoka kwa kitengo cha Takwimu; weka safu ya visanduku B4:B14 katika sehemu_ya_maadili_y inayojulikana na safu ya visanduku A4:A14 katika sehemu ya_maadili_x inayojulikana. Fomula itaingizwa katika seli C19: =Mteremko(B4:B14,A4:A14);
Kutumia mbinu sawa, thamani ya parameter b katika kiini D19 imedhamiriwa. Na yaliyomo yake yataonekana kama: =SEGMENT(B4:B14,A4:A14).Kwa hivyo, maadili ya vigezo m na b zinazohitajika kwa ajili ya kujenga regression ya mstari zitahifadhiwa katika seli C19, D19, kwa mtiririko huo;
Ifuatayo, weka fomula ya urejeshaji mstari katika kisanduku C4 katika umbo: =$C*A4+$D. Katika fomula hii, seli C19 na D19 zimeandikwa kwa marejeleo kamili (anwani ya seli haipaswi kubadilika wakati wa kunakili iwezekanavyo). Alama kamili ya marejeleo $ inaweza kuandikwa kutoka kwa kibodi au kwa kutumia kitufe cha F4, baada ya kuweka kishale kwenye anwani ya seli. Kwa kutumia kishikio cha kujaza, nakili fomula hii katika anuwai ya seli C4:C17. Tunapata mfululizo wa data unaohitajika (Mchoro 12). Kwa sababu ya ukweli kwamba idadi ya maombi ni nambari kamili, unapaswa kuweka umbizo la nambari na idadi ya maeneo ya decimal hadi 0 kwenye kichupo cha Nambari cha dirisha la Umbizo la Kiini.

2 . Sasa wacha tujenge urejeshaji wa mstari uliotolewa na equation:

y = mx+b

kwa kutumia kitendakazi cha LINEST.

Kwa hii; kwa hili:

Ingiza kitendakazi cha LINEST kama fomula ya safu katika safu ya seli C20:D20: =(LINEST(B4:B14,A4:A14)). Matokeo yake, tunapata thamani ya parameter m katika kiini C20, na thamani ya parameter b katika kiini D20;
ingiza formula katika kiini D4: =$C*A4+$D;
nakili fomula hii kwa kutumia alama ya kujaza kwenye safu ya seli D4:D17 na upate mfululizo wa data unaotaka.

3 . Tunaunda urejeshaji wa kielelezo na mlinganyo:

y = bmx

kwa kutumia kazi ya LGRFPRIBL inafanywa vile vile:

Katika safu ya seli C21:D21 tunaingiza chaguo za kukokotoa za LGRFPRIBL kama fomula ya mkusanyiko: =( LGRFPRIBL (B4:B14,A4:A14)). Katika kesi hii, thamani ya parameter m itatambuliwa katika kiini C21, na thamani ya parameter b itatambuliwa katika kiini D21;

formula imeingia kwenye kiini E4: =$D*$C^A4;

kwa kutumia alama ya kujaza, fomula hii inakiliwa kwa anuwai ya seli E4: E17, ambapo safu ya data ya urekebishaji wa kielelezo itapatikana (ona Mchoro 12).

Katika Mtini. Mchoro wa 13 unaonyesha jedwali ambapo unaweza kuona vitendakazi tunavyotumia na safu za seli zinazohitajika, pamoja na fomula.

Kwa data ya awali na mfululizo wa data uliopatikana, mchoro umeonyeshwa kwenye Mtini. 14.