Mazāko kvadrātu metode (LSM) pieder regresijas analīzes jomai. 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 ļoti noderīgs novērojumu apstrādē, un to aktīvi izmanto, lai noteiktu dažus lielumus no 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 konkrētā piemērā

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 realizētas, izmantojot iebūvētās funkcijas), nekavējoties jāturpina. apsvērt konkrētu problēmu.

Tātad, lai X ir pārtikas veikala tirdzniecības platība, mērot kvadrātmetros, un Y ir gada apgrozījums, kas definēts miljonos rubļu.

Nepieciešams veikt prognozi, kāds būs veikala apgrozījums (Y), ja tam būs viena vai otra 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 ar n veikalu datiem.

Pēc matemātiskās statistikas, rezultāti būs vairāk vai mazāk pareizi, ja tiks pārbaudīti dati vismaz par 5-6 objektiem. Tāpat nevar izmantot "anomālus" rezultātus. Jo īpaši elitāra maza veikala apgrozījums var būt daudzkārt lielāks nekā lielo “masmarket” klases veikalu apgrozījums.

Metodes būtība

Tabulas datus var attēlot Dekarta plaknē kā punktus M 1 (x 1, y 1), ... M n (x n, y n). Tagad uzdevuma risinājums tiks reducēts uz aproksimējošas funkcijas y = f (x) izvēli, kuras grafiks 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. Vissaprātīgākais risinājums ir meklēt taisni y = ax + b, kas vislabāk tuvina eksperimentālos datus un precīzāk, koeficientus - a un b.

Precizitātes rādītājs

Jebkurai tuvināšanai tās precizitātes novērtējums ir īpaši svarīgs. Apzīmējiet 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, var izmantot noviržu summu, t.i., izvēloties taisnu līniju aptuvenai X atkarības no Y attēlojumam, priekšroka jādod tai, kurai ir mazākā vērtība. no summas e i visos izskatāmajos punktos. Tomēr ne viss ir tik vienkārši, jo kopā ar pozitīvajām novirzēm praktiski būs arī negatīvas.

Problēmu var atrisināt, izmantojot novirzes moduļus vai to kvadrātus. Pēdējā metode ir visizplatītākā. To izmanto daudzās jomās, tostarp regresijas analīzē (programmā Excel tā ieviešana tiek veikta, izmantojot divas iebūvētās funkcijas), un 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 automātiskās summas funkcija, 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).

Matemātiskajā pierakstā tas izskatās šādi:

Tā kā sākotnēji tika pieņemts lēmums tuvināt, izmantojot taisnu līniju, mums ir:

Tādējādi uzdevums atrast taisnu līniju, kas vislabāk apraksta konkrētu sakarību starp X un Y, nozīmē divu mainīgo funkcijas minimuma aprēķināšanu:

Tas prasa pielīdzināt nullei daļējiem atvasinājumiem attiecībā uz jauniem mainīgajiem a un b un atrisināt primitīvu sistēmu, kas sastāv no diviem vienādojumiem ar 2 formas nezināmajiem:

Pēc vienkāršām transformācijām, ieskaitot dalīšanu ar 2 un manipulācijas ar summām, mēs iegūstam:

Atrisinot to, piemēram, ar 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ā, der taisne y = a * x + b *, kas ir regresijas modelis aplūkojamajam piemēram. Protams, tas neļaus jums atrast precīzu rezultātu, bet tas palīdzēs jums iegūt priekšstatu par to, vai veikala pirkšana uz kredīta konkrētai zonai atmaksāsies.

Kā ieviest mazāko kvadrātu metodi programmā Excel

Programmā Excel ir funkcija mazāko kvadrātu vērtības aprēķināšanai. 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). Pielietosim mūsu tabulai formulu OLS aprēķināšanai programmā Excel.

Lai to izdarītu, šūnā, kurā jāparāda aprēķina rezultāts, izmantojot mazāko kvadrātu metodi programmā Excel, ievadiet zīmi “=” 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 apgrozījumu);
• diapazons x 1 , …x n , t.i., tirdzniecības telpas lielums;
• un zināmās un nezināmās x vērtības, kurām jānoskaidro apgrozījuma lielums (informāciju par to atrašanās vietu darblapā skatiet tālāk).

Turklāt formulā ir loģiskais mainīgais "Const". Ja tam atbilstošajā laukā ievadāt 1, tas nozīmēs, ka ir jāveic aprēķini, pieņemot, ka b \u003d 0.

Ja jums jāzina prognoze vairāk nekā vienai x vērtībai, tad pēc formulas ievadīšanas nevajadzētu spiest "Enter", bet jums ir jāievada kombinācija "Shift" + "Control" + "Enter" ("Enter" ) uz tastatūras.

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, kas nekad nav dzirdējuši par mazāko kvadrātu metodi. Pietiek tikai zināt dažas tā darba iezīmes. It īpaši:

• Ja vienā rindā vai kolonnā ievietojat mainīgā y zināmo vērtību diapazonu, programma katru rindu (kolonnu) ar zināmām x vērtībām uztvers kā atsevišķu mainīgo.
• Ja logā TREND nav norādīts diapazons ar zināmu x, tad funkcijas izmantošanas gadījumā programmā Excel programma to uzskatīs par masīvu, kas sastāv no veseliem skaitļiem, kuru skaits atbilst diapazonam ar dotajām vērtībām ​no mainīgā y.
• Lai izvadītu "paredzamo" vērtību masīvu, tendences izteiksme jāievada kā masīva formula.
• Ja nav norādītas jaunas x vērtības, 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 dotajiem parametriem y.
• Diapazonam, 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 ar norādītajām y vērtībām. Citiem vārdiem sakot, tam jābūt samērīgam ar 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 samērīgi. 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

