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

• Úvod do lineární regrese 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 -

1. Jak připravit výplatní listinu v Excelu?
2. Použití MAX Formula v Excelu
3. Výukové programy o buněčných referencích v Excelu
4. Vytvoření regresní analýzy v Excelu
5. Lineární programování v Excelu