Eksperimentālo datu tuvināšana ir metode, kuras pamatā ir eksperimentāli iegūto datu aizstāšana ar analītisko funkciju, kas mezglpunktos visciešāk šķērso vai sakrīt ar sākotnējām vērtībām (dati, kas iegūti eksperimenta vai eksperimenta laikā). Pašlaik ir divi veidi, kā definēt analītisko funkciju:
Konstruējot n-pakāpju interpolācijas polinomu, kas iziet tieši caur visiem punktiem dots datu masīvs. IN šajā gadījumā aproksimējošā funkcija tiek attēlota kā: interpolācijas polinoms Lagranža formā vai interpolācijas polinoms Ņūtona formā.
Konstruējot n-pakāpju aproksimējošu polinomu, kas iziet punktu tiešā tuvumā no dotā datu masīva. Tādējādi aproksimējošā funkcija izlīdzina visus nejaušos trokšņus (vai kļūdas), kas var rasties eksperimenta laikā: eksperimenta laikā izmērītās vērtības ir atkarīgas no nejaušiem faktoriem, kas svārstās atbilstoši tiem. izlases likumi(mērījumu vai instrumentu kļūdas, neprecizitātes vai eksperimentālās kļūdas). Šajā gadījumā aproksimējošā funkcija tiek noteikta, izmantojot metodi mazākie kvadrāti.
Mazākā kvadrāta metode(angļu literatūrā Ordinary Least Squares, OLS) ir matemātiska metode, kuras pamatā ir aproksimējošas funkcijas noteikšana, kas tiek konstruēta vistuvākajā punktu tuvumā no noteikta eksperimentālo datu masīva. Sākotnējās un tuvinātās funkcijas F(x) tuvumu nosaka ar skaitlisku mēru, proti: eksperimentālo datu kvadrātu noviržu summai no aproksimējošās līknes F(x) jābūt vismazākajai.
Aproksimējošā līkne, kas izveidota, izmantojot mazāko kvadrātu metodi
Tiek izmantota mazāko kvadrātu metode:
Risināt pārdefinētas vienādojumu sistēmas, kad vienādojumu skaits pārsniedz nezināmo skaitu;
Lai rastu risinājumu parastā (nav ignorēta) gadījumā nelineāras sistēmas vienādojumi;
Lai tuvinātu punktu vērtības ar kādu tuvinātu funkciju.
Tuvinošā funkcija, izmantojot mazāko kvadrātu metodi, tiek noteikta no nosacījuma par aprēķinātās aproksimējošās funkcijas minimālās kvadrātiskās noviržu summas no dotā eksperimentālo datu masīva. Šis mazāko kvadrātu metodes kritērijs ir uzrakstīts kā šāda izteiksme:
Aprēķinātās tuvinātās funkcijas vērtības mezglu punktos,
Dotais eksperimentālo datu masīvs mezglu punktos.
Kvadrātiskajam kritērijam ir vairākas “labas” īpašības, piemēram, diferenciācija, kas nodrošina unikālu risinājumu aproksimācijas problēmai ar polinomu tuvināšanas funkcijām.
Atkarībā no uzdevuma nosacījumiem aproksimējošā funkcija ir m pakāpes polinoms
Tuvināšanas funkcijas pakāpe nav atkarīga no mezglu punktu skaita, bet tās dimensijai vienmēr jābūt mazākai par dotā eksperimentālā datu masīva dimensiju (punktu skaitu).
∙ Ja aproksimējošās funkcijas pakāpe ir m=1, tad tabulas funkciju aproksimējam ar taisni (lineārā regresija).
∙ Ja aproksimējošās funkcijas pakāpe ir m=2, tad aproksimējam tabulas funkciju kvadrātiskā parabola(kvadrātiskā tuvināšana).
∙ Ja aproksimējošās funkcijas pakāpe ir m=3, tad tabulas funkciju aproksimējam ar kubisko parabolu (kubiskā aproksimācija).
IN vispārējs gadījums kad dotajam ir jākonstruē tuvināts polinoms ar grādu m tabulas vērtības, nosacījums par minimālo noviržu kvadrātu visos mezglpunktos tiek pārrakstīts šādā formā:
- m pakāpes aproksimējošā polinoma nezināmie koeficienti;
Norādītais tabulas vērtību skaits.
Nepieciešams nosacījums funkcijas minimuma pastāvēšanai ir tās daļējo atvasinājumu vienādība ar nulli attiecībā uz nezināmiem mainīgajiem. . Rezultātā mēs iegūstam šādu sistēmu vienādojumi:
Pārveidosim iegūto lineārā sistēma vienādojumi: atveriet iekavas un pārvietojiet brīvos vārdus izteiksmes labajā pusē. Iegūtā lineārā sistēma algebriskās izteiksmes tiks rakstīts šādā formā:
Šo lineāro algebrisko izteiksmju sistēmu var pārrakstīt matricas formā:
Rezultāts bija sistēma lineārie vienādojumi dimensija m+1, kas sastāv no m+1 nezināmajiem. Šo sistēmu var atrisināt, izmantojot jebkuru lineāro problēmu risināšanas metodi. algebriskie vienādojumi(piemēram, pēc Gausa metodes). Risinājuma rezultātā tiks atrasti nezināmi aproksimēšanas funkcijas parametri, kas nodrošina minimālo aproksimējošās funkcijas noviržu kvadrātu summu no sākotnējiem datiem, t.i. labākā iespējamā kvadrātiskā tuvināšana. Jāatceras, ka, mainoties kaut vienai avota datu vērtībai, visi koeficienti mainīs savas vērtības, jo tos pilnībā nosaka avota dati.
Avota datu tuvināšana pēc lineārās atkarības
(lineārā regresija)
Kā piemēru apsveriet tuvinātās funkcijas noteikšanas paņēmienu, kas norādīts formā lineārā atkarība. Saskaņā ar mazāko kvadrātu metodi noviržu kvadrātu summas minimuma nosacījumu raksta šādā formā:
Tabulas mezglu koordinātas;
Nezināmi aproksimējošās funkcijas koeficienti, kas norādīta kā lineāra atkarība.
Nepieciešams nosacījums funkcijas minimuma pastāvēšanai ir tās daļējo atvasinājumu vienādība ar nulli attiecībā uz nezināmiem mainīgajiem. Rezultātā mēs iegūstam šādu vienādojumu sistēmu:
Pārveidosim iegūto lineāro vienādojumu sistēmu.
Mēs atrisinām iegūto lineāro vienādojumu sistēmu. Tuvinošās funkcijas koeficientus analītiskā formā nosaka šādi (Krāmera metode):
Šie koeficienti nodrošina lineāras aproksimējošas funkcijas konstruēšanu saskaņā ar kritēriju samazināt aproksimējošās funkcijas kvadrātu summu no dotajām tabulas vērtībām (eksperimentālie dati).
Algoritms mazāko kvadrātu metodes ieviešanai
1. Sākotnējie dati:
Ir norādīts eksperimentālo datu masīvs ar mērījumu skaitu N
Ir norādīta aproksimējošā polinoma pakāpe (m).
2. Aprēķinu algoritms:
2.1. Koeficientus nosaka vienādojumu sistēmas ar izmēriem konstruēšanai
Vienādojumu sistēmas koeficienti ( kreisā puse vienādojumi)
- vienādojumu sistēmas kvadrātmatricas kolonnas numura indekss
Lineāro vienādojumu sistēmas brīvie termini ( labā daļa vienādojumi)
- vienādojumu sistēmas kvadrātmatricas rindas numura indekss
2.2. Lineāru vienādojumu sistēmas ar dimensiju veidošana .
2.3. Lineāro vienādojumu sistēmas atrisināšana, lai noteiktu m pakāpes tuvinātā polinoma nezināmos koeficientus.
2.4. Tuvinošā polinoma noviržu kvadrātu summas noteikšana no sākotnējām vērtībām visos mezgla punktos
Atrastā noviržu kvadrātu summas vērtība ir minimālā iespējamā.
Tuvināšana, izmantojot citas funkcijas
Jāņem vērā, ka aproksimējot avota datus saskaņā ar mazāko kvadrātu metodi, dažkārt kā tuvinājuma funkciju izmanto logaritmisko funkciju, eksponenciālā funkcija un jaudas funkcija.
Logaritmiskā tuvināšana
Apskatīsim gadījumu, kad aproksimējošā funkcija tiek dota ar formas logaritmisko funkciju:
Mazāko kvadrātu metodes būtība ir trenda modeļa parametru atrašanā, kas vislabāk raksturo jebkuras nejaušas parādības attīstības tendenci laikā vai telpā (trends ir līnija, kas raksturo šīs attīstības tendenci). Mazāko kvadrātu metodes (LSM) uzdevums ir atrast ne tikai kādu tendenču modeli, bet arī atrast labāko vai optimālo modeli. Šis modelis būs optimāls, ja kvadrātveida noviržu summa starp novērotajām faktiskajām vērtībām un atbilstošajām aprēķinātajām tendenču vērtībām ir minimāla (mazākā):
Kur - standarta novirze starp novēroto faktisko vērtību
un atbilstošā aprēķinātā tendences vērtība,
pētāmās parādības faktiskā (novērotā) vērtība,
tendences modeļa aprēķinātā vērtība,
Pētāmās parādības novērojumu skaits.
MNC atsevišķi tiek izmantots diezgan reti. Parasti korelācijas pētījumos to izmanto tikai kā nepieciešamo tehnisko paņēmienu. Jāatceras, ka MNC informācijas bāze var būt tikai uzticama statistikas sērijas, un novērojumu skaits nedrīkst būt mazāks par 4, pretējā gadījumā OLS izlīdzināšanas procedūras var zaudēt veselo saprātu.
MNC rīku komplekts sastāv no šādām procedūrām:
Pirmā procedūra. Izrādās, vai vispār ir tendence mainīt rezultējošo atribūtu, mainoties izvēlētajam faktora argumentam, vai, citiem vārdiem sakot, vai pastāv saikne starp “ plkst " Un " X ».
Otrā procedūra. Tiek noteikts, kura līnija (trajektorija) var vislabāk raksturot vai raksturot šo tendenci.
Trešā procedūra.
Piemērs. Pieņemsim, ka mums ir informācija par vidējo saulespuķu ražu pētāmajai saimniecībai (9.1. tabula).
9.1. tabula
|
Novērošanas numurs |
||||||||||
|
Produktivitāte, c/ha |
Tā kā saulespuķu audzēšanas tehnoloģiju līmenis mūsu valstī pēdējos 10 gadus ir palicis praktiski nemainīgs, tas nozīmē, ka, acīmredzot, ražas svārstības analizētajā periodā bija ļoti atkarīgas no laika un klimatisko apstākļu svārstībām. Vai tā tiešām ir taisnība?
Pirmā OLS procedūra. Tiek pārbaudīta hipotēze par saulespuķu ražas izmaiņu tendences pastāvēšanu atkarībā no laikapstākļu un klimatisko apstākļu izmaiņām analizētajos 10 gados.
Šajā piemērā " y "Ieteicams ņemt saulespuķu ražu, un " x » – novērotā gada numurs analizētajā periodā. Pārbaudot hipotēzi par jebkādu attiecību esamību starp " x " Un " y "var izdarīt divos veidos: manuāli un izmantojot datorprogrammas. Protams, ja ir pieejams datortehnikašī problēma atrisinās pati no sevis. Bet, lai labāk izprastu MNC rīkus, ir ieteicams pārbaudīt hipotēzi par saistību starp " x " Un " y » manuāli, kad pie rokas ir tikai pildspalva un parasts kalkulators. Šādos gadījumos hipotēzi par tendences esamību vislabāk vizuāli pārbaudīt pēc analizējamās dinamikas sērijas grafiskā attēla atrašanās vietas - korelācijas lauka:
Mūsu piemērā korelācijas lauks atrodas ap lēni augošu līniju. Tas pats par sevi liecina par zināmas tendences saulespuķu ražas izmaiņās. Nevar runāt par kādas tendences esamību tikai tad, ja korelācijas lauks izskatās pēc apļa, apļa, stingri vertikāla vai stingri horizontāla mākoņa vai sastāv no haotiski izkliedētiem punktiem. Visos citos gadījumos hipotēze par saistību pastāvēšanu starp " x " Un " y ", un turpiniet izpēti.
Otrā OLS procedūra. Tiek noteikts, kura līnija (trajektorija) vislabāk var raksturot vai raksturot saulespuķu ražas izmaiņu tendenci analizētajā periodā.
Ja jums ir datortehnika, optimālās tendences izvēle notiek automātiski. Apstrādājot manuāli, izvēle optimāla funkcija parasti tiek veikts vizuāli - pēc korelācijas lauka atrašanās vietas. Tas ir, pamatojoties uz grafika veidu, tiek izvēlēts līnijas vienādojums, kas vislabāk atbilst empīriskajai tendencei (faktiskajai trajektorijai).
Kā zināms, dabā ir ļoti daudz dažādu funkcionālo atkarību, tāpēc ir ārkārtīgi grūti vizuāli analizēt pat nelielu daļu no tām. Par laimi, reālajā ekonomiskajā praksē lielāko daļu attiecību var diezgan precīzi aprakstīt vai nu ar parabolu, vai hiperbolu, vai taisni. Šajā sakarā, izmantojot “manuālo” iespēju izvēlēties labāko funkciju, varat aprobežoties tikai ar šiem trim modeļiem.
|
Hiperbola: |
||
|
|
|
Otrās kārtas parabola: 
:
Ir viegli redzēt, ka mūsu piemērā saulespuķu ražas izmaiņu tendenci analizētajos 10 gados vislabāk raksturo taisne, tāpēc regresijas vienādojums būs taisnes vienādojums.
Trešā procedūra. Tiek aprēķināti parametri regresijas vienādojums raksturojot doto līniju jeb, citiem vārdiem sakot, tiek noteikta analītiskā formula, kas apraksta labākais modelis tendence.
Regresijas vienādojuma parametru vērtību atrašana, mūsu gadījumā parametri un , ir OLS kodols. Šis process ir saistīts ar normālu vienādojumu sistēmas atrisināšanu.
(9.2)
Šo vienādojumu sistēmu var diezgan viegli atrisināt ar Gausa metodi. Atgādināsim, ka risinājuma rezultātā mūsu piemērā tiek atrastas parametru vērtības un. Tādējādi atrastajam regresijas vienādojumam būs šāda forma: 
Tam ir daudz lietojumprogrammu, jo tas ļauj aptuvenu attēlot doto funkciju ar citām vienkāršākām funkcijām. LSM var būt ārkārtīgi noderīgs novērojumu apstrādē, un to aktīvi izmanto, lai novērtētu dažus lielumus, pamatojoties uz citu mērījumu rezultātiem, kuros ir nejaušas kļūdas. Šajā rakstā jūs uzzināsit, kā programmā Excel ieviest mazāko kvadrātu aprēķinus.
Problēmas izklāsts, izmantojot konkrētu piemēru
Pieņemsim, ka ir divi rādītāji X un Y. Turklāt Y ir atkarīgs no X. Tā kā OLS mūs interesē no regresijas analīzes viedokļa (programmā Excel tās metodes tiek ieviestas, izmantojot iebūvētās funkcijas), mums nekavējoties jāpāriet pie specifiska problēma.
Tātad, lai X ir pārtikas preču veikala tirdzniecības platība kvadrātmetros, bet Y ir gada apgrozījums, kas mērīts miljonos rubļu.
Nepieciešams veikt prognozi, kāds būs veikala apgrozījums (Y), ja tam būs tā vai cita tirdzniecības platība. Acīmredzot funkcija Y = f (X) palielinās, jo hipermārkets pārdod vairāk preču nekā stends.
Daži vārdi par prognozēšanai izmantoto sākotnējo datu pareizību
Pieņemsim, ka mums ir tabula, kas izveidota, izmantojot n veikalu datus.
Saskaņā ar matemātiskā statistika, rezultāti būs vairāk vai mazāk pareizi, ja tiks pārbaudīti dati par vismaz 5-6 objektiem. Turklāt nevar izmantot “anomālus” rezultātus. Jo īpaši elitāra maza veikala apgrozījums var būt vairākas reizes lielāks nekā lielo “masmarket” klases mazumtirdzniecības vietu apgrozījums.
Metodes būtība
Tabulas datus var attēlot Dekarta plaknē punktu M 1 (x 1, y 1), ... M n (x n, y n) formā. Tagad uzdevuma risinājums tiks reducēts līdz aproksimējošas funkcijas y = f (x) izvēlei, kurai ir grafiks, kas iet pēc iespējas tuvāk punktiem M 1, M 2, .. M n.
Protams, jūs varat izmantot augstas pakāpes polinomu, taču šī opcija ir ne tikai grūti īstenojama, bet arī vienkārši nepareiza, jo tā neatspoguļos galveno tendenci, kas ir jāatklāj. Saprātīgākais risinājums ir meklēt taisni y = ax + b, kas vislabāk tuvina eksperimentālos datus jeb precīzāk, koeficientus a un b.
Precizitātes novērtējums
Ar jebkuru tuvinājumu tā precizitātes novērtēšana ir īpaši svarīga. Apzīmēsim ar e i atšķirību (novirzi) starp punkta x i funkcionālajām un eksperimentālajām vērtībām, t.i., e i = y i - f (x i).
Acīmredzot, lai novērtētu aproksimācijas precizitāti, varat izmantot noviržu summu, t.i., izvēloties taisnu līniju aptuvenai X atkarības no Y attēlojumam, jums ir jādod priekšroka tai, kurai ir mazākā vērtība summas e i visos aplūkotajos punktos. Tomēr ne viss ir tik vienkārši, jo kopā ar pozitīvām novirzēm būs arī negatīvas.
Problēmu var atrisināt, izmantojot novirzes moduļus vai to kvadrātus. Pēdējā metode ir visplašāk izmantotā. To izmanto daudzās jomās, tostarp regresijas analīzē (ieviesta programmā Excel, izmantojot divas iebūvētās funkcijas), un tā jau sen ir pierādījusi savu efektivitāti.
Mazākā kvadrāta metode
Programmā Excel, kā jūs zināt, ir iebūvēta funkcija AutoSum, kas ļauj aprēķināt visu vērtību vērtības, kas atrodas atlasītajā diapazonā. Tādējādi nekas netraucēs mums aprēķināt izteiksmes vērtību (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
IN matemātiskais apzīmējums tas izskatās:
Tā kā sākotnēji tika pieņemts lēmums tuvināt, izmantojot taisnu līniju, mums ir:
Tādējādi uzdevums ir atrast līniju, kas vislabāk raksturo specifiska atkarība lielumi X un Y, tiek aprēķināts divu mainīgo lielumu funkcijas minimums:
Lai to izdarītu, daļējie atvasinājumi attiecībā uz jaunajiem mainīgajiem a un b ir jāpielīdzina nullei un jāatrisina primitīva sistēma, kas sastāv no diviem vienādojumiem ar 2 formas nezināmajiem:
Pēc dažām vienkāršām transformācijām, ieskaitot dalīšanu ar 2 un manipulācijas ar summām, mēs iegūstam:
To atrisinot, piemēram, izmantojot Krāmera metodi, iegūstam stacionāru punktu ar noteiktiem koeficientiem a * un b *. Tas ir minimums, t.i., lai prognozētu, kāds būs veikala apgrozījums noteiktā apgabalā, ir piemērota taisne y = a * x + b *, kas ir regresijas modelis aplūkojamajam piemēram. Protams, tas neļaus atrast precīzu rezultātu, taču palīdzēs gūt priekšstatu par to, vai konkrētas zonas iegāde veikala kredītā atmaksāsies.
Kā programmā Excel ieviest mazāko kvadrātu skaitu
Programmā Excel ir funkcija vērtību aprēķināšanai, izmantojot mazāko kvadrātu. Tam ir šāda forma: “TREND” (zināmās Y vērtības; zināmās X vērtības; jaunas X vērtības; konstante). Piemērosim mūsu tabulai formulu OLS aprēķināšanai programmā Excel.
Lai to izdarītu, ievadiet zīmi “=” šūnā, kurā jāparāda aprēķina rezultāts, izmantojot mazāko kvadrātu metodi programmā Excel, un atlasiet funkciju “TREND”. Atvērtajā logā aizpildiet atbilstošos laukus, iezīmējot:
- zināmo Y vērtību diapazons (šajā gadījumā dati par tirdzniecības apgrozījumu);
- diapazons x 1 , …x n , t.i., tirdzniecības telpas lielums;
- gan slavens, gan nezināmas vērtības x, kam jānoskaidro apgrozījuma lielums (informāciju par to atrašanās vietu darblapā skatīt zemāk).
Turklāt formula satur loģisko mainīgo “Const”. Ja attiecīgajā laukā ievadāt 1, tas nozīmēs, ka jums jāveic aprēķini, pieņemot, ka b = 0.
Ja ir jānoskaidro prognoze vairāk nekā vienai x vērtībai, tad pēc formulas ievadīšanas nevajadzētu spiest “Enter”, bet gan tastatūrā jāievada kombinācija “Shift” + “Control” + “Enter”.
Dažas funkcijas
Regresijas analīze var būt pieejama pat manekeniem. Excel formulu nezināmu mainīgo masīva vērtības prognozēšanai — TREND — var izmantot pat tie, kuri nekad nav dzirdējuši par mazākajiem kvadrātiem. Pietiek tikai zināt dažas tā darba iezīmes. It īpaši:
- Ja vienā rindā vai kolonnā sakārtojat mainīgā y zināmo vērtību diapazonu, tad katra rinda (kolonna) ar zināmās vērtības x programma apstrādās kā atsevišķu mainīgo.
- Ja logā TREND nav norādīts diapazons ar zināmu x, tad, ja funkcija tiek izmantota Excel programma apstrādās to kā masīvu, kas sastāv no veseliem skaitļiem, kuru skaits atbilst diapazonam ar norādītajām mainīgā y vērtībām.
- Lai izvadītu “paredzamo” vērtību masīvu, izteiksme tendences aprēķināšanai jāievada kā masīva formula.
- Ja jaunas x vērtības nav norādītas, funkcija TREND uzskata tās par vienādām ar zināmajām. Ja tie nav norādīti, tad par argumentu tiek ņemts masīvs 1; 2; 3; 4;…, kas ir samērojams ar diapazonu ar jau norādītajiem parametriem y.
- Diapazonā, kurā ir jaunās x vērtības, ir jābūt tādām pašām vai vairākām rindām vai kolonnām kā diapazonam, kurā ir norādītās y vērtības. Citiem vārdiem sakot, tam jābūt proporcionālam neatkarīgiem mainīgajiem.
- Masīvs ar zināmām x vērtībām var saturēt vairākus mainīgos. Tomēr, ja mēs runājam tikai par vienu, tad ir nepieciešams, lai diapazoni ar dotajām x un y vērtībām būtu proporcionāli. Vairāku mainīgo gadījumā ir nepieciešams, lai diapazons ar dotajām y vērtībām ietilptu vienā kolonnā vai vienā rindā.
PROGNOZES funkcija
Ieviests, izmantojot vairākas funkcijas. Viens no tiem tiek saukts par “PREDICTION”. Tas ir līdzīgs “TREND”, t.i., sniedz aprēķinu rezultātu, izmantojot mazāko kvadrātu metodi. Tomēr tikai vienam X, kuram Y vērtība nav zināma.
Tagad jūs zināt formulas programmā Excel manekeniem, kas ļauj prognozēt konkrēta rādītāja nākotnes vērtību atbilstoši lineārai tendencei.
Piemērs.
Eksperimentālie dati par mainīgo vērtībām X Un plkst ir norādīti tabulā.
To izlīdzināšanas rezultātā tiek iegūta funkcija 
Izmantojot mazāko kvadrātu metode, tuviniet šos datus ar lineāro atkarību y=cirvis+b(atrodiet parametrus A Un b). Uzziniet, kura no divām rindām labāk (mazāko kvadrātu metodes nozīmē) saskaņo eksperimentālos datus. Izveidojiet zīmējumu.
Mazāko kvadrātu metodes (LSM) būtība.
Uzdevums ir atrast lineārās atkarības koeficientus, pie kuriem funkcionē divi mainīgie A Un b
ņem mazāko vērtību. Tas ir, dots A Un b eksperimentālo datu noviržu kvadrātā summa no atrastās taisnes būs mazākā. Šī ir visa mazāko kvadrātu metodes būtība.
Tādējādi piemēra atrisināšana ir divu mainīgo funkcijas galējības atrašana.
Formulu atvasināšana koeficientu atrašanai.
Tiek sastādīta un atrisināta divu vienādojumu sistēma ar diviem nezināmajiem. Funkcijas daļējo atvasinājumu atrašana 
pēc mainīgajiem A Un b, mēs šos atvasinājumus pielīdzinām nullei. 
Mēs atrisinām iegūto vienādojumu sistēmu, izmantojot jebkuru metodi (piemēram ar aizstāšanas metodi vai Krāmera metode) un iegūt formulas koeficientu atrašanai, izmantojot mazāko kvadrātu metodi (LSM). 
Ņemot vērā A Un b funkciju 
ņem mazāko vērtību. Šim faktam ir sniegts pierādījums zemāk tekstā lapas beigās.
Tā ir visa mazāko kvadrātu metode. Formula parametra atrašanai a satur summas ,, un parametru n- eksperimentālo datu apjoms. Mēs iesakām šo summu vērtības aprēķināt atsevišķi. Koeficients b atrasts pēc aprēķina a.
Ir pienācis laiks atcerēties sākotnējo piemēru.
Risinājums.
Mūsu piemērā n=5. Mēs aizpildām tabulu, lai ērtāk aprēķinātu summas, kas iekļautas nepieciešamo koeficientu formulās. 
Vērtības tabulas ceturtajā rindā tiek iegūtas, reizinot 2. rindas vērtības ar 3. rindas vērtībām katram skaitlim i.
Vērtības tabulas piektajā rindā tiek iegūtas, 2. rindā esošās vērtības izliekot kvadrātā katram skaitlim i.
Vērtības tabulas pēdējā kolonnā ir vērtību summas visās rindās.
Koeficientu atrašanai izmantojam mazāko kvadrātu metodes formulas A Un b. Mēs tajās aizstājam atbilstošās vērtības no tabulas pēdējās kolonnas: 
Tāpēc y = 0,165x+2,184- vēlamā aptuvenā taisne.
Atliek noskaidrot, kura no līnijām y = 0,165x+2,184 vai 
labāk tuvina sākotnējos datus, tas ir, veic aplēses, izmantojot mazāko kvadrātu metodi.
Mazāko kvadrātu metodes kļūdu novērtējums.
Lai to izdarītu, jums jāaprēķina sākotnējo datu noviržu kvadrātā summa no šīm līnijām 
Un 
, mazāka vērtība atbilst līnijai, kas labāk tuvina sākotnējos datus mazāko kvadrātu metodes izpratnē. 
Kopš , tad taisni y = 0,165x+2,184 labāk tuvina sākotnējos datus.
Mazāko kvadrātu (LS) metodes grafiskā ilustrācija.
Grafikos viss ir skaidri redzams. Sarkanā līnija ir atrastā taisne y = 0,165x+2,184, zilā līnija ir 
, rozā punktiņi ir sākotnējie dati.
Praksē, modelējot dažādus procesus - jo īpaši ekonomiskos, fiziskos, tehniskos, sociālos - tiek plaši izmantota viena vai cita metode, kā aprēķināt funkciju aptuvenās vērtības no to zināmajām vērtībām noteiktos fiksētos punktos.
Šāda veida funkciju tuvināšanas problēma bieži rodas:
veidojot aptuvenas formulas pētāmā procesa raksturīgo lielumu vērtību aprēķināšanai, izmantojot eksperimenta rezultātā iegūtos tabulas datus;
skaitliskā integrācijā, diferencēšanā, risināšanā diferenciālvienādojumi utt.;
ja nepieciešams, aprēķina funkciju vērtības aplūkotā intervāla starppunktos;
nosakot procesa raksturīgo lielumu vērtības ārpus aplūkotā intervāla, jo īpaši prognozējot.
Ja, lai modelētu noteiktu procesu, kas norādīts tabulā, mēs konstruēsim funkciju, kas aptuveni apraksta šo procesu, pamatojoties uz mazāko kvadrātu metodi, to sauks par aproksimējošu funkciju (regresiju), bet pašu aproksimējošu funkciju konstruēšanas uzdevumu. tuvināšanas problēma.
Šajā rakstā ir apskatītas MS Excel pakotnes iespējas šāda veida problēmu risināšanai, turklāt tajā ir sniegtas metodes un paņēmieni regresiju konstruēšanai (izveidošanai) tabulētām funkcijām (kas ir regresijas analīzes pamatā).
Programmā Excel ir divas regresijas veidošanas iespējas.
Pievienojot atlasītās regresijas ( tendenču līnijas- tendenču līnijas) diagrammā, kas veidota, pamatojoties uz pētāmā procesa raksturojuma datu tabulu (pieejama tikai tad, ja ir izveidota diagramma);
Izmantojot Excel darblapas iebūvētās statistikas funkcijas, kas ļauj iegūt regresijas (tendenču līnijas) tieši no avota datu tabulas.
Tendenču līniju pievienošana diagrammai
Datu tabulai, kas apraksta procesu un ir attēlota diagrammā, programmā Excel ir efektīvs regresijas analīzes rīks, kas ļauj:
veidot, pamatojoties uz mazāko kvadrātu metodi, un pievienot diagrammai piecu veidu regresijas, kas modelē pētāmo procesu ar dažādu precizitātes pakāpi;
pievienot diagrammai konstruēto regresijas vienādojumu;
noteikt atlasītās regresijas atbilstības pakāpi diagrammā parādītajiem datiem.
Pamatojoties uz diagrammas datiem, programma Excel ļauj iegūt lineāras, polinomiskas, logaritmiskas, jaudas, eksponenciālas regresijas, kuras nosaka vienādojums:
y = y(x)
kur x ir neatkarīgs mainīgais, kas bieži vien ņem naturālu skaitļu virknes vērtības (1; 2; 3; ...) un rada, piemēram, pētāmā procesa laika atpakaļskaitīšanu (raksturības).
1 . Lineārā regresija ir laba, lai modelētu raksturlielumus, kuru vērtības palielinās vai samazinās nemainīgā ātrumā. Šis ir vienkāršākais modelis pētāmajam procesam. Tas ir izveidots saskaņā ar vienādojumu:
y = mx + b
kur m ir slīpuma leņķa pieskare lineārā regresija uz abscisu asi; b - lineārās regresijas krustošanās punkta koordināte ar ordinātu asi.
2 . Polinoma tendenču līnija ir noderīga, lai aprakstītu raksturlielumus, kuriem ir vairākas atšķirīgas galējības (maksimums un minimums). Polinoma pakāpes izvēli nosaka pētāmā raksturlieluma ekstrēmu skaits. Tādējādi otrās pakāpes polinoms var labi aprakstīt procesu, kuram ir tikai viens maksimums vai minimums; trešās pakāpes polinoms - ne vairāk kā divas galējības; ceturtās pakāpes polinoms - ne vairāk kā trīs ekstrēmas utt.
Šajā gadījumā tendences līnija tiek veidota saskaņā ar vienādojumu:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
kur koeficienti c0, c1, c2,...c6 ir konstantes, kuru vērtības nosaka būvniecības laikā.
3 . Logaritmiskā tendences līnija tiek veiksmīgi izmantota, modelējot raksturlielumus, kuru vērtības sākotnēji strauji mainās un pēc tam pakāpeniski stabilizējas.
y = c ln(x) + b
4 . Jaudas likuma tendences līnija dod labus rezultātus, ja pētāmo attiecību vērtības raksturo pastāvīgas pieauguma ātruma izmaiņas. Šādas atkarības piemērs ir automašīnas vienmērīgi paātrinātas kustības grafiks. Ja datos ir nulle vai negatīvas vērtības, jūs nevarat izmantot jaudas tendenču līniju.
Izgatavots saskaņā ar vienādojumu:
y = c xb
kur koeficienti b, c ir konstantes.
5 . Ja datu izmaiņu ātrums nepārtraukti pieaug, ir jāizmanto eksponenciāla tendences līnija. Datiem, kas satur nulles vai negatīvas vērtības, arī šāda veida tuvināšana nav piemērojama.
Izgatavots saskaņā ar vienādojumu:
y = c ebx
kur koeficienti b, c ir konstantes.
Izvēloties tendences līniju, programma Excel automātiski aprēķina R2 vērtību, kas raksturo tuvinājuma ticamību: nekā tuvāka vērtība R2 līdz vienotībai, jo ticamāk tendences līnija tuvina pētāmo procesu. Ja nepieciešams, R2 vērtību vienmēr var parādīt diagrammā.
Nosaka pēc formulas:
Lai datu sērijai pievienotu tendences līniju:
aktivizējiet diagrammu, pamatojoties uz datu sēriju, t.i., noklikšķiniet diagrammas apgabalā. Galvenajā izvēlnē parādīsies vienums Diagramma;
pēc noklikšķināšanas uz šī vienuma ekrānā parādīsies izvēlne, kurā jāizvēlas komanda Pievienot tendences līniju.
Tās pašas darbības var viegli īstenot, pārvietojot peles rādītāju virs diagrammas, kas atbilst vienai no datu sērijām, un ar peles labo pogu noklikšķinot; Parādītajā konteksta izvēlnē atlasiet komandu Pievienot tendences līniju. Ekrānā parādīsies dialoglodziņš Trendline ar atvērtu cilni Tips (1. att.).
Pēc tam jums ir nepieciešams:
Cilnē Tips atlasiet vajadzīgo tendenču līnijas veidu (pēc noklusējuma ir atlasīts Lineārais veids). Polinoma tipam laukā Degree norādiet atlasītā polinoma pakāpi.
1 . Laukā Built on series ir norādītas visas attiecīgās diagrammas datu sērijas. Lai pievienotu tendences līniju noteiktai datu sērijai, atlasiet tās nosaukumu laukā Built on series.
Ja nepieciešams, dodoties uz cilni Parametri (2. att.), trenda līnijai var iestatīt šādus parametrus:
mainiet tendences līnijas nosaukumu laukā Aproksimējošās (izlīdzinātās) līknes nosaukums.
iestatiet periodu skaitu (uz priekšu vai atpakaļ) prognozei laukā Prognoze;
diagrammas apgabalā attēlo trenda līnijas vienādojumu, kuram ir jāiespējo izvēles rūtiņa rādīt vienādojumu diagrammā;
diagrammas apgabalā attēlo tuvinājuma ticamības vērtību R2, kurai jāiespējo izvēles rūtiņa Novietot aproksimācijas ticamības vērtību diagrammā (R^2);
iestatiet tendences līnijas krustošanās punktu ar Y asi, kuram jāiespējo izvēles rūtiņa līknes krustojumam ar Y asi punktā;
Noklikšķiniet uz pogas Labi, lai aizvērtu dialoglodziņu.
Lai sāktu rediģēt jau novilktu tendenču līniju, ir trīs veidi:
izvēlnē Format izmantojiet komandu Selected trend line, iepriekš atlasot tendences līniju;
konteksta izvēlnē atlasiet komandu Format trend line, kas tiek izsaukta, ar peles labo pogu noklikšķinot uz tendences līnijas;
veiciet dubultklikšķi uz tendenču līnijas.
Ekrānā parādīsies dialoglodziņš Trend Line Format (3. att.), kurā ir trīs cilnes: View, Type, Parameters, un pēdējo divu saturs pilnībā sakrīt ar līdzīgām cilnēm Trend Line dialoglodziņā (1. att.). -2). Cilnē Skats varat iestatīt līnijas veidu, krāsu un biezumu.
Lai izdzēstu jau uzzīmētu tendenču līniju, atlasiet dzēšamo tendenču līniju un nospiediet taustiņu Dzēst.
Aplūkotā regresijas analīzes rīka priekšrocības ir:
relatīvā vienkāršība diagrammās izveidot tendenču līniju, neveidojot tai datu tabulu;
diezgan plašs piedāvāto tendenču līniju veidu saraksts, un šajā sarakstā ir iekļauti visbiežāk izmantotie regresijas veidi;
spēja paredzēt pētāmā procesa uzvedību ar patvaļīgu (veselā saprāta robežās) soļu skaitu uz priekšu un arī atpakaļ;
spēja iegūt tendenču līnijas vienādojumu analītiskā formā;
iespēja, ja nepieciešams, iegūt tuvinājuma ticamības novērtējumu.
Trūkumi ir šādi:
tendenču līnijas veidošana tiek veikta tikai tad, ja ir diagramma, kas veidota uz datu sērijas;
pētāmā raksturlieluma datu rindu ģenerēšanas process, pamatojoties uz tam iegūtajiem tendenču līnijas vienādojumiem, ir nedaudz pārblīvēts: nepieciešamie regresijas vienādojumi tiek atjaunināti ar katru sākotnējo datu sērijas vērtību izmaiņu, bet tikai diagrammas apgabalā. , kamēr datu sērijas, kas ģenerēts, pamatojoties uz veco tendenču līnijas vienādojumu, paliek nemainīgs;
Rakurdiagrammas pārskatos mainot diagrammas vai saistītā rakurstabulas pārskata skatu, netiek saglabātas esošās tendenču līnijas, kas nozīmē, ka pirms tendenču līniju zīmēšanas vai citāda rakurdiagrammas pārskata formatēšanas ir jāpārliecinās, vai pārskata izkārtojums atbilst nepieciešamajām prasībām.
Tendenču līnijas var izmantot, lai papildinātu datu sērijas, kas parādītas diagrammās, piemēram, diagrammās, histogrammās, plakanās nestandartizētās apgabalu diagrammās, joslu diagrammās, izkliedes diagrammās, burbuļu diagrammās un akciju diagrammās.
Jūs nevarat pievienot tendenču līnijas datu sērijām 3D, normalizētajās, radara, sektoru un virtuļu diagrammās.
Programmā Excel iebūvēto funkciju izmantošana
Programmā Excel ir arī regresijas analīzes rīks tendenču līniju attēlošanai ārpus diagrammas apgabala. Šim nolūkam varat izmantot vairākas statistikas darblapas funkcijas, taču tās visas ļauj veidot tikai lineāras vai eksponenciālas regresijas.
Programmai Excel ir vairākas funkcijas lineārās regresijas konstruēšanai, jo īpaši:
SLĪPĒT un IZgriezt.
TREND;
Kā arī vairākas funkcijas eksponenciālas tendenču līnijas izveidošanai, jo īpaši:
LGRFPRIBL.
Jāatzīmē, ka regresijas konstruēšanas metodes, izmantojot funkcijas TREND un GROWTH, ir gandrīz vienādas. To pašu var teikt par funkciju pāri LINEST un LGRFPRIBL. Šīm četrām funkcijām, veidojot vērtību tabulu, tiek izmantoti Excel līdzekļi, piemēram, masīvu formulas, kas nedaudz pārblīvē regresijas veidošanas procesu. Ņemsim vērā arī to, ka lineārās regresijas konstruēšanu, mūsuprāt, visvieglāk var veikt, izmantojot SLOPE un INTERCEPT funkcijas, kur pirmā no tām nosaka lineārās regresijas slīpumu, bet otrā nosaka segmentu, ko pārtver regresija y ass.
Iebūvētā funkciju rīka priekšrocības regresijas analīzei ir šādas:
diezgan vienkāršs, vienots process pētāmā raksturlieluma datu sēriju ģenerēšanai visām iebūvētajām statistikas funkcijām, kas nosaka tendenču līnijas;
standarta metodika tendenču līniju konstruēšanai, pamatojoties uz ģenerētajām datu sērijām;
spēja paredzēt pētāmā procesa uzvedību ar nepieciešamo soļu skaitu uz priekšu vai atpakaļ.
Trūkumi ietver faktu, ka programmā Excel nav iebūvētu funkciju cita veida (izņemot lineāro un eksponenciālo) tendenču līniju izveidošanai. Šis apstāklis nereti neļauj izvēlēties pietiekami precīzu pētāmā procesa modeli, kā arī iegūt īstenībai tuvas prognozes. Turklāt, izmantojot funkcijas TREND un GROWTH, nav zināmi tendenču līniju vienādojumi.
Jāatzīmē, ka autori nav izvirzījuši mērķi sniegt regresijas analīzes gaitu ar jebkādu pilnīgumu. Tās galvenais uzdevums ir, izmantojot konkrētus piemērus, parādīt Excel pakotnes iespējas, risinot aproksimācijas uzdevumus; parādīt, kādi efektīvi rīki Excel ir regresijas veidošanai un prognozēšanai; ilustrējiet, kā šādas problēmas var samērā viegli atrisināt pat lietotājs, kuram nav plašu zināšanu par regresijas analīzi.
Konkrētu problēmu risināšanas piemēri
Apskatīsim konkrētu problēmu risināšanu, izmantojot uzskaitītos Excel rīkus.
1. problēma
Ar autotransporta uzņēmuma peļņas datu tabulu 1995.-2002.gadam. jums ir jāveic šādas darbības:
Izveidojiet diagrammu.
Pievienojiet diagrammai lineāras un polinomiskas (kvadrātiskās un kubiskās) tendenču līnijas.
Izmantojot tendenču līnijas vienādojumus, iegūstiet tabulas datus par uzņēmuma peļņu katrai tendences līnijai laika posmā no 1995. līdz 2004. gadam.
Sastādiet uzņēmuma peļņas prognozi 2003. un 2004. gadam.
Problēmas risinājums
Excel darblapas šūnu diapazonā A4:C11 ievadiet darblapu, kas parādīta attēlā. 4.
Izvēloties šūnu diapazonu B4:C11, mēs veidojam diagrammu.
Aktivizējam konstruēto diagrammu un saskaņā ar iepriekš aprakstīto metodi pēc trenda līnijas veida izvēles Trend Line dialoglodziņā (skat. 1. att.) pamīšus pievienojam diagrammai lineārās, kvadrātiskās un kubiskās tendences līnijas. Tajā pašā dialoglodziņā atveriet cilni Parametri (skat. 2. att.), laukā Name of the approximating (izlīdzinātās) līknes nosaukums ievadiet pievienojamās tendences nosaukumu un laukā Forecast forward for: periods iestatiet vērtība 2, jo plānots veikt peļņas prognozi diviem gadiem uz priekšu. Lai diagrammas apgabalā parādītu regresijas vienādojumu un aproksimācijas ticamības vērtību R2, iespējojiet vienādojuma rādīšanas izvēles rūtiņas un ievietojiet diagrammā aproksimācijas ticamības vērtību (R^2). Labākai vizuālajai uztverei mainām konstruēto tendenču līniju veidu, krāsu un biezumu, kam izmantojam dialoglodziņa Trend Line Format cilni View (skat. 3. att.). Iegūtā diagramma ar pievienotajām tendenču līnijām ir parādīta attēlā. 5.
Iegūt tabulas datus par uzņēmumu peļņu katrai tendences līnijai 1995.-2004. Izmantosim tendenču līnijas vienādojumus, kas parādīti attēlā. 5. Lai to izdarītu, diapazona D3:F3 šūnās ievadiet teksta informāciju par izvēlētās tendences līnijas veidu: Lineārā tendence, Kvadrātiskā tendence, Kubiskā tendence. Pēc tam ievadiet lineārās regresijas formulu šūnā D4 un, izmantojot aizpildīšanas marķieri, kopējiet šo formulu ar relatīvām atsaucēm uz šūnu diapazonu D5:D13. Jāņem vērā, ka katrai šūnai ar lineārās regresijas formulu no šūnu diapazona D4:D13 kā arguments ir atbilstoša šūna no diapazona A4:A13. Līdzīgi kvadrātiskās regresijas gadījumā aizpildiet šūnu diapazonu E4:E13, bet kubiskās regresijas gadījumā aizpildiet šūnu diapazonu F4:F13. Tādējādi ir sastādīta uzņēmuma peļņas prognoze 2003. un 2004. gadam. izmantojot trīs tendences. Iegūtā vērtību tabula ir parādīta attēlā. 6.
2. problēma
Izveidojiet diagrammu.
Pievienojiet diagrammai logaritmiskās, jaudas un eksponenciālās tendenču līnijas.
Atvasiniet iegūto tendenču līniju vienādojumus, kā arī katrai no tām tuvinājuma R2 ticamības vērtības.
Izmantojot tendenču līnijas vienādojumus, iegūstiet tabulas datus par uzņēmuma peļņu katrai tendences līnijai laika posmā no 1995. līdz 2002. gadam.
Izmantojot šīs tendenču līnijas, izveidojiet uzņēmuma peļņas prognozi 2003. un 2004. gadam.
Problēmas risinājums
Ievērojot 1. uzdevuma risināšanā doto metodiku, iegūstam diagrammu, kurai pievienotas logaritmiskās, jaudas un eksponenciālās tendences līnijas (7. att.). Tālāk, izmantojot iegūtos tendenču līnijas vienādojumus, aizpildām uzņēmuma peļņas vērtību tabulu, iekļaujot prognozētās vērtības 2003. un 2004. gadam. (8. att.).
Attēlā 5 un att. redzams, ka modelis ar logaritmisko tendenci atbilst zemākajai aproksimācijas ticamības vērtībai
R2 = 0,8659
Augstākās R2 vērtības atbilst modeļiem ar polinoma tendenci: kvadrātiskais (R2 = 0,9263) un kubiskais (R2 = 0,933).
3. problēma
Izmantojot 1. uzdevumā doto autotransporta uzņēmuma peļņas datu tabulu par 1995.-2002.gadu, jāveic šādas darbības.
Iegūstiet datu sērijas lineārām un eksponenciālām tendenču līnijām, izmantojot funkcijas TREND un GROW.
Izmantojot funkcijas TREND un GROWTH, sastādiet uzņēmuma peļņas prognozi 2003. un 2004. gadam.
Izveidojiet diagrammu sākotnējiem datiem un iegūtajām datu sērijām.
Problēmas risinājums
Izmantosim darba lapu 1. uzdevumam (skat. 4. att.). Sāksim ar funkciju TREND:
atlasiet šūnu diapazonu D4:D11, kas jāaizpilda ar funkcijas TREND vērtībām, kas atbilst zināmajiem datiem par uzņēmuma peļņu;
Izvēlnē Ievietot izsauciet komandu Funkcija. Parādītajā dialoglodziņā Funkciju vednis no kategorijas Statistika atlasiet funkciju TREND un pēc tam noklikšķiniet uz pogas Labi. To pašu darbību var veikt, noklikšķinot uz pogas (Ievietot funkciju) standarta rīkjoslā.
Parādītajā dialoglodziņā Function Arguments laukā Known_values_y ievadiet šūnu diapazonu C4:C11; laukā Known_values_x - šūnu diapazons B4:B11;
Lai ievadītā formula kļūtu par masīva formulu, izmantojiet taustiņu kombināciju + + .
Formula, ko ievadījām formulu joslā, izskatīsies šādi: =(TREND(C4:C11,B4:B11)).
Rezultātā šūnu diapazons D4:D11 tiek aizpildīts ar atbilstošām funkcijas TREND vērtībām (9. att.).
Sastādīt uzņēmuma peļņas prognozi 2003. un 2004. gadam. nepieciešams:
atlasiet šūnu diapazonu D12:D13, kur tiks ievadītas funkcijas TREND paredzētās vērtības.
izsauciet funkciju TREND un dialoglodziņā Function Arguments, kas parādās, laukā Known_values_y ievadiet - šūnu diapazonu C4:C11; laukā Known_values_x - šūnu diapazons B4:B11; un laukā New_values_x - šūnu diapazons B12:B13.
pārvērst šo formulu par masīva formulu, izmantojot taustiņu kombināciju Ctrl + Shift + Enter.
Ievadītā formula izskatīsies šādi: =(TREND(C4:C11;B4:B11;B12:B13)), un šūnu diapazons D12:D13 tiks aizpildīts ar prognozētajām funkcijas TREND vērtībām (sk. 9).
Datu rindas līdzīgi aizpilda, izmantojot funkciju GROWTH, kas tiek izmantota nelineāro atkarību analīzē un darbojas tieši tāpat kā tās lineārā līdziniece TREND.
10. attēlā parādīta tabula formulas displeja režīmā.
Sākotnējiem datiem un iegūtajām datu sērijām diagramma, kas parādīta attēlā. vienpadsmit.
4. problēma
Ar autotransporta uzņēmuma dispečerdienesta pakalpojumu pieteikumu saņemšanas datu tabulu par laika posmu no kārtējā mēneša 1. līdz 11. datumam jāveic šādas darbības.
Datu sērijas iegūšana lineārajai regresijai: izmantojot funkcijas SLOPE un INTERCEPT; izmantojot funkciju LINEST.
Iegūstiet datu sēriju eksponenciālai regresijai, izmantojot funkciju LGRFPRIBL.
Izmantojot augstāk minētās funkcijas, sastādīt prognozi par pieteikumu saņemšanu dispečerdienestā laika posmam no kārtējā mēneša 12. līdz 14. datumam.
Izveidojiet diagrammu sākotnējām un saņemtajām datu sērijām.
Problēmas risinājums
Ņemiet vērā, ka atšķirībā no funkcijām TREND un GROWTH neviena no iepriekš minētajām funkcijām (SLOPE, INTERCEPT, LINEST, LGRFPRIB) nav regresija. Šīs funkcijas spēlē tikai atbalsta lomu, nosakot nepieciešamos regresijas parametrus.
Lineārām un eksponenciālām regresijām, kas veidotas, izmantojot funkcijas SLOPE, INTERCEPT, LINEST, LGRFPRIB, to vienādojumu izskats vienmēr ir zināms, atšķirībā no lineārajām un eksponenciālajām regresijām, kas atbilst funkcijām TREND un GROWTH.
1 . Izveidosim lineāro regresiju ar vienādojumu:
y = mx+b
izmantojot funkcijas SLOPE un INTERCEPT, regresijas slīpumu m nosaka funkcija SLOPE, bet brīvo terminu b nosaka funkcija INTERCEPT.
Lai to izdarītu, mēs veicam šādas darbības:
ievadiet sākotnējo tabulu šūnu diapazonā A4:B14;
parametra m vērtība tiks noteikta šūnā C19. Izvēlieties funkciju Slope no kategorijas Statistical; ievadiet šūnu diapazonu B4:B14 laukā zināmās_vērtības_y un šūnu diapazonu A4:A14 laukā zināmās_vērtības_x. Formula tiks ievadīta šūnā C19: =SLOPE(B4:B14,A4:A14);
Izmantojot līdzīgu paņēmienu, tiek noteikta parametra b vērtība šūnā D19. Un tā saturs izskatīsies šādi: =SEGMENTS(B4:B14,A4:A14). Tādējādi lineārās regresijas konstruēšanai nepieciešamo parametru m un b vērtības tiks saglabātas attiecīgi šūnās C19, D19;
Pēc tam šūnā C4 ievadiet lineārās regresijas formulu šādā formā: =$C*A4+$D. Šajā formulā šūnas C19 un D19 ir rakstītas ar absolūtām atsaucēm (iespējamās kopēšanas laikā šūnas adrese nedrīkst mainīties). Absolūto atsauces zīmi $ var ierakstīt vai nu no tastatūras, vai izmantojot taustiņu F4, pēc kursora novietošanas uz šūnas adreses. Izmantojot aizpildīšanas turi, kopējiet šo formulu šūnu diapazonā C4:C17. Iegūstam nepieciešamās datu rindas (12. att.). Tā kā pieprasījumu skaits ir vesels skaitlis, loga Šūnu formāts cilnē Skaitlis ir jāiestata skaitļu formāts ar decimāldaļu skaitu uz 0.
2 . Tagad izveidosim lineāro regresiju, ko dod vienādojums:
y = mx+b
izmantojot funkciju LINEST.
Priekš šī:
Ievadiet funkciju LINEST kā masīva formulu šūnu diapazonā C20:D20: =(LINEST(B4:B14,A4:A14)). Rezultātā iegūstam parametra m vērtību šūnā C20, bet parametra b vērtību šūnā D20;
ievadiet formulu šūnā D4: =$C*A4+$D;
kopējiet šo formulu, izmantojot aizpildīšanas marķieri, šūnu diapazonā D4:D17 un iegūstiet vajadzīgo datu sēriju.
3 . Mēs veidojam eksponenciālu regresiju ar vienādojumu:
izmantojot LGRFPRIBL funkciju, tas tiek veikts līdzīgi:
Šūnu diapazonā C21:D21 mēs ievadām funkciju LGRFPRIBL kā masīva formulu: =( LGRFPRIBL (B4:B14,A4:A14)). Šajā gadījumā parametra m vērtība tiks noteikta šūnā C21, bet parametra b vērtība tiks noteikta šūnā D21;
formulu ievada šūnā E4: =$D*$C^A4;
izmantojot aizpildījuma marķieri, šī formula tiek kopēta šūnu diapazonā E4:E17, kur atradīsies eksponenciālās regresijas datu rindas (sk. 12. att.).
Attēlā 13. attēlā ir parādīta tabula, kurā var redzēt funkcijas, kuras mēs izmantojam ar nepieciešamajiem šūnu diapazoniem, kā arī formulas.
Lielums R 2 sauca determinācijas koeficients.
Regresijas atkarības konstruēšanas uzdevums ir atrast (1) modeļa koeficientu m vektoru, pie kura koeficients R iegūst maksimālo vērtību.
Lai novērtētu R nozīmīgumu, tiek izmantots Fišera F tests, kas aprēķināts, izmantojot formulu
Kur n- izlases lielums (eksperimentu skaits);
k ir modeļa koeficientu skaits.
Ja F pārsniedz kādu datu kritisko vērtību n Un k un pieņemto ticamības varbūtību, tad R vērtība tiek uzskatīta par nozīmīgu. Tabulas kritiskās vērtības F ir doti matemātiskās statistikas uzziņu grāmatās.
Tādējādi R nozīmīgumu nosaka ne tikai tā vērtība, bet arī attiecība starp eksperimentu skaitu un modeļa koeficientu (parametru) skaitu. Patiešām, korelācijas koeficients n = 2 vienkāršam lineāram modelim ir vienāds ar 1 (vienu taisnu līniju vienmēr var novilkt caur 2 punktiem plaknē). Tomēr, ja eksperimentālie dati ir nejauši mainīgie, šādai R vērtībai vajadzētu uzticēties ļoti piesardzīgi. Parasti, lai iegūtu nozīmīgu R un ticamu regresiju, viņi cenšas nodrošināt, lai eksperimentu skaits ievērojami pārsniegtu modeļa koeficientu skaitu (n>k).
Lai izveidotu lineārās regresijas modeli, jums ir nepieciešams:
1) sagatavojiet n rindu un m kolonnu sarakstu, kas satur eksperimentālos datus (kolonna, kurā ir izvades vērtība Y jābūt pirmajam vai pēdējam sarakstā); Piemēram, ņemsim datus no iepriekšējā uzdevuma, pievienojot kolonnu ar nosaukumu "Perioda Nr.", numurējiet perioda skaitļus no 1 līdz 12. (tās būs vērtības X)
2) dodieties uz izvēlni Dati/Datu analīze/Regresija
Ja izvēlnē "Rīki" trūkst vienuma "Datu analīze", tad tajā pašā izvēlnē dodieties uz vienumu "Pievienojumi" un atzīmējiet izvēles rūtiņu "Analīzes pakotne".
3) dialoglodziņā "Regresija" iestatiet:
· ievades intervāls Y;
· ievades intervāls X;
· izvades intervāls - tā intervāla augšējā kreisā šūna, kurā tiks ievietoti aprēķinu rezultāti (ieteicams tos ievietot jaunā darblapā);
4) noklikšķiniet uz "Ok" un analizējiet rezultātus.
Mazākā kvadrāta metode izmanto, lai novērtētu regresijas vienādojuma parametrus.Viena no metodēm stohastisko attiecību starp raksturlielumiem pētīšanai ir regresijas analīze.
Regresijas analīze ir regresijas vienādojuma atvasināšana, ar kuras palīdzību tiek atrasta nejauša lieluma (rezultāta atribūta) vidējā vērtība, ja ir zināma cita (vai cita) mainīgā (faktora atribūtu) vērtība. Tas ietver šādas darbības:
- savienojuma formas izvēle (analītiskās regresijas vienādojuma veids);
- vienādojuma parametru novērtēšana;
- analītiskās regresijas vienādojuma kvalitātes novērtējums.
Lineāras pāru attiecības gadījumā regresijas vienādojums būs šāds: y i =a+b·x i +u i . Šī vienādojuma parametri a un b ir novērtēti no datiem statistiskais novērojums x un y. Šāda novērtējuma rezultāts ir vienādojums: , kur , ir parametru a un b aprēķini, ir iegūtā atribūta (mainīgā) vērtība, kas iegūta no regresijas vienādojuma (aprēķinātā vērtība).
Visbiežāk izmanto parametru novērtēšanai mazāko kvadrātu metode (LSM).
Mazāko kvadrātu metode nodrošina labākos (konsekventus, efektīvus un objektīvus) regresijas vienādojuma parametru aprēķinus. Bet tikai tad, ja ir izpildīti noteikti pieņēmumi attiecībā uz nejaušo terminu (u) un neatkarīgo mainīgo (x) (sk. OLS pieņēmumus).
Lineāra pāra vienādojuma parametru novērtēšanas problēma, izmantojot mazāko kvadrātu metodi ir šāds: lai iegūtu tādus parametru aprēķinus , , pie kuriem rezultējošā raksturlieluma faktisko vērtību - y i - noviržu kvadrātu summa no aprēķinātajām vērtībām ir minimāla.
Formāli OLS tests var uzrakstīt šādi: 
.
Mazāko kvadrātu metožu klasifikācija
- Mazākā kvadrāta metode.
- Maksimālās varbūtības metode (normālam klasiskajam lineārās regresijas modelim tiek postulēta regresijas atlikuma normalitāte).
- Vispārinātā mazāko kvadrātu OLS metode tiek izmantota kļūdu autokorelācijas gadījumā un heteroskedastiskuma gadījumā.
- Svērto mazāko kvadrātu metode ( īpašs gadījums OLS ar heteroskedastiskiem atlikumiem).
Ilustrēsim būtību klasiskā metode mazākie kvadrāti grafiski. Lai to izdarītu, uz novērojumu datiem (x i, y i, i=1;n) konstruēsim izkliedes diagrammu taisnstūra koordinātu sistēmā (šādu izkliedes grafiku sauc par korelācijas lauku). Mēģināsim atlasīt taisnu līniju, kas ir vistuvāk korelācijas lauka punktiem. Saskaņā ar mazāko kvadrātu metodi līniju izvēlas tā, lai vertikālo attālumu kvadrātu summa starp korelācijas lauka punktiem un šo taisni būtu minimāla. 
Šīs problēmas matemātiskais apzīmējums: 
.
Mums ir zināmas y i un x i =1...n vērtības, tie ir novērojumu dati. S funkcijā tie apzīmē konstantes. Mainīgie šajā funkcijā ir nepieciešamie parametru aprēķini - , . Lai atrastu divu mainīgo funkcijas minimumu, ir jāaprēķina šīs funkcijas daļējie atvasinājumi katram no parametriem un jāpielīdzina nullei, t.i. 
.
Rezultātā mēs iegūstam 2 normālu lineāru vienādojumu sistēmu: 
Lemjot šī sistēma, mēs atrodam nepieciešamos parametru aprēķinus: 
Regresijas vienādojuma parametru aprēķina pareizību var pārbaudīt, salīdzinot summas (var būt zināma neatbilstība aprēķinu noapaļošanas dēļ).
Lai aprēķinātu parametru aplēses, varat izveidot 1. tabulu.
Regresijas koeficienta b zīme norāda attiecības virzienu (ja b >0, saistība ir tieša, ja b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formāli parametra a vērtība ir y vidējā vērtība ar x vienādu ar nulli. Ja atribūta faktoram nav un nevar būt nulles vērtības, tad iepriekšminētajai parametra a interpretācijai nav jēgas.
Pazīmju savstarpējo attiecību ciešuma novērtēšana
veikta, izmantojot lineāro pāru korelācijas koeficientu - r x,y. To var aprēķināt, izmantojot formulu: 
. Turklāt lineāro pāru korelācijas koeficientu var noteikt, izmantojot regresijas koeficientu b: 
.
Lineārā pāra korelācijas koeficienta pieļaujamo vērtību diapazons ir no –1 līdz +1. Korelācijas koeficienta zīme norāda attiecības virzienu. Ja r x, y >0, tad savienojums ir tiešs; ja r x, y<0, то связь обратная.
Ja šis koeficients ir tuvu vienībai pēc lieluma, tad attiecību starp raksturlielumiem var interpretēt kā diezgan ciešu lineāru. Ja tā modulis ir vienāds ar vienu ê r x , y ê =1, tad sakarība starp raksturlielumiem ir funkcionāli lineāra. Ja pazīmes x un y ir lineāri neatkarīgi, tad r x,y ir tuvu 0.
Lai aprēķinātu r x,y, varat izmantot arī 1. tabulu.
1. tabula
| N novērojumi | x i | y i | x i ∙y i | ||
| 1 | x 1 | y 1 | x 1 g 1 | ||
| 2 | x 2 | y 2 | x 2 y 2 | ||
| ... | |||||
| n | x n | g n | x n y n | ||
| Kolonnas summa | ∑x | ∑y | ∑xy | ||
| Vidējā vērtība |
 |
 |
,
kur d 2 ir y dispersija, kas izskaidrota ar regresijas vienādojumu;
e 2 - y atlikušā (ar regresijas vienādojumu neizskaidrojama) dispersija;
s 2 y — y kopējā (kopējā) dispersija.
Determinācijas koeficients raksturo rezultējošā atribūta y variācijas (dispersijas) proporciju, kas izskaidrojama ar regresiju (un līdz ar to arī faktoru x) kopējā variācijā (dispersijā) y. Determinācijas koeficients R 2 yx ņem vērtības no 0 līdz 1. Attiecīgi vērtība 1-R 2 yx raksturo dispersijas y proporciju, ko izraisa citu modelī neņemtu faktoru ietekme un specifikācijas kļūdas.
Ar pāru lineāro regresiju R 2 yx = r 2 yx.
