Ich habe in diesem Blog bereits über lineare Regression gesprochen, aber oft ist eine gerade Linie nicht der beste Weg, um Ihre Daten darzustellen. Für diese spezifischen Situationen können wir einige der verfügbaren Tools nutzen, um nichtlineare Regression oder Kurvenanpassung in Excel durchzuführen.
Erinnerst du dich an unseren alten Freund LINEST? Obwohl LINEST die Abkürzung für „lineare Schätzung“ ist, können wir es auch für nichtlineare Daten mit ein paar einfachen Verbesserungen verwenden.
Nehmen wir an, wir haben einige Daten von Druckabfall vs. Durchflussrate durch ein Wasserventil, und nachdem wir die Daten in einem Diagramm dargestellt haben, sehen wir, dass die Daten quadratisch sind.
Obwohl diese Daten nichtlinear sind, kann die LINEST-Funktion auch hier verwendet werden, um die beste Anpassungskurve für diese Daten zu finden. Für eine Polynomgleichung verwenden wir Array-Konstanten.
Ein Vorteil der Verwendung von LINEST , um die Koeffizienten zu erhalten, die die Polynomgleichung definieren, besteht darin, dass wir die Koeffizienten direkt an Zellen zurückgeben können. Auf diese Weise müssen wir sie nicht manuell aus dem Diagramm übertragen.Da die Gleichung quadratisch oder ein Polynom zweiter Ordnung ist, gibt es drei Koeffizienten, einen für x im Quadrat, einen für x und eine Konstante. Wir müssen also zunächst einen Raum zum Speichern der drei Koeffizienten für die Gleichung erstellen.
Verwenden von LINEST für die nichtlineare Regression in Excel
Die Funktion LINEST gibt ein Array von Koeffizienten und optionale Regressionsstatistiken zurück. Wir müssen es also als Array-Formel eingeben, indem wir alle drei Zellen für die Koeffizienten auswählen, bevor wir die Formel eingeben.
Wenn die Zellen, die die Durchfluss- und Druckdaten enthalten, „Durchfluss“ und „Druck“ heißen, sieht die Formel folgendermaßen aus:
=LINEST(pressure, flow^{1,2},TRUE, FALSE)
Die bekannten y sind in diesem Fall die Druckmessungen und die bekannten x sind die Durchflussmessungen. zur ersten und zweiten Potenz. Die geschweiften Klammern „{“ und „}“ geben eine Array-Konstante in Excel an. Grundsätzlich weisen wir Excel an, zwei Arrays zu erstellen: eines mit flow und eines mit flow-squared und den Druck an beide Arrays anzupassen.
Schließlich weisen die Argumente TRUE und FALSE die LINEST-Funktion an, den y-Achsenabschnitt normal zu berechnen (anstatt ihn auf Null zu setzen) und keine zusätzlichen Regressionsstatistiken zurückzugeben.
Da es sich um eine Array-Formel handelt, müssen wir sie durch Eingabe von Strg + Umschalt + Eingabetaste eingeben.
Die Funktion gibt dann die Koeffizienten von x2 und x sowie eine Konstante zurück (weil wir LINEST die Berechnung des y-Schnittpunkts erlauben).
Die Koeffizienten sind identisch mit denen, die vom Chart Trendline Tool generiert wurden, befinden sich jedoch jetzt in Zellen, was die Verwendung in nachfolgenden Berechnungen erheblich vereinfacht.
Für jede Polynomgleichung gibt LINEST den Koeffizienten für die höchste Ordnung der unabhängigen Variablen ganz links zurück, gefolgt von der nächsthöheren usw. und schließlich der Konstanten.Eine ähnliche Technik kann auch für die exponentielle, logarithmische und Potenzfunktions-Kurvenanpassung in Excel verwendet werden.
Anpassen einer logarithmischen Kurve an Daten
Eine logarithmische Funktion hat die Form:
Wir können LINEST weiterhin verwenden, um den Koeffizienten m und die Konstante b für diese Gleichung zu finden, indem wir ln(x) als Argument für die known_x’s einfügen:
=LINEST(y_values,ln(x_values),TRUE,FALSE)
Natürlich gilt diese Methode für jede logarithmische Gleichung, unabhängig von der Basiszahl. Es könnte also genauso einfach auf eine Gleichung angewendet werden, die log10 oder log2 enthält.
Ermitteln der Koeffizienten einer am besten passenden Exponentialkurve
Eine Exponentialfunktion hat die Form:
Es ist etwas schwieriger, die Koeffizienten a und b für diese Gleichung zu erhalten, da wir zuerst eine kleine Algebra ausführen müssen, damit die Gleichung eine „lineare“ Form annimmt. Nehmen Sie zuerst das natürliche Protokoll beider Seiten der Gleichung, um Folgendes zu erhalten:
Jetzt können wir LINEST verwenden, um ln(a) und b zu erhalten, indem wir ln(y) als Argument für die y_values:
=LINEST(ln(y_values),x_values,TRUE,FALSE)
Der zweite Wert, der von dieser Array-Formel zurückgegeben wird, ist ln(a). Um also nur „a“ zu erhalten, würden wir einfach die Exponentialfunktion verwenden:
Die in Excel , übersetzt zu:
=EXP(number)
Anpassen einer Potenzfunktion an Daten
Eine Potenzfunktionskurve kann mit LINEST auf die gleiche Weise an Daten angepasst werden, wie wir es für eine Exponentialfunktion tun. Eine Potenzfunktion hat die Form:
Auch hier können wir es „linearisieren“, indem wir die Basis 10 log beider Seiten der Gleichung nehmen, um zu erhalten:
Mit der Gleichung in dieser Form kann die LINEST-Funktion, die b und log10(a) zurückgibt, folgendermaßen eingerichtet werden:
=LINEST(LOG10(yvalues),LOG10(xvalues),TRUE,FALSE)
Da die LINEST-Funktion b und log10(a) zurückgibt, müssen wir a mit der folgenden Formel finden:
In Excel lautet diese Formel:
=10^(Zahl)
Das war’s jetzt. Wie Sie sehen, gibt es eine Reihe von Möglichkeiten, die LINEST-Funktion für die nichtlineare Kurvenanpassung in Excel zu verwenden.