Metóda najmenších štvorcov (MNC) patrí do oblasti regresnej analýzy. Má mnoho aplikácií, pretože umožňuje vykonávať približné zobrazenie danej funkcie inými, jednoduchšími. MNC sa môže ukázať ako veľmi užitočná pri liečbe pozorovaní a aktívne sa používa na vyhodnotenie niektorých hodnôt meraných inými, ktoré obsahujú náhodné chyby. Z tohto článku sa dozviete, ako implementovať výpočet najmenších štvorcov v programe Excel.

Problém Konkrétnym príkladom

Predpokladajme, že existujú dva indikátor X a Y. A Y závisí na X. Vzhľadom k tomu, nadnárodných spoločností nás zaujímajú, pokiaľ ide o regresnej analýzy v metódach Excel implementovaná ho pomocou vstavaných funkcií), mali by ste okamžite to isté platí aj pre posúdenie konkrétnej úlohy.


Tak X - Obchod s potravinami obchodné plochy, merané v metroch štvorcových a Y - ročný obrat určený v miliónoch rubľov. Je potrebné predpovedať, aký obratový tovar (Y) bude blízko obchodu, ak má jednu alebo druhú obchodnú oblasť. Samozrejme, funkcia Y = f (X) sa zvyšuje, pretože hypermarket predáva viac tovaru ako stánok.

Niekoľko slov o správnosti zdrojových údajov použitých na predpoveď

Predpokladajme, že máme vytvorenú tabuľku podľa údajov pre n obchody.

, X



x 1



x 2







xn



, y



y 1



y 2







yn

Podľamatematické štatistiky, výsledky budú viac-menej správne, ak sa študia údaje o minimálne 5 - 6 objektoch. Okrem toho nemôžete používať "abnormálne" výsledky. Najmä elitný malý butik môže mať obrat viac ako obrat veľkých maloobchodných predajní triedy "masového trhu".

Spôsob

Tieto tabuľky možno čerpať karteziánske rovine ako body M 1 (x 1, y 1), M n (x n, y n). Teraz znížená na riešenie problému funkcie výberu približovanie y = f (x), ktorá má plán, ktorý je tak blízko, ako je to možné, aby body M 1 M 2 M n. Samozrejme, môžete použiť polynóm vysokým stupňom, ale táto možnosť je nielen trudnorealyzuem, ale jednoducho nesprávne, pretože neodrážajú hlavný trend, ktorý tiež treba objaviť. Najviac rozumné riešenie je nájsť priamky y = ax + b, ktorý sa najviac blíži experimentálne údaje, presnejšie, koeficienty a a b.

Odhad presnosti

Pri akomkoľvek aproximácii má hodnotenie presnosti osobitný význam. E i označuje rozdiel (odchýlka) medzi funkčnými a experimentálne hodnoty pre body x i, tj e i = r i - f (x i). Je zrejmé, že na posúdenie správnosti aproximácie možno použiť veľkosť odchýlky, ktoré pri výbere žiť približnej reprezentácie X závisí na Y by mala dať prednosť ten, ktorý má najmenší hodnota súčtu E aj na všetkých miestach. Nie všetky sú však také jednoduché, pretože spolu s pozitívnymi odchýlkami budú prakticky negatívne. Túto otázku môžete vyriešiť pomocouodchýlkových modulov alebo ich štvorcov. Posledná metóda získala najrozšírenejšiu. Používa sa v mnohých oblastiach, vrátane regresnej analýzy v programe Excel, jeho implementácia sa vykonáva pomocou dvoch vstavaných funkcií) a už dlho preukázala svoju účinnosť.

Metóda najmenších štvorcov

Ako je známe, Excel má vstavanú funkciu automatického sumy, ktorá umožňuje vypočítať hodnoty všetkých hodnôt nachádzajúcich sa vo vybranom rozsahu. Takže nič nám neumožňuje vypočítať hodnotu výrazu (e 1 2 + e 2 2 + e 3 2 + e n 2). V matematickom zázname má formu: Keďže sa pôvodne rozhodlo priblížiť pomocou priamky, máme: Problém nájdenia priamej čiary, ktorá najlepšie opisuje špecifickú závislosť množstiev X a Y, teda znižuje výpočet minimálnej funkcie dvoch premenných: K tomu musíme zaradiť čiastkové čiastkové deriváty nových premenných a a b na nulu a vyriešiť primitívny systém pozostávajúci z dvoch rovníc dvoch neznámych druhov: Po jednoduchých transformáciách vrátane rozdelenia na 2 a manipulácie so sumami získame: Jeho riešením, napríklad Cramerovou metódou, získame stacionárny bod s určitými koeficientmi a * a b *. Toto je minimum, to znamená predpovedať, aký obrat na komodite bude v obchode v určitej oblasti, priamka y = a * x + b *, ktorá je regresným modelom pre daný príklad. Samozrejme, nedovoľuje vám nájsť presný výsledok, ale pomôže vám získať predstavu o tom, či si kúpiť alebo nie.oblasť úverového obchodu.