Regresijas analīze programmā Excel tiek īstenota, izmantojot vairākas funkcijas. Viens no tiem saucas "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 Excel formulas manekeniem, kas ļauj prognozēt indikatora nākotnes vērtības vērtību atbilstoši lineārai tendencei.

kas atrod visvairāk plašs pielietojums dažādās zinātnes jomās un praktiskās aktivitātes. Tā var būt fizika, ķīmija, bioloģija, ekonomika, socioloģija, psiholoģija un tā tālāk, un tā tālāk. Pēc likteņa gribas man bieži nākas saskarties ar ekonomiku, un tāpēc šodien es jums noorganizēšu biļeti uz pārsteidzošu valsti ar nosaukumu Ekonometrija=) … Kā tu to negribi?! Tur ir ļoti labi – tikai jāizlemj! …Bet tas, ko jūs droši vien vēlaties, ir iemācīties risināt problēmas mazākie kvadrāti. Un īpaši čakli lasītāji iemācīsies tos atrisināt ne tikai precīzi, bet arī ĻOTI ĀTRI ;-) Bet vispirms vispārīgs problēmas izklāsts+ saistīts piemērs:

Ļaujiet rādītājus pētīt kādā mācību priekšmeta jomā, kam ir kvantitatīvā izteiksme. Tajā pašā laikā ir pamats uzskatīt, ka rādītājs ir atkarīgs no rādītāja. Šis pieņēmums var būt gan zinātniska hipotēze, gan balstīta uz elementāru veselo saprātu. Tomēr atstāsim zinātni malā un izpētīsim apetītīgākas jomas, proti, pārtikas preču veikalus. Apzīmē ar:

– pārtikas preču veikala tirdzniecības platība, kv.m,
- pārtikas veikala gada apgrozījums, miljoni rubļu.

Ir pilnīgi skaidrs, kas vairāk platības veikals, jo lielāks tā apgrozījums vairumā gadījumu.

Pieņemsim, ka pēc novērojumu / eksperimentu / aprēķinu / dejošanas ar tamburīnu mūsu rīcībā ir skaitliskie dati:

Ar pārtikas veikaliem, manuprāt, viss ir skaidrs: - šī ir 1. veikala platība, - tā gada apgrozījums, - 2. veikala platība, - tā gada apgrozījums utt. Starp citu, pieeja klasificētiem materiāliem nemaz nav nepieciešama - diezgan precīzu apgrozījuma novērtējumu var iegūt, izmantojot matemātiskā statistika. Tomēr nenovērsieties, komerciālās spiegošanas kurss jau ir apmaksāts =)

Tabulas datus var rakstīt arī punktu veidā un attēlot mums ierastajā veidā. Dekarta sistēma .

Atbildēsim uz svarīgu jautājumu: cik punktu vajag kvalitatīvam pētījumam?

Jo lielāks, jo labāk. Minimālais pieļaujamais komplekts sastāv no 5-6 punktiem. Turklāt, ja datu apjoms ir neliels, izlasē nevajadzētu iekļaut “neparastus” rezultātus. Tā, piemēram, neliels elites veikals var palīdzēt daudz vairāk nekā “viņu kolēģi”, tādējādi izkropļojot vispārējo modeli, kas jāatrod!

Ja tas ir pavisam vienkārši, mums ir jāizvēlas funkcija, grafiks kas iet pēc iespējas tuvāk punktiem . Tādu funkciju sauc tuvinot (tuvinājums - tuvinājums) vai teorētiskā funkcija . Vispārīgi runājot, šeit uzreiz parādās acīmredzams "pretende" - augstas pakāpes polinoms, kura grafiks iet caur VISIEM punktiem. Bet šī iespēja ir sarežģīta un bieži vien vienkārši nepareiza. (jo diagramma visu laiku "vīsies" un slikti atspoguļos galveno tendenci).

Tādējādi vēlamajai funkcijai jābūt pietiekami vienkāršai un tajā pašā laikā adekvāti jāatspoguļo atkarība. Kā jūs varētu nojaust, viena no metodēm šādu funkciju atrašanai tiek izsaukta mazākie kvadrāti. Pirmkārt, analizēsim tā būtību vispārīgā veidā. Ļaujiet kādai funkcijai tuvināt eksperimentālos datus:


Kā novērtēt šī tuvinājuma precizitāti? Aprēķināsim arī atšķirības (novirzes) starp eksperimentālo un funkcionālo vērtību (mēs pētām zīmējumu). Pirmā doma, kas nāk prātā, ir novērtēt, cik liela ir summa, bet problēma ir tā, ka atšķirības var būt negatīvas. (piemēram, ) un novirzes šādas summēšanas rezultātā viena otru atslēgs. Tāpēc kā tuvinājuma precizitātes aplēsi tā iesaka ņemt summu moduļi novirzes:

vai salocītā veidā: (pēkšņi, kurš nezina: ir summas ikona un ir papildu mainīgais - "skaitītājs", kas ņem vērtības no 1 līdz ).

Tuvinot eksperimentālos punktus ar dažādām funkcijām, iegūsim dažādas nozīmes, un, protams, ja šī summa ir mazāka, šī funkcija ir precīzāka.

Šāda metode pastāv un tiek saukta mazākā moduļa metode. Tomēr praksē tas ir kļuvis daudz izplatītāks. mazāko kvadrātu metode, kurā iespējamās negatīvās vērtības tiek izslēgtas nevis ar moduli, bet gan noviržu kvadrātā:

, pēc kura pūles tiek vērstas uz tādas funkcijas izvēli, lai noviržu summa kvadrātā bija pēc iespējas mazāks. Patiesībā, līdz ar to metodes nosaukums.

