Excel regresní analýza (obsah)
- Regresní analýza v Excelu
- Matematické vysvětlení regrese
- Jak provést lineární regresi v Excelu?
- # 1 - Regresní nástroj využívající analytický nástroj v Excelu
- # 2 - Regresní analýza pomocí Scatterplot s Trendline v Excelu
Regresní analýza v Excelu
Lineární regrese je statistická technika, která zkoumá lineární vztah mezi závislou proměnnou a jednou nebo více nezávislými proměnnými.
- Závislá proměnná (aka proměnná odpověď / výsledek): Je proměnná vašeho zájmu a kterou jste chtěli předpovídat na základě dostupných informací o nezávislé proměnné (proměnných).
- Nezávislá proměnná (aka vysvětlující / predikční proměnná): Je / jsou proměnná (proměnné), na které závisí proměnná odezvy. Což znamená, že se jedná o proměnné, pomocí kterých lze předpovídat proměnnou odezvy.
Lineární vztah znamená, že změna nezávislé proměnné způsobuje změnu závislé proměnné.
V zásadě existují také dva typy lineárních vztahů.
- Pozitivní lineární vztah: Když se nezávislá proměnná zvyšuje, zvyšuje se také závislá proměnná.
- Negativní lineární vztah: Když se nezávislá proměnná zvyšuje, závislá proměnná se snižuje.
To byly některé z nezbytných předpokladů, než se ve skutečnosti excelujete k regresní analýze.
Existují dva základní způsoby provedení lineární regrese v excelu pomocí:
- Nástroj regrese pomocí nástroje Analysis ToolPak
- Bodový graf s trendovou čarou
Ve skutečnosti existuje ještě jedna metoda, která používá k výpočtu lineární regrese manuální vzorce. Ale proč byste měli jít za to, když exceluje výpočty pro vás?
Proto budeme hovořit pouze o dvou výše diskutovaných metodách.
Předpokládejme, že máte údaje o výšce a hmotnosti 10 osob. Pokud tyto informace vykreslíte do grafu, podívejme se, co dává.
Jak ukazuje výše uvedený snímek obrazovky, lze lineární vztah nalézt v grafu Výška a hmotnost. Teď se do grafu moc nezaujímáme, v druhé části tohoto článku to tak nějak vykopeme.
Matematické vysvětlení regrese
Máme matematický výraz pro lineární regresi, jak je uvedeno níže:
Y = aX + b + ε
Kde,
- Y je závislá proměnná nebo proměnná odezvy.
- X je nezávislá proměnná nebo prediktor.
- a je sklon regresní linie. Což znamená, že když se X změní, dojde ke změně Y o „a“ jednotky.
- b je zachycující. Je to hodnota Y, když je hodnota X nula.
- ε je náhodný chybový termín. Vyskytuje se, protože předpokládaná hodnota Y se nikdy nebude přesně shodovat se skutečnou hodnotou pro dané X. Tento chybový termín se nemusíme obávat. Vzhledem k tomu, že existují některé programy, které vypočítávají tento chybový termín v backendu za vás. Excel je jedním z těchto programů.
V tom případě se rovnice stává,
Y = aX + b
Což lze reprezentovat jako:
Hmotnost = a * Výška + b
Pokusíme se zjistit hodnoty těchto aab pomocí metod, které jsme diskutovali výše.
Jak provést lineární regresi v Excelu?
Další článek vysvětluje základy regresní analýzy ve Excelu a ukazuje několik různých způsobů, jak provést lineární regresi v Excelu.
Tuto šablonu Excel regresní analýzy si můžete stáhnout zde - šablonu Excel regresní analýzy# 1 - Regresní nástroj využívající analytický nástroj v Excelu
V našem příkladu se pokusíme přizpůsobit regresi pro hodnoty hmotnosti (což je závislá proměnná) pomocí hodnot výšky (což je nezávislá proměnná).
- V tabulce Excel klikněte na Data Analysis (presented in Analysis Group) pod Data.
- Vyhledejte regresi . Vyberte ji a stiskněte ok.
- Použijte následující vstupy v podokně Regrese, které se otevře.
- Rozsah vstupu Y : Vyberte buňky, které obsahují závislou proměnnou (v tomto příkladu B1: B11)
- Rozsah vstupu X : Vyberte buňky, které obsahují vaši nezávislou proměnnou (v tomto příkladu A1: A11).
- Pokud vaše data obsahují názvy sloupců, zaškrtněte políčko Štítky (v tomto příkladu máme názvy sloupců).
- Úroveň spolehlivosti je ve výchozím nastavení nastavena na 95%, což lze změnit podle požadavků uživatelů.
- V části Možnosti výstupu můžete přizpůsobit, kde chcete vidět výstup regresní analýzy v Excelu. V tomto případě chceme vidět výstup na stejném listu. Proto daný rozsah odpovídajícím způsobem.
- Ve volbě Zbytky máte volitelné vstupy jako Zbytky, Reziduální grafy, Standardizované rezidua, Grafy přizpůsobení čar, které si můžete vybrat podle svých potřeb. V tomto případě zaškrtněte políčko Residuals, abychom viděli rozptyl mezi predikovanými a skutečnými hodnotami.
- V části Normální pravděpodobnost můžete vybrat Normální pravděpodobnostní grafy, které vám pomohou zkontrolovat normálnost prediktorů. Klikněte na OK .
- Excel vypočítá regresní analýzu za zlomek vteřin.
Doposud to bylo snadné a ne tak logické. Interpretovat tento výstup a získat z něj cenné poznatky je však složitý úkol.
Jednou důležitou součástí celého tohoto výstupu je R Square / Adjusted R Square pod tabulkou SUMMARY OUTPUT. Což poskytuje informace, jak dobrý je náš model. V tomto případě je R čtvercová hodnota 0, 9547. Což interpretuje, že model má 95, 47% přesnost (dobrá shoda). Nebo v jiném jazyce je informace o proměnné Y vysvětlena proměnnou X o 95, 47%.
Druhou důležitou součástí celého výstupu je tabulka koeficientů. Uvádí hodnoty koeficientů, které lze použít k vytvoření modelu pro budoucí předpovědi.
Nyní se naše regresní rovnice pro predikci stává:
Hmotnost = 0, 6746 * Výška - 38, 45508 (hodnota sklonu pro výšku je 0, 6746… a přestávka je -38, 45508…)
Dostali jste, co jste definovali? Definovali jste funkci, ve které musíte nyní zadat hodnotu Výška a dostanete hodnotu Hmotnost.
# 2 - Regresní analýza pomocí Scatterplot s Trendline v Excelu
Nyní uvidíme, jak v excelu dokážeme umístit regresní rovnici na samotný rozptyl.
- Vyberte svá dvě zbývající data (včetně záhlaví).
- Klikněte na Vložit a vyberte bodový graf v sekci grafů, jak je znázorněno na obrázku níže.
- Viz výstupní graf.
- Nyní musíme mít na tomto grafu regresní linii s nejmenším čtvercem. Chcete-li přidat tento řádek, klikněte pravým tlačítkem na kterýkoli z datových bodů v grafu a vyberte možnost Přidat trendline .
- To vám umožní mít trendovou linii s nejmenším čtvercem regrese, jak je uvedeno níže.
- Ve skupinovém rámečku Formát Trendline zaškrtněte políčko Zobrazit rovnici na grafu.
- To vám umožní vidět rovnici nejméně čtvercové regresní čáry v grafu.
Toto je rovnice, pomocí které můžeme předpovídat hodnoty hmotnosti pro jakoukoli danou sadu hodnot Výška.
Důležité informace o regresní analýze v Excelu
- Rozvržení trendové čáry můžete změnit v nabídce Formát Trendline v rozptylovém grafu.
- Při provádění regresní analýzy pomocí nástroje Data Analysis ToolPak v Excelu se vždy doporučuje podívat se na zbytkové grafy. To vám umožní lépe porozumět šíření skutečných hodnot Y a odhadovaných hodnot X.
- Jednoduchá lineární regrese v Excelu nepotřebuje ANOVA a upravený čtverec R ke kontrole. Tyto vlastnosti lze vzít v úvahu při vícenásobné lineární regresi. Což je nad rámec tohoto článku.
Doporučené články
Toto byl průvodce analýzou regrese v Excelu. Zde diskutujeme o tom, jak provést regresní analýzu v Excelu spolu s příklady aplikace Excel a šablonou Excel ke stažení. Můžete si také prohlédnout naše další doporučené články -
- Excel nástroj pro analýzu dat
- Vypočítejte ANOVA v Excelu
- Jak najít klouzavé průměry Excelu
- Příklady Z TEST v Excelu