Ladda ner exempelarbetsboken
Denna handledning visar hur du använder Excel LINEST -funktion i Excel för att beräkna statistik om en trendlinje.
LINEST Funktionsöversikt
LINEST -funktionen Beräknar statistik om en trendlinje som är monterad på kända datapunkter med hjälp av metoden minst kvadrat.
Om du vill använda funktionen FÖRSTA Excel -kalkylblad väljer du en cell och skriver:
(Lägg märke till hur formelinmatningarna ser ut)
LINEST -funktion Syntax och ingångar
| 1 | = LINEST (känd_ys, känd_xs, konstant, statistik) |
kända_y - En rad kända Y -värden.
kända_x - En uppsättning kända X -värden.
konst - VALFRITT. Logiskt värde som anger om B (interceptet i y = mx + b) ska beräknas med hjälp av metoden minst kvadrater (TRUE Or Omitted) eller manuellt ställa in B = 0 (FALSE).
statistik - VALFRITT. Returnera ytterligare statistik (TRUE) eller returnera endast m (lutning) och b (skärning) (FALSE eller Utelämnad)
Vad är LINEST?
LINEST -funktionen i Excel är en funktion som används för att generera regressionsstatistik för en linjär regressionsmodell. LINEST är en matrisformel och kan användas ensam eller tillsammans med andra funktioner för att beräkna specifik statistik om modellen.
Linjär regression är en metod i statistik som används för att förutsäga data efter en rak linje med hjälp av kända data. Regression används för att förutsäga värden som försäljningstillväxt, lagerkrav eller enkla marknadstrender.
LINEST är som PROGNOS genom att det uppnår ett liknande resultat, men med mycket mer information om din regressionsmodell samt möjligheten att passa mer än en oberoende variabel.
Antag att jag har en tabell med data med x och y värderingar var x är den oberoende variabeln och y är den beroende variabeln:
Jag vill veta vad regressionsekvationen för ovanstående data är. Använda LINEST:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
Y-avlyssningsvärdet här är ekvivalent med 0, i vetenskaplig notation.
Ekvationen för raden är y = 2x + 0. Observera att LINEST returnerar både lutningen och avlyssningen av linjen. För att returnera båda värdena måste formeln anges som en matrisformel. Mer om matrisformler senare.
Hur man använder LINEST
LINEST -funktionen tar fyra argument:
| 1 | = LINEST (kända_y, kända_x, konstant, statistik) |
Var,
| Argument | Beskrivning |
| kända_y och kända_x | Är x och y data i din datatabell |
| konst | SANT/FALSK alternativ för om y-avlyssningen ska tvingas till 0 eller beräknas normalt |
| statistik | SANT/FALSK alternativ om ytterligare regressionsstatistik ska returneras |
Med vårt första exempel skrivs funktionen som:
| 1 | = LINEST (B3: B7, C3: C7, TRUE, FALSE) |
När statistik alternativet är satt till TRUE, är regressionsstatistikens organisation följande:
Du kanske undrar vad varje variabel betyder.
| Statistisk | Beskrivning |
| mn | Lutningskoefficienter för x variabler |
| b | y-avlyssning |
| sen | Standardfel för varje lutningskoefficient |
| seb | Standardfel för y-avlyssningen |
| r2 | Determinationskoefficient |
| sey | Standardfel för y uppskatta |
| F | F -statistiken (för att avgöra om variablernas relation inträffar av en slump) |
| df | Grader av frihet |
| ssreg | Regressionssumma av kvadrater |
| ssrest | Återstående summa av rutor |
Den viktigaste statistiken att förstå är lutningskoefficienterna, y-interceptet och bestämningskoefficienten eller r2 modellens värde.
Använd exemplet ovan och välj SANT för statistik parameter:
De markerade cellerna visar lutningen = 2, skärningspunkten = 0 och r2 = 1.
R2 värde är en indikator på styrkan i modellens korrelation. Det kan ses som en indikator på passform. Ett lågt r2 värde skulle innebära en dålig korrelation mellan dina beroende och oberoende variabler, och motsatsen gäller för hög r2 värden, med r2 = 1 passar perfekt.
I versioner efter januari 2022 av Excel i Microsoft 365 (tidigare Office 365) har dynamiska matriser förändrat hur matrisformler utvärderas. Det är inte längre nödvändigt att använda CTRL + SKIFT + ENTER eller markera cellområdet som arrayen kommer att ta upp. Ange bara formeln och klicka på enter så kommer de resulterande cellerna att "spillas ut" i matrisen.
För resten av denna artikel kommer vi att referera med LINEST med avseende på dynamiska matriser i Microsoft 365 Excel.
Prognos med LINEST (enkel regression)
Att kombinera funktionerna LINEST och SUM kan användas för att förutsäga värdet på en beroende variabel y, givet känt x och y data. Nedan följer ett exempel som visar vad y värdet blir när x = 14.
| 1 | = SUMMA (LINEST (C3: C7, B3: B7)*{14,1}) |
Modellen kommer i formen y = mx + b. Detta är detsamma som y = a+ bx, bara ett annat sätt att representera ekvationen. Ett tips att tänka på för linjära ekvationer är variabeln bredvid x är alltid lutningen, och variabeln efter ett plus- eller minustecken är alltid skärningspunkten, oavsett bokstäverna som används i ekvationen.
Med hjälp av formeln: = SUMMA (LINEST (C3: C7, B3: B7)*{14,1}) returneras resultatet av 28. Eftersom detta är ett enda resultat är det inte nödvändigt att ange som en array.
Svansänden på ovanstående formel *{14,1} anger den oberoende variabeln som ska användas för att förutsäga den beroende variabeln, i detta fall 14.
Vi kan kontrollera detta genom att ange x = 14 i linjens ekvation, y = 2x + 0.
Prognos med LINEST (multipel linjär regression)
Följande datatabell kommer från Microsofts supportwebbplats LINEST -sida.
I vissa fall finns det mer än en oberoende variabel som bör beaktas när man skapar en linjär regressionsmodell. Detta kallas multipel linjär regression (dvs. flera oberoende variabler). Om jag vill uppskatta kostnaden för en kontorsbyggnad skulle saker som golvyta, antal entréer, byggnadens ålder och antalet kontor alla vara en del av ekvationen. Låt oss se ett exempel.
När vi skriver den LINÄSTA formeln i cell G29 och kör den får vi:
| 1 | = LINEST (E3: E13, A3: D13, TRUE, TRUE) |
Modellen kommer i formen:
Kom ihåg att LINEST resultatmatris är i omvänd ordning från ekvationen. I exemplet ovan är 52 317,8 vår avlyssning, b och 27,6 är vår m1 eller lutningsvärdet för golvyta variabel, x1.
Med hjälp av LINEST -funktionen med de tillhandahållna uppgifterna är vår regressionsmodell:
Med en r2 värdet 0,997, vilket indikerar en stark eller starkt korrelerad modell. Med hjälp av modellen kan du nu förutsäga vad det uppskattade värdet på en kontorsbyggnad kommer att baseras på vilken kombination som helst av ovanstående oberoende variabler.
LINEST tips
- Se till att du har den mest uppdaterade versionen av Microsoft 365 för att använda LINEST med dynamiska matriser. Du kan behöva aktivera Office Insider Current Channel (Preview) för att kunna använda dynamiska arrayfunktioner. På kontosidan:
- Om du använder en version som inte är Microsoft 365 måste du använda den äldre CTRL + SHIFT + ENTER (CSE) -metoden för att utvärdera matrisformler.
- Om du använder den äldre metoden är antalet kolumner som ska markeras när du anger en LINEST -matrisfunktion alltid antalet x variabler i din data plus 1. Antalet rader att välja för matrisen är 5.
- Om du delar din dynamiska array-aktiverade version av Excel med någon som använder en version som inte är Microsoft 365 använder du den äldre CSE-metoden för att undvika kompatibilitetsproblem.
Intresserad av mer prognoser?
Se våra andra artiklar om prognoser med exponentiell utjämning, TREND, tillväxt och LOGEST -funktioner.
LINEST funktion i Google Kalkylark
LINEST -funktionen fungerar exakt samma sak i Google Kalkylark som i Excel.
LINEST Exempel i VBA
Du kan också använda LINEST -funktionen i VBA. Typ:application.worksheetfunction.linest (known_ys, known_xs, const, stats)
Kör följande VBA -uttalande
| 1 | Range ("D2") = Application.WorksheetFunction.LinEst (Range ("A2: A8"), Range ("B2: B8")) |
kommer att ge följande resultat
För funktionsargumenten (kända_y, etc.) kan du antingen skriva in dem direkt i funktionen eller definiera variabler som ska användas istället.
Återgå till listan över alla funktioner i Excel
