Metoda nejmenších čtverců (MNC) patří do oblasti regresní analýzy. Má mnoho aplikací, protože umožňuje provádět přibližné zastoupení dané funkce jinými, jednoduššími. MNC se může ukázat jako velmi užitečné při léčbě pozorování a aktivně se používá k vyhodnocení některých hodnot naměřených jinými, které obsahují náhodné chyby. Z tohoto článku se dozvíte, jak implementovat výpočet nejmenších čtverců v aplikaci Excel.

Problém Konkrétním příkladem

Předpokládejme, že existují dva indikátor X a Y. A Y závisí na X. Vzhledem k tomu, nadnárodních společností nás zajímají, pokud jde o regresní analýzy v metodách Excel implementována jej pomocí vestavěných funkcí), měli byste okamžitě totéž platí pro zvážení konkrétního úkolu.


Tak X - Obchod s potravinami obchodní plochy, měřeno v metrech čtverečních a Y - roční obrat určený v milionech rublů. Je nutné předpovídat, jaký obrat (Y) zboží se bude nacházet v blízkosti obchodu, pokud má jednu nebo druhou obchodní oblast. Je zřejmé, že funkce Y = f (X) se zvyšuje, protože hypermarket prodává více zboží než stánku.

Několik slov o správnosti zdrojových dat používaných předvídat

Předpokládejme, že máme tabulku konstruovanou podle n obchody.

, X



x 1



x 2







xn



, y



y 1



y 2







yn

Podlematematická statistika, výsledky budou víceméně správné, pokud budou studovány údaje o minimálně 5-6 objektech. Kromě toho nelze použít "abnormální" výsledky. Obzvláště elitní malý butik může mít obrat více než obrat velkých maloobchodních prodejen třídy "masového trhu".

Způsob

Tyto tabulky lze čerpat kartézské rovině jako body M 1 (x 1, y 1), M n (x n, y n). Nyní snížena na řešení problému funkce výběru přibližování y = f (x), která má plán, který je tak blízko, jak je to možné, aby body M 1 M 2 M n. Samozřejmě, můžete použít polynom vysokým stupněm, ale tato možnost je nejen trudnorealyzuem, ale prostě nesprávné, protože neodrážejí hlavní trend, který také třeba objevit. Nejvíce rozumné řešení je najít přímky y = ax + b, který se nejvíce blíží experimentální údaje, přesnější, koeficienty a a b.

Odhad přesnosti

Při jakémkoli přiblížení má hodnocení její přesnosti zvláštní význam. E i označuje rozdíl (odchylka) mezi funkčními a experimentální hodnoty pro body x i, tj e i = r i - f (x i). Je zřejmé, že pro posouzení správnosti aproximace lze použít velikost odchylky, které při výběru žít přibližné reprezentace X závisí na Y by měla dát přednost ten, který má nejmenší hodnota součtu E i na všech místech. Nicméně ne všechny jsou tak jednoduché, protože spolu s pozitivními odchylkami budou prakticky negativní. Tuto otázku můžete vyřešit pomocíodchylovací moduly nebo jejich čtverce. Poslední metoda získala nejrozšířenější metodu. Používá se v mnoha oblastech, včetně regresní analýzy v aplikaci Excel, její implementace se provádí pomocí dvou vestavěných funkcí) a již dlouho prokázala svou účinnost.

Metoda nejmenších čtverců

Jak je známo, Excel má vestavěnou funkci automatického součtu, která umožňuje vypočítat hodnoty všech hodnot umístěných ve zvoleném rozsahu. Takže nic nám nezabrání vypočítat hodnotu výrazu (e 1 2 + e 2 2 + e 3 2 + e n 2). V matematickém záznamu má formu: Jelikož se původně rozhodlo přibližovat se pomocí přímky, máme: Problém nalezení přímky, která nejlépe popisuje specifickou závislost veličin X a Y, tedy snižuje výpočet minimální funkce dvou proměnných: Abychom to dosáhli, musíme parciální dílčí deriváty nových proměnných a a b nulovat a vyřešit primitivní systém sestávající ze dvou rovnic dvou neznámých druhů: Po jednoduchých transformacích, včetně rozdělení na 2 a manipulaci se součtem, získáváme: Řešením, například Cramerovou metodou, získáme stacionární bod s určitými koeficienty a * a b *. To je minimum, tj. Předpovídat, jaký bude obrat komodity v obchodě v určité oblasti, přímá přímka y = a * x + b *, která je regresním modelem pro daný příklad. Samozřejmě vám nedovolí najít přesný výsledek, ale pomůže vám získat představu o tom, zda si koupíteoblast úvěrového obchodu.

