Výber typu regresnej funkcie, t.j. typ uvažovaného modelu závislosti Y na X (alebo X na Y), napríklad lineárny model y x = a + bx, je potrebné určiť konkrétne hodnoty koeficientov modelu.
Pre rôzne hodnoty a a b je možné zostrojiť nekonečný počet závislostí tvaru y x = a + bx, t.j. na rovine súradníc je nekonečný počet čiar, ale potrebujeme takú závislosť, ktorá zodpovedá k pozorovaným hodnotám najlepšia cesta. Problém sa teda redukuje na výber najlepších koeficientov.
Hľadáme lineárnu funkciu a + bx, založenú len na určitom počte dostupných pozorovaní. Na nájdenie funkcie, ktorá najlepšie zodpovedá pozorovaným hodnotám, používame metódu najmenších štvorcov.
Označme: Y i - hodnotu vypočítanú rovnicou Y i =a+bx i . y i - nameraná hodnota, ε i =y i -Y i - rozdiel medzi nameranými a vypočítanými hodnotami, ε i =y i -a-bx i.
Metóda najmenších štvorcov vyžaduje, aby ε i, rozdiel medzi nameraným y i a hodnotami Y i vypočítanými z rovnice, bol minimálny. Preto nájdeme koeficienty a a b tak, aby súčet druhých mocnín odchýlok pozorovaných hodnôt od hodnôt na priamej regresnej čiare bol najmenší:
Skúmaním tejto funkcie argumentov a a pomocou derivácií do extrému môžeme dokázať, že funkcia nadobúda minimálnu hodnotu, ak sú koeficienty a a b riešeniami sústavy:
(2)
Ak obe strany normálnych rovníc vydelíme n, dostaneme:
Vzhľadom na to 
(3)
Získajte 
, odtiaľ dosadením hodnoty a v prvej rovnici dostaneme:
V tomto prípade sa b nazýva regresný koeficient; a sa nazýva voľný člen regresnej rovnice a vypočíta sa podľa vzorca:
Výsledná priamka je odhadom pre teoretickú regresnú priamku. Máme:
takže, 
je lineárna regresná rovnica.
Regresia môže byť priama (b>0) a inverzná (b Príklad 1. Výsledky merania hodnôt X a Y sú uvedené v tabuľke:
| x i | -2 | 0 | 1 | 2 | 4 |
| y i | 0.5 | 1 | 1.5 | 2 | 3 |
Za predpokladu, že medzi X a Y existuje lineárny vzťah y=a+bx, určte koeficienty a a b pomocou metódy najmenších štvorcov.
Riešenie. Tu n=5
x i = -2+0+1+2+4=5;
x i2 = 4+0+1+4+16=25
x i y i =-2 0,5+0 1+1 1,5+2 2+4 3=16,5
yi = 0,5 + 1 + 1,5 + 2 + 3 = 8
a normálny systém (2) má tvar 
Vyriešením tejto sústavy dostaneme: b=0,425, a=1,175. Preto y=1,175+0,425x.
Príklad 2. Existuje vzorka 10 pozorovaní ekonomických ukazovateľov (X) a (Y).
| x i | 180 | 172 | 173 | 169 | 175 | 170 | 179 | 170 | 167 | 174 |
| y i | 186 | 180 | 176 | 171 | 182 | 166 | 182 | 172 | 169 | 177 |
Je potrebné nájsť vzorovú regresnú rovnicu Y na X. Zostrojiť vzorovú regresnú priamku Y na X.
Riešenie. 1. Zoraďme údaje podľa hodnôt x i a y i . Dostávame novú tabuľku:
| x i | 167 | 169 | 170 | 170 | 172 | 173 | 174 | 175 | 179 | 180 |
| y i | 169 | 171 | 166 | 172 | 180 | 176 | 177 | 182 | 182 | 186 |
Pre zjednodušenie výpočtov zostavíme výpočtovú tabuľku, do ktorej zadáme potrebné číselné hodnoty.
| x i | y i | x i 2 | x i y i |
| 167 | 169 | 27889 | 28223 |
| 169 | 171 | 28561 | 28899 |
| 170 | 166 | 28900 | 28220 |
| 170 | 172 | 28900 | 29240 |
| 172 | 180 | 29584 | 30960 |
| 173 | 176 | 29929 | 30448 |
| 174 | 177 | 30276 | 30798 |
| 175 | 182 | 30625 | 31850 |
| 179 | 182 | 32041 | 32578 |
| 180 | 186 | 32400 | 33480 |
| ∑x i = 1729 | ∑y i = 1761 | ∑x i 2 299105 | ∑x i y i =304696 |
| x = 172,9 | y = 176,1 | x i2 = 29910,5 | xy=30469,6 |
Podľa vzorca (4) vypočítame regresný koeficient
a podľa vzorca (5)
Vzorová regresná rovnica teda vyzerá ako y=-59,34+1,3804x.
Vynesme body (x i ; y i) na súradnicovú rovinu a označme regresnú priamku.
Obr
Obrázok 4 ukazuje, ako sú pozorované hodnoty umiestnené vzhľadom na regresnú čiaru. Na numerický odhad odchýlok y i od Y i, kde y i sú pozorované hodnoty a Y i sú hodnoty určené regresiou, vytvoríme tabuľku:
| x i | y i | Y i | Y i -y i |
| 167 | 169 | 168.055 | -0.945 |
| 169 | 171 | 170.778 | -0.222 |
| 170 | 166 | 172.140 | 6.140 |
| 170 | 172 | 172.140 | 0.140 |
| 172 | 180 | 174.863 | -5.137 |
| 173 | 176 | 176.225 | 0.225 |
| 174 | 177 | 177.587 | 0.587 |
| 175 | 182 | 178.949 | -3.051 |
| 179 | 182 | 184.395 | 2.395 |
| 180 | 186 | 185.757 | -0.243 |
Hodnoty Y i sa vypočítajú podľa regresnej rovnice.
Znateľná odchýlka niektorých pozorovaných hodnôt od regresnej priamky sa vysvetľuje malým počtom pozorovaní. Pri štúdiu stupňa lineárnej závislosti Y na X sa berie do úvahy počet pozorovaní. Sila závislosti je určená hodnotou korelačného koeficientu.
Príklad.
Experimentálne údaje o hodnotách premenných X A pri sú uvedené v tabuľke.
Výsledkom ich zosúladenia je funkcia 
Použitím metóda najmenších štvorcov, aproximovať tieto údaje s lineárnou závislosťou y=ax+b(nájdite parametre A A b). Zistite, ktorý z dvoch riadkov je lepší (v zmysle metódy najmenších štvorcov) zarovnáva experimentálne údaje. Urobte si kresbu.
Podstata metódy najmenších štvorcov (LSM).
Problémom je nájsť lineárne koeficienty závislosti, pre ktoré je funkcia dvoch premenných A A b
má najmenšiu hodnotu. Teda vzhľadom na dáta A A b súčet štvorcových odchýlok experimentálnych údajov od nájdenej priamky bude najmenší. Toto je celý zmysel metódy najmenších štvorcov.
Riešenie príkladu sa teda redukuje na nájdenie extrému funkcie dvoch premenných.
Odvodenie vzorcov na hľadanie koeficientov.
Zostaví sa a vyrieši systém dvoch rovníc s dvoma neznámymi. Hľadanie parciálnych derivácií funkcií 
podľa premenných A A b, prirovnávame tieto deriváty k nule. 
Výslednú sústavu rovníc riešime ľubovoľnou metódou (napr substitučná metóda alebo Cramerova metóda) a získajte vzorce na nájdenie koeficientov pomocou metódy najmenších štvorcov (LSM). 
S údajmi A A b funkciu 
má najmenšiu hodnotu. Dôkaz tejto skutočnosti je uvedený pod textom na konci strany.
To je celá metóda najmenších štvorcov. Vzorec na nájdenie parametra a obsahuje súčty ,,, a parameter n- množstvo experimentálnych údajov. Hodnoty týchto súm sa odporúča vypočítať samostatne. Koeficient b zistené po výpočte a.
Je čas pripomenúť si pôvodný príklad.
Riešenie.
V našom príklade n=5. Tabuľku vypĺňame pre pohodlie výpočtu súm, ktoré sú zahrnuté vo vzorcoch požadovaných koeficientov. 
Hodnoty vo štvrtom riadku tabuľky sa získajú vynásobením hodnôt v 2. riadku hodnotami v 3. riadku pre každé číslo i.
Hodnoty v piatom riadku tabuľky sa získajú umocnením hodnôt v 2. riadku pre každé číslo i.
Hodnoty posledného stĺpca tabuľky sú súčty hodnôt v riadkoch.
Na zistenie koeficientov používame vzorce metódy najmenších štvorcov A A b. Nahradíme v nich zodpovedajúce hodnoty z posledného stĺpca tabuľky: 
teda y = 0,165 x + 2,184 je požadovaná približná priamka.
Zostáva zistiť, ktorý z riadkov y = 0,165 x + 2,184 alebo 
lepšie aproximuje pôvodné údaje, t. j. urobiť odhad pomocou metódy najmenších štvorcov.
Odhad chyby metódy najmenších štvorcov.
Aby ste to dosiahli, musíte vypočítať súčty štvorcových odchýlok pôvodných údajov z týchto riadkov 
A 
, menšia hodnota zodpovedá riadku, ktorý sa lepšie približuje pôvodným údajom z hľadiska metódy najmenších štvorcov. 
Od , potom riadok y = 0,165 x + 2,184 sa lepšie približuje pôvodným údajom.
Grafické znázornenie metódy najmenších štvorcov (LSM).
Na grafoch vyzerá všetko skvele. Červená čiara je nájdená čiara y = 0,165 x + 2,184, modrá čiara je 
, ružové bodky sú pôvodné údaje.
V praxi sa pri modelovaní rôznych procesov - najmä ekonomických, fyzikálnych, technických, sociálnych - široko používa jedna alebo druhá metóda výpočtu približných hodnôt funkcií z ich známych hodnôt v niektorých pevných bodoch.
Problémy s aproximáciou funkcií tohto druhu často vznikajú:
pri konštrukcii približných vzorcov na výpočet hodnôt charakteristických veličín skúmaného procesu podľa tabuľkových údajov získaných ako výsledok experimentu;
v numerickej integrácii, diferenciácii, riešení diferenciálnych rovníc a pod.;
ak je potrebné vypočítať hodnoty funkcií v medziľahlých bodoch uvažovaného intervalu;
pri určovaní hodnôt charakteristických veličín procesu mimo uvažovaného intervalu, najmä pri prognózovaní.
Ak sa na modelovanie určitého procesu špecifikovaného tabuľkou zostrojí funkcia, ktorá tento proces približne opisuje na základe metódy najmenších štvorcov, bude sa nazývať aproximačná funkcia (regresia) a samotná úloha konštrukcie aproximačných funkcií bude byť aproximačným problémom.
Tento článok rozoberá možnosti balíka MS Excel na riešenie takýchto problémov, okrem toho sú uvedené metódy a techniky na konštruovanie (vytváranie) regresií pre tabuľkovo dané funkcie (čo je základom regresnej analýzy).
Existujú dve možnosti vytvárania regresií v Exceli.
Pridanie vybraných regresií (trendových línií) do grafu zostaveného na základe údajovej tabuľky pre študovanú charakteristiku procesu (dostupné, len ak je graf zostavený);
Pomocou vstavaných štatistických funkcií pracovného hárka Excel, ktoré umožňujú získať regresie (trendové čiary) priamo z tabuľky zdrojových údajov.
Pridanie trendových čiar do grafu
Pre tabuľku údajov popisujúcich určitý proces a reprezentovaných diagramom má Excel efektívny nástroj regresnej analýzy, ktorý vám umožňuje:
stavať na základe metódy najmenších štvorcov a pridať do diagramu päť typov regresií, ktoré modelujú skúmaný proces s rôznym stupňom presnosti;
pridajte do diagramu rovnicu zostrojenej regresie;
určiť mieru zhody vybranej regresie s údajmi zobrazenými v grafe.
Na základe údajov z grafu vám Excel umožňuje získať lineárne, polynomické, logaritmické, exponenciálne, exponenciálne typy regresií, ktoré sú dané rovnicou:
y = y (x)
kde x je nezávislá premenná, ktorá často nadobúda hodnoty postupnosti prirodzených čísel (1; 2; 3; ...) a vytvára napríklad odpočítavanie času skúmaného procesu (charakteristiky) .
1 . Lineárna regresia je dobrá pri modelovaní prvkov, ktoré sa zvyšujú alebo znižujú konštantnou rýchlosťou. Toto je najjednoduchší model skúmaného procesu. Je zostavený podľa rovnice:
y=mx+b
kde m je dotyčnica sklonu lineárnej regresie k osi x; b - súradnica priesečníka lineárnej regresie s osou y.
2 . Polynomická trendová čiara je užitočná na opis charakteristík, ktoré majú niekoľko odlišných extrémov (horné a nízke). Výber stupňa polynómu je určený počtom extrémov skúmanej charakteristiky. Polynóm druhého stupňa teda môže dobre opísať proces, ktorý má len jedno maximum alebo minimum; polynóm tretieho stupňa - nie viac ako dva extrémy; polynóm štvrtého stupňa - nie viac ako tri extrémy atď.
V tomto prípade je trendová čiara zostavená v súlade s rovnicou:
y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6
kde koeficienty c0, c1, c2,...c6 sú konštanty, ktorých hodnoty sa určujú počas konštrukcie.
3 . Logaritmická trendová čiara sa úspešne používa pri modelovaní charakteristík, ktorých hodnoty sa najskôr rýchlo menia a potom sa postupne stabilizujú.
y = c ln(x) + b
4 . Trendová čiara výkonu poskytuje dobré výsledky, ak sú hodnoty študovanej závislosti charakterizované konštantnou zmenou rýchlosti rastu. Príklad takejto závislosti môže slúžiť ako graf rovnomerne zrýchleného pohybu auta. Ak sú v údajoch nulové alebo záporné hodnoty, nemôžete použiť silovú trendovú čiaru.
Je zostavený podľa rovnice:
y = cxb
kde koeficienty b, c sú konštanty.
5 . Ak sa rýchlosť zmeny údajov neustále zvyšuje, mala by sa použiť exponenciálna trendová čiara. Tento druh aproximácie tiež nie je použiteľný pre údaje obsahujúce nulové alebo záporné hodnoty.
Je zostavený podľa rovnice:
y=cebx
kde koeficienty b, c sú konštanty.
Pri výbere trendovej čiary Excel automaticky vypočíta hodnotu R2, ktorá charakterizuje presnosť aproximácie: čím je hodnota R2 bližšie k jednej, tým spoľahlivejšie trendová čiara aproximuje skúmaný proces. V prípade potreby môže byť hodnota R2 vždy zobrazená na diagrame.
Určené podľa vzorca:
Ak chcete pridať trendovú čiaru do série údajov:
aktivovať graf zostavený na základe údajových radov, t. j. kliknite do oblasti grafu. V hlavnom menu sa zobrazí položka Graf;
po kliknutí na túto položku sa na obrazovke zobrazí ponuka, v ktorej vyberte príkaz Pridať trendovú čiaru.
Rovnaké akcie sa dajú ľahko implementovať, ak umiestnite kurzor myši na graf zodpovedajúci jednému z radov údajov a kliknete pravým tlačidlom myši; v kontextovej ponuke, ktorá sa zobrazí, vyberte príkaz Pridať trendovú čiaru. Na obrazovke sa objaví dialógové okno Trendová čiara s otvorenou kartou Typ (obr. 1).
Potom potrebujete:
Na karte Typ vyberte požadovaný typ trendovej čiary (predvolene je vybratá možnosť Lineárna). Pre typ polynómu v poli Stupeň zadajte stupeň vybratého polynómu.
1 . Pole Built on Series obsahuje zoznam všetkých radov údajov v príslušnom grafe. Ak chcete pridať trendovú čiaru ku konkrétnej sérii údajov, vyberte jej názov v poli Postavené na sérii.
V prípade potreby môžete prechodom na kartu Parametre (obr. 2) nastaviť nasledujúce parametre pre trendovú čiaru:
zmeňte názov trendovej čiary v poli Názov aproximačnej (vyhladenej) krivky.
nastavte počet období (dopredu alebo dozadu) pre predpoveď v poli Predpoveď;
zobraziť rovnicu trendovej čiary v oblasti grafu, pre ktorú by ste mali zaškrtnúť políčko zobraziť rovnicu v grafe;
zobrazte hodnotu spoľahlivosti aproximácie R2 v oblasti diagramu, pre ktorú by ste mali povoliť zaškrtávacie políčko vložte do diagramu hodnotu spoľahlivosti aproximácie (R^2);
nastavte priesečník trendovej čiary s osou Y, pre ktorý by ste mali zaškrtnúť políčko Priesečník krivky s osou Y v bode;
kliknutím na tlačidlo OK zatvorte dialógové okno.
Existujú tri spôsoby, ako začať upravovať už vytvorenú trendovú čiaru:
po výbere trendovej čiary použite príkaz Vybraná trendová čiara z ponuky Formát;
z kontextovej ponuky vyberte príkaz Formátovať trendovú čiaru, ktorý sa vyvolá kliknutím pravým tlačidlom myši na čiaru trendu;
dvojitým kliknutím na trendovú čiaru.
Na obrazovke sa objaví dialógové okno Formátovať trendovú čiaru (obr. 3), ktoré obsahuje tri záložky: View, Type, Parameters a obsah posledných dvoch sa úplne zhoduje s podobnými kartami dialógového okna Trendline (obr. 1-2). ). Na karte Zobraziť môžete nastaviť typ čiary, jej farbu a hrúbku.
Ak chcete vymazať už vytvorenú trendovú čiaru, vyberte trendovú čiaru, ktorá sa má vymazať, a stlačte kláves Delete.
Výhody uvažovaného nástroja regresnej analýzy sú:
relatívna jednoduchosť vykresľovania trendovej čiary do grafov bez vytvorenia tabuľky s údajmi;
pomerne široký zoznam typov navrhovaných trendových čiar a tento zoznam obsahuje najbežnejšie používané typy regresie;
možnosť predpovedania správania sa skúmaného procesu pre ľubovoľný (v rámci zdravého rozumu) počet krokov vpred, ako aj späť;
možnosť získania rovnice trendovej čiary v analytickej forme;
možnosť v prípade potreby získať posúdenie spoľahlivosti aproximácie.
Nevýhody zahŕňajú nasledujúce body:
konštrukcia trendovej čiary sa vykonáva iba vtedy, ak existuje graf zostavený zo série údajov;
proces generovania radov údajov pre skúmanú charakteristiku na základe rovníc trendovej čiary získaných pre ňu je trochu neprehľadný: požadované regresné rovnice sa aktualizujú pri každej zmene hodnôt pôvodného radu údajov, ale iba v rámci oblasti grafu , zatiaľ čo rad údajov vytvorený na základe trendu starej čiarovej rovnice zostáva nezmenený;
Keď v zostavách kontingenčného grafu zmeníte zobrazenie grafu alebo súvisiacu zostavu kontingenčnej tabuľky, existujúce spojnice trendu sa nezachovajú, takže pred nakreslením spojníc trendu alebo iným formátovaním zostavy kontingenčného grafu musíte zabezpečiť, aby rozloženie zostavy vyhovovalo vašim požiadavkám.
Trendové čiary možno pridať do dátových radov prezentovaných na grafoch, ako sú graf, histogram, ploché nenormalizované plošné grafy, stĺpcové, bodové, bublinové a akciové grafy.
Trendové čiary nemôžete pridávať do dátových radov na 3-D, štandardnom, radarovom, koláčovom a prstencovom grafe.
Používanie vstavaných funkcií programu Excel
Excel tiež poskytuje nástroj regresnej analýzy na vykresľovanie trendových čiar mimo oblasti grafu. Na tento účel možno použiť množstvo štatistických funkcií pracovného hárka, ale všetky vám umožňujú zostaviť iba lineárne alebo exponenciálne regresie.
Excel má niekoľko funkcií na vytváranie lineárnej regresie, najmä:
SLOPE a REZ.
TREND;
Rovnako ako niekoľko funkcií na vytvorenie exponenciálnej trendovej čiary, najmä:
LGRFPpribl.
Treba poznamenať, že techniky konštrukcie regresií pomocou funkcií TREND a GROWTH sú prakticky rovnaké. To isté možno povedať o dvojici funkcií LINEST a LGRFPRIBL. Pre tieto štyri funkcie sa pri vytváraní tabuľky hodnôt používajú funkcie Excelu, ako sú vzorce poľa, čo trochu komplikuje proces vytvárania regresií. Poznamenávame tiež, že konštrukciu lineárnej regresie je podľa nášho názoru najjednoduchšie implementovať pomocou funkcií SLOPE a INTERCEPT, kde prvá z nich určuje sklon lineárnej regresie a druhá určuje segment odrezaný regresiou. na osi y.
Výhody vstavaného nástroja funkcií pre regresnú analýzu sú:
pomerne jednoduchý proces rovnakého typu tvorby radov údajov sledovanej charakteristiky pre všetky vstavané štatistické funkcie, ktoré určujú trendové čiary;
štandardná technika na vytváranie trendových čiar na základe vygenerovaných radov údajov;
schopnosť predpovedať správanie sa skúmaného procesu pre požadovaný počet krokov vpred alebo vzad.
A medzi nevýhody patrí skutočnosť, že Excel nemá vstavané funkcie na vytváranie iných (okrem lineárnych a exponenciálnych) typov trendových čiar. Táto okolnosť často neumožňuje vybrať dostatočne presný model skúmaného procesu, ako aj získať prognózy blízke realite. Navyše pri použití funkcií TREND a GROW nie sú známe rovnice trendových čiar.
Je potrebné poznamenať, že autori si nestanovili za cieľ článku predstaviť priebeh regresnej analýzy s rôznou mierou úplnosti. Jeho hlavnou úlohou je ukázať schopnosti balíka Excel pri riešení aproximačných problémov na konkrétnych príkladoch; demonštrovať, aké efektívne nástroje má Excel na vytváranie regresií a prognóz; ilustrujú, ako relatívne ľahko môže takéto problémy vyriešiť aj používateľ, ktorý nemá hlboké znalosti o regresnej analýze.
Príklady riešenia konkrétnych problémov
Zvážte riešenie konkrétnych problémov pomocou uvedených nástrojov balíka Excel.
Úloha 1
S tabuľkou údajov o zisku podniku motorovej dopravy za roky 1995-2002. musíte urobiť nasledovné.
Zostavte graf.
Pridajte do grafu lineárne a polynomické (kvadratické a kubické) trendové čiary.
Pomocou rovníc trendových čiar získajte tabuľkové údaje o zisku podniku pre každú trendovú čiaru za roky 1995-2004.
Urobte prognózu zisku pre podnik na roky 2003 a 2004.
Riešenie problému
Do rozsahu buniek A4:C11 hárka programu Excel zadáme hárok znázornený na obr. 4.
Po výbere rozsahu buniek B4:C11 vytvoríme graf.
Zostrojený graf aktivujeme a vyššie popísanou metódou po výbere typu trendovej čiary v dialógovom okne Trendová čiara (pozri obr. 1) do grafu striedavo pridávame lineárne, kvadratické a kubické trendové čiary. V tom istom dialógovom okne otvorte záložku Parametre (pozri obr. 2), do poľa Názov aproximačnej (vyhladenej) krivky zadajte názov pridávaného trendu a v poli Forecast forward for: periods nastavte hodnotu 2, keďže sa plánuje urobiť prognóza zisku na dva roky dopredu. Ak chcete zobraziť regresnú rovnicu a hodnotu aproximačnej spoľahlivosti R2 v oblasti diagramu, začiarknite políčka Zobraziť rovnicu na obrazovke a umiestnite do diagramu hodnotu aproximačnej spoľahlivosti (R^2). Pre lepšie vizuálne vnímanie meníme typ, farbu a hrúbku vykresľovaných trendových čiar, na čo slúži záložka Zobraziť dialógového okna Formát čiary trendu (pozri obr. 3). Výsledný graf s pridanými trendovými čiarami je znázornený na obr. 5.
Získať tabuľkové údaje o zisku podniku pre každú trendovú čiaru za roky 1995-2004. Použime rovnice trendových čiar uvedených na obr. 5. Za týmto účelom zadajte do buniek rozsahu D3:F3 textové informácie o type vybranej trendovej čiary: Lineárny trend, Kvadratický trend, Kubický trend. Potom zadajte vzorec lineárnej regresie do bunky D4 a pomocou značky výplne skopírujte tento vzorec s relatívnymi odkazmi na rozsah buniek D5:D13. Treba poznamenať, že každá bunka so vzorcom lineárnej regresie z rozsahu buniek D4:D13 má ako argument zodpovedajúcu bunku z rozsahu A4:A13. Podobne pre kvadratickú regresiu sa vyplní oblasť buniek E4:E13 a pre kubickú regresiu sa vyplní oblasť buniek F4:F13. Preto sa urobila prognóza zisku podniku na roky 2003 a 2004. s tromi trendmi. Výsledná tabuľka hodnôt je znázornená na obr. 6.
Úloha 2
Zostavte graf.
Pridajte do grafu logaritmické, exponenciálne a exponenciálne trendové čiary.
Odvoďte rovnice získaných trendových čiar, ako aj hodnoty aproximačnej spoľahlivosti R2 pre každú z nich.
Pomocou rovníc trendových čiar získajte tabuľkové údaje o zisku podniku pre každú trendovú čiaru za roky 1995-2002.
Vytvorte prognózu zisku pre podnikanie na roky 2003 a 2004 pomocou týchto trendových čiar.
Riešenie problému
Podľa metodiky uvedenej pri riešení úlohy 1 získame diagram s pridanými logaritmickými, exponenciálnymi a exponenciálnymi trendovými čiarami (obr. 7). Ďalej pomocou získaných rovníc trendových čiar vyplníme tabuľku hodnôt zisku podniku vrátane predpovedaných hodnôt za roky 2003 a 2004. (obr. 8).
Na obr. 5 a obr. je vidieť, že model s logaritmickým trendom zodpovedá najnižšej hodnote spoľahlivosti aproximácie
R2 = 0,8659
Najvyššie hodnoty R2 zodpovedajú modelom s polynomickým trendom: kvadratický (R2 = 0,9263) a kubický (R2 = 0,933).
Úloha 3
S tabuľkou údajov o zisku podniku motorovej dopravy za roky 1995-2002, ktorá je uvedená v úlohe 1, musíte vykonať nasledujúce kroky.
Získajte dátové série pre lineárne a exponenciálne trendové čiary pomocou funkcií TREND a GROW.
Pomocou funkcií TREND a GROWTH vytvorte prognózu zisku pre podnik na roky 2003 a 2004.
Pre počiatočné údaje a prijaté série údajov vytvorte diagram.
Riešenie problému
Využime pracovný list úlohy 1 (pozri obr. 4). Začnime funkciou TREND:
vyberte rozsah buniek D4:D11, ktorý by mal byť vyplnený hodnotami funkcie TREND zodpovedajúcimi známym údajom o zisku podniku;
zavolajte príkaz Funkcia z ponuky Vložiť. V zobrazenom dialógovom okne Sprievodca funkciou vyberte funkciu TREND z kategórie Štatistika a potom kliknite na tlačidlo OK. Rovnakú operáciu je možné vykonať stlačením tlačidla (funkcia Vložiť) na štandardnom paneli nástrojov.
V zobrazenom dialógovom okne Argumenty funkcie zadajte rozsah buniek C4:C11 do poľa Známe_hodnoty_y; v poli Known_values_x - rozsah buniek B4:B11;
ak chcete zo zadaného vzorca urobiť vzorec poľa, použite kombináciu kláves + + .
Vzorec, ktorý sme zadali do riadka vzorcov, bude vyzerať takto: =(TREND(C4:C11;B4:B11)).
Výsledkom je, že rozsah buniek D4:D11 je vyplnený zodpovedajúcimi hodnotami funkcie TREND (obr. 9).
Urobiť prognózu zisku spoločnosti na roky 2003 a 2004. potrebné:
vyberte rozsah buniek D12:D13, kde budú zadané hodnoty predpovedané funkciou TREND.
zavolajte funkciu TREND a v zobrazenom dialógovom okne Argumenty funkcie zadajte do poľa Známe_hodnoty_y rozsah buniek C4:C11; v poli Known_values_x - rozsah buniek B4:B11; a v poli Nové_hodnoty_x - rozsah buniek B12:B13.
premeňte tento vzorec na vzorec poľa pomocou klávesovej skratky Ctrl + Shift + Enter.
Zadaný vzorec bude vyzerať takto: =(TREND(C4:C11;B4:B11;B12:B13)) a rozsah buniek D12:D13 bude vyplnený predpovedanými hodnotami funkcie TREND (pozri obr. 9).
Podobne sa dátový rad napĺňa pomocou funkcie GROWTH, ktorá sa používa pri analýze nelineárnych závislostí a funguje úplne rovnako ako jeho lineárny náprotivok TREND.
Obrázok 10 zobrazuje tabuľku v režime zobrazenia vzorca.
Pre počiatočné dáta a získané dátové série je diagram znázornený na obr. jedenásť.
Úloha 4
S tabuľkou údajov o príjme žiadostí o výkony dispečerskou službou podniku motorovej dopravy za obdobie od 1. do 11. dňa bežného mesiaca je potrebné vykonať nasledovné úkony.
Získajte rad údajov pre lineárnu regresiu: pomocou funkcií SLOPE a INTERCEPT; pomocou funkcie LINEST.
Získajte sériu údajov pre exponenciálnu regresiu pomocou funkcie LYFFPRIB.
Pomocou vyššie uvedených funkcií vytvorte prognózu príjmu žiadostí na dispečing na obdobie od 12. do 14. dňa aktuálneho mesiaca.
Pre pôvodný a prijatý rad údajov vytvorte diagram.
Riešenie problému
Všimnite si, že na rozdiel od funkcií TREND a GROW žiadna z vyššie uvedených funkcií (SLOPE, INTERCEPTION, LINEST, LGRFPRIB) nie je regresia. Tieto funkcie zohrávajú len pomocnú úlohu, určujúce potrebné regresné parametre.
Pre lineárne a exponenciálne regresie postavené pomocou funkcií SLOPE, INTERCEPT, LINEST, LGRFPRIB je vzhľad ich rovníc vždy známy, na rozdiel od lineárnych a exponenciálnych regresií zodpovedajúcich funkciám TREND a GROWTH.
1 . Zostavme lineárnu regresiu, ktorá má rovnicu:
y=mx+b
pomocou funkcií SLOPE a INTERCEPT, pričom sklon regresie m určuje funkcia SLOPE a konštantný člen b - funkcia INTERCEPT.
Za týmto účelom vykonávame nasledujúce akcie:
zadajte zdrojovú tabuľku v rozsahu buniek A4:B14;
hodnota parametra m bude určená v bunke C19. Vyberte z kategórie Štatistika funkciu Sklon; zadajte rozsah buniek B4:B14 do poľa známe_hodnoty_y a rozsah buniek A4:A14 do poľa známe_hodnoty_x. Do bunky C19 sa zadá vzorec: =SLOPE(B4:B14;A4:A14);
pomocou podobnej metódy sa určí hodnota parametra b v bunke D19. A jeho obsah bude vyzerať takto: = INTERCEPT(B4:B14;A4:A14). Hodnoty parametrov m a b, ktoré sú potrebné na zostavenie lineárnej regresie, budú teda uložené v bunkách C19, D19;
potom zadáme vzorec lineárnej regresie do bunky C4 v tvare: = $ C * A4 + $ D. V tomto vzorci sú bunky C19 a D19 zapísané s absolútnymi odkazmi (adresa bunky by sa pri prípadnom kopírovaní nemala meniť). Absolútny referenčný znak $ je možné zadať buď z klávesnice alebo pomocou klávesu F4 po umiestnení kurzora na adresu bunky. Pomocou rukoväte výplne skopírujte tento vzorec do rozsahu buniek C4:C17. Dostaneme požadovaný rad údajov (obr. 12). Vzhľadom na to, že počet žiadostí je celé číslo, mali by ste na karte Číslo v okne Formát bunky nastaviť formát čísla s počtom desatinných miest na 0.
2 . Teraz zostavme lineárnu regresiu danú rovnicou:
y=mx+b
pomocou funkcie LINEST.
Pre to:
zadajte funkciu LINEST ako vzorec poľa do rozsahu buniek C20:D20: =(LINEST(B4:B14;A4:A14)). V dôsledku toho dostaneme hodnotu parametra m v bunke C20 a hodnotu parametra b v bunke D20;
do bunky D4 zadajte vzorec: =$C*A4+$D;
skopírujte tento vzorec pomocou značky výplne do rozsahu buniek D4:D17 a získajte požadovaný rad údajov.
3 . Zostavíme exponenciálnu regresiu, ktorá má rovnicu:
pomocou funkcie LGRFPRIBL sa vykonáva podobne:
v rozsahu buniek C21:D21 zadajte funkciu LGRFPRIBL ako vzorec poľa: =( LGRFPRIBL (B4:B14;A4:A14)). V tomto prípade sa hodnota parametra m určí v bunke C21 a hodnota parametra b sa určí v bunke D21;
vzorec sa zadá do bunky E4: =$D*$C^A4;
pomocou značky výplne sa tento vzorec skopíruje do rozsahu buniek E4:E17, kde bude umiestnený rad údajov pre exponenciálnu regresiu (pozri obr. 12).
Na obr. 13 je tabuľka, kde môžeme vidieť funkcie, ktoré používame s potrebnými rozsahmi buniek, ako aj vzorce.
Hodnota R 2 volal determinačný koeficient.
Úlohou konštrukcie regresnej závislosti je nájsť vektor koeficientov m modelu (1), pri ktorom koeficient R nadobúda maximálnu hodnotu.
Na posúdenie významnosti R sa používa Fisherov F-test vypočítaný podľa vzorca
Kde n- veľkosť vzorky (počet experimentov);
k je počet modelových koeficientov.
Ak F prekročí určitú kritickú hodnotu pre dáta n A k a akceptovanej úrovni spoľahlivosti, potom sa hodnota R považuje za významnú. Tabuľky kritických hodnôt F sú uvedené v referenčných knihách o matematickej štatistike.
Význam R je teda určený nielen jeho hodnotou, ale aj pomerom medzi počtom experimentov a počtom koeficientov (parametrov) modelu. V skutočnosti je korelačný pomer pre n=2 pre jednoduchý lineárny model 1 (cez 2 body v rovine môžete vždy nakresliť jednu priamku). Ak sú však experimentálne údaje náhodné premenné, takejto hodnote R by sa malo dôverovať veľmi opatrne. Zvyčajne, aby sa získala významná R a spoľahlivá regresia, je zameraná na zabezpečenie toho, aby počet experimentov výrazne prevyšoval počet modelových koeficientov (n>k).
Ak chcete vytvoriť model lineárnej regresie, musíte:
1) pripravte zoznam n riadkov a m stĺpcov obsahujúcich experimentálne údaje (stĺpec obsahujúci výstupnú hodnotu Y musí byť buď prvý alebo posledný v zozname); zoberme si napríklad údaje predchádzajúcej úlohy, pričom pridáme stĺpec s názvom „číslo obdobia“, očíslujeme čísla období od 1 do 12. (toto budú hodnoty X)
2) prejdite do ponuky Údaje/Analýza údajov/Regresia
Ak položka „Analýza údajov“ v ponuke „Nástroje“ chýba, mali by ste prejsť na položku „Doplnky“ v tej istej ponuke a začiarknuť políčko „Analytický balík“.
3) v dialógovom okne "Regresia" nastavte:
vstupný interval Y;
vstupný interval X;
výstupný interval - ľavá horná bunka intervalu, v ktorom budú umiestnené výsledky výpočtu (odporúča sa umiestniť ho na nový pracovný hárok);
4) kliknite na „OK“ a analyzujte výsledky.
Má veľa aplikácií, keďže umožňuje približnú reprezentáciu danej funkcie inými jednoduchšími. LSM môže byť mimoriadne užitočné pri spracovaní pozorovaní a aktívne sa používa na odhadovanie niektorých veličín z výsledkov meraní iných, ktoré obsahujú náhodné chyby. V tomto článku sa dozviete, ako implementovať výpočty najmenších štvorcov v Exceli.
Vyjadrenie problému na konkrétnom príklade
Predpokladajme, že existujú dva ukazovatele X a Y. Navyše Y závisí od X. Keďže OLS je pre nás zaujímavý z hľadiska regresnej analýzy (v Exceli sú jeho metódy implementované pomocou vstavaných funkcií), mali by sme okamžite pokračovať zvážiť konkrétny problém.
Nech je teda X predajná plocha obchodu s potravinami meraná v metroch štvorcových a Y je ročný obrat definovaný v miliónoch rubľov.
Je potrebné urobiť prognózu, aký obrat (Y) bude mať obchod, ak má jednu alebo druhú maloobchodnú plochu. Je zrejmé, že funkcia Y = f (X) rastie, keďže hypermarket predáva viac tovaru ako stánok.
Niekoľko slov o správnosti počiatočných údajov použitých na predikciu
Povedzme, že máme zostavenú tabuľku s údajmi pre n obchodov.
Podľa matematických štatistík budú výsledky viac-menej správne, ak sa preskúmajú údaje aspoň o 5-6 objektoch. Taktiež nemožno použiť „anomálne“ výsledky. Najmä elitný malý butik môže mať obrat mnohonásobne väčší ako obrat veľkých predajní triedy „masmarket“.
Podstata metódy
Údaje tabuľky je možné zobraziť v karteziánskej rovine ako body M 1 (x 1, y 1), ... M n (x n, y n). Teraz sa riešenie úlohy zredukuje na výber aproximačnej funkcie y = f (x), ktorej graf prechádzajúci čo najbližšie k bodom M 1, M 2, .. M n .
Samozrejme, môžete použiť polynóm vysokého stupňa, ale táto možnosť je nielen náročná na implementáciu, ale jednoducho nesprávna, pretože nebude odrážať hlavný trend, ktorý je potrebné zistiť. Najrozumnejším riešením je hľadať priamku y = ax + b, ktorá najlepšie aproximuje experimentálne údaje, presnejšie koeficienty - a a b.
Skóre presnosti
Pre akúkoľvek aproximáciu je mimoriadne dôležité posúdenie jej presnosti. Označme e i rozdiel (odchýlku) medzi funkčnou a experimentálnou hodnotou pre bod x i, t.j. e i = y i - f (x i).
Je zrejmé, že na posúdenie presnosti aproximácie môžete použiť súčet odchýlok, t.j. pri výbere priamky na približné znázornenie závislosti X na Y by sa mala uprednostniť tá, ktorá má najmenšiu hodnotu súčet e i vo všetkých posudzovaných bodoch. Nie všetko je však také jednoduché, pretože spolu s pozitívnymi odchýlkami budú prakticky existovať aj negatívne.
Problém môžete vyriešiť pomocou modulov odchýlky alebo ich štvorcov. Posledná uvedená metóda je najpoužívanejšia. Používa sa v mnohých oblastiach vrátane regresnej analýzy (v Exceli sa jej implementácia vykonáva pomocou dvoch vstavaných funkcií) a dlho sa osvedčila ako účinná.
Metóda najmenších štvorcov
V Exceli, ako viete, je vstavaná funkcia automatického súčtu, ktorá vám umožňuje vypočítať hodnoty všetkých hodnôt nachádzajúcich sa vo vybranom rozsahu. Nič nám teda nebude brániť vypočítať hodnotu výrazu (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
V matematickom zápise to vyzerá takto:
Keďže sa pôvodne rozhodlo o aproximácii pomocou priamky, máme:
Úloha nájsť priamku, ktorá najlepšie popisuje špecifický vzťah medzi X a Y, teda znamená výpočet minima funkcie dvoch premenných:
To si vyžaduje rovnanie nulovým parciálnym deriváciám vzhľadom na nové premenné a a b a riešenie primitívneho systému pozostávajúceho z dvoch rovníc s 2 neznámymi tvaru:
Po jednoduchých transformáciách, vrátane delenia 2 a manipulácie so súčtami, dostaneme:
Riešením napríklad Cramerovou metódou dostaneme stacionárny bod s určitými koeficientmi a * a b * . Toto je minimum, teda na predpovedanie, aký obrat bude mať obchod pre určitú oblasť, je vhodná priamka y = a * x + b *, čo je regresný model pre daný príklad. Samozrejme, že vám to nedovolí nájsť presný výsledok, ale pomôže vám to získať predstavu o tom, či sa nákup obchodu na úver pre konkrétnu oblasť oplatí.
Ako implementovať metódu najmenších štvorcov v Exceli
Excel má funkciu na výpočet hodnoty najmenších štvorcov. Má nasledujúci tvar: TREND (známe hodnoty Y; známe hodnoty X; nové hodnoty X; konštanta). Aplikujme vzorec na výpočet OLS v Exceli na našu tabuľku.
Ak to chcete urobiť, v bunke, v ktorej by sa mal zobraziť výsledok výpočtu metódou najmenších štvorcov v programe Excel, zadajte znak „=“ a vyberte funkciu „TREND“. V okne, ktoré sa otvorí, vyplňte príslušné polia a zvýraznite:
- rozsah známych hodnôt pre Y (v tomto prípade údaje pre obrat);
- rozsah x 1 , … x n , t. j. veľkosť predajnej plochy;
- a známe a neznáme hodnoty x, pre ktoré musíte zistiť veľkosť obratu (informácie o ich umiestnení na pracovnom hárku nájdete nižšie).
Okrem toho je vo vzorci logická premenná "Const". Ak zadáte 1 do príslušného poľa, bude to znamenať, že by sa mali vykonať výpočty za predpokladu, že b \u003d 0.
Ak potrebujete poznať predpoveď pre viac ako jednu hodnotu x, potom po zadaní vzorca by ste nemali stlačiť „Enter“, ale musíte zadať kombináciu „Shift“ + „Control“ + „Enter“ („Enter“ ) na klávesnici.
Niektoré funkcie
Regresná analýza môže byť prístupná aj pre figuríny. Excelovský vzorec na predpovedanie hodnoty poľa neznámych premenných – „TREND“ – môže použiť aj ten, kto o metóde najmenších štvorcov nikdy nepočul. Stačí poznať niektoré črty jeho práce. Konkrétne:
- Ak umiestnite rozsah známych hodnôt premennej y do jedného riadku alebo stĺpca, potom každý riadok (stĺpec) so známymi hodnotami x bude programom vnímaný ako samostatná premenná.
- Ak v okne TREND nie je zadaný rozsah so známym x, v prípade použitia funkcie v Exceli ho program bude považovať za pole pozostávajúce z celých čísel, ktorých počet zodpovedá rozsahu s danými hodnotami. premennej y.
- Na výstup poľa „predpovedaných“ hodnôt je potrebné zadať výraz trendu ako vzorec poľa.
- Ak nie sú zadané žiadne nové hodnoty x, funkcia TREND ich považuje za rovnaké ako tie známe. Ak nie sú špecifikované, potom sa pole 1 berie ako argument; 2; 3; 4;…, ktorá je úmerná rozsahu s už danými parametrami y.
- Rozsah obsahujúci nové hodnoty x musí mať rovnaký alebo viac riadkov alebo stĺpcov ako rozsah s danými hodnotami y. Inými slovami, musí byť úmerná nezávislým premenným.
- Pole so známymi hodnotami x môže obsahovať viacero premenných. Ak však hovoríme len o jednom, potom je potrebné, aby rozsahy s danými hodnotami x a y boli úmerné. V prípade viacerých premenných je potrebné, aby sa rozsah s danými hodnotami y zmestil do jedného stĺpca alebo jedného riadku.
Funkcia FORECAST
Realizuje sa pomocou niekoľkých funkcií. Jeden z nich sa nazýva „PREDICTION“. Je podobný TRENDU, teda dáva výsledok výpočtov metódou najmenších štvorcov. Avšak len pre jedno X, pre ktoré je hodnota Y neznáma.
Teraz poznáte vzorce Excel pre figuríny, ktoré vám umožňujú predpovedať hodnotu budúcej hodnoty ukazovateľa podľa lineárneho trendu.
Metóda najmenších štvorcov je jednou z najbežnejších a najrozvinutejších vďaka jej jednoduchosť a efektívnosť metód na odhad parametrov lineárnych. Zároveň je potrebné dbať na istú opatrnosť pri jeho používaní, pretože modely s jeho pomocou nemusia spĺňať množstvo požiadaviek na kvalitu svojich parametrov a v dôsledku toho „nezodpovedajú“ vzorom vývoja procesov.
Pozrime sa podrobnejšie na postup odhadu parametrov lineárneho ekonometrického modelu metódou najmenších štvorcov. Takýto model vo všeobecnej forme môže byť reprezentovaný rovnicou (1.2):
yt = a 0 + a 1 x 1 t +...+ a n x nt + ε t.
Počiatočný údaj pri odhade parametrov a 0 , a 1 ,..., a n je vektor hodnôt závislej premennej r= (y 1 , y 2 , ... , y T)“ a matica hodnôt nezávislých premenných
v ktorom prvý stĺpec pozostávajúci z jednotiek zodpovedá koeficientu modelu .
Metóda najmenších štvorcov dostala svoj názov na základe základného princípu, že odhady parametrov získané na jej základe musia spĺňať: súčet štvorcov chyby modelu by mal byť minimálny.
Príklady riešenia úloh metódou najmenších štvorcov
Príklad 2.1. Obchodný podnik má sieť 12 predajní, o ktorých činnosti sú uvedené v tabuľke. 2.1.
Vedenie spoločnosti by chcelo vedieť, ako závisí veľkosť ročenky od predajnej plochy predajne.
Tabuľka 2.1
|
Číslo predajne |
Ročný obrat, milióny rubľov |
Obchodná plocha, tis. m2 |
Riešenie najmenších štvorcov. Označme - ročný obrat -tého obchodu, milióny rubľov; - predajná plocha predajne, tisíc m2.
Obr.2.1. Bodový graf pre príklad 2.1
Určiť formu funkčného vzťahu medzi premennými a zostrojiť bodový graf (obr. 2.1).
Na základe rozptylového diagramu môžeme konštatovať, že ročný obrat je pozitívne závislý od predajnej plochy (t.j. y sa zvýši s rastom o ). Najvhodnejšia forma funkčného spojenia je − lineárne.
Informácie pre ďalšie výpočty sú uvedené v tabuľke. 2.2. Pomocou metódy najmenších štvorcov odhadujeme parametre lineárneho jednofaktorového ekonometrického modelu
Tabuľka 2.2
teda
Preto so zvýšením obchodnej oblasti o 1 000 m 2, ak sú ostatné veci rovnaké, priemerný ročný obrat sa zvyšuje o 67,8871 milióna rubľov.
Príklad 2.2. Vedenie podniku si všimlo, že ročný obrat nezávisí len od predajnej plochy predajne (pozri príklad 2.1), ale aj od priemerného počtu návštevníkov. Príslušné informácie sú uvedené v tabuľke. 2.3.
Tabuľka 2.3
Riešenie. Označte - priemerný počet návštevníkov obchodu za deň, tisíc ľudí.
Určiť formu funkčného vzťahu medzi premennými a zostrojiť bodový graf (obr. 2.2).
Na základe rozptylového diagramu môžeme konštatovať, že ročný obrat pozitívne súvisí s priemerným počtom návštevníkov za deň (t. j. y sa bude zvyšovať s rastom o ). Forma funkčnej závislosti je lineárna.
Ryža. 2.2. Napríklad bodový graf 2.2
Tabuľka 2.4
Vo všeobecnosti je potrebné určiť parametre dvojfaktorového ekonometrického modelu
y t \u003d a 0 + a 1 x 1 t + a 2 x 2 t + ε t
Informácie potrebné pre ďalšie výpočty sú uvedené v tabuľke. 2.4.
Odhadnime parametre lineárneho dvojfaktorového ekonometrického modelu metódou najmenších štvorcov.
teda
Z vyhodnotenia koeficientu = 61,6583 vyplýva, že za rovnakých okolností pri zvýšení obchodnej plochy o 1 tisíc m 2 vzrastie ročný obrat v priemere o 61,6583 milióna rubľov.
Som počítačový programátor. Najväčší skok vo svojej kariére som urobil, keď som sa naučil povedať: "Ničomu nerozumiem!" Teraz sa nehanbím povedať osvetľovačovi vedy, že mi robí prednášku, že nerozumiem, o čom ona, svetlica, so mnou hovorí. A je to veľmi ťažké. Áno, je ťažké a trápne priznať, že to neviete. Kto sa rád prizná, že nevie základy niečoho-tam. Z titulu svojej profesie musím absolvovať veľké množstvo prezentácií a prednášok, kde, priznám sa, v drvivej väčšine prípadov pociťujem ospalosť, pretože ničomu nerozumiem. A nerozumiem, pretože obrovský problém súčasnej situácie vo vede spočíva v matematike. Predpokladá, že všetci študenti poznajú absolútne všetky oblasti matematiky (čo je absurdné). Priznať, že neviete, čo je derivát (že toto je trochu neskôr), je škoda.
Ale naučil som sa povedať, že neviem, čo je násobenie. Áno, neviem, čo je subalgebra nad Lieovou algebrou. Áno, neviem, prečo sú v živote potrebné kvadratické rovnice. Mimochodom, ak ste si istí, že viete, potom sa máme o čom rozprávať! Matematika je séria trikov. Matematici sa snažia zmiasť a zastrašiť verejnosť; kde nie je zmätok, povesť, autorita. Áno, je prestížne hovoriť čo najabstraktnejším jazykom, čo je samo o sebe úplný nezmysel.
Viete, čo je derivát? S najväčšou pravdepodobnosťou mi poviete o limite rozdielového vzťahu. V prvom ročníku matematiky na Petrohradskej štátnej univerzite ma Viktor Petrovič Khavin definované derivácia ako koeficient prvého člena Taylorovho radu funkcie v bode (bola to samostatná gymnastika na určenie Taylorovho radu bez derivácií). Dlho som sa na tejto definícii smial, až som konečne pochopil, o čo ide. Derivácia nie je nič iné ako len miera toho, nakoľko je funkcia, ktorú derivujeme, podobná funkcii y=x, y=x^2, y=x^3.
Teraz mám tú česť prednášať študentom, ktorí strach matematiky. Ak sa bojíte matematiky - sme na ceste. Akonáhle sa pokúsite prečítať nejaký text a bude sa vám zdať, že je prehnane komplikovaný, tak vedzte, že je napísaný zle. Tvrdím, že neexistuje jediná oblasť matematiky, o ktorej by sa nedalo hovoriť „na prstoch“ bez straty presnosti.
Výzva pre blízku budúcnosť: Inštruoval som svojich študentov, aby pochopili, čo je lineárny-kvadratický regulátor. Nehanbite sa, premárnite tri minúty svojho života, nasledujte odkaz. Ak niečomu nerozumiete, sme na ceste. Ja (profesionálny matematik-programátor) som tiež ničomu nerozumel. A uisťujem vás, že sa to dá vyriešiť „na prstoch“. Zapnuté tento moment Neviem, čo to je, ale uisťujem vás, že na to prídeme.
Takže prvá prednáška, ktorú dám svojim študentom po tom, ako ku mne zdesene pribehnú so slovami, že lineárny kvadratický regulátor je strašná chyba, ktorú nikdy v živote nezvládnete, je metódy najmenších štvorcov. Viete riešiť lineárne rovnice? Ak čítate tento text, tak s najväčšou pravdepodobnosťou nie.
Takže ak sú dané dva body (x0, y0), (x1, y1), napríklad (1,1) a (3,2), úlohou je nájsť rovnicu priamky prechádzajúcej týmito dvoma bodmi:
ilustrácie
Táto priamka by mala mať rovnicu, ako je táto:
Alfa a beta sú nám neznáme, ale známe sú dva body tejto čiary:
Túto rovnicu môžete napísať v maticovom tvare:
Tu by ste mali urobiť lyrická odbočka: čo je matica? Matica nie je nič iné ako dvojrozmerné pole. Toto je spôsob ukladania údajov, nemali by sa mu dávať žiadne ďalšie hodnoty. Je na nás, ako presne interpretovať určitú maticu. Periodicky to budem interpretovať ako lineárne zobrazenie, periodicky ako kvadratickú formu a niekedy jednoducho ako množinu vektorov. Toto všetko bude objasnené v kontexte.
Nahraďme konkrétne matice ich symbolickým znázornením:
Potom (alfa, beta) možno ľahko nájsť:
Konkrétnejšie pre naše predchádzajúce údaje:
Čo vedie k nasledujúcej rovnici priamky prechádzajúcej bodmi (1,1) a (3,2):
Dobre, tu je všetko jasné. A nájdime rovnicu priamky prechádzajúcej cez tri body: (x0,y0), (x1,y1) a (x2,y2):
Oh-och-och, ale máme tri rovnice pre dve neznáme! Štandardný matematik povie, že neexistuje žiadne riešenie. Čo povie programátor? A najprv prepíše predchádzajúci systém rovníc v nasledujúcom tvare:
V našom prípade vektory i,j,b sú trojrozmerné, preto (vo všeobecnom prípade) neexistuje riešenie tohto systému. Akýkoľvek vektor (alpha\*i + beta\*j) leží v rovine preklenutej vektormi (i, j). Ak b nepatrí do tejto roviny, potom neexistuje riešenie (rovnosť v rovnici nemožno dosiahnuť). Čo robiť? Hľadajme kompromis. Označme podľa e (alfa, beta) ako presne sme nedosiahli rovnosť:
A túto chybu sa pokúsime minimalizovať:
Prečo štvorec?
Hľadáme nielen minimum normy, ale aj minimum druhej mocniny normy. prečo? Samotný minimálny bod sa zhoduje a štvorec dáva hladkú funkciu (kvadratická funkcia argumentov (alfa,beta)), zatiaľ čo len dĺžka dáva funkciu vo forme kužeľa, nediferencovateľného v minimálnom bode. Brr. Námestie je pohodlnejšie.
Je zrejmé, že chyba je minimalizovaná, keď vektor e ortogonálne k rovine preklenutej vektormi i A j.
Ilustračné
Inými slovami: hľadáme takú priamku, aby súčet druhých mocnín vzdialeností od všetkých bodov k tejto priamke bol minimálny:
AKTUALIZÁCIA: tu mám zárubňu, vzdialenosť k čiare by sa mala merať vertikálne, nie ortografická projekcia. komentátor má pravdu.
Ilustračné
Úplne inými slovami (opatrne, zle formalizované, ale malo by to byť jasné na prstoch): vezmeme všetky možné čiary medzi všetkými pármi bodov a hľadáme priemernú čiaru medzi všetkými:
Ilustračné
Ďalšie vysvetlenie na prstoch: medzi všetky dátové body (tu máme tri) a čiaru, ktorú hľadáme, pripevníme pružinu a čiara rovnovážneho stavu je presne to, čo hľadáme.
Kvadratické minimum tvaru
Takže vzhľadom na vektor b a rovinu preklenutú stĺpcami-vektormi matice A(v tomto prípade (x0,x1,x2) a (1,1,1)), hľadáme vektor e s minimálnou štvorcovou dĺžkou. Je zrejmé, že minimum je dosiahnuteľné len pre vektor e, ortogonálne k rovine preklenutej stĺpcami-vektormi matice A:Inými slovami, hľadáme vektor x=(alfa, beta) taký, že:
Pripomínam vám, že tento vektor x=(alfa, beta) je minimum kvadratickej funkcie ||e(alfa, beta)||^2:
Tu je užitočné pripomenúť, že maticu možno interpretovať rovnako ako kvadratickú formu, napríklad maticu identity ((1,0), (0,1)) možno interpretovať ako funkciu x^2 + y ^2:
kvadratická forma
Celá táto gymnastika je známa ako lineárna regresia.
Laplaceova rovnica s Dirichletovou okrajovou podmienkou
Teraz najjednoduchší skutočný problém: existuje určitý trojuholníkový povrch, je potrebné ho vyhladiť. Napríklad načítajme model mojej tváre:
Pôvodný záväzok je k dispozícii. Aby som minimalizoval externé závislosti, zobral som kód môjho softvérového renderera, už na Habré. Na vyriešenie lineárneho systému používam OpenNL , je to skvelý riešiteľ, ale jeho inštalácia je veľmi náročná: musíte skopírovať dva súbory (.h + .c) do priečinka projektu. Celé vyhladenie sa vykonáva pomocou nasledujúceho kódu:
Pre (int d=0; d<3; d++) {
nlNewContext();
nlSolverParameteri(NL_NB_VARIABLES, verts.size());
nlSolverParameteri(NL_LEAST_SQUARES, NL_TRUE);
nlBegin(NL_SYSTEM);
nlBegin(NL_MATRIX);
for (int i=0; i<(int)verts.size(); i++) {
nlBegin(NL_ROW);
nlCoefficient(i, 1);
nlRightHandSide(verts[i][d]);
nlEnd(NL_ROW);
}
for (unsigned int i=0; i
Súradnice X, Y a Z sú oddeliteľné, hladkám ich samostatne. To znamená, že riešim tri sústavy lineárnych rovníc, každú s rovnakým počtom premenných ako je počet vrcholov v mojom modeli. Prvých n riadkov matice A má iba jednu 1 na riadok a prvých n riadkov vektora b má pôvodné súradnice modelu. To znamená, že prepojím novú pozíciu vrcholu a starú pozíciu vrcholu - nové by nemali byť príliš ďaleko od starých.
Všetky nasledujúce riadky matice A (faces.size()*3 = počet hrán všetkých trojuholníkov v mriežke) majú jeden výskyt 1 a jeden výskyt -1, pričom vektor b má nulové zložky oproti. To znamená, že na každý okraj našej trojuholníkovej siete vložím pružinu: všetky okraje sa snažia získať rovnaký vrchol ako ich počiatočný a koncový bod.
Ešte raz: všetky vrcholy sú premenné a nemôžu sa odchýliť ďaleko od svojej pôvodnej polohy, no zároveň sa snažia byť si navzájom podobné.
Tu je výsledok:
Všetko by bolo v poriadku, model je naozaj vyhladený, no vzdialil sa od pôvodného okraja. Poďme trochu zmeniť kód:
Pre (int i=0; i<(int)verts.size(); i++) { float scale = border[i] ? 1000: 1; nlBegin(NL_ROW); nlCoefficient(i, scale); nlRightHandSide(scale*verts[i][d]); nlEnd(NL_ROW); }
V našej matici A pre vrcholy, ktoré sú na okraji, pridávam nie riadok z kategórie v_i = verts[i][d], ale 1000*v_i = 1000*verts[i][d]. čo to mení? A to mení našu kvadratickú formu chyby. Teraz jedna odchýlka od vrcholu na okraji nebude stáť jednu jednotku, ako predtým, ale 1 000 * 1 000 jednotiek. To znamená, že na krajné vrcholy sme zavesili silnejšiu pružinu, riešenie radšej silnejšie natiahne ostatné. Tu je výsledok:
Zdvojnásobme silu pružín medzi vrcholmi:
nlKoeficient(tvár[ j], 2); nlKoeficient(tvár[(j+1)%3], -2);
Je logické, že povrch je hladší:
A teraz ešte stokrát silnejšie:
Čo to je? Predstavte si, že sme drôtený krúžok ponorili do mydlovej vody. Výsledkom je, že výsledný mydlový film sa bude snažiť mať čo najmenšie zakrivenie a dotýkať sa rovnakej hranice - nášho drôteného krúžku. To je presne to, čo sme získali, keď sme upevnili okraj a požiadali o hladký povrch vo vnútri. Gratulujeme, práve sme vyriešili Laplaceovu rovnicu s Dirichletovými okrajovými podmienkami. To znie dobre? Ale v skutočnosti stačí vyriešiť len jeden systém lineárnych rovníc.
Poissonova rovnica
Dajme ďalšie skvelé meno.Povedzme, že mám takýto obrázok:
Všetci sú dobrí, ale stolička sa mi nepáči.
Rozrezal som obrázok na polovicu: 
A vyberiem si stoličku rukami: 
Potom pretiahnem všetko, čo je v maske biele, na ľavú stranu obrázka a zároveň cez celý obrázok poviem, že rozdiel dvoch susedných pixelov by sa mal rovnať rozdielu dvoch susedných pixelov obrázka. pravý obrázok:
Pre (int i=0; i Tu je výsledok: Mám niekoľko fotografií vzoriek látok, ako je táto: Mojou úlohou je vytvárať bezšvové textúry z fotografií takejto kvality. Najprv (automaticky) hľadám opakujúci sa vzor: Ak vystrihnem tento štvoruholník tu, potom sa kvôli deformáciám okraje nebudú zbiehať, tu je príklad vzoru, ktorý sa opakuje štyrikrát: Skrytý text Tu je fragment, kde je šev jasne viditeľný: Preto nebudem rezať pozdĺž priamky, tu je čiara rezu: Skrytý text A tu je vzorec opakovaný štyrikrát: Skrytý text A jeho fragment, aby to bolo jasnejšie: Už lepšie, strih nešiel v priamej línii, obchádzal všetky druhy kučier, ale stále je šev viditeľný kvôli nerovnomernému osvetleniu na pôvodnej fotografii. Tu prichádza na pomoc metóda najmenších štvorcov pre Poissonovu rovnicu. Tu je konečný výsledok po zarovnaní osvetlenia: Textúra sa ukázala byť dokonale hladká a to všetko automaticky z fotografie veľmi priemernej kvality. Nebojte sa matematiky, hľadajte jednoduché vysvetlenia a budete mať šťastie v strojárstve.
Príklad zo skutočného života
Schválne som nerobil vylízané výsledky, lebo. Chcel som len ukázať, ako presne môžete použiť metódy najmenších štvorcov, toto je tréningový kód. Teraz uvediem príklad zo života: 
