LINREGR, funktionen

Bemærk!: Vi vil gerne give dig den mest opdaterede hjælp, så hurtigt vi kan, på dit eget sprog. Denne side er oversat ved hjælp af automatisering og kan indeholde grammatiske fejl og unøjagtigheder. Det er vores hensigt, at dette indhold skal være nyttigt for dig. Vil du fortælle os, om oplysningerne var nyttige for dig, nederst på denne side? Her er artiklen på engelsk så du kan sammenligne.

I denne artikel beskrives formelsyntaksen for og brugen af funktionen LINREGR i Microsoft Excel. Du kan finde links til flere oplysninger om oprettelse af diagrammer og udførelse af en regressionsanalyse i afsnittet Se også.

Beskrivelse

Funktionen LINREGR beregner en linjes stikprøvefunktioner ved at bruge de mindste kvadraters metode til beregning af den lige linje med den bedste tilnærmelse til dataene, og returnerer derefter en matrix, der beskriver linjen. Du kan også kombinere funktionen LINREGR med andre funktioner for at beregne statistikken for andre typer modeller, der er lineære, i de ukendte parametre, herunder polynomiske, logaritmiske og eksponentielle serier og potensserier. Da denne funktion returnerer en matrix med værdier, skal den angives som en matrixformel. Vejledningen følger eksemplerne i denne artikel.

Ligningen for linjen er:

y = mx + b

– eller –

y = m1x1 + m2x2 + ... + b

Hvis der er flere intervaller for x-værdier, hvor de afhængige y-værdier er en funktion af de uafhængige x-værdier. M-værdierne er koefficienter svarende til hver x-værdi, og b er en konstant værdi. Bemærk, at y, x og m kan være matrixer. Den matrix, funktionen LINREGR returnerer, er {mn;mn-1;...;m1;b}. LINREGR kan også returnere andre regressionsspecifikke stikprøvefunktioner.

Syntaks

LINREGR(kendte_y'er;[kendte_x'er];[konstant];[statistik])

Syntaksen for funktionen LINREGR har følgende argumenter:

Syntaks

  • kendte_y'er Påkrævet. Det sæt y-værdier, som du eventuelt allerede kender fra forholdet y = mx + b.

    • Hvis området med kendte_y'er findes i en enkelt kolonne, fortolkes hver kolonne med kendte_x'er som en separat variabel.

    • Hvis området med kendte_y'er er placeret i en enkelt række, fortolkes hver række med kendte_x'er som en separat variabel.

  • kendte_x'er Valgfrit. Et sæt x-værdier, som du eventuelt allerede kender fra forholdet y = mx + b.

    • Området af kendte_x'er kan indeholde et eller flere sæt variabler. Hvis der kun anvendes én variabel, kan kendte_y'er og kendte_x'er være et interval af en hvilken som helst form, så længe de har samme dimensioner. Hvis der bruges mere end én variabel, skal kendte_y'er være en vektor (dvs. et område med en højde på én række eller en bredde på én kolonne).

    • Hvis kendte_x'er udelades, antages matrixen {1;2;3;...}, der har samme størrelse som kendte_y'er.

  • konstant Valgfrit. En logisk værdi, der angiver, om konstanten b skal være lig med 0.

    • Hvis konstant er SAND eller udelades, beregnes b på normal vis.

    • Hvis konstant er FALSK, defineres b som lig med 0, og m-værdierne justeres til y = mx.

  • statistik Valgfrit. En logisk værdi, der angiver, om der skal returneres regressionsspecifikke stikprøvefunktioner.

    • Hvis statistik er SAND, returnerer LINREGR den øvrige regressionsstatistik. Som et resultat heraf er den returnerede matrix {mn;mn-1;...;m1;b\sfn;sfn-1;...;sf1;sfb\r2;sfy\F;fg\ksreg;ksrest}.

    • Hvis statistik er FALSK eller udelades, returnerer LINREGR kun m-koefficienterne og konstanten b.

      De andre regressionsspecifikke stikprøvefunktioner er som følger:

Statistik

Beskrivelse

sf1;sf2;...;sfn

Standardfejlværdier for koefficienterne m1;m2;...;mn.

sfb

