Funktionsgleichungen aus Punkten mit Excel ermitteln

Wie ermittle ich eine Funktionsgleichung aus Punkten mit Excel? Das war die Kernfrage mit der ich mich im Artikel „Funktion zur Ermittlung der Produktionsstruktur über Kooperationsgrad“ beschäftigt habe. Zur Berechnung brauchte ich eine Formel für mehrere Kurven aus einem Diagramm. Als Hilfsmittel habe ich die Trendlinie-Funktion genutzt, die genauso eine Formel berechnet und als Funktionsgleichung anzeigt.

Es war aber mühsam, die Punkte für die Kurven über eine Wertetabelle zu ermitteln. Bei jedem Punkt musste ich immer wieder nachjustieren, sodass er wirklich gut auf die Kurve des Original-Diagramms passte. Am Schluss war die beste Variante das Diagramm mit transparentem Hintergrund zu formatieren und über das Original-Diagramm zu schieben. So konnte ich dann besser den Kurvenverlauf kontrollieren.

Eine erste Idee

Das hat mich dann später noch beschäftigt. Irgendwie könnte das doch einfacher gehen. Eigentlich möchte ich doch nur eine Funktionsgleichung aus mehreren Punkten auf einer bestehenden Vorlage nachzeichnen und damit gleich kontrollieren, dass das Ergebnis ziemlich gut auf dem Original liegt.

Zuerst dachte ich in die Richtung, eine UserForm zu nehmen und ein Bild mit dem Diagramm dort zu laden. Man könnte dann mit der Maus mehrere Punkte auf dem Diagramm anklicken und diese speichern und in die Wertetabelle einfügen. Das würde bestimmt funktionieren, es wäre aber aufwändig das visuell zu realisieren.

Die bessere Lösung

Welche Funktionen bringt Excel noch von Haus aus mit, die helfen könnten? Und da war mir klar, ja – das gib es bereits fertig in Excel: eine Freihandlinie. Mit dem Shape können beliebige Punkte gesetzt werden und diese werden zu einer Linie verbunden. Falls ein Punkt nicht genau getroffen hat, ist das auch kein Problem. Die Punkte lassen sich später noch in der Position nachbearbeiten, löschen oder zusätzliche einfügen.

Damit stand das Konzept.

Tabellenblatt mit allen Elementen zur Ermittlung der Funktionsgleichung

Auf einem Tabellenblatt wird alles zusammen gelöst. Da ist das Bild mit dem Original-Diagramm eingefügt. Mit dem Tool Freihandlinie zeichnet man die Kurve auf dem Diagramm nach. Wenn ein Punkt nicht beim ersten Mal gepasst hat, kann man ihn später optimal anpassen. Um die Kurve zu berechnen, werden dann die Punkte über eine VBA Funktion durchlaufen, umgerechnet und in die Wertetabelle eingefügt – fertig.

Nach den ersten Überlegungen war klar, das reicht noch nicht ganz. Es muss auch ein Nullpunkt des Diagramms definiert werden. Da für die Kurve schon ein Shape benutzt wird, warum nicht auch den Nullpunkt mit einem Shape kennzeichnen? Das hörte sich praktikabel an und ich nutzte das gleiche Prinzip auch noch, um die Skalierung der X und Y Achse so zu lösen.

Allerdings ist hier noch die Information über die max. Länge als Wert notwendig. Diese holte ich mir über eine Eingabe in einer Zelle.

Damit klappt das Ermitteln einer Funktionsgleichung aus Punkten mit Excel im Handumdrehen in 7 Schritten:

Hier beliebiges Bild einer Kurve einfügen
Den Kreis auf den Nullpunkt verschieben
Linien für X und Y Achse auf das Achsenkreuz legen und Skalierung einstellen
Werte für max. Längen X und Y in Zellen eintragen
Mit Punkten der Freihandlinie, die Kurve nachbilden
Schaltfläche „Wertetabelle füllen“ anklicken
Formel aus Diagramm kopieren

7 Schritte zum Erstellen der Funktionsgleichung aus Punkten

Die Shapes haben dafür Namen bekommen. Und die VBA Sub greift darauf zu. Daher bitte keine Shapes löschen!

Sub WertetabelleErstellen()
Dim i As Integer
Dim x As Single
Dim y As Single
Dim x0 As Single
Dim y0 As Single
Dim xScale As Single
Dim yScale As Single
Dim pointsArray() As Single
  

  xScale = Sheets("Diagramm").Range("max_x") / Sheets("Diagramm").Shapes("LängeX").Width
  yScale = Sheets("Diagramm").Range("max_y") / Sheets("Diagramm").Shapes("LängeY").Height

  With Sheets("Diagramm").Shapes("Nullpunkt")
    x0 = .Left + .Width / 2
    y0 = .Top + .Height / 2
  End With

  With Sheets("Diagramm").Shapes("Kurve").Nodes
    For i = 1 To .Count
     pointsArray = .Item(i).Points
     x = (pointsArray(1, 1) - x0) * xScale
     y = (pointsArray(1, 2) - y0) * yScale * (-1)
     Sheets("Diagramm").Cells(7, i + 1) = x
     Sheets("Diagramm").Cells(8, i + 1) = y
    Next
  End With
End Sub

So, damit wird jede neue Herausforderung, um eine Formel aus einer Kurve zu bestimmen, zu einer einfachen Fingerübung.

Und der Quellcode ist gar nicht so schwierig.

Wie hätten Sie es gelöst?