Un tagad mēs esam atgriezušies pie cita svarīgs punkts: kā minēts iepriekš, atlasītajai funkcijai jābūt diezgan vienkāršai, taču ir arī daudz šādu funkciju: lineārs , hiperbolisks, eksponenciāls, logaritmisks, kvadrātveida utt. Un, protams, šeit es uzreiz gribētu "samazināt darbības jomu". Kādu funkciju klasi izvēlēties pētniecībai? Primitīva, bet efektīva tehnika:

- Vienkāršākais veids, kā iegūt punktus uz zīmējuma un analizēt to atrašanās vietu. Ja tie mēdz būt taisnā līnijā, tad jums vajadzētu meklēt taisnās līnijas vienādojums ar optimālām vērtībām un . Citiem vārdiem sakot, uzdevums ir atrast TĀDUS koeficientus - lai noviržu kvadrātā summa būtu mazākā.

Ja punkti atrodas, piemēram, gar hiperbola, tad ir skaidrs, ka lineārā funkcija dos sliktu tuvinājumu. Šajā gadījumā mēs meklējam “labvēlīgākos” hiperbolas vienādojuma koeficientus - tie, kas dod minimālo kvadrātu summu .

Tagad ievērojiet, ka abos gadījumos mēs runājam par divu mainīgo funkcijas, kura argumenti ir meklēja atkarības opcijas:

Un būtībā mums ir jāatrisina standarta problēma – jāatrod divu mainīgo funkciju minimums.

Atgādiniet mūsu piemēru: pieņemsim, ka "veikala" punkti mēdz atrasties taisnā līnijā un ir pamats uzskatīt, ka ir lineārā atkarība apgrozījums no tirdzniecības zonas. Atradīsim TĀDUS koeficientus "a" un "būt", lai noviržu summa kvadrātā bija mazākais. Viss kā parasti – vispirms 1. kārtas daļēji atvasinājumi. Saskaņā ar linearitātes noteikums jūs varat atšķirt tieši zem summas ikonas:

Ja vēlaties šo informāciju izmantot esejai vai kursa darbam, būšu ļoti pateicīgs par saiti avotu sarakstā, tik detalizētus aprēķinus nekur neatradīsit:

Sacerēsim standarta sistēma:

Mēs samazinām katru vienādojumu par "diviem" un papildus "sadalām" summas:

Piezīme : neatkarīgi analizējiet, kāpēc no summas ikonas var izņemt "a" un "be". Starp citu, formāli to var izdarīt ar summu

Pārrakstīsim sistēmu "piemērotā" formā:

pēc kura sāk sastādīt mūsu problēmas risināšanas algoritmu:

Vai mēs zinām punktu koordinātas? Mēs zinām. Summas vai varam atrast? Viegli. Mēs sastādām visvienkāršāko divu lineāru vienādojumu sistēma ar diviem nezināmajiem("a" un "beh"). Mēs risinām sistēmu, piemēram, Krāmera metode, kā rezultātā rodas stacionārs punkts . Pārbauda pietiekams nosacījums ekstremitātei, mēs varam pārbaudīt, ka šajā brīdī funkcija sasniedz precīzi minimums. Pārbaude ir saistīta ar papildu aprēķiniem, tāpēc mēs to atstāsim aizkulisēs. (ja nepieciešams, trūkstošo kadru var apskatīt). Mēs izdarām galīgo secinājumu:

Funkcija labākais veids (vismaz salīdzinājumā ar jebkuru citu lineāro funkciju) tuvina eksperimentālos punktus . Aptuveni runājot, tā grafiks iet pēc iespējas tuvāk šiem punktiem. Tradīcijās ekonometrija tiek saukta arī iegūtā aproksimējošā funkcija pārī lineārās regresijas vienādojums .

Apskatāmajai problēmai ir liela praktiska nozīme. Situācijā ar mūsu piemēru vienādojums ļauj prognozēt, kāda veida apgrozījums ("yig") būs pie veikala ar tādu vai citu tirdzniecības laukuma vērtību (viena vai cita "x" nozīme). Jā, iegūtā prognoze būs tikai prognoze, taču daudzos gadījumos tā izrādīsies diezgan precīza.

Es analizēšu tikai vienu problēmu ar "reāliem" skaitļiem, jo ​​tajā nav nekādu grūtību - visi aprēķini ir skolas mācību programmas līmenī 7.-8. 95 procentos gadījumu jums tiks lūgts atrast tikai lineāru funkciju, bet pašā raksta beigās es parādīšu, ka nav grūtāk atrast vienādojumus optimālai hiperbolai, eksponentam un dažām citām funkcijām.

Patiesībā atliek izdalīt solītos labumus - lai jūs iemācītos šādus piemērus atrisināt ne tikai precīzi, bet arī ātri. Mēs rūpīgi izpētām standartu:

Uzdevums

Pētot divu rādītāju attiecības, tika iegūti šādi skaitļu pāri:

Izmantojot mazāko kvadrātu metodi, atrodiet lineāro funkciju, kas vislabāk tuvina empīrisko (pieredzējis) datus. Izveidojiet zīmējumu, uz kura Dekarta taisnstūra koordinātu sistēmā uzzīmējiet eksperimentālos punktus un aproksimējošās funkcijas grafiku . Atrodiet empīrisko un teorētisko vērtību noviržu kvadrātā summu. Uzziniet, vai funkcija ir labāka (mazāko kvadrātu metodes izteiksmē) aptuvenie eksperimentālie punkti.

Ņemiet vērā, ka "x" vērtības ir dabiskas vērtības, un tai ir raksturīga nozīmīga nozīme, par kuru es runāšu nedaudz vēlāk; bet tie, protams, var būt daļēji. Turklāt atkarībā no konkrēta uzdevuma satura gan "X", gan "G" vērtības var būt pilnībā vai daļēji negatīvas. Nu, mums ir dots “bez sejas” uzdevums, un mēs to sākam risinājums:

