Jednou z metód na štúdium stochastických vzťahov medzi znakmi je regresná analýza.
Regresná analýza je odvodením regresnej rovnice, ktorá sa používa na nájdenie priemernej hodnoty náhodnej premennej (vlastnosti-výsledku), ak je známa hodnota inej (alebo iných) premenných (vlastnostných faktorov). Zahŕňa nasledujúce kroky:

1. voľba formy spojenia (typ analytickej regresnej rovnice);
2. odhad parametrov rovnice;
3. hodnotenie kvality analytickej regresnej rovnice.

Na odhad parametrov sa najčastejšie používa metóda najmenších štvorcov (LSM).
Metóda najmenších štvorcov poskytuje najlepšie (konzistentné, efektívne a nezaujaté) odhady parametrov regresnej rovnice. Ale iba ak sú splnené určité predpoklady o náhodnom člene (u) a nezávislej premennej (x) (pozri predpoklady OLS).

Problém odhadu parametrov lineárnej párovej rovnice metódou najmenších štvorcov spočíva v nasledujúcom: získať také odhady parametrov, pri ktorých je súčet druhých mocnín odchýlok skutočných hodnôt efektívnej funkcie - y i od vypočítaných hodnôt - minimálny.
Formálne Kritérium OLS dá sa napísať takto: .

Klasifikácia metód najmenších štvorcov

1. Metóda najmenších štvorcov.
2. Metóda maximálnej pravdepodobnosti (pre normálny klasický lineárny regresný model sa postuluje normalita regresných zvyškov).
3. Zovšeobecnená metóda najmenších štvorcov GLSM sa používa v prípade autokorelácie chýb a v prípade heteroskedasticity.
4. Metóda vážených najmenších štvorcov (špeciálny prípad GLSM s heteroskedastickými rezíduami).

Ilustrujte podstatu graficky klasická metóda najmenších štvorcov. Aby sme to dosiahli, zostavíme bodový graf podľa pozorovacích údajov (x i, y i, i=1;n) v pravouhlom súradnicovom systéme (takýto bodový graf sa nazýva korelačné pole). Skúsme nájsť priamku, ktorá je najbližšie k bodom korelačného poľa. Podľa metódy najmenších štvorcov sa čiara volí tak, aby súčet štvorcových vertikálnych vzdialeností medzi bodmi korelačného poľa a touto čiarou bol minimálny.

Matematický zápis tohto problému: .
Hodnoty y i a x i = 1...n sú nám známe, ide o pozorovacie údaje. Vo funkcii S sú konštanty. Premenné v tejto funkcii sú požadované odhady parametrov - , . Na nájdenie minima funkcie 2 premenných je potrebné vypočítať parciálne derivácie tejto funkcie vzhľadom na každý z parametrov a prirovnať ich k nule, t.j. .
Výsledkom je systém 2 normálnych lineárnych rovníc:
Pri riešení tohto systému nájdeme požadované odhady parametrov:

