Lineárna regresia v Exceli (obsah)

  • Úvod do lineárnej regresie v Exceli
  • Metódy použitia lineárnej regresie v Exceli

Úvod do lineárnej regresie v Exceli

Lineárna regresia je štatistická technika / metóda používaná na štúdium vzťahu medzi dvoma kontinuálnymi kvantitatívnymi premennými. V tejto technike sa na predpovedanie hodnoty závislej premennej používajú nezávislé premenné. Ak existuje iba jedna nezávislá premenná, potom ide o jednoduchú lineárnu regresiu, a ak je počet nezávislých premenných viac ako jedna, potom ide o viacnásobnú lineárnu regresiu. Modely lineárnej regresie majú vzťah medzi závislými a nezávislými premennými prispôsobením lineárnej rovnice pozorovaným údajom. Lineárne odkazuje na skutočnosť, že na prispôsobenie našich údajov používame čiaru. Závislé premenné použité v regresnej analýze sa tiež nazývajú odozva alebo predpovedané premenné a nezávislé premenné sa nazývajú aj vysvetľujúce premenné alebo prediktory.

Lineárna regresná priamka má rovnicu druhu: Y = a + bX;

Kde:

  • X je vysvetľujúca premenná,
  • Y je závislá premenná,
  • b je sklon čiary,
  • a je priesečník y (tj hodnota y, keď x = 0).

Metóda najmenších štvorcov sa všeobecne používa v lineárnej regresii, ktorá vypočíta najlepšiu líniu pre pozorované údaje minimalizovaním súčtu štvorcov odchýlky dátových bodov od čiary.

Metódy použitia lineárnej regresie v Exceli

Tento príklad vás naučí metódy vykonávania analýzy lineárnej regresie v Exceli. Pozrime sa na niekoľko metód.

Túto šablónu lineárnej regresie Excel si môžete stiahnuť tu - šablónu lineárnej regresie Excel

Metóda č. 1 - Bodový graf s trendovou čiarou

Povedzme, že máme súbor údajov o niektorých jednotlivcoch s ich vekom, indexom biomasy (BMI) a sumou, ktorú mesačne vynaložili na lekárske výdavky. Teraz s pohľadom na charakteristiky jednotlivcov, ako je vek a BMI, chceme zistiť, ako tieto premenné ovplyvňujú náklady na zdravotnú starostlivosť, a preto ich používajú na uskutočnenie regresie a odhad / predpovedanie priemerných liečebných nákladov pre niektorých konkrétnych jednotlivcov. Pozrime sa najprv, ako vek ovplyvňuje lekárske výdavky iba vek. Pozrime sa na súbor údajov:

Suma na lekárske výdavky = b * vek + a

  • Vyberte dva stĺpce množiny údajov (xay) vrátane záhlaví.

  • Kliknite na „Vložiť“ a rozbaľte rozbaľovaciu ponuku „Bodový graf“ a vyberte miniatúru „Bodový“ (prvá)

  • Teraz sa objaví rozptýlený pozemok a na to by sme nakreslili regresnú čiaru. Kliknite pravým tlačidlom myši na ľubovoľný dátový bod a vyberte príkaz „Pridať trend“

  • Teraz v paneli „Formát trendovej línie“ napravo vyberte položky „Lineárny trend“ a „Zobraziť rovnicu na mape“.

  • Vyberte 'Zobraziť rovnicu na grafe'.

Graf dokážeme improvizovať podľa našich požiadaviek, ako napríklad pridávanie názvov osí, zmena mierky, farby a typu čiar.

Po zlepšení grafu je to výstup, ktorý dostaneme.

Poznámka: V tomto type regresného grafu by závislá premenná mala byť vždy na osi y a nezávislá na osi x. Ak je graf vykreslený v opačnom poradí, potom buď prepnite osi v grafe, alebo vymieňajte stĺpce v množine údajov.

Metóda č. 2 - Metóda doplnku Analysis ToolPak

Analytický nástroj nie je v predvolenom nastavení povolený a musíme ho vykonať ručne. Urobiť tak:

  • Kliknite na ponuku „Súbor“.

Potom kliknite na 'Možnosti'.

  • V poli „Spravovať“ vyberte položku Doplnky programu Excel a kliknite na tlačidlo Prejsť.

  • Vyberte položku Analytický nástroj -> „OK“