Mēs atrodam optimālās funkcijas koeficientus kā sistēmas risinājumu:

Lai iegūtu kompaktāku apzīmējumu, mainīgo “skaitītājs” var izlaist, jo jau ir skaidrs, ka summēšana tiek veikta no 1 līdz .

Ērtāk ir aprēķināt nepieciešamās summas tabulas veidā:


Aprēķinus var veikt ar mikrokalkulatoru, taču daudz labāk ir izmantot Excel - gan ātrāk, gan bez kļūdām; noskatieties īsu video:

Tādējādi mēs iegūstam sekojošo sistēma:

Šeit jūs varat reizināt otro vienādojumu ar 3 un atņemt 2. no 1. vienādojuma locekļa pa vārdam. Bet tā ir veiksme – praksē sistēmas bieži vien nav apdāvinātas, un tādos gadījumos tas ietaupa Krāmera metode:
, tāpēc sistēmai ir unikāls risinājums.

Veiksim pārbaudi. Es saprotu, ka negribu, bet kāpēc gan izlaist kļūdas, kur tās noteikti nevar palaist garām? Aizvietojiet atrasto risinājumu katra sistēmas vienādojuma kreisajā pusē:

Tiek iegūtas atbilstošo vienādojumu pareizās daļas, kas nozīmē, ka sistēma ir pareizi atrisināta.

Tādējādi vēlamā aproksimējošā funkcija: – no visas lineārās funkcijas eksperimentālos datus vislabāk var tuvināt ar to.

Atšķirībā no taisni veikala apgrozījuma atkarība no tā platības, konstatētā atkarība ir otrādi (princips "jo vairāk - jo mazāk"), un šo faktu uzreiz atklāj negatīvais leņķiskais koeficients. Funkcija informē mūs, ka, palielinoties noteiktam rādītājam par 1 vienību, atkarīgā rādītāja vērtība samazinās vidēji par 0,65 vienībām. Kā saka, jo augstāka griķu cena, jo mazāk pārdots.

Lai attēlotu tuvināšanas funkciju, mēs atrodam divas tās vērtības:

un izpildiet zīmējumu:


Izbūvēto līniju sauc tendenču līnija (proti, lineāra tendences līnija, t.i., vispārīgā gadījumā tendence ne vienmēr ir taisna līnija). Ikviens ir pazīstams ar izteicienu "būt trendā", un es domāju, ka šim terminam nav nepieciešami papildu komentāri.

Aprēķiniet noviržu kvadrātā summu starp empīriskām un teorētiskām vērtībām. Ģeometriski tā ir "sārtināto" segmentu garumu kvadrātu summa (no kurām divas ir tik mazas, ka tās pat nevar redzēt).

Apkoposim aprēķinus tabulā:


Tos atkal var veikt manuāli, tikai gadījumā, ja es sniegšu piemēru 1. punktam:

bet daudz efektīvāk ir darīt jau zināmo veidu:

Atkārtosim: kāda ir rezultāta nozīme? No visas lineārās funkcijas funkciju eksponents ir mazākais, tas ir, tas ir labākais tuvinājums savā saimē. Un šeit, starp citu, galīgais problēmas jautājums nav nejaušs: kā būtu, ja ierosinātā eksponenciālā funkcija vai labāk būs tuvināt eksperimentālos punktus?

Atradīsim atbilstošo noviržu kvadrātu summu - lai tās atšķirtu, apzīmēšu ar burtu "epsilon". Tehnika ir tieši tāda pati:


Un vēlreiz par katru ugunsgrēka aprēķinu 1. punktam:

Programmā Excel mēs izmantojam standarta funkciju EXP (Sintaksi var atrast Excel palīdzībā).

Secinājums: , tāpēc eksponenciālā funkcija eksperimentālos punktus tuvina sliktāk nekā taisne .

Bet šeit jāatzīmē, ka "sliktāk" ir vēl nenozīmē, kas vainas. Tagad es izveidoju šīs eksponenciālās funkcijas grafiku - un tā arī iet tuvu punktiem - tik ļoti, ka bez analītiska pētījuma ir grūti pateikt, kura funkcija ir precīzāka.

Tas pabeidz risinājumu, un es atgriežos pie jautājuma par argumenta dabiskajām vērtībām. Dažādos pētījumos, kā likums, ekonomiskie vai socioloģiskie, mēneši, gadi vai citi vienādi laika intervāli tiek numurēti ar dabisku "X". Apsveriet, piemēram, šādu problēmu.

4.1. Izmantojot iebūvētās funkcijas

aprēķins regresijas koeficienti veikta, izmantojot funkciju

LINEST(Vērtības_y; Vērtības_x; Konst; statistika),

Vērtības_y- y vērtību masīvs,

Vērtības_x- izvēles vērtību masīvs x ja masīvs X izlaists, tiek pieņemts, ka tas ir tāda paša izmēra masīvs (1;2;3;...) Vērtības_y,

Konst- Būla vērtība, kas norāda, vai konstante ir nepieciešama b bija vienāds ar 0. Ja Konst ir nozīme PATIESA vai izlaists, tad b aprēķina parastajā veidā. Ja arguments Konst tad ir FALSE b tiek pieņemts, ka tas ir 0 un vērtības a ir izvēlēti tā, lai attiecības y=cirvis.

Statistika- Būla vērtība, kas norāda, vai ir jāatgriež papildu regresijas statistika. Ja arguments Statistika ir nozīme PATIESA, tad funkcija LINEST atgriež papildu regresijas statistiku. Ja arguments Statistika ir nozīme FALSE vai izlaists, tad funkcija LINEST atgriež tikai koeficientu a un pastāvīgs b.

Jāatceras, ka funkciju rezultāts LINEST() ir vērtību kopa - masīvs.

