Nelineární regrese pomocí funkcí

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 on “Nelineární regrese pomocí funkcí
  1. Lu napsal:

    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. Jiří Beran napsal:

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

  3. Lu napsal:

    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. Jiří Beran napsal:

    Dobrý den,
    v tomhle bohužel neporadím. To by chtělo zeptat se spíš na nějakém webu jako je tenhle:
    http://forum.matematika.cz/

  5. w+v napsal:

    Proč zde je vzorec na Log. y = a * ln x + b ale na příkladu na odkazu: http://vyuka-excelu.cz/navody/pokrocila-analyza-regrese-korelace/nelinearni-regrese-v-excelu-s-vyuzitim-resitele/ je y = a * ln x – b ? přesněji proč se + změnilo na -?

  6. Jiří Beran napsal:

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