Príklady metódy najmenších štvorcov s riešeniami v Exceli. Metóda najmenších štvorcov v Exceli. Regresná analýza. Niekoľko slov o správnosti počiatočných údajov použitých na predikciu

Ktorý nachádza najširšie uplatnenie v rôznych oblastiach vedy a praktickej činnosti. 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 sprostredkujem výlet do úžasnej krajiny tzv. Ekonometria=) ...Ako to nechceš?! Je to tam veľmi dobré – stačí sa rozhodnúť! ...Ale to, čo asi určite chcete, je naučiť sa riešiť problémy metóda najmenších štvorcov. A hlavne pilní čitatelia sa ich naučia riešiť nielen presne, ale aj VEĽMI RÝCHLO ;-) Ale najprv všeobecné vyjadrenie problému+ sprievodný príklad:

Pozrime sa na ukazovatele v určitej 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ť buď vedecká hypotéza alebo založená na základnom zdravom rozume. Nechajme však vedu bokom a preskúmajme chutnejšie oblasti – menovite obchody s potravinami. Označme podľa:

– predajná plocha 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äčší bude vo väčšine prípadov jej obrat.

Predpokladajme, že po vykonaní pozorovaní/experimentov/výpočtov/tancov 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, vôbec nie je potrebné mať prístup k utajovaným materiálom - pomerne presné posúdenie obchodného obratu je možné získať pomocou matematickej štatistiky. Nenechajme sa však rozptyľovať, kurz komerčnej špionáže je už zaplatený =)

Tabuľkové údaje môžu byť tiež zapísané vo forme bodov a zobrazené v známej forme 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álna prijateľná sada pozostáva z 5-6 bodov. Okrem toho, keď je množstvo údajov malé, „anomálne“ výsledky nemožno zahrnúť do vzorky. Takže napríklad malý elitný obchod môže zarobiť rádovo viac ako „jeho kolegovia“, čím skresľuje všeobecný vzorec, ktorý musíte nájsť!

Veľmi zjednodušene povedané, musíme vybrať funkciu, harmonogram ktorý prechádza čo najbližšie k bodom . Táto funkcia sa nazýva aproximácia (aproximácia - aproximácia) alebo teoretická funkcia . Vo všeobecnosti sa tu okamžite objaví zjavný „súťažník“ - 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. (keďže graf sa bude neustále „zacykliť“ a zle odráža hlavný trend).

Hľadaná funkcia teda musí byť celkom 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 metóda najmenších štvorcov. Najprv sa pozrime na jeho podstatu všeobecný pohľad. Nech nejaká funkcia aproximuje experimentálne dáta:


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 v tom, ž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 ako odhad presnosti aproximácie treba brať súčet modulov odchýlky:

alebo zbalené: (pre prípad, že by niekto nevedel: – toto je ikona súčtu a – 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 samozrejme, ak je tento súčet menší, je táto funkcia presnejšia.

Takáto metóda existuje a je tzv 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 sú snahy zamerané na výber funkcie takej, že súčet štvorcových odchýlok bol čo najmenší. V skutočnosti odtiaľ pochádza názov metódy.

A teraz sa vrátime k niečomu inému 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 chcel okamžite „zmenšiť pole pôsobnosti“. Ktorú triedu funkcií by som si mal vybrať pre výskum? Primitívna, ale účinná technika:

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

Ak sú body umiestnené napr hyperbola, potom je samozrejme jasné, že lineárna funkcia poskytne zlú aproximáciu. V tomto prípade hľadáme „najpriaznivejš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ú vyhľadávané parametre závislosti:

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

Spomeňme si na náš príklad: predpokladajme, že „ukladacie“ body majú tendenciu byť umiestnené v priamke a existujú všetky dôvody domnievať sa, že lineárna závislosť obrat z maloobchodných priestorov. Nájdite TAKÉTO koeficienty „a“ ​​a „be“ také, že sú to súčet kvadrátov odchýlok bol najmenší. Všetko je ako obvykle - prvé Parciálne deriváty 1. rádu. Podľa pravidlo linearity Priamo pod ikonou sumy môžete rozlišovať:

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

Vytvorme štandardný systém:

Každú rovnicu znížime o „dve“ a navyše „rozdelíme“ súčty:

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

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

po ktorom sa začína objavovať algoritmus na riešenie nášho problému:

Poznáme súradnice bodov? Vieme. čiastky môžeme to nájsť? Jednoducho. Urobme to najjednoduchšie sústava dvoch lineárnych rovníc o dvoch neznámych(„a“ a „byť“). Systém riešime napr. Cramerova metóda, v dôsledku čoho získame stacionárny bod. Kontrola postačujúca podmienka pre extrém, môžeme overiť, že v tomto bode je funkcia dosiahne presne minimálne. Kontrola zahŕňa dodatočné výpočty, a preto ju necháme v zákulisí (v prípade potreby je možné zobraziť chýbajúci rám). 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 našej príkladnej situácii, Eq. umožňuje predpovedať, aký obchodný obrat ("Igrek") obchod bude mať jednu alebo druhú hodnotu 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á.

Budem analyzovať iba jeden problém so „skutočnými“ číslami, pretože v ňom nie sú žiadne ťažkosti - všetky výpočty sú na úrovni školských osnov pre 7. - 8. ročník. 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 optimálnej hyperboly, exponenciálnej a niektorých ďalších funkcií nie je o nič ťažšie.

Vlastne ostáva už len rozdávať sľúbené dobroty – aby ste sa takéto príklady naučili 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 nákres, na ktorom zostrojíte experimentálne body a graf aproximačnej funkcie v kartézskom pravouhlom súradnicovom systéme . Nájdite súčet štvorcových odchýlok medzi empirickými a teoretickými hodnotami. Zistite, či by funkcia bola lepšia (z pohľadu metódy najmenších štvorcov) priblížiť experimentálne body.

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

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

Pre účely kompaktnejšieho záznamu 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ému:

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ú darom a v takýchto prípadoch šetrí Cramerova metóda:
, čo znamená, že systém má jedinečné riešenie.

Skontrolujme to. Chápem, že to nechcete, ale prečo preskakovať chyby tam, kde ich absolútne nemožno vynechať? Nájdené riešenie dosadíme na ľavú stranu každej rovnice systému:

Získajú sa pravé strany 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 Je to ona, ktorá najlepšie aproximuje 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 sklon. Funkcia nám hovorí, ž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 grafu aproximačnej funkcie nájdeme jej dve hodnoty:

a vykonajte kreslenie:


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

Vypočítajme súčet štvorcových odchýlok medzi empirickými a teoretickými hodnotami. Geometricky je to súčet druhých mocnín dĺžok „malinových“ segmentov (dve z nich sú také malé, že ich ani nevidno).

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


Opäť sa dajú urobiť ručne; pre každý prípad uvediem príklad pre 1. bod:

ale oveľa efektívnejšie je to urobiť už známym spôsobom:

Opakujeme ešte raz: Aký je význam získaného výsledku? Od všetky lineárne funkcie y funkciu ukazovateľ je najmenší, to znamená, že vo svojej rodine je to najlepšia aproximácia. A tu, mimochodom, posledná otázka problému nie je náhodná: čo ak navrhovaná exponenciálna funkcia bolo by lepšie priblížiť experimentálne body?

Nájdite zodpovedajúci súčet štvorcových odchýlok - na rozlíšenie ich označím písmenom „epsilon“. Technika je úplne rovnaká:


A ešte raz, pre každý prípad, výpočty pre 1. bod:

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

Záver: , čo znamená, ž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 analytického výskumu je ťažké povedať, ktorá funkcia je presnejšia.

Toto uzatvára riešenie a vraciam sa k otázke prirodzených hodnôt argumentu. V rôznych štúdiách, zvyčajne ekonomických alebo sociologických, sa prirodzené „X“ používajú na číslovanie mesiacov, rokov alebo iných rovnakých časových intervalov. Zvážte napríklad nasledujúci problém.

Metóda najmenších štvorcov (LS) je založená na minimalizácii súčtu štvorcových odchýlok vybranej funkcie od skúmaných údajov. V tomto článku aproximujeme dostupné údaje pomocou lineárnej funkcier = a X + b .

Metóda najmenších štvorcov(Angličtina) Obyčajný Najmenej Štvorce , O.L.S.) je jednou zo základných metód regresnej analýzy z hľadiska odhadu neznámych parametrov regresné modely podľa vzorových údajov.

