Vytvoření modelu lineární regrese v Excelu

Lineární regrese je datový graf, který graficky zobrazuje lineární vztah mezi nezávislou a závislou proměnnou. Obvykle se používá k vizuálnímu zobrazení síly vztahu a rozptylu výsledků - to vše za účelem vysvětlení chování závislé proměnné.

Řekněme, že jsme chtěli vyzkoušet sílu vztahu mezi množstvím konzumované zmrzliny a obezitou. Vzali bychom nezávislou proměnnou, množství zmrzliny a spojili ji se závislou proměnnou, obezitou, abychom zjistili, zda existuje vztah. Vzhledem k regresi je grafické znázornění tohoto vztahu, čím menší je variabilita dat, tím silnější vztah a tím těsnější přizpůsobení se regresní linii.

Klíč s sebou

• Lineární regrese modeluje vztah mezi závislou a nezávislou proměnnou (proměnnými).
• Regresní analýzu lze dosáhnout, pokud jsou proměnné nezávislé, neexistuje heteroscedasticita a chybové termíny proměnných nejsou korelovány.
• Modelování lineární regrese v Excelu je jednodušší pomocí nástroje Data Analysis ToolPak.

Důležité úvahy

Existuje několik kritických předpokladů o vaší datové sadě, které musí být pravdivé pro provedení regresní analýzy:

1. Proměnné musí být skutečně nezávislé (pomocí testu na kvadrát).
2. Data nesmí mít různé odchylky chyb (to se nazývá heteroskedasticita (také hláskovaná heteroscedasticita)).
3. Chybové podmínky každé proměnné musí být nekorelované. Pokud ne, znamená to, že proměnné jsou sériově korelovány.

Pokud tyto tři věci zní komplikovaně, jsou. Důsledkem toho, že některá z těchto úvah není pravdivá, je však zkreslený odhad. V podstatě byste zkreslili vztah, který měříte.

Výstup regrese v Excelu

Prvním krokem při provádění regresní analýzy v Excelu je opětovné ověření, zda je nainstalován bezplatný Excel Data Data ToolPak. Tento plugin usnadňuje výpočet rozsahu statistik. Není nutné mapovat lineární regresní čáru, ale zjednodušuje vytváření statistických tabulek. Chcete-li ověřit, zda je nainstalován, vyberte na panelu nástrojů „Data“. Pokud je možnost „Analýza dat“, je tato funkce nainstalována a připravena k použití. Pokud není nainstalována, můžete požádat o tuto možnost kliknutím na tlačítko Office a výběrem možnosti „Excel“.

Pomocí nástroje Data Analysis ToolPak je vytvoření regresního výstupu jen několik kliknutí.

Nezávislá proměnná spadá do rozsahu X.

Vzhledem k výnosům S&P 500 řekněme, že chceme vědět, zda můžeme odhadnout sílu a vztah výnosů z akcií Visa (V). Zásoba Visa (V) vrací data naplňuje sloupec 1 jako závislou proměnnou. S&P 500 vrací data naplňuje sloupec 2 jako nezávislou proměnnou.

1. Na panelu nástrojů vyberte „Data“. Zobrazí se nabídka „Data“.
2. Vyberte „Analýza dat“. Zobrazí se dialogové okno Analýza dat - nástroje pro analýzu.
3. Z nabídky vyberte „Regrese“ a klikněte na „OK“.
4. V dialogu Regrese klikněte na pole „Vstupní rozsah Y“ a vyberte závislá data proměnné (návratnost zásob Visa (V)).
5. Klikněte na pole „Input X Range“ a vyberte nezávislá proměnná data (návratnost S&P 500).
6. Výsledky spustíte kliknutím na „OK“.

[Poznámka: Pokud se tabulka zdá malá, klikněte pravým tlačítkem myši na obrázek a otevřete na nové kartě pro vyšší rozlišení.]

Interpretujte výsledky

Použitím těchto dat (stejných jako v našem článku na druhou mocninu) získáme následující tabulku:

Hodnota R2, také známá jako koeficient stanovení, měří podíl variace v závislé proměnné vysvětlené nezávislou proměnnou nebo jak dobře regresní model odpovídá datům. Hodnota R2 je v rozmezí 0 až 1 a vyšší hodnota znamená lepší přizpůsobení. Hodnota p nebo pravděpodobnost se také pohybuje v rozmezí 0 až 1 a označuje, zda je test významný. Na rozdíl od hodnoty R2 je menší p-hodnota příznivá, protože naznačuje korelaci mezi závislými a nezávislými proměnnými.

Mapování regrese v Excelu

Můžeme zmapovat regresi v Excelu zvýrazněním dat a zmapováním jako rozptylový graf. Chcete-li přidat regresní čáru, vyberte v nabídce Nástroje grafu možnost Rozvržení. V dialogovém okně vyberte „Trendline“ a poté „Lineární Trendline“. Chcete-li přidat hodnotu R ², vyberte v nabídce Trendline možnost Další možnosti trendu. Nakonec vyberte možnost Zobrazit hodnotu čtvercového R v grafu. Vizuální výsledek shrnuje sílu vztahu, i když na úkor neposkytnutí tolik podrobností jako tabulka výše.