Standardfejlværdien for konstanten b (sfb = #I/T, når konstant er FALSK).

r2

Determinationskoefficienten. Sammenligner estimerede og faktiske y-værdier og værdier mellem 0 og 1. Hvis værdien er 1, er korrelationen perfekt i stikprøven, og der er ingen forskel mellem den estimerede y-værdi og den faktiske y-værdi. Hvis determinationskoefficienten i det andet grænsetilfælde er 0, kan regressionsligningen ikke anvendes til at beregne en y-værdi. Oplysninger om beregning af r2 finder du under "Bemærk!" nedenfor.

sfy

Standardfejlen for estimerede y-værdier.

F

Stikprøvefunktionen F eller den F-observerede værdi. Brug stikprøvefunktionen F til at afgøre, om det observerede forhold mellem de afhængige og uafhængige variabler er en tilfældighed.

fg

Frihedsgraderne. Brug frihedsgraderne til at finde de F-kritiske værdier i en statistisk tabel. Sammenlign de fundne værdier i tabellen med stikprøvefunktionen F, som returneres af LINREGR, for at bestemme et signifikansniveau for modellen. Oplysninger om beregning af fg finder du under "Bemærk!" nedenfor. I Eksempel 4 vises brugen af F og fg.

ksreg

Regressionssummen af kvadrater.

ksrest

Restsummen af kvadrater. Oplysninger om beregning af ksreg and ksrest finder du under "Bemærk!" nedenfor.

I følgende illustration vises rækkefølgen, hvori de andre regressionsspecifikke stikprøvefunktioner returneres:

Regneark

Bemærkninger

  • Du kan beskrive en vilkårlig ret linje med hældningskoefficienten og y-skæringspunktet:

    Hældningskoefficient (m):
    Hvis du ønsker at finde en linjes hældningskoefficient, der ofte skrives som m, skal du finde to punkter på linjen (x1,y1) og (x2,y2). Hældningskoefficienten er lig med (y2 - y1)/(x2 - x1).

    Y-skæringspunktet (b):
    Y-skæringspunktet for en linje, der ofte skrives som b, er y-værdien ved det punkt, hvor linjen krydser y-aksen.

    Ligningen for en ret linje er y = mx + b. Når du kender værdien af m og b, kan du beregne et vilkårligt punkt på linjen ved at indsætte y- eller x-værdien i ligningen. Du kan også bruge funktionen TENDENS.

  • Når der kun er én uafhængig x-variabel, kan du udregne hældningskoefficienten og værdien for y-skæringspunktet direkte med følgende formler:

    Hældningskoefficient:
    =INDEKS(LINREGR(kendte_y'er;kendte_x'er);1)

    Y-skæringspunkt:
    =INDEKS(LINREGR(kendte_y'er;kendte_x'er);2)

  • Linjens nøjagtighed, der er beregnet med funktionen LINREGR, afhænger af spredningsgraden for de anvendte data. Jo mere lineære dataene er, jo mere nøjagtig er LINREGR-modellen. LINREGR bruger de mindste kvadraters metode til at bestemme den bedste tilnærmelse til dataene, og returnerer derefter en matrix, der beskriver linjen. Når der kun er én uafhængig x-variabel, baseres beregningerne for m og b på følgende formler:

    Ligning

    Ligning

    hvor x og y er stikprøvernes middelværdi, dvs. x = MIDDEL(kendte_x'er) og y = MIDDEL(kendte_y'er)).

  • Linje-og kurve tilpasningsfunktionerne LINREGR og LOGREGR kan beregne den bedste, lige linje eller eksponentiel kurve, der passer til dine data. Du skal dog beslutte, hvilket af de to resultater, der bedst passer til dine data. Du kan beregne tendens (kendte_y'er; kendte_x'er) for en lige linje eller vækst (kendte_y'er; kendte_x'er) for en eksponentiel kurve. Disse funktioner uden argumentet nye_x'er returnerer en matrix af y-værdier, der er estimeret langs den pågældende linje eller kurve ved dine faktiske datapunkter. Du kan derefter sammenligne de forventede værdier med de faktiske værdier. Det kan være en god ide at lave en diagram for at lave en visuel sammenligning.

  • Ved regressionsanalyse beregnes for hvert punkt den kvadrerede forskel mellem den skønnede y-værdi for det pågældende punkt og den faktiske y-værdi. Summen af disse kvadrerede forskelle kaldes kvadraternes restsum, ksrest. Derefter beregnes den totale sum af kvadrater, sstotal. Når argumentet konstant = SAND eller er udeladt, er den totale sum af kvadrater lig med summen af de kvadrerede forskelle mellem de faktiske y-værdier og gennemsnitsværdien for y-værdierne. Når argumentet konstant = FALSK, er den totale sum af kvadrater lig med summen af kvadrater af de faktiske y-værdier (uden at trække den gennemsnitlige y-værdi fra hver individuel y-værdi). Derefter kan regressionssummen af kvadrater, ksreg, findes ud fra: ksreg = sstotal - ksrest. Jo mindre restsummen af kvadrater er i forhold til den totale sum af kvadrater, jo større er værdien for determinationskoefficienten r2, der angiver, hvor godt ligningen i regressionsanalysen beskriver forholdet mellem variablerne. Værdien af r2 er lig med ssreg/sstotal.

  • I nogle tilfælde har en eller flere af X-kolonnerne (antag, at Y’er og X’er er i kolonner) ingen ekstra skønnet værdi ved tilstedeværelse af de andre X-kolonner. Med andre ord kan udeladelse af en eller flere X-kolonner føre til skønnede Y-værdier, der er lige præcise. I dette tilfælde skal disse redundante X-kolonner udelades fra regressionsmodellen. Dette fænomen kaldes “kollinearitet”, fordi eventuelle redundante X-kolonner kan udtrykkes som en sum af multipler af de ikke-redundante X-kolonner. Funktionen LINREGR kontrollerer, om der er kollinearitet og fjerner eventuelle redundante X-kolonner fra regressionsmodellen, når de identificeres. Fjernede X-kolonner kan genkendes i LINREGR-output, derved at de har både 0-koefficienter og 0 se-værdier. Hvis en eller flere kolonner fjernes som redundante, berøres fg, fordi fg afhænger af antallet af X-kolonner, der faktisk bruges til skønsformål. Yderligere oplysninger om beregning af fg findes i Eksempel 4 nedenfor. Hvis fg ændres, fordi redundante X-kolonner fjernes, berøres værdierne af sey og F også. Kollinearitet bør i praksis være relativ sjælden. Der er dog ét tilfælde, hvor det mere sandsynligt kan forekomme, nemlig når nogle X-kolonner kun indeholder 0- og 1-værdier som indikatorer for, om et individ i et eksperiment er medlem af en bestemt gruppe eller ej. Hvis konstant = SAND eller udeladt, indsætter funktionen LINREGR effektivt en ekstra X-kolonne for alle 1’er for at beregne skæringspunktet. Hvis du har en kolonne med et 1 for hvert individ, hvis det er hankøn, og 0 hvis ikke, og du også har en kolonne med 1 for hvert individ, hvis det er hunkøn, og 0 hvis ikke, er den sidste kolonne redundant, fordi posterne i den kan udledes ved at trække posten i kolonnen med “hankønsindikator” fra posten i den ekstra kolonne med alle 1’er tilføjet af funktionen LINREGR.

  • Værdien af FG beregnes på følgende måde, når der ikke fjernes X-kolonner fra modellen på grund af collinearity: Hvis der er k-kolonner af kendte_x'er og konstant = sand eller udeladt, FG = n – k – 1. Hvis konstant = falsk, DF = n-k. I begge tilfælde øger hver X-kolonne, der blev fjernet på grund af collinearity værdien af DF ved 1.

  • Formler, der returnerer matrixer, skal indtastes som matrixformler.

    Bemærk!: Du kan ikke oprette matrixformler i Excel Online.

  • Når du angiver en matrixkonstant (som f. eks. kendte_x'er) som argument, skal du bruge kommaer til at adskille værdier, der er indeholdt i den samme række og semikoloner for at adskille rækker. Separatortegn kan være forskellige, afhængigt af dine internationale indstillinger.

  • Bemærk, at y-værdierne, der er skønsmæssigt beregnet med regressionsligningen, eventuelt ikke er gyldige, hvis de ligger uden for intervallet for de y-værdier, du har anvendt til at udregne ligningen.

  • Den underliggende algoritme, der bruges i funktionen LINREGR , er anderledes end den underliggende algoritme, der bruges i funktionerne stigning og skæring . Forskellen mellem disse algoritmer kan føre til forskellige resultater, når data er ubestemte og ligger på samme linje. Hvis datapunkterne i argumentet kendte_y'er er 0, og datapunkterne i argumentet kendte_x'er er 1:

    • LINREGR returnerer en værdi på 0. Algoritmen for funktionen LINREGR er designet til at returnere rimelige resultater for data på samme linje, og i dette tilfælde kan der findes mindst ét svar.

    • Hældning og skæring returnerer en #DIV/0! som fejl. Algoritmen for funktionerne hældning og skæring er designet til kun at lede efter ét svar, og i dette tilfælde kan der være mere end ét svar.

  • Ud over at bruge LOGREGR til at beregne statistik for andre regressionstyper kan du bruge LINREGR til at beregne en række andre regressionstyper ved at indtaste funktionerne for x- og y-variablerne som x- og y-serien for LINEST, f.eks. følgende formel:

    =LINREGR(yværdier; xværdier^KOLONNE($A:$C))

    fungerer, hvis du har en enkelt kolonne med y-værdier og en enkelt kolonne med x-værdier til at beregne den kubiske (polynomisk af tredje orden) tilnærmelse af formlen:

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

    Du kan justere denne formel for at beregne andre regressionstyper, men i nogle tilfælde kræver det, at outputværdierne og anden statistik justeres.

  • Den værdi for F-testen, der returneres af funktionen LINREGR, adskiller sig fra den værdi for F-testen, der returneres af funktionen FTEST. LINREGR returnerer F-statistikken, mens FTEST returnerer sandsynligheden.

Eksempler

Eksempel 1 – Hældningskoefficient og y-skæringspunkt

Kopiér eksempeldataene i følgende tabel, og sæt dem ind i celle A1 i et nyt Excel-regneark. For at få formlerne til at vise resultater skal du markere dem, trykke på F2 og derefter trykke på Enter. Hvis der er brug for det, kan du justere bredden på kolonnerne, så du kan se alle dataene.

Kendt y

Kendt x

1

0

9

4

5

2

7

3

Resultat (hældingskoefficient)

Resultat (y-skæringspunkt)

2

1

Formel (matrixformel i cellerne A7:B7)

=LINREGR(A2:A5;B2:B5;FALSK)

Eksempel 2 – Simpel lineær regression

Kopiér eksempeldataene i følgende tabel, og sæt dem ind i celle A1 i et nyt Excel-regneark. For at få formlerne til at vise resultater skal du markere dem, trykke på F2 og derefter trykke på Enter. Hvis der er brug for det, kan du justere bredden på kolonnerne, så du kan se alle dataene.

Måned

Salg

1

kr. 31.000

2

kr. 45.000

3

kr. 44.000

4

kr. 54.000

5

kr. 75.000

6

kr. 81.000

Formel

Resultat

=SUM(LINREGR(B1:B6; A1:A6)*{9;1})

DKK 11.000

Beregner det anslåede salg i niende måned på basis af salget i måned 1 til og med 6.

Eksempel 3 – Flerlineær regression

Kopiér eksempeldataene i følgende tabel, og sæt dem ind i celle A1 i et nyt Excel-regneark. For at få formlerne til at vise resultater skal du markere dem, trykke på F2 og derefter trykke på Enter. Hvis der er brug for det, kan du justere bredden på kolonnerne, så du kan se alle dataene.

Gulvareal (x1)

Kontorer (x2)

Indgange (x3)

Alder (x4)

Ejendomsværdi (y)

2310

2

2

20

DKK 142.000

2333

2

2

12

DKK 144.000

2356

3

1,5

33

DKK 151.000

2379

3

2

43

DKK 150.000

2402

2

3

53

DKK 139.000

2425

4

2

s

DKK 169.000

2448

2

1,5

99

DKK 126.000

2471

2

2

34

DKK 142.900

2494

3

3

s

DKK 163.000

2517

4

4

55

DKK 169.000

2540

2

3

22

DKK 149.000

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Formel (matrixformel angivet i A14:A18)

=LINREGR(E2:E12;A2:D12;SAND;SAND)

Eksempel 4 – Brug af statistikkerne F og r2

I det foregående eksempel er determinationskoefficienten r2 lig med 0,99676 (se celle A17 i returdataene for funktionen LINREGR), hvilket angiver en tæt forbindelse mellem de uafhængige variabler og salgsprisen. Du kan bruge stikprøvefunktionen F til at afgøre, om disse resultater med så høj en r2-værdi er tilfældige.

Antag, at der faktisk ikke er en forbindelse mellem variablerne, men at du har udtaget en usædvanlig stikprøve på 11 kontorbygninger, som bevirker, at den statistiske analyse viser en nær forbindelse. Sandsynligheden for, at du fejlagtigt konkluderer, at der foreligger en forbindelse, kaldes Alpha.

Værdierne F og fg i output fra funktionen LINREGR kan bruges til at vurdere sandsynligheden for, at en højere F-værdi opstår tilfældigt. F kan sammenlignes med kritiske værdier i udgivne F-fordelingstabeller, eller funktionen FFORDELING i Excel kan bruges til at beregne sandsynligheden for, at en større F-værdi opstår tilfældigt. Den passende F-fordeling har v1 og v2 frihedsgrader. Hvis n er antallet af datapunkter, og konstant = SAND eller er udeladt, er v1 = n – fg – 1 og v2 = fg. (Hvis konstant = FALSK, er v1 = n – fg og v2 = fg). Funktionen FFORDELING  med syntaksen FFORDELING(F,v1,v2)  returnerer sandsynligheden for, at en større F-værdi opstår tilfældigt. I eksempel 4 er fg = 6 (celle B18) og F = 459,753674 (celle A18).

Hvis du antager en alpha-værdi på 0,05, v1 = 11 – 6 – 1 = 4 og v2 = 6, er det kritiske niveau for F 4,53. Da F = 459,753674 er meget større end 4,53, er det meget usandsynligt, at en F (Med Alpha = 0,05 er hypotesen, at der ikke er nogen relation mellem kendte_y'er og kendte_x'er , at de afvises, når F overstiger det vigtige niveau, 4,53.) Du kan bruge funktionen FFORDELING i Excel til at få sandsynligheden for, at en F-værdi, der er den høje, er en chance. F. eks. FFORDELING(459,753674, 4, 6) = 1.37 e-7, en særdeles lille sandsynlighed. Du kan indgå, enten ved at finde det kritiske niveau for F i en tabel eller ved at bruge funktionen FFORDELING , at regressionsligningen er nyttig til at forudsige den skønsmæssige værdi af kontorbygninger i dette område. Husk, at det er afgørende for at bruge de korrekte værdier for v1 og v2, der blev beregnet i det foregående afsnit.

Eksempel 5 – Beregning af t-statistikken

En anden hypotesetest vil afgøre, om hver stignings koefficient er nyttig i vurdering af den skønsmæssige værdi af en Office-bygning i eksempel 3. Hvis du for eksempel vil afprøve alders koefficienten for Statistisk signifikans, division-234,24 (alders baseret hældningskoefficient) med 13,268 (anslået standardfejl i celle A15). Følgende er den t-observerede værdi:

t = m4 ÷ se4 = -234,24 ÷ 13,268 = -17,7

Hvis den absolutte værdi af t er tilstrækkelig stor, kan det konkluderes, at hældningskoefficienten er nyttig til at beregne den skønsmæssige ejendomsværdi for en kontorbygning i Eksempel 3. I følgende tabel vises de absolutte værdier af de 4 t-observerede værdier.

Hvis du hører en tabel i en statistik-manual, vil du opdage, at t-kritisk, to-sidet, med 6 frihedsgrader og Alpha = 0,05 er 2,447. Denne kritiske værdi kan også findes ved hjælp af funktionen TINV i Excel. TINV (0,05, 6) = 2,447. Da den absolutte værdi af t (17,7) er større end 2,447, er alder en vigtig variabel ved beregning af den skønsmæssige værdi for en Office-bygning. Hver af de andre uafhængige variabler kan undersøges for statistisk betydning på samme måde. Følgende er de t-observerede værdier for hver enkelt af de uafhængige variabler.

Variabel

t-observeret værdi

Gulvareal

5,1

Antal kontorer

31,3

Antal indgange

4,8

Alder

17,7

Disse værdier har alle en absolut værdi, der er større end 2,447, og alle anvendte variabler i regressionsligningen kan derfor med fordel anvendes til at beregne den skønsmæssige ejendomsværdi for kontorbygninger i dette område.

Udvid dine Office-færdigheder
Gå på opdagelse i kurser
Få nye funktioner først
Bliv Office Insider

Var disse oplysninger nyttige?

Tak for din feedback!

Tak for din feedback! Det lyder, som om det vil kunne hjælpe, hvis du bliver sat i forbindelse med en af vores Office-supportteknikere.

×