Uvažujme aproximáciu pomocou funkcií, ktoré závisia iba od jednej premennej:

• Lineárne: y=ax+b (tento článok)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Poznámka: V tomto článku sa zaoberáme prípadmi aproximácie polynómom od 3. do 6. stupňa. Uvažuje sa tu o aproximácii trigonometrickým polynómom.

Lineárna závislosť

Zaujíma nás súvislosť medzi 2 premennými X A r. Existuje predpoklad, že r záleží na X podľa lineárneho zákona r = sekera + b. Na určenie parametrov tohto vzťahu výskumník vykonal pozorovania: pre každú hodnotu x i sa vykonalo meranie y i (pozri príkladový súbor). Podľa toho nech existuje 20 párov hodnôt (x i; y i).

Poznámka: Ak je krok zmeny X je konštantná, potom stavať rozptylové grafy možno použiť, ak nie, potom musíte použiť typ grafu Spot .

Z diagramu je zrejmé, že vzťah medzi premennými je takmer lineárny. Aby sme pochopili, ktorá z mnohých priamych čiar najviac „správne“ opisuje vzťah medzi premennými, je potrebné určiť kritérium, podľa ktorého sa budú čiary porovnávať.

Ako také kritérium používame výraz:

Kde ŷ i = a * x i + b ; n – počet párov hodnôt (v našom prípade n=20)

Vyššie uvedený výraz je súčtom štvorcových vzdialeností medzi pozorovanými hodnotami y i a ŷ i a často sa označuje ako SSE ( Sum z Štvorcový Chyby (Zvyšky), súčet štvorcových chýb (zvyšky)) .

Metóda najmenších štvorcov je vybrať takýto riadok ŷ = sekera + b, pre ktoré má vyššie uvedený výraz minimálnu hodnotu.

Poznámka: Akákoľvek čiara v dvojrozmernom priestore je jednoznačne určená hodnotami 2 parametrov: a (sklon) a b (posun).

Predpokladá sa, že čím menší je súčet štvorcových vzdialeností, tým lepšie zodpovedajúca čiara aproximuje dostupné údaje a môže sa ďalej použiť na predpovedanie hodnôt y z premennej x. Je jasné, že aj keď v skutočnosti neexistuje žiadny vzťah medzi premennými alebo je vzťah nelineárny, potom OLS stále vyberie „najlepší“ riadok. Metóda najmenších štvorcov teda nehovorí nič o prítomnosti skutočného vzťahu medzi premennými; metóda vám jednoducho umožňuje vybrať parametre funkcie a A b , pre ktoré je uvedený výraz minimálny.

Vykonaním nie príliš zložitých matematických operácií (podrobnejšie pozri), môžete vypočítať parametre a A b :

Ako je vidieť zo vzorca, parameter a predstavuje pomer kovariancie a teda v MS EXCEL na výpočet parametra A Môžete použiť nasledujúce vzorce (pozri Vzorový súbor lineárneho listu):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45) alebo

= COVARIANCE.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Tiež na výpočet parametra A môžete použiť vzorec = NAKLONENIE(C26:C45;B26:B45). Pre parameter b použite vzorec = LEG(C26:C45;B26:B45) .

Nakoniec funkcia LINREGRESE() umožňuje vypočítať oba parametre naraz. Ak chcete zadať vzorec LINEST(C26:C45;B26:B45) Musíte vybrať 2 bunky v rade a kliknúť CTRL + SHIFT + ENTER(pozri článok o). Hodnota sa vráti do ľavej bunky A , napravo - b .

Poznámka: Aby ste sa vyhli zápletkám so vstupom vzorce poľa budete musieť dodatočne použiť funkciu INDEX(). Vzorec = INDEX(LINEST(C26:C45;B26:B45);1) alebo len = LINEST(C26:C45;B26:B45) vráti parameter zodpovedný za sklon čiary, t.j. A . Vzorec = INDEX(LINEST(C26:C45;B26:B45);2) vráti parameter zodpovedný za priesečník priamky s osou Y, t.j. b .

Po výpočte parametrov, Rozptylový diagram môžete nakresliť príslušnú čiaru.

