Vytvorenie modelu lineárnej regresie v Exceli

Lineárna regresia je dátový graf, ktorý graficky znázorňuje lineárny vzťah medzi nezávislou a závislou premennou. Zvyčajne sa používa na vizuálne znázornenie sily vzťahu a rozptylu výsledkov - to všetko za účelom vysvetlenia správania závislej premennej.

Povedzme, že sme chceli otestovať silu vzťahu medzi množstvom konzumovanej zmrzliny a obezitou. Zoberieme nezávislú premennú, množstvo zmrzliny a spojíme ju so závislou premennou, obezitou, aby sme zistili, či existuje vzťah. Vzhľadom na regresiu je grafické znázornenie tohto vzťahu, čím je menšia variabilita údajov, tým je vzťah silnejší a čím tesnejšie sa prispôsobí regresnej priamke.

Kľúčové jedlá

• Lineárna regresia modeluje vzťah medzi závislými a nezávislými premennými.
• Regresnú analýzu možno dosiahnuť, ak sú premenné nezávislé, nedochádza k heteroscedasticite a chybové pomery premenných nie sú korelované.
• Modelovanie lineárnej regresie v Exceli je ľahšie pomocou nástroja Data Analysis ToolPak.

Dôležité úvahy

Existuje niekoľko kritických predpokladov týkajúcich sa vášho súboru údajov, ktoré musia byť pravdivé, aby ste mohli pokračovať v regresnej analýze:

1. Premenné musia byť skutočne nezávislé (pomocou testu chí-kvadrát).
2. Údaje nesmú mať rôzne odchýlky chýb (nazýva sa to heteroskedasticita (tiež hláskovaná heteroscedasticita)).
3. Chybové termíny každej premennej musia byť nekorelované. Ak nie, znamená to, že premenné sú sériovo korelované.

Ak tieto tri veci znejú komplikovane, sú. Účinok jednej z týchto úvah na pravdivosť je však skreslený odhad. V podstate by ste skreslili vzťah, ktorý meriate.

Výstup regresie v Exceli

Prvým krokom pri spustení regresnej analýzy v programe Excel je opätovné skontrolovanie, či je nainštalovaný bezplatný program Excel Data Data ToolPak pre program Excel. Tento doplnok uľahčuje výpočet rozsahu štatistík. Nie je potrebné mapovať lineárnu regresnú čiaru, ale zjednodušuje vytváranie štatistických tabuliek. Ak chcete overiť, či je nainštalovaný, vyberte na paneli nástrojov položku „Údaje“. Ak je možnosť „Analýza údajov“, táto funkcia je nainštalovaná a pripravená na použitie. Ak nie je nainštalovaná, môžete požiadať o túto možnosť kliknutím na tlačidlo Office a výberom možnosti „Možnosti programu Excel“.

Pomocou nástroja Data Analysis ToolPak je vytvorenie regresného výstupu iba pár kliknutí.

Nezávislá premenná spadá do rozsahu X.

Vzhľadom na výnosy S&P 500 povedzme, že chceme vedieť, či dokážeme odhadnúť silu a vzťah výnosov z akcií Visa (V). Zásoby Visa (V) vracajú údaje naplnia stĺpec 1 ako závislú premennú. S&P 500 vráti údaje, ktoré vyplnia stĺpec 2 ako nezávislú premennú.

1. Na paneli s nástrojmi vyberte možnosť „Údaje“. Zobrazí sa menu „Data“.
2. Vyberte položku „Analýza údajov“. Zobrazí sa dialógové okno Analýza údajov - nástroje na analýzu.
3. Z ponuky vyberte „Regresia“ a kliknite na „OK“.
4. V dialógovom okne Regresia kliknite na políčko „Vstupný rozsah Y“ a vyberte závislé údaje premenných (návratnosť zásob Visa (V)).
5. Kliknite na políčko „Rozsah vstupu X“ a vyberte nezávislé údaje premennej (návratnosť S&P 500).
6. Výsledky spustíte kliknutím na tlačidlo „OK“.

[Poznámka: Ak sa tabuľka javí ako malá, kliknite pravým tlačidlom myši na obrázok a otvorte na novej karte s vyšším rozlíšením.]

Interpretujte výsledky

Použitím týchto údajov (rovnakých ako v našom článku na druhej strane) dostaneme nasledujúcu tabuľku:

Hodnota R2, známa tiež ako koeficient stanovenia, meria podiel variácie závislej premennej vysvetlenej nezávislou premennou alebo ako dobre regresný model vyhovuje údajom. Hodnota R2 je v rozsahu od 0 do 1 a vyššia hodnota znamená lepšie prispôsobenie. Hodnota p alebo pravdepodobnostná hodnota sa tiež pohybujú od 0 do 1 a označujú, či je test významný. Na rozdiel od hodnoty R2 je menšia hodnota p priaznivá, pretože naznačuje koreláciu medzi závislými a nezávislými premennými.

Mapovanie regresie v Exceli

V Exceli môžeme zmapovať regresiu zvýraznením údajov a ich zmapovaním ako bodový graf. Ak chcete pridať čiaru regresie, vyberte položku Rozloženie v ponuke Nástroje grafu. V dialógovom okne vyberte „Trendline“ a potom „Lineárne Trendline“. Ak chcete pridať hodnotu R2, vyberte v ponuke „Trendline“ možnosť „Ďalšie možnosti trendu“. Nakoniec vyberte možnosť „Zobraziť hodnotu štvorcovej hodnoty R v grafe“. Vizuálny výsledok sumarizuje silu vzťahu, aj keď na úkor neposkytnutia toľko podrobností ako tabuľka vyššie.