Jak implementovat metodu nejmenších čtverců v aplikaci Excel

V aplikaci Excel existuje funkce pro výpočet hodnoty pro MNC. Má následující formu: "TREND" (protože hodnota je Y, hodnota je X, nová hodnota je X; const.). Použijte vzorec pro výpočet MNC v tabulce aplikace Excel. Chcete-li to provést, v buňce, ve které je výsledek výpočtu metodou nejmenších čtverců Excel zobrazen, uvedeme znaménko "=" a vybereme funkci "TREND". V otevřeném okně vyplňte příslušná pole a zvýrazněte:

rozsah známých hodnot pro Y (v tomto případě údaje o obratu);

rozsah x 1, x n, tedy velikost obchodního prostoru;

známých neznámých hodnot x, pro které je nutné zjistit velikost obratu (informace o jejich umístění na listu, viz níže).

Navíc ve vzorci existuje logická proměnná "Constant". Pokud zadáte pole 1 do příslušného pole, znamená to, že výpočet by měl být proveden, protože b = 0.
Pokud chcete znát předpověď pro více než jednu hodnotu x, pak po zadání vzorce byste neměli kliknout na "Enter" ale musíte na klávesnici kombinovat klávesy Shift + Ctrl + Enter.

Některé funkce

Regresní analýza může být dokonce k dispozici čajníkům. Vzorec Excel pro předpovídat hodnotu souboru neznámých proměnných - "TREND" - lze použít i ti, kteří nikdy neslyšeli metodu nejmenších čtverců. Stačí, abyste poznali některé rysy její práce. Zejména:

Pokud nastavíte rozsah známých hodnot proměnné y v jednom řádku nebo sloupci, pakKaždý řádek (sloupec) se známými hodnotami x bude vnímán programem jako jedna proměnná.

V případě, že pole „trend“ není specifikován řadu známých x, v případě, že funkce v programu Excel bude zacházet jako pole sestávající z celých čísel, jejichž počet odpovídá rozmezí daných hodnot proměnné y.

pro výstupní pole „předpovězené hodnoty pro výpočet trendů výraz by měl být zadán jako maticový vzorec.

Není-li stanoveno nové hodnoty x, potom funkce „Trends“ z nich rovněž známý. Pokud nejsou dány, pak pole je bráno jako argument 1; 2; 3; 4, což je měřitelný rozsah s již nastavenými parametry y.

se pohybují s obsahem nových hodnot x, by se měla skládat ze stejných nebo více řádků nebo sloupců, jako řada předem stanovené hodnoty y. Jinými slovy by to měly být odpovídající nezávislé proměnné.

Řada známých hodnot x může obsahovat několik proměnných. Je-li však pouze jedna, rozsahy s danými hodnotami x a y by měly být proporcionální. V případě několika proměnných, je třeba, aby v rozmezí od daných hodnot y vejde na jednu řádku nebo jednoho řádku.

Funkce FORECAST

Regresní analýza v aplikaci Excel je implementována s několika funkcemi. Jeden z nich se nazývá "FORECAST". Je to podobné jako "TRENDS", tj. Produkuje výsledek výpočtů metodou nejmenších čtverců. Avšak pouze jeden ze symbolů X, pro které je neznámé hodnota Y. Nyní, když víte, že vzorec v Excelu pro nechápavé, což umožňujepředpovědět hodnotu budoucí hodnoty jednoho nebo jiného ukazatele v souladu s lineárním trendem.