Statistische Excel-Funktionen: LOGEST

Zusammenfassung

In diesem Artikel wird die LOGEST-Funktion in Microsoft Office Excel 2003 und in späteren Versionen von Excel beschrieben. Es veranschaulicht, wie die Funktion verwendet wird, und vergleicht die Ergebnisse der Funktion in Excel 2003 und in späteren Versionen von Excel mit den Ergebnissen der Funktion in früheren Excel-Versionen.

LOGEST wird ausgewertet, indem die zugehörige Funktion LINEST aufgerufen wird. Umfangreiche Änderungen an LINEST für Excel 2003 und für spätere Versionen von Excel werden zusammengefasst, und ihre Auswirkungen auf LOGEST werden vermerkt.

Informationen zu Microsoft Excel 2004 für Macintosh

Die statistischen Funktionen in Microsoft Excel 2004 für Macintosh wurden mit denselben Algorithmen wie Excel 2003 und höheren Versionen von Excel aktualisiert. Alle Informationen in diesem Artikel, die beschreiben, wie eine Funktion funktioniert oder wie eine Funktion für Excel 2003 und für spätere Versionen von Excel geändert wurde, gelten auch für Excel 2004 für Macintosh.

Weitere Informationen

Die LOGEST-Funktion (known_y, known_x, Konstante, Statistik) wird verwendet, um Regressionen durchzuführen, bei denen eine exponentielle Kurve angepasst wird. Es wird ein Kriterium der kleinsten Quadrate verwendet, und LOGEST versucht, die beste Passform unter dieses Kriterium zu finden. Known_y stellen Daten für die abhängige Variable dar, und known_x stellen Daten für eine oder mehrere unabhängige Variablen dar. Das zweite Argument ist optional. Wenn sie nicht angegeben wird, wird davon ausgegangen, dass es sich um ein Array der gleichen Größe wie known_y handelt, das die Werte (1, 2, 3, ...).

Unter der Annahme, dass p-Prädiktorvariablen vorhanden sind, passt LOGEST eine Formel der folgenden Form an:

y = b * (m1^x1) * (m2^x2) * … * (mp^xp)

Werte der Koeffizienten b, m1, m2, ..., mp, die die beste Passform an die y-Daten liefern, werden bestimmt.

Das letzte Argument für LOGEST ist auf TRUE festgelegt, wenn Sie zusätzliche Statistiken benötigen, einschließlich verschiedener Summen von Quadraten, r-Quadrat, f-Statistik und Standardfehlern der Regressionskoeffizienten. In diesem Fall muss LOGEST als Matrixformel eingegeben werden. Das letzte Argument ist optional. Wenn sie nicht angegeben wird, wird sie als FALSCH interpretiert. Die Dimensionen des Arrays sind fünf Zeilen durch eine Anzahl von Spalten, die der Anzahl der unabhängigen Variablen entsprechen, sowie eine, wenn das dritte Argument auf WAHR festgelegt ist (plus 0, wenn das dritte Argument nicht auf WAHR festgelegt ist).

Wenn das dritte Argument "konstante" auf TRUE festgelegt ist, soll das Regressionsmodell die Konstante b in der vorherigen Formel in das Modell aufnehmen. Wenn sie auf FALSE festgelegt ist, wird die multiplikative Konstante b aus dem Regressionsmodell ausgeschlossen, indem sie gleich 1 erzwungen wird. Das dritte Argument ist optional. Wenn sie nicht angegeben wird, wird sie als WAHR interpretiert.

Gehen Sie in diesem Artikel davon aus, dass Daten in Spalten angeordnet sind, sodass known_y eine Spalte mit y-Daten und known_x eine oder mehrere Spalten mit x-Daten sind. Die Abmessungen oder Längen jeder dieser Spalten müssen gleich sein. Alle folgenden Beobachtungen sind gleich wahr, wenn die Daten nicht in Spalten angeordnet sind, aber es ist einfacher, den am häufigsten verwendeten Fall zu diskutieren. Gehen Sie außerdem davon aus, dass das letzte Argument für LOGEST immer WAHR ist und Sie immer an einer detaillierten Ausgabe interessiert sind. Diese Annahme trägt dazu bei, numerische Probleme in früheren Excel-Versionen anzuzeigen. Einige numerische Probleme sind weiterhin vorhanden, wenn Sie für dieses Argument FALSE verwenden.

