V tomto článku si ukážeme, jakými způsoby je možné v Excelu řešit lineární regresi.

Co to vlastně je lineární regrese?

Lineární regrese je vztah dvou proměnných (v našem dvou sloupců dat v jedné tabulce) regrese, kdy y (závislá proměnná) závisí na x (závislé proměnné) vztahem který se dá popsat rovnicí y = a*x + b. Jinými slovy pokud si závislost nakreslíme, vidíme přímku. Pokud vás zajímá regrese nelineární, tedy ta, která se popisuje jinou křivkou jenž je přímka, přejděte sem (funkce) nebo sem (řešitel).

Příklad

Potřebuji posoudit závislost dvou řad hodnot, přičemž předpokládám, že jedna závisí na druhé a tuším, která na které.

V mém případě řeším závislost počtu prodaných kopečků zmrzliny v určitý den na průměrné teplotě toho dne. Chci zjistit, jaká je závislost, a také na základě historických dat odhadnout, kolik zmrzliny prodám další den, kdy má být např. 17°C.

Pro zjednodušení budu předpokládat, že prodej zmrzliny nezávisí na ničem jiném než na teplotě.

Toto jsou data, která mám k dispozici:

regresni_analyza_puvodni_data

Návod

Pokud je regrese lineární (a já teď budu předpokládat, že je), tak je určena rovnicí: y = a * x + b. Neboli prodej zmrzliny = a * teplota + b.

X je nezávislá proměnná - jinými slovy proměnná, na která závisí ta druhá. V mém případě je nezávislou proměnnou teplota - protože prodej zmrzliny závisí na teplotě, ne naopak. Ještě jinými slovy je nezávislou proměnnou ta, která se kresli na ose x - to je ta vodorovná 🙂

Y je závislá proměnná - jinými slovy ta, jejíž hodnoty závisí na nezávislé proměnné. V mém případě je to prodej zmrzliny, protože ten závisí na teplotě. Ještě jinými slovy je závislou proměnnou taa, která se kreslí na ose y - to je ta nahoru 🙂

Jedním ze smyslů regresní analýzy je určit koeficienty "a" a "b". Máme minimálně čtyři způsoby, jak je zjistit - přičemž výsledné koeficienty jsou samozřejmě vždy stejné.

Výpočet pomocí funkcí Intercept a Slope, případně Forecast

Tento postup je už jednou popsaný zde.

Maticový vzorec LINREGRESE

Funkce LINREGRESE získá koeficienty podobně. Jde ale o maticový vzorec, proto musím pracovat trochu jinak. Označím dvě buňky vedle sebe. Do řádku vzorců napíšu =LINREGRESE(C2:C14;B2:B14) Stisknu Ctrl + Shift + Enter Tím se mi vzorec rozkopíruje do obou značených buněk. V jedné z nich je koeficient a, ve druhé koeficient b.

Graf

Pokud stejně jako já chápete věci lépe když jsou graficky znázorněné, můžete použít následující způsob. Označíte číselné řady hodnot i se záhlavími a vložíte graf typu XY.

 V grafu už je většinou vidět, jestli nějaká závislost existuje - v případě, že "tečky" dávají dohromady "čáru" jako v mém případě.           regresni_analyza_hotovy_graf

Kliknu na jednu z těch teček pravým tlačítkem a pak levým na "Přidat spojnici trendu".

regresni_analyza_pridani_spojnice_grafu

regresni_analyza_formular_zaskrtnuti_zobrazeni_rovnice_regrese Kliknu na Zavřít.

regresni_analyza_hotova_primka_v_grafu

Do grafu už se mi promítla přímka, která znázorňuje závislost. A u ní se zobrazila rovnice, kterou jsem hledal. Už vím, že a = 8,9707 a b = 14,166. Tedy slovy když vynásobím teplotu zhruba devíti a přičtu zhruba 14, dostanu odhadovanou spotřebu zmrzliny.

Analytické nástroje

Pokud chci dostat kromě koeficientů rovnice ještě další údaje, použiji analytické nástroje.

Nejprve je zprovozním. To je popsáno tady

Na kartě Data pak v Analytických nástrojích vyberu Regrese. Do hodnot Y zadám čísla týkající se zmrzliny. Do hodnot X zadám čísla týkající se teploty.

regrese_formular

Výstupem je spousta hodnot. Pokud do diskuse pod tímto článkem napíšete, jak je věcně interpretovat, budu rád. Mně teď ale zajímají zase jen koeficienty rovnice. Vidím, že jsou stejné jako v předchozím případě.

Výsledek

Ať postupuji jakoukoliv cestou, vždy dojdu ke stejným hodnotám a a b. Proto pokud si myslím, že zítra bude 17 stupňů, objednám 166,6675 kopečků zmrzliny - což je 17 * 8,9707 + 14,166. A budu doufat, že regrese funguje 🙂 Zdrojová data pro zkoušení: vysledek_linearni_regrese  

3 Komentářů

  1. Když u regrese zadám vstupní a výstupní oblast hodnoty z obou sloupců bez názvu sloupce výsledky v regresní tabulce jsou jiné než když označím i název sloupce (mění se o jedničku rezidua). Co je prosím správně?

  2. Dobrý den,

    děkuji moc za návod, nádherně vysvětlené, pochopil jsem to i já …. moc mě to pomohla děkuji ještě jednou

Napsat komentář

Vaše emailová adresa nebude publikována.

*

smazat formulářOdeslat komentář