Ustvarjanje linearnega regresijskega modela v Excelu

Linearna regresija je graf podatkov, ki graficira linearno razmerje med neodvisno in odvisno spremenljivko. Običajno se uporablja za vizualno prikaz trdnosti odnosa in razpršenosti rezultatov - vse z namenom razlage vedenja odvisne spremenljivke.

Recimo, da smo želeli preizkusiti moč razmerja med količino pojenega sladoleda in debelostjo. Vzeli bi neodvisno spremenljivko, količino sladoleda in jo povezali z odvisno spremenljivko, debelostjo, da bi videli, ali obstaja odnos. Glede na to, da je regresija grafični prikaz tega razmerja, čim manjša je spremenljivost podatkov, močnejša je povezava in bolj tesna prileganje regresijski črti.

Ključni odvzemi

• Linearna regresija modelira razmerje med odvisno in neodvisno spremenljivko.
• Regresijsko analizo je mogoče doseči, če so spremenljivke neodvisne, ni heteroscedastičnosti in izrazi napak spremenljivk niso povezani.
• Z orodjem Data Analysis ToolPak je lažje modelirati linearno regresijo v Excelu.

Pomembna vprašanja

Obstaja nekaj kritičnih predpostavk o vašem naboru podatkov, ki morajo biti resnične za nadaljevanje regresijske analize:

1. Spremenljivke morajo biti resnično neodvisne (s testom Chi-kvadrat).
2. Podatki ne smejo imeti različnih odstopanj napak (temu rečemo heteroskedastičnost (tudi črkovalna heteroscedastičnost)).
3. Pogoji napake vsake spremenljivke morajo biti neusklajeni. Če ni, pomeni, da so spremenljivke serijsko povezane.

Če se te tri stvari slišijo zapleteno, so. Učinek enega od teh pomislekov pa ni resničen. V bistvu bi napačno navedli odnos, ki ga merite.

Izdelava regresije v Excelu

Prvi korak pri izvajanju regresijske analize v Excelu je, da dvakrat preverite, ali je nameščen brezplačni Excel vtičnik Data Analysis ToolPak. Ta vtičnik zelo olajša izračun različnih statističnih podatkov. Ni potrebno načrtovati linearne regresijske črte, vendar ustvarjanje tabel s statistiko poenostavi. Če želite preveriti, ali je nameščen, v orodni vrstici izberite »Podatki«. Če je možnost »Analiza podatkov« možnost, je funkcija nameščena in pripravljena za uporabo. Če ni nameščen, lahko to možnost zahtevate s klikom na gumb Office in izbiro "Možnosti Excela".

Z uporabo orodja DataP ToolPak je ustvarjanje regresijskega izhoda le nekaj klikov.

Neodvisna spremenljivka je v območju X.

Glede na donos S&P 500 recimo, da želimo vedeti, ali lahko ocenimo moč in razmerje med donosnostmi delnic Visa (V). Podatki o vračilu zalog Visa (V) napolnijo stolpec 1 kot odvisno spremenljivko. S&P 500 vrne podatke napolni stolpec 2 kot neodvisno spremenljivko.

1. V orodni vrstici izberite "Podatki". Prikaže se meni "Podatki".
2. Izberite "Analiza podatkov". Prikaže se pogovorno okno Analiza podatkov - orodja za analizo.
3. V meniju izberite "Regression" in kliknite "OK".
4. V pogovornem oknu Regression kliknite polje "Vhodni razpon Y" in izberite odvisne spremenljive podatke (Visa (V) zaloge).
5. Kliknite polje "Vhodni obseg X" in izberite neodvisne podatke spremenljivke (S&P 500 vrne).
6. Za prikaz rezultatov kliknite »V redu«.

[Opomba: Če se miza zdi majhna, z desno miškino tipko kliknite sliko in odprite na novem zavihku za višjo ločljivost.]

Razložite rezultate

Z uporabo teh podatkov (enakih v članku R-kvadrata) dobimo naslednjo tabelo:

Vrednost R2, znana tudi kot koeficient določanja, meri delež variacije v odvisni spremenljivki, ki ga razloži neodvisna spremenljivka, ali kako regresijski model ustreza podatkom. Vrednost R2 se giblje od 0 do 1, višja vrednost pa kaže na boljše prileganje. P-vrednost ali verjetnostna vrednost se prav tako giblje od 0 do 1 in označuje, ali je test pomemben. V nasprotju z vrednostjo R2 je manjša p-vrednost ugodna, saj kaže na povezavo med odvisnimi in neodvisnimi spremenljivkami.

Grafikovanje regresije v Excelu

Regresijo v Excelu lahko narišemo tako, da podatke označimo in jih izrišemo kot raztreseno ploskev. Če želite dodati regresijsko vrstico, v meniju "Orodja za grafikone" izberite "Postavitev". V pogovornem oknu izberite "Trendline" in nato "Linear Trendline". Če želite dodati vrednost R ², v meniju Trendline izberite "More Trendline Options". Nazadnje izberite "Prikaži vrednost R-kvadrat v grafikonu". toliko podrobnosti kot zgornja tabela.