Statistische Excel-Funktionen: LOGEST

Zusammenfassung

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

LOGEST wird durch Aufrufen der zugehörigen Funktion LINEST ausgewertet. Umfangreiche Änderungen an LINEST für Excel 2003 und für höhere Excel-Versionen werden zusammengefasst, und ihre Auswirkungen auf LOGEST sind aufgeführt.

Informationen zu Microsoft Excel 2004 für Macintosh

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

Weitere Informationen

Die FUNKTION LOGEST(known_y, known_x, constant, statistics) wird verwendet, um eine Regression durchzuführen, bei der eine exponentielle Kurve angepasst wird. Es wird ein Kriterium mit den geringsten Quadraten verwendet, und LOGEST versucht, die beste Eignung für dieses Kriterium zu finden. Known_y stellt Daten für die abhängige Variable dar, und known_x stellt Daten für eine oder mehrere unabhängige Variablen dar. Das zweite Argument ist optional. Wenn es nicht angegeben wird, wird davon ausgegangen, dass es sich um ein Array mit der gleichen Größe wie known_y handelt, das die Werte (1, 2, 3, ...) enthält.

Unter der Annahme, dass p-Vorhersagevariablen vorhanden sind, passt LOGEST zu einer Gleichung der folgenden Form:

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

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

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

Wenn das dritte Argument "constant" auf TRUE festgelegt ist, soll das Regressionsmodell die Konstante b in die vorherige Formel in sein Modell einschließen. Wenn sie auf FALSE festgelegt ist, wird die multiplikative Konstante b aus dem Regressionsmodell ausgeschlossen, indem sie gleich eins erzwingt. Das dritte Argument ist optional. Wenn sie nicht angegeben wird, wird sie als TRUE interpretiert.

Gehen Sie in diesem Artikel davon aus, dass daten in Spalten so angeordnet sind, dass known_y eine Spalte mit y-Daten und known_x eine oder mehrere Spalten mit x-Daten ist. Die Dimensionen oder Längen jeder dieser Spalten müssen gleich sein. Alle folgenden Beobachtungen sind gleichermaßen zutreffend, wenn die Daten nicht in Spalten angeordnet sind, aber es ist einfacher, den am häufigsten verwendeten Fall zu besprechen. Gehen Sie außerdem davon aus, dass das letzte Argument für LOGEST immer TRUE ist und Sie immer an einer detaillierten Ausgabe interessiert sind. Diese Annahme hilft, numerische Probleme in früheren Versionen von Excel aufzudecken. Einige numerische Probleme sind weiterhin vorhanden, wenn Sie FALSE für dieses Argument verwenden.

In diesem Artikel werden die folgenden Beispiele verwendet, um zu zeigen, wie LOGEST mit LINEST zusammenhängt, und um auf Probleme mit LINEST in früheren Versionen von Excel hinzuweisen, die zu Problemen mit LOGEST führen. Während der Code für LOGEST für Excel 2003 und für spätere Versionen von Excel nicht 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 bescheid wissen. Weitere Informationen zu LINEST finden Sie, indem Sie auf die folgende Artikelnummer klicken, um den Artikel in der Microsoft Knowledge Base anzuzeigen:

828533 Beschreibung der LINEST-Funktion in Excel 2003 und höheren Excel-Versionen

Die LINEST-Hilfedatei wurde auch für Excel 2003 und für spätere Versionen von Excel überarbeitet und ist für weitere Informationen nützlich. 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 mit verwandten Dimensionen sein. Wenn known_y eine Spalte für m Zeilen ist, sollten known_x c-Spalten nach m Zeilen sein, wobei c größer als oder gleich 1 ist. C ist die Anzahl der Vorhersagevariablen; m ist die Anzahl der Datenpunkte. Ähnliche Beziehungen müssen enthalten sein, wenn known_y in einer einzelnen Zeile angeordnet ist. in diesem Fall sollte sich known_x in r-Zeilen befinden. (R muss größer oder gleich 1 sein.) Konstante und Statistiken sind logische Argumente, die auf TRUE oder FALSE festgelegt werden müssen. In Excel müssen diese Argumente auf 0 oder 1 festgelegt werden und werden von Excel als FALSE bzw. TRUE 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 TRUE interpretiert. Wenn Sie das vierte Argument weglassen, wird es als FALSE 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 mehr als eine Vorhersagevariable gibt, enthält das zweite Argument in diesem Beispiel mehrere Spalten. In diesem Beispiel gibt es 100 Themen, 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 wichtigen Konzepte zu veranschaulichen:

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

Weitere Informationen zum zweiten und dritten Konzept in dieser Liste finden Sie im folgenden Artikel der Microsoft Knowledge Base:

828533 Beschreibung der LINEST-Funktion in Excel 2003 und höheren 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 die Tabelle später in diesem Abschnitt.

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

