Buchvorstellung
Buchcover

Das einzige Mittel, den Irrtum zu vermeiden, ist die Unwissenheit.
Jean-Jacques Rousseau

Vorgehensweise bei der Parameterschätzung mit dem Excel-Werkzeug Solver

Zum Auswerten von Messdaten und –reihen gibt es verschiedene Softwarepakete. Im Hochschulbereich hat sich beispielsweise der Einsatz von Matlab durchgesetzt. In der freien Wirtschaft wird man die Kosten für Produkte dieser Art eher scheuen, da der direkte Nutzen für gängige Aufgabenstellung nicht gegeben ist. Auch die Anschaffung problembezogener Spezialsoftware kommt nur selten in Frage, mit denen (Sonder-)Aufgaben gelöst werden könnten.

Ein universelles und nahezu auf jedem Computer verfügbares Softwarepaket ist Microsoft Office und das darin enthaltene Tabellenkalkulationsprogramm Excel. Mithilfe von Excel lassen sich neben einfachen Berechnungen auch komplexere Matrizenoperationen durchführen. Die vielfältigen Anwendungsmöglichkeiten führen dazu, dass der Nutzer mit der grundlegenden Struktur dieses Programms i.A. vertraut ist und eine größere Einarbeitungszeit entfällt.

Excel verfügt mit dem sogenannten Solver auch über ein Optimierungswerkzeug zum Lösen von Gleichungen. Ist der funktionale Zusammenhang zwischen den gesuchten Unbekannten und den Beobachtungen bekannt, so kann mithilfe des Solver-Add-ins die Lösung bestimmt werden. Es lassen sich somit problemlos Ausgleichungsaufgaben lösen, wobei idealerweise das mitunter komplizierte Linearisieren durch Bilden der partiellen Ableitungen entfällt. An einigen Beispielen soll das Add-in demonstriert werden. Für eine detailliertere Betrachtung sei auf (Staiger, 2007) verwiesen.


Einrichtung des Solvers und prinzipielle Anwendung an einem einfachen Beispiel

Wurde der Solver bisher noch nie benutzt, so ist er einmal zu laden. Hierzu ist in Excel einfach der entsprechende Haken im Add-in Menü zu setzen, wie nachfolgender Screenshot zeigt.


Excel-Optionen: Add-ins zum Hinzufügen des Optimierungswerkzeugs Solver
Add-ins zum Hinzufügen des Werkzeugs Solver

Das Menü ist in Office2007 unter den Excel-Optionen zu erreichen, die über die Office-Schaltfläche oben links zu finden sind. Das Optimierungswerkzeugt steht anschließend sofort im Daten-Menü zur Verfügung.

An einem Beispiel soll nun die generelle Vorgehensweise in Excel gezeigt werden, wie sich die Unbekannten durch eine vorliegende Beobachtungsreihe schätzen lassen. Gewählt wird ein einfaches Beispiel, welches (Großmann, 1969) entnommen ist. Bestimmt werden sollen Stand und Gang einer Pendeluhr, die sich aus den Koeffizienten a, b und c einer nicht-linearen Funktion f ableiten lassen.


Zielfunktion
Zielfunktion

Zur Bestimmung der Koeffizienten dieser Funktion liegen 13 Messungen vor, die in den ersten beiden Spalten einer Tabelle in Excel zusammengefasst wurden, vgl. nachfolgende Abbildung.


Excel Solver: Einstellungen zur Parameterschätzung
Excel Solver: Einstellungen zur Parameterschätzung

In der dritten Spalte sind die Funktionswerte f(Xi) eingetragen, die sich aus den Initialisierungsparametern a=b=c=0 (siehe rechte Tabelle unter dem Plot), zunächst ergeben. In den nachfolgenden Spalten sind die Verbesserungen vi und das Quadrat dieser Verbesserungen aufgelistet. Die jeweilige Verbesserung vi ergibt sich dabei aus:


Verbesserungsgleichung
Verbesserungsgleichung

Zu schätzen sind somit die Parameter a, b und c der Funktion, die bestmöglich zu den erhobenen Daten passt. Um eine bessere Vorstellung über die Zielfunktion zu erhalten, ist die Messreihe in der o.g. Abbildung geplottet. Zur Parameterschätzung wenden wir die auf Gauß zurückgehende Methode der kleinsten Quadrate, bei der die Summe der quadrierten Verbesserungen minimiert wird, an. Unsere Zielfunktion ist somit die Spaltensumme der letzten Spalte in der o.g. Tabelle und beträgt vor der Ausgleichung [vv] = 1709,208.

Im aufgerufenen Solver ist diese Zielfunktion, die wir minimieren wollen (Zielwert: Min), in der Zielzelle einzutragen. Darüber hinaus sind die drei Spalten, die die gesuchten Parameter a, b und c repräsentieren, als Veränderbare Zellen hinzuzufügen und anschließend die Parameterschätzung durch Lösen zu starten.


Lösungen der einzelnen Iterationsschritte
Lösungen der einzelnen Iterationsschritte

