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:

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.

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

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

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.

Klikneme na Solve a je hotovo.

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

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