olen keskustellut lineaarinen regressio tässä blogissa ennen, mutta melko usein suora viiva ei ole paras tapa edustaa tietoja. Näissä erityistilanteissa voimme hyödyntää joitakin työkaluja, jotka ovat käytettävissä epälineaarisen regression tai käyrän sovittamiseen Excelissä.
Muistatko vanhan ystävämme LINESTIN? Vaikka LINEST on lyhenne sanoista ”linear estimation”, Voimme käyttää sitä myös epälineaarisiin tietoihin muutamalla yksinkertaisella hienosäädöllä.
sanotaan, että meillä on joitain tietoja painehäviöstä vs. virtausnopeus Vesiventtiilin läpi, ja sen jälkeen, kun tiedot on piirretty kaavioon, näemme, että tiedot ovat neliömäisiä.
vaikka tämä tieto on epälineaarinen, LINEST-funktiota voidaan käyttää myös tässä, jotta löydetään paras fit-käyrä tälle datalle. Jotta polynomi yhtälö, teemme, että käyttämällä array vakioita.
etuna LINESTIN avulla saadaan polynomiyhtälön määrittelevät kertoimet on, että kertoimet voidaan palauttaa suoraan soluihin. Siten niitä ei tarvitse siirtää manuaalisesti pois kartasta.
koska yhtälö on nelikulmainen eli toisen kertaluvun polynomi, on olemassa kolme kerrointa, joista yksi on X: n neliö, yksi x: n ja vakio. Joten meidän täytyy aloittaa luomalla tilaa tallentaa kolme kertoimet yhtälö.
LINESTIN käyttäminen epälineaariseen regressioon Excelissä
LINEST-funktio palauttaa joukon kertoimia ja valinnaisia regressiotilastoja. Joten meidän täytyy syöttää se array kaava valitsemalla kaikki kolme solujen kertoimet ennen syöttämistä kaava.
Jos virtaus-ja painetiedot sisältäviä soluja nimitetään ”virtauksiksi” ja ”paineiksi”, kaava näyttää tältä:
=LINEST(paine, virtaus^{1,2},tosi, epätosi)
tässä tapauksessa tunnetut y: t ovat painemittauksia, ja tunnetut x: t ovat virtausmittaukset nostettu ensimmäiseen ja toiseen potenssiin. Kihara suluissa, ” {”ja”}”, osoittavat joukkovakion Excelissä. Pohjimmiltaan, kerromme Excel luoda kaksi ryhmää: yksi flow ja toinen flow-potenssiin, ja sovittaa paine molemmat ryhmät yhteen.
lopuksi tosi-ja epätosi argumentit kertovat LINEST-funktion laskevan y-leikkauspisteen normaalisti (sen sijaan, että se pakotettaisiin nollaan) ja olemaan palauttamatta ylimääräisiä regressiotilastoja vastaavasti.
koska se on array kaava, meidän täytyy syöttää se kirjoittamalla Ctrl+Shift+Enter.
funktio palauttaa tällöin X2: n ja x: n kertoimet sekä vakion (koska päätimme sallia LINESTIN laskea y-sieppauksen).
kertoimet ovat samat kuin kaavion trendline-työkalun tuottamat, mutta ne ovat nyt soluissa, mikä helpottaa niiden käyttöä myöhemmissä laskelmissa.
mille tahansa polynomiyhtälölle LINEST palauttaa kertoimen, joka on riippumattoman muuttujan korkein järjestyksessä vasemmalla puolella, jota seuraa seuraavaksi korkein ja niin edelleen, ja lopuksi vakio.
samankaltaista tekniikkaa voidaan käyttää eksponentti -, logaritminen-ja Potenssifunktiokäyrän sovittamiseen myös Excelissä.
logaritmisen käyrän sovittaminen dataan
logaritminen funktio on muotoa:
voimme vielä käyttää linestiä löytääksemme kertoimen, m, ja vakion, b, tälle yhtälölle lisäämällä Ln(X): n argumentiksi known_x: n:
=LINEST(y_values,ln(x_values),TRUE,FALSE)
tätä menetelmää sovelletaan tietysti mihin tahansa logaritmiseen yhtälöön kantaluvusta riippumatta. Sitä voitaisiin siis soveltaa yhtälöön, joka sisältää log10 tai log2 yhtä helposti.
parhaiten sovitettavan Eksponenttikäyrän kertoimien löytäminen
eksponenttifunktio on muotoa:
on hieman hankalampaa saada kertoimet, a ja b, tälle yhtälölle, koska ensin täytyy tehdä vähän algebraa, jotta yhtälö saa ”lineaarisen” muodon. Otetaan ensin yhtälön molempien puolien luonnollinen loki, jotta saadaan seuraava:
nyt voidaan LINESTIN avulla saada Ln(A) ja b syöttämällä Ln(y) argumentiksi y_values:
=LINEST(Ln(y_values),x_values,TRUE,FALSE)
tämän joukon kaavalla palautettu toinen arvo on LN(A), joten saadaksemme vain ”a”, käyttäisimme yksinkertaisesti eksponenttifunktiota:
joka Excelissä tarkoittaa:
=Exp(numero)
tehofunktion sovittaminen dataan
tehofunktio voidaan sovittaa dataan linestin avulla paljolti samalla tavalla kuin teemme sen eksponenttifunktiolle. Potenssifunktiolla on muoto:
taas voidaan ”linearisoida” se ottamalla yhtälön molemmilta puolilta kantaloki:
yhtälön ollessa tässä muodossa LINEST funktio palauttaa B: n ja log10(a) voidaan asettaa näin:
=LINEST(log10(yvalues), log10(xvalues), true,false)
koska LINEST funktio palauttaa B: n ja log10(A): n,on löydettävä a seuraavalla kaavalla:
Excelissä,että kaava on:
=10^(numero)
That ’ s it for now. Kuten näette, on olemassa useita tapoja käyttää LINEST-toimintoa epälineaariselle käyrälle Excelissä.