In diesem Artikel werden die folgenden Beispiele verwendet, um zu zeigen, wie SICH LOGEST auf LINEST bezieht, und um auf Probleme mit LINEST in früheren Excel-Versionen hinzuweisen, die in Probleme mit LOGEST übersetzt werden. Während der Code für LOGEST nicht für Excel 2003 und für spätere Versionen von Excel umgeschrieben wurde, wurden umfangreiche Änderungen und Verbesserungen am Code für LINEST vorgenommen. LOGEST ruft EFFEKTIV LINEST auf, führt LINEST aus, ändert die LINEST-Ausgabe und stellt sie Ihnen vor. Daher sollten Sie über Probleme bei der Ausführung von LINEST informiert sein. Wenn Sie weitere Informationen zu LINEST wünschen, klicken Sie auf die folgende Artikelnummer, um den Artikel in der Microsoft Knowledge Base anzuzeigen:

828533 Beschreibung der FUNKTION ZEILENLINIEN in Excel 2003 und in späteren Excel-Versionen

Die LINEST-Hilfedatei wurde auch für Excel 2003 und für spätere Versionen von Excel überarbeitet und ist hilfreich für weitere Informationen. Da der Schwerpunkt dieses Artikels auf numerischen Problemen in früheren Versionen von Microsoft Excel liegt, enthält dieser Artikel nicht viele praktische Beispiele für LOGEST. Die LOGEST-Hilfedatei enthält zwei nützliche Beispiele.

Syntax

LOGEST(known_y's, known_x's, constant, statistics)

Die Argumente known_y und known_x müssen Arrays oder Zellbereiche sein, die verwandte Dimensionen aufweisen. Wenn known_y zeilenweise eine Spalte nach m ist, sollten known_x spaltenweise c zeilenweise sein, wobei c größer oder gleich eins ist. C ist die Anzahl der Prädiktorvariablen; m ist die Anzahl der Datenpunkte. Ähnliche Beziehungen müssen gehalten werden, wenn known_y in einer einzigen Zeile angeordnet sind; in diesem Fall sollten sich known_x in Zeilen r befinden. (R muss größer oder gleich 1 sein.) Konstanten und Statistiken sind logische Argumente, die auf WAHR oder FALSCH festgelegt werden müssen. In Excel müssen diese Argumente auf 0 oder 1 festgelegt sein und werden von Excel als FALSCH bzw. WAHR interpretiert. Die letzten drei Argumente für LOGEST sind alle optional. Wenn Sie das zweite Argument weglassen, geht LOGEST von einem einzelnen Prädiktor aus, der die Einträge {1, 2, 3, ...} enthält. Wenn Sie das dritte Argument weglassen, wird es als WAHR interpretiert. Wenn Sie das vierte Argument weglassen, wird es als FALSCH interpretiert.

Die häufigste Verwendung von LOGEST umfasst zwei Zellbereiche, die die Daten enthalten, z. B. LOGEST(A1:A100, B1:F100, TRUE, TRUE). Da es in der Regel mehrere Vorhersagevariablen gibt, enthält das zweite Argument in diesem Beispiel mehrere Spalten. In diesem Beispiel gibt es 100 Subjekte, einen abhängigen Variablenwert, known_y für jedes Subjekt und fünf abhängige Variablenwerte known_x für jedes Subjekt.

Beispiel für die Verwendung

Zwei separate Excel-Arbeitsblattbeispiele werden bereitgestellt, um die folgenden Schlüsselkonzepte zu veranschaulichen:

  • Interaktion von LOGEST mit LINEST
  • Probleme in Microsoft Excel 2002 und in früheren Excel-Versionen, wenn das dritte Argument für LOGEST oder LINEST auf FALSE festgelegt oder ausgelassen wird.
  • Probleme aufgrund von kollinearen known_x in LOGEST oder LINEST in Excel 2002 und früheren Versionen.

Wenn Sie weitere Informationen zu den zweiten und dritten Konzepten in dieser Liste wünschen, klicken Sie auf die folgende Artikelnummer, um den Artikel in der Microsoft Knowledge Base anzuzeigen:

828533 Beschreibung der FUNKTION ZEILENLINIEN in Excel 2003 und in späteren Excel-Versionen

