Lineární regrese v Excelu

V tomto článku si ukážeme, jakými způsoby je možné v Excelu řešit lineární regresi. Pro článek o lineární regresi v R-ku klikněte sem.

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), kdy y (závislá proměnná) závisí na x (nezávislé proměnné) vztahem, který se dá popsat rovnicí y = a*x + b.

Také se dá říci, že 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 (nástroj Řešitel).

Příklad

Potřebuji posoudit závislost dvou řad hodno. Očekává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 z minulosti, která mám k dispozici:

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

Počet prodaných kopečků za den = a * Teplota v tento den + 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 ta, 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 bodový graf (v jiných verzích raf 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ě. 

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

 

K vložené spojnici můžeme ještě zobrazit rovnici, která určuje tvar přímky. Tím zjistíme, že a = 8,9707 a b = 14,166. Tedy slovy když vynásobíme teplotu zhruba devíti a přičteme zhruba 14, dostaneme odhadovanou spotřebu zmrzliny.

Analytické nástroje

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

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

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

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ť postupujeme jakoukoliv cestou, vždy dojdeme ke stejným hodnotám a a b. Proto pokud si myslíme, že zítra bude 17 stupňů, objednáme 166,6675 kopečků zmrzliny – což je 17 * 8,9707 + 14,166. A budeme doufat, že regrese funguje – pokud ano, měli bychom se trefit do spotřeby.

Zdrojová data pro zkoušení: vysledek_linearni_regrese  

Lineární regrese v krátkém videu