Aprēķinam korelācijas koeficients funkcija tiek izmantota

KORREĻA(Masīvs1;Masīvs2),

atgriežot korelācijas koeficienta vērtības, kur Masīvs1- vērtību masīvs y, Masīvs2- vērtību masīvs x. Masīvs1 un Masīvs2 jābūt vienāda izmēra.

1. PIEMĒRS. Atkarība y(x) ir parādīts tabulā. Būvēt regresijas līnija un aprēķināt korelācijas koeficients.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

Ievadīsim vērtību tabulu MS Excel lapā un izveidosim izkliedes diagrammu. Darblapai būs tāda forma, kas parādīta attēlā. 2.

Lai aprēķinātu regresijas koeficientu vērtības a un b atlasiet šūnas A7:B7, pievērsīsimies funkciju vednim un kategorijā Statistikas izvēlieties funkciju LINEST. Aizpildiet parādīto dialoglodziņu, kā parādīts attēlā. 3 un nospiediet labi.

Rezultātā aprēķinātā vērtība parādīsies tikai šūnā A6(4. att.). Lai šūnā parādītos vērtība B6 jums jāieiet rediģēšanas režīmā (taustiņš F2) un pēc tam nospiediet taustiņu kombināciju CTRL+SHIFT+ENTER.

Lai aprēķinātu korelācijas koeficienta vērtību šūnai C6 tika ieviesta šāda formula:

C7=KORREL(B3:J3;B2:J2).

Zinot regresijas koeficientus a un b aprēķināt funkcijas vērtības y=cirvis+b par doto x. Lai to izdarītu, mēs ieviešam formulu

B5=$A$7*B2+$B$7

un kopējiet to diapazonā С5:J5(5. att.).

Uzzīmēsim regresijas taisni diagrammā. Diagrammā atlasiet eksperimentālos punktus, ar peles labo pogu noklikšķiniet un atlasiet komandu Sākotnējie dati. Parādītajā dialoglodziņā (5. att.) atlasiet cilni Rinda un noklikšķiniet uz pogas Pievienot. Aizpildiet ievades laukus, kā parādīts attēlā. 6 un nospiediet pogu labi. Eksperimentālo datu diagrammai tiks pievienota regresijas līnija. Pēc noklusējuma tā diagramma tiks parādīta kā punkti, kas nav savienoti ar izlīdzinošām līnijām.

Rīsi. 6

Lai mainītu regresijas līnijas izskatu, veiciet šādas darbības. Ar peles labo pogu noklikšķiniet uz punktiem, kas attēlo līniju diagrammu, atlasiet komandu Diagrammas veids un iestatiet izkliedes diagrammas veidu, kā parādīts attēlā. 7.

Līnijas veidu, krāsu un biezumu var mainīt šādi. Diagrammā atlasiet līniju, nospiediet peles labo pogu un konteksta izvēlnē atlasiet komandu Datu sērijas formāts… Pēc tam veiciet iestatījumus, piemēram, kā parādīts attēlā. astoņi.

Visu transformāciju rezultātā vienā grafiskajā apgabalā iegūstam eksperimentālo datu grafiku un regresijas līniju (9. att.).

4.2. Izmantojot tendenču līniju.

Dažādu tuvinājumu atkarību konstruēšana programmā MS Excel tiek realizēta kā diagrammas rekvizīts - tendenču līnija.

2. PIEMĒRS. Eksperimenta rezultātā tika noteikta zināma tabulas atkarība.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Izvēlieties un izveidojiet aptuvenu atkarību. Veidojiet tabulas un pielāgoto analītisko atkarību grafikus.

Problēmas risinājumu var iedalīt šādos posmos: sākotnējo datu ievade, izkliedes diagrammas izveidošana un tendenču līnijas pievienošana šim grafikam.

Apsvērsim šo procesu sīkāk. Ievadīsim sākotnējos datus darblapā un uzzīmēsim eksperimentālos datus. Pēc tam diagrammā atlasiet eksperimentālos punktus, ar peles labo pogu noklikšķiniet un izmantojiet komandu Pievienot l tendenču līnija(10. att.).

Parādītajā dialoglodziņā varat izveidot aptuvenu atkarību.

Šī loga pirmā cilne (11. att.) norāda tuvinātās atkarības veidu.

Otrais (12. att.) nosaka konstrukcijas parametrus:

tuvinātās atkarības nosaukums;

Prognoze uz priekšu (atpakaļ) ieslēgta n vienības (šis parametrs nosaka, cik vienību uz priekšu (atpakaļ) nepieciešams pagarināt trenda līniju);

vai parādīt līknes krustošanās punktu ar līniju y=konst;

vai diagrammā parādīt tuvināto funkciju vai nē (parādīt vienādojumu diagrammas parametrā);

Vai diagrammā ievietot standartnovirzes vērtību vai ne (parametrs, lai diagrammā ievietotu tuvinājuma ticamības vērtību).

Izvēlēsimies otrās pakāpes polinomu kā aproksimējošu atkarību (11. att.) un atvasināsim šo polinomu aprakstošu vienādojumu grafikā (12. att.). Iegūtā diagramma ir parādīta attēlā. 13.

Līdzīgi, ar tendenču līnijas jūs varat izvēlēties tādu atkarību parametrus kā

lineārs y=a∙x+b,

logaritmisks y=a ln(x)+b,

eksponenciāls y=a∙eb,

jauda y=a x b,

polinoms y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d un tā tālāk, līdz 6. pakāpes polinomam ieskaitot,

Lineārā filtrēšana.

4.3. Izmantojot lēmēju

Ievērojamu interesi rada MS Excel parametru atlases ieviešana pēc mazāko kvadrātu metodes, izmantojot lēmumu bloku. Šis paņēmiens ļauj izvēlēties jebkura veida funkcijas parametrus. Apskatīsim šo iespēju, izmantojot šādas problēmas piemēru.

