Regresia v Exceli je spôsob, ako automatizovať štatistický proces porovnávania niekoľkých súborov informácií, aby ste videli, ako zmeny v nezávislých premenných ovplyvňujú zmeny v závislých premenných. Ak ste niekedy chceli nájsť koreláciu medzi dvoma vecami, použitie regresnej analýzy v Exceli je jedným z najlepších spôsobov, ako to urobiť.
Pokyny v tomto článku platia pre Excel 2019, Excel 2016, Excel 2013, Excel 2010.
Aký je význam regresie?
Regresia je prístup štatistického modelovania, ktorý analytici používajú na určenie vzťahov medzi viacerými premennými.
Regresná analýza začína jednou premennou, ktorú sa pokúšate analyzovať, a nezávislými premennými, ktoré testujete, aby ste zistili, či túto jedinú premennú ovplyvňujú. Analýza sa zameriava na zmeny v nezávislých premenných a pokúša sa korelovať tieto zmeny s výslednými zmenami v jedinej (závislej) premennej.
Môže to znieť ako pokročilá štatistika, ale Excel túto komplexnú analýzu sprístupňuje každému.
Vykonávanie lineárnej regresie v Exceli
Najjednoduchšou formou regresnej analýzy je lineárna regresia. Jednoduchá lineárna regresia skúma vzťah iba medzi dvoma premennými.
Napríklad nasledujúca tabuľka zobrazuje údaje obsahujúce počet kalórií, ktoré človek zjedol každý deň, a ich hmotnosť v daný deň.
Keďže táto tabuľka obsahuje dva stĺpce údajov a jedna premenná môže potenciálne ovplyvňovať druhú, môžete na tieto údaje spustiť regresnú analýzu pomocou Excelu.
Povolenie doplnku analytických nástrojov
Skôr ako budete môcť použiť funkciu regresnej analýzy Excelu, musíte povoliť doplnok Analysis ToolPak na obrazovke Možnosti programu Excel.
-
V Exceli vyberte ponuku File a zvoľte Options.
Image -
V ľavej navigačnej ponuke vyberte Doplnky. Potom sa uistite, že Doplnky Excelu je vybraté v poli Manage.
Image -
Nakoniec vyberte tlačidlo Go.
Image -
Vo vyskakovacom okne doplnkov. Povoľte Analysis ToolPack kliknutím na políčko pred ním, čím pridáte začiarknutie a vyberiete OK.
Image
Teraz, keď je aktivovaný nástroj Analysis ToolPak, ste pripravení začať robiť regresnú analýzu v Exceli.
Ako vykonať jednoduchú lineárnu regresiu v Exceli
Pomocou tabuľky hmotnosti a kalórií ako príkladu môžete vykonať lineárnu regresnú analýzu v Exceli takto.
-
Vyberte ponuku Data. Potom v skupine Analysis vyberte Analýza údajov.
Image -
V okne Analýza údajov vyberte zo zoznamu Regression a kliknite na OK.
Image -
Vstupný rozsah Y je rozsah buniek, ktorý obsahuje závislú premennú. V tomto príklade je to hmotnosť. Vstup X Rozsah je rozsah buniek, ktorý obsahuje nezávislú premennú. V tomto príklade je to stĺpec kalórií.
Image -
Vyberte Labels pre bunky hlavičky a potom vyberte Nový pracovný hárok na odoslanie výsledkov do nového pracovného hárka. Vyberte OK, aby Excel spustil analýzu a odoslal výsledky do nového hárka.
Image -
Preskúmajte nový pracovný hárok. Výstup analýzy má množstvo hodnôt, ktorým musíte porozumieť, aby ste mohli interpretovať výsledky.
Image Každé z týchto čísel má nasledujúci význam:
- Viacnásobné R: Korelačný koeficient. 1 znamená silnú koreláciu medzi týmito dvoma premennými, zatiaľ čo -1 znamená, že existuje silný negatívny vzťah. 0 znamená, že neexistuje žiadna korelácia.
- R Square: Koeficient determinácie, ktorý ukazuje, koľko bodov medzi týmito dvoma premennými pripadá na regresnú priamku. Štatisticky je to súčet štvorcových odchýlok od priemeru.
- Adjusted R Square: Štatistická hodnota nazývaná R square, ktorá je upravená pre počet nezávislých premenných, ktoré ste si vybrali.
- Štandardná chyba: Ako presné sú výsledky regresnej analýzy. Ak je táto chyba malá, potom sú vaše výsledky regresie presnejšie.
- Pozorovania: Počet pozorovaní vo vašom regresnom modeli.
Zostávajúce hodnoty vo výstupe regresie vám poskytnú podrobnosti o menších komponentoch v regresnej analýze.
- df: Štatistická hodnota známa ako stupne voľnosti súvisiace so zdrojmi rozptylu.
- SS: Súčet štvorcov. Pomer zvyškového súčtu štvorcov k celkovej SS by mal byť menší, ak väčšina vašich údajov zodpovedá regresnej čiare.
- MS: Stredná štvorec regresných údajov.
- F: F štatistika (F-test) pre nulovú hypotézu. To poskytuje význam regresného modelu.
- Významnosť F: Štatistická hodnota známa ako P-hodnota F.
Pokiaľ nerozumiete štatistikám a výpočtovým regresným modelom, hodnoty v spodnej časti súhrnu nebudú mať veľký význam. Najdôležitejšie sú však viaceré štvorce R a R.
Ako môžete vidieť, v tomto príklade majú kalórie silnú koreláciu s celkovou hmotnosťou.
Viacnásobná lineárna regresná analýza v Exceli
Ak chcete vykonať rovnakú lineárnu regresiu, ale s viacerými nezávislými premennými, vyberte celý rozsah (viacero stĺpcov a riadkov) pre Vstup X Rozsah.
Pri výbere viacerých nezávislých premenných je menej pravdepodobné, že nájdete tak silnú koreláciu, pretože existuje veľa premenných.
Regresná analýza v Exceli vám však môže pomôcť nájsť korelácie s jednou alebo viacerými premennými, o ktorých si možno ani neuvedomujete, že existujú, a to iba manuálnym skontrolovaním údajov.
