RETTLINJE (funksjon)

Beskrivelse

Funksjonen RETTLINJE beregner statistikken for en linje ved å bruke "minste kvadraters" metode til å beregne en rett linje som er best tilpasset dataene, og returnerer deretter en matrise som beskriver linjen. Du kan også kombinere funksjonen RETTLINJE med andre funksjoner for å beregne statistikken for andre typer modeller som er lineære i de ukjente parameterne, inkludert polynom, logaritmisk, eksponentiell og potensrekke. Ettersom denne funksjonen returnerer en matrise av verdier, må den skrives inn som en matriseformel. Instruksjoner følger etter eksemplene i denne artikkelen.

Formelen for linjen er:

y = mx + b

– eller –

y = m1x1 + m2x2 + ... + b

hvis det er flere områder av x-verdier, der de avhengige y-verdiene er en funksjon av de uavhengige x-verdiene. M-verdiene er koeffisienter som svarer til hver enkelt x-verdi, og b er en konstantverdi. Merk at y, x og m kan være vektorer. Matrisen som funksjonen RETTLINJE returnerer, er {mn,mn-1,...,m1,b}. RETTLINJE kan også returnere flere regresjonsstatistikker.

Syntaks

RETTLINJE(kjente_y, [kjente_x], [konst], [stats])

Syntaksen for RETTLINJE har følgende argumenter:

Syntaks

• kjente_y    Obligatorisk. Settet med y-verdier som du allerede kjenner i forholdet y = mx + b.

  • Hvis matrisen kjente_y er i en enkelt kolonne, tolkes hver kolonne med kjente_x som en separat variabel.

  • Hvis matrisen kjente_y er i én rad, blir hver rad med kjente_x tolket som en egen variabel.

• kjente_x    Valgfritt. Et sett med x-verdier som du eventuelt kjenner i forholdet y = mx + b.

  • Området for known_x kan inneholde ett eller flere sett med variabler. Hvis bare én variabel brukes, kan known_y og known_x være områder av en hvilken som helst figur, så lenge de har like dimensjoner. Hvis mer enn én variabel brukes, må known_y være en vektor (det vil se ut som et område med en høyde på én rad eller en bredde på én kolonne).

  • Hvis kjente_x utelates, blir det satt lik matrisen {1;2;3;...} som er den samme størrelsen som kjente_y.

• konst    Valgfritt. En logisk verdi som angir om konstanten b skal tvinges til å være lik 0 eller ikke.

  • Hvis konst er SANN eller utelates, beregnes b normalt.

  • Hvis konst er USANN, angis b til er lik 0, og m-verdiene tilpasses til y = mx.

• stats    Valgfritt. En logisk verdi som angir om ekstra regresjonsstatistikk skal returneres.

  • Hvis stats er SANN, returnerer RETTLINJE den ekstra regresjonsstatistikken. Som et resultat er den returnerte matrisen {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r^2,sey; F,df; ssreg,ssresid}.

  • Hvis stats er USANN eller utelates, returnerer RETTLINJE bare m-koeffisientene og konstanten b.

    Den ekstra regresjonsstatistikken er som følger:

Følgende illustrasjon viser i hvilken rekkefølge den ekstra regresjonsstatistikken returneres.

Merknader

• Du kan beskrive alle rette linjer med stigningstallet og y-skjæringspunktet:

  Stigningstall (m):
  Hvis du vil finne stigningstallet for en linje, ofte skrevet som m, tar du to punkter på linjen( x1,y1) og (x2,y2); stigningstallet er lik (y2 - y1)/(x2 - x1).

  Y-skjæringspunkt (b):
  Y-skjæringspunktet for en linje, ofte skrevet som b, er verdien av y på punktet der linjen krysser y-aksen.

  Ligningen av en rett linje er y = mx + b. Når du kjenner verdiene av m og b, kan du beregne alle punktene på linjen ved å legge y- og x-verdien inn i ligningen. Du kan også bruke funksjonen TREND.