Správnosť výpočtu parametrov regresnej rovnice je možné skontrolovať porovnaním súčtov (je možná určitá nezrovnalosť v dôsledku zaokrúhľovania výpočtov).
Ak chcete vypočítať odhady parametrov, môžete zostaviť tabuľku 1.
Znamienko regresného koeficientu b udáva smer vzťahu (ak b > 0, vzťah je priamy, ak b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formálne je hodnota parametra a priemerná hodnota y pre x rovná nule. Ak znamienkový faktor nemá a nemôže mať nulovú hodnotu, potom vyššie uvedená interpretácia parametra a nemá zmysel.

Posúdenie tesnosti vzťahu medzi znakmi sa vykonáva pomocou koeficientu lineárnej párovej korelácie - r x,y . Dá sa vypočítať pomocou vzorca: . Okrem toho možno koeficient lineárnej párovej korelácie určiť pomocou regresného koeficientu b: .
Rozsah prípustných hodnôt lineárneho koeficientu párovej korelácie je od –1 do +1. Znamienko korelačného koeficientu udáva smer vzťahu. Ak r x, y > 0, potom je spojenie priame; ak r x, y<0, то связь обратная.
Ak je tento koeficient blízky jednotke v module, potom vzťah medzi znakmi možno interpretovať ako pomerne blízky lineárny. Ak sa jeho modul rovná jednej ê r x , y ê =1, potom je vzťah medzi vlastnosťami funkčný lineárny. Ak sú znaky x a y lineárne nezávislé, potom r x, y je blízko 0.
Tabuľku 1 možno použiť aj na výpočet r x, y.

Na posúdenie kvality získanej regresnej rovnice sa vypočíta teoretický koeficient determinácie - R 2 yx:

,
kde d2 je rozptyl y vysvetlený regresnou rovnicou;
e 2 - reziduálny (nevysvetlený regresnou rovnicou) rozptyl y ;
s 2 y - celkový (celkový) rozptyl y .
Koeficient determinácie charakterizuje podiel variácie (disperzie) výsledného znaku y, vysvetleného regresiou (a následne faktorom x), na celkovej variácii (disperzii) y. Koeficient determinácie R 2 yx nadobúda hodnoty od 0 do 1. Hodnota 1-R 2 yx teda charakterizuje podiel rozptylu y spôsobený vplyvom iných faktorov nezohľadnených v modeli a špecifikačných chýb.
Pri párovej lineárnej regresii R 2 yx = r 2 yx .

Metóda najmenších štvorcov (LSM)

Sústava m lineárnych rovníc s n neznámymi má tvar:

Možné sú tri prípady: m n. Prípad, keď m=n bol uvažovaný v predchádzajúcich odsekoch. Pre m

Ak m>n a systém je konzistentný, potom matica A má aspoň m - n lineárne závislých riadkov. Tu je možné riešenie získať výberom n ľubovoľných lineárne nezávislých rovníc (ak existujú) a použitím vzorca X=A -1 CV, čiže zredukovaním problému na predtým vyriešený. V tomto prípade bude výsledné riešenie vždy spĺňať zvyšných m - n rovníc.

Pri používaní počítača je však vhodnejšie použiť všeobecnejší prístup – metódu najmenších štvorcov.

Algebraické najmenšie štvorce

Algebraická metóda najmenších štvorcov sa chápe ako metóda riešenia sústav lineárnych rovníc

minimalizovaním euklidovskej normy

Sekera? b? > inf . (1,2)

Experimentálna analýza dát

Uvažujme o nejakom experimente, počas ktorého v časových okamihoch

napríklad sa meria teplota Q(t). Nech sú výsledky merania dané poľom

Predpokladajme, že podmienky experimentu sú také, že merania sa vykonávajú so známou chybou. V týchto prípadoch sa zákon zmeny teploty Q(t) hľadá pomocou nejakého polynómu

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

určenie neznámych koeficientov, ..., z úvah, že hodnotu E(, ...,) definuje rovnosť

gaussova algebraická exel aproximácia

nabral minimálnu hodnotu. Keďže súčet štvorcov je minimalizovaný, táto metóda sa nazýva najmenšie štvorce prispôsobené údajom.

Ak nahradíme P(t) jeho výrazom, dostaneme

Dajme si za úlohu definovať pole tak, aby hodnota bola minimálna, t.j. definujte pole pomocou metódy najmenších štvorcov. Aby sme to dosiahli, prirovnáme parciálne derivácie k nule:

Ak zadáte m × n maticu A = (), i = 1, 2..., m; j = 1, 2, ..., n, kde

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

potom písomná rovnosť nadobúda formu

Prepíšme zapísanú rovnosť z hľadiska operácií s maticami. Podľa definície máme násobenie matice stĺpcom

Pre transponovanú maticu vyzerá podobný vzťah takto

Zavedieme nasledovný zápis: budeme označovať i -tu zložku vektora Ax V súlade so zapísanými maticovými rovnosťami budeme mať

V maticovej forme môže byť táto rovnosť prepísaná ako

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

Tu je A obdĺžniková matica m×n. Navyše v problémoch aproximácie údajov je spravidla m > n. Rovnica (1.3) sa nazýva normálna rovnica.

Od samého začiatku bolo možné pomocou euklidovskej normy vektorov zapísať problém v ekvivalentnej maticovej forme:

Naším cieľom je minimalizovať túto funkciu v x. Aby sa v bode riešenia dosiahlo minimum, musia sa prvé derivácie vzhľadom na x v tomto bode rovnať nule. Deriváty tejto funkcie sú

2A T B + 2A T Ax

a preto riešenie musí vyhovovať sústave lineárnych rovníc

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

Tieto rovnice sa nazývajú normálne rovnice. Ak A je matica m × n, potom A>A - n × n je matica, t.j. matica normálnej rovnice je vždy štvorcová symetrická matica. Okrem toho má vlastnosť pozitívnej určitosti v tom zmysle, že (A>Ax, x) = (Ax, Ax) ? 0.

Komentujte. Niekedy sa riešenie rovnice v tvare (1.3) nazýva riešením sústavy Ax = B, kde A je pravouhlá matica m × n (m > n) metódou najmenších štvorcov.

Problém najmenších štvorcov možno graficky interpretovať ako minimalizáciu vertikálnych vzdialeností od údajových bodov k modelovej krivke (pozri obrázok 1.1). Táto myšlienka je založená na predpoklade, že všetky aproximačné chyby zodpovedajú pozorovacím chybám. Ak sú chyby aj vo vysvetľujúcich premenných, potom môže byť vhodnejšie minimalizovať euklidovskú vzdialenosť od údajov k modelu.

OLS v Exceli

Algoritmus implementácie OLS v Exceli uvedený nižšie predpokladá, že všetky počiatočné údaje sú už známe. Obe časti maticovej rovnice AЧX=B systému zľava vynásobíme transponovanou maticou systému А Т:

A T AX \u003d A T B

Potom obe časti rovnice vľavo vynásobíme maticou (A T A) -1. Ak táto matica existuje, potom je systém definovaný. Berúc do úvahy skutočnosť, že

(A T A) -1 * (A T A) \u003d E, dostaneme

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

Výsledná maticová rovnica je riešením sústavy m lineárnych rovníc s n neznámymi pre m>n.

Zvážte použitie vyššie uvedeného algoritmu na konkrétnom príklade.

Príklad. Nech je potrebné vyriešiť systém

V Exceli vyzerá hárok s riešením v režime zobrazenia vzorca pre tento problém takto:

Výsledky výpočtu:

Požadovaný vektor X sa nachádza v rozsahu E11:E12.

Pri riešení danej sústavy lineárnych rovníc boli použité nasledujúce funkcie:

1. MINUTA – Vráti inverznú hodnotu matice uloženej v poli.

Syntax: NBR(pole).

Pole je číselné pole s rovnakým počtom riadkov a stĺpcov.

2. MULTIP - vráti súčin matíc (matice sú uložené v poliach). Výsledkom je pole s rovnakým počtom riadkov ako pole1 a rovnakým počtom stĺpcov ako pole2.

Syntax: MULT(pole1, pole2).

Pole1, pole2 -- vynásobené polia.

Po zadaní funkcie do ľavej hornej bunky rozsahu poľa vyberte pole, počnúc bunkou obsahujúcou vzorec, stlačte kláves F2 a potom stlačte klávesy CTRL+SHIFT+ENTER.

3. TRANSPOSE - prevedie vertikálnu sadu buniek na horizontálnu, alebo naopak. Výsledkom použitia tejto funkcie je pole s počtom riadkov rovným počtu stĺpcov v pôvodnom poli a počtom stĺpcov rovným počtu riadkov v počiatočnom poli.

Ktorý nachádza najširšie uplatnenie v rôznych oblastiach vedy a praxe. Môže to byť fyzika, chémia, biológia, ekonómia, sociológia, psychológia a tak ďalej a tak ďalej. Z vôle osudu sa často musím popasovať s ekonomikou, a preto vám dnes vybavím letenku do úžasnej krajiny tzv. Ekonometria=) ... Ako to nechceš?! Je to tam veľmi dobré - stačí sa rozhodnúť! ...Ale to, čo pravdepodobne určite chcete, je naučiť sa riešiť problémy najmenších štvorcov. A hlavne usilovní čitatelia sa ich naučia riešiť nielen presne, ale aj VEĽMI RÝCHLO ;-) Ale najskôr všeobecné vyjadrenie problému+ súvisiaci príklad:

Nech sa študujú ukazovatele v nejakej tematickej oblasti, ktoré majú kvantitatívne vyjadrenie. Zároveň existujú všetky dôvody domnievať sa, že ukazovateľ závisí od ukazovateľa. Tento predpoklad môže byť vedeckou hypotézou aj založenou na elementárnom zdravom rozume. Nechajme však vedu bokom a preskúmajme chutnejšie oblasti – menovite obchody s potravinami. Označiť podľa:

– obchodný priestor predajne potravín, m2,
- ročný obrat obchodu s potravinami, milióny rubľov.

Je úplne jasné, že čím väčšia je plocha predajne, tým väčší je jej obrat vo väčšine prípadov.

Predpokladajme, že po vykonaní pozorovaní / experimentov / výpočtov / tanca s tamburínou máme k dispozícii číselné údaje:

Pri obchodoch s potravinami je myslím všetko jasné: - toto je plocha 1. predajne, - jej ročný obrat, - plocha 2. predajne, - jej ročný obrat atď. Mimochodom, nie je vôbec potrebné mať prístup k utajovaným materiálom - pomerne presné vyhodnotenie obratu možno získať pomocou matematickej štatistiky. Nenechajte sa však rozptyľovať, kurz komerčnej špionáže je už zaplatený =)

