Linie glätten

Wer häufiger mit einem Punkt (XY)-Diagramm (engl. XY (scatter) chart) oder einem Verbund-Diagramm, das eine Linie enthält, arbeitet, dürfte die Option “Linie glätten” (engl. “Smoothed line”) kennen. Dass nicht nur ich an Werten dieser Kurve interessiert war, zeigte mir eine Recherche im Internet. Unglücklicherweise bietet weder Excel einen diesbezüglichen Zugang noch offeriert Microsoft eine Dokumentation des Algorithmus!
Den Hinweis, dass Excel dazu möglicherweise einen Kubisch Hermiteschen Spline aus ineinander übergehenden Kubischen Bézierkurven verwendet, verdanke ich Brian T. Murphy, dessen Respekt einflößende Datei ich als Ausgangsbasis meiner 1. Arbeit nahm (Version 1.00). Für die darin vorkommende Notwendigkeit, die Nullstellen eines Polynoms 3. Grades zu finden, bediente ich mich dankenswerterweise bei Excel4Managers, wobei ich die nicht zwingend nötig ausgelagerten Funktionen für die 3. Wurzel und den Arkuskosinus wieder integriert habe.
Der Kritik in einem Forum, dass Murphys Lösung fehlerbehaftet sei (z. B. im Bereich 10 bis 11 für [9;8] [10;12] [11;12] [14;6]), folgte meinerseits ein Tabellenblatt zum Testen aller möglichen Konstellationen für 4 Punkte, was obigen Fehler ausmerzte, aber in weiterer Folge nicht alle Schwächen dieses Ansatzes beseitigte.
User Ihem zeigt in der Microsoft Community einen einfacheren und stimmigeren Ansatz mittels Catmull-Rom-Spline, der auf dem Tabellenblatt “Test” nachvollzogen werden kann (worauf ich nicht näher eingehen möchte). Seinen angeführten Code, der als Algorithmus für bloß einen Punkt bei bekannter Laufvariable taugt, habe ich zu einer praxistauglichen und universellen benutzerdefinierten Funktion SLINE_VAL(Wert;Typ;Sortierreihenfolge;Diagramm;Datenreihe;Endenmodifikation) erweitert (Version 3.00): Man erhält Werte der Schnittpunkte der geglätteten Linie für einen numerischen Wert des Typs 0 oder 1 (optional mit Default = 0 = Abszisse, t, x,… und 1 = Ordinate, f(t), y(x),…) gemäß Sortierreihenfolge (optional mit Default = WAHR für aufsteigend) für eine Datenreihe (optional mit Default = 1) eines Diagramms (optional mit Default = 1) mit Endenmodifikation (optional mit Default = FALSCH; Erklärung siehe Linie glätten 3).
Achtung: SLINE_VAL benötigt die Maßstäbe des Diagramms, das sich aber erst am Schluss aktualisiert, was bei automatischen Achsen-Grenzen zu dann geänderten Maßstäben und somit falschen Ergebnissen führen kann. Deswegen wird im Ereignis “Private Sub Worksheet_Calculate()” des jeweiligen Worksheets zur Sicherheit eine 2. Berechnung erzwungen!
Wer überdies Abszissen oder/und Ordinaten zu einer bestimmten Steigung benötigt (eventuell für Extrema), findet die Lösung im Beitrag Linie glätten 2.
Die folgende Datei bietet einerseits die Möglichkeit, den Algorithmus auf Herz und Nieren zu testen, um bisher unentdeckte Fehler zu finden (bei Verdacht unbedingt auf 400% zoomen), und andererseits die Nutzung der benutzerdefinierten Funktion SLINE_VAL nachzuvollziehen, wobei die anschauliche Animation hilfreich sein dürfte:

Versionstabelle

Linie glaetten
PS: Entdeckte Fehler bitte nicht für sich zu behalten!