Polynome und Trendlinien in Excel

Kürzlich stand ich vor der Aufgabe, mehrere Messwerte durch ein Polynom n-ten Grades anzunähern. Excel bietet dazu einige Möglichkeiten, die ich im folgenden vorstelle.

Polynomische Trendlinie

Die einfachste Lösung bietet das “Trendlinien”-Werkzeug: x-y-Diagramm erstellen, Rechtsklick auf Daten > Trendlinie hinzufügen > polynomisch auswählen, Grad einstellen, fertig. Wenn man jetzt noch Formel im Diagramm anzeigen anwählt, kann man so die Koeffizienten des Polynoms ablesen.

Vorteil: WYSIWYG-Methode
Nachteil: Diagramm erforderlich, Koeffizienten des Polynoms können nicht automatisch zur weiteren Berechnung ausgelesen werden.

Polynom mit Hilfe der RGP-Funktion

Wenn man die Koeffizienten zum Weiterrechnen benötigt, hilft die Funktion RGP weiter. Diese eignet sich (entgegen der Kurzbeschreibung in Excel) nicht nur für lineare, sondern auch für polynomische Trends. Beispiel für ein Polynom dritten Grades mit den Koeffizienten k₀ bis k₃:

k0 = INDEX(RGP([x-werte];[y-werte]^{1.2.3});1;4)
k1 = INDEX(RGP([x-werte];[y-werte]^{1.2.3});1;3)
k2 = INDEX(RGP([x-werte];[y-werte]^{1.2.3});1;2)
k3 = INDEX(RGP([x-werte];[y-werte]^{1.2.3});1)

Quelle: Chart Trendline Formulas

Vorteile: schnell, Koeffizienten stehen für weitere Berechnungen zur Verfügung
Nachteile: Fehler bei “Löchern” in den x- oder y-Wertereihen, Formel muss für jeden Koeffizienten per Hand angepasst werden (kein Copy-Paste möglich)

Polynom durch Lösen eines linearen Gleichungssystems

Weitere Möglichkeiten (z.B. Vorgabe der Ableitung an einem Punkt) erhält man durch Lösen eines linearen Gleichungssystems. Dazu muss jeder Punkt p(x) an der Stelle x die Gleichung

p(x) = k₀ + k₁ * x + … + k_n * xⁿ

erfüllen, zur Bestimmung der Koeffizienten k₀ bis k_n werden also n+1 Punkte benötigt, an denen p(x) oder eine Ableitung vorgegeben wird.

Die Lösung dieses linearen Gleichungssystems ist in Excel problemlos möglich, auch wenn die Formeln etwas kryptisch anmuten. Genaueres dazu im angehängten Beispiel-Arbeitsblatt Polynome und Trendlinien.

Weitere Infos

• Lösen von linearen Gleichungssystemen in Excel als Matrix
• Excelformeln: Lösen eines linearen Gleichungssystems

Vorteile: flexible Methode, genaue Anpassung des Polynoms (z.B. Vorgabe von Minima/Maxima) möglich
Nachteile: Theorie etwas anspruchsvoller, kryptische Formeln