Bereits nach wenigen Iterationen liefert der Solver mit [vv] = 0,053 die Lösung, die auch im (Großmann, 1969) zu finden ist. Betrachtet man die einzelnen Berechnungsschritte, so ist zu erkennen, das bereits nach der vierten Iteration die gefundenen Parameter a, b und c der Zielfunktion f gut zu den erhobenen Daten passt.


Ergebnis der Parameterschätzung mittels Solver
Ergebnis der Parameterschätzung mittels Solver

Die gesuchten Parameter werden automatisch nach jeder Iteration in der Tabelle überschrieben, sodass wir letztlich dort auch die endgültige Lösung finden. Das Abbruchkriterium für die Parameterschätzung kann im Solver problembezogen angepasst werden. (Staiger, 2007) schlägt für Ingenieuraufgaben eine engere Schranke von 1E-8 und kleiner vor, um zufriedenstellende Lösungen zu erhalten. Diese Grenzwerte können vor dem Lösen in den Solver-Optionen gesetzt werden.


Solver-Optionen
Solver-Optionen

Schätzung der Parameter bei groben Fehlern im Datenmaterial

Die Parameterschätzung mittels des Excel Add-ins Solver wurde im zurückliegenden Kapitel anschaulich demonstriert. Der Solver ist dabei jedoch nicht auf die Methode der kleinsten Quadrate beschränkt, sodass auch eine Lösung der Absolutsumme der Verbesserungen (L1-Norm) denkbar ist. Bei der Absolutsumme der Verbesserungen handelt es sich um ein robustes Schätzverfahren, welches im Gegensatz zur L2-Norm (Methode der kleinsten Quadrate) weitgehend unempfindliche gegenüber groben Messfehlern ist (vgl. Wicki, 1998, Jäger et al., 2005 oder Niemeier, 2008).

Zur Demonstration werden beim Beispiel des letzten Kapitels drei grobe Fehler eingebaut und die Schätzung von a, b und c wie beschrieben erneut durchgeführt. Nachfolgende Abbildung zeigt die geänderten Rohdaten und die mit dem robusten Schätzverfahren erzielten Ergebnisse. Ein Vergleich mit den oben bestimmten Parametern zeigt, dass die drei groben Messfehler kaum Einfluss auf die Schätzung hatten.


Parameterschätzung mittels Solver durch eine robuste Schätzung der Absolutsumme der Verbesserungen (L1-Norm)
Parameterschätzung mittels Solver durch eine robuste Schätzung der Absolutsumme (L1-Norm)

Die modifizierten Beobachtungen lassen sich ferner problemlos an ihren auffällig großen Verbesserungen problemlos lokalisieren. Zum Vergleich nachfolgend auch die Lösung der nicht robusten Schätzung durch Quadratsummenminimierung, bei der eine deutliche Abweichung von der ursprünglichen Funktion zu verzeichnen ist, was besonders deutlich am Plot wird.


Parameterschätzung mittels Solver durch Quadratsummenminimierung (L2-Norm)
Parameterschätzung mittels Solver durch Quadratsummenminimierung (L2-Norm)

Natürlich ist auch das Black-Box System Solver nicht unfehlbar bei der Parameterschätzung. Die Lösung ist daher in jedem Fall kritisch zu prüfen! Verschiedene Möglichkeiten, Maßnahmen bei Konvergenzproblemen und weitere Beispiele sind (Staiger, 2007) zu entnehmen.


Zusammenfassung und Download

Im Unterschied zum einfachen Anwender soll(te) der Ingenieur auch in der Lage sein, Problemstellungen nicht standardisierter Aufgaben zu lösen. Dass es hierfür nicht immer zwingend etwas im Umfang der bspw. Optimization Toolbox von Matlab sein muss, sondern auch Excel sich grundsätzlich für Optimierungsprobleme eignet, wurde versucht beispielhaft zu verdeutlichen. Dem Praktiker ist damit ein universelles Handwerkszeug gegeben, Optimierungsaufgaben zu lösen. Eine Sachgerechte Beurteilung der Lösung und das damit nötige Hintergrundwissen darf jedoch nicht fehlen, um die Ergebnisse richtig einzuordnen!

Auch in der freien Office Suite OpenOffice von Sun existiert ein Solver. Dieser ist jedoch derzeit beschränkt auf lineare Probleme. Eine Erweiterung auf nicht-lineare Aufgabenstellung ist in Arbeit, sodass er in diesem Beitrag unberücksichtigt blieb.

Die für diesen Beitrag genutzte Excel-Tabelle steht zu Demonstrationszwecken zur freien Verfügung.

Wie grundsätzlich - auch ohne EXCEL - ein robustes Schätzverfahren arbeitet, kann bei der Vorstellung der robusten Parameterschätzung mit dem Least-Median-Square (LMS) am Beispiel Kreis nachgelesen werden.

Excel-Tabelle


Quellen

Verwendete Literatur, die nicht der Bibliothek entnommen ist:

  • Staiger, R. (2007), Numerische Optimierung als Werkzeug zur Ausgleichungsrechnung? Eine praktische Anleitung für MS-Excel, Allgemeine Vermessungs-Nachrichten (AVN)

11.12.2009 von Michael Lösler