Metoda nejmenších čtverců v aplikaci Excel. Regresní analýza

Metoda nejmenších čtverců (OLS) se vztahuje na oblast regresní analýzy. Má mnoho využití, protože umožňuje přibližné zastoupení dané funkce jinými jednoduššími. OLS může být mimořádně užitečné při zpracování pozorování a aktivně se používá k vyhodnocení určitých veličin z výsledků měření ostatních obsahujících náhodné chyby. Z tohoto článku se dozvíte, jak implementovat výpočet nejmenších čtverců v aplikaci Excel.

Vyjádření problému na konkrétním příkladu

Předpokládejme, že existují dva parametry X a Y. A Y závisí na X. Vzhledem k tomu, Nadnárodní nás zajímá z hlediska regresní analýzy (Excel ve jsou jeho metody implementovány pomocí vestavěných funkcí), měli byste okamžitě jít na zvážení konkrétního úkolu.

Takže nechte X - obchodní oblast obchodu s potravinami, měřená v metrech čtverečních, a Y - roční obrat, definovaný v milionech rublů.

Je třeba předpovídat, jaký obrat (Y) bude v obchodě, pokud má jednu nebo druhou prodejní plochu. 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 počátečních údajů použitých pro predikci

Řekněme, že máme stůl postavený podle údajů pro n obchody.

Podle matematické statistiky budou výsledky více či méně správné, pokud budou zkoumány údaje o minimálně 5-6 objektech. Kromě toho nemůžete použít "anomální" výsledky. Konkrétně může mít elitní malý butik obrat v době vyšší než obrat velkých prodejen třídy "masmarket".

Podstata metody

Údaje tabulky mohou být reprezentovány na kartézské rovině ve formě bodů M₁ (x₁, y₁), hellip- M_n (x_n, y_n). Nyní je řešení problému omezeno na výběr přibližovací funkce y = f (x), která má graf, který prochází co nejblíže bodům M_1, M_{2, ..}M_n.

Samozřejmě můžete použít polynom s vysokým stupněm, ale tato možnost je nejen obtížně implementovatelná, ale prostě není správná, protože neodpovídá hlavnímu trendu, který musíte zjistit. Nejvhodnějším řešením je najít přímku y = ax + b, která nejlépe přibližuje experimentální data, přesněji koeficienty - a a b.

Odhad přesnosti

Pro jakoukoli aproximaci se odhad jejich přesnosti stává obzvláště důležitým. Označujeme e_i rozdíl (odchylka) mezi funkčními a experimentálními hodnotami pro bod x_i, tj. e_i = y_i- f (x_i).

Je zřejmé, že pro odhad přesnosti aproximace lze použít součet odchylek, tj. Při výběru přímky pro přibližné zastoupení závislostí X na Y bychom měli dát přednost tomu, který má nejmenší hodnotu součtu e_i ve všech zvažovaných bodech. Avšak ne všechno je tak jednoduché, že spolu s pozitivními odchylkami budou prakticky negativní.

Problém můžete vyřešit pomocí modulů odchylek nebo jejich čtverců. Poslední metoda byla nejčastěji používána. Používá se v mnoha oblastech, včetně regresní analýzy (v aplikaci Excel, implementuje se pomocí dvou vestavěných funkcí) a již dlouho se ukázala jako účinná.

Metoda nejmenších čtverců

V aplikaci Excel, jak víte, je vestavěná funkce automatické sumy, která umožňuje vypočítat hodnoty všech hodnot umístěných ve zvoleném rozsahu. Nic nám tedy neumožňuje vypočítat hodnotu výrazu (např₁² + e₂² + e₃²+ ... e_n²).

V matematické notaci má tento tvar:

Od počátku bylo učiněno rozhodnutí přiblížit se přímou čárou:

Problém nalezení linky, která nejlépe popisuje specifickou závislost veličin X a Y, se tedy sníží na výpočet minimální funkce dvou proměnných:

Abychom toho dosáhli, musíme rovnat nulovým dílčím derivátům s ohledem na nové proměnné a a b a vyřešit primitivní systém sestávající ze dvou rovnic se dvěma neznámými:

Po jednoduchých přeměnách, včetně rozdělení na 2 a manipulaci s částkami, získáváme:

Řešením tohoto problému, například Cramerovou metodou, získáme stacionární bod s některými koeficienty a^{* * *} a b^{* * *}. To je minimum, to znamená předpovídat, jaký obrat bude v obchodě pro určitou oblast, přímka y = a^{* * *}x + b^{* * *}, což je regresní model pro daný příklad. Samozřejmě vám to neumožní najít přesný výsledek, ale pomůže vám získat představu o tom, zda půjde o koupi určité oblasti.

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

V "Excel" je funkce pro výpočet hodnoty OLS. Má následující podobu: "TRENDS" (známé hodnoty Y-známých hodnot X-nových hodnot X-const.). V tabulce používáme vzorec pro výpočet OLS v aplikaci Excel.

Chcete-li to provést, zadejte znak "=" v buňce, ve kterém by měl být zobrazen výsledek výpočtu metodou nejmenších čtverců v aplikaci Excel a vyberte funkci "TRENDS". 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₁, hellip-x_n, tedy velikost obchodních prostor;
• a známé a neznámé hodnoty x, pro které je třeba zjistit velikost obratu (informace o jejich umístění na pracovním listu, viz níže).

Navíc vzorec obsahuje logickou proměnnou "Konst". Zadáte-li 1 do příslušného pole, znamená to, že byste měli provést výpočty za předpokladu, že b = 0.

Pokud potřebujete znát předpověď pro více než jednu hodnotu x, pak po zadání vzorce byste neměli stisknout "Enter", ale musíte na klávesnici zadat "Shift" + "Control" + "Enter".

Některé funkce

Regresní analýzu lze získat i pomocí varných konvic. Vzorec Excel pro předpovídat hodnotu souboru neznámých proměnných - "TRENDS" - lze použít i ti, kteří o metodě nejmenších čtverců nikdy neslyšeli. Nestačí jen poznat některé rysy její práce. Zejména:

• Pokud uspořádáte rozsah známých hodnot proměnné y v jednom řádku nebo sloupci, pak každý řádek (sloupec) se známými hodnotami x bude vnímán programem jako samostatná proměnná.
• Pokud není v okně "TRENDS" zadán rozsah se známým x, pak v případě použití funkce v aplikaci Excel bude program považovat za pole obsahující celá čísla, jejichž počet odpovídá rozsahu s danými hodnotami proměnné y.
• Chcete-li na výstupu získat pořadí "předpovězených" hodnot, musí být výraz pro výpočet trendu zadán jako vzorec pole.
• Pokud nejsou zadány nové hodnoty x, funkce "TRENDS" je považuje za rovna známému. Pokud nejsou specifikovány, pak pole 1 - 2 - 3 - 4 - hellip - je bráno jako argument, který je úměrný rozsahu s již zadanými parametry y.
• Rozsah obsahující nové hodnoty x musí obsahovat stejné nebo více řádků nebo sloupců jako rozsah s danými hodnotami y. Jinými slovy, musí být přiměřené nezávislým proměnným.
• Pole s známými hodnotami x může obsahovat několik proměnných. Je-li však pouze jedna, je třeba, aby rozsahy s danými hodnotami x a y byly přiměřené. V případě několika proměnných je nutné, aby rozsah s danými hodnotami y byl obsažen v jednom sloupci nebo v jednom řádku.

Funkce "PREDICTION"

Regresní analýza v aplikaci Excel je implementována pomocí několika funkcí. Jeden z nich se nazývá "PREDICTION". Je to podobné jako "TRENDS", tj. Výsledkem výpočtů je metoda nejmenších čtverců. Pouze pro jednu X, pro kterou není hodnota Y známa.

Nyní znáte vzorce v aplikaci Excel for Dummies, které vám umožňují předpovědět hodnotu budoucí hodnoty daného ukazatele podle lineárního trendu.