Statistische Excel-Funktionen: INTERCEPT

Zusammenfassung

In diesem Artikel wird die INTERCEPT-Funktion in Microsoft Excel erläutert, die Verwendung der Funktion veranschaulicht und die Ergebnisse für Excel 2003 und für spätere Excel-Versionen mit den Ergebnissen in früheren Excel-Versionen verglichen.

Weitere Informationen

Die FUNKTION INTERCEPT(known_y,known_x) gibt den INTERCEPT der linearen Regressionslinie zurück, die verwendet wird, um y-Werte aus x-Werten vorherzusagen.

Syntax

INTERCEPT(known_y's,known_x's)

Die Argumente known_y und known_x müssen Arrays oder Zellbereiche sein, die die gleiche Anzahl numerischer Datenwerte enthalten. Häufig umfasst INTERCEPT zwei Zellbereiche, die die Daten enthalten, z. B. INTERCEPT(A1:A100, B1:B100).

Beispiel für die Verwendung

Um die INTERCEPT-Funktion zu veranschaulichen, erstellen Sie ein leeres Excel-Arbeitsblatt, kopieren Sie die folgende Tabelle, wählen Sie zelle A1 in Ihrem leeren Excel-Arbeitsblatt aus, und fügen Sie dann die Einträge ein, sodass die folgende Tabelle die Zellen A1:D13 in Ihrem Arbeitsblatt ausfüllt.

• Klicken Sie in Microsoft Office Excel 2007 auf die Registerkarte Start, klicken Sie in der Gruppe Zellen auf Format, und klicken Sie dann auf Spaltenbreiten automatisch anpassen.
• Zeigen Sie in Excel 2003 im Menü Format auf Spalte, und klicken Sie dann auf Auswahl automatisch anpassen.

Möglicherweise möchten Sie die Zellen B2:B7 als Zahl mit 0 Dezimalstellen und die Zellen A9:D13 als Zahl mit 6 Dezimalstellen formatieren.

Die Zellen A2:A7 und B2:B7 enthalten die y- und x-Werte, die INTERCEPT in Zelle A10 aufrufen.

In Excel-Versionen, die älter als Excel 2003 sind, kann INTERCEPT Rundungsfehler aufweisen. Excel 2003 und höhere Versionen von Excel verbessern das Verhalten von INTERCEPT. INTERCEPT(known_y,known_x's) ist das Ergebnis der Auswertung von AVERAGE(known_y) – SLOPE(known_y's, known_x's) * AVERAGE(known_x's). Während der Code für INTERCEPT für Excel 2003 und für spätere Excel-Versionen nicht direkt geändert wurde, wurde das Verhalten von INTERCEPT aufgrund des verbesserten Codes für SLOPE verbessert.

Wenn Sie über eine frühere Version von Excel verfügen, können Sie das zuvor erstellte Arbeitsblatt verwenden, um ein Experiment auszuführen, um zu ermitteln, wann Rundungsfehler auftreten. Das Hinzufügen einer positiven Konstante zu jeder der Beobachtungen in B2:B7 sollte den Wert von SLOPE nicht beeinflussen. Wenn Sie x,y-Paare mit x auf der horizontalen Achse und y auf der vertikalen Achse zeichnen und dann jedem x-Wert eine positive Konstante hinzufügen, werden die Daten einfach nach rechts verschoben. Die am besten geeignete Regressionslinie weist immer noch die gleiche Steigung auf. Die verschobenen Daten haben jedoch einen anderen Intercept.

Mit dem Standardwert 0 in D3 beträgt SLOPE in A9 0,775280899. Zelle A10 zeigt den Wert von INTERCEPT und Zelle A11 den Wert des Ausdrucks an, der bei der Berechnung von INTERCEPT ausgewertet wird:

AVERAGE(known_y's) – SLOPE(known_y's, known_x's) * AVERAGE(known_x's)

Werte in den Zellen A9 und A10 stimmen immer überein, da der Wert in A10 genau dem entspricht, was INTERCEPT zurückgibt. SLOPE sollte nicht variieren, wenn Sie verschiedene positive Konstanten zu den known_x hinzufügen. Zelle A11 zeigt AVERAGE(known_y) – 0,775280899 * AVERAGE(known_x) an. Da SICH SLOPE nicht ändern sollte und 0,775280899 der Wert von SLOPE ist, wenn D3 = 0, sollten die Werte dieses Ausdrucks in A11 auch mit den Werten in den Zellen A9 und A10 übereinstimmen.

Wenn Sie den Wert in D3 erhöhen, fügen Sie B2:B7 eine größere Konstante hinzu. Wenn D3 <= 7 ist, gibt es keine Rundungsfehler, die in den ersten 6 Dezimalstellen von SLOPE angezeigt werden. Wenn Sie jedoch 7,25, 7,5, 7,75 und 8 ausprobieren, ändert sich die STEIGUNG in A9. Daher unterscheiden sich die Werte in den Zellen A11 (die mit A10 übereinstimmen) und A12. Die Werte in A11 (oder A10) und A12 sollten jedoch identisch sein, da sich das Hinzufügen einer Konstante zu den known_x nicht auf DIE STEIGUNG auswirken sollte.

D7:D13 zeigt die Werte an, die INTERCEPT zurückgibt, und die Werte, die INTERCEPT hätte zurückgeben müssen, wenn SLOPE nicht geändert wurde. Diese Wertepaare werden für die Fälle angezeigt, in denen D3 = 7,5 bzw. 8 ist. Rundungsfehler sind so schwerwiegend geworden, dass die Division durch 0 auftritt, wenn D3 = 8 ist.

Frühere Excel-Versionen geben in diesen Fällen falsche Antworten, da die Auswirkungen von Rundungsfehlern mit der berechnungsbasierten Formel, die diese Versionen verwenden, größer sind. Dennoch zeigt dieses Experiment, dass die Fälle, in denen die Fehler auftreten, extrem sind.

Wenn Sie Excel 2003 oder eine höhere Version von Excel verwenden, gibt es nur einen geringen oder keinen Unterschied zwischen den allgemeinen Werten in A10 und A11 und dem Wert in A12, wenn Sie das Experiment ausprobieren. Die Zellen D7:D13 zeigen jedoch die Abrundungsfehler an, die Sie in früheren Excel-Versionen erhalten.

Ergebnisse in früheren Versionen von Excel

Im Artikel über SLOPE wird die weniger robuste Formel beschrieben, die in früheren Versionen verwendet wird. Die Formel erfordert nur einen Durchlauf der Daten. Nur die Mängel von SLOPE in diesen Versionen führen dazu, dass INTERCEPT in den Extremfällen Rundungsfehler gibt.

Ergebnisse in Excel 2003 und höheren Versionen von Excel

Excel 2003 und höhere Versionen von Excel verwendet ein verbessertes Verfahren zum Berechnen von SLOPE. Dadurch verbessert sich die Leistung von INTERCEPT. Das verbesserte Verfahren erfordert zwei Datendurchläufe. Auch hier wird im folgenden Artikel über SLOPE die Verbesserung beschrieben.

Weitere Informationen zu den Verbesserungen in SLOPE für Excel 2003 und für höhere Excel-Versionen finden Sie in den folgenden Artikeln der Microsoft Knowledge Base:

828142 Statistische Excel-Funktionen: SLOPE

Schlussfolgerungen

Da Excel 2003 und höhere Versionen von Excel einen 1-Durchlauf-Ansatz durch einen Zweidurchlauf-Ansatz ersetzen, ist die numerische Leistung von SLOPE in Excel 2003 und in höheren Excel-Versionen besser als in früheren Versionen von Excel. Daher ist die numerische Leistung von INTERCEPT besser. Ergebnisse in Excel 2003 und höheren Excel-Versionen sind nie weniger genau als die Ergebnisse in früheren Excel-Versionen.

In der Regel gibt es keinen Unterschied zwischen den Ergebnissen in Excel 2003 und höheren Versionen von Excel und den Ergebnissen in früheren Versionen von Excel, da sich Daten nicht häufig in der ungewöhnlichen Weise verhalten, die dieses Experiment veranschaulicht. Numerische Instabilität tritt wahrscheinlich in früheren Excel-Versionen auf, wenn die Daten viele signifikante Ziffern und geringe Abweichungen zwischen den Datenwerten enthalten.

Das folgende Verfahren ermittelt die Summe der quadratischen Abweichungen über einen Stichprobenmittelwert:

1. Suchen Sie den Stichprobenmittelwert.
2. Berechnen Sie jede quadratische Abweichung.
3. Summieren Sie die quadratischen Abweichungen.

Dieses Verfahren ist genauer als das folgende alternative Verfahren (auch als "Rechnerformel" bezeichnet, da es für einen Rechner für eine kleine Anzahl von Datenpunkten geeignet war):

1. Ermitteln Sie die Summe der Quadrate aller Beobachtungen, die Stichprobengröße und die Summe aller Beobachtungen.
2. Berechnen Sie die Summe der Quadrate aller Beobachtungen minus ((Summe aller Beobachtungen)^2)/Stichprobengröße).

Durch Ersetzen dieser 1-Durchlauf-Prozedur durch die 2-Durchlauf-Prozedur, die den Stichprobenmittelwert beim ersten Durchgang findet und die Summe der quadratischen Abweichungen darüber beim zweiten Durchlauf berechnet, verbessern Excel 2003 und spätere Versionen von Excel viele andere Funktionen. Eine kurze Liste solcher Funktionen umfasst VAR, VARP, STDEV, STDEVP, DVARP, DSTDEV, DSTDEVP, FORECAST, SLOPE, INTERCEPT, PEARSON, RSQ und STEYX. Microsoft hat ähnliche Verbesserungen in jedem der drei Analyse von Varianztools im AnalysetoolPak vorgenommen.