Ďalším spôsobom, ako vykresliť priamku pomocou metódy najmenších štvorcov, je nástroj grafu Trendová línia. Ak to chcete urobiť, vyberte diagram, vyberte z ponuky Karta rozloženie, V skupinová analýza kliknite Trendová línia, potom Lineárna aproximácia .

Začiarknutím políčka „zobraziť rovnicu v diagrame“ v dialógovom okne sa môžete uistiť, že parametre uvedené vyššie sa zhodujú s hodnotami v diagrame.

Poznámka: Aby sa parametre zhodovali, typ diagramu musí byť . Ide o to, že pri konštrukcii diagramu Rozvrh Hodnoty osi X nemôže zadať používateľ (používateľ môže zadať iba označenia, ktoré neovplyvňujú umiestnenie bodov). Namiesto hodnôt X sa používa sekvencia 1; 2; 3; ... (pre číslovanie kategórií). Preto, ak staviate trendová čiara na typovom diagrame Rozvrh, potom sa namiesto skutočných hodnôt X použijú hodnoty tejto sekvencie, čo povedie k nesprávnemu výsledku (pokiaľ sa, samozrejme, skutočné hodnoty X nezhodujú so sekvenciou 1; 2; 3; ...).

Metóda najmenších štvorcov je matematický postup na zostavenie lineárnej rovnice, ktorá bude najpresnejšie zodpovedať množine dvoch radov čísel. Účelom použitia tejto metódy je minimalizovať celkovú štvorcovú chybu. Excel má nástroje, ktoré vám môžu pomôcť použiť túto metódu na vaše výpočty. Poďme zistiť, ako sa to robí.

· Použitie metódy v Exceli

o Povolenie doplnku „Solution Search“.

o Problémové stavy

o Riešenie

Použitie metódy v Exceli

Metóda najmenších štvorcov (LSM) je matematický popis závislosti jednej premennej od druhej. Dá sa použiť na predpovedanie.

Povolenie doplnku Nájsť riešenie

Ak chcete používať MNC v Exceli, musíte povoliť doplnok "Hľadanie riešenia", ktorá je predvolene vypnutá.

1. Prejdite na kartu "Súbor".

2. Kliknite na názov sekcie "Možnosti".

3. V okne, ktoré sa otvorí, vyberte podsekciu "Doplnky".

4. V bloku "ovládanie", ktorý sa nachádza v spodnej časti okna, nastavte spínač do polohy "Doplnky programu Excel"(ak má inú hodnotu) a kliknite na tlačidlo "Choď...".

5. Otvorí sa malé okno. Vedľa parametra dáme začiarknutie "Hľadanie riešenia". Kliknite na tlačidlo "OK".

Teraz funkcia Hľadanie riešenia v Exceli je aktivovaný a jeho nástroje sa zobrazia na páse s nástrojmi.

lekcia: Hľadanie riešenia v Exceli

Podmienky problému

Opíšme použitie LSM na konkrétnom príklade. Máme dva rady čísel X A r, ktorej postupnosť je znázornená na obrázku nižšie.

Túto závislosť možno najpresnejšie opísať funkciou:

Zároveň je známe, že kedy x = 0 r tiež rovný 0 . Preto možno túto rovnicu opísať závislosťou y=nx.

Musíme nájsť minimálny súčet druhých mocnín rozdielu.

Riešenie

Prejdime k popisu priamej aplikácie metódy.

1. Naľavo od prvej hodnoty X dať číslo 1 . Bude to približná hodnota hodnoty prvého koeficientu n.

2. Napravo od stĺpca r pridať ďalší stĺpec - nx. Do prvej bunky tohto stĺpca napíšeme vzorec na násobenie koeficientu n na bunku prvej premennej X. Zároveň urobíme prepojenie na pole s koeficientom absolútnym, keďže táto hodnota sa nezmení. Kliknite na tlačidlo Zadajte.

3. Pomocou značky výplne skopírujte tento vzorec pre celý rozsah tabuľky v stĺpci nižšie.

4. V samostatnej bunke vypočítajte súčet rozdielov medzi štvorcami hodnôt r A nx. Ak to chcete urobiť, kliknite na tlačidlo "Vložiť funkciu".

5. V otvorenom "Sprievodca funkciami" hľadá vstup "SUMMKVARNA". Vyberte ho a stlačte tlačidlo "OK".

