Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger Excel LINEST funktion i Excel for at beregne statistik om en trendlinje.
LINEST Funktionsoversigt
LINEST -funktionen Beregner statistik om en trendlinie, der er monteret på kendte datapunkter ved hjælp af metoden med mindst kvadrater.
Hvis du vil bruge LINEST Excel -regnearksfunktionen, skal du markere en celle og skrive:
(Bemærk hvordan formelindgangene vises)
LINEST funktion Syntaks og input
| 1 | = LINEST (kendt_ys, kendt_xs, const, statistik) |
kendte_y’er - En række kendte Y -værdier.
kendte_x’er - En række kendte X -værdier.
konst - VALGFRIT. Logisk værdi, der angiver, om B skal beregnes (skæringspunktet i y = mx + b) ved hjælp af metoden med mindst kvadrater (TRUE Or Omitted) eller manuelt at indstille B = 0 (FALSE).
statistik - VALGFRIT. Returner yderligere statistik (SAND) eller returner kun m (hældning) og b (skæringspunkt) (FALSK eller udeladt)
Hvad er LINEST?
LINEST -funktionen i Excel er en funktion, der bruges til at generere regressionsstatistik for en lineær regressionsmodel. LINEST er en matrixformel og kan bruges alene eller sammen med andre funktioner til at beregne specifik statistik om modellen.
Lineær regression er en metode i statistik, der bruges til at forudsige data efter en lige linje ved hjælp af kendte data. Regression bruges til at forudsige værdier som salgsvækst, lagerbehov eller simple markedstendenser.
LINEST er ligesom FORECAST, idet den opnår et lignende resultat, men med langt flere oplysninger om din regressionsmodel samt mulighed for at passe mere end én uafhængig variabel.
Antag, at jeg har en datatabel med x og y værdier hvor x er den uafhængige variabel og y er den afhængige variabel:
Jeg vil vide, hvad regressionsligningen for ovenstående data er. Brug af LINEST:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Y-skæringsværdien her svarer til 0 i videnskabelig notation.
Linjens ligning er y = 2x + 0. Bemærk, at LINEST returnerer begge skråningens og skæringspunktet. For at returnere begge værdier skal formlen indtastes som en matrixformel. Mere om matrixformler senere.
Sådan bruges LINEST
Funktionen LINEST tager fire argumenter:
| 1 | = LINEST (kendt_y’er, kendt_x’er, const, statistik) |
Hvor,
| Argument | Beskrivelse |
| kendte_y’er og kendte_x’er | Er x og y data i din datatabel |
| konst | SAND/FALSK mulighed for, om y-skæringen skal tvinges til 0 eller beregnes normalt |
| statistik | SAND/FALSK mulighed for, om yderligere regressionsstatistikker skal returneres |
Ved hjælp af vores første eksempel er funktionen skrevet som:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Når statistik er indstillet til SAND, er organisationen af regressionsstatistikkerne som følger:
Du undrer dig måske over, hvad hver variabel betyder.
| Statistik | Beskrivelse |
| mn | Hældningskoefficienter for x variabler |
| b | y-aflytning |
| sen | Standardfejl for hver hældningskoefficient |
| seb | Standardfejl for y-skæringen |
| r2 | Bestemmelseskoefficient |
| sey | Standardfejl for y skøn |
| F | F -statistikken (for at afgøre, om variablenes forhold opstår tilfældigt) |
| df | Grader af frihed |
| ssreg | Regressionssum af kvadrater |
| ssrest | Resterende sum af firkanter |
De vigtigste statistikker, der skal forstås, er hældningskoefficienterne, y-interceptet og bestemmelseskoefficienten eller r2 værdien af modellen.
Brug ovenstående eksempel og vælg SAND for statistik parameter:
De fremhævede celler viser hældningen = 2, skæringspunktet = 0 og r2 = 1.
R2 værdi er en indikator for styrken af modelens korrelation. Det kan betragtes som en indikator på pasform. En lav r2 værdi ville betyde en dårlig korrelation mellem dine afhængige og uafhængige variabler, og det modsatte gælder for høj r2 værdier, med r2 = 1 passer perfekt.
I udgivelser efter januar 2022 af Excel i Microsoft 365 (tidligere Office 365) har dynamiske arrays ændret måden, hvorpå matrixformler evalueres. Det er ikke længere nødvendigt at bruge CTRL + SKIFT + ENTER eller markere det område af celler, arrayet vil optage. Du skal blot indtaste formlen og klikke på enter, og de resulterende celler vil "spilde" ud i arrayet.
I resten af denne artikel refererer vi til brug af LINEST med hensyn til dynamiske arrays i Microsoft 365 Excel.
Prognoser med LINEST (simpel regression)
Kombinationen af LINEST- og SUM -funktionerne kan bruges til at forudsige værdien af en afhængig variabel y, givet kendt x og y data. Nedenfor er et eksempel, der viser, hvad y værdien vil være når x = 14.
| 1 | = SUM (LINEST (C3: C7, B3: B7)*{14,1}) |
Modellen kommer i formen y = mx + b. Dette er det samme som y = a+ bx, bare en anden måde at repræsentere ligningen på. Et tip at huske på for lineære ligninger er variablen ved siden af x er altid hældningen, og variablen efter et plus- eller minustegn er altid skæringspunktet, uanset hvilke bogstaver der bruges i ligningen.
Brug af formlen: = SUM (LINEST (C3: C7, B3: B7)*{14,1}) returnerer resultatet af 28. Da dette er et enkelt resultat, er det ikke nødvendigt at indtaste som en matrix.
Haleenden af ovenstående formel *{14,1} angiver den uafhængige variabel, der skal bruges til at forudsige den afhængige variabel, i dette tilfælde 14.
Vi kan kontrollere dette ved at indtaste x = 14 i linjens ligning, y = 2x + 0.
Prognoser med LINEST (multipel lineær regression)
Følgende datatabel stammer fra Microsoft Support -webstedets LINEST -side.
I nogle tilfælde er der mere end en uafhængig variabel, som bør overvejes, når der oprettes en lineær regressionsmodel. Dette kaldes multiple lineær regression (dvs. flere uafhængige variabler). Hvis jeg vil estimere omkostningerne ved en kontorbygning, ville ting som gulvplads, antal bygningsindgange, bygningens alder og antallet af kontorer alle være en del af ligningen. Lad os se et eksempel.
Når vi skriver den LINEST formel i celle G29 og udfører den, får vi:
| 1 | = LINEST (E3: E13, A3: D13, TRUE, TRUE) |
Modellen kommer i formen:
Husk, at LINEST resultat array er i omvendt rækkefølge fra ligningen. I ovenstående eksempel er 52,317,8 vores afsnit, b, og 27,6 er vores m1 eller hældningsværdien for variablen Gulvareal, x1.
Vores regressionsmodel bruger LINEST -funktionen med de leverede data:
Med en r2 værdi på 0,997, hvilket indikerer en stærk eller stærkt korreleret model. Ved hjælp af modellen kan du nu forudsige, hvad den vurderede værdi af en kontorbygning vil være baseret på enhver kombination af ovenstående uafhængige variabler.
LINEST tips
- Sørg for, at du har den mest opdaterede version af Microsoft 365 til at bruge LINEST med dynamiske arrays. Du skal muligvis aktivere Office Insider Current Channel (Preview) for at bruge dynamiske array -funktioner. På kontosiden:
- Hvis du bruger en ikke-Microsoft 365-version, skal du bruge den ældre CTRL + SHIFT + ENTER (CSE) -metode til at evaluere matrixformler.
- Hvis du bruger den ældre metode, er antallet af kolonner, der skal fremhæves, når du indtaster en LINEST array -funktion altid antallet af x variabler i dine data plus 1. Antallet af rækker, der skal vælges til arrayet, er 5.
- Hvis du vil dele din dynamiske array-aktiverede version af Excel med en person, der bruger en ikke-Microsoft 365-version, skal du bruge den ældre CSE-metode til at undgå kompatibilitetsproblemer.
Interesseret i flere prognoser?
Se vores andre artikler om prognoser med eksponentiel udjævning, TREND, VÆKST og LOGEST -funktioner.
LINEST funktion i Google Sheets
LINEST -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel.
LINEST Eksempler i VBA
Du kan også bruge funktionen LINEST i VBA. Type:application.worksheetfunction.linest (kendt_ys, kendt_xs, const, statistik)
Udførelse af følgende VBA -erklæring
| 1 | Range ("D2") = Application.WorksheetFunction.LinEst (Range ("A2: A8"), Range ("B2: B8")) |
vil frembringe følgende resultater
For funktionsargumenterne (kendt_y’er osv.) Kan du enten indtaste dem direkte i funktionen eller definere variabler, der skal bruges i stedet.
Tilbage til listen over alle funktioner i Excel