Čo sa realyzoavat metóda najmenších štvorcov v Exceli

v "Excel" je funkcia pre výpočet hodnoty MNC. Má nasledujúcu formu: "TREND" (pretože hodnota je Y, hodnota je X, nová hodnota je X; const.). Použiť vzorec na výpočet MNC v tabuľke programu Excel. Z tohto článku, ktorý by mal byť odráža výsledok výpočtu metódou najmenších štvorcov Excel zaviesť znak "=" Potom vyberte "Trends". V otvorenom okne vyplniť do príslušných políčok, zvýraznenie:

pre celý rad známych Y (v tomto prípade údaje o obrate);

rozsah x 1, x n, to znamená veľkosť obchodného priestoru;

známa, neznáma hodnota x, pre ktorú chcete zistiť veľkosť obratu (o ich umiestnení na liste viď. Nižšie).

Okrem toho vo vzorci existuje logická premenná "Constant". Pokiaľ ho zadať do príslušného poľa 1 znamenalo by to, že by sme mali vykonať výpočet, vzhľadom k tomu, že b = 0.
Ak chcete vedieť o počasí pre viac ako jednu hodnotu x, potom po zadaní vzorec nemali kliknite na «Enter», ale je potrebné zadať kombináciu klávesov Shift + Ctrl + Enter.

Niektoré funkcie

Regresná analýza môže byť dokonca k dispozícii čajníkom. Excel vzorec predpovedať hodnotu poľa neznámych premenných - ďalej len "trendy" - možno použiť aj tí, ktorí nikdy nepočuli o metóde najmenších štvorcov. Stačí, aby ste poznali niektoré prvky jej práce. Najmä:

Ak nastavíte rozsah známych hodnôt premennej y v jednom riadku alebo stĺpci, potomKaždý riadok (stĺpec) so známymi hodnotami x bude vnímaný programom ako jedna premenná.

Ak okno TENDENCE neurčuje rozsah so známym x, potom ak použijete funkciu v programe Excel, program ju považuje za pole pozostávajúce z celých čísel, ktorých počet zodpovedá rozsahu so špecifikovanými hodnotami premennej y.

Na získanie množstva predpokladaných hodnôt na výstupe by sa mal výraz pre výpočet trendu uviesť ako vzorec poľa.

Ak nie sú zadané žiadne nové hodnoty x, funkcia "TREND" ich považuje za rovnú známej hodnote. Ak nie sú uvedené, potom pole je brané ako argument 1; 2; 3; 4, čo je merateľný rozsah s už nastavenými parametrami y.

Rozsah obsahujúci nové hodnoty x by mal pozostávať z rovnakých alebo viacerých riadkov alebo stĺpcov ako rozsah so zadanými hodnotami y. Inými slovami, mali by byť zodpovedajúce nezávislé premenné.

Množina známych hodnôt x môže obsahovať niekoľko premenných. Avšak, ak je to len jedna, rozsahy s danými hodnotami x a y by mali byť proporcionálne. V prípade viacerých premenných sa vyžaduje, aby rozsah so zadanými hodnotami y bol v jednom riadku alebo v jednom riadku.

Funkcia FORECAST

Regresná analýza v programe Excel je implementovaná s niekoľkými funkciami. Jeden z nich sa nazýva "FORECAST". Je to podobné ako "TRENDS", to znamená, že produkuje výsledok výpočtov pomocou metódy najmenších štvorcov. Avšak len pre jednu X, pre ktorú je neznáma hodnota Y. Teraz poznáte vzorce v programe Excel pre figuríny, ktoré to umožňujúpredpovedať hodnotu budúcej hodnoty jedného alebo iného ukazovateľa v súlade s lineárnym trendom.