Führen Sie die folgenden Schritte aus, um LOGEST mit dem dritten Argument zu veranschaulichen, das auf FALSE festgelegt ist:

  1. Erstellen Sie ein leeres Excel-Arbeitsblatt, und kopieren Sie dann die Tabelle weiter unten in diesem Abschnitt.

  2. Klicken Sie im leeren Excel-Arbeitsblatt auf Zelle A1. Fügen Sie dann die Einträge ein, sodass die Tabelle die Zellen A1:H17 in Ihrem Arbeitsblatt ausfüllt.

  3. Nachdem Sie die Tabelle in das neue Excel-Arbeitsblatt eingefügt haben, klicken Sie auf "Einfügeoptionen" und dann auf " Zielformatierung anpassen".

  4. Während der eingefügte Bereich noch ausgewählt ist, verwenden Sie eines der folgenden Verfahren, je nachdem, welche Excel-Version Sie ausführen:

    • Klicken Sie in Microsoft Office Excel 2007 auf die Registerkarte "Start", dann in der Gruppe "Zellen" auf "Format" und dann auf "Spaltenbreite automatisch anpassen".
    • Zeigen Sie in Excel 2003 und früheren Excel-Versionen im Menü "Format" auf "Spalte", und klicken Sie dann auf "Auswahl automatisch anpassen".