Na kartu „Údaje“ sa tým pridajú nástroje „Analýza údajov“. Teraz spustíme regresnú analýzu:

  • Kliknite na kartu „Analýza údajov“ na karte „Údaje“

  • Vyberte „Regresia“ -> „OK“

  • Zobrazí sa dialógové okno regresie. Vyberte rozsah Vstup Y a rozsah X (zdravotné náklady a vek). V prípade viacnásobnej lineárnej regresie si môžeme vybrať viac stĺpcov nezávislých premenných (napríklad, ak chceme vidieť vplyv BMI aj na lekárske výdavky).
  • Ak chcete zahrnúť hlavičky, začiarknite políčko „Štítky“.
  • Vyberte požadovanú možnosť výstupu.
  • Začiarknite políčko „zvyšky“ a kliknite na „OK“.

Teraz bude výstup našej regresnej analýzy vytvorený v novom pracovnom hárku s uvedením regresnej štatistiky, ANOVA, zvyškov a koeficientov.

Interpretácia výstupu:

  • Štatistika regresie hovorí, ako dobre regresná rovnica zodpovedá údajom:

  • Násobok R je korelačný koeficient, ktorý meria silu lineárneho vzťahu medzi dvoma premennými. Leží medzi -1 a 1 a jej absolútna hodnota zobrazuje silu vzťahu s veľkou hodnotou, ktorá indikuje silnejší vzťah, nízka hodnota znamená negatívnu a nulovú hodnotu, ktorá označuje žiadny vzťah.
  • Štvorec R je koeficient určenia, ktorý sa používa ako indikátor dobrej zhody. Je medzi 0 a 1, s hodnotou blízkou 1, čo naznačuje, že model je vhodný. V tomto prípade sa 0, 57 = 57% y-hodnôt vysvetľuje x-hodnotami.
  • Nastavený štvorec R je obdĺžnik R upravený pre počet prediktorov v prípade viacnásobnej lineárnej regresie.
  • Štandardná chyba zobrazuje presnosť regresnej analýzy.
  • Pozorovania znázorňujú počet modelových pozorovaní.
  • Anova hovorí o úrovni variability v regresnom modeli.

To sa všeobecne nepoužíva pre jednoduchú lineárnu regresiu. „Hodnoty významnosti F“ však naznačujú spoľahlivosť našich výsledkov, pričom hodnota väčšia ako 0, 05 naznačuje voľbu iného prediktora.

  • Koeficienty sú najdôležitejšou časťou použitou na zostavenie regresnej rovnice.

Naša regresná rovnica by teda bola: y = 16, 891 x - 355, 32. Je to rovnaké ako pri metóde 1 (bodový graf s trendovou čiarou).

Ak teraz chceme predpovedať priemerné náklady na zdravotnú starostlivosť vo veku 72 rokov:

Takže y = 16, 891 * 72 -355, 32 = 860, 832

Týmto spôsobom môžeme predpovedať hodnoty y pre akékoľvek ďalšie hodnoty x.

  • Zvyšok ukazuje rozdiel medzi skutočnými a predpokladanými hodnotami.

Posledná metóda regresie nie je tak často používaná a vyžaduje štatistické funkcie ako sklon (), priesečník (), korel () atď. Na vykonanie regresnej analýzy.

Dôležité informácie o lineárnej regresii v programe Excel

  • Regresná analýza sa zvyčajne používa na zistenie, či existuje štatisticky významný vzťah medzi dvoma súbormi premenných.
  • Používa sa na predpovedanie hodnoty závislej premennej na základe hodnôt jednej alebo viacerých nezávislých premenných.
  • Vždy, keď si prajeme prispôsobiť lineárny regresný model skupine údajov, potom by sa mal rozsah údajov starostlivo sledovať, ako keby sme pomocou regresnej rovnice predpovedali akúkoľvek hodnotu mimo tohto rozsahu (extrapolácia), potom to môže viesť k nesprávnym výsledkom.

Odporúčané články

Toto je príručka pre lineárnu regresiu v Exceli. Tu diskutujeme o tom, ako robiť lineárnu regresiu v Exceli spolu s praktickými príkladmi a stiahnuteľnou šablónou Excel. Môžete si tiež prečítať naše ďalšie navrhované články -

  1. Ako pripraviť mzdu v Exceli?
  2. Použitie MAX vzorca v Exceli
  3. Výukové programy o bunkových referenciách v Exceli
  4. Vytvorenie regresnej analýzy v Exceli
  5. Lineárne programovanie v Exceli

Kategórie:

Tipy programu Excel