6. Otvorí sa okno s argumentmi. V teréne "Pole_x" r. V teréne "Array_y" zadajte rozsah buniek stĺpca nx. Ak chcete zadať hodnoty, jednoducho umiestnite kurzor do poľa a vyberte zodpovedajúci rozsah na hárku. Po zadaní kliknite na tlačidlo "OK".

7. Prejdite na kartu "údaje". Na páse s nástrojmi "analýza" kliknite na tlačidlo "Hľadanie riešenia".

8. Otvorí sa okno parametrov tohto nástroja. V teréne „Optimalizácia cieľovej funkcie“ uveďte adresu bunky so vzorcom "SUMMKVARNA". V parametri "Pred" nezabudnite nastaviť prepínač do polohy "minimálne". V teréne "Zmena buniek" uveďte adresu s hodnotou koeficientu n. Kliknite na tlačidlo "Nájsť riešenie".

9. Riešenie sa zobrazí v bunke koeficientu n. Táto hodnota bude najmenším štvorcom funkcie. Ak výsledok uspokojí používateľa, kliknite na tlačidlo "OK" v dodatočnom okne.

Ako vidíte, aplikácia metódy najmenších štvorcov je pomerne zložitý matematický postup. Ukázali sme to v praxi na jednoduchom príklade, no je ich oveľa viac zložité prípady. Nástroje Microsoft Excel sú však navrhnuté tak, aby čo najviac zjednodušili výpočty.

http://multitest.semico.ru/mnk.htm

Všeobecné ustanovenia

Čím menšie číslo v absolútnej hodnote, tým lepšia je zvolená priamka (2). Ako charakteristiku presnosti výberu priamky (2) môžeme vziať súčet štvorcov

Minimálne podmienky pre S budú

	(6)
	(7)

Rovnice (6) a (7) možno napísať takto:

	(8)
	(9)

Z rovníc (8) a (9) je ľahké nájsť a a b z experimentálnych hodnôt xi a y i. Priamka (2), definovaná rovnicami (8) a (9), sa nazýva priamka získaná metódou najmenších štvorcov (tento názov zdôrazňuje, že súčet štvorcov S má minimum). Rovnice (8) a (9), z ktorých je určená priamka (2), sa nazývajú normálne rovnice.

Môžete uviesť jednoduchý a všeobecný spôsob skladania normálnych rovníc. Pomocou experimentálnych bodov (1) a rovnice (2) môžeme napísať sústavu rovníc pre a a b

Vynásobme ľavú a pravú stranu každej z týchto rovníc koeficientom prvej neznámej a (t.j. x 1, x 2, ..., x n) a výsledné rovnice sčítajme, čím vznikne prvá normálna rovnica (8) .

Vynásobme ľavú a pravú stranu každej z týchto rovníc koeficientom druhej neznámej b, t.j. o 1 a pridajte výsledné rovnice, výsledkom je druhá normálna rovnica (9).

Tento spôsob získavania normálnych rovníc je všeobecný: je vhodný napríklad pre funkciu

existuje konštantná hodnota a musí sa určiť z experimentálnych údajov (1).

Systém rovníc pre k možno napísať:

Nájdite priamku (2) metódou najmenších štvorcov.

Riešenie. Nájdeme:

Xi = 21, yi = 46,3, xi2 = 91, xi yi = 179,1.

Napíšeme rovnice (8) a (9)91a+21b=179,1,

21a+6b=46,3, odtiaľto nájdeme
a = 0,98 b = 4,3.

No v práci sme sa hlásili na inšpekciu, článok bol napísaný doma na konferenciu - teraz môžeme písať na blog. Keď som spracovával svoje údaje, uvedomil som si, že nemôžem nepísať o veľmi skvelom a potrebnom doplnku v Exceli s názvom . 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) na hľadanie neznámych koeficientov rovníc pri popise experimentálnych údajov.

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čiarknite políčko vedľa „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á použiť.

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

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

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

Netreba zabúdať na to, že body sú umiestnené jeden za druhým akoby na 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 úplne 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ľadanie riešení“.

