RETTLINJE

Se også

Beregner statistikken for en linje ved hjelp av "minste kvadraters" metode for å beregne en rett linje som er best tilpasset dataene, og returnerer en matrise som beskriver linjen. Ettersom denne funksjonen returnerer en matrise med verdier, må den angis som en matriseformel.

Formelen for linjen er:

y = mx + b

eller

y = m1x1 + m2x2 + ... + b (hvis det er flere områder med x-verdier)

der den avhengige y-verdien er en funksjon av de uavhengige x-verdiene. M-verdiene er koeffisienter som svarer til hver enkelt x-verdi, og b er en konstant verdi. Legg merke til at y, x og m kan være vektorer. Matrisen som RETTLINJE returnerer, er {mnd;mnd-1;...;m1;b}. RETTLINJE kan også returnere ekstra regresjonsstatistikk.

Syntaks

RETTLINJE(kjente_y;kjente_x,konst;statistikk)

Kjente_y    er 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 egen variabel.

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

Kjente_x    er et valgfritt sett med x-verdier som du kanskje allerede kjenner i forholdet y = mx + b.

• Matrisen kjente_x kan omfatte ett eller flere sett med variabler. Hvis det bare er brukt ett sett med variabler, kan kjente_y og kjente_x være områder med en hvilken som helst form, så lenge de har de samme dimensjonene. Hvis flere variabler brukes, må kjente_y være en vektor (det vil si et område med en høyde på én rad eller en bredde på én kolonne).

• Hvis kjente_x er utelatt, brukes matrisen {1;2;3;...} som er den samme størrelsen som kjente_y.

Konst    er 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, settes b til 0, og m-verdiene tilpasses til y = mx.

Statistikk    er en logiske verdien som angir om ekstra regresjonsstatistikk skal returneres.

• Hvis statistikk er SANN, returnerer RETTLINJE ekstra regresjonsstatistikk, slik at den returnerte matrisen blir {mn;mn-1;...;m1;b\sen;sen-1;...;se1;seb\r2;sey\F;df\ssreg;ssresid}.

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

Oversikt over den ekstra regresjonsstatistikken:

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

Kommentarer

• 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 punkt 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 ved punktet der linjen krysser y-aksen.

  Ligningen for 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 TREND-funksjonen.

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

  Stigningstall:
  =INDEKS(RETTLINJE(kjente_y;kjente_x);1)

  Y-skjæringspunkt:
  =INDEKS(RETTLINJE(kjente_y;kjente_x);2)

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

  

  

• Funksjonene RETTLINJE og KURVE beregner henholdsvis den rette linjen og den eksponentielle kurven som passer best til dataene. Du må imidlertid selv avgjøre hvilket av de to resultatene som passer best til dataene. Du kan beregne TREND(kjente_y;kjente_x) for en rett linje, eller VEKST(kjente_y;kjente_x) for en eksponentiell kurve. Disse funksjonene, uten argumentet nye_ x, returnerer en matrise med y-verdier som forutses langs linjen eller kurven ved de faktiske datapunktene. Du kan deretter sammenligne de anslåtte verdiene med de faktiske verdiene. Det er enklere å se forskjellene mellom verdiene dersom du fremstiller dem grafisk i et diagram.
• 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 restsum. Deretter beregnes summen av de kvadrerte differansene mellom de faktiske y-verdiene og gjennomsnittet av y-verdiene, som kalles kvadrert totalsum (kvadrert regresjonssum + kvadrert restsum). Jo lavere den kvadrerte restsummen er sammenlignet med kvadrert totalsum, jo større er verdien av determinantens koeffisient, r2, som er en indikator på hvor godt ligningen fra regresjonsanalysen forklarer forholdet mellom variablene.
• Formler som returnerer matriser, må angis som matriseformler.
• Når du skriver inn en matrisekonstant, for eksempel kjent_x, som et argument, må du bruke semikolon for å skille verdier i samme rad, og omvendt skråstrek for å skille rader. Skilletegn kan være forskjellige avhengig av innstillingene i Regionale innstillinger i Kontrollpanelet.
• Legg merke til at y-verdiene som regresjonsligningen beregner, ikke nødvendigvis er gyldige dersom de er utenfor området for de y-verdiene du brukte til å sette opp ligningen.

Eksempel 1   Stigningstall og Y-skjæringspunkt

Det kan være enklere å forstå eksemplet hvis du kopierer det til et tomt regneark.

Hvordan?

Obs!  Formelen i eksemplet må angis som en matriseformel. Når du har kopiert eksemplet til et tomt regneark, merker du området A7:B7 med start i formelcellen. Trykk F2, og trykk deretter CTRL+SKIFT+ENTER. Hvis formelen ikke er angitt som en matriseformel, er enkeltresultatet 2.

Når formelen angis som en matrise, returneres stigningstallet (2) og y-skjæringspunktet (1).

Eksempel 2   Enkel lineær regresjon

Det kan være enklere å forstå eksemplet hvis du kopierer det til et tomt regneark.

Hvordan?

Vanligvis er SUMMER({m;b}*{x;1}) lik mx + b, den beregnede y-verdien for en gitt x-verdi. Du kan også bruke TREND-funksjonen.

