Excel regresná analýza (obsah)

  • Regresná analýza v Exceli
  • Matematické vysvetlenie regresie
  • Ako vykonať lineárnu regresiu v Exceli?
    • # 1 - Regresný nástroj využívajúci analytický nástroj v programe Excel
    • # 2 - Regresná analýza pomocou Scatterplot s Trendline v Exceli

Regresná analýza v Exceli

Lineárna regresia je štatistická technika, ktorá skúma lineárny vzťah medzi závislou premennou a jednou alebo viacerými nezávislými premennými.

  • Závislá premenná (premenná odozvy / výsledku): Je premenná vášho záujmu a ktorú ste chceli predpovedať na základe dostupných informácií o nezávislej premennej (premenných).
  • Nezávislá premenná (tiež vysvetľujúca / predikčná premenná): Je / sú premenné, od ktorých je závislá premenná odozvy. Čo znamená, že sú to premenné, pomocou ktorých možno predpovedať premennú odozvy.

Lineárny vzťah znamená, že zmena v nezávislej premennej (premenných) spôsobuje zmenu v závislej premennej.

V zásade existujú aj dva typy lineárnych vzťahov.

  1. Pozitívny lineárny vzťah: Keď sa nezávislá premenná zvyšuje, zvyšuje sa aj závislá premenná.
  2. Záporný lineárny vzťah: Keď sa nezávislá premenná zvyšuje, závislá premenná sa znižuje.

Boli to niektoré z nevyhnutných predpokladov skôr, ako skutočne pristúpite k vynikajúcej regresnej analýze.

Existujú dva základné spôsoby, ako vykonať excelentnú lineárnu regresiu pomocou:

  • Regresný nástroj prostredníctvom nástroja Analytické nástroje
  • Bodový graf s trendovou čiarou

V skutočnosti existuje ešte jedna metóda, ktorá používa na výpočet lineárnej regresie manuálne vzorce. Prečo by ste si to však mali zvoliť, keď pre vás vynikajú výpočty?

Z tohto dôvodu budeme hovoriť iba o dvoch vyššie diskutovaných metódach.

Predpokladajme, že máte údaje o výške a hmotnosti 10 jednotlivcov. Ak tieto informácie vynesiete do grafu, pozrime sa, čo poskytujú.

Ako ukazuje obrázok vyššie, lineárny vzťah možno nájsť v grafe Výška a hmotnosť. Teraz sa do grafu moc nezaujímajte, v druhej časti tohto článku ho vôbec prekopáme.

Matematické vysvetlenie regresie

Máme matematický výraz pre lineárnu regresiu, ako je uvedené nižšie:

Y = aX + b + ε

Kde,

  • Y je závislá premenná alebo premenná odozvy.
  • X je nezávislá premenná alebo prediktor.
  • a je sklon regresnej priamky. Čo znamená, že keď sa X zmení, nastane zmena Y pomocou jednotiek „a“.
  • b je odpočúvanie. Je to hodnota Y, keď je hodnota X nula.
  • ε je náhodný chybový termín. Vyskytuje sa, pretože predpokladaná hodnota Y sa nikdy nebude presne zhodovať so skutočnou hodnotou pre dané X. Tento chybový termín sa nemusíme obávať. Pretože existujú niektoré softvérové ​​programy, ktoré vypočítavajú tento chybový termín v backende za vás. Excel je jedným z týchto softvérov.

V takom prípade sa rovnica stáva,

Y = aX + b

Ktorý môže byť reprezentovaný ako:

Hmotnosť = a * Výška + b

Pokúsime sa zistiť hodnoty týchto aab pomocou metód, ktoré sme diskutovali vyššie.

Ako vykonať lineárnu regresiu v Exceli?

Ďalší článok vysvetľuje základy regresnej analýzy vo vynikajúcej podobe a ukazuje niekoľko rôznych spôsobov, ako urobiť lineárnu regresiu v Exceli.

Túto šablónu Excelu pre regresnú analýzu si môžete stiahnuť tu - šablónu Excelu pre regresnú analýzu

# 1 - Regresný nástroj využívajúci analytický nástroj v programe Excel