Metóda spočíva v tom, že (tu - pozor: musíte o tom premýšľať) súčet štvorcov rozdielov medzi experimentálne získanými a zodpovedajúcimi vypočítanými hodnotami je minimálny. To znamená, že keď X1=1 skutočná nameraná hodnota Y1=4,6 a vypočítané y1=f (x1) sa rovná 4, druhá mocnina rozdielu bude (y1-Y1)^2=(4-4,6)^ 2 = 0,36. Je to rovnaké s nasledujúcim: 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“ .

Použitie doplnku na nájdenie riešenia

1. Ak ste nepovolili doplnok „hľadať riešenie“, vráťte sa k bodu Ako povoliť doplnok „hľadať riešenie“ a zapnúť ho 🙂

2. Do bunky A1 zadajte hodnotu „1“. Táto jednotka bude prvým priblížením k reálnej hodnote koeficientu (k) nášho funkčného vzťahu y=kx.

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

4. Veríme, že koeficient k sa rovná jednej a funkcia f (x)=y=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. To všetko môžeme urobiť manuálne zadaním príslušných odkazov na bunky do vzorca: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... atď. pomýlime sa a uvedomíme si, že sme premrhali veľa času. Na výpočet súčtu druhých mocnín rozdielov v Exceli existuje špeciálny vzorec „SUMQUARRENT“, ktorý urobí všetko za nás. Zadajte ho do bunky A2 a nastavte 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. Súčet rozdielov štvorcov bol vypočítaný – teraz prejdite na kartu „Údaje“ a vyberte „Hľadať riešenie“.

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

6. Ako cieľ vyberte bunku A2 a nastavte podmienku „nastaviť na minimálnu hodnotu“. Pamätáme si, že toto je bunka, kde vypočítame súčet druhých mocnín rozdielov medzi vypočítanými a nameranými hodnotami, pričom tento súčet by mal byť minimálny. Kliknite na „vykonať“.

7. Koeficient k bol zvolený. Teraz si môžete overiť, že vypočítané hodnoty sú teraz veľmi blízke nameraným.

P.S.

Vo všeobecnosti, samozrejme, na aproximáciu experimentálnych údajov v Exceli existujú špeciálne nástroje, ktoré vám umožňujú popísať údaje pomocou lineárnych, exponenciálnych, mocninových a polynomických funkcií, takže sa často zaobídete bez doplnky „hľadať riešenie“.. Hovoril som o všetkých týchto aproximačných metódach v mojom, 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 nemohol prísť v lepšom čase.

Doplnok vyhľadávania riešení môže byť použitý na iné úlohy, hlavné je pochopiť podstatu: existuje bunka, kde vyberáme hodnotu, a je tu cieľová bunka, v ktorej je zadaná 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 uverejnenie článku,

4.1. Používanie vstavaných funkcií

Kalkulácia regresné koeficienty vykonávané pomocou funkcie

LINEST(Hodnoty_y; x-hodnoty; Konšt; štatistiky),

Hodnoty_y- pole hodnôt y,

x-hodnoty- voliteľné pole hodnôt X, ak pole X je vynechané, predpokladá sa, že ide o pole (1;2;3;...) rovnakej veľkosti ako Hodnoty_y,

Konšt- boolovská hodnota, ktorá udáva, či je požadovaná konštanta b bola rovná 0. Ak Konšt má význam PRAVDA alebo vynechané, potom b sa vypočítava bežným spôsobom. Ak argument Konšt je teda NEPRAVDA b predpokladá sa 0 a hodnoty a sú vybrané tak, aby bol vzťah splnený y=ax.

Štatistiky je boolovská hodnota, ktorá označuje, či je potrebné vrátiť ďalšie regresné štatistiky. Ak argument Štatistiky má význam PRAVDA, potom funkciu LINEST vráti ďalšie regresné štatistiky. Ak argument Štatistiky má význam klamať alebo vynechané, potom funkcia LINEST vráti iba koeficient a a konštantný b.

Treba mať na pamäti, že výsledok funkcií LINEST() je množina hodnôt – pole.

Pre výpočet korelačný koeficient používa sa funkcia

CORREL(Pole1;Pole2),

vrátenie hodnôt korelačného koeficientu, kde Pole1- pole hodnôt r, Pole2- pole hodnôt X. Pole1 A Pole2 musí mať rovnakú veľkosť.

PRÍKLAD 1. Závislosť r(X) je uvedený v tabuľke. Stavať regresná čiara a vypočítať korelačný koeficient.

