LIJNSCH Functie Excel - Statistieken lineaire regressie

Voorbeeldwerkboek downloaden

Download het voorbeeldwerkboek

Deze tutorial laat zien hoe je de Excel LIJNSCH-functie in Excel om statistieken over een trendlijn te berekenen.

LIJNSCH Functie Overzicht

De LIJNSCH-functie Berekent statistieken over een trendlijn die is aangepast aan bekende gegevenspunten met behulp van de kleinste-kwadratenmethode.

Om de LIJNSCH Excel-werkbladfunctie te gebruiken, selecteert u een cel en typt u:

(Let op hoe de formule-invoer verschijnt)

LIJNSCH-functie Syntaxis en ingangen

1	=LIJNSCH(bekende_ys,bekende_xs,const,statistieken)

bekende_y's - Een array van bekende Y-waarden.

bekende_x's - Een array van bekende X-waarden.

const - OPTIONEEL. Logische waarde die aangeeft of B (het snijpunt in y = mx + b) moet worden berekend met de kleinste-kwadratenmethode (TRUE of weggelaten) of dat B = 0 (FALSE) handmatig moet worden ingesteld.

statistieken - OPTIONEEL. Retourneer aanvullende statistieken (TRUE) of retourneer alleen m (helling) en b (snijpunt) (FALSE of weggelaten)

Wat is LIJNSCH?

De LIJNSCH-functie in Excel is een functie die wordt gebruikt om regressiestatistieken te genereren voor een lineair regressiemodel. LIJNSCH is een matrixformule en kan alleen of met andere functies worden gebruikt om specifieke statistieken over het model te berekenen.

Lineaire regressie is een methode in de statistiek die wordt gebruikt voor het voorspellen van gegevens die een rechte lijn volgen met behulp van bekende gegevens. Regressie wordt gebruikt om waarden zoals omzetgroei, voorraadvereisten of eenvoudige markttrends te voorspellen.

LIJNSCH is als FORECAST omdat het een vergelijkbaar resultaat behaalt, maar met veel meer informatie over uw regressiemodel en de mogelijkheid om meer dan één onafhankelijke variabele te passen.

Stel dat ik een tabel met gegevens heb met: x en ja waarden waar x is de onafhankelijke variabele en ja is de afhankelijke variabele:

Ik wil weten wat de regressievergelijking van de bovenstaande gegevens is. LIJNSCH gebruiken:

1	=LIJNSCH(B3:B7,C3:C7,WAAR,ONWAAR)

De waarde van het y-snijpunt is hier gelijk aan 0, in wetenschappelijke notatie.

De vergelijking van de lijn is y= 2x + 0 . Merk op dat LIJNSCH terugkeert beide de helling en het snijpunt van de lijn. Om beide waarden te retourneren, moet de formule worden ingevoerd als een matrixformule. Later meer over matrixformules.

LIJNSCH gebruiken

De LIJNSCH-functie heeft vier argumenten:

1	=LIJNSCH(y-bekend, x-bekend, const, stats)

Waar,

Argument	Beschrijving
bekende_y's en bekende_x's	Is de x en ja gegevens in uw gegevenstabel
const	TRUE/FALSE optie om te bepalen of het y-snijpunt op 0 moet worden gezet of normaal moet worden berekend
statistieken	TRUE/FALSE optie of aanvullende regressiestatistieken moeten worden geretourneerd

Met ons eerste voorbeeld wordt de functie geschreven als:

1	=LIJNSCH(B3:B7,C3:C7,WAAR,ONWAAR)

Wanneer de statistieken optie is ingesteld op TRUE, is de organisatie van de regressiestatistieken als volgt:

Je vraagt ​​je misschien af ​​wat elke variabele betekent.

statistiek	Beschrijving
mN	Hellingcoëfficiënten voor x variabelen
B	y-onderscheppen
seN	Standaardfout voor elke hellingscoëfficiënt
seB	Standaardfout voor het y-snijpunt
R2	Determinatiecoëfficiënt
seja	Standaardfout voor de ja schatting
F	De F-statistiek (om te bepalen of de relatie tussen de variabelen toevallig is)
NSF	Graden van vrijheid
ssreg	Regressie som van kwadraten
sswoonachtig	Resterende kwadratensom

De belangrijkste statistieken om te begrijpen zijn de hellingscoëfficiënten, het y-snijpunt en de determinatiecoëfficiënt of de r² waarde van het model.

Gebruik het bovenstaande voorbeeld en selecteer TRUE voor de statistieken parameter:

De gemarkeerde cellen tonen de helling = 2, intercept = 0 en r² = 1.

de r² waarde is een indicator van de sterkte van de correlatie van het model. Het kan worden gezien als een indicator van fitheid. een lage r² waarde zou een slechte correlatie betekenen tussen uw afhankelijke en onafhankelijke variabelen, en het tegenovergestelde is waar voor hoge r² waarden, met r² = 1 is een perfecte pasvorm.

In releases na januari 2022 van Excel in Microsoft 365 (voorheen Office 365) hebben dynamische matrices de manier veranderd waarop matrixformules worden geëvalueerd. Het is niet langer nodig om CTRL + SHIFT + ENTER te gebruiken of het gebied met cellen te markeren dat de array in beslag zal nemen. Voer eenvoudig de formule in en klik op Enter en de resulterende cellen zullen "uitlopen" in de array.

