Nelineární regrese v Excelu pomocí funkcí

V jednom z minulých článků jsme se věnovali tomu, jak určovat nelineární závislosti pomocí řešitele.
V tomto článku si ukážeme, jak koeficienty závislostí určovat pomocí funkcí. To je obvykle přesnější a praktičtější než zjišťování funkcemi. Přiznávám, že tento článek je více než inspirovaný tímto velmi zajímavým textem.

Lineární závislost

Je popsaná zde. Pro rovnici zapsanou: y = a * x + b se koeficienty počítají:
  • a = SLOPE (oblast y; oblast x) nebo =INDEX(LINREGRESE(oblast y;oblast x;1;0);1)
  • b = INTERCEPT(oblast y; oblast x) nebo =INDEX(LINREGRESE(oblasty y;oblast x;1;0);2)
Ta druhá možnost je vždy s využitím funkce LINGREGRESE. To je standardně maticová funkce, která generuje oblast, ve které jsou postupně vypsané různé charakteristiky lineární regrese. Funkce INDEX si z této oblasti vždy "vyzobne" tu charakteristiku, která nás zajímá.

Exponenciální závislost

Pro rovnici zapsanou:
y = a * e ^ (b * x) (v řeči Excelu se e zapisuje jako EXP(1)) se koeficienty počítají:
  • a = EXP(INDEX(LINREGRESE(LN(oblast y);oblast x);1;2))
  • b = INDEX(LINREGRESE(LN(oblast y);oblast x);1)

Mocninná závislost

Pro rovnici zapsanou: y = a * x ^ b se koeficienty počítají:
  • a = EXP(INDEX(LINREGRESE(LN(oblast y);LN(oblast x);;);1;2))
  • b = INDEX(LINREGRESE(LN(oblast y);LN(oblast x);;);1)

Logaritmická závislost

Pro rovnici zapsanou y = a * ln x + b se koeficienty počítají:
  • =INDEX(LINREGRESE(oblast y;LN(oblast x));1)
  • =INDEX(LINREGRESE(oblast y;LN(oblast x));1;2)

6 Comments

  1. Dobrý den, mám zjištěnou regresní exponencielu ke svým datům a podle ní i dopočítané očekávané hodnoty. Zajímalo by mě ale, jak mohu vypočítat koeficient determinace, abych určila, jak regresní funkce odpovídá realitě? Předem děkuji za odpověď.

  2. Dobrý den, nešel by použít korelační koeficient – funkce CORREL?

  3. Dobrý den, děkuji za odpověď, ale funkce correl vyjadřuje míru lineární závislosti. Můj problém spočívá hlavně v tomto: když svá data proložím exponenciální spojnicí trendu a nechám si vypsat její rovnici i míru přiléhavosti R^2, vyjdou mi určité hodnoty. Když ale dopočítám predikci hodnot podle určené exponenciely a následně s těmito daty vypočítám koeficient determinace přes součty čtverců podle vzorce, vyjde mi diametrálně odlišné číslo. Nevíte, prosím, kde by mohl být problém?

  4. Já už si moc nepamatuju jak jsem to vytvářel, ale pro praktické výpočty je to jedno, jen se pak musí ve výsledku počítat s tím, že se znaménko obracelo…

Napsat komentář

Vaše emailová adresa nebude publikována.

smazat formulářOdeslat komentář