3. PIEMĒRS. Eksperimenta rezultātā tabulā parādītā atkarība z(t).

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

Izvēlieties atkarības koeficientus Z(t)=Pie 4 +Bt 3 +Ct 2 +Dt+K ar mazāko kvadrātu metodi.

Šī problēma ir līdzvērtīga piecu mainīgo funkcijas minimuma atrašanas problēmai

Apsveriet optimizācijas problēmas risināšanas procesu (14. att.).

Ļaujiet vērtībām BET, AT, NO, D un Uz glabājas šūnās A7:E7. Aprēķiniet funkcijas teorētiskās vērtības Z(t)=At4+Bt3+Ct2+Dt+K par doto t(B2:J2). Lai to izdarītu, šūnā B4 ievadiet funkcijas vērtību pirmajā punktā (šūnā B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Kopējiet šo formulu diapazonā С4:J4 un iegūt funkcijas paredzamo vērtību punktos, kuru abscises tiek saglabātas šūnās B2:J2.

Uz šūnu B5 mēs ieviešam formulu, kas aprēķina starpības kvadrātu starp eksperimentālo un aprēķināto punktu:

B5=(B4-B3)^2,

un kopējiet to diapazonā С5:J5. Šūnā F7 mēs saglabāsim kopējo kvadrātisko kļūdu (10). Lai to izdarītu, mēs ieviešam formulu:

F7 = SUMMA(B5:J5).

Izmantosim komandu Service®Meklējiet risinājumu un atrisināt optimizācijas problēmu bez ierobežojumiem. Dialoglodziņā, kas parādīts attēlā, aizpildiet atbilstošos ievades laukus. 14 un nospiediet pogu Skrien. Ja risinājums tiek atrasts, logs, kas parādīts attēlā. piecpadsmit.

Lēmuma bloka rezultāts būs izvade šūnām A7:E7parametru vērtības funkcijas Z(t)=At4+Bt3+Ct2+Dt+K. Šūnās B4:J4 mēs saņemam paredzamā funkcijas vērtība sākuma punktos. Šūnā F7 tiks paturēts kopējā kļūda kvadrātā.

Varat parādīt eksperimentālos punktus un pielāgoto līniju tajā pašā grafiskajā apgabalā, ja atlasāt diapazonu B2:J4, zvaniet Diagrammu vednis un pēc tam formatējiet iegūto grafiku izskatu.

Rīsi. 17 parāda MS Excel darblapu pēc aprēķinu veikšanas.

5. ATSAUCES

1. Aleksejevs E.R., Česnokova O.V., Skaitļošanas matemātikas uzdevumu risināšana paketēs Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596.s. : ill. – (Pamācība)

2. Aleksejevs E.R., Česnokova O.V., E.A. Rudčenko, Scilab, inženierzinātņu un matemātikas uzdevumu risināšana. –M., BINOM, 2008.–260.gadi.

3. I. S. Berezins un N. P. Židkovs, Methods of Computation, Maskava: Nauka, 1966. gads.

4. Garnaev A.Yu., MS EXCEL un VBA izmantošana ekonomikā un finansēs. - Sanktpēterburga: BHV - Pēterburga, 1999.-332lpp.

5. B. P. Demidovičs, I. A. Marons un V. Z. Šuvalova, Numerical Methods of Analysis.–M.: Nauka, 1967.–368 lpp.

6. Korn G., Korn T., Matemātikas rokasgrāmata zinātniekiem un inženieriem.–M., 1970, 720 lpp.

7. Aleksejevs E.R., Česnokova O.V. Vadlīnijas veikt laboratorijas darbus programmā MS EXCEL. Visu specialitāšu studentiem. Doņecka, DonNTU, 2004. 112 lpp.

Mazāko kvadrātu (LSM) metode ir balstīta uz atlasītās funkcijas kvadrātu noviržu summas samazināšanu no pētāmajiem datiem. Šajā rakstā mēs aptuvenējam pieejamos datus, izmantojot lineāro funkcijuy = a x + b .

Mazākā kvadrāta metode(Angļu) Parasta Vismazāk Kvadrāti , OLS) ir viena no regresijas analīzes pamatmetodēm nezināmu parametru novērtēšanas ziņā regresijas modeļi saskaņā ar parauga datiem.

Apsveriet aproksimāciju pēc funkcijām atkarībā tikai no viena mainīgā:

• Lineārs: y=ax+b (šis raksts)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

Piezīme: Šajā rakstā aplūkoti gadījumi, kad tiek veikta tuvināšana ar polinomu no 3. līdz 6. pakāpei. Šeit aplūkota tuvināšana ar trigonometrisku polinomu.

Lineārā atkarība

Mūs interesē 2 mainīgo attiecības X un y. Pastāv pieņēmums, ka y atkarīgs no X saskaņā ar lineāro likumu y = cirvis + b. Lai noteiktu šīs attiecības parametrus, pētnieks veica novērojumus: katrai x i vērtībai tika veikts y i mērījums (sk. piemēru failu). Attiecīgi, lai ir 20 vērtību pāri (х i ; y i).

Piezīme: Ja izmaiņas soli pa X ir nemainīgs, tad būvēt izkliedes diagrammas var izmantot, ja nē, tad jāizmanto diagrammas veids punktēts .

No diagrammas redzams, ka attiecības starp mainīgajiem ir tuvu lineārai. Lai saprastu, kura no daudzajām taisnēm vis "pareizāk" raksturo attiecības starp mainīgajiem, ir jānosaka kritērijs, pēc kura līnijas tiks salīdzinātas.

Kā šādu kritēriju mēs izmantojam izteicienu:

kur ŷ i = a * x i + b ; n – vērtību pāru skaits (mūsu gadījumā n=20)

