Lineární regrese v Excelu Jak udělat lineární regresi v Excelu?

Lineární regrese v Excelu (obsah)

Úvod do lineární regrese v Excelu
Metody použití lineární regrese v Excelu

Úvod do lineární regrese v Excelu

Lineární regrese je statistická technika / metoda používaná ke studiu vztahu mezi dvěma spojitými kvantitativními proměnnými. V této technice se pro predikci hodnoty závislé proměnné používají nezávislé proměnné. Pokud existuje pouze jedna nezávislá proměnná, jedná se o jednoduchou lineární regresi, a pokud je počet nezávislých proměnných více než jedna, jedná se o násobnou lineární regresi. Modely lineární regrese mají vztah mezi závislými a nezávislými proměnnými tím, že k pozorovaným datům odpovídají lineární rovnici. Linear odkazuje na skutečnost, že k přizpůsobení našich dat používáme řádek. Závislé proměnné používané v regresní analýze se také nazývají odpověď nebo predikované proměnné a nezávislé proměnné se nazývají také vysvětlující proměnné nebo prediktory.

Lineární regresní přímka má rovnici tohoto typu: Y = a + bX;

Kde:

X je vysvětlující proměnná,
Y je závislá proměnná,
b je sklon přímky,
a je průnik y (tj. hodnota y, když x = 0).

Metoda nejmenších čtverců se obecně používá v lineární regresi, která vypočítává nejvhodnější linii pro pozorovaná data minimalizováním součtu čtverců odchylky datových bodů od linie.

Metody použití lineární regrese v Excelu

Tento příklad vás naučí metody provádění analýzy lineární regrese v Excelu. Pojďme se podívat na několik metod.

Tuto šablonu lineární regrese Excel si můžete stáhnout zde - šablonu lineární regrese Excel

Metoda # 1 - Bodový graf s trendem

Řekněme, že máme dataset některých jedinců s jejich věkem, indexem biomasy (BMI) a částkou, kterou za měsíc utratili za léčebné výdaje. Nyní s nahlédnutím do charakteristik jednotlivců, jako je věk a BMI, chceme zjistit, jak tyto proměnné ovlivňují léčebné výdaje, a proto je použít k provádění regrese a odhadu / predikci průměrných léčebných nákladů u některých konkrétních jedinců. Podívejme se nejprve, jak jen věk ovlivňuje lékařské výdaje. Uvidíme dataset:

Částka na zdravotní výdaje = b * věk + a

Vyberte dva sloupce datové sady (xay), včetně záhlaví.

Klikněte na 'Vložit' a rozbalte rozbalovací nabídku 'Bodový graf' a vyberte miniaturu 'Bodový' (první)

Nyní se objeví rozptylový graf a na to bychom nakreslili regresní čáru. Chcete-li to provést, klepněte pravým tlačítkem myši na libovolný datový bod a vyberte možnost Přidat trendline.

Nyní v podokně „Formát trendline“ napravo vyberte „Lineární trend“ a „Zobrazit rovnici na grafu“.

Vyberte 'Zobrazit rovnici na grafu'.

Graf můžeme improvizovat podle našich požadavků, jako je přidání názvů os, změna měřítka, barvy a typu čáry.

Po vylepšení grafu získáme výstup.

Poznámka: V tomto typu regresního grafu by závislá proměnná měla být vždy na ose y a nezávislá na ose x. Pokud je graf vykreslen v obráceném pořadí, pak buď přepněte osy v grafu, nebo zaměňte sloupce v datové sadě.

Metoda # 2 - Analytická metoda doplňku

Analytický analytický nástroj není ve výchozím nastavení povolen a musíme to udělat ručně. Udělat to tak:

Klikněte na nabídku „Soubor“.

Poté klikněte na 'Možnosti'.

V poli „Spravovat“ vyberte „Doplňky aplikace Excel“ a klikněte na „Přejít“

Vyberte „Analytický nástroj“ -> „OK“

Tím se na kartu „Data“ přidají nástroje „Analýza dat“. Nyní provedeme regresní analýzu:

Na kartě „Data“ klikněte na „Analýza dat“

Vyberte „Regrese“ -> „OK“

Zobrazí se dialogové okno regrese. Vyberte rozsah vstupu Y a rozsah vstupu X (náklady na zdravotní péči a věk). V případě vícenásobné lineární regrese můžeme vybrat více sloupců nezávislých proměnných (jako kdybychom chtěli vidět vliv BMI také na léčebné náklady).
Zaškrtněte políčko Štítky a zahrňte záhlaví.
Vyberte požadovanou možnost výstupu.
Zaškrtněte políčko „zbytky“ a klikněte na „OK“.

Nyní bude výstup naší regresní analýzy vytvořen v novém pracovním listu, který bude uvádět statistiku regrese, ANOVA, zbytky a koeficienty.

Interpretace výstupu:

Regresní statistika říká, jak dobře regresní rovnice odpovídá datům:

Násobek R je korelační koeficient, který měří sílu lineárního vztahu mezi dvěma proměnnými. Leží mezi -1 a 1 a jeho absolutní hodnota zobrazuje sílu vztahu s velkou hodnotou, která naznačuje silnější vztah, nízká hodnota označuje zápornou a nulovou hodnotu, která označuje žádný vztah.
Čtverec R je koeficient determinace používaný jako indikátor dobré kondice. Leží mezi 0 a 1, s hodnotou blízkou 1, což znamená, že model je vhodný. V tomto případě je 0, 57 = 57% y-hodnot vysvětleno x-hodnotami.
Upravený čtverec R je čtverec R upravený pro počet prediktorů v případě vícenásobné lineární regrese.
Standardní chyba zobrazuje přesnost regresní analýzy.
Pozorování znázorňují počet modelových pozorování.
Anova říká úroveň variability v regresním modelu.

To se obvykle nepoužívá pro jednoduchou lineární regresi. „Hodnoty významnosti F“ však ukazují, jak spolehlivé jsou naše výsledky, s hodnotou vyšší než 0, 05, která navrhuje zvolit jiného prediktora.

Koeficienty jsou nejdůležitější částí používanou k sestavení regresní rovnice.

Naše regresní rovnice by tedy byla: y = 16, 891 x - 355, 32. To je stejné jako u metody 1 (bodový graf s trendovou čarou).

Pokud nyní chceme předpovědět průměrné náklady na zdravotní péči ve věku 72 let:

Takže y = 16, 891 * 72 -355, 32 = 860, 832

Tímto způsobem můžeme předpovědět hodnoty y pro jakékoli jiné hodnoty x.

Zbytky označují rozdíl mezi skutečnými a předpovězenými hodnotami.

Poslední metoda pro regresi není tak běžně používaná a vyžaduje statistické funkce jako slope (), intercept (), correl () atd. K provedení regresní analýzy.

Zajímavosti o lineární regresi v Excelu

Regresní analýza se obvykle používá ke zjištění, zda existuje statisticky významný vztah mezi dvěma sadami proměnných.
Používá se k predikci hodnoty závislé proměnné na základě hodnot jedné nebo více nezávislých proměnných.
Kdykoli si přejeme přizpůsobit lineární regresní model ke skupině dat, pak by měl být rozsah dat pečlivě sledován, jako bychom použili regresní rovnici k predikci jakékoli hodnoty mimo tento rozsah (extrapolace), pak to může vést k nesprávným výsledkům.

Doporučené články

Toto je průvodce lineární regresí v Excelu. Zde diskutujeme o tom, jak provést lineární regresi v Excelu, spolu s praktickými příklady a šablonou Excel ke stažení. Můžete si také prohlédnout naše další doporučené články -