Nelineární regrese v Excelu s využitím řešitele

V Excelu existuje řada způsobů, jak pracovat s lineární regresí - věnuje se jim tento článek. Lineární regrese je vztah, kdy závislost dvou proměnných je v grafu vyjádřena přímkou, jinými slovy y = a * x. Problém ale je, když je závislost nelineární.

Tedy např. logaritmická, exponenciální atd. Je sice možné, podobně jako u lineární regrese, použít doplnění křivky a rovnice do grafu, ale to není příliš přesné a pokrývá to jen vybrané typy závislostí. Také je možné použít kombinace příslušných funkcí.

V tomto článku si ukážeme, jak vypočítat koeficienty nelineárních závislostí pomocí řešitele. Ten postup by měl fungovat víceméně na většinu běžných typů závislostí, my si jej ukážeme na závislosti logaritmické.

Příklad

Ve firmě sledují závislost tržeb na investicích do reklamy. Data za dosavadní období jsou popsaná v této tabulce:

1 původní data
a v tomto grafu:
2 graf původních hodnot

Je patrné, že závislost je v zásadě logaritmická. Tedy že tržby sice rostou s investicemi do reklamy, ale tento růst je pomalejší a pomalejší a od určité úrovně už investice do reklamy nemají na tržby téměř žádný vliv.

Logaritmická závislost je běžně popsaná rovnicí: y = a * LN(x) + b V našem případě jsou y tržby a x investice do reklamy. Abychom popsali závislost tržeb na investicích do reklamy, je nutné zjistit hodnoty a a b. Soubor s daty a nastaveným řešitelem je ke stažení tady.

Návod

Na nalezení koeficientů použijeme nástroj Řešitel. Necháme ho hledat koeficienty a a b tak, aby byl co nejmenší rozdíl mezi hodnotami spočítanými regresní funkcí a skutečnými hodnotami.Tedy aby křivka funkce byla co nejblíže bodům v grafu.

Mimo tabulku si připravíme místo pro koeficienty a a b (žluté buňky) a z nich vypočteme pomocí logaritmické funkce nový sloupec. Ten je zatím prázdný, ale až budou vyplněné hodnoty ve žlutých buňkách, bude ukazovat odpovídající hodnoty.

3 dopočítaný sloupec

Do nového sloupce pak spočteme rozdíl skutečných a dopočtených hodnot.

4 výpočet rozdílu

Tento rozdíl za všechny hodnoty chceme minimalizovat - tak, aby nalezená funkce byla co nejblíž bodům ze zadání.. Do nové buňky tedy sečteme druhé mocniny rozdílů.

5 součet čtverců

A jdeme nastavit Řešitele. Je to jednoduché. Minimalizujeme součet mocnin rozdílů tím, že měníme žluté buňky, a nastavíme metodu řešení GRG Nonlinear.

6 nastavení řešitele

Klikneme na Solve a je hotovo.

7 výsledek

Ve žlutých buňkách jsou teď koeficienty rovnice. O tom, že jsou správné, se můžeme přesvědčit, když do grafu zahrneme původní i dopočítané hodnoty. Vidíme, že dopočítané červené body jsou přibližně na stejné křivce jako skutečné modré.

8 ověřovací graf
  • Tento výsledek má zcela praktické využití. Pokud bych chtěl v budoucím období investovat do reklamy např. 180, mohu očekávat tržby ve výši 799 * ln (180) - 2408, tedy 1742.
  • Se znalostí této závislosti a se znalostí podílu zisku na tržbách by teď už bylo snadné určit, která velikost investic do reklamy maximalizuje zisk firmy.
  • Je dobré uvědomit si, že hodnota v buňce D12, součet čtverců rozdílů, je zajímavá nejen pro řešitele. Umožnila by totiž porovnat, jak moc vhodné jsou různé typy závislostí. Nejlepší bude zřejmě ta závislost, která bude mít v této buňce nejnižší hodnotu.
  • Koeficienty, které jsme v tomto případě zjistili řešitelem, lze získat i kombinací funkcí. Koeficient "a" zjistíme vzorcem =INDEX(LINREGRESE(B2:B11;LN(A2:A11));1;2) Koeficient "b" zjistíme vzorcem =INDEX(LINREGRESE(B2:B11;LN(A2:A11));1) Je ale zajímavé, že funkce nám dá sice podobné výsledky jako řešitel, ale přeci jen o trošku méně přesné. Přeci jen je to ale přesnější než to, co se ukáže po přidání spojnice v grafu.
  • Pokud bychom chtěli najít koeficient determinace (to, co se u grafu zobrazí jako R2), použijeme vzorec =RKQ(B2:B11;LN(A2:A11))

Soubor s daty a nastaveným řešitelem je ke stažení tady.

2 comments on “Nelineární regrese v Excelu s využitím řešitele
  1. Mato napsal:

    Zaujimalo by ma ako ste vypocitali z investicie 180 trzbu 1742? 799 * ln (180) + 2408 je 6557 a nie 1742. Diky za odpoved.

  2. Jiří Beran napsal:

    Promiňte, má tam být mínus místo plusu, tedy:
    799 * ln (180) – 2408
    Opravuji.

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ář

Nejnovější komentáře

TOPlist