Drittes Argument auf FALSE festgelegt B C D E F G H
Excel 2002 und frühere Versionen von Excel
Excel 2003 und höhere Versionen von Excel
Y X RKP: RKP:
=EXP(A11) 1 197.495201079493 1 197.495201079493 1
=EXP(A12) 2 1.23717914826348 #N/A 1.23717914826348 #N/A
=EXP(A13) 3 -20.4285714285714 4.62910049886276 0.901250822909809 4.62910049886276
-1.90666666666667 2 18.2533333333333 2
-40.8571428571429 42.8571428571429 391.142857142857 42.8571428571429
LN(Y's) X RGP: RGP:
11 =B4 5.28571428571429 0 5.28571428571429 0
12 =B5 1.23717914826348 #N/A 1.23717914826348 #N/A
13 =B6 -20.4285714285714 4.62910049886276 0.901250822909809 4.62910049886276
-1.90666666666667 2 18.2533333333333 2
-40.8571428571429 42.8571428571429 391.142857142857 42.8571428571429
Verwenden von EXP: =EXP(G11) =EXP(H11)

Daten für LOGEST befinden sich in den Zellen A4:B6. Detaillierte Ergebnisse für Excel 2002 und für frühere Versionen von Excel sowie für Excel 2003 und für spätere Versionen von Excel werden in den Zellen D4:E8 bzw. G4:H8 angezeigt. Die Zellen A11:B13 zeigen die gleichen known_x an, aber die known_y in den Zellen A4:A6 wurden transformiert, indem ihr natürlicher Logarithmus mithilfe der Excel-LN-Funktion verwendet wird. LINEST wird dann für diese transformierten Daten aufgerufen, und die Ergebnisse werden in den Zellen G11:H15 angezeigt. Beachten Sie, dass die Ergebnisse in den Zellen G12:H15 mit den LOGEST-Ergebnissen in den Zellen G5:H8 identisch sind. Die LINEST-Koeffizienten in den Zellen G11:H11 werden durch Exponentiation transformiert. Das heißt, die LINEST-Koeffizienten in den Zellen werden transformiert, indem im Wesentlichen die Excel EXP-Funktion verwendet wird, um die LOGEST-Koeffizienten in den Zellen G4:H4 zu berechnen. Sie können diese Beziehung überprüfen, indem Sie die EXP-Funktion in den Zellen G17:H17 verwenden. Um zusammenzufassen, wie LOGEST und LINEST interagieren, beachten Sie die folgende Abfolge von Schritten:

  1. Sie rufen LOGEST(known_y's, known_x's, constant, TRUE) auf.
  2. LOGEST ruft LINEST(LN of known_y's, known_x's, constant, TRUE) auf.
  3. LOGEST empfängt die Ergebnistabelle aus diesem Aufruf von LINEST.
  4. LOGEST ändert die LINEST-Koeffizienten in der ersten Zeile der LINEST-Ergebnistabelle durch Exponentiation. Ersetzen Sie z. B. jeden LINEST-Koeffizienten m durch EXP(m).
  5. LOGEST gibt diese geänderte LINEST-Ergebnistabelle als LOGEST-Ergebnistabelle zurück.

Wenn LOGEST geeignete Ergebnisse zurückgeben soll, muss LINEST in Schritt 3 entsprechende Ergebnisse generieren. Untersuchen Sie die Zellen D13:D15. Zelle D13 enthält einen R-Quadrat-Wert, Zelle D14 eine f-Statistik und Zelle D15 die LINEST-Regressionssumme von Quadraten.

In Excel 2002 und in früheren Versionen von Excel wird, wenn LINEST aufgerufen wird, dessen drittes Argument auf FALSE festgelegt ist, immer eine Regressionssumme von Quadraten berechnet, die nicht korrekt ist, da eine formel verwendet wird, die nicht korrekt ist. Dieses Problem wurde in Excel 2003 und in späteren Versionen von Excel behoben. Beachten Sie, dass die Werte in den ersten beiden Zeilen der Ausgabetabelle von diesem Problem nicht betroffen sind. In Excel 2002 und in früheren Versionen von Excel wird im ARTIKEL LINEST eine Problemumgehung beschrieben, um die entsprechenden Werte in den letzten drei Zeilen der LINEST-Ausgabetabelle zu generieren. Wenn Sie eine frühere Version von Excel verwenden und das dritte Argument auf LOGEST auf FALSE festlegen möchten, empfehlen wir, dass Sie die Schritte 2 und 3 der vorherigen Prozedur explizit ausführen und dann die Problemumgehung im LINEST-Artikel verwenden, um die letzten drei Zeilen der LINEST-Ausgabetabelle zu ändern.

Probleme treten aufgrund von kollinearen known_x in LOGEST oder LINEST in Excel 2002 und in früheren Excel-Versionen auf. Prädiktorspalten, known_x sind kollinear, wenn mindestens eine Spalte, c, als Summe von Vielfachen anderer (c1, c2 und andere Werte) ausgedrückt werden kann. Spalte c wird als redundant bezeichnet, da die darin enthaltenen Informationen aus den Spalten (c1, c2 und anderen Werten) erstellt werden können. Das Grundprinzip bei Kollinearität besteht darin, dass die Ergebnisse nicht davon beeinflusst werden sollten, ob eine redundante Spalte in die ursprünglichen Daten einbezogen oder aus den ursprünglichen Daten entfernt wird. Da die Version von LINEST in Excel 2002 und in früheren Versionen von Excel nicht nach Kollinearität gesucht hat, wurde dieses Prinzip leicht verletzt. Prädiktorspalten sind fast kollinear, wenn mindestens eine Spalte, c, als nahezu gleich einer Summe von Vielfachen anderer (c1, c2 und andere Werte) ausgedrückt werden kann. In diesem Fall bedeutet "fast gleich" eine sehr kleine Summe der quadrierten Abweichungen von Einträgen in c von entsprechenden Einträgen in der gewichteten Summe von c1, c2 und anderen Werten. Beispielsweise kann "sehr klein" kleiner als 10^(-12) sein.

Führen Sie die folgenden Schritte aus, um die LOGEST-Kollinearität zu veranschaulichen:

  1. Erstellen Sie ein leeres Excel-Arbeitsblatt, und kopieren Sie dann die folgende Tabelle.

  2. Klicken Sie im leeren Excel-Arbeitsblatt auf Zelle A1. Fügen Sie dann die Einträge ein, damit die Tabelle die Zellen A1:N27 in Ihrem Arbeitsblatt ausfüllt.

  3. Nachdem Sie die Tabelle in das neue Excel-Arbeitsblatt eingefügt haben, klicken Sie auf "Einfügeoptionen" und dann auf " Zielformatierung anpassen".

  4. Während der eingefügte Bereich noch ausgewählt ist, verwenden Sie eines der folgenden Verfahren, je nachdem, welche Excel-Version Sie ausführen:

    • Klicken Sie in Excel 2007 auf die Registerkarte "Start", klicken Sie in der Gruppe "Zellen" auf "Format", und klicken Sie dann auf "Spaltenbreite automatisch anpassen".
    • Zeigen Sie in Excel 2003 und früheren Excel-Versionen im Menü "Format" auf "Spalte", und klicken Sie dann auf "Auswahl automatisch anpassen".
A B C D E F G H I J K
Y X
=EXP(A23) 1 2 1
=EXP(A24) 3 4 1
=EXP(A25) 4 5 1
=EXP(A26) 6 7 1
=EXP(A27) 7 8 1
LOGEST mit den Spalten B, C: Werte für Excel 2002 und frühere Versionen von Excel: Werte für Excel 2003 und für spätere Versionen von Excel:
=LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) #NUM! #NUM! #NUM! 1 1.9307233720034 1.26724101129183
=LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) #NUM! #NUM! #NUM! 0 0.043859649122807 0.206652964726136
=LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) #NUM! #NUM! #NUM! 0.986842105263158 0.209426954145848 #N/A
=LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) #NUM! #NUM! #NUM! 225 3 #N/A
=LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) =LOGEST(A2:A6;B2:C6;TRUE;TRUE) #NUM! #NUM! #NUM! 9.86842105263158 0.131578947368421 #N/A
LOGEST nur mit Spalte B
=LOGEST(A2:A6;B2:B6;TRUE;TRUE) =LOGEST(A2:A6;B2:B6;TRUE;TRUE) 1.9307233720034 1.26724101129183 1.9307233720034 1.26724101129183
=LOGEST(A2:A6;B2:B6;TRUE;TRUE) =LOGEST(A2:A6;B2:B6;TRUE;TRUE) 0.0438596491228071 0.206652964726136 0.043859649122807 0.206652964726136
=LOGEST(A2:A6;B2:B6;TRUE;TRUE) =LOGEST(A2:A6;B2:B6;TRUE;TRUE) 0.986842105263158 0.209426954145848 0.986842105263158 0.209426954145848
=LOGEST(A2:A6;B2:B6;TRUE;TRUE) =LOGEST(A2:A6;B2:B6;TRUE;TRUE) 224.999999999999 3 225 3
=LOGEST(A2:A6;B2:B6;TRUE;TRUE) =LOGEST(A2:A6;B2:B6;TRUE;TRUE) 9.86842105263158 0.131578947368421 9.86842105263158 0.131578947368421
Y X
1 1 2
2 3 4
3 4 5
4 6 7
5 7 8
ZEILEN MIT den Spalten B, C: Werte für Excel 2002 und frühere Versionen von Excel: Werte für Excel 2003 und für spätere Versionen von Excel:
=ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) #NUM! #NUM! #NUM! 0 0.657894736842105 0.236842105263158
=ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) #NUM! #NUM! #NUM! 0 0.043859649122807 0.206652964726136
=ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) #NUM! #NUM! #NUM! 0.986842105263158 0.209426954145848 #N/A
=ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) #NUM! #NUM! #NUM! 225 3 #N/A
=ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:C27;WAHR;WAHR) #NUM! #NUM! #NUM! 9.86842105263158 0.131578947368421 #N/A
LINEST nur mit Spalte B
=ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) 0.657894736842105 0.236842105263159 0.657894736842105 0.236842105263158
=ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) 0.0438596491228071 0.206652964726136 0.043859649122807 0.206652964726136
=ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) 0.986842105263158 0.209426954145848 0.986842105263158 0.209426954145848
=ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) 224.999999999999 3 225 3
=ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) =ZEILENABSTAND(A23:A27;B23:B27;WAHR;WAHR) 9.86842105263158 0.131578947368421 9.86842105263158 0.131578947368421

