Statistische functies van Excel: INTERCEPT

Samenvatting

In dit artikel wordt de functie INTERCEPT in Microsoft Excel besproken, wordt uitgelegd hoe u de functie gebruikt en worden de resultaten voor Excel 2003 en voor latere versies van Excel vergeleken met de resultaten in eerdere versies van Excel.

Meer informatie

De functie INTERCEPT (known_y,known_x) retourneert de INTERCEPT van de lineaire regressielijn die wordt gebruikt om y-waarden te voorspellen op basis van x-waarden.

Syntaxis

INTERCEPT(known_y's,known_x's)

De argumenten , known_y en known_x, moeten matrices of celbereiken zijn die gelijke aantallen numerieke gegevenswaarden bevatten. Intercept bevat vaak twee cellenbereiken met de gegevens, zoals INTERCEPT(A1:A100, B1:B100).

Voorbeeld van gebruik

Ter illustratie van de functie INTERCEPT maakt u een leeg Excel-werkblad, kopieert u de volgende tabel, selecteert u cel A1 in het lege Excel-werkblad en plakt u de vermeldingen zodat de volgende tabel de cellen A1:D13 in het werkblad vult.

• Klik in Microsoft Office Excel 2007 op het tabblad Start , klik op Opmaak in de groep Cellen en klik vervolgens op Kolombreedten automatisch aanpassen.
• Wijs in Excel 2003 kolom aan in het menu Opmaak en klik vervolgens op Selectie automatisch aanpassen.

U kunt cellen B2:B7 opmaken als Getal met 0 decimalen en cellen A9:D13 als Getal met 6 decimalen.

Cellen A2:A7 en B2:B7 bevatten de y- en x-waarden die INTERCEPT aanroepen in cel A10.

In versies van Excel die ouder zijn dan Excel 2003, kan INTERCEPT afrondingsfouten vertonen. Excel 2003 en latere versies van Excel verbeteren het gedrag van INTERCEPT. INTERCEPT(known_y,known_x) is het resultaat van het evalueren van GEMIDDELDE(known_y) – HELLING(known_y, known_x) * GEMIDDELDE(known_x).) Hoewel de code voor INTERCEPT niet rechtstreeks is gewijzigd voor Excel 2003 en voor latere versies van Excel, is het gedrag van INTERCEPT verbeterd vanwege verbeterde code voor SLOPE.

Als u een eerdere versie van Excel hebt, kunt u het werkblad gebruiken dat u eerder hebt gemaakt om een experiment uit te voeren om te detecteren wanneer er afrondingsfouten optreden. Het toevoegen van een positieve constante aan elk van de waarnemingen in B2:B7 mag de waarde van HELLING niet beïnvloeden. Als u x,y koppelt met x op de horizontale as en y op de verticale as en vervolgens een positieve constante toevoegt aan elke x-waarde, worden de gegevens naar rechts verplaatst. De best passende regressielijn heeft nog steeds dezelfde helling. De verschoven gegevens hebben echter een andere interceptie.

Met de standaardwaarde 0 in D3 is HELLING in A9 0,775280899. Cel A10 toont de waarde van INTERCEPT en cel A11 geeft de waarde weer van de expressie die wordt geëvalueerd bij het berekenen van INTERCEPT:

GEMIDDELDE(known_y) – HELLING(known_y,known_x) * GEMIDDELDE(known_x))

Waarden in de cellen A9 en A10 komen altijd overeen omdat de waarde in A10 precies is wat INTERCEPT retourneert. DE HELLING mag niet variëren als u verschillende positieve constanten toevoegt aan de known_x. Cel A11 toont GEMIDDELDE(known_y) – 0,775280899 * GEMIDDELDE(known_x's). Omdat HELLING niet mag veranderen en 0,775280899 de waarde is van HELLING wanneer D3 = 0, moeten de waarden van deze expressie in A11 ook overeenkomen met de waarden in de cellen A9 en A10.

Als u de waarde in D3 verhoogt, voegt u een grotere constante toe aan B2:B7. Als D3 <= 7, zijn er geen afrondingsfouten die worden weergegeven in de eerste 6 decimalen van HELLING. Maar als u 7,25, 7,5, 7,75 en 8 probeert, verandert de HELLING in A9. Als gevolg hiervan verschillen de waarden in de cellen A11 (die overeenkomen met A10) en A12. Waarden in A11 (of A10) en A12 moeten echter hetzelfde zijn, omdat het toevoegen van een constante aan de known_x geen invloed mag hebben op HELLING.

D7:D13 geeft de waarden weer die INTERCEPT retourneert en de waarden die INTERCEPT had moeten retourneren als DE HELLING niet was gewijzigd. Deze paren van waarden worden weergegeven voor de gevallen waarin D3 = respectievelijk 7,5 en 8. Afrondingsfouten zijn zo ernstig geworden dat deling door 0 optreedt wanneer D3 = 8.

Eerdere versies van Excel geven in deze gevallen verkeerde antwoorden omdat de effecten van afrondingsfouten groter zijn met de rekenformule die in deze versies wordt gebruikt. Toch laat dit experiment zien dat de gevallen waarin de fouten optreden extreem zijn.

Als u Excel 2003 of een latere versie van Excel hebt, is er weinig of geen verschil tussen de algemene waarden in A10 en A11 en de waarde in A12 als u het experiment probeert. Cellen D7:D13 bevatten echter de afrondingsfouten die u verkrijgt met de eerdere versies van Excel.

Resultaten in eerdere versies van Excel

In het artikel over SLOPE wordt de minder numeriek robuuste formule beschreven die in eerdere versies wordt gebruikt. Voor de formule is slechts één pass through de gegevens vereist. Alleen de tekortkomingen van SLOPE in deze versies zorgen ervoor dat INTERCEPT in extreme gevallen afrondingsfouten geeft.

Resultaten in Excel 2003 en in latere versies van Excel

In Excel 2003 en latere versies van Excel wordt een verbeterde procedure gebruikt voor het berekenen van HELLING. Als gevolg hiervan verbeteren de prestaties van INTERCEPT. Voor de verbeterde procedure zijn twee doorgangen van de gegevens vereist. Ook in het volgende artikel over SLOPE wordt de verbetering beschreven.

Klik op het volgende artikelnummer in de Microsoft Knowledge Base voor meer informatie over de verbeteringen in SLOPE voor Excel 2003 en voor latere versies van Excel:

828142 statistische functies van Excel: HELLING

Conclusies

Omdat Excel 2003 en latere versies van Excel een one-pass-benadering vervangen door een tweeledige benadering, zijn de numerieke prestaties van SLOPE in Excel 2003 en in latere versies van Excel beter dan in eerdere versies van Excel. Daarom zijn de numerieke prestaties van INTERCEPT beter. Resultaten in Excel 2003 en in latere versies van Excel zijn nooit minder nauwkeurig dan de resultaten in eerdere versies van Excel.

Meestal is er geen verschil tussen de resultaten in Excel 2003 en in latere versies van Excel en de resultaten in eerdere versies van Excel, omdat gegevens zich niet vaak gedragen op de ongebruikelijke manier die dit experiment illustreert. Numerieke instabiliteit treedt waarschijnlijk op in eerdere versies van Excel wanneer de gegevens veel significante cijfers bevatten en weinig variatie tussen de gegevenswaarden.

Met de volgende procedure wordt de som van de kwadratische afwijkingen over een steekproefgemiddelde gevonden:

1. Zoek het gemiddelde van de steekproef.
2. Bereken elke kwadratische deviatie.
3. De kwadratische afwijkingen optellen.

Deze procedure is nauwkeuriger dan de volgende alternatieve procedure (ook wel bekend als de "rekenmachineformule", omdat deze geschikt was voor gebruik op een rekenmachine voor een klein aantal gegevenspunten):

1. Zoek de som van de kwadraten van alle waarnemingen, de steekproefgrootte en de som van alle waarnemingen.
2. Bereken de som van de kwadraten van alle waarnemingen min ((som van alle waarnemingen)^2)/steekproefgrootte).

Door deze laatste one-pass-procedure te vervangen door de tweeledige procedure waarmee het steekproefgemiddelde op de eerste doorgang wordt gevonden en de som van de kwadratische afwijkingen wordt berekend op de tweede doorgang, verbeteren Excel 2003 en latere versies van Excel veel andere functies. Een korte lijst met dergelijke functies bevat VAR, VARP, STDEV, STDEVP, DVAR, DVARP, DSTDEV, DSTDEVP, FORECAST, SLOPE, INTERCEPT, PEARSON, RSQ en STEYX. Microsoft heeft vergelijkbare verbeteringen aangebracht in elk van de drie hulpprogramma's voor variantieanalyse in Analysis ToolPak.