Iepriekš minētā izteiksme ir attālumu kvadrātā summa starp novērotajām y i un ŷ i vērtībām, un to bieži apzīmē kā SSE ( summa no kvadrātā Kļūdas (Atlikumi), kļūdu kvadrātu summa (atlikumi)) .

Mazākā kvadrāta metode ir izvēlēties šādu līniju ŷ = cirvis + b, kurai iepriekšminētajai izteiksmei ir minimālā vērtība.

Piezīme: Jebkuru līniju divdimensiju telpā unikāli nosaka 2 parametru vērtības: a (slīpums) un b (maiņa).

Tiek uzskatīts, ka jo mazāka ir attālumu kvadrātā summa, jo labāk atbilstošā līnija tuvina pieejamos datus un to var tālāk izmantot, lai prognozētu y vērtības no mainīgā x. Ir skaidrs, ka pat tad, ja patiesībā starp mainīgajiem nav sakarības vai attiecības ir nelineāras, LSM tik un tā izvēlēsies “labāko” līniju. Tādējādi LSM neko nesaka par reālu mainīgo attiecību esamību, metode vienkārši ļauj izvēlēties šādus funkcijas parametrus a un b , kam iepriekš minētā izteiksme ir minimāla.

Veicot ne pārāk sarežģītas matemātiskas darbības (sīkāku informāciju skatiet), varat aprēķināt parametrus a un b :

Kā redzams no formulas, parametrs a ir kovariācijas attiecība un , tāpēc MS EXCEL, lai aprēķinātu parametru a Varat izmantot šādas formulas (sk parauga faila lapa Lineārs):

= COVAR(B26:B45;C26:C45)/VAR.G(B26:B45) vai

= COVARIATION.B(B26:B45;C26:C45)/VAR.B(B26:B45)

Arī parametra aprēķināšanai a varat izmantot formulu = SLOPE(C26:C45;B26:B45). Par parametru b izmantojiet formulu = INTERCUT(C26:C45;B26:B45) .

Visbeidzot, funkcija LINEST() ļauj aprēķināt abus parametrus vienlaikus. Lai ievadītu formulu LINEST(C26:C45;B26:B45) atlasiet 2 šūnas pēc kārtas un nospiediet CTRL + SHIFT + ENTER(skatiet rakstu par to). Kreisā šūna atgriezīs vērtību a , pa labi b .

Piezīme: Lai nejauktos ar ievadi masīvu formulas papildus būs jāizmanto funkcija INDEX(). Formula = INDEKSS(LINEST(C26:C45,B26:B45),1) vai vienkārši = LINEST(C26:C45;B26:B45) atgriezīs parametru, kas ir atbildīgs par līnijas slīpumu, t.i. a . Formula = INDEKSS(LINEST(C26:C45,B26:B45),2) atgriezīs parametru, kas ir atbildīgs par taisnes krustošanos ar Y asi, t.i. b .

Pēc parametru aprēķināšanas izkliedes diagramma līniju var novilkt.

Vēl viens veids, kā novilkt taisnu līniju, izmantojot mazāko kvadrātu metodi, ir diagrammas rīks tendenču līnija. Lai to izdarītu, atlasiet diagrammu, atlasiet izvēlnē Izkārtojuma cilne, iekšā Grupas analīze klikšķis tendenču līnija, tad Lineāra tuvināšana .

Dialoglodziņā atzīmējot izvēles rūtiņu "rādīt vienādojumu diagrammā", varat pārliecināties, vai iepriekš atrastie parametri atbilst diagrammā redzamajām vērtībām.

Piezīme: lai parametri atbilstu, diagrammas veidam ir jābūt . Fakts ir tāds, ka, veidojot diagrammu Grafiks x-ass vērtības lietotājs nevar iestatīt (lietotājs var norādīt tikai etiķetes, kas neietekmē punktu atrašanās vietu). X vērtību vietā tiek izmantota secība 1; 2; 3; … (kategoriju numerācijai). Tāpēc, ja ēka tendenču līnija uz tipa diagrammas Grafiks, tad X faktisko vērtību vietā tiks izmantotas šīs secības vērtības, kas novedīs pie nepareiza rezultāta (ja vien, protams, X faktiskās vērtības nesakrīt ar secību 1; 2 ; 3; ...).

Mazāko kvadrātu metode (LSM)

Sistēma m lineārie vienādojumi ar n nezināmajiem ir šāda forma:

Iespējami trīs gadījumi: m n. Iepriekšējos punktos tika aplūkots gadījums, kad m=n. Par m

Ja m>n un sistēma ir konsekventa, tad matricai A ir vismaz m - n lineāri atkarīgas rindas. Šeit atrisinājumu var iegūt, izvēloties n jebkurus lineāri neatkarīgus vienādojumus (ja tādi pastāv) un pielietojot formulu X=A -1 CV, tas ir, reducējot uzdevumu uz iepriekš atrisināto. Šajā gadījumā iegūtais risinājums vienmēr apmierinās atlikušos m - n vienādojumus.

Tomēr, izmantojot datoru, ērtāk ir izmantot vispārīgāku pieeju - mazāko kvadrātu metodi.

Algebriskie mazākie kvadrāti

Ar mazāko kvadrātu algebrisko metodi saprot lineāro vienādojumu sistēmu risināšanas metodi

samazinot Eiklīda normu

Cirvis? b? > inf . (1.2)

Eksperimentālā datu analīze

Apskatīsim kādu eksperimentu, kura laikā laika momentā

piemēram, mēra temperatūru Q(t). Mērījumu rezultātus uzrāda masīvs

Pieņemsim, ka eksperimenta apstākļi ir tādi, ka mērījumi tiek veikti ar zināmu kļūdu. Šajos gadījumos temperatūras izmaiņu likums Q(t) tiek meklēts, izmantojot kādu polinomu

