V tomto článku si ukážeme, jakými způsoby je možné v Excelu počítat lineární regresi. Pokud vás zajímá regrese nelineární, přejděte na tento článek.
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ě mám závislost prodeje zmrzliny v určitý den na průměrné teplotě toho dne. Chci zjistit, jaká je závislost, a také odhadnout, kolik zmrzliny prodám další den, kdy má být 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:
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 to teplota - protože prodej zmrzliny závisí na teplotě, ne naopak. Ještě jinými slovy je to to, co 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 to to, co se kreslí na ose y - to je ta nahoru :)
Smyslem regresní analýzy je určit koeficienty "a" a "b".
Mám čtyři způsoby, jak to zjistit - přičemž výsledné koeficienty jsou samozřejmě vždy stejné.
1. Výpočet pomocí funkcí Intercept a Slope, případně Forecast
Tento postup je na blogu už jednou popsaný zde:http://www.excelentnitriky.com/2012/03/linearni-regrese-v-excelu.html
2. 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.
3. 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ě.
Kliknu na jednu z těch teček pravým tlačítkem a pak levým na "Přidat spojnici trendu".
Kliknu na Zavřít.
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. Jinými slovy když vynásobím teplotu zhruba devíti a přičtu zhruba 14, dostanu odhadovanou spotřebu zmrzliny.
4. 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.
Výstupem je spousta hodnot.
Pokud do diskuse pod tímto článkem napíšete, jak je věcně interpretovat, budu rád.
Mně 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 :)
Žádné komentáře:
Okomentovat