Lineárna regresia v Exceli

V tomto článku si ukážeme, akými spôsobmi je možné v Exceli počítať lineárnu regresiu. Ak vás zaujíma regresia nelineárna, prejdite sem (funkce) nebo sem (řešitel).

Príklad

Potrebujem posúdiť závislosť dvoch riadok hodnôt, pričom predpokladám, že jedna závisí na druhej a tuším, ktorá na ktorej.

V mojom prípade mám závislosť predaja zmrzliny v určitý deň na priemernej teplote toho dňa. Chcem zistiť, aká je závislosť, a tiež odhadnúť, koľko zmrzliny predám ďalší deň, keď má byť 17°C. Pre zjednodušenie budem predpokladať, že predaj zmrzliny nezávisí na ničom inom než na teplote.

Toto sú dáta, ktoré mám k dispozícii:

Návod

Ak je regresia lineárna (a ja teraz budem predpokladať, že je), tak je určená rovnicou: y = a * x + b čiže predaj zmrzliny = a * teplota + b.

X je nezávislá premenná – inými slovami premenná, na ktorej závisí tá druhá. V mojom prípade je to teplota – pretože predaj zmrzliny závisí na teplote, nie opačne. Ešte inými slovami je to to, čo sa kreslí na ose x – to je tá vodorovná 🙂

Y je závislá premenná – inými slovami tá, ktorej hodnoty závisia na nezávislej premennej. V mojom prípade je to predaj zmrzliny, pretože ten závisí na teplote. Ešte inak povedané to je to, čo sa kreslí na ose y – to je tá nahor 🙂

Zmyslom regresnej analýzy je určiť koeficienty “a” a “b”. Mám štyri spôsoby, ako to zistiť – pričom výsledné koeficienty sú samozrejme vždy rovnaké.

Výpočet pomocou funkcií Intercept a Slope, prípadne Forecast

Tento postup je už raz popísaný tu.

Maticový vzorec LINREGRESE

Funkcia LINREGRESE získa koeficienty podobne. Ide ale o maticový vzorec, preto musím pracovať trochu inak.Označím dve bunky vedľa seba. Do riadka vzorcov napíšem =LINREGRESE(C2:C14;B2:B14) Stlačím Ctrl + Shift + Enter Tým sa mi vzorec skopíruje do oboch značených buniek. V jednej z nich je koeficient a, v druhej koeficient b.

Graf

Ak rovnako ako ja chápete veci lepšie, keď sú graficky znázornené, môžete použiť nasledujúci spôsob. Označíte číselnú radu hodnôt aj so záhlaviami a vložíte graf typu XY (závislosť).

V grafe už je väčšinou vidieť, či nejaká závislosť existuje – v prípade, že “bodky” dávajú dohromady “čiaru” ako v mojom prípade.

Kliknem na jednu z tých bodiek pravým tlačidlom a potom ľavým na “Pridať trendovú spojnicu…”. Tiež vyberiem správny trend (například “Lineárna) a pridám rovnicu.

 A to je všetko.

Do grafu už sa mi premietla priamka, ktorá znázorňuje závislosť. A u nej sa zobrazila rovnica, ktorú som hľadal. Už viem, že a = 8,9707 a b = 14,166. Teda keď vynásobím teplotu zhruba deviatich a pripočítam zhruba 14, dostanem odhadovanú spotrebu zmrzliny.

Analytické nástroje

Ak chcem získať okrem koeficientov rovnice ešte ďalšie údaje, použijem analytické nástroje.

Najskôr ich sprevádzkujem. To je popísané tu.. 

Na karte Údaje potom v Data Analysis vyberiem Regression.

Do hodnôt Y zadám čísla týkajúce sa zmrzliny. Do hodnôt X zadám čísla týkajúce sa teploty.

Výstupom je množstvo hodnôt. Ak do diskusie pod týmto článkom napíšete, ako ich vecne interpretovať, budem rád. Mňa teraz ale zaujímajú opäť len koeficienty rovnice. Vidím, že sú rovnaké ako v predošlom prípade.

Výsledek

Nech postupujem akoukoľvek cestou, vždy dôjdem k rovnakým hodnotám a a b. Preto pokiaľ si myslím, že zajtra bude 17 stupňov, objednám 166,6675 kopčekov zmrzliny – čo je 17 * 8,9707 + 14,166. A budem dúfať, že regresia funguje 🙂 Zdrojové dáta pre skúšanie:výsledok lineárnej regresie