Voor de rest van dit artikel verwijzen we naar het gebruik van LIJNSCH met betrekking tot dynamische arrays in Microsoft 365 Excel.

Prognose met LIJNSCH (eenvoudige regressie)

Het combineren van de LIJNSCH- en SOM-functies kan worden gebruikt om de waarde van een afhankelijke variabele te voorspellen ja, gegeven bekend x en ja gegevens. Hieronder ziet u een voorbeeld dat laat zien wat de ja waarde zal zijn wanneer x=14.

1	=SOM(LIJNSCH(C3:C7,B3:B7)*{14,1})

Het model heeft de vorm y =mx + b . Dit is hetzelfde als y = a+ bx, alleen een andere manier om de vergelijking weer te geven. Een tip om in gedachten te houden voor lineaire vergelijkingen is de variabele naast x is altijd de helling, en de variabele na een plus- of minteken is altijd het snijpunt, ongeacht de letters die in de vergelijking worden gebruikt.

Met behulp van de formule: =SUM(LIJNSCH(C3:C7,B3:B7)*{14,1}) geeft het resultaat van 28 als resultaat. Aangezien dit een enkel resultaat is, is het niet nodig om het als een array in te voeren.

Het uiteinde van de bovenstaande formule *{14,1} specificeert de onafhankelijke variabele die moet worden gebruikt voor het voorspellen van de afhankelijke variabele, in dit geval 14.

We kunnen dit controleren door x= 14 in te voeren in de vergelijking van de lijn, y =2x + 0.

Prognose met LIJNSCH (meervoudige lineaire regressie)

De volgende gegevenstabel is afkomstig van de LIJNSCH-pagina van de ondersteuningswebsite van Microsoft.

In sommige gevallen is er meer dan één onafhankelijke variabele waarmee rekening moet worden gehouden bij het maken van een lineair regressiemodel. Dit wordt meervoudige lineaire regressie genoemd (d.w.z. meerdere onafhankelijke variabelen). Als ik de kosten van een kantoorgebouw wil schatten, zouden zaken als vloeroppervlak, aantal ingangen van gebouwen, ouderdom van het gebouw en het aantal kantoren allemaal deel uitmaken van de vergelijking. Laten we een voorbeeld bekijken.

Als we de LIJNSCH-formule in cel G29 typen en uitvoeren, krijgen we:

1	=LIJNSCH(E3:E13,A3:D13,WAAR,WAAR)

Het model komt in de vorm:

Onthoud dat de LIJNSCH-resultatenreeks in omgekeerde volgorde staat van de vergelijking. In het bovenstaande voorbeeld is 52.317,8 ons snijpunt, b en 27,6 is onze m₁ of de hellingswaarde voor de variabele Vloerruimte, x₁.

Met behulp van de LIJNSCH-functie met de verstrekte gegevens, is ons regressiemodel:

met een r² waarde van 0,997, wat wijst op een sterk of sterk gecorreleerd model. Met behulp van het model kunt u nu voorspellen wat de geschatte waarde van een kantoorgebouw zal zijn op basis van een willekeurige combinatie van bovenstaande onafhankelijke variabelen.

LIJNSCH-tips

1. Zorg ervoor dat je de meest recente versie van Microsoft 365 hebt om LIJNSCH te gebruiken met dynamische arrays. Mogelijk moet u Office Insider Current Channel (Preview) inschakelen om dynamische arrayfuncties te gebruiken. Op de accountpagina:
   
2. Als u een niet-Microsoft 365-release gebruikt, moet u de oude methode CTRL + SHIFT + ENTER (CSE) gebruiken om matrixformules te evalueren.
3. Als u de legacy-methode gebruikt, is het aantal kolommen dat moet worden gemarkeerd bij het invoeren van een LIJNSCH-arrayfunctie altijd het aantal x variabelen in uw gegevens plus 1. Het aantal rijen dat u voor de array kunt selecteren, is 5.
4. Als u uw dynamische array-versie van Excel deelt met iemand die een niet-Microsoft 365-release gebruikt, gebruikt u de verouderde CSE-methode om compatibiliteitsproblemen te voorkomen.

Geïnteresseerd in meer prognoses?

Zie onze andere artikelen over prognoses met exponentiële afvlakking, TREND, GROEI en LOGSCH-functies.

LIJNSCH functie in Google Spreadsheets

De LIJNSCH-functie werkt in Google Spreadsheets precies hetzelfde als in Excel.

LIJNSCH-voorbeelden in VBA

U kunt ook de LIJNSCH-functie in VBA gebruiken. Type:
application.worksheetfunction.linest(bekende_ys,bekende_xs,const,stats)

De volgende VBA-instructie uitvoeren

1	Range("D2") = Application.WorksheetFunction.LinEst(Range("A2:A8"), Range("B2:B8"))

zal de volgende resultaten opleveren:

Voor de functieargumenten (bekende_y's, enz.), kunt u ze rechtstreeks in de functie invoeren of variabelen definiëren om in plaats daarvan te gebruiken.

Keer terug naar de lijst met alle functies in Excel