P(t) = + + + ... +,

nosakot nezināmos koeficientus, ..., no apsvērumiem, ka vērtība E(, ...,) definēta ar vienādību

Gausa algebriskā eksel aproksimācija

paņēma minimālo vērtību. Tā kā kvadrātu summa ir samazināta līdz minimumam, šo metodi sauc par mazākajiem kvadrātiem, kas atbilst datiem.

Ja P(t) aizstājam ar tā izteiksmi, mēs iegūstam

Izvirzīsim uzdevumu definēt masīvu tā, lai vērtība būtu minimāla, t.i. definēt masīvu, izmantojot mazāko kvadrātu metodi. Lai to izdarītu, daļējos atvasinājumus pielīdzinām nullei:

Ja ievadāt m × n matricu A = (), i = 1, 2..., m; j = 1, 2, ..., n, kur

I = 1, 2..., m; j = 1, 2, ..., n,

tad rakstiskā vienlīdzība iegūst formu

Pārrakstīsim uzrakstīto vienādību operāciju ar matricām izteiksmē. Pēc definīcijas mums ir matricas reizināšana ar kolonnu

Transponētajai matricai līdzīgas attiecības izskatās šādi

Mēs ieviešam šādu apzīmējumu: apzīmēsim vektora Ax i-to komponentu Saskaņā ar uzrakstītajām matricas vienādībām mums būs

Matricas formā šo vienādību var pārrakstīt kā

A T x = A T B (1,3)

Šeit A ir taisnstūra m × n matrica. Turklāt datu tuvināšanas problēmās, kā likums, m > n. Vienādojumu (1.3) sauc par normālo vienādojumu.

Jau no paša sākuma, izmantojot Eiklīda vektoru normu, problēmu bija iespējams uzrakstīt līdzvērtīgā matricas formā:

Mūsu mērķis ir samazināt šo funkciju x. Lai risinājuma punktā tiktu sasniegts minimums, pirmajiem atvasinājumiem attiecībā pret x šajā punktā jābūt vienādiem ar nulli. Šīs funkcijas atvasinājumi ir

2A T B + 2A T Ax

un tāpēc risinājumam jāapmierina lineāro vienādojumu sistēma

(A T A)x = (A T B).

Šos vienādojumus sauc par normāliem vienādojumiem. Ja A ir m × n matrica, tad A>A - n × n ir matrica, t.i. parastā vienādojuma matrica vienmēr ir kvadrātveida simetriska matrica. Turklāt tam ir pozitīvas noteiktības īpašība tādā nozīmē, ka (A>Ax, x) = (Ax, Ax) ? 0.

komentēt. Dažkārt formas (1.3) vienādojuma risinājumu sauc par sistēmas Ax = B risinājumu, kur A ir taisnstūra m × n (m > n) matrica pēc mazāko kvadrātu metodes.

Mazāko kvadrātu problēmu var grafiski interpretēt kā vertikālo attālumu samazināšanu no datu punktiem līdz modeļa līknei (sk. 1.1. attēlu). Šī ideja ir balstīta uz pieņēmumu, ka visas aproksimācijas kļūdas atbilst novērojumu kļūdām. Ja arī skaidrojošajos mainīgajos ir kļūdas, tad varētu būt pareizāk samazināt Eiklīda attālumu no datiem līdz modelim.

OLS programmā Excel

Tālāk sniegtais algoritms OLS ieviešanai programmā Excel pieņem, ka visi sākotnējie dati jau ir zināmi. Reizinām abas sistēmas matricas vienādojuma AЧX=B daļas no kreisās puses ar sistēmas transponēto matricu А Т:

A T AX \u003d A T B

Tad mēs reizinām abas vienādojuma daļas kreisajā pusē ar matricu (A T A) -1. Ja šī matrica pastāv, tad sistēma ir definēta. Ņemot vērā faktu, ka

(A T A) -1 * (A T A) \u003d E, mēs iegūstam

X \u003d (A T A) -1 A T B.

Iegūtais matricas vienādojums ir risinājums m lineāro vienādojumu sistēmai ar n nezināmajiem m>n.

Apsveriet iepriekšminētā algoritma piemērošanu konkrētam piemēram.

Piemērs. Lai sistēma jāatrisina

Programmā Excel šīs problēmas risinājuma lapa formulas displeja režīmā izskatās šādi:

Aprēķinu rezultāti:

Vēlamais vektors X atrodas diapazonā E11:E12.

Risinot noteiktu lineāro vienādojumu sistēmu, tika izmantotas šādas funkcijas:

1. MINUTE — atgriež masīvā saglabātās matricas apgriezto vērtību.

Sintakse: NBR(masīvs).

Masīvs ir skaitlisks masīvs ar vienādu rindu un kolonnu skaitu.

2. MULTIP - atgriež matricu reizinājumu (matricas tiek saglabātas masīvos). Rezultāts ir masīvs ar tādu pašu rindu skaitu kā masīvs1 un tādu pašu kolonnu skaitu kā masīvs2.

Sintakse: MULT(masīvs1, masīvs2).

Masīvs1, masīvs2 — reizināti masīvi.

Pēc funkcijas ievadīšanas masīva diapazona augšējā kreisajā šūnā atlasiet masīvu, sākot no šūnas, kurā ir formula, nospiediet taustiņu F2 un pēc tam nospiediet taustiņus CTRL+SHIFT+ENTER.

3. TRANSPOZE - pārvērš vertikālu šūnu kopu horizontālā vai otrādi. Šīs funkcijas izmantošanas rezultāts ir masīvs, kura rindu skaits ir vienāds ar kolonnu skaitu sākotnējā masīvā un kolonnu skaits ir vienāds ar rindu skaitu sākotnējā masīvā.