• Når du bare har én uavhengig y-variabel, kan du finne verdiene for stigningstallet og y-skjæringspunktet direkte ved hjelp av følgende formler:

  Skråning:
  =INDEKS(RETTLINJE(known_y;known_x),1)

  Y-skjæringspunkt:
  =INDEKS(RETTLINJE(known_y;known_x),2)

• Nøyaktigheten av linjen som beregnes av funksjonen RETTLINJE, avhenger av graden av punkt i dataene. Jo mer lineære dataene er, desto mer nøyaktig blir RETTLINJE-modellen. RETTLINJE bruker minste kvadraters metode til å bestemme beste tilpassing av dataene. Når du bare har én uavhengig x-variabel, baseres beregningene for m og b på følgende formler:

  

  

  der x og y er utvalgsgjennomsnitt, for eksempel x = GJENNOMSNITT(kjente_x) og y = GJENNOMSNITT(kjente_y).

• Linje- og kurvetilpasningsfunksjonene RETTLINJE og KURVE kan beregne den beste rette linjen eller eksponentielle kurven som passer til dataene. Du må imidlertid bestemme hvilke av de to resultatene som passer best til dataene dine. Du kan beregne TREND(known_y;known_x) for en rett linje, eller VEKST(known_y, known_x) for en eksponentiell kurve. Disse funksjonene, uten argumentet new_x , returnerer en matrise med y-verdier som forutses langs linjen eller kurven ved de faktiske datapunktene. Deretter kan du sammenligne de beregnede verdiene med de faktiske verdiene. Det kan være lurt å lage et diagram over dem begge for en visuell sammenligning.

• I regresjonsanalyser beregnes den kvadrerte differansen mellom den anslåtte og den faktiske y-verdien for hvert punkt. Summen av disse kvadrerte differansene kalles kvadrert residualsum, ssresid. Deretter beregnes den totale summen kvadratene, sstotal. Når argumentet konst er SANN eller utelatt, er den totale summen lik summen av de kvadrerte differansene mellom de faktiske y-verdiene og gjennomsnittet av y-verdiene. Når argumentet konst er USANN, er den totale summen lik summen av kvadratene for de faktiske y-verdiene (uten fratrekk av den gjennomsnittlige y-verdien fra hver enkelt individuelle y-verdi). Deretter kan regresjonssummen for kvadratene, ssreg, bli funnet fra: ssreg = sstotal - ssresid. Jo mindre restsummen av kvadrater er, sammenlignet med den totale summen av kvadrater, jo større er verdien av koeffisienten av besluttsomhet, r², som er en indikator på hvor godt ligningen som er resultatet av regresjonsanalysen, forklarer forholdet mellom variablene. Verdien av r² er lik ssreg/sstotal.