Tabuľkové údaje môžu byť zapísané aj vo forme bodov a zobrazené pre nás obvyklým spôsobom. karteziánsky systém .

Odpovedzme si na dôležitú otázku: koľko bodov je potrebných na kvalitatívnu štúdiu?

Čím väčšie, tým lepšie. Minimálny prípustný set pozostáva z 5-6 bodov. Okrem toho pri malom množstve údajov by do vzorky nemali byť zahrnuté „abnormálne“ výsledky. Takže napríklad malý elitný obchod môže pomôcť rádovo viac ako „ich kolegovia“, čím skresľuje všeobecný vzorec, ktorý je potrebné nájsť!

Ak je to celkom jednoduché, musíme vybrať funkciu, harmonogram ktorý prechádza čo najbližšie k bodom . Takáto funkcia sa nazýva aproximácia (aproximácia - aproximácia) alebo teoretická funkcia . Vo všeobecnosti sa tu okamžite objaví zrejmý „predstierač“ - polynóm vysokého stupňa, ktorého graf prechádza VŠETKÝMI bodmi. Táto možnosť je však komplikovaná a často jednoducho nesprávna. (pretože graf sa bude neustále „navíjať“ a zle odráža hlavný trend).

Požadovaná funkcia teda musí byť dostatočne jednoduchá a zároveň primerane odrážať závislosť. Ako asi tušíte, jedna z metód na nájdenie takýchto funkcií je tzv najmenších štvorcov. Najprv analyzujme jeho podstatu všeobecný pohľad. Nechajte nejakú funkciu aproximovať experimentálne údaje:


Ako vyhodnotiť presnosť tejto aproximácie? Vypočítajme aj rozdiely (odchýlky) medzi experimentálnymi a funkčnými hodnotami (študujeme kresbu). Prvá myšlienka, ktorá príde na myseľ, je odhadnúť, aká veľká je suma, ale problém je, že rozdiely môžu byť negatívne. (Napríklad, ) a odchýlky v dôsledku takéhoto súčtu sa navzájom vyrušia. Preto sa ako odhad presnosti aproximácie navrhuje použiť súčet modulov odchýlky:

alebo v zloženom tvare: (zrazu, kto nevie: je ikona súčtu a je to pomocná premenná - „počítadlo“, ktoré nadobúda hodnoty od 1 do ).

Aproximáciou experimentálnych bodov s rôznymi funkciami získame rôzne hodnoty a je zrejmé, že kde je tento súčet menší, je táto funkcia presnejšia.

