Lineární regrese v Excelu

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 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ě 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:

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 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é.

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  

2 comments on “Lineární regrese v Excelu
  1. Terulinaaa@seznam.cz napsal:

    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. Jakub napsal:

    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 zveřejněna.

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář
TOPlist