Ang pagtatantya ng pang-eksperimentong data ay isang paraan batay sa pagpapalit ng data na nakuha sa eksperimentong gamit ang isang analytical function na pinaka malapit na pumasa o nag-tutugma sa mga nodal point na may orihinal na mga halaga (data na nakuha sa panahon ng isang eksperimento o eksperimento). Sa kasalukuyan, mayroong dalawang paraan upang tukuyin ang isang analytical function:
Sa pamamagitan ng pagbuo ng n-degree interpolation polynomial na pumasa direkta sa lahat ng mga punto isang ibinigay na hanay ng data. SA sa kasong ito ang approximating function ay kinakatawan bilang: isang interpolation polynomial sa Lagrange form o isang interpolation polynomial sa Newton form.
Sa pamamagitan ng pagbuo ng isang n-degree na tinatayang polynomial na pumasa sa agarang paligid ng mga punto mula sa isang ibinigay na hanay ng data. Kaya, pinapakinis ng approximating function ang lahat ng random na ingay (o mga error) na maaaring lumabas sa panahon ng eksperimento: ang mga sinusukat na halaga sa panahon ng eksperimento ay nakasalalay sa mga random na salik na nagbabago ayon sa kanilang sarili. mga random na batas(mga error sa pagsukat o instrumento, kamalian o mga eksperimentong error). Sa kasong ito, ang approximating function ay tinutukoy gamit ang pamamaraan hindi bababa sa mga parisukat.
Pinakamababang parisukat na pamamaraan(sa English-language literature Ordinary Least Squares, OLS) ay isang mathematical method batay sa pagtukoy sa approximating function, na binuo sa pinakamalapit na proximity sa mga puntos mula sa isang naibigay na array ng experimental data. Ang lapit ng orihinal at tinatayang function na F(x) ay tinutukoy ng isang numerical na sukat, ibig sabihin: ang kabuuan ng mga squared deviations ng eksperimental na data mula sa approximating curve na F(x) ay dapat na pinakamaliit.
Tinatayang curve na ginawa gamit ang least squares method
Ang paraan ng least squares ay ginagamit:
Upang malutas ang mga overdetermined system ng mga equation kapag ang bilang ng mga equation ay lumampas sa bilang ng mga hindi alam;
Upang makahanap ng solusyon sa kaso ng ordinaryo (hindi na-override) nonlinear system mga equation;
Upang tantiyahin ang mga halaga ng punto na may ilang tinatayang function.
Ang approximating function gamit ang least squares method ay tinutukoy mula sa kundisyon ng minimum sum ng squared deviations ng kinakalkula approximating function mula sa isang ibinigay na array ng experimental data. Ang criterion na ito ng least squares method ay nakasulat bilang sumusunod na expression:
Ang mga halaga ng kinakalkula na approximating function sa mga nodal point,
Isang ibinigay na hanay ng pang-eksperimentong data sa mga nodal na punto.
Ang quadratic criterion ay may ilang "magandang" katangian, tulad ng differentiability, na nagbibigay ng natatanging solusyon sa problema sa pagtatantya sa mga polynomial approximating function.
Depende sa mga kondisyon ng problema, ang approximating function ay isang polynomial ng degree m
Ang antas ng approximating function ay hindi nakadepende sa bilang ng mga nodal point, ngunit ang dimensyon nito ay dapat palaging mas mababa kaysa sa dimensyon (bilang ng mga puntos) ng isang ibinigay na pang-eksperimentong array ng data.
∙ Kung ang antas ng approximating function ay m=1, pagkatapos ay tinatantya namin ang tabular function na may isang tuwid na linya (linear regression).
∙ Kung ang antas ng approximating function ay m=2, pagkatapos ay tinatantya namin ang table function parisukat na parabola(quadratic approximation).
∙ Kung ang antas ng approximating function ay m=3, pagkatapos ay tinatantya namin ang table function na may cubic parabola (cubic approximation).
SA pangkalahatang kaso kapag ito ay kinakailangan upang bumuo ng isang approximating polynomial ng degree m para sa ibinigay mga halaga ng talahanayan, ang kundisyon para sa pinakamababang kabuuan ng mga squared deviation sa lahat ng nodal point ay muling isinulat sa sumusunod na anyo:
- hindi kilalang coefficient ng tinatayang polynomial ng degree m;
Ang bilang ng mga halaga ng talahanayan na tinukoy.
Ang isang kinakailangang kondisyon para sa pagkakaroon ng isang minimum ng isang function ay ang pagkakapantay-pantay sa zero ng mga partial derivatives nito na may paggalang sa mga hindi kilalang variable. . Bilang resulta nakukuha namin ang sumusunod na sistema mga equation:
Ibahin natin ang resulta linear na sistema equation: buksan ang mga bracket at ilipat ang mga libreng termino sa kanang bahagi ng expression. Ang resultang sistema ng linear algebraic expression isusulat sa sumusunod na anyo:
Ang sistemang ito ng mga linear algebraic na expression ay maaaring muling isulat sa matrix form:
Ang resulta ay isang sistema linear na equation dimensyon m+1, na binubuo ng m+1 na hindi alam. Ang sistemang ito ay maaaring malutas gamit ang anumang linear na paraan ng solusyon algebraic equation(halimbawa, sa pamamaraang Gaussian). Bilang resulta ng solusyon, makikita ang hindi kilalang mga parameter ng approximating function na nagbibigay ng pinakamababang kabuuan ng squared deviations ng approximating function mula sa orihinal na data, i.e. pinakamahusay na posibleng quadratic approximation. Dapat alalahanin na kung magbago man ang isang value ng source data, babaguhin ng lahat ng coefficient ang kanilang mga value, dahil ganap silang natutukoy ng source data.
Approximation ng source data sa pamamagitan ng linear dependence
(linear regression)
Bilang halimbawa, isaalang-alang ang pamamaraan para sa pagtukoy ng approximating function, na ibinibigay sa form linear dependence. Alinsunod sa paraan ng least squares, ang kundisyon para sa minimum ng kabuuan ng squared deviations ay nakasulat sa sumusunod na anyo:
Mga coordinate ng mga node ng talahanayan;
Mga hindi kilalang coefficient ng approximating function, na tinukoy bilang linear dependence.
Ang isang kinakailangang kondisyon para sa pagkakaroon ng isang minimum ng isang function ay ang pagkakapantay-pantay sa zero ng mga partial derivatives nito na may paggalang sa mga hindi kilalang variable. Bilang resulta, nakukuha namin ang sumusunod na sistema ng mga equation:
Ibahin natin ang resultang linear system ng mga equation.
Nalulutas namin ang nagresultang sistema ng mga linear na equation. Ang mga coefficient ng approximating function sa analytical form ay tinutukoy tulad ng sumusunod (Cramer's method):
Tinitiyak ng mga coefficient na ito ang pagbuo ng isang linear approximating function alinsunod sa criterion ng pag-minimize ng kabuuan ng mga parisukat ng approximating function mula sa mga ibinigay na tabular value (data ng eksperimento).
Algorithm para sa pagpapatupad ng pamamaraan ng least squares
1. Paunang data:
Isang hanay ng pang-eksperimentong data na may bilang ng mga sukat N ay tinukoy
Tinukoy ang antas ng tinatayang polynomial (m).
2. Algoritmo ng pagkalkula:
2.1. Ang mga coefficient para sa pagbuo ng isang sistema ng mga equation na may mga sukat ay tinutukoy
Mga koepisyent ng sistema ng mga equation ( kaliwang bahagi equation)
- index ng numero ng column ng square matrix ng sistema ng mga equation
Libreng mga tuntunin ng sistema ng mga linear na equation ( kanang bahagi equation)
- index ng row number ng square matrix ng system of equation
2.2. Pagbubuo ng isang sistema ng mga linear na equation na may dimensyon .
2.3. Paglutas ng isang sistema ng mga linear na equation upang matukoy ang hindi kilalang coefficient ng isang tinatayang polynomial ng degree m.
2.4. Pagpapasiya ng kabuuan ng mga parisukat na paglihis ng tinatayang polynomial mula sa orihinal na mga halaga sa lahat ng mga nodal na punto
Ang nahanap na halaga ng kabuuan ng mga squared deviations ay ang pinakamababang posible.
Approximation gamit ang iba pang function
Dapat tandaan na kapag tinatantya ang pinagmumulan ng data alinsunod sa pinakamababang paraan ng mga parisukat, minsan ginagamit ang logarithmic function bilang approximating function, exponential function at isang power function.
Logarithmic approximation
Isaalang-alang natin ang kaso kapag ang approximating function ay ibinigay ng isang logarithmic function ng form:
Ang kakanyahan ng pamamaraan ng hindi bababa sa mga parisukat ay sa paghahanap ng mga parameter ng isang modelo ng trend na pinakamahusay na naglalarawan sa ugali ng pag-unlad ng anumang random na kababalaghan sa oras o espasyo (isang trend ay isang linya na nagpapakilala sa ugali ng pag-unlad na ito). Ang gawain ng least squares method (LSM) ay bumaba sa paghahanap hindi lang ng ilang trend model, kundi sa paghahanap ng pinakamahusay o pinakamainam na modelo. Ang modelong ito ay magiging pinakamainam kung ang kabuuan ng mga parisukat na paglihis sa pagitan ng naobserbahang aktwal na mga halaga at ang kaukulang kinakalkula na mga halaga ng trend ay minimal (pinakamaliit):
saan- karaniwang lihis sa pagitan ng naobserbahang aktwal na halaga
at ang kaukulang kinakalkulang halaga ng trend,
Ang aktwal na (naobserbahan) na halaga ng phenomenon na pinag-aaralan,
Ang kinakalkula na halaga ng modelo ng trend,
Bilang ng mga obserbasyon ng phenomenon na pinag-aaralan.
Ang MNC ay bihirang ginagamit sa sarili nitong. Bilang isang patakaran, kadalasan ito ay ginagamit lamang bilang isang kinakailangang teknikal na pamamaraan sa pag-aaral ng ugnayan. Dapat tandaan na ang batayan ng impormasyon ng isang MNC ay maaasahan lamang serye ng istatistika, at ang bilang ng mga obserbasyon ay hindi dapat mas mababa sa 4, kung hindi ay maaaring mawalan ng sentido kumon ang mga pamamaraan ng OLS smoothing.
Ang toolkit ng MNC ay bumaba sa mga sumusunod na pamamaraan:
Unang pamamaraan. Lumalabas kung may anumang ugali na baguhin ang resultang katangian kapag nagbago ang napiling kadahilanan-argument, o sa madaling salita, mayroon bang koneksyon sa pagitan ng " sa "At" X ».
Pangalawang pamamaraan. Natutukoy kung aling linya (trajectory) ang pinakamahusay na naglalarawan o nagpapakilala sa trend na ito.
Pangatlong pamamaraan.
Halimbawa. Sabihin nating mayroon tayong impormasyon tungkol sa average na ani ng sunflower para sa sakahan na pinag-aaralan (Talahanayan 9.1).
Talahanayan 9.1
|
Numero ng pagmamasid |
||||||||||
|
Produktibo, c/ha |
Dahil ang antas ng teknolohiya sa paggawa ng sunflower sa ating bansa ay nanatiling halos hindi nagbabago sa nakalipas na 10 taon, nangangahulugan ito na, tila, ang mga pagbabago sa ani sa panahon ng nasuri ay lubos na nakadepende sa mga pagbabago sa panahon at klimatiko na kondisyon. Totoo ba talaga ito?
Unang pamamaraan ng OLS. Ang hypothesis tungkol sa pagkakaroon ng trend sa sunflower yield ay nagbabago depende sa mga pagbabago sa lagay ng panahon at klimatiko sa loob ng nasuri na 10 taon ay nasubok.
Sa halimbawang ito, para sa " y " ipinapayong kunin ang ani ng mirasol, at para sa " x » – bilang ng naobserbahang taon sa nasuri na panahon. Pagsubok sa hypothesis tungkol sa pagkakaroon ng anumang kaugnayan sa pagitan ng " x "At" y "ay maaaring gawin sa dalawang paraan: manu-mano at paggamit ng mga programa sa computer. Siyempre, kung magagamit kagamitan sa kompyuter ang problemang ito ay nalulutas mismo. Ngunit upang mas maunawaan ang mga tool ng MNC, ipinapayong subukan ang hypothesis tungkol sa pagkakaroon ng isang relasyon sa pagitan ng " x "At" y » mano-mano, kapag panulat at ordinaryong calculator lang ang nasa kamay. Sa ganitong mga kaso, ang hypothesis tungkol sa pagkakaroon ng isang trend ay pinakamahusay na sinusuri nang biswal sa pamamagitan ng lokasyon ng graphical na imahe ng nasuri na serye ng dynamics - ang patlang ng ugnayan:
Ang patlang ng ugnayan sa aming halimbawa ay matatagpuan sa paligid ng dahan-dahang pagtaas ng linya. Ito mismo ay nagpapahiwatig ng pagkakaroon ng isang tiyak na kalakaran sa mga pagbabago sa mga ani ng mirasol. Imposibleng pag-usapan ang pagkakaroon ng anumang ugali lamang kapag ang patlang ng ugnayan ay mukhang isang bilog, isang bilog, isang mahigpit na patayo o mahigpit na pahalang na ulap, o binubuo ng mga chaotically scattered na mga puntos. Sa lahat ng iba pang mga kaso, ang hypothesis tungkol sa pagkakaroon ng isang relasyon sa pagitan ng " x "At" y ", at magpatuloy sa pananaliksik.
Pangalawang pamamaraan ng OLS. Natutukoy kung aling linya (trajectory) ang pinakamahusay na naglalarawan o nagpapakita ng takbo ng mga pagbabago sa ani ng sunflower sa nasuri na panahon.
Kung mayroon kang teknolohiya sa computer, awtomatikong nangyayari ang pagpili ng pinakamainam na trend. Kapag manu-mano ang pagproseso, ang pagpipilian pinakamainam na pag-andar ay isinasagawa, bilang isang panuntunan, biswal - sa pamamagitan ng lokasyon ng patlang ng ugnayan. Iyon ay, batay sa uri ng graph, ang equation ng linya na pinakaangkop sa empirical trend (ang aktwal na trajectory) ay pinili.
Tulad ng nalalaman, sa likas na katangian mayroong isang malaking iba't ibang mga functional dependencies, kaya napakahirap na biswal na pag-aralan kahit isang maliit na bahagi ng mga ito. Sa kabutihang palad, sa tunay na kasanayan sa ekonomiya, karamihan sa mga relasyon ay maaaring ilarawan nang tumpak alinman sa pamamagitan ng isang parabola, o isang hyperbola, o isang tuwid na linya. Sa pagsasaalang-alang na ito, gamit ang "manu-manong" na opsyon sa pagpili ng pinakamahusay na function, maaari mong limitahan ang iyong sarili sa tatlong modelong ito lamang.
|
Hyperbola: |
||
|
|
|
Pangalawang order parabola: 
:
Madaling makita na sa aming halimbawa, ang takbo ng pagbabago sa ani ng sunflower sa loob ng nasuri na 10 taon ay pinakamahusay na nailalarawan sa pamamagitan ng isang tuwid na linya, kaya ang equation ng regression ay magiging equation ng isang tuwid na linya.
Pangatlong pamamaraan. Kinakalkula ang mga parameter equation ng regression nagpapakilala sa isang naibigay na linya, o sa madaling salita, isang analytical formula ay tinutukoy na naglalarawan pinakamahusay na modelo uso.
Ang paghahanap ng mga halaga ng mga parameter ng equation ng regression, sa aming kaso ang mga parameter at , ay ang core ng OLS. Ang prosesong ito ay bumababa sa paglutas ng isang sistema ng mga normal na equation.
(9.2)
Ang sistemang ito ng mga equation ay madaling malutas sa pamamagitan ng pamamaraang Gauss. Alalahanin natin na bilang isang resulta ng solusyon, sa aming halimbawa, ang mga halaga ng mga parameter at natagpuan. Kaya, ang nahanap na equation ng regression ay magkakaroon ng sumusunod na anyo: 
Mayroon itong maraming mga application, dahil pinapayagan nito ang isang tinatayang representasyon ng isang naibigay na function ng iba pang mas simple. Ang LSM ay maaaring maging lubhang kapaki-pakinabang sa pagproseso ng mga obserbasyon, at ito ay aktibong ginagamit upang tantiyahin ang ilang dami batay sa mga resulta ng mga sukat ng iba na naglalaman ng mga random na error. Sa artikulong ito, matututunan mo kung paano ipatupad ang mga kalkulasyon ng hindi bababa sa mga parisukat sa Excel.
Paglalahad ng problema gamit ang isang tiyak na halimbawa
Ipagpalagay na mayroong dalawang mga tagapagpahiwatig X at Y. Bukod dito, ang Y ay nakasalalay sa X. Dahil ang OLS ay interesado sa amin mula sa punto ng view ng pagsusuri ng regression (sa Excel ang mga pamamaraan nito ay ipinatupad gamit ang mga built-in na function), dapat nating agad na magpatuloy sa pagsasaalang-alang ng isang tiyak na problema.
Kaya, hayaang ang X ang retail space ng isang grocery store, na sinusukat sa square meters, at ang Y ang taunang turnover, na tinutukoy sa milyun-milyong rubles.
Kinakailangang gumawa ng forecast kung ano ang magiging turnover (Y) ng tindahan kung mayroon itong ganito o iyon na retail space. Malinaw, ang function na Y = f (X) ay tumataas, dahil ang hypermarket ay nagbebenta ng mas maraming kalakal kaysa sa stall.
Ilang salita tungkol sa kawastuhan ng paunang data na ginamit para sa hula
Sabihin nating mayroon kaming isang talahanayan na binuo gamit ang data para sa n mga tindahan.
Ayon sa mga istatistika ng matematika, ang mga resulta ay magiging mas marami o hindi gaanong tama kung ang data sa hindi bababa sa 5-6 na mga bagay ay susuriin. Bilang karagdagan, hindi maaaring gamitin ang mga "anomalyang" resulta. Sa partikular, ang isang piling maliit na boutique ay maaaring magkaroon ng turnover na ilang beses na mas malaki kaysa sa turnover ng malalaking retail outlet ng klase ng "masmarket".
Ang kakanyahan ng pamamaraan
Ang data ng talahanayan ay maaaring ilarawan sa isang Cartesian plane sa anyo ng mga puntos M 1 (x 1, y 1), ... M n (x n, y n). Ngayon ang solusyon sa problema ay mababawasan sa pagpili ng isang approximating function y = f (x), na may isang graph na dumadaan nang mas malapit hangga't maaari sa mga puntos na M 1, M 2, .. M n.
Siyempre, maaari kang gumamit ng isang high-degree na polynomial, ngunit ang pagpipiliang ito ay hindi lamang mahirap ipatupad, ngunit mali din, dahil hindi ito magpapakita ng pangunahing trend na kailangang makita. Ang pinaka-makatwirang solusyon ay ang paghahanap para sa tuwid na linya y = ax + b, na pinakamahusay na tinatantya ang pang-eksperimentong data, o mas tiyak, ang mga coefficient a at b.
Pagtatasa ng katumpakan
Sa anumang pagtataya, ang pagtatasa ng katumpakan nito ay partikular na kahalagahan. Tukuyin natin sa pamamagitan ng e i ang pagkakaiba (paglihis) sa pagitan ng mga functional at pang-eksperimentong halaga para sa punto x i, ibig sabihin, e i = y i - f (x i).
Malinaw, upang masuri ang katumpakan ng pagtatantya, maaari mong gamitin ang kabuuan ng mga paglihis, ibig sabihin, kapag pumipili ng isang tuwid na linya para sa isang tinatayang representasyon ng pag-asa ng X sa Y, kailangan mong bigyan ng kagustuhan ang isa na may pinakamaliit na halaga sums e i sa lahat ng isinasaalang-alang na puntos. Gayunpaman, hindi lahat ay napakasimple, dahil kasama ang mga positibong paglihis ay magkakaroon din ng mga negatibo.
Ang isyu ay maaaring malutas gamit ang mga module ng paglihis o ang kanilang mga parisukat. Ang huling paraan ay ang pinaka malawak na ginagamit. Ginagamit ito sa maraming lugar, kabilang ang pagsusuri ng regression (sa Excel, ipinatupad ito gamit ang dalawang built-in na function), at matagal nang napatunayang epektibo.
Pinakamababang parisukat na pamamaraan
Ang Excel, tulad ng alam mo, ay may built-in na AutoSum function na nagbibigay-daan sa iyo upang kalkulahin ang mga halaga ng lahat ng mga halaga na matatagpuan sa napiling hanay. Kaya, walang makakapigil sa amin sa pagkalkula ng halaga ng expression (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
SA mathematical notation parang:
Dahil ang desisyon ay unang ginawa sa pagtatantya gamit ang isang tuwid na linya, mayroon kaming:
Kaya, ang gawain ng paghahanap ng linya na pinakamahusay na naglalarawan tiyak na dependency dami X at Y, bumababa sa pagkalkula ng minimum ng isang function ng dalawang variable:
Upang gawin ito, kailangan mong i-equate ang mga partial derivatives na may paggalang sa mga bagong variable na a at b sa zero, at lutasin ang isang primitive system na binubuo ng dalawang equation na may 2 hindi alam ng form:
Pagkatapos ng ilang simpleng pagbabago, kabilang ang paghahati sa 2 at pagmamanipula ng mga kabuuan, makakakuha tayo ng:
Ang paglutas nito, halimbawa, gamit ang paraan ng Cramer, nakakakuha tayo ng isang nakatigil na punto na may ilang mga coefficient a * at b *. Ito ang minimum, ibig sabihin, upang mahulaan kung anong turnover ang magkakaroon ng isang tindahan para sa isang partikular na lugar, ang tuwid na linyang y = a * x + b * ay angkop, na isang modelo ng regression para sa halimbawang pinag-uusapan. Siyempre, hindi ka nito papayagan na mahanap ang eksaktong resulta, ngunit makakatulong ito sa iyong magkaroon ng ideya kung ang pagbili ng isang partikular na lugar sa credit ng tindahan ay magbabayad.
Paano Ipatupad ang Least Squares sa Excel
Ang Excel ay may function para sa pagkalkula ng mga halaga gamit ang hindi bababa sa mga parisukat. Mayroon itong sumusunod na anyo: "TREND" (kilalang mga halaga ng Y; kilalang mga halaga ng X; mga bagong halaga ng X; pare-pareho). Ilapat natin ang formula para sa pagkalkula ng OLS sa Excel sa aming talahanayan.
Upang gawin ito, ipasok ang "=" sign sa cell kung saan dapat ipakita ang resulta ng pagkalkula gamit ang pinakamababang paraan ng mga parisukat sa Excel at piliin ang function na "TREND". Sa window na bubukas, punan ang naaangkop na mga patlang, na naka-highlight:
- hanay ng mga kilalang halaga para sa Y (sa kasong ito, data para sa trade turnover);
- range x 1 , …x n , ibig sabihin, ang laki ng retail space;
- parehong sikat at hindi kilalang mga halaga x, kung saan kailangan mong malaman ang laki ng turnover (para sa impormasyon tungkol sa kanilang lokasyon sa worksheet, tingnan sa ibaba).
Bilang karagdagan, ang formula ay naglalaman ng lohikal na variable na "Const". Kung maglalagay ka ng 1 sa kaukulang field, nangangahulugan ito na dapat mong isagawa ang mga kalkulasyon, sa pag-aakalang b = 0.
Kung kailangan mong malaman ang forecast para sa higit sa isang x na halaga, pagkatapos ay pagkatapos na ipasok ang formula hindi mo dapat pindutin ang "Enter", ngunit kailangan mong i-type ang kumbinasyon na "Shift" + "Control" + "Enter" sa keyboard.
Ang ilang mga tampok
Pagsusuri ng regression maaaring ma-access kahit sa mga dummies. Ang formula ng Excel para sa paghula ng halaga ng isang hanay ng mga hindi kilalang variable—TREND—ay maaaring gamitin kahit na sa mga hindi pa nakakarinig ng hindi bababa sa mga parisukat. Sapat lamang na malaman ang ilan sa mga tampok ng gawain nito. Sa partikular:
- Kung ayusin mo ang hanay ng mga kilalang halaga ng variable y sa isang hilera o haligi, pagkatapos ay ang bawat hilera (column) ay may kilalang halaga x ay ituturing ng programa bilang isang hiwalay na variable.
- Kung ang TREND window ay hindi nagsasaad ng isang range na may alam na x, kung gayon kung ang function ay ginagamit sa Excel program ituturing ito bilang isang array na binubuo ng mga integer, ang bilang nito ay tumutugma sa hanay na may ibinigay na mga halaga ng variable na y.
- Upang mag-output ng array ng mga "hulaang" value, ang expression para sa pagkalkula ng trend ay dapat na ilagay bilang array formula.
- Kung ang mga bagong halaga ng x ay hindi tinukoy, ang TREND function ay isinasaalang-alang ang mga ito na katumbas ng mga kilala. Kung hindi sila tinukoy, ang array 1 ay kukunin bilang argumento; 2; 3; 4;…, na naaayon sa hanay na may tinukoy nang mga parameter y.
- Ang hanay na naglalaman ng mga bagong x value ay dapat na pareho o higit pang mga row o column gaya ng range na naglalaman ng mga ibinigay na y value. Sa madaling salita, dapat itong proporsyonal sa mga independiyenteng variable.
- Ang isang array na may mga kilalang x value ay maaaring maglaman ng maraming variable. Gayunpaman, kung isa lamang ang pinag-uusapan, kinakailangan na ang mga saklaw na may ibinigay na mga halaga ng x at y ay proporsyonal. Sa kaso ng ilang mga variable, kinakailangan na ang saklaw na may ibinigay na mga halaga ng y ay magkasya sa isang hanay o isang hilera.
PREDICTION function
Ipinatupad gamit ang ilang mga function. Ang isa sa mga ito ay tinatawag na "PREDICTION". Ito ay katulad ng "TREND", ibig sabihin, binibigyan nito ang resulta ng mga kalkulasyon gamit ang paraan ng least squares. Gayunpaman, para lamang sa isang X, kung saan hindi alam ang halaga ng Y.
Ngayon alam mo na ang mga formula sa Excel para sa mga dummies na nagbibigay-daan sa iyong hulaan ang hinaharap na halaga ng isang partikular na tagapagpahiwatig ayon sa isang linear na trend.
Halimbawa.
Pang-eksperimentong data sa mga halaga ng mga variable X At sa ay ibinigay sa talahanayan.
Bilang resulta ng kanilang pagkakahanay, nakuha ang pag-andar 
Gamit hindi bababa sa parisukat na paraan, tantiyahin ang mga data na ito sa pamamagitan ng isang linear na dependence y=ax+b(hanapin ang mga parameter A At b). Alamin kung alin sa dalawang linya ang mas mahusay (sa kahulugan ng paraan ng least squares) ang nakahanay sa pang-eksperimentong data. Gumawa ng drawing.
Ang kakanyahan ng pamamaraan ng least squares (LSM).
Ang gawain ay upang mahanap ang mga linear dependence coefficients kung saan ang function ng dalawang variable A At b
kumukuha ng pinakamaliit na halaga. Ibig sabihin, binigay A At b ang kabuuan ng mga squared deviations ng pang-eksperimentong data mula sa natagpuang tuwid na linya ang magiging pinakamaliit. Ito ang buong punto ng pamamaraan ng least squares.
Kaya, ang paglutas ng halimbawa ay bumababa sa paghahanap ng extremum ng isang function ng dalawang variable.
Pagkuha ng mga formula para sa paghahanap ng mga coefficient.
Ang isang sistema ng dalawang equation na may dalawang hindi alam ay pinagsama-sama at nalutas. Paghahanap ng mga partial derivatives ng isang function 
sa pamamagitan ng mga variable A At b, itinutumbas namin ang mga derivatives na ito sa zero. 
Nilulutas namin ang nagresultang sistema ng mga equation gamit ang anumang pamamaraan (halimbawa sa pamamagitan ng paraan ng pagpapalit o Pamamaraan ni Cramer) at kumuha ng mga formula para sa paghahanap ng mga coefficient gamit ang least squares method (LSM). 
Ibinigay A At b function 
kumukuha ng pinakamaliit na halaga. Ang patunay ng katotohanang ito ay ibinigay sa ibaba sa teksto sa dulo ng pahina.
Iyan ang buong paraan ng hindi bababa sa mga parisukat. Formula para sa paghahanap ng parameter a naglalaman ng mga kabuuan ,,, at parameter n- dami ng pang-eksperimentong data. Inirerekomenda namin ang pagkalkula ng mga halaga ng mga halagang ito nang hiwalay. Coefficient b natagpuan pagkatapos ng pagkalkula a.
Oras na para alalahanin ang orihinal na halimbawa.
Solusyon.
Sa ating halimbawa n=5. Pinupuno namin ang talahanayan para sa kaginhawaan ng pagkalkula ng mga halaga na kasama sa mga formula ng kinakailangang coefficients. 
Ang mga halaga sa ika-apat na hilera ng talahanayan ay nakuha sa pamamagitan ng pagpaparami ng mga halaga ng ika-2 hilera sa mga halaga ng ika-3 hilera para sa bawat numero i.
Ang mga halaga sa ikalimang hilera ng talahanayan ay nakuha sa pamamagitan ng pag-squaring ng mga halaga sa ika-2 hilera para sa bawat numero i.
Ang mga halaga sa huling hanay ng talahanayan ay ang mga kabuuan ng mga halaga sa mga hilera.
Ginagamit namin ang mga formula ng pinakamaliit na paraan ng mga parisukat upang mahanap ang mga coefficient A At b. Pinapalitan namin ang kaukulang mga halaga mula sa huling hanay ng talahanayan sa kanila: 
Kaya naman, y = 0.165x+2.184- ang nais na tinatayang tuwid na linya.
Ito ay nananatiling alamin kung alin sa mga linya y = 0.165x+2.184 o 
mas mahusay na tinatantya ang orihinal na data, iyon ay, gumagawa ng isang pagtatantya gamit ang least squares method.
Error sa pagtatantya ng least squares method.
Upang gawin ito, kailangan mong kalkulahin ang kabuuan ng mga squared deviations ng orihinal na data mula sa mga linyang ito 
At 
, ang isang mas maliit na halaga ay tumutugma sa isang linya na mas mahusay na tinatantya ang orihinal na data sa kahulugan ng paraan ng least squares. 
Since , tapos straight y = 0.165x+2.184 mas mahusay na tinatantya ang orihinal na data.
Graphic na paglalarawan ng least squares (LS) na pamamaraan.
Ang lahat ay malinaw na nakikita sa mga graph. Ang pulang linya ay ang natagpuang tuwid na linya y = 0.165x+2.184, ang asul na linya ay 
, ang mga pink na tuldok ay ang orihinal na data.
Sa pagsasagawa, kapag nagmomodelo ng iba't ibang mga proseso - sa partikular, pang-ekonomiya, pisikal, teknikal, panlipunan - isa o ibang paraan ng pagkalkula ng tinatayang mga halaga ng mga pag-andar mula sa kanilang mga kilalang halaga sa ilang mga nakapirming punto ay malawakang ginagamit.
Ang ganitong uri ng problema sa approximation ng function ay madalas na lumitaw:
kapag bumubuo ng tinatayang mga formula para sa pagkalkula ng mga halaga ng mga katangian na dami ng proseso sa ilalim ng pag-aaral gamit ang tabular na data na nakuha bilang isang resulta ng eksperimento;
sa numerical integration, differentiation, solusyon differential equation atbp.;
kung kinakailangan, kalkulahin ang mga halaga ng mga pag-andar sa mga intermediate na punto ng itinuturing na agwat;
kapag tinutukoy ang mga halaga ng mga katangian na dami ng isang proseso sa labas ng itinuturing na agwat, lalo na sa pagtataya.
Kung, upang mag-modelo ng isang tiyak na proseso na tinukoy ng isang talahanayan, bumuo kami ng isang function na humigit-kumulang na naglalarawan sa prosesong ito batay sa pamamaraan ng hindi bababa sa mga parisukat, ito ay tatawaging approximating function (regression), at ang gawain ng pagbuo ng approximating function mismo ay tatawagin isang problema sa pagtatantya.
Tinatalakay ng artikulong ito ang mga kakayahan ng pakete ng MS Excel para sa paglutas ng ganitong uri ng problema, bilang karagdagan, nagbibigay ito ng mga pamamaraan at pamamaraan para sa pagbuo (paglikha) ng mga regression para sa mga naka-tabulated na function (na siyang batayan ng pagsusuri ng regression).
May dalawang opsyon ang Excel para sa pagbuo ng mga regression.
Pagdaragdag ng mga napiling regression ( mga linya ng uso- trendlines) sa isang diagram na binuo batay sa isang talahanayan ng data para sa katangian ng proseso sa ilalim ng pag-aaral (magagamit lamang kung mayroong isang binuo na diagram);
Gamit ang built-in na statistical function ng Excel worksheet, na nagbibigay-daan sa iyong makakuha ng mga regression (mga linya ng trend) nang direkta mula sa source data table.
Pagdaragdag ng mga linya ng trend sa isang tsart
Para sa isang talahanayan ng data na naglalarawan sa isang proseso at kinakatawan ng isang diagram, ang Excel ay may isang epektibong tool sa pagsusuri ng regression na nagbibigay-daan sa iyong:
bumuo sa batayan ng paraan ng least squares at magdagdag ng limang uri ng regressions sa diagram, na nagmomodelo sa prosesong pinag-aaralan na may iba't ibang antas ng katumpakan;
idagdag ang constructed regression equation sa diagram;
tukuyin ang antas ng pagsusulatan ng napiling regression sa data na ipinapakita sa tsart.
Batay sa data ng tsart, pinapayagan ka ng Excel na makakuha ng linear, polynomial, logarithmic, power, exponential na mga uri ng regressions, na tinukoy ng equation:
y = y(x)
kung saan ang x ay isang independiyenteng variable na kadalasang kumukuha ng mga halaga ng pagkakasunod-sunod ng mga natural na numero (1; 2; 3; ...) at gumagawa, halimbawa, ng countdown ng oras ng prosesong pinag-aaralan (mga katangian).
1 . Ang linear regression ay mabuti para sa pagmomodelo ng mga katangian na ang mga halaga ay tumataas o bumaba sa isang pare-parehong rate. Ito ang pinakasimpleng modelo na gagawin para sa prosesong pinag-aaralan. Ito ay itinayo alinsunod sa equation:
y = mx + b
kung saan ang m ay ang padaplis ng anggulo ng pagkahilig linear regression sa abscissa axis; b - coordinate ng punto ng intersection ng linear regression na may ordinate axis.
2 . Ang isang polynomial trend line ay kapaki-pakinabang para sa paglalarawan ng mga katangian na may ilang natatanging extremes (maxima at minima). Ang pagpili ng polynomial degree ay tinutukoy ng bilang ng extrema ng katangian na pinag-aaralan. Kaya, ang isang pangalawang-degree na polynomial ay mahusay na naglalarawan ng isang proseso na mayroon lamang isang maximum o minimum; polynomial ng ikatlong antas - hindi hihigit sa dalawang extrema; polynomial ng ika-apat na degree - hindi hihigit sa tatlong extrema, atbp.
Sa kasong ito, ang linya ng trend ay itinayo alinsunod sa equation:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
kung saan ang mga coefficient c0, c1, c2,... c6 ay mga constant na ang mga halaga ay tinutukoy sa panahon ng konstruksiyon.
3 . Ang logarithmic trend line ay matagumpay na ginagamit kapag nagmomodelo ng mga katangian na ang mga halaga ay mabilis na nagbabago at pagkatapos ay unti-unting nagpapatatag.
y = c ln(x) + b
4 . Ang isang power-law trend line ay nagbibigay ng magagandang resulta kung ang mga halaga ng relasyon sa ilalim ng pag-aaral ay nailalarawan sa pamamagitan ng patuloy na pagbabago sa rate ng paglago. Ang isang halimbawa ng naturang pag-asa ay ang graph ng pantay na pinabilis na paggalaw ng isang kotse. Kung mayroong zero o negatibong mga halaga sa data, hindi ka maaaring gumamit ng linya ng trend ng kuryente.
Binuo alinsunod sa equation:
y = c xb
kung saan ang mga coefficient b, c ay mga constant.
5 . Dapat gumamit ng exponential trend line kapag patuloy na tumataas ang rate ng pagbabago sa data. Para sa data na naglalaman ng zero o negatibong mga halaga, hindi rin naaangkop ang ganitong uri ng pagtatantya.
Binuo alinsunod sa equation:
y = c ebx
kung saan ang mga coefficient b, c ay mga constant.
Kapag pumipili ng linya ng trend, awtomatikong kinakalkula ng Excel ang halaga ng R2, na nagpapakilala sa pagiging maaasahan ng pagtatantya: kaysa mas malapit na halaga R2 sa pagkakaisa, mas mapagkakatiwalaan ang trend line na tinatantya ang prosesong pinag-aaralan. Kung kinakailangan, ang halaga ng R2 ay maaaring palaging ipakita sa tsart.
Natutukoy ng formula:
Upang magdagdag ng linya ng trend sa isang serye ng data:
buhayin ang isang tsart batay sa isang serye ng data, ibig sabihin, mag-click sa loob ng lugar ng tsart. Ang item na Diagram ay lilitaw sa pangunahing menu;
pagkatapos mag-click sa item na ito, lalabas ang isang menu sa screen kung saan dapat mong piliin ang Add trend line command.
Ang parehong mga aksyon ay madaling maipatupad sa pamamagitan ng paggalaw ng mouse pointer sa ibabaw ng graph na tumutugma sa isa sa mga serye ng data at pag-right-click; Sa lalabas na menu ng konteksto, piliin ang command na Add trend line. Lalabas ang dialog box ng Trend Line sa screen na may nakabukas na tab na Uri (Fig. 1).
Pagkatapos nito kailangan mo:
Piliin ang kinakailangang uri ng linya ng trend sa tab na Uri (ang uri ng Linear ay pinili bilang default). Para sa uri ng Polynomial, sa field na Degree, tukuyin ang antas ng napiling polynomial.
1 . Inililista ng field na Built on series ang lahat ng serye ng data sa chart na pinag-uusapan. Upang magdagdag ng linya ng trend sa isang partikular na serye ng data, piliin ang pangalan nito sa field na Built on series.
Kung kinakailangan, sa pamamagitan ng pagpunta sa tab na Mga Parameter (Fig. 2), maaari mong itakda ang mga sumusunod na parameter para sa linya ng trend:
baguhin ang pangalan ng trend line sa Pangalan ng tinatayang (pinakinis) na patlang ng kurba.
itakda ang bilang ng mga tuldok (pasulong o paatras) para sa pagtataya sa field ng Pagtataya;
ipakita ang equation ng trend line sa lugar ng diagram, kung saan dapat mong paganahin ang show equation sa checkbox ng diagram;
ipakita ang approximation reliability value R2 sa lugar ng diagram, kung saan dapat mong paganahin ang checkbox na Ilagay ang approximation reliability value sa diagram (R^2);
itakda ang intersection point ng trend line na may Y axis, kung saan dapat mong paganahin ang checkbox para sa intersection ng curve na may Y axis sa isang punto;
I-click ang OK button upang isara ang dialog box.
Upang simulan ang pag-edit ng iginuhit na trend line, mayroong tatlong paraan:
gamitin ang Napiling linya ng trend na utos mula sa Format menu, na dati nang napili ang trend line;
piliin ang Format trend line command mula sa context menu, na tinatawag sa pamamagitan ng right-click sa trend line;
i-double click ang trend line.
Ang dialog box ng Trend Line Format ay lalabas sa screen (Fig. 3), na naglalaman ng tatlong tab: View, Type, Parameters, at ang mga nilalaman ng huling dalawang ganap na nag-tutugma sa mga katulad na tab ng Trend Line dialog box (Fig. 1). -2). Sa tab na View, maaari mong itakda ang uri ng linya, kulay at kapal nito.
Upang tanggalin ang isang trend line na na-drawing na, piliin ang trend line na tatanggalin at pindutin ang Delete key.
Ang mga pakinabang ng itinuturing na tool sa pagsusuri ng regression ay:
ang kamag-anak na kadalian ng pagbuo ng isang trend line sa mga chart nang hindi gumagawa ng talahanayan ng data para dito;
isang medyo malawak na listahan ng mga uri ng mga iminungkahing linya ng trend, at kasama sa listahang ito ang mga pinakakaraniwang ginagamit na uri ng regression;
ang kakayahang mahulaan ang pag-uugali ng prosesong pinag-aaralan sa pamamagitan ng isang arbitraryo (sa loob ng mga limitasyon ng sentido komun) bilang ng mga hakbang pasulong at paatras din;
ang kakayahang makuha ang equation ng trend line sa analytical form;
ang posibilidad, kung kinakailangan, ng pagkuha ng pagtatasa ng pagiging maaasahan ng approximation.
Kabilang sa mga disadvantage ang mga sumusunod:
ang pagtatayo ng isang trend line ay isinasagawa lamang kung mayroong isang diagram na binuo sa isang serye ng data;
ang proseso ng pagbuo ng serye ng data para sa katangiang pinag-aaralan batay sa mga equation ng trend line na nakuha para dito ay medyo kalat: ang mga kinakailangang regression equation ay ina-update sa bawat pagbabago sa mga halaga ng orihinal na serye ng data, ngunit sa loob lamang ng lugar ng diagram , habang serye ng datos, na nabuo batay sa lumang equation ng linya ng trend, ay nananatiling hindi nagbabago;
Sa mga ulat ng PivotChart, ang pagbabago ng view ng isang chart o nauugnay na ulat ng PivotTable ay hindi nagpapanatili ng mga kasalukuyang trendline, ibig sabihin, bago ka gumuhit ng mga trendline o kung hindi man ay mag-format ng ulat ng PivotChart, dapat mong tiyakin na ang layout ng ulat ay nakakatugon sa mga kinakailangang kinakailangan.
Maaaring gamitin ang mga linya ng trend upang madagdagan ang mga serye ng data na ipinakita sa mga chart gaya ng graph, histogram, flat non-standardized na area chart, bar chart, scatter chart, bubble chart, at stock chart.
Hindi ka maaaring magdagdag ng mga linya ng trend sa mga serye ng data sa 3D, normalized, radar, pie, at donut chart.
Gamit ang mga built-in na function ng Excel
Ang Excel ay mayroon ding tool sa pagsusuri ng regression para sa pag-plot ng mga linya ng trend sa labas ng lugar ng tsart. Mayroong ilang mga statistical worksheet function na maaari mong gamitin para sa layuning ito, ngunit lahat ng mga ito ay nagpapahintulot lamang sa iyo na bumuo ng mga linear o exponential regression.
Ang Excel ay may ilang mga function para sa pagbuo ng linear regression, sa partikular:
SLOPE at PUTOL.
TREND;
Pati na rin ang ilang mga function para sa pagbuo ng isang exponential trend line, sa partikular:
LGRFPRIBL.
Dapat tandaan na ang mga pamamaraan para sa pagbuo ng mga regression gamit ang TREND at GROWTH function ay halos pareho. Ang parehong ay maaaring sinabi tungkol sa pares ng mga function LINEST at LGRFPRIBL. Para sa apat na function na ito, ang paglikha ng isang talahanayan ng mga halaga ay gumagamit ng mga tampok ng Excel tulad ng mga array formula, na medyo nakakagulo sa proseso ng pagbuo ng mga regression. Tandaan din natin na ang pagbuo ng linear regression, sa aming opinyon, ay pinakamadaling magawa gamit ang SLOPE at INTERCEPT function, kung saan ang una sa mga ito ay tumutukoy sa slope ng linear regression, at ang pangalawa ay tumutukoy sa segment na naharang ng regression sa ang y-axis.
Ang mga bentahe ng built-in na tool sa pag-andar para sa pagsusuri ng regression ay:
isang medyo simple, pare-parehong proseso ng pagbuo ng serye ng data ng katangiang pinag-aaralan para sa lahat ng built-in na istatistikal na function na tumutukoy sa mga linya ng trend;
karaniwang pamamaraan para sa pagbuo ng mga linya ng trend batay sa nabuong serye ng data;
ang kakayahang mahulaan ang pag-uugali ng prosesong pinag-aaralan sa pamamagitan ng kinakailangang bilang ng mga hakbang pasulong o paatras.
Kabilang sa mga disadvantage ang katotohanang walang built-in na function ang Excel para sa paglikha ng iba pang (maliban sa linear at exponential) na uri ng mga linya ng trend. Madalas na hindi pinapayagan ng sitwasyong ito ang pagpili ng sapat na tumpak na modelo ng prosesong pinag-aaralan, pati na rin ang pagkuha ng mga hula na malapit sa katotohanan. Bilang karagdagan, kapag ginagamit ang TREND at GROWTH function, ang mga equation ng mga linya ng trend ay hindi alam.
Dapat tandaan na ang mga may-akda ay hindi nagtakda upang ipakita ang kurso ng pagsusuri ng regression na may anumang antas ng pagkakumpleto. Ang pangunahing gawain nito ay upang ipakita, gamit ang mga partikular na halimbawa, ang mga kakayahan ng pakete ng Excel kapag nilulutas ang mga problema sa pagtatantya; ipakita kung anong mga epektibong tool ang Excel para sa pagbuo ng mga regression at pagtataya; ilarawan kung paano mas madaling malutas ang mga naturang problema kahit ng isang gumagamit na walang malawak na kaalaman sa pagsusuri ng regression.
Mga halimbawa ng paglutas ng mga partikular na problema
Tingnan natin ang paglutas ng mga partikular na problema gamit ang mga nakalistang tool sa Excel.
Problema 1
Sa isang talahanayan ng data sa kita ng isang negosyo sa transportasyon ng motor para sa 1995-2002. kailangan mong gawin ang sumusunod:
Bumuo ng diagram.
Magdagdag ng linear at polynomial (quadratic at cubic) na mga linya ng trend sa chart.
Gamit ang mga equation ng trend line, kumuha ng tabular na data sa mga kita ng enterprise para sa bawat trend line para sa 1995-2004.
Gumawa ng pagtataya para sa kita ng negosyo para sa 2003 at 2004.
Ang solusyon sa problema
Sa hanay ng mga cell A4:C11 ng Excel worksheet, ilagay ang worksheet na ipinapakita sa Fig. 4.
Ang pagkakaroon ng napiling hanay ng mga cell B4:C11, bumuo kami ng isang diagram.
Isinaaktibo namin ang itinayong diagram at, ayon sa pamamaraang inilarawan sa itaas, pagkatapos piliin ang uri ng trend line sa dialog box ng Trend Line (tingnan ang Fig. 1), halili kaming nagdaragdag ng mga linear, quadratic at cubic trend lines sa diagram. Sa parehong dialog box, buksan ang tab na Mga Parameter (tingnan ang Fig. 2), sa Pangalan ng tinatayang (smoothed) na curve field, ipasok ang pangalan ng trend na idinaragdag, at sa Forecast forward para sa: mga patlang, itakda ang halaga 2, dahil ito ay binalak na gumawa ng pagtataya ng kita para sa dalawang taon sa hinaharap. Upang ipakita ang regression equation at ang approximation reliability value na R2 sa lugar ng diagram, paganahin ang show equation sa mga checkbox ng screen at ilagay ang approximation reliability value (R^2) sa diagram. Para sa mas mahusay na visual na perception, binabago namin ang uri, kulay at kapal ng mga itinayong linya ng trend, kung saan ginagamit namin ang View tab ng Trend Line Format dialog box (tingnan ang Fig. 3). Ang resultang diagram na may idinagdag na mga linya ng trend ay ipinapakita sa Fig. 5.
Upang makakuha ng tabular na data sa mga kita ng enterprise para sa bawat trend line para sa 1995-2004. Gamitin natin ang mga equation ng trend line na ipinakita sa Fig. 5. Upang gawin ito, sa mga cell ng hanay na D3:F3, ilagay ang impormasyon ng teksto tungkol sa uri ng napiling linya ng trend: Linear trend, Quadratic trend, Cubic trend. Susunod, ilagay ang linear regression formula sa cell D4 at, gamit ang fill marker, kopyahin ang formula na ito na may mga kaugnay na sanggunian sa hanay ng cell D5:D13. Dapat pansinin na ang bawat cell na may linear regression formula mula sa hanay ng mga cell D4:D13 ay may isang argumentong katumbas na cell mula sa hanay na A4:A13. Katulad nito, para sa quadratic regression, punan ang hanay ng mga cell E4:E13, at para sa cubic regression, punan ang hanay ng mga cell F4:F13. Kaya, isang pagtataya para sa kita ng negosyo para sa 2003 at 2004 ay naipon. gamit ang tatlong uso. Ang resultang talahanayan ng mga halaga ay ipinapakita sa Fig. 6.
Problema 2
Bumuo ng diagram.
Magdagdag ng logarithmic, power at exponential trend lines sa chart.
Kunin ang mga equation ng nakuha na mga linya ng trend, pati na rin ang mga halaga ng pagiging maaasahan ng approximation R2 para sa bawat isa sa kanila.
Gamit ang mga equation ng trend line, kumuha ng tabular data sa kita ng enterprise para sa bawat trend line para sa 1995-2002.
Gumawa ng pagtataya ng kita ng kumpanya para sa 2003 at 2004 gamit ang mga linya ng trend na ito.
Ang solusyon sa problema
Kasunod ng pamamaraang ibinigay sa paglutas ng problema 1, nakakuha tayo ng isang diagram na may logarithmic, power at exponential trend lines na idinagdag dito (Fig. 7). Susunod, gamit ang nakuha na mga equation ng trend line, pinupunan namin ang isang talahanayan ng mga halaga para sa kita ng negosyo, kasama ang mga hinulaang halaga para sa 2003 at 2004. (Larawan 8).
Sa Fig. 5 at fig. makikita na ang modelo na may logarithmic trend ay tumutugma sa pinakamababang halaga ng approximation reliability
R2 = 0.8659
Ang pinakamataas na halaga ng R2 ay tumutugma sa mga modelong may polynomial trend: quadratic (R2 = 0.9263) at cubic (R2 = 0.933).
Suliranin 3
Gamit ang talahanayan ng data sa kita ng isang negosyo sa transportasyon ng motor para sa 1995-2002, na ibinigay sa gawain 1, dapat mong gawin ang mga sumusunod na hakbang.
Kumuha ng data series para sa linear at exponential trend lines gamit ang TREND at GROW function.
Gamit ang TREND at GROWTH function, gumawa ng forecast ng kita ng enterprise para sa 2003 at 2004.
Bumuo ng diagram para sa orihinal na data at ang resultang serye ng data.
Ang solusyon sa problema
Gamitin natin ang worksheet para sa Problema 1 (tingnan ang Fig. 4). Magsimula tayo sa TREND function:
piliin ang hanay ng mga cell D4:D11, na dapat punan ng mga halaga ng TREND function na naaayon sa kilalang data sa kita ng negosyo;
Tawagan ang utos ng Function mula sa Insert menu. Sa lalabas na dialog box ng Function Wizard, piliin ang TREND function mula sa kategoryang Statistical, at pagkatapos ay i-click ang OK na buton. Ang parehong operasyon ay maaaring isagawa sa pamamagitan ng pag-click sa (Insert Function) na buton sa karaniwang toolbar.
Sa lalabas na dialog box ng Function Arguments, ilagay ang hanay ng mga cell C4:C11 sa Known_values_y field; sa Known_values_x field - ang hanay ng mga cell B4:B11;
Upang gawing array formula ang inilagay na formula, gamitin ang key combination + + .
Ang formula na inilagay namin sa formula bar ay magiging ganito ang hitsura: =(TREND(C4:C11,B4:B11)).
Bilang resulta, ang hanay ng mga cell D4:D11 ay napuno ng kaukulang mga halaga ng TREND function (Larawan 9).
Upang gumawa ng isang pagtataya ng kita ng negosyo para sa 2003 at 2004. kailangan:
piliin ang hanay ng mga cell D12:D13 kung saan ilalagay ang mga halaga na hinulaan ng TREND function.
tawagan ang TREND function at sa lalabas na dialog box ng Function Arguments, ilagay ang hanay ng mga cell C4:C11 sa Known_values_y field; sa Known_values_x field - ang hanay ng mga cell B4:B11; at sa New_values_x na patlang - ang hanay ng mga cell B12:B13.
gawing array formula ang formula na ito gamit ang key combination na Ctrl + Shift + Enter.
Ang inilagay na formula ay magmumukhang: =(TREND(C4:C11;B4:B11;B12:B13)), at ang hanay ng mga cell D12:D13 ay mapupuno ng mga hinulaang halaga ng TREND function (tingnan ang Fig. 9).
Parehong pinupunan ang serye ng data gamit ang GROWTH function, na ginagamit sa pagsusuri ng mga nonlinear na dependency at gumagana nang eksakto sa parehong paraan tulad ng linear counterpart na TREND nito.
Ipinapakita ng Figure 10 ang talahanayan sa formula display mode.
Para sa paunang data at ang nakuhang serye ng data, ang diagram na ipinapakita sa Fig. labing-isa.
Suliranin 4
Gamit ang talahanayan ng data sa pagtanggap ng mga aplikasyon para sa mga serbisyo ng serbisyo ng pagpapadala ng isang negosyo sa transportasyon ng motor para sa panahon mula ika-1 hanggang ika-11 ng kasalukuyang buwan, dapat mong gawin ang mga sumusunod na aksyon.
Kumuha ng serye ng data para sa linear regression: gamit ang SLOPE at INTERCEPT function; gamit ang LINEST function.
Kumuha ng serye ng data para sa exponential regression gamit ang LGRFPRIBL function.
Gamit ang mga function sa itaas, gumawa ng hula tungkol sa pagtanggap ng mga aplikasyon sa serbisyo ng pagpapadala para sa panahon mula ika-12 hanggang ika-14 ng kasalukuyang buwan.
Gumawa ng diagram para sa orihinal at natanggap na serye ng data.
Ang solusyon sa problema
Tandaan na, hindi katulad ng TREND at GROWTH function, wala sa mga function na nakalista sa itaas (SLOPE, INTERCEPT, LINEST, LGRFPRIB) ang regression. Ang mga function na ito ay gumaganap lamang ng isang sumusuportang papel, na tinutukoy ang mga kinakailangang parameter ng regression.
Para sa mga linear at exponential regression na binuo gamit ang mga function na SLOPE, INTERCEPT, LINEST, LGRFPRIB, ang hitsura ng kanilang mga equation ay palaging kilala, sa kaibahan ng mga linear at exponential regression na tumutugma sa TREND at GROWTH function.
1 . Bumuo tayo ng linear regression na may equation:
y = mx+b
gamit ang SLOPE at INTERCEPT function, na ang regression slope m ay tinutukoy ng SLOPE function, at ang free term b ng INTERCEPT function.
Upang gawin ito, isinasagawa namin ang mga sumusunod na aksyon:
ipasok ang orihinal na talahanayan sa hanay ng cell A4:B14;
ang halaga ng parameter m ay matutukoy sa cell C19. Piliin ang Slope function mula sa Statistical category; ilagay ang hanay ng mga cell B4:B14 sa kilalang_values_y na field at ang hanay ng mga cell A4:A14 sa kilalang_values_x na field. Ang formula ay ilalagay sa cell C19: =SLOPE(B4:B14,A4:A14);
Gamit ang katulad na pamamaraan, tinutukoy ang halaga ng parameter b sa cell D19. At ang mga nilalaman nito ay magmumukhang: =SEGMENT(B4:B14,A4:A14). Kaya, ang mga halaga ng mga parameter m at b na kinakailangan para sa pagbuo ng isang linear regression ay maiimbak sa mga cell C19, D19, ayon sa pagkakabanggit;
Susunod, ipasok ang linear regression formula sa cell C4 sa form: =$C*A4+$D. Sa formula na ito, ang mga cell C19 at D19 ay nakasulat na may ganap na mga sanggunian (ang cell address ay hindi dapat magbago sa panahon ng posibleng pagkopya). Maaaring i-type ang absolute reference sign na $ mula sa keyboard o gamit ang F4 key, pagkatapos ilagay ang cursor sa cell address. Gamit ang fill handle, kopyahin ang formula na ito sa hanay ng mga cell C4:C17. Nakukuha namin ang kinakailangang serye ng data (Fig. 12). Dahil sa katotohanan na ang bilang ng mga kahilingan ay isang integer, dapat mong itakda ang format ng numero na may bilang ng mga decimal na lugar sa 0 sa tab na Numero ng window ng Cell Format.
2 . Ngayon, bumuo tayo ng linear regression na ibinigay ng equation:
y = mx+b
gamit ang LINEST function.
Para dito:
Ilagay ang LINEST function bilang array formula sa cell range C20:D20: =(LINEST(B4:B14,A4:A14)). Bilang resulta, nakuha namin ang halaga ng parameter m sa cell C20, at ang halaga ng parameter b sa cell D20;
ipasok ang formula sa cell D4: =$C*A4+$D;
kopyahin ang formula na ito gamit ang fill marker sa hanay ng cell D4:D17 at makuha ang gustong serye ng data.
3 . Bumubuo kami ng exponential regression na may equation:
gamit ang LGRFPRIBL function na ito ay ginaganap nang katulad:
Sa hanay ng cell C21:D21 ipinapasok namin ang function na LGRFPRIBL bilang array formula: =( LGRFPRIBL (B4:B14,A4:A14)). Sa kasong ito, ang halaga ng parameter m ay tutukuyin sa cell C21, at ang halaga ng parameter b ay tutukuyin sa cell D21;
ang formula ay ipinasok sa cell E4: =$D*$C^A4;
gamit ang fill marker, ang formula na ito ay kinokopya sa hanay ng mga cell E4:E17, kung saan matatagpuan ang serye ng data para sa exponential regression (tingnan ang Fig. 12).
Sa Fig. Ipinapakita ng Figure 13 ang isang talahanayan kung saan makikita mo ang mga function na ginagamit namin kasama ang mga kinakailangang hanay ng cell, pati na rin ang mga formula.
Magnitude R 2 tinawag koepisyent ng determinasyon.
Ang gawain ng pagbuo ng isang regression dependence ay upang mahanap ang vector ng coefficients m ng modelo (1) kung saan ang coefficient R ay tumatagal sa pinakamataas na halaga.
Upang masuri ang kahalagahan ng R, ang Fisher's F test ay ginagamit, na kinakalkula gamit ang formula
saan n- laki ng sample (bilang ng mga eksperimento);
k ay ang bilang ng mga model coefficient.
Kung ang F ay lumampas sa ilang kritikal na halaga para sa data n At k at ang tinatanggap na posibilidad ng kumpiyansa, kung gayon ang halaga ng R ay itinuturing na makabuluhan. Mga mesa kritikal na halaga Ang F ay ibinibigay sa mga sangguniang aklat sa mga istatistika ng matematika.
Kaya, ang kahalagahan ng R ay natutukoy hindi lamang sa halaga nito, kundi pati na rin sa ratio sa pagitan ng bilang ng mga eksperimento at bilang ng mga coefficient (parameter) ng modelo. Sa katunayan, ang ratio ng ugnayan para sa n=2 para sa isang simpleng linear na modelo ay katumbas ng 1 (isang solong tuwid na linya ay maaaring palaging iguguhit sa pamamagitan ng 2 puntos sa isang eroplano). Gayunpaman, kung ang pang-eksperimentong data ay mga random na variable, ang naturang halaga ng R ay dapat na pagkatiwalaan nang may matinding pag-iingat. Karaniwan, upang makakuha ng makabuluhang R at maaasahang pagbabalik, sinisikap nilang tiyakin na ang bilang ng mga eksperimento ay makabuluhang lumampas sa bilang ng mga coefficient ng modelo (n>k).
Upang bumuo ng isang linear regression model kailangan mo:
1) maghanda ng listahan ng mga n row at m column na naglalaman ng pang-eksperimentong data (column na naglalaman ng output value Y dapat mauna o huli sa listahan); Halimbawa, kunin natin ang data mula sa nakaraang gawain, pagdaragdag ng column na tinatawag na "Period No.", lagyan ng numero ang mga period number mula 1 hanggang 12. (ito ang magiging mga value X)
2) pumunta sa menu na Data/Data Analysis/Regression
Kung ang item na "Pagsusuri ng Data" sa menu na "Mga Tool" ay nawawala, dapat kang pumunta sa item na "Mga Add-In" sa parehong menu at lagyan ng check ang checkbox na "Package ng Pagsusuri."
3) sa dialog box na "Regression", itakda ang:
· pagitan ng input Y;
· pagitan ng input X;
· agwat ng output - ang itaas na kaliwang cell ng agwat kung saan ilalagay ang mga resulta ng pagkalkula (inirerekumenda na ilagay ang mga ito sa isang bagong worksheet);
4) i-click ang "Ok" at suriin ang mga resulta.
Pinakamababang parisukat na pamamaraan ginamit upang tantyahin ang mga parameter ng equation ng regression.Ang isa sa mga pamamaraan para sa pag-aaral ng mga stochastic na relasyon sa pagitan ng mga katangian ay ang pagsusuri ng regression.
Ang pagsusuri ng regression ay ang derivation ng isang regression equation, sa tulong ng kung saan ang average na halaga ng isang random variable (result attribute) ay matatagpuan kung ang halaga ng isa pa (o iba pang) variable (factor-attributes) ay kilala. Kabilang dito ang mga sumusunod na hakbang:
- pagpili ng anyo ng koneksyon (uri ng analytical regression equation);
- pagtatantya ng mga parameter ng equation;
- pagtatasa ng kalidad ng analytical regression equation.
Sa kaso ng isang linear pairwise na relasyon, ang regression equation ay kukuha ng anyo: y i =a+b·x i +u i . Ang mga parameter a at b ng equation na ito ay tinatantya mula sa data istatistikal na pagmamasid x at y. Ang resulta ng naturang pagtatasa ay ang equation: , kung saan , ay mga pagtatantya ng mga parameter a at b, ay ang halaga ng nagresultang katangian (variable) na nakuha mula sa equation ng regression (kinakalkulang halaga).
Kadalasang ginagamit upang tantyahin ang mga parameter least squares method (LSM).
Ang paraan ng least squares ay nagbibigay ng pinakamahusay (pare-pareho, mahusay, at walang pinapanigan) na mga pagtatantya ng mga parameter ng equation ng regression. Ngunit lamang kung ang ilang mga pagpapalagay ay natutugunan tungkol sa random na termino (u) at ang independiyenteng variable (x) (tingnan ang mga pagpapalagay ng OLS).
Ang problema sa pagtatantya ng mga parameter ng isang linear pair equation gamit ang least squares method ay ang mga sumusunod: upang makakuha ng mga naturang pagtatantya ng mga parameter , , kung saan ang kabuuan ng mga squared deviations ng aktwal na mga halaga ng resultang katangian - y i mula sa mga kinakalkula na halaga - ay minimal.
Pormal Pagsubok sa OLS maaaring isulat ng ganito: 
.
Pag-uuri ng mga pamamaraan ng least squares
- Pinakamababang parisukat na pamamaraan.
- Maximum na paraan ng posibilidad (para sa isang normal na klasikal na linear regression na modelo, ang normalidad ng mga nalalabi sa regression ay postulated).
- Ang pangkalahatang hindi bababa sa mga parisukat na pamamaraan ng OLS ay ginagamit sa kaso ng autocorrelation ng mga error at sa kaso ng heteroscedasticity.
- Paraan ng weighted least squares ( espesyal na kaso OLS na may heteroscedastic residual).
Ilarawan natin ang punto klasikal na pamamaraan hindi bababa sa mga parisukat sa graphic. Para magawa ito, gagawa tayo ng scatter plot batay sa observational data (x i, y i, i=1;n) sa isang rectangular coordinate system (ang naturang scatter plot ay tinatawag na correlation field). Subukan nating pumili ng isang tuwid na linya na pinakamalapit sa mga punto ng field ng ugnayan. Ayon sa paraan ng least squares, ang linya ay pinili upang ang kabuuan ng mga parisukat ng mga patayong distansya sa pagitan ng mga punto ng field ng ugnayan at linyang ito ay minimal. 
Mathematical notation para sa problemang ito: 
.
Ang mga halaga ng y i at x i =1...n ay kilala sa amin; Sa S function, kinakatawan nila ang mga constant. Ang mga variable sa function na ito ay ang mga kinakailangang pagtatantya ng mga parameter - , . Upang mahanap ang minimum ng isang function ng dalawang variable, kinakailangan upang kalkulahin ang mga partial derivatives ng function na ito para sa bawat isa sa mga parameter at i-equate ang mga ito sa zero, i.e. 
.
Bilang resulta, nakakakuha tayo ng isang sistema ng 2 normal na linear equation: 
Pagpapasya ang sistemang ito, nakita namin ang mga kinakailangang pagtatantya ng parameter: 
Ang kawastuhan ng pagkalkula ng mga parameter ng equation ng regression ay maaaring suriin sa pamamagitan ng paghahambing ng mga halaga (maaaring mayroong ilang pagkakaiba dahil sa pag-ikot ng mga kalkulasyon).
Upang kalkulahin ang mga pagtatantya ng parameter, maaari kang bumuo ng Talahanayan 1.
Ang sign ng regression coefficient b ay nagpapahiwatig ng direksyon ng relasyon (kung b>0, ang relasyon ay direkta, kung b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Sa pormal, ang halaga ng parameter a ay ang average na halaga ng y na may x na katumbas ng zero. Kung ang attribute-factor ay wala at hindi maaaring magkaroon ng zero value, kung gayon ang interpretasyon sa itaas ng parameter a ay walang saysay.
Pagtatasa ng lapit ng ugnayan sa pagitan ng mga katangian
isinasagawa gamit ang linear pair correlation coefficient - r x,y. Maaari itong kalkulahin gamit ang formula: 
. Bilang karagdagan, ang linear pair correlation coefficient ay maaaring matukoy sa pamamagitan ng regression coefficient b: 
.
Ang hanay ng mga katanggap-tanggap na halaga ng linear pair correlation coefficient ay mula -1 hanggang +1. Ang tanda ng koepisyent ng ugnayan ay nagpapahiwatig ng direksyon ng relasyon. Kung r x, y >0, kung gayon ang koneksyon ay direkta; kung r x, y<0, то связь обратная.
Kung ang koepisyent na ito ay malapit sa pagkakaisa sa magnitude, kung gayon ang ugnayan sa pagitan ng mga katangian ay maaaring bigyang-kahulugan bilang isang medyo malapit na linear. Kung ang module nito ay katumbas ng isang ê r x , y ê =1, kung gayon ang ugnayan sa pagitan ng mga katangian ay functional linear. Kung ang mga feature na x at y ay linearly independent, ang r x,y ay malapit sa 0.
Upang kalkulahin ang r x,y, maaari mo ring gamitin ang Talahanayan 1.
Talahanayan 1
| N obserbasyon | x i | y i | x i ∙y i | ||
| 1 | x 1 | y 1 | x 1 y 1 | ||
| 2 | x 2 | y 2 | x 2 y 2 | ||
| ... | |||||
| n | x n | y n | x n y n | ||
| Column Sum | ∑x | ∑y | ∑x y | ||
| Average na halaga |
 |
 |
,
kung saan ang d 2 ay ang pagkakaiba ng y na ipinaliwanag ng equation ng regression;
e 2 - nalalabi (hindi maipaliwanag ng equation ng regression) na pagkakaiba ng y;
s 2 y - kabuuang (kabuuang) pagkakaiba ng y.
Ang koepisyent ng determinasyon ay nagpapakilala sa proporsyon ng variation (dispersion) ng resultang attribute y na ipinaliwanag ng regression (at, dahil dito, factor x) sa kabuuang variation (dispersion) y. Ang koepisyent ng pagpapasiya R 2 yx ay tumatagal ng mga halaga mula 0 hanggang 1. Alinsunod dito, ang halaga 1-R 2 yx ay nagpapakilala sa proporsyon ng pagkakaiba-iba y sanhi ng impluwensya ng iba pang mga kadahilanan na hindi isinasaalang-alang sa mga error sa modelo at pagtutukoy.
Sa ipinares na linear regression, R 2 yx =r 2 yx.
