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.
- Pozitívny lineárny vzťah: Keď sa nezávislá premenná zvyšuje, zvyšuje sa aj závislá premenná.
- 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 -
- Excel nástroj pre analýzu dát
- Vypočítajte ANOVA v Exceli
- Ako nájsť kĺzavé priemery Excelu
- Príklady testov Z v Exceli