Daten sind in den Zellen A1:C6 enthalten. Einträge in den Zellen D2:D6 sind nicht Teil der Daten. Diese Einträge werden zur Veranschaulichung im nächsten Absatz verwendet. Ergebnisse von zwei verschiedenen Aufrufen von LOGEST werden für Excel 2002 und für frühere Versionen von Excel in den Zellen E8:G20 und für Excel 2003 und für spätere Versionen von Excel in den Zellen I8:K20 angezeigt.

Das erste Modell in den Zeilen 8 bis 13 verwendet die Spalten B und C als Prädiktoren und fordert Excel auf, die Konstante zu modellieren, bei der das dritte Argument auf WAHR festgelegt ist. Excel fügt dann effektiv eine zusätzliche Prädiktorspalte ein, die wie die Zellen D2:D6 aussieht. Beachten Sie, dass Einträge in Spalte C in den Zeilen 2 bis 6 genau der Summe der entsprechenden Einträge in den Spalten B und D entsprechen. Daher tritt die Kollinearität auf, da Spalte C eine Summe von Vielfachen von Spalte B ist und die excel-zusätzliche Spalte 1 eingefügt wird, da das dritte Argument für LOGEST ausgelassen wurde oder WAHR, das den "normalen" Fall darstellt. Diese Kollinearität verursacht numerische Probleme, und Excel 2002 und frühere Versionen von Excel können keine Ergebnisse berechnen, und die LOGEST-Ausgabetabelle ist mit #NUM! gefüllt.