• Noen ganger kan det hende at én eller flere av X-kolonnene (anta at Y and X er i kolonner) ikke har noen ekstra forutsett verdi hvis det finnes andre X-kolonner. Med andre ord: Ved å eliminere én eller flere X-kolonner, kan det føre til forutsette Y-verdier som er like nøyaktige. I dette tilfellet bør disse overflødige X-kolonnene utelates fra regresjonsmodellen. Dette fenomenet kalles kollinearitet fordi enhver overflødig X-kolonne kan uttrykkes som en sum av intervaller av nødvendige X-kolonner. Funksjonen RETTLINJE kontrollerer om det finnes kollinearitet og fjerner overflødige X-kolonner fra regresjonsmodellen når de identifiseres. X-kolonner som er blitt fjernet, kan gjenkjennes i utdata for RETTLINJE ved å ha 0 koeffisienter i tillegg til 0 se-verdier. Hvis én eller flere kolonner er fjernet som overflødige, påvirkes df fordi df er avhengig av antallet X-kolonner som faktisk er brukt til forutseende formål. Hvis du vil se beregningen av df mer detaljert, se eksempel 4. Hvis df er endret fordi overflødige X-kolonner er blitt fjernet, er også verdier av sey og F påvirket. Kollinearitet bør være relativ sjeldent i praksis. Det er imidlertid ett tilfelle der det kan dukke opp, og det er når noen X-kolonner bare inneholder 0- og 1-verdier som indikatorer på om et emne i et eksperiment regnes som medlem av en bestemt gruppe eller ikke. Hvis konst er SANN eller utelates, setter funksjonen RETTLINJE effektivt inn en ekstra X-kolonne på alle 1-verdiene for å vise skjæringspunktet. Hvis du har en kolonne med 1 for hvert emne hvis det er hannkjønn eller 0 hvis ikke, og du også har en kolonne med 1 for hvert emne hvis det er hunnkjønn eller 0 hvis ikke, er den sistnevnte kolonnen overflødig fordi oppføringer i den kan hentes ved å trekke oppføringen i kolonnen “hannkjønn indikator” fra oppføringen i den ekstra kolonnen for alle 1-verdiene som er lagt til av funksjonen RETTLINJE.

• Df-verdien beregnes som følger, når ingen X-kolonner er fjernet fra modellen på grunn av kollinearitet: Hvis det er k-kolonner med kjente_x og const = SANN eller er utelatt, df = n – k – 1. Hvis const = FALSE, df = n - k. I begge tilfeller øker hver X-kolonne som ble fjernet på grunn av kollinearitet, df-verdien med 1.

• Når du skriver inn en matrisekonstant (for eksempel kjent_x) som et argument, må du bruke komma for å skille verdier som finnes i samme rad, og semikolon for å skille rader. Skilletegn kan være forskjellige avhengig av de regionale innstillingene.

• Du bør være oppmerksom på at y-verdiene som regresjonsligningen anslår, ikke nødvendigvis er gyldige dersom de er utenfor området for de y-verdiene du brukte til å sette opp ligningen.

• Den underliggende algoritmen som brukes i funksjonen RETTLINJE, er forskjellig fra den underliggende algoritmen brukes i funksjonene SKJÆRINGSPUNKT og STIGNINGSTALL. Forskjellen mellom disse algoritmene kan føre til ulike resultater når data ikke er bestemte og kollineære. Hvis for eksempel datapunktene til argumentet kjente_y er 0 og datapunktene til argumentet kjente_x er 1:

  • RETTLINJE returnerer en verdi på 0. Algoritmen i funksjonen RETTLINJE er utformet for å returnere fornuftige resultater for kollineære data, og i dette tilfellet kan minst ett svar bli funnet.

  • STIGNINGSTALL og SKJÆRINGSPUNKT returnerer en #DIV/0! . Algoritmen for funksjonene STIGNINGSTALL og SKJÆRINGSPUNKT er utformet for å se etter bare ett svar, og i dette tilfellet kan det være mer enn ett svar.

• I tillegg til å bruke KURVE til å beregne statistikk for andre regresjonstyper, kan du bruke RETTLINJE til å beregne et område av andre regresjonstyper ved å angi funksjoner for x- og y-variablene som x- og y-seriene for RETTLINJE. Formelen

  =RETTLINJE(yvalues, xvalues^COLUMN($A:$C))

  fungerer når du har en enkeltkolonne med y-verdier og en enkeltkolonne med x-verdier for å beregne den kubiske (polynom av orden 3) approksimasjonen for skjemaet:

  y = m1*x + m2*x^2 + m3*x^3 + b

  Du kan justere denne formelen for å beregne andre typer regresjon, men i enkelte tilfeller krever den at du justerer utdataverdiene og annen statistikk.

• F-testverdien som returneres av funksjonen RETTLINJE skiller seg fra F-testverdien som returneres av funksjonen FTEST. RETTLINJE returnerer F-statistikken, mens FTEST returnerer sannsynligheten.

Eksempler