Zadajte tabuľku hodnôt do hárku MS Excel a vytvorte bodový graf. Pracovný list bude mať podobu znázornenú na obr. 2.

Na výpočet hodnôt regresných koeficientov A A b vyberte bunky A7:B7, Poďme do sprievodcu funkciou a do kategórie Štatistické vyberte funkciu LINEST. Vyplníme dialógové okno, ktoré sa zobrazí, ako je znázornené na obr. 3 a stlačte OK.

V dôsledku toho sa vypočítaná hodnota zobrazí iba v bunke A6(obr. 4). Aby sa hodnota objavila v bunke B6 musíte vstúpiť do režimu úprav (kláves F2) a potom stlačte kombináciu klávesov CTRL+SHIFT+ENTER.

Na výpočet hodnoty korelačného koeficientu v bunke C6 bol zavedený nasledujúci vzorec:

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

Poznanie regresných koeficientov A A b vypočítajme funkčné hodnoty r=sekera+b za dané X. Na tento účel zavedieme vzorec

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

a skopírujte ho do rozsahu C5:J5(obr. 5).

Nakreslite regresnú čiaru na diagram. Vyberte experimentálne body na grafe, kliknite pravým tlačidlom myši a vyberte príkaz Počiatočné údaje. V zobrazenom dialógovom okne (obr. 5) vyberte kartu riadok a kliknite na tlačidlo Pridať. Vyplníme vstupné polia podľa obr. 6 a stlačte tlačidlo OK. Do grafu experimentálnych údajov sa pridá regresná čiara. V predvolenom nastavení bude jeho graf nakreslený ako body, ktoré nie sú spojené vyhladzovacími čiarami.

Ryža. 6

Ak chcete zmeniť vzhľad regresnej čiary, vykonajte nasledujúce kroky. Kliknite pravým tlačidlom myši na body znázorňujúce čiarový graf a vyberte príkaz Typ grafu a nastavte typ rozptylového diagramu, ako je znázornené na obr. 7.

Typ čiary, farbu a hrúbku je možné zmeniť nasledovne. Vyberte čiaru na diagrame, kliknite pravým tlačidlom myši a vyberte príkaz v kontextovej ponuke Formát radov údajov...Ďalej vykonajte nastavenia, napríklad ako je znázornené na obr. 8.

Výsledkom všetkých transformácií získame graf experimentálnych dát a regresnú priamku v jednej grafickej oblasti (obr. 9).

4.2. Použitie trendovej čiary.

Konštrukcia rôznych aproximačných závislostí v MS Excel je implementovaná ako vlastnosť grafu - trendová čiara.

PRÍKLAD 2. Výsledkom experimentu bola stanovená určitá tabuľková závislosť.

Vyberte a vytvorte približnú závislosť. Zostrojte grafy tabuľkových a vybraných analytických závislostí.

Riešenie problému možno rozdeliť do nasledujúcich etáp: zadanie počiatočných údajov, zostavenie bodového grafu a pridanie trendovej čiary do tohto grafu.

Pozrime sa na tento proces podrobne. Zadáme počiatočné údaje do pracovného hárka a vykreslíme experimentálne údaje. Ďalej vyberte experimentálne body na grafe, kliknite pravým tlačidlom myši a použite príkaz Pridať l trendová čiara(obr. 10).

Dialógové okno, ktoré sa zobrazí, vám umožňuje vytvoriť približný vzťah.

Prvá záložka (obr. 11) tohto okna označuje typ aproximovanej závislosti.

Na druhom (obr. 12) sú určené konštrukčné parametre:

· názov aproximačnej závislosti;

· predpoveď dopredu (dozadu) o n jednotky (tento parameter určuje, o koľko jednotiek dopredu (dozadu) je potrebné predĺžiť trendovú čiaru);

či sa má zobraziť priesečník krivky s priamkou y=konšt;

· zobraziť aproximáciu funkcie na diagrame alebo nie (možnosť zobraziť rovnicu na diagrame);

· či umiestniť do diagramu hodnotu smerodajnej odchýlky alebo nie (možnosť umiestniť do diagramu hodnotu aproximačnej spoľahlivosti).

Zvoľme si polynóm druhého stupňa ako približnú závislosť (obr. 11) a rovnicu, ktorá tento polynóm popisuje, zobrazme na grafe (obr. 12). Výsledný diagram je znázornený na obr. 13.