Jede Excel-Version kann das zweite Modell in den Zeilen 15 bis 20 verarbeiten. Kollinearität tritt bei diesem Modell nicht auf, und der Benutzer fordert Excel auf, die Konstante zu modellieren. Dieses Beispiel ist hier aus den folgenden beiden Gründen enthalten:

  • Dieses Beispiel ist die typischste in praktischen Fällen: Es ist keine Kollinearität vorhanden, und das dritte Argument für LOGEST ist entweder WAHR oder wird ausgelassen. Wenn Sie über eine frühere Version von Excel verfügen, treten numerische Probleme im häufigsten praktischen Fall wahrscheinlich nicht auf.
  • Zweitens wird dieses Beispiel verwendet, um das Verhalten von Excel 2003 und neueren Versionen von Excel in den beiden Modellen zu vergleichen. Die meisten wichtigen statistischen Pakete analysieren die Kollinearität, entfernen eine Spalte, bei der es sich um eine Summe von Vielfachen anderer Elemente aus dem Modell handelt, und benachrichtigen Sie mit einer Meldung wie "Spalte C ist linear von anderen Prädiktorspalten abhängig und wurde aus der Analyse entfernt".

In Excel 2003 und in späteren Versionen von Excel erhalten Sie die Informationen in der LOGEST-Ausgabetabelle, nicht in einer Nachricht oder in einer Textzeichenfolge. Ein Regressionskoeffizient, der eins ist und dessen Standardfehler "Null" ist, entspricht einem Koeffizienten für eine Spalte, die aus dem Modell entfernt wurde. (Ein Beispiel finden Sie in den Einträgen in den Zellen I9:I10.) In diesem Fall entfernt LOGEST Spalte C (Koeffizienten in den Zellen I9, J9, K9 entsprechen den Spalten C, B und in der Excel-Konstantenspalte). Wenn die Kollinearität auftritt, kann eine der beteiligten Spalten entfernt werden.

Im zweiten Modell in den Zeilen 16 bis 20 tritt keine Kollinearität auf, und keine der Spalten wird entfernt. Die prognostizierten y-Werte sind in beiden Modellen identisch, da das Entfernen einer redundanten Spalte, die eine Summe von Vielfachen von anderen (das erste und das zweite Modell) ist, die Passform des resultierenden Modells nicht verringert. Solche Spalten werden genau deshalb entfernt, weil sie keinen Mehrwert bei dem Versuch darstellen, die besten Quadrate zu finden. Außerdem sind in der Ausgabe von Excel 2003 und späteren Versionen von Excel in den Zellen I8:K20 die letzten drei Zeilen der Ausgabetabellen identisch, und die Einträge in den Zellen I16:J17 und J9:K10 stimmen überein. Dies zeigt, dass dieselben Ergebnisse erzielt werden, wenn Spalte C im Modell enthalten ist, sich jedoch als redundant (Ausgabe in I9:K13) erwiesen hat, als wenn Spalte C vor der Ausführung von LOGEST eliminiert wurde (Ausgabe in I16:J20). Diese Ausgabe entspricht dem Grundprinzip der Kollinearität.

Collinearity wird in LINEST in Excel 2003 und in späteren Versionen von Excel mithilfe eines völlig anderen Ansatzes, der QR-Zersetzung, identifiziert, um die Regressionskoeffizienten zu lösen. Der ARTIKEL LINEST beschreibt eine exemplarische Vorgehensweise des QR-Zersetzungsalgorithmus für ein kleines Beispiel.