Takáto metóda existuje a volá sa metóda najmenšieho modulu. V praxi sa však výrazne rozšíril. metóda najmenších štvorcov, v ktorom možné záporné hodnoty nie sú eliminované modulom, ale kvadratúrou odchýlok:

, po ktorom úsilie smeruje k výberu takej funkcie, aby súčet kvadrátov odchýlok bol čo najmenší. Odtiaľ vlastne pochádza aj názov metódy.

A teraz sme späť pri ďalšej dôležitý bod: ako je uvedené vyššie, zvolená funkcia by mala byť pomerne jednoduchá - existuje však aj veľa takýchto funkcií: lineárne , hyperbolický, exponenciálny, logaritmický, kvadratický atď. A, samozrejme, tu by som okamžite rád „zmenšil pole pôsobnosti“. Akú triedu funkcií zvoliť pre výskum? Primitívna, ale účinná technika:

- Najjednoduchší spôsob kreslenia bodov na výkrese a analyzovať ich umiestnenie. Ak majú tendenciu byť v priamej línii, mali by ste hľadať priamka rovnica s optimálnymi hodnotami a . Inými slovami, úlohou je nájsť TAKÉTO koeficienty – aby súčet kvadrátov odchýlok bol čo najmenší.

Ak sú body umiestnené napr hyperbola, potom je jasné, že lineárna funkcia poskytne zlú aproximáciu. V tomto prípade hľadáme „najvýhodnejšie“ koeficienty pre rovnicu hyperboly - tie, ktoré dávajú minimálny súčet štvorcov .

Teraz si všimnite, že v oboch prípadoch hovoríme o funkcie dvoch premenných, ktorých argumenty sú hľadal možnosti závislosti:

A v podstate potrebujeme vyriešiť štandardný problém – nájsť minimálne funkcie dvoch premenných.

Pripomeňme si náš príklad: Predpokladajme, že body „obchodu“ majú tendenciu byť umiestnené v priamej línii a existuje každý dôvod domnievať sa, že existuje lineárna závislosť obrat z obchodnej oblasti. Nájdite TAKÉTO koeficienty "a" a "be" tak, aby bol súčet kvadrátov odchýlok bol najmenší. Všetko ako obvykle - prvé parciálne deriváty 1. rádu. Podľa pravidlo linearity môžete rozlišovať priamo pod ikonou sumy:

Ak chcete použiť tieto informácie na esej alebo semestrálnu prácu, budem veľmi vďačný za odkaz v zozname zdrojov, nikde nenájdete také podrobné výpočty:

Urobme štandardný systém:

Každú rovnicu zredukujeme o „dvojku“ a navyše „rozdelíme“ súčty:

Poznámka : nezávisle analyzovať, prečo je možné z ikony súčtu vyňať „a“ a „byť“. Mimochodom, formálne sa to dá urobiť so sumou

Prepíšme systém do „aplikovanej“ formy:

potom sa začne kresliť algoritmus na riešenie nášho problému:

Poznáme súradnice bodov? Vieme. Sumy môžeme nájsť? Jednoducho. Skladáme to najjednoduchšie sústava dvoch lineárnych rovníc s dvoma neznámymi("a" a "beh"). Systém riešime napr. Cramerova metóda, výsledkom čoho je stacionárny bod . Kontrola postačujúca podmienka pre extrém, môžeme overiť, že v tomto bode je funkcia dosiahne presne minimálne. Overenie je spojené s dodatočnými výpočtami a preto ho necháme v zákulisí. (v prípade potreby je možné zobraziť chýbajúci rámček). Vyvodzujeme konečný záver:

Funkcia najlepšia cesta (aspoň v porovnaní s akoukoľvek inou lineárnou funkciou) približuje experimentálne body . Zhruba povedané, jeho graf prechádza čo najbližšie k týmto bodom. V tradícii ekonometrie sa nazýva aj výsledná aproximačná funkcia párová lineárna regresná rovnica .

Uvažovaný problém má veľký praktický význam. V situácii s naším príkladom, rovnica umožňuje predpovedať, aký druh obratu ("yig") bude v predajni s jednou alebo druhou hodnotou predajnej plochy (jeden alebo iný význam "x"). Áno, výsledná predpoveď bude len predpoveďou, no v mnohých prípadoch sa ukáže ako celkom presná.

