já jsem o lineární regrese na tento blog dříve, ale docela často se přímka není nejlepší způsob, jak reprezentovat data. Pro tyto specifické situace můžeme využít některé z dostupných nástrojů pro provádění nelineární regrese nebo křivky v aplikaci Excel.
Pamatujete si našeho starého přítele LINESTA? Ačkoli LINEST je zkratka pro „lineární odhad“, můžeme jej také použít pro nelineární data s několika jednoduchými vylepšeními.
řekněme, že máme nějaké údaje o poklesu tlaku vs. průtok vodním ventilem a po vykreslení dat na grafu vidíme, že data jsou kvadratická.
I když tato data jsou nelineární funkce LINREGRESE může také být použit k najít nejlepší fit křivky pro tato data. Pro polynomiální rovnici to děláme pomocí konstant pole.
výhodou použití funkce LINREGRESE získat koeficienty, které určují polynom rovnice je, že se můžeme vrátit koeficienty přímo do buňky. Tímto způsobem je nemusíme ručně přenášet z grafu.
protože rovnice je kvadratická nebo polynom druhého řádu, existují tři koeficienty, jeden pro X na druhou, jeden pro x a konstanta. Takže budeme muset začít vytvořením prostoru pro uložení tří koeficientů pro rovnici.
pomocí LINEST pro nelineární regresi v Excelu
funkce LINEST vrací pole koeficientů a volitelnou regresní statistiku. Takže to budeme muset zadat jako vzorec pole výběrem všech tří buněk pro koeficienty před zadáním vzorce.
Pokud buňky obsahující průtoku a tlaku data jsou pojmenovány „flow“ a „tlak“, vzorec vypadá takto:
=LINEST(tlak, průtok^{1,2},TRUE, FALSE)
Je známo, že y je v tomto případě jsou měření tlaku, a je známo, že x je tok měření zvýšen na první a druhou. Složené závorky “ {„a“} “ označují konstantu pole v aplikaci Excel. V podstatě říkáme Excelu, aby vytvořil dvě pole: jedno z flow a druhé z flow-squared, a přizpůsobit tlak oběma těmto polím dohromady.
a Konečně, PRAVDIVÉ a NEPRAVDIVÉ argumenty, řekni funkce LINREGRESE pro výpočet y-intercept normálně (spíše než síla na nulu) a ne se vracet další regresní statistiky.
protože se jedná o vzorec pole, musíme jej zadat zadáním Ctrl + Shift+Enter.
funkce pak vrací koeficienty x2 a x, stejně jako konstantu (protože jsme se rozhodli umožnit LINESTU vypočítat y-intercept).
koeficienty jsou totožné s těmi, generované grafu trendovou linii nástroj, ale oni jsou v buňkách a teď, který z nich dělá mnohem jednodušší použít v dalších výpočtech.
pro jakoukoli polynomiální rovnici LINEST vrací koeficient pro nejvyšší pořadí nezávislé proměnné na zcela levé straně, následovaný další nejvyšší a tak dále a nakonec konstantou.
podobná technika může být použita i pro exponenciální, logaritmickou a výkonovou křivku.
Montáž Logaritmické Křivky na Data
logaritmické funkce má tvar:
stále můžeme použít funkce LINREGRESE najít koeficient, m, a konstantní, b, pro rovnice o vložení ln(x) jako argument pro pole_x:
=LINEST(y_values,ln(x_values),TRUE,FALSE)
samozřejmě, tato metoda se vztahuje na jakékoli logaritmické rovnice, bez ohledu na základnu číslo. Takže by to mohlo být aplikováno na rovnici obsahující log10 nebo log2 stejně snadno.
Hledání Koeficientů Nejlepší-Fit Exponenciální Křivka
exponenciální funkce má tvar:
je To trochu složitější získat koeficienty a a b, pro tuto rovnici, protože nejprve musíme udělat trochu algebry, aby se rovnice na „lineární“ podobě. Za prvé, vzít přirozený logaritmus obou stran rovnice, aby se následující:
Nyní můžeme použít funkce LINREGRESE dostat ln(a) a b zadáním ln(y) jako argument pro y_values:
=LINEST(ln(y_values),x_values,TRUE,FALSE)
druhá hodnota vrácené tento maticový vzorec je ln(a), tak, aby si jen „a“, bychom jednoduše pomocí exponenciální funkce:
v aplikaci Excel, překládá se:
=EXP(číslo)
Montáž Power Funkce, aby Data
výkon funkce křivky můžete být fit na data pomocí funkce LINREGRESE v podstatě stejným způsobem, jako to děláme pro exponenciální funkci. Funkce napájení má tvar:
Opět, můžeme „linearize“ je to tím, že základna 10 logaritmus obou stran rovnice získat:
S rovnice v této formě, funkce LINREGRESE vrátí b a log10 () lze nastavit takto:
=LINEST(LOG10(yvalues),LOG10(xvalues),TRUE,FALSE)
Od funkce LINREGRESE vrací b a log10(a), budeme muset najít pomocí následujícího vzorce:
V aplikaci Excel, že vzorec je:
=10^(číslo)
to je prozatím vše. Jak vidíte, existuje řada způsobů, jak použít funkci LINEST pro nelineární křivku v aplikaci Excel.