Zusammenfassung der Ergebnisse in früheren Excel-Versionen

LOGEST-Ergebnisse werden in Excel 2002 und in früheren Excel-Versionen durch ergebnisse in nicht korrekten ZEILEN BEEINTRÄCHTIGT.

LINEST hat eine Formel verwendet, die für die Summe der Quadrate nicht korrekt ist, wenn das dritte Argument in LINEST auf FALSE festgelegt ist. Diese Formel führte zu Werten der Regressionssumme von Quadraten, die nicht korrekt sind. Außerdem sind die Werte, die von der Regressionssumme von Quadraten,r-Quadraten und der f-Statistik abhängen, nicht korrekt. (Wenn Sie eine frühere Version von Excel verwenden, lesen Sie die Problemumgehung im LINEST-Artikel.) Daher müssen Benutzer von LOGEST diese Problemumgehung verwenden, wenn sie LOGEST aufrufen, wobei das dritte Argument auf FALSE festgelegt ist.

Unabhängig vom Wert des dritten Arguments wurde LINEST mithilfe eines Ansatzes berechnet, mit dem Kollinearitätsprobleme nicht behoben wurden. Die Kollinearität verursachte Abrundungsfehler, Standardfehler von Regressionskoeffizienten, die nicht angemessen waren, und Freiheitsgrade, die nicht angemessen waren. In einigen Fällen waren Abrundenfehler so schwerwiegend, dass die LINEST-Ausgabetabelle mit #NUM! gefüllt war. LINEST liefert im Allgemeinen akzeptable Ergebnisse, wenn die folgenden Bedingungen zutreffen:

  • Benutzer sind sicher, dass die Prädiktorspalten nicht kollinear (oder fast kollinear) sind.
  • Das dritte Argument für LINEST ist WAHR oder wird ausgelassen.

Daher liefert LOGEST im Allgemeinen akzeptable Ergebnisse, wenn die Prädiktorspalten nicht kollinear (oder fast kollinear) sind und wenn das dritte Argument für LOGEST WAHR ist oder weggelassen wird.

Zusammenfassung der Ergebnisse in Excel 2003 und in späteren Excel-Versionen

Die folgenden Verbesserungen in LINEST wurden vorgenommen:

  • Die Formel für die Summe der Quadrate, bei denen das dritte Argument für LINEST auf FALSE festgelegt ist, wurde korrigiert.
  • Die QR-Analysemethode wird verwendet, um die Regressionskoeffizienten zu bestimmen.

Die QR-Zersetzung hat zwei Vorteile:

  • Bessere numerische Stabilität (oder im Allgemeinen kleinere Abrunden von Fehlern).
  • Analyse von Kollinearitätsproblemen.

Alle Probleme mit Excel 2002 und früheren Excel-Versionen, die in diesem Artikel behandelt werden, wurden in Excel 2003 und in späteren Versionen von Excel behoben.

Schlussfolgerungen

Die Leistung von LOGEST wurde verbessert, da LINEST in Excel 2003 und in späteren Versionen von Excel erheblich verbessert wurde. Wenn Sie eine frühere Version von Excel verwenden, stellen Sie sicher, dass die Prädiktorspalten nicht kollinear sind, bevor Sie LOGEST verwenden. Achten Sie auch darauf, die Problemumgehung zu verwenden, die im LINEST-Artikel dargestellt wird, wenn das dritte Argument für LOGEST auf FALSE festgelegt ist. Obwohl diese Informationen in diesem Artikel und im LINEST-Artikel für Benutzer von Excel 2002 und früheren Versionen von Excel möglicherweise alarmierend erscheinen, ist die Kollinearität in einem kleinen Prozentsatz der Fälle ein Problem. Aufrufe von LOGEST mit dem dritten Argument, das auf FALSE festgelegt ist, sind in der Praxis wahrscheinlich auch relativ selten. Frühere Excel-Versionen liefern akzeptable LOGEST-Ergebnisse, wenn keine Kollinearität vorhanden ist und das dritte Argument von LOGEST WAHR ist oder ausgelassen wird.

Hinweis

Die Verbesserungen in LINEST wirken sich auch auf das lineare Regressionstool von Analysis ToolPak (das LINEST aufruft) und zwei andere verwandte Excel-Funktionen aus: TREND und WACHSTUM.