Obs!  Formelen i eksemplet må angis som en matriseformel. Når du har kopiert eksemplet til et tomt regneark, merker du formelcellen (A9). Trykk F2, og trykk deretter CTRL+SKIFT+ENTER.

Eksempel 3   Sammensatt lineær regresjon

Sett at en byggmester vurderer å kjøpe å gruppe bygninger beregnet for småbedrifter i et etablert forretningsområde.

Byggmesteren kan bruke sammensatt lineær regresjonsanalyse til å beregne verdien av et kontorbygg i et gitt område basert på følgende variabler:

I dette eksemplet forutsettes det at det eksisterer en rettlinjet relasjon mellom hver uavhengige variabel (x1, x2, x3 og x4) og den avhengige variabelen (y), verdien på kontorbygg i området.

Byggmesteren velger et tilfeldig utvalg på 11 kontorbygg av 1 500 mulige alternativer, og innhenter dataene nedenfor. "Halv inngang" betyr at inngangen bare brukes til leveringer.

Det kan være enklere å forstå eksemplet hvis du kopierer det til et tomt regneark.

Hvordan?

Obs!  Formelen i eksemplet må angis som en matriseformel. Når du har kopiert eksemplet til et tomt regneark, merker du området A14:E18 med start i formelcellen. Trykk F2, og trykk deretter CTRL+SKIFT+ENTER. Hvis formelen ikke er angitt som en matriseformel, er enkeltresultatet -234,2371645.

Når formelen er angitt som en matrise, returneres regresjonsstatistikken nedenfor. Bruk denne nøkkelen til å identifisere statistikken du vil ha.

Ligningen for sammensatt regresjon y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b kan nå hentes ved hjelp av verdiene fra rad 14:

y = 27,64*x1 + 12 530*x2 + 2 553*x3+ 234,24*x4 + 52 318

Byggmesteren kan nå beregne den takserte verdien av et kontorbygg i det samme området som er på 2 500 kvadratmeter, har tre kontorer og to innganger, og som er 25 år gammelt, ved hjelp av følgende ligning:

y = 27,64*2500 + 12 530*3 + 2 553*2 - 234,24*25 + 52 318 = kr 158 261

Du kan også kopiere følgende formel til celle A21 i eksempelarbeidsboken.

Du kan også bruke TREND-funksjonen til å beregne denne verdien.

Eksempel 4   Bruke F- og R2-statistikken

I det forrige eksemplet hadde determinantens koeffisient, eller r2, verdien 0,99675 (se celle A17 i utdataene for RETTLINJE), som indikerer en sterk relasjon mellom de uavhengige variablene og salgsprisen. Du kan bruke F-statistikken til å bestemme om disse resultatene, med en så høy r2-verdi, har oppstått ved en tilfeldighet.

Tenk deg et øyeblikk at ikke er noen relasjon mellom variablene, men at du har valgt et sjeldent utvalg av 11 kontorbygg som forårsaker at statistikkanalysen viser en sterk relasjon. Begrepet "Alfa" brukes for sannsynligheten for at det feilaktig konkluderes at det finnes en relasjon.

Det er en relasjon mellom variablene hvis den F-observerte statistikken er større enn den F-kritiske verdien. Den F-kritiske verdien kan finnes ved å referere til en tabell med F-kritiske verdier i mange statistikkbøker. Hvis du vil lese tabellen, utfører du en ensidig test, bruker Alfa-verdien 0,05, og for frihetsgradene (forkortet som v1 og v2 i de fleste tabeller) bruker du v1 = k = 4 og v2 = n - (k + 1) = 11 - (4 + 1) = 6, der k er antall variabler i regresjonsanalysen og n er antall datapunkt. Den F-kritiske verdien er 4,53.

Den F-observerte verdien er 459,753674 (celle A18), som er betydelig større enn den F-kritiske verdien 4,53. Derfor kan regresjonsligningen brukes til å forutse den takserte verdien av kontorbygg i dette området.

Eksempel 5   Beregne T-statistikken

En annen hypotesetest vil avgjøre om hver stigningstallskoeffisient kan brukes ved beregningen av den takserte verdien av et kontorbygg i eksempel 3. Hvis du for eksempel vil kontrollere den statistiske betydningen av alderskoeffisienten, dividerer du -234,24 (stigningstallskoeffisient for alder) med 13,268 (den beregnede standardfeilen for alderskoeffisienter i celle A15). Den t-observerte verdien er:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17.7

Hvis du slår opp i en tabell i en statistikkhåndbok, vil du se at t-kritisk, ensidig, med 6 frihetsgrader og Alfa = 0,05 er 1,94. Fordi den absolutte verdien av t, 17,7, er større enn 1,94, er alder en viktig variabel ved beregning av den takserte verdien av et kontorbygg. Hver av de andre uavhengige variablene kan kontrolleres mht statistisk signifikans på lignende måte. Nedenfor følger t-observerte verdier for hver av de uavhengige variablene.

Disse verdiene har alle en absoluttverdi større enn 1,94, og derfor kan alle variablene i regresjonsligningen brukes til å anslå den takserte verdien på kontorbygg i området.