3. Nachdem Sie die Tabelle in Ihr neues Excel-Arbeitsblatt eingefügt haben, klicken Sie auf Einfügeoptionen und dann auf Zielformatierung übereinstimmen.

4. Während der eingefügte Bereich noch ausgewählt ist, verwenden Sie eines der folgenden Verfahren, die für die von Ihnen ausgeführte Excel-Version geeignet ist:

   • Klicken Sie in Microsoft Office 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.

Die Daten für LOGEST befinden sich in den Zellen A4:B6. Detaillierte Ergebnisse für Excel 2002 und frühere Versionen von Excel und für Excel 2003 und für spätere Versionen von Excel werden in den Zellen D4:E8 bzw. G4:H8 dargestellt. Die Zellen A11:B13 weisen die gleichen known_x auf, aber die known_y in den Zellen A4:A6 wurden mithilfe der Excel-LN-Funktion mithilfe ihres natürlichen Logarithmus transformiert. Für diese transformierten Daten wird dann LINEST 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 LINIENT-Koeffizienten in den Zellen werden im Wesentlichen mithilfe der Excel EXP-Funktion transformiert, um die LOGEST-Koeffizienten in den Zellen G4:H4 zu berechnen. Sie können diese Beziehung mithilfe der EXP-Funktion in den Zellen G17:H17 überprüfen. Um zusammenzufassen, wie LOGEST und LINEST interagieren, beachten Sie die folgende Abfolge von Schritten:

1. Sie rufen LOGEST(known_y,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 LINEST-Koeffizienten in der ersten Zeile der LINEST-Ergebnistabelle nach Exponentiation. Ersetzen Sie beispielsweise jeden LINEST-Koeffizient (m) durch EXP(m).
5. LOGEST gibt diese geänderte LINEST-Ergebnistabelle als LOGEST-Ergebnistabelle an Sie zurück.

Wenn LOGEST geeignete Ergebnisse zurückgeben soll, muss LINEST in Schritt 3 die entsprechenden Ergebnisse generieren. Untersuchen Sie die Zellen D13:D15. Zelle D13 enthält einen r-Quadratwert, Zelle D14 eine f-Statistik und Zelle D15 enthält die LinesT-Regressionssumme der Quadrate.

Wenn IN Excel 2002 und früheren Excel-Versionen LINEST aufgerufen wird, wobei das dritte Argument auf FALSE festgelegt ist, wird 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 höheren 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 früheren Excel-Versionen beschreibt der ARTIKEL ZEILENT eine Problemumgehung, 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, empfiehlt es sich, die Schritte 2 und 3 der vorherigen Prozedur explizit auszuführen und dann die Problemumgehung im Artikel LINEST zu verwenden, um die letzten drei Zeilen der AUSGABEtabelle LINEST zu ändern.

Probleme treten aufgrund kollinearer known_x in LOGEST oder LINEST in Excel 2002 und früheren Excel-Versionen auf. Vorhersagespalten, known_x sind kollinear, wenn mindestens eine Spalte, c, als Summe von Vielfachen anderer (c1, c2 und anderer 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 grundlegende Prinzip beim Vorhandensein von Kollinearität besteht darin, dass ergebnisse nicht davon beeinflusst werden sollten, ob eine redundante Spalte in den ursprünglichen Daten enthalten 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 suchte, wurde dieses Prinzip leicht verletzt. Vorhersagespalten sind nahezu kollinear, wenn mindestens eine Spalte , c, fast gleich einer Summe von Vielfachen anderer (c1, c2 und anderer Werte) ausgedrückt werden kann. In diesem Fall bedeutet "fast gleich" eine sehr kleine Summe von quadratischen 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 in Ihrem leeren Excel-Arbeitsblatt auf Zelle A1. Fügen Sie dann die Einträge so ein, dass die Tabelle die Zellen A1:N27 im Arbeitsblatt ausfüllt.

3. Nachdem Sie die Tabelle in Ihr neues Excel-Arbeitsblatt eingefügt haben, klicken Sie auf Einfügeoptionen und dann auf Zielformatierung übereinstimmen.

4. Während der eingefügte Bereich noch ausgewählt ist, verwenden Sie eines der folgenden Verfahren, die für die von Ihnen ausgeführte Excel-Version geeignet ist:

   • 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.

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. Die Ergebnisse von zwei verschiedenen Aufrufen von LOGEST werden für Excel 2002 und für frühere Excel-Versionen 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 TRUE festgelegt ist. Excel fügt dann effektiv eine zusätzliche Vorhersagespalte ein, die genau wie die Zellen D2:D6 aussieht. Beachten Sie, dass Einträge in Spalte C in den Zeilen 2 bis 6 genau gleich der Summe der entsprechenden Einträge in den Spalten B und D sind. Daher tritt die Kollinearität auf, weil Spalte C eine Summe aus Vielfachen von Spalte B ist und die excel-zusätzliche Spalte 1 eingefügt wird, weil das dritte Argument für LOGEST ausgelassen wurde, oder TRUE, das die "normale" Groß-/Kleinschreibung ist. 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 Version von Excel kann das zweite Modell in den Zeilen 15 bis 20 verarbeiten. Bei diesem Modell tritt keine Kollinearität auf, und der Benutzer fordert Excel auf, die Konstante zu modellieren. Dieses Beispiel ist hier aus den folgenden zwei Gründen enthalten:

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

In Excel 2003 und höheren Excel-Versionen erhalten Sie die Informationen in der LOGEST-Ausgabetabelle, nicht in einer Nachricht oder in einer Textzeichenfolge. Ein Regressionskoeffizienten, 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 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 vorhergesagten y-Werte sind in beiden Modellen identisch, da das Entfernen einer redundanten Spalte, die eine Summe von Vielfachen anderer Ist (das erste und zweite Modell) ist, die Eignung des resultierenden Modells nicht beeinträchtigt. Solche Spalten werden genau deshalb entfernt, weil sie keinen Mehrwert bei dem Versuch darstellen, die am wenigsten geeigneten Quadrate zu finden. Außerdem sind in der Ausgabe von Excel 2003 und höheren Excel-Versionen in den Zellen I8:K20 die letzten drei Zeilen der Ausgabetabellen identisch, und die Einträge in den Zellen I16:J17 und den Zellen J9:K10 stimmen überein. Dies zeigt, dass die gleichen Ergebnisse erzielt werden, wenn Spalte C im Modell enthalten ist, aber redundant ist (Ausgabe in I9:K13), als Spalte C vor der Ausführung von LOGEST entfernt wurde (Ausgabe in I16:J20). Diese Ausgabe erfüllt das Grundprinzip bei Vorhandensein von Kollinearität.

Die Kollinearität wird in LINEST in Excel 2003 und in späteren Excel-Versionen mithilfe eines völlig anderen Ansatzes, der QR-Analyse, identifiziert, um die Regressionskoeffizienten zu lösen. Der LINEST-Artikel beschreibt eine exemplarische Vorgehensweise des QR-Analysealgorithmus für ein kleines Beispiel.

Zusammenfassung der Ergebnisse in früheren Versionen von Excel

LOGEST-Ergebnisse werden in Excel 2002 und in früheren Excel-Versionen durch ergebnisse in ZEILENT beeinträchtigt, die nicht genau sind.

LINEST hat eine Formel verwendet, die für die Gesamtsumme 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 der Quadrate (r squared und f) abhängen, nicht korrekt. (Wenn Sie eine frühere Version von Excel verwenden, finden Sie weitere Informationen zur Problemumgehung im Artikel LINEST.) 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, der keine Kollinearitätsprobleme behandelte. Kollinearität verursachte Rundungsfehler, Standardfehler von Regressionskoeffizienten, die nicht geeignet waren, und Freiheitsgrade, die nicht geeignet waren. In einigen Fällen waren Rundungsfehler so schwerwiegend, dass die LINEST-Ausgabetabelle mit #NUM! gefüllt wurde. LINEST liefert im Allgemeinen akzeptable Ergebnisse, wenn die folgenden Bedingungen zutreffen:

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

Daher liefert LOGEST im Allgemeinen akzeptable Ergebnisse, wenn die Vorhersagespalten nicht kollinear (oder fast kollinear) sind und wenn das dritte Argument für LOGEST TRUE ist oder ausgelassen wird.

Zusammenfassung der Ergebnisse in Excel 2003 und höheren Versionen von Excel

Die folgenden Verbesserungen wurden in LINEST vorgenommen:

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

Die QR-Analyse hat zwei Vorteile:

• Bessere numerische Stabilität (oder im Allgemeinen kleinere Rundungsfehler).
• 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 höheren Versionen von Excel behoben.

Schlussfolgerungen

Die Leistung von LOGEST wurde verbessert, da LINEST in Excel 2003 und späteren Excel-Versionen erheblich verbessert wurde. Wenn Sie eine frühere Version von Excel verwenden, überprüfen Sie, ob die Vorhersagespalten nicht kollinear sind, bevor Sie LOGEST verwenden. Achten Sie außerdem darauf, die Problemumgehung zu verwenden, die im Artikel LINEST beschrieben wird, wenn das dritte Argument auf 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 beunruhigend erscheinen, ist Kollinearität in einem kleinen Prozentsatz der Fälle ein Problem. Aufrufe von LOGEST mit dem dritten Argument, das auf FALSE festgelegt ist, sind wahrscheinlich auch relativ selten in der Praxis. Frühere Excel-Versionen liefern akzeptable LOGEST-Ergebnisse, wenn keine Kollinearität vorliegt und das dritte Argument von LOGEST TRUE ist oder ausgelassen wird.