RETTLINJE (funksjon)

Denne artikkelen beskriver formelsyntaks for og bruk av funksjonen RETTLINJE i Microsoft Excel. Du finner koblinger til mer informasjon om hvordan du lager diagrammer og utfører en regresjonsanalyse, under Se også.

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.

    • Matrisen kjente_x kan omfatte ett eller flere sett med variabler. Hvis det brukes bare ett sett 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 (dvs. 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, slik at den returnerte matrisen blir {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:

Statistikk

Beskrivelse

se1;se2,...;sen

Standard feilverdier for koeffisientene m1,m2,...,mn.

seb

Standard feilverdi for konstanten b (seb = #N/A når konst er USANN).

r2

Determinantens koeffisient. Sammenligner beregnede og faktiske y-verdier, og områder i verdi fra 0 til 1. Hvis den er 1, blir det en perfekt korrelasjon i eksemplet – Det er ingen forskjell mellom den beregnede y-verdien og den faktiske y-verdien. I den andre yttergrensen er det slik at hvis determinantens koeffisient er 0, kan ikke regresjonsligningen brukes til å forutse en y-verdi. Hvis du vil ha informasjon om hvordan r2 beregnes, kan du se "Kommentarer" senere i dette emnet.

sey

Standard feilverdi for y-beregningen.

F

F-statistikken, eller F-observert verdi. Bruk F-statistikken til å bestemme om den observerte relasjonen mellom de avhengige og uavhengige variablene inntreffer tilfeldig.

df

Frihetsgradene. Bruk frihetsgradene til å hjelpe å finne F-kritiske verdier i en statistikktabell. Sammenlign verdiene du finner i tabellen, med F-statistikken som returneres av RETTLINJE, for å bestemme et konfidensnivå for modellen. Hvis du vil ha informasjon om hvordan du beregner df, se Kommentarer senere i dette emnet. Eksempel 4 viser bruken av F og df.

ssreg

Regresjonssummen av kvadrater.

ssresid

Restsummen av kvadrater. Hvis du vil ha informasjon om hvordan ssreg og ssresid beregnes, se Kommentarer senere i dette emnet.

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

En nøkkel til regresjonsstatistikk

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 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 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:

    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 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:

    Formel

    Formel

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

  • 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 dine faktiske datapunkt. 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 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 lavere den kvadrerte residualsummen for kvadrater 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. Verdien av r2 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.

  • Formler som returnerer matriser, må angis som matriseformler.

    Merknad: I Excel Online kan du ikke opprette matriseformler.

  • 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 feilen #DIV/0!. Algoritmen i funksjonene STIGNINGSTALL og SKJÆRINGSPUNKT er utformet til å søke etter ett og 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

Eksempel 1: Stigningstall og Y-skjæringspunkt

Kopier eksempeldataene i tabellen nedenfor, og lim dem inn i celle A1 i et nytt Excel-regneark. Hvis du vil at formlene skal vises resultater, merker du dem, trykker F2 og deretter ENTER. Hvis du vil, kan du justere kolonnebreddene slik at du kan se alle dataene.

Kjent y

Kjent x

1

0

9

4

5

2

7

3

Resultat (helling)

Resultat (y-skjæringspunkt)

2

1

Formek (matriseformel i cellene A7:B7)

=RETTLINJE(A2:A5;B2:B5;;USANN)

Eksempel 2: Enkel lineær regresjon

Kopier eksempeldataene i tabellen nedenfor, og lim dem inn i celle A1 i et nytt Excel-regneark. Hvis du vil at formlene skal vises resultater, merker du dem, trykker F2 og deretter ENTER. Hvis du vil, kan du justere kolonnebreddene slik at du kan se alle dataene.

Måned

Salg

1

kr3 100

2

kr4 500

3

kr4 400

4

kr5 400

5

kr7 500

6

kr8 100

Formel

Resultat

=SUMMER(RETTLINJE(B1:B6; A1:A6)*{9;1})

kr 11 000

Beregner antatt salg i niende måned basert på salgene i måned 1 til 6.

Eksempel 3: Flerlineær regresjon

Kopier eksempeldataene i tabellen nedenfor, og lim dem inn i celle A1 i et nytt Excel-regneark. Hvis du vil at formlene skal vises resultater, merker du dem, trykker F2 og deretter ENTER. Hvis du vil, kan du justere kolonnebreddene slik at du kan se alle dataene.

Gulvareal (x1)

Kontorer (x2)

Innganger (x3)

Alder (x4)

Taksert verdi (y)

2310

2

2

20

kr 142 000

2333

2

2

12

kr 144 000

2356

3

1,5

33

kr 151 000

2379

3

2

43

kr 150 000

2402

2

3

53

kr 139 000

2425

4

2

23

kr 169 000

2448

2

1,5

99

kr 126 000

2471

2

2

34

kr 142 900

2494

3

3

23

kr 163 000

2517

4

4

55

kr 169 000

2540

2

3

22

kr 149 000

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Formel (matriseformel lagt inn i A14:A18)

=RETTLINJE(E2:E12;A2:D12;SANN;SANN)

Eksempel 4: Bruke F- og r2-statistikken

I det foregående 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 at det for øyeblikket 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.

F- og df-verdiene i utdata fra funksjonen RETTLINJE kan brukes til å anslå sannsynligheten av en høyere F-verdi som har oppstått ved en tilfeldighet. F kan sammenlignes med de kritiske verdiene i publiserte F-fordelingstabeller, eller funksjonen FFORDELING i Excel kan brukes til å beregne sannsynligheten for en større F-verdi som oppstår ved en tilfeldighet. Den passende F-fordeling har frihetsgradene v1 og v2. Hvis n er antall datapunkt og konst er SANN eller utelates, er v1 = n – df – 1 og v2 = df. (Hvis konst er USANN, da er v1 = n – df og v2 = df.) Funksjonen FFORDELING  – med syntaksenFFORDELING(F;v1;v2) – returnerer sannsynligheten for en høyere F-verdi som oppstår ved en tilfeldighet. I dette eksemplet er df = 6 (celle B18) og F = 459,753674 (celle A18).

Forutsatt at en Alfa-verdi er 0,05, v1 = 11 – 6 – 1 = 4 og v2 = 6, er det kritiske nivået for F 4,53. Siden F = 459,753674 er mye høyere enn 4,53, er det ekstremt usannsynlig at en så høy F-verdi oppstod ved en tilfeldighet. (Med Alfa = 0,05 bør hypotesen om at det ikke er noe forhold mellom kjente_y og kjente_x forkastes når F overskrider det kritiske nivået, 4,53.) Du kan bruke funksjonen FFORDELING i Excel til å få frem sannsynligheten for at en så høy F-verdi oppstod ved en tilfeldighet. FFORDELING(459,753674;4;6) = 1,37E-7, ekstremt lite sannsynlig. Du kan konkludere med å finne det kritiske nivået for F i en tabell, eller (ved å bruke FFORDELING i Excel) med at regresjonsligningen er nyttig til å anslå den takserte verdien av kontorbygg i dette området. Husk at det er kritisk å bruke de riktige verdiene for v1 og v2 som ble beregnet i forrige avsnitt.

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 følgende:

t = m4 ÷ se4 = -234.24 ÷ 13,268 = -17,7

Hvis absoluttverdien for t er høy nok, kan det konkluderes med at stigningstallskoeffisienten kan brukes ved beregningen av den takserte verdien av et kontorbygg i eksempel 3. Tabellen nedenfor viser absoluttverdiene for de t-observerte verdiene.

Hvis du slår opp i en tabell i en statistikkhåndbok, vil du se at t-kritisk, tosidig, med 6 frihetsgrader og Alfa = 0,05 er 2,447. Denne kritiske verdien kan også finnes ved å bruke funksjonen TINV i Excel. TINV(0,05;6) = 2,447. Fordi absoluttverdien av t (17,7) er større enn 2,447, 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.

Variabel

t-observert verdi

Gulvareal

5,1

Antall kontorer

31,3

Antall innganger

4,8

Alder

17,7

Disse verdiene har alle en absoluttverdi over 2,447, og derfor kan alle variablene i regresjonsligningen brukes til å forutse den beregnede verdien på kontorbygg i området.

Utvid ferdighetene dine
Utforsk opplæring
Vær først ute med de nye funksjonene
Bli med i Office Insiders

Var denne informasjonen nyttig?

Takk for tilbakemeldingen!

Takk for tilbakemeldingen! Det høres ut som det kan være lurt å sette deg i kontakt med én av våre Office-kundestøtteagenter.

×