V našom príklade sa pokúsime prispôsobiť regresiu hodnôt hmotnosti (čo je závislá premenná) pomocou hodnôt výšky (čo je nezávislá premenná).

  • V excelovskej tabuľke kliknite na položku Analýza údajov (v položke Analytická skupina) v časti Údaje.

  • Vyhľadajte regresiu . Vyberte ju a stlačte tlačidlo ok.

  • Na otvorenom paneli Regresia použite nasledujúce vstupy.

  • Rozsah vstupu Y : Vyberte bunky, ktoré obsahujú závislú premennú (v tomto príklade B1: B11)

  • Rozsah vstupu X : Vyberte bunky, ktoré obsahujú vašu nezávislú premennú (v tomto príklade A1: A11).

  • Začiarknite políčko Menovky, ak vaše údaje obsahujú názvy stĺpcov (v tomto príklade máme názvy stĺpcov).

  • Úroveň spoľahlivosti je v predvolenom nastavení nastavená na 95%, ktorú je možné zmeniť podľa požiadaviek používateľov.

  • V časti Možnosti výstupu môžete upraviť, kde chcete vidieť výstup regresnej analýzy v Exceli. V tomto prípade chceme vidieť výstup na rovnakom hárku. Preto je uvedený rozsah zodpovedajúcim spôsobom.

  • Podľa možnosti Rezíduá máte voliteľné vstupy, ako sú Rezíduá, Reziduálne grafy, Štandardizované rezíduá, Grafy prispôsobenia čiar, ktoré si môžete vybrať podľa svojich potrieb. V takom prípade začiarknite políčko Zvyšky, aby sme videli rozptyl medzi predpokladanými a skutočnými hodnotami.

  • V časti Normálna pravdepodobnosť môžete vybrať Normálne pravdepodobnostné grafy, ktoré vám pomôžu skontrolovať normálnosť prediktorov. Kliknite na OK .

  • Excel pre vás vypočíta regresnú analýzu za zlomok sekúnd.

Doteraz to bolo ľahké a nie také logické. Interpretácia tohto výstupu a urobiť z neho cenné informácie je však zložitá úloha.

Jednou z dôležitých súčastí celého výstupu je R Square / Adjusted R Square pod tabuľkou SUMMARY OUTPUT. Čo poskytuje informácie o tom, ako dobrý je náš model. V tomto prípade je hodnota R štvorca 0, 9547. Čo interpretuje, že model má presnosť 95, 47% (správna priľnavosť). Alebo v inom jazyku je informácia o premennej Y vysvetlená premennou X na 95, 47%.

Druhou dôležitou súčasťou celého výstupu je tabuľka koeficientov. Uvádza hodnoty koeficientov, ktoré sa môžu použiť na zostavenie modelu pre budúce predpovede.

Teraz sa naša regresná rovnica pre predpoveď stáva:

Hmotnosť = 0, 6746 * výška - 38, 45508 (hodnota sklonu pre výšku je 0, 6746 … a odpočúvanie je -38, 45508…)

Dostali ste to, čo ste definovali? Definovali ste funkciu, do ktorej musíte teraz vložiť hodnotu Výška a dostanete hodnotu Hmotnosť.

# 2 - Regresná analýza pomocou Scatterplot s Trendline v Exceli

Teraz uvidíme, ako v exceli dokážeme umiestniť regresnú rovnicu na samotný rozptyl.

  • Vyberte celé dve stĺpcové údaje (vrátane hlavičiek).
  • Kliknite na Vložiť a vyberte bodový graf v časti grafov, ako je to znázornené na obrázku nižšie.

  • Pozrite si výstupný graf.

  • Teraz musíme mať na tomto grafe regresnú priamku s najmenšími štvorcami. Ak chcete pridať tento riadok, kliknite pravým tlačidlom myši na ktorýkoľvek z údajových bodov v grafe a vyberte možnosť Pridať trendovú čiaru.

  • To vám umožní mať trendovú líniu najmenšieho štvorca regresie, ako je uvedené nižšie.

  • V časti Formát trendu začiarknite políčko Zobraziť rovnicu na grafe.

  • To vám umožní vidieť rovnicu najmenšej štvorcovej regresnej priamky v grafe.

Toto je rovnica, pomocou ktorej môžeme predpovedať hodnoty hmotnosti pre každú danú množinu hodnôt výšky.

Pamätajte na regresnú analýzu v Exceli

  • Rozloženie trendovej čiary môžete zmeniť v časti Formát trendovej čiary v bodovom grafe.
  • Počas regresnej analýzy pomocou nástroja na analýzu údajov v programe Excel sa vždy odporúča pozrieť sa na zvyškové grafy. To vám umožní lepšie porozumieť šíreniu skutočných hodnôt Y a odhadovaných hodnôt X.
  • Jednoduchá lineárna regresia v exceli nevyžaduje kontrolu ANOVA a upravený štvorec R. Tieto vlastnosti je možné vziať do úvahy pri viacnásobnej lineárnej regresii. Čo je nad rámec tohto článku.

Odporúčané články

Toto bol návod na analýzu regresie v Exceli. Tu diskutujeme o tom, ako urobiť regresnú analýzu v Exceli spolu s vynikajúcimi príkladmi a stiahnuteľnou vynikajúcou šablónou. Môžete si tiež prečítať naše ďalšie navrhované články -

  1. Excel nástroj pre analýzu dát
  2. Vypočítajte ANOVA v Exceli
  3. Ako nájsť kĺzavé priemery Excelu
  4. Príklady testov Z v Exceli

Kategórie: