Last ned eksemplet på arbeidsbok
Denne opplæringen viser hvordan du bruker Excel LINEST -funksjon i Excel for å beregne statistikk om en trendlinje.
LINEST Funksjon Oversikt
LINEST -funksjonen Beregner statistikk om en trendlinje montert på kjente datapunkter ved bruk av metoden for minst kvadrater.
For å bruke LINEST Excel -regnearkfunksjonen, velg en celle og skriv:
(Legg merke til hvordan formelinngangene vises)
LINEST -funksjon Syntaks og innganger
| 1 | = LINEST (kjent_ys, kjent_xs, const, statistikk) |
kjente_y’er - En rekke kjente Y -verdier.
kjente_x - En rekke kjente X -verdier.
konst - VALGFRITT. Logisk verdi som indikerer om B skal beregnes (skjæringspunktet i y = mx + b) ved bruk av metoden for minst kvadrater (TRUE Or Omitted) eller manuelt å angi B = 0 (FALSE).
statistikk - VALGFRITT. Returner tilleggsstatistikk (TRUE) eller bare returner m (stigning) og b (skjær opp) (FALSE eller Utelatt)
Hva er LINEST?
LINEST -funksjonen i Excel er en funksjon som brukes til å generere regresjonsstatistikk for en lineær regresjonsmodell. LINEST er en matriseformel og kan brukes alene eller sammen med andre funksjoner for å beregne spesifikk statistikk om modellen.
Lineær regresjon er en metode i statistikk som brukes til å forutsi data etter en rett linje ved hjelp av kjente data. Regresjon brukes til å forutsi verdier som salgsvekst, lagerbehov eller enkle markedstrender.
LINEST er som FORECAST ved at den oppnår et lignende utfall, men med langt mer informasjon om regresjonsmodellen din samt muligheten til å passe mer enn en uavhengig variabel.
Anta at jeg har en tabell med data med x og y verdier hvor x er den uavhengige variabelen og y er den avhengige variabelen:
Jeg vil vite hva regresjonsligningen for dataene ovenfor er. Bruke LINEST:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Y-skjæringsverdien her tilsvarer 0, i vitenskapelig notasjon.
Linjens ligning er y = 2x + 0. Vær oppmerksom på at LINEST returnerer både skråningen og avskjæringen av linjen. For å returnere begge verdiene må formelen legges inn som en matriseformel. Mer om matriseformler senere.
Slik bruker du LINEST
LINEST -funksjonen tar fire argumenter:
| 1 | = LINEST (kjente_y’er, kjente_x’er, const, statistikk) |
Hvor,
| Argument | Beskrivelse |
| kjente_y’er og kjente_x | Er den x og y data i datatabellen |
| konst | TRUE/FALSE alternativet for om y-avskjæringen skal tvinges til 0 eller beregnes normalt |
| statistikk | SANN/FALSK alternativ om ytterligere regresjonsstatistikk skal returneres |
Ved å bruke vårt første eksempel, er funksjonen skrevet som:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Når statistikk alternativet er satt til TRUE, er organisasjonen av regresjonsstatistikken som følger:
Du lurer kanskje på hva hver variabel betyr.
| Statistikk | Beskrivelse |
| mn | Hellingskoeffisienter for x variabler |
| b | y-avskjæring |
| sen | Standard feil for hver skråningskoeffisient |
| seb | Standard feil for y-avskjæringen |
| r2 | Determisjonskoeffisient |
| sey | Standard feil for y anslag |
| F | F -statistikken (for å avgjøre om variablers forhold oppstår ved en tilfeldighet) |
| df | Grader av frihet |
| ssreg | Regresjonssum av kvadrater |
| ssrest | Restsummen av firkanter |
Hovedstatistikken å forstå er skråningskoeffisientene, y-avskjæringen og bestemmelseskoeffisienten eller r2 modellens verdi.
Ved å bruke eksemplet ovenfor og velge TRUE for statistikk parameter:
De markerte cellene viser skråningen = 2, skjæringspunktet = 0 og r2 = 1.
R2 verdi er en indikator på styrken til modellens korrelasjon. Det kan betraktes som en indikator på passform. En lav r2 verdi ville bety en dårlig korrelasjon mellom dine avhengige og uavhengige variabler, og det motsatte gjelder for høy r2 verdier, med r2 = 1 passer perfekt.
I utgivelser etter januar 2022 av Excel i Microsoft 365 (tidligere Office 365) har dynamiske matriser endret måten matriseformler blir evaluert på. Det er ikke lenger nødvendig å bruke CTRL + SKIFT + ENTER eller markere celleområdet matrisen vil ta opp. Bare skriv inn formelen og klikk på enter, og de resulterende cellene vil "søle ut" i matrisen.
For resten av denne artikkelen vil vi referere til bruk av LINEST med hensyn til dynamiske matriser i Microsoft 365 Excel.
Prognoser med LINEST (enkel regresjon)
Ved å kombinere funksjonene LINEST og SUM kan du forutsi verdien av en avhengig variabel y, gitt kjent x og y data. Nedenfor er et eksempel som viser hva y verdien vil være når x = 14.
| 1 | = SUMME (LINEST (C3: C7, B3: B7)*{14,1}) |
Modellen kommer i formen y = mx + b. Dette er det samme som y = a+ bx, bare en annen måte å representere ligningen på. Et tips å huske på for lineære ligninger er variabelen ved siden av x er alltid skråningen, og variabelen etter et pluss- eller minustegn er alltid skjæringspunktet, uavhengig av bokstavene som brukes i ligningen.
Ved å bruke formelen: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) returneres resultatet av 28. Siden dette er et enkelt resultat, er det ikke nødvendig å angi som en matrise.
Halen på formelen ovenfor *{14,1} angir den uavhengige variabelen som skal brukes til å forutsi den avhengige variabelen, i dette tilfellet 14.
Vi kan kontrollere dette ved å skrive x = 14 i linjens ligning, y = 2x + 0.
Prognoser med LINEST (multiple lineær regresjon)
Følgende datatabell kommer fra LINEST -siden for Microsoft Support -nettstedet.
I noen tilfeller er det mer enn en uavhengig variabel som bør vurderes når du oppretter en lineær regresjonsmodell. Dette kalles multiple lineær regresjon (dvs. flere uavhengige variabler). Hvis jeg vil estimere kostnaden for et kontorbygg, ville ting som gulvplass, antall bygningsinnganger, bygningsalder og antall kontorer alle være en del av ligningen. La oss se et eksempel.
Når vi skriver den LINEST formelen i celle G29 og kjører den, får vi:
| 1 | = LINEST (E3: E13, A3: D13, TRUE, TRUE) |
Modellen kommer i formen:
Husk at LINEST result array er i motsatt rekkefølge fra ligningen. I eksemplet ovenfor er 52 317,8 vårt skjæringspunkt, b og 27,6 er vårt m1 eller stigningsverdien for gulvplassvariabelen, x1.
Ved å bruke LINEST -funksjonen med de oppgitte dataene, er regresjonsmodellen vår:
Med en r2 verdi på 0,997, noe som indikerer en sterk eller sterkt korrelert modell. Ved å bruke modellen kan du nå forutsi hva den vurderte verdien av et kontorbygg vil være basert på en hvilken som helst kombinasjon av de ovennevnte uavhengige variablene.
LINEST tips
- Sørg for at du har den mest oppdaterte versjonen av Microsoft 365 for å bruke LINEST med dynamiske matriser. Du må kanskje aktivere Office Insider Current Channel (Preview) for å bruke dynamiske matrisefunksjoner. På kontosiden:
- Hvis du bruker en ikke-Microsoft 365-versjon, må du bruke den eldre CTRL + SKIFT + ENTER (CSE) metoden for å evaluere matriseformler.
- Hvis du bruker den eldre metoden, er antallet kolonner som skal fremheves når du angir en LINEST matrisefunksjon alltid antallet x variabler i dataene pluss 1. Antall rader du skal velge for matrisen er 5.
- Hvis du vil dele den dynamiske array-aktiverte versjonen av Excel med noen som bruker en ikke-Microsoft 365-versjon, kan du bruke den eldre CSE-metoden for å unngå kompatibilitetsproblemer.
Interessert i mer prognoser?
Se våre andre artikler om prognoser med eksponentiell utjevning, TREND, VEKST og LOGEST -funksjoner.
LINEST funksjon i Google Regneark
LINEST -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel.
LINEST Eksempler i VBA
Du kan også bruke LINEST -funksjonen i VBA. Type:application.worksheetfunction.linest (kjent_ys, kjent_xs, const, statistikk)
Utfører følgende VBA -uttalelse
| 1 | Range ("D2") = Application.WorksheetFunction.LinEst (Range ("A2: A8"), Range ("B2: B8")) |
vil gi følgende resultater
For funksjonsargumentene (kjente_y’er, etc.) kan du enten skrive dem inn direkte i funksjonen, eller definere variabler som skal brukes i stedet.
Gå tilbake til listen over alle funksjoner i Excel
