Denne artikkelen beskriver formelsyntaks for og bruk av funksjonen RETTLINJE i Microsoft Excel.
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:
known_y er Obligatorisk. Settet med y-verdier som du allerede kjenner i forholdet y = mx + b.
- Hvis området med known_y er i én kolonne, tolkes hver kolonne i known_x som en egen variabel.
- Hvis known_y-området er i én enkelt rad, tolkes hver rad i known_x som en egen variabel.
known_x er Valgfritt. Et sett med x-verdier som du eventuelt kjenner i forholdet y = mx + b.
- Utvalget av known_x kan omfatte ett eller flere sett med variabler. Hvis det bare er brukt én variabel, kan known_y og known_x være områder med en hvilken som helst form, så lenge de har de samme dimensjonene. Hvis mer enn én variabel brukes, må known_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 known_x er utelatt, antas det å være matrisen {1,2,3,...} som er den samme størrelsen som known_y's.
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.
statistikk Valgfritt. En logisk verdi som angir om ekstra regresjonsstatistikk skal returneres.
- Hvis stats er SANN, returnerer RETTLINJE den ekstra regresjonsstatistikken. Resultatet er at den returnerte matrisen blir {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r2,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 er2 beregnes, 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.
Merknader
Du kan beskrive alle rette linjer med stigningstallet og y-skjæringspunktet:
Stigningstall (m):
For å finne helningen til en linje, ofte skrevet som m, ta 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 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(known_y known_x s),1)
Y-skjæringspunkt:
=INDEKS(RETTLINJE(known_y known_x s),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:
hvor x og y er utvalgsgjennomsnitt; det vil si, x = GJENNOMSNITT(kjente x) og y = GJENNOMSNITT(known_y).Funksjonene for linje- og kurvetilpasning RETTLINJE og KURVE kan beregne den beste rette linjen eller eksponentielle kurven som passer til dataene. Du må imidlertid bestemme hvilket av de to resultatene som passer best til dataene. 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 new_x argument, returnerer en matrise med y-verdier forutsett 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 diagrammer over 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 konstargumentet = SANN eller utelates, er den totale summen av kvadratene summen av de kvadrerte differansene mellom de faktiske y-verdiene og gjennomsnittet av y-verdiene. Når konstargumentet = USANN, er den totale summen av kvadratene summen av kvadratene til de faktiske y-verdiene (uten å trekke den gjennomsnittlige y-verdien fra hver enkelt 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 = SANN eller utelates, setter funksjonen RETTLINJE effektivt inn en ekstra X-kolonne med alle 1-verdiene for å modellere 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.
Verdien av df beregnes som følger når ingen X-kolonner fjernes fra modellen på grunn av kollinearitet: Hvis det er n kolonner av known_x og konst = SANN eller utelates, df = n – k – 1. Hvis konst = USANN, 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 known_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 known_y-argumentet er 0 og datapunktene til known_x-argumentet 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! -feil. Algoritmen i funksjonene STIGNINGSTALL og SKJÆRINGSPUNKT er utformet til å søke 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
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 (dynamisk matriseformel lagt inn i A19) | ||||
| =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 = USANN, så er v1 = n – df og v2 = df.) FFORDELING-funksjonen – med syntaksen FFORDELING(F,v1,v2) – returnerer sannsynligheten for en høyere F-verdi som har oppstått 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 known_y og known_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.