EXCEL-Tool zur Polynomregression von Datenpunkten (und Kurvenverläufen)

Hallo Liebe LOGO!-Programmierer,

heute stelle ich euch ein weiteres (makro-freies) Tool auf EXCEL-Basis vor. Neben den "Hinweisen zur Berechnung von Gain und Offset für Analog-Blöcke der LOGO!" incl. EXCEL-Tools und dem neuen EXCEL-Tool zur "Faktorzerlegung für Analogverstärker" habe ich ja bereits in verschiedensten Beiträgen an konkreten Beispielen gezeigt, wie einerseits Berechnungsoptimierungen durch Vermeiden bzw. Verringern von Rundungsfehlern bei der Schaltungskonzeption und der Aufbereitung der "Rohdaten" erfolgen können und was dabei alles zu beachten ist und andererseits wie lineare und auch nicht lineare Zusammenhänge und Kurvenverläufe umgesetzt werden können.

Von besonderem Interesse sind dabei Lösungen zur Umsetzung von nicht linearen Zusammenhängen und Kurvenverläufen mit den Mitteln der LOGO! (0BA6 und folgende). Grundlegende Angaben dazu findet ihr insbesondere in meinen Antworten zum Beitrag "CO2 calculation - Math equations with LOGO!" und dem Dateianhang dort. Wesentliche weitere Beiträge mit Hinweisen und Schaltungen von mir sowie mit weiteren diesbezüglichen LINKS sind u. a. folgende:

- Zeitfunktion in LOGO / mathematische Berechnungen mit der Siemens LOGO
- Astro Uhr (Dateianhang dazu: hier)
- Array von Konstanten (Wertetabelle mit der LOGO)
- Nutzbare Besonderheit beim Block "Komparator"

Das (makro-freie)Tool "PolynomRegressionDynamisch_LOGO_V2.xls" ermöglicht nach Eingabe entsprechender Datenpaare (mindestens 2 bis max. 4000) in ein separates Tabellenblatt die Berechnung von Koeffizienten für ein Ausgleichspolynom, dessen Grad zwischen 1 und 16 liegen darf (für eine Anwendung der LOGO! nur 1 bis max. 4 sinnvoll). Vor der Datenübenahme zur Regression können die y-Daten noch mittels eines Faktors an den Datenbereich der LOGO!-Analogfunktionen angepasst werden. Für die Regression selbst ist die Ordnung des Polynoms und der Wertebereich für den Koeffizienten b festzulegen.

Berechnet werden nun (mittels sog. EXCEL-MATRIX-Funktionen) ein Ausgleichspolynom z' über den gesamten Wertebereich x und (nach entspr echender Festlegung von xa und xe sowie der Polynomparameter) ein weiteres Polynom z1' (bzw. dessen Koeffizienten) für einen Teilwertebereich x von xa bis xe. Mit diesem Polynom z1' ist es euch insbesondere möglich, den Gesamtdatenbereich in Teilbereiche (Teilkurven) zu zerlegen, um so etwa die Gesamtkurve durch zwei oder mehr Teilkurven, jeweils mit kleiner (1 bis 3) Ordnung, anzunähern (und so für die LOGO! nutzbar bzw. programmierbar zu gestalten)!

Die Zahlenformate sind überwiegend an die Bedürfnisse der LOGO! angepasst. Die Basisdaten und ggf. xa und xe bestimmen entscheidend die Koeffizienten. Diese müssen mit der LOGO! nutzbar sein (d. h. max. 2 bzw. 4 Nachkommastellen; ggf. "Faktorzerlegung für Analogverstärker" anwenden; bestimmte Absolutbeträge dürfen nicht überschritten werden; Rundungsfehler durch Ganzzahlenverarbeitung zwischen einzelnen LOGO!-Blöcken). Experimentieren mit der Ordnung, dem Wertebereich x und ggf. dem Verwerfen von einzelnen Datenpaaren kann diesbezüglich von mir nur empfohlen werden, denn die absoluten Werte der Koeffizienten entscheiden letztendlich, ob eine Umsetzung dann mit LOGO!-Mitteln möglich ist oder nicht...

Das Polynom bzw. die (Teil-)polynome werden dann in der LOGO! mittels der Blöcke "Analogverstärker" und "analoge Arithmetik" (und ggf. Kombinationen davon) berechnet. Die Auswahl eines Teilpolynoms erfolgt auf Basis des (aktuellen) Wertes x mittels "Analog-Schwellwertschalter", die einen (oder ggf. auch mehrere) "Multiplexer" ansteuern, so dass am Multiplexerausgang eine "Ergebniskurve" dabei herauskommt (vgl. Beispielschaltungen in den angegebnen Links sowie folgende Schaltungen im Dateianhang hier: "DEMO_Linearisierung_V01.lsc" und "DEMO_Kurveninterpolation (0BA6).lsc").

Zur DEMO habe ich mal als Basisdaten eine Sinus-Kurve vorgegeben, deren y-Werte [y = f(x)] einen Zufallsanteil haben, so dass nach jeder Neuberechnung von EXCEL ein klein wenig modifiziertes Datenmaterial angenähert wird. Wenn ihr die Basis-Datenpaare mit Ihren Werten Überschreibt, dann sind die Basisdaten fixiert!

