Regrese v Excelu je způsob, jak automatizovat statistický proces porovnávání několika sad informací, abyste viděli, jak změny v nezávislých proměnných ovlivňují změny v závislých proměnných. Pokud jste někdy chtěli najít korelaci mezi dvěma věcmi, použití regresní analýzy v Excelu je jedním z nejlepších způsobů, jak toho dosáhnout.
Pokyny v tomto článku platí pro Excel 2019, Excel 2016, Excel 2013, Excel 2010.
Co znamená regrese?
Regrese je přístup statistického modelování, který analytici používají k určení vztahů mezi více proměnnými.
Regresní analýza začíná jednou proměnnou, kterou se pokoušíte analyzovat, a nezávislými proměnnými, které testujete, abyste zjistili, zda tuto jedinou proměnnou ovlivňují. Analýza se zaměřuje na změny v nezávislých proměnných a pokouší se tyto změny korelovat s výslednými změnami v jediné (závislé) proměnné.
To může znít jako pokročilá statistika, ale Excel tuto komplexní analýzu zpřístupňuje komukoli.
Provádění lineární regrese v Excelu
Nejjednodušší formou regresní analýzy je lineární regrese. Jednoduchá lineární regrese se dívá na vztah pouze mezi dvěma proměnnými.
Například následující tabulka ukazuje údaje obsahující počet kalorií, které člověk každý den snědl, a jeho váhu v daný den.
Protože tato tabulka obsahuje dva sloupce dat a jedna proměnná by mohla mít vliv na druhou, můžete na tato data spustit regresní analýzu pomocí Excelu.
Povolení doplňku analytického nástroje
Než budete moci používat funkci regresní analýzy aplikace Excel, musíte na obrazovce Možnosti aplikace Excel povolit doplněk Analysis ToolPak.
-
V Excelu vyberte nabídku Soubor a zvolte Options.
Image -
V levé navigační nabídce vyberte Doplňky. Poté se ujistěte, že je v poli Manage vybráno Doplňky Excelu.
Image -
Nakonec vyberte tlačítko Go.
Image -
Ve vyskakovacím okně doplňků. Povolte Analysis ToolPack zaškrtnutím políčka před ním a vyberte OK.
Image
Nyní, když je nástroj Analysis ToolPak povolen, jste připraveni začít provádět regresní analýzu v Excelu.
Jak provést jednoduchou lineární regresi v Excelu
Na příkladu tabulky hmotnosti a kalorií můžete v Excelu provést lineární regresní analýzu následovně.
-
Vyberte nabídku Data. Poté ve skupině Analysis vyberte Analýza dat.
Image -
V okně Analýza dat vyberte ze seznamu Regrese a klikněte na OK.
Image -
Vstupní rozsah Y je rozsah buněk, který obsahuje závislou proměnnou. V tomto příkladu je to hmotnost. Vstup X Rozsah je rozsah buněk, který obsahuje nezávislou proměnnou. V tomto příkladu je to sloupec kalorií.
Image -
Vyberte Labels pro buňky záhlaví a poté vyberte Nový list pro odeslání výsledků do nového listu. Vyberte OK, aby Excel provedl analýzu a odeslal výsledky do nového listu.
Image -
Prohlédněte si nový pracovní list. Výstup analýzy má řadu hodnot, kterým musíte porozumět, abyste mohli výsledky interpretovat.
Image Každé z těchto čísel má následující význam:
- Multiple R: Korelační koeficient. 1 znamená silnou korelaci mezi těmito dvěma proměnnými, zatímco -1 znamená, že existuje silný negativní vztah. 0 znamená, že neexistuje žádná korelace.
- R Čtverec: Koeficient determinace, který ukazuje, kolik bodů mezi dvěma proměnnými spadá na regresní přímku. Statisticky se jedná o součet čtverců odchylek od průměru.
- Upravený čtverec R: Statistická hodnota zvaná R čtverec, která je upravena podle počtu nezávislých proměnných, které jste vybrali.
- Standardní chyba: Jak přesné jsou výsledky regresní analýzy. Pokud je tato chyba malá, pak jsou vaše výsledky regrese přesnější.
- Pozorování: Počet pozorování ve vašem regresním modelu.
Zbývající hodnoty ve výstupu regrese vám poskytnou podrobnosti o menších složkách v regresní analýze.
- df: Statistická hodnota známá jako stupně volnosti související se zdroji rozptylu.
- SS: Součet čtverců. Poměr zbytkového součtu čtverců k celkovému SS by měl být menší, pokud většina vašich dat odpovídá regresní přímce.
- MS: Střední čtverec regresních dat.
- F: F statistika (F-test) pro nulovou hypotézu. To poskytuje význam regresního modelu.
- Významnost F: Statistická hodnota známá jako P-hodnota F.
Pokud nerozumíte statistikám a výpočetním regresním modelům, nebudou mít hodnoty ve spodní části souhrnu velký význam. Nicméně násobek R a R čtverec jsou dva nejdůležitější.
Jak vidíte, v tomto příkladu mají kalorie silnou korelaci s celkovou hmotností.
Vícenásobná lineární regresní analýza v Excelu
Chcete-li provést stejnou lineární regresi, ale s více nezávislými proměnnými, vyberte celý rozsah (více sloupců a řádků) pro Vstup X Rozsah.
Při výběru více nezávislých proměnných je méně pravděpodobné, že najdete tak silnou korelaci, protože existuje tolik proměnných.
Regresní analýza v Excelu vám však může pomoci najít korelace s jednou nebo více z těchto proměnných, o kterých si možná neuvědomujete, že existují, pouze ruční kontrolou dat.
