am discutat regresie liniară pe acest blog înainte, dar destul de des o linie dreaptă nu este cel mai bun mod de a reprezenta datele. Pentru aceste situații specifice, putem profita de unele dintre instrumentele disponibile pentru a efectua regresia neliniară sau montarea curbei în Excel.
îți amintești de vechiul nostru prieten LINEST? Deși LINEST este prescurtarea de la” estimare liniară”, o putem folosi și pentru date neliniare cu câteva modificări simple.
Să presupunem că avem câteva date despre scăderea presiunii vs. debitul printr-o supapă de apă și, după trasarea datelor pe o diagramă, vedem că datele sunt pătratice.
chiar dacă aceste date sunt neliniare, funcția LINEST poate fi folosită și aici pentru a găsi curba cea mai potrivită pentru aceste date. Pentru o ecuație polinomială, facem asta folosind constante matrice.
un avantaj al utilizării LINEST pentru a obține coeficienții care definesc ecuația polinomială este că putem returna coeficienții direct în celule. În acest fel nu trebuie să le transferăm manual din diagramă.
deoarece ecuația este pătratică sau un polinom de ordinul doi, există trei coeficienți, unul pentru X pătrat, unul pentru x și o constantă. Deci, va trebui să începem prin crearea unui spațiu pentru a stoca cei trei coeficienți pentru ecuație.
utilizarea LINEST pentru regresie neliniară în Excel
funcția LINEST returnează o serie de coeficienți și statistici de regresie opționale. Deci, va trebui să o introducem ca o formulă matrice selectând toate cele trei celule pentru coeficienți înainte de a introduce formula.
dacă celulele care conțin datele de debit și presiune sunt denumite „flux” și „presiune”, formula arată astfel:
=LINEST(presiune, debit^{1,2},adevărat, fals)
y-urile cunoscute în acest caz sunt măsurătorile de presiune, iar X-urile cunoscute sunt măsurătorile de debit ridicate la prima și a doua putere. Parantezele buclate, ” {„și”}”, indică o constantă de matrice în Excel. Practic, îi spunem lui Excel să creeze două matrice: una de flux și alta de flux-pătrat și să se potrivească presiunii la ambele matrice împreună.
în cele din urmă, argumentele adevărate și FALSE spun funcției LINEST să calculeze interceptarea y în mod normal (mai degrabă decât să o forțeze la zero) și să nu returneze statistici suplimentare de regresie, respectiv.
deoarece este o formulă matrice, trebuie să o introducem tastând Ctrl+Shift+Enter.
funcția returnează apoi coeficienții lui x2 și x, precum și o constantă (pentru că am ales să permitem LINEST să calculeze interceptarea y).
coeficienții sunt identici cu cei generați de instrumentul grafic trendline, dar acum sunt în celule, ceea ce le face mult mai ușor de utilizat în calculele ulterioare.
pentru orice ecuație polinomială, LINEST returnează coeficientul pentru cea mai înaltă ordine a variabilei independente din partea stângă, urmată de următoarea cea mai mare și așa mai departe și, în final, Constanta.
o tehnică similară poate fi utilizată și pentru montarea curbei funcțiilor exponențiale, logaritmice și de putere în Excel.
montarea unei curbe logaritmice la date
o funcție logaritmică are forma:
putem folosi în continuare LINEST pentru a găsi coeficientul, m, și constantă, b, pentru această ecuație prin introducerea ln(x) ca argument pentru known_x:
=LINEST(y_values,ln(x_values),TRUE,FALSE)
desigur, această metodă se aplică oricărei ecuații logaritmice, indiferent de numărul de bază. Deci, ar putea fi aplicată unei ecuații care conține log10 sau log2 la fel de ușor.
găsirea coeficienților unei curbe exponențiale care se potrivește cel mai bine
o funcție exponențială are forma:
este puțin mai complicat să obținem coeficienții, a și b, pentru această ecuație, deoarece mai întâi trebuie să facem puțină algebră pentru a face ecuația să ia o formă „liniară”. Mai întâi, luați jurnalul natural al ambelor părți ale ecuației pentru a obține următoarele:
acum putem folosi LINEST pentru a obține ln (a) și b introducând ln (y) ca argument pentru y_values:
=LINEST(LN(y_values),x_values,TRUE,FALSE)
a doua valoare returnată de această formulă matrice este ln(a), astfel încât pentru a obține doar „a”, ne-ar folosi pur și simplu funcția exponențială:
care, în Excel, se traduce la:
=exp(număr)
montarea unei funcții de alimentare la date
o curbă a funcției de alimentare poate fi potrivită pentru date folosind LINEST în același mod în care o facem pentru o funcție exponențială. O funcție de putere are forma:
Din nou, îl putem „lineariza” luând Jurnalul de bază 10 al ambelor părți ale ecuației pentru a obține:
cu ecuația în această formă, funcția LINEST pentru a returna B și log10(A) poate fi configurată astfel:
=LINEST(log10(Yvalues),log10(xvalues),true,false)
deoarece funcția LINEST returnează B și log10(a), va trebui să găsim a cu următoarea formulă:
în Excel, această formulă este:
=10^(număr)
asta e pentru moment. După cum puteți vedea, există o serie de moduri de a utiliza funcția LINEST pentru montarea curbei neliniare în Excel.