zpět na výpis    domů » excel » Analytický nástroj Regrese

Analytický nástroj Regrese

Publikováno: 5.7.2018

Analytický nástroj Regrese

V předchozích článcích jsem představil excelovske funkce INTERCEPT() a SLOPE() a funkci LINREGRESE() pro odhad regresního modelu. Funkce LINREGRESE() je již poměrně komplexní funkcí. Poskytuje kromě odhadů regresních parametrů také odhad jejich standardních chyb, koeficient determinace, F-test, standardní chybu odhadu závisle proměnné, ESS a RSS.

Nejkomplexnější nástroj, který MS Excel pro regresní analýzu nabízí, je však analytický nástroj Regrese. Nástroj Regrese je součástí doplňku Analytické nástroje. Poskytuje, kromě výše uvedených statistik, informace o p-value regresních parametrů a regresního modelu, konfidenční intervaly regresních parametrů, upravený koeficient determinace, hodnoty reziduí, graf regresní funkce apod.

Nástroj Regrese je demonstrován na notorický známé spotřební funkci, pomocí které zkoumáme závislost individuálních spotřebních výdajů na průměrných měsíčních příjmech v jednotlivých letech.

Doplněk Analytické nástroje

Doplněk Analytické nástroje najdete od verze MS Excel 2007 na kartě Data → sekce Analýza → tlačítko Analýza dat. Pokud doplněk na kartě Data nevidíte, tak jej musíte aktivovat na kartě SouborMožnostiDoplňky.

Možnosti MS Excelu - doplňky

V Doplňcích najdete všechny aktivní a neaktivní doplňky. Dole v rozbalovací nabídce Spravovat vyberte Doplňky Excelu a stiskněte tlačítko Přejít. Zobrazí se všechny doplňky, které jsou k dispozici. Požadovaný doplněk aktivujte zaškrtnutím dvoupolohového přepínače a stiskem tlačítka OK.

Aktivní a neaktivní doplňky v MS Excelu

Možnosti nástroje Regrese

Máte-li vyřešen předchozí krok, pokračujte stiskem tlačítka Analýza dat na kartě Data.

Analytické nástroje v MS Excel

V okně Analýza dat vyberte nástroj Regrese. Zobrazí se okno, ve kterém vyplníte vstupní data a nastavíte možnosti výstupu. Nastavit můžete například hladinu spolehlivosti jinou než 95 %, model bez úrovňové konstanty, graf regresní přímky, hodnoty reziduí apod.

Analytický nástroj Regrese v MS Excel - vstupní hodnoty

Základní a povinné vstupy jsou oblasti s hodnotami závisle proměnné y (Vstupní oblast Y), a hodnotami jedné nebo více nezávisle proměnných x (Vstupní oblast X). Výběr dalších vstupů závisí na požadavcích zadavatele a specifikaci modelu.

VstupPopis
PopiskyObsahují-li zadané oblasti proměnných názvy sloupců.
Konstatnta je nulaMá-li být model bez úrovňové konstanty, tj. b0 = 0.
Hladina spolehlivostiZměna hladiny spolehlivosti na požadovanou úroveň. Defaultně nastaveno na 95 %.

Hladina spolehlivosti určuje s jakou pravděpodobností se pokoušíme prokázat statistickou významnost regresních parametrů (t-test) a regresního modelu (F-test). Hladina spolehlivosti se také používá pro intervalové odhady regresních parametrů.

Individuální spotřební funkce

V následující tabulce jsou vstupní data pro odhad jednoduché spotřební funkce. Spotřební výdaje spotřebitele jsou reprezentovány závisle proměnnou y a disponibilní příjem nezávisle proměnnou x. Spotřební výdaje a disponibilní příjem jsou uvedeny jako průměrné roční hodnoty v tisících Kč.

Analytický nástroj Regrese v MS Excel - vstupní hodnoty

Výběrová spotřební funkce má lineární tvar Y = b0 + b1x. Jde o jednofaktorovou regresní funkci. Zajímají nás odhady parametrů b0 a b1, jejich kvalita a kvalita modelu jako celku. Statisticky průkazný model může být použit pro předpověď budoucích hodnot spotřebních výdajů na základě očekávaného disponibilní příjmu v následujících letech.

V našem konkrétním případě odhadujeme model s úrovňovou konstantou, vybrané oblasti dat neobsahují popisky a volíme hladinu spolehlivosti 99 %.

Analytický nástroj Regrese v MS Excel - vstupní hodnoty

Dále požadujeme, aby výstup Regrese obsahoval hodnoty reziduální složky a graf regresní přímky.

Výstup nástroje Regrese

Výstup regresní analýzy obsahuje 3 základní tabulky, které se týkají kvality regresního modelu, rozptylu ANOVA, odhadů a kvality regresních parametrů. Detailněji je tato problematiky rozebrána v článku Testování regresního modelu.

První tabulka Regresní statistika obsahuje vypočtené statistiky, které vypovídají o shodě regresního modelu s empirickými daty. Excel však používá zcela odlišnou terminologii než je zvykem, proto jsem k jednotlivým názvům dopsal standardní názvy.

Nástroj Regrese - koeficient determinace a korelace

Druhá tabulka obsahuje analýzu rozptylu ANOVA. ANOVA rozkládá rozptyl empirických hodnot závisle proměnné Celkem (TSS) na rozptyl vyrovnaných hodnot Regrese (ESS) a rozptyl reziduální složky modelu Rezidua (RSS).

Nástroj Regrese - analýza ANOVA

Další důležitou statistikou v rámci tabulky ANOVA je F-test, kterým se testuje statistická významnost regresního modelu. Hodnota vypočteného F-testu je 5 806,73. Významnost F představuje hladinu významnosti, na které ještě můžeme zamítnout nulovou hypotézu o statistické nevýznamnosti modelu. Tato hodnota se standardně označuje jako F-value. Hodnota F-value svědčí o vysoké statistické průkaznosti modelu.

Třetí základní tabulka obsahuje odhady regresních parametrů bj, standardní chyby odhadů s(bj), testovací statistiku t-test, minimální hladinu významnosti p-value, na které ještě lze zamítnout nulovou hypotézu a intervaly spolehlivosti 95 % a 99 %.

Nástroj Regrese - odhad regresních parametrů

Na základě odhadů b0 a b1 nyní můžeme sestavit výběrovou regresní funkci Y v indexním tvaru.

Výběrová regresní přímka

Pomocí funkce můžeme pro konkrétní hodnotu xi dopočítat vyrovnané hodnoty regresní přímky.

Vyrovnané hodnoty spotřeby versus příjem

Vyrovnané hodnoty spotřebních výdajů Y mimo jiné obsahuje výstup s hodnotami reziduální složky.

Následující graf obsahuje vyrovnané body odhadnute spotřební funkce, které reprezentují regresní přímku.

Nástroj Regrese - graf regresní přímky

Nad rámec základního výstupu nás ještě zajímají hodnoty reziduální složky. Ty můžeme využít pro testování existence autokorelace v podobě grafického vývoje v čase a výpočet Durbin-Watsonova testu.

Nástroj Regrese - hodnoty reziduální složky

Líbí se vám článek? Dejte o něm vědět lidem na sociálních sítích a v diskusních fórech.

Google+

Sdílejte článek na sociálních sítích

Nahoru