Podobne pomocou trendové línie môžete vybrať parametre takých závislostí ako

lineárne r=a∙x+b,

logaritmický r=a∙ln(X)+b,

· exponenciálny r=a∙e b,

· upokojiť r=a∙x b,

polynóm r=a∙x 2 +b∙x+c, r=a∙x 3 +b∙x 2 +c∙x+d a tak ďalej, až po polynóm 6. stupňa vrátane,

· lineárna filtrácia.

4.3. Použitie riešiteľského bloku

Významnou zaujímavosťou je implementácia v MS Excel výberu parametrov metódou najmenších štvorcov pomocou riešiteľského bloku. Táto technika vám umožňuje vybrať parametre funkcie akéhokoľvek typu. Zvážme túto možnosť pomocou nasledujúceho problému ako príkladu.

PRÍKLAD 3. Ako výsledok experimentu bola získaná závislosť z(t), uvedená v tabuľke

Vyberte koeficienty závislosti Z(t)=A4+Bt3+Ct2+Dt+K metóda najmenších štvorcov.

Tento problém je ekvivalentný problému hľadania minima funkcie piatich premenných

Uvažujme o postupe riešenia optimalizačného problému (obr. 14).

Nechajte hodnoty A, IN, S, D A TO uložené v bunkách A7:E7. Vypočítajme teoretické hodnoty funkcie Z(t)=Pri 4 +Bt 3 +Ct 2 +Dt+K za dané t(B2:J2). Ak to chcete urobiť, v bunke B4 zadajte hodnotu funkcie v prvom bode (bunka B2):

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

Skopírujme tento vzorec do rozsahu C4:J4 a získajte očakávanú hodnotu funkcie v bodoch, ktorých úsečky sú uložené v bunkách B2:J2.

Do bunky B5 Predstavme si vzorec, ktorý vypočíta druhú mocninu rozdielu medzi experimentálnymi a vypočítanými bodmi:

B5=(B4-B3)^2,

a skopírujte ho do rozsahu C5:J5. V bunke F7 uložíme celkovú druhú druhú chybu (10). Ak to chcete urobiť, zadajte vzorec:

F7 = SUM(B5:J5).

Použime príkaz Service®Hľadať riešenie a vyriešiť problém optimalizácie bez obmedzení. Podľa toho vyplníme vstupné polia v dialógovom okne znázornenom na obr. 14 a stlačte tlačidlo Vykonať. Ak sa nájde riešenie, okno zobrazené na obr. 15.

Výsledok rozhodovacieho bloku bude odoslaný do buniek A7:E7hodnoty parametrov funkcie Z(t)=Pri 4 +Bt 3 +Ct 2 +Dt+K. V bunkách B4:J4 dostaneme očakávaná funkčná hodnota vo východiskových bodoch. V cele F7 budú uložené celková kvadratická chyba.

Výberom rozsahu môžete zobraziť experimentálne body a prispôsobenú čiaru v jednej grafickej oblasti B2:J4, zavolajte Sprievodca grafom a potom formátovať vzhľad prijaté grafy.

Ryža. 17 zobrazí pracovný hárok MS Excel po vykonaní výpočtov.

5. REFERENCIE

1. Alekseev E.R., Chesnokova O.V., Riešenie problémov výpočtovej matematiky v balíkoch Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596 s. :il. – (Návod)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, riešenie inžinierskych a matematických problémov. –M., BINOM, 2008.–260 s.

3. Berezin I.S., Zhidkov N.P., Metódy výpočtov. – M.: Nauka, 1966. – 632 s.

4. Garnaev A.Yu., Používanie MS EXCEL a VBA v ekonomike a financiách. – Petrohrad: BHV - Petersburg, 1999.–332 s.

5. Demidovich B.P., Maron I.A., Shuvalova V.Z., Numerické metódy analýzy. – M.: Nauka, 1967. – 368 s.

6. Korn G., Korn T., Príručka matematiky pre vedcov a inžinierov – M., 1970, 720 s.

7. Alekseev E.R., Chesnokova O.V. Návod na vykonávanie laboratórnych prác v MS EXCEL. Pre študentov všetkých odborov. Doneck, DonNTU, 2004. 112 s.