Rozoberiem len jeden problém so „skutočnými“ číslami, keďže v ňom nie sú žiadne ťažkosti – všetky výpočty sú na úrovni školských osnov v 7. – 8. ročníku. V 95 percentách prípadov budete vyzvaní, aby ste našli len lineárnu funkciu, ale na samom konci článku ukážem, že nájsť rovnice pre optimálnu hyperbolu, exponent a niektoré ďalšie funkcie nie je o nič zložitejšie.

V skutočnosti zostáva rozdávať sľúbené dobroty – aby ste sa naučili takéto príklady riešiť nielen presne, ale aj rýchlo. Starostlivo študujeme štandard:

Úloha

Ako výsledok štúdia vzťahu medzi dvoma ukazovateľmi sa získali nasledujúce dvojice čísel:

Pomocou metódy najmenších štvorcov nájdite lineárnu funkciu, ktorá najlepšie aproximuje empirickú funkciu (skúsený)údajov. Vytvorte výkres, na ktorom v karteziánskom pravouhlom súradnicovom systéme nakreslite experimentálne body a graf aproximačnej funkcie . Nájdite súčet štvorcových odchýlok medzi empirickými a teoretickými hodnotami. Zistite, či je funkcia lepšia (v zmysle metódy najmenších štvorcov) približné experimentálne body.

Všimnite si, že hodnoty „x“ sú prirodzené hodnoty a to má charakteristický zmysluplný význam, o ktorom budem hovoriť o niečo neskôr; ale, samozrejme, môžu byť zlomkové. Okrem toho v závislosti od obsahu konkrétnej úlohy môžu byť hodnoty „X“ aj „G“ úplne alebo čiastočne záporné. Dostali sme „netvárnu“ úlohu a začíname s ňou Riešenie:

Nájdeme koeficienty optimálnej funkcie ako riešenie systému:

Na účely kompaktnejšieho zápisu možno premennú „counter“ vynechať, pretože už je jasné, že sčítanie sa vykonáva od 1 do .

Je vhodnejšie vypočítať požadované množstvá v tabuľkovej forme:


Výpočty je možné vykonávať na mikrokalkulačke, ale oveľa lepšie je použiť Excel - rýchlejšie a bez chýb; pozrite si krátke video:

Dostávame teda nasledovné systém:

Tu môžete vynásobiť druhú rovnicu 3 a odčítajte 2. od 1. rovnice člen po člene. Ale to je šťastie - v praxi systémy často nie sú nadané a v takýchto prípadoch šetrí Cramerova metóda:
, takže systém má jedinečné riešenie.

Urobme kontrolu. Chápem, že to nechcem, ale prečo preskakovať chyby tam, kde si ich nemôžete nechať ujsť? Nájdené riešenie dosaďte na ľavú stranu každej rovnice systému:

Získajú sa správne časti zodpovedajúcich rovníc, čo znamená, že systém je vyriešený správne.

Požadovaná aproximačná funkcia: – od všetky lineárne funkcie najlepšie sa ním priblížia experimentálne údaje.

Na rozdiel od rovno závislosť obratu predajne od jej plochy, zistená závislosť je obrátene (zásada „čím viac – tým menej“), a túto skutočnosť okamžite odhalí negatív uhlový koeficient. Funkcia nás informuje, že so zvýšením určitého ukazovateľa o 1 jednotku sa hodnota závislého ukazovateľa znižuje priemer o 0,65 jednotky. Ako sa hovorí, čím vyššia je cena pohánky, tým menej sa predáva.

Na vykreslenie aproximačnej funkcie nájdeme dve jej hodnoty:

a vykonajte kreslenie:


Vybudovaná čiara je tzv trendová čiara (konkrétne lineárna trendová čiara, t. j. vo všeobecnom prípade trend nemusí byť nevyhnutne priamka). Výraz „byť v trende“ pozná každý a myslím si, že tento výraz nepotrebuje ďalší komentár.

Vypočítajte súčet štvorcových odchýlok medzi empirickými a teoretickými hodnotami. Geometricky ide o súčet druhých mocnín dĺžok „karmínových“ segmentov (dve z nich sú také malé, že ich ani nevidíte).

Zhrňme si výpočty do tabuľky:


Môžu byť opäť vykonané ručne, len v prípade, že uvediem príklad pre 1. bod:

ale oveľa efektívnejšie je urobiť už známy spôsob:

Zopakujme si: aký je zmysel výsledku? Od všetky lineárne funkcie funkciu exponent je najmenší, to znamená, že je to najlepšia aproximácia vo svojej rodine. A tu, mimochodom, posledná otázka problému nie je náhodná: čo ak navrhovaná exponenciálna funkcia bude lepšie aproximovať experimentálne body?

Nájdite zodpovedajúci súčet štvorcových odchýlok - aby som ich rozlíšil, označím ich písmenom "epsilon". Technika je úplne rovnaká:


A opäť pre každý výpočet požiaru pre 1. bod:

V Exceli používame štandardnú funkciu EXP (Syntax nájdete v Pomocníkovi programu Excel).

Záver: , takže exponenciálna funkcia aproximuje experimentálne body horšie ako priamka .

Tu však treba poznamenať, že „horšie“ je ešte neznamená, čo je zle. Teraz som vytvoril graf tejto exponenciálnej funkcie - a tiež prechádza blízko k bodom - natoľko, že bez analytickej štúdie je ťažké povedať, ktorá funkcia je presnejšia.

Tým je riešenie dokončené a vraciam sa k otázke prirodzených hodnôt argumentu. V rôznych štúdiách sú spravidla ekonomické alebo sociologické mesiace, roky alebo iné rovnaké časové intervaly očíslované prirodzeným „X“. Zvážte napríklad takýto problém.

No v práci sa hlásili na inšpekciu, článok písali doma na konferenciu - teraz môžete písať do blogu. Keď som spracovával svoje údaje, uvedomil som si, že nemôžem nenapísať o veľmi skvelom a potrebnom doplnku v Exceli, ktorý sa nazýva . Takže článok bude venovaný tomuto konkrétnemu doplnku a poviem vám o ňom na príklade použitia metóda najmenších štvorcov(LSM) hľadať neznáme koeficienty rovnice pri popise experimentálnych dát.

Ako povoliť doplnok „hľadať riešenie“

Po prvé, poďme zistiť, ako povoliť tento doplnok.

1. Prejdite do ponuky „Súbor“ a vyberte „Možnosti programu Excel“

2. V okne, ktoré sa zobrazí, zvoľte „Vyhľadať riešenie“ a kliknite na „prejsť“.

3. V ďalšom okne zaškrtnite položku „hľadať riešenie“ a kliknite na „OK“.

4. Doplnok je aktivovaný - teraz ho nájdete v položke menu "Údaje".

Metóda najmenších štvorcov

Teraz stručne o metóda najmenších štvorcov (LSM) a kde sa dá uplatniť.

Povedzme, že máme súbor údajov po vykonaní nejakého experimentu, v ktorom sme študovali účinky hodnoty X na hodnotu Y.

Chceme tento vplyv opísať matematicky, aby sme neskôr mohli použiť tento vzorec a vedeli, že ak zmeníme hodnotu X o toľko, dostaneme hodnotu Y také a také ...

Zoberme si super jednoduchý príklad (pozri obrázok).

Niet zamyslenia nad tým, že body sú umiestnené za sebou akoby v priamke, a preto bezpečne predpokladáme, že naša závislosť je opísaná lineárnou funkciou y=kx+b. Zároveň sme si istí, že keď sa X rovná nule, hodnota Y sa tiež rovná nule. To znamená, že funkcia popisujúca závislosť bude ešte jednoduchšia: y=kx (pamätajte na školské osnovy).

Vo všeobecnosti musíme nájsť koeficient k. To je to, s čím budeme robiť MNC pomocou doplnku „hľadať riešenie“.

Metóda spočíva v tom, že (tu - pozor: musíte o tom premýšľať) súčet štvorcových rozdielov medzi experimentálne získanými a zodpovedajúcimi vypočítanými hodnotami bol minimálny. To znamená, že keď X1=1 skutočná nameraná hodnota Y1=4,6 a vypočítané y1=f (x1) je 4, druhá mocnina rozdielu bude (y1-Y1)^2=(4-4,6)^2= 0,36. To isté platí pre nasledujúce: keď X2=2, skutočná nameraná hodnota Y2=8,1 a vypočítané y2 je 8, druhá mocnina rozdielu bude (y2-Y2)^2=(8-8,1)^2=0,01. A súčet všetkých týchto štvorcov by mal byť čo najmenší.

Začnime teda trénovať používanie LSM a Doplnky programu Excel „hľadajte riešenie“ .

Aplikácia doplnku nájsť riešenie

1. Ak ste nepovolili doplnok „hľadať riešenie“, vráťte sa na krok Ako povoliť doplnok „hľadať riešenie“ a povoliť 🙂

2. Do bunky A1 zadajte hodnotu "1". Táto jednotka bude prvým priblížením k reálnej hodnote koeficientu (k) našej funkčnej závislosti y=kx.

3. V stĺpci B máme hodnoty parametra X, v stĺpci C - hodnoty parametra Y. Do buniek stĺpca D zadáme vzorec: „koeficient k vynásobený hodnotou X“. Napríklad do bunky D1 zadajte „=A1*B1“, do bunky D2 zadajte „=A1*B2“ atď.

4. Veríme, že koeficient k sa rovná jednej a funkcia f (x) \u003d y \u003d 1 * x je prvou aproximáciou nášho riešenia. Môžeme vypočítať súčet štvorcových rozdielov medzi nameranými hodnotami Y a hodnotami vypočítanými pomocou vzorca y=1*x. Toto všetko môžeme urobiť manuálne zaradením príslušných odkazov na bunky do vzorca: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... atď. sa mýlime a chápeme, že sme stratili veľa času. V Exceli existuje na výpočet súčtu druhých mocnín rozdielov špeciálny vzorec „SUMQDIFF“, ktorý urobí všetko za nás. Zadáme ho do bunky A2 a nastavíme počiatočné údaje: rozsah nameraných hodnôt Y (stĺpec C) a rozsah vypočítaných hodnôt Y (stĺpec D).

4. Vypočítal sa súčet rozdielov štvorcov – teraz prejdite na kartu „Údaje“ a vyberte „Hľadať riešenie“.

5. V zobrazenej ponuke vyberte bunku A1 ako bunku, ktorú chcete zmeniť (tú s koeficientom k).

6. Ako cieľ vyberte bunku A2 a nastavte podmienku "nastaviť rovnú minimálnej hodnote." Pamätajte, že toto je bunka, kde vypočítame súčet druhých mocnín rozdielov medzi vypočítanými a nameranými hodnotami a táto suma by mala byť minimálna. Stlačíme "vykonať".

7. Je zvolený koeficient k. Teraz je vidieť, že vypočítané hodnoty sú teraz veľmi blízko k nameraným.

P.S.

Vo všeobecnosti, samozrejme, na aproximáciu experimentálnych údajov v Exceli existujú špeciálne nástroje, ktoré umožňujú popísať údaje pomocou lineárnej, exponenciálnej, mocninovej a polynomiálnej funkcie, takže sa často zaobídete bez doplnky "Hľadať riešenie". O všetkých týchto metódach aproximácie som hovoril vo svojom článku, takže ak máte záujem, pozrite sa. Ale keď už ide o nejakú exotickú funkciu s jedným neznámym koeficientom alebo problémy s optimalizáciou, potom tu nadstavbačo najlepšie.

Doplnok "hľadať riešenie" dá sa použiť aj na iné úlohy, ide hlavne o pochopenie podstaty: existuje bunka, kde vyberáme hodnotu a je tu cieľová bunka, v ktorej je nastavená podmienka pre výber neznámeho parametra.
To je všetko! V ďalšom článku vám poviem rozprávku o dovolenke, aby ste nezmeškali vydanie článku,