MfG
Betel

Hallo liebe LOGO!-Anwender,

heute stelle ich euch meine überarbeitete und deutlich erweiterte Version des EXCEl-Tools zur Polynomregression (zur Anwendung mit der LOGO!) vor.

a) Im Tabellenblatt "Regression" wurde die Möglichkeit der Approximation von Teilkurven der Gesamtdaten(paare) von einer Teilkurve z1´ auf 4 Teilkurven z1´bis z4´ erweitert. Alle GRAU unterlegten Zellbereiche lassen USER-Eingaben zu. Die Ergebnisse werden insbesondere in den farbig unterlegten Bereichen rechts von der Gesamttabelle dargestellt. Unterhalb dieser Darstellungen sind noch statistische Angaben zur Qualität der Approximation(en) ergänzt worden.

Im Blatt "Regression" werden die Teilkurven Z´1 bis Z´4 durch geeignetes Festlegen der jeweiligen Werte xa und xe definiert. Habt Geduld die vielen verwendeten Matrixformeln benötigen sehr viel Rechenzeit! Mittels der Diagramme und den Daten unterhalb der Diagramme optimiert ihr nun die Kurvendatenaufteilung bis ihr mit den verbleibenden Abweichungen zufrieden seit. Die Schaltplandatei "DEMO_Teilkurven_eines_Analogbereiches_V01.lsc" demonstriert euch, wie ein Wertebereich (z. B. eines Analogeingangs der LOGO!) in (4) Teilkurven aufgeteilt werden kann.

b) NEU hinzugekommen ist das Tabellenblatt "Regression_LOGO!". Dieses Tabellenblatt übernimmt die Werte aus dem Tabellenblatt "Regression" und berechnet daraus die Parameter der LOGO!-Analogblöcke (incl. Endergebnis) für alle definiertern Wertebereiche. Die Ergebnisse für die Kurven z' sowie Z1' bis z4' werden insbesondere in den farbig unterlegten Bereichen rechts von der Gesamttabelle dargestellt. Unterhalb dieser Ergebnisdaten sind noch statistische Angaben zur Qualität der an die LOGO!-Schaltung angepassten Approximation(en) zusammengestellt. Die Überprüfung des Resultats erfolgt Anhand einer LOGO!-gerechten Berechnung der Analogausgänge jedes erforderlichen Blocks für alle definierten Wertebereiche und Teilpolynome in der linken gegliederten Tabelle.

Dazu achtet darauf, dass stets "mit Formel" als Berechnungsgrundlage in den grauen Feldern steht, denn dann werden die Polynomkoeffifienten mi in die Werte Zi und Ni
gemäß der Gleichungen mi = Zi / Ni berechnet (auf Basis von einfachen Regeln). Die Polynomberechnungen in der linken Datentabelle erfolgen so, wie mit einer LOGO!-0BA6 oder höher, wobei die Polynome so programmiert werden, wie in der Schaltplandatei "DEMO_Polynomberechnung_V01 (0BA6).lsc" angegeben. Die Werte Zi und Ni können dann direkt als Blockparameter angewendet werden.

Anm.: Vergleiche dazu auch meinen Beitrag "Berechnung von Polynomen f(Ax) aus analogen Eingangswerten Ax" und insbesondere die Datei Hinweise_zur_Polynombildung_mit_einer_LOGO_0BA6.pdf

c) Teilweise sind Verbesserungen der Berechnungsqualität durch Modifikation dieser Werte Zi und Ni möglich (vgl. b)). Ihr könnt dies von Hand durch Werteingaben testen ODER für ausgewählte Polynome eine (empirische) Optimierung mittels MAKROs starten. Doch seit gewarnt, denn eine Makro-Ausführung benötigt je Polynom, abhängig von der jeweiligen Polynomordnung sowie den Parameterschätzungen mit Matrix-Formeln, mehrere (!) Stunden für die Optimierung der Parameter (Z, N, Z1, N1, Z2, N2). Daten zur Optimierung mittels Makro werden dann im (ebenfalls neuen) Tabellenblatt "Makro_Ergebnisse" protokolliert.

Wenn man das mit "seinen" Daten macht, dann ist es halb so schlimm wie es sich liest....Mit diesem Tool sind bereits (ansonsten nicht gelöste) Aufgabenstellungen bezüglich nichtlinearer Funktionszusammenhänge von mir erfolgreich (d. h. mit guter Berechnungsqualität) gelöst worden. Beispiele dafür sind meine Version einer "Astro Uhr" für die LOGO!-0BA6 und die "Berechnung der absoluten (Luft-)Feuchte" aus Messwerten von Temperatur und relativer Feuchte.

Auch für die Umsetzung von nichtlinearen Berechnungsfunktionenab der LOGO!-0BA6 durch Approximation mittels Polynomen sind im Forum bereits Beispiele von mir angegeben, so im Beitrag "Komplexes Rechnen". In meiner Antwort dort ist insbesondere auch eine ausführliche Anleitung zum Vorgehen (Datenaufbereitung mit MS-Excel sowie Kurvenaufteilung und Polymomprogrammierung mit der LOGO!) angegeben...

MfG
Betel