jeg har diskutert lineær regresjon på denne bloggen før, men ganske ofte er en rett linje ikke den beste måten å representere dataene dine på. For disse spesifikke situasjonene kan vi dra nytte av noen av verktøyene som er tilgjengelige for å utføre ikke-lineær regresjon eller kurvetilpasning i Excel.
Husk vår gamle venn RETTLINJE? SELV OM RETTLINJE er kort for «lineær estimering», kan vi også bruke den til ikke-lineære data med noen få enkle tweaks.
La oss si at vi har noen data om trykkfall vs. flow rate gjennom en vannventil, og etter å plotte dataene på et diagram ser vi at dataene er kvadratisk.
SELV om disse dataene ikke er lineære, KAN RETTLINJE-funksjonen også brukes her for å finne den beste passformen for disse dataene. For en polynomligning gjør vi det ved å bruke matrisekonstanter.En fordel ved Å bruke RETTLINJE for å få koeffisientene som definerer polynomligningen, er at vi kan returnere koeffisientene direkte til celler. På den måten trenger vi ikke å overføre dem manuelt ut av diagrammet.
siden ligningen er kvadratisk, eller et andre ordenspolynom, er det tre koeffisienter, en for x i andre, en for x og en konstant. Så vi må begynne med å lage et mellomrom for å lagre de tre koeffisientene for ligningen.
BRUKE RETTLINJE for Ikke-Lineær Regresjon i Excel
RETTLINJE-funksjonen returnerer en matrise med koeffisienter og valgfri regresjonsstatistikk. Så vi må skrive det inn som en matriseformel ved å velge alle tre cellene for koeffisientene før du går inn i formelen.
hvis cellene som inneholder strømnings-og trykkdataene heter «flow» og «pressure», ser formelen slik ut:
=RETTLINJE(trykk, flyt^{1,2},TRUE, FALSE)
De kjente y-ene i dette tilfellet er trykkmålinger, og de kjente x-ene er strømmen.målinger hevet til første Og andre Kraft. Klammeparentesene » {«og»} » angir en matrisekonstant i Excel. I utgangspunktet forteller Vi Excel å lage to arrays: en av flow og en annen av flow-squared, og å passe trykket til begge disse arrays sammen.TIL Slutt forteller de SANNE og FALSKE argumentene RETTLINJE-funksjonen å beregne y-avskjæringen normalt (i stedet for å tvinge den til null)og ikke å returnere ytterligere regresjonsstatistikk, henholdsvis.
siden det er en matriseformel, må vi skrive det inn Ved Å skrive Ctrl + Shift + Enter.
funksjonen returnerer deretter koeffisientene til x2 og x samt en konstant (fordi vi valgte å tillate RETTLINJE å beregne y-avskjæringen).
koeffisientene er identiske med de som genereres av chart trendline tool, men de er i celler nå som gjør dem mye enklere å bruke i senere beregninger.
FOR en polynomligning returnerer RETTLINJE koeffisienten for den høyeste rekkefølgen av den uavhengige variabelen helt til venstre, etterfulgt av den nest høyeste og så videre, og til slutt konstanten.
en lignende teknikk kan også brukes Til Eksponentiell, Logaritmisk og Strømfunksjonskurvetilpasning i Excel.
Tilpasse En Logaritmisk Kurve Til Data
en logaritmisk funksjon har formen:
vi kan fortsatt bruke rettlinje for å finne koeffisienten, m og konstant, b, for denne ligningen ved å sette inn ln(x) som argumentet for kjente_x:
=RETTLINJE(y_values,ln(X_VALUES),TRUE,FALSE)
selvfølgelig gjelder denne metoden for enhver logaritmisk ligning, uavhengig av basisnummeret. Så det kan brukes på en ligning som inneholder log10 eller log2 like enkelt.
Finne Koeffisientene Til En Best Egnet Eksponentiell Kurve
en eksponentiell funksjon har formen:
det er litt vanskeligere å få koeffisientene, a og b, for denne ligningen fordi først må vi gjøre litt algebra for å få ligningen til å ta en «lineær» form. Først tar du den naturlige loggen på begge sider av ligningen for å få følgende:
nå kan Vi bruke RETTLINJE for å få ln(a) og b ved å skrive inn ln(y) som argument for y_values:
=RETTLINJE(ln(y_values),x_values,TRUE,FALSE)
som i excel oversetter til:
=exp(nummer)
montering av en strømfunksjon til data
en strømfunksjonskurve kan passe til data ved hjelp av rettlinje på samme måte som vi gjør det for en eksponentiell funksjon. En kraftfunksjon har formen:
Igjen kan vi «linearisere» det ved å ta base 10-loggen på begge sider av ligningen for å oppnå:
med ligningen i dette skjemaet kan rettlinje-funksjonen returnere b og log10(a) settes opp slik:
=rettlinje(log10(yvalues), log10(xvalues), true,false)
siden rettlinje-funksjonen returnerer b og log10(a), må vi finne a med følgende formel:
i excel er denne formelen:
=10^(tall)
Det er det for nå. Som du kan se, finnes det en rekke måter å bruke RETTLINJE-funksjonen for ikke-lineær kurvetilpasning I Excel.