Verwendung des Excel-Solvers

Zusammenfassung

In den vorangegangenen Kapiteln haben wir den Umfang der mathematischen Modelle immer so weit eingeschränkt, dass sie noch mit Handrechnung zu lösen waren. Um aber die vorgestellten Algorithmen effizient auf größere Problemstellungen anwenden zu können, ist der Einsatz von Computern unerlässlich. Dass man dabei durchaus auch auf Standardprogramme zurückgreifen kann, zeigt dieses Kapitel.

Ein nützliches Hilfsmittel zur Lösung Linearer Probleme bietet das weitverbreitete Tabellenkalkulationsprogramm Excel der Firma Microsoft (vgl. www.microsoft.de). Der sogenannte Solver, ein Bestandteil des Excel-Programms, enthält alle dazu benötigten Routinen und hat sich als stabil und flexibel erwiesen. Andere Tabellenkalkulationsprogramme mit entsprechenden Optimierungs-Add-Ins funktionieren im Prinzip ähnlich, sind jedoch weniger im Einsatz.

In den vorangegangenen Kapiteln haben wir den Umfang der mathematischen Modelle immer so weit eingeschränkt, dass sie noch mit Handrechnung zu lösen waren. Um aber die vorgestellten Algorithmen effizient auf größere Problemstellungen anwenden zu können, ist der Einsatz von Computern unerlässlich. Dass man dabei durchaus auch auf Standardprogramme zurückgreifen kann, zeigt dieses Kapitel.

Ein nützliches Hilfsmittel zur Lösung Linearer Probleme bietet das weitverbreitete Tabellenkalkulationsprogramm Excel der Firma Microsoft (vgl. www.microsoft.de). Der sogenannte Solver, ein Bestandteil des Excel-Programms, enthält alle dazu benötigten Routinen und hat sich als stabil und flexibel erwiesen. Andere Tabellenkalkulationsprogramme mit entsprechenden Optimierungs-Add-Ins funktionieren im Prinzip ähnlich, sind jedoch weniger im Einsatz.

Entwickelt wurde der Solver von der Firma Frontline Systems (siehe www.frontsys.com bzw. www.solver.com), die auf ihrer Internetseite weitere Informationen über die technischen Einzelheiten des Programms sowie ein kleines Tutorium in englischer Sprache anbietet. Weitere Beispiele zur Verwendung des Solvers liegen dem Excel-Programm mit der Datei SOLVSAMP.XLS bei, die dem Leser zum Selbststudium empfohlen seien. Mit dem Excel-Solver lassen sich nur Probleme bis zu einer gewissen Größe lösen, Details darüber findet man ebenfalls unter den angegebenen Internetadressen.

Wie der Solver anzuwenden ist, soll in den folgenden Abschnitten anhand von Beispielen demonstriert werden. Wir setzen dabei einige elementare Grundkenntnisse in Excel voraus, wie z. B. das Anlegen einer Tabelle und die Eingabe von Werten und Funktionen. Wer noch niemals mit Excel gearbeitet hat, der möge sich zunächst mit diesem Basiswissen vertraut machen. Wir verwenden die Version Excel 2013, die ein Bestandteil des Programmpakets Microsoft Office Professional Plus 2013 ist. Der Solver ist jedoch auch in älteren Programmversionen (ab dem Jahr 1990) enthalten.

10.1 Der Excel-Solver für Lineare Programme

In diesem Abschnitt erläutern wir, wie man ein einfaches lineares Optimierungsproblem mithilfe des Excel-Solvers löst. Um die Handhabung des Programms zu demonstrieren, greifen wir auf das Beispiel 3.1 aus Kap. 3 zurück. Zu maximieren ist

$$\displaystyle z=F(x_{1},x_{2})=10x_{1}+40x_{2}$$

unter den Nebenbedingungen

$$\begin{aligned}\displaystyle 40x_{1}+24x_{2}&\displaystyle\leq 480,\\ \displaystyle 24x_{1}+48x_{2}&\displaystyle\leq 480,\\ \displaystyle 60x_{2}&\displaystyle\leq 480,\\ \displaystyle x_{1},x_{2}&\displaystyle\geq\phantom{48}0.\end{aligned}$$

Wir öffnen zunächst ein neues Arbeitsblatt, in das wir die erforderlichen Daten eintragen. Die Gestaltung dieses Arbeitsblatts ist weitgehend frei, eine Möglichkeit zeigt die Abb. 10.1. Wichtig sind in dem Beispiel die Felder C15 und C16 für die Werte der Variablen $x_{1}$ und $x_{2}$, hier soll später die Lösung des Problems stehen. Wir programmieren die Tabelle nun so, dass eine Veränderung der Werte für $x_{1}$ und $x_{2}$ ebenfalls eine Veränderung des Zielfunktionswertes $z$ (Zelle C18) und der Werte für die Restriktionsgleichungen

$$\begin{aligned}\displaystyle N_{1}&\displaystyle=40x_{1}+24x_{2},\\ \displaystyle N_{2}&\displaystyle=24x_{1}+48x_{2},\\ \displaystyle N_{3}&\displaystyle=60x_{2},\end{aligned}$$

also der Zellen C20, C21 und C22, bewirkt. Die verwendeten Formeln sind in Abb. 10.1 angegeben. Zu Testzwecken sollte man nun verschiedene Werte für $x_{1}$ und $x_{2}$ eingeben und die Veränderungen der davon abhängigen Zellen beobachten.

Abb. 10.1

Ausgangstableau für das LP

Ist das Arbeitsblatt aufgebaut, müssen noch die Parameter für den Solver eingestellt werden. Hierzu öffnen wir zunächst über das Menü Daten den Solver und erhalten dann die Möglichkeit, die nötigen Angaben zu machen (siehe Abb. 10.2 ). Falls sich der Punkt Solver nicht in dem Menü Daten befindet, sollte unter dem Menüpunkt Datei $>$ Optionen $>$ Add-Ins zunächst überprüft werden, ob die entsprechende Checkbox für den Solver markiert ist. Gegebenenfalls muss der Solver noch nachinstalliert werden. Näheres hierzu findet man in der Dokumentation zu Excel.

Abb. 10.2

Solver-Parameter für das LP

Durch Anklicken oder durch Tastatureingabe bestimmen wir in dem Fenster Solver-Parameter zuerst die Zielzelle, in unserem Beispiel ist dies die Zelle C18. Wir wählen Max aus, um den Wert in dieser Zelle zu maximieren. Danach legen wir die veränderbaren Zellen, also C15 und C16, fest. Der Solver „weiß“ somit, dass diese Zellen so zu belegen sind, dass der Wert in C18 maximal wird. Im unteren Bereich des Fensters werden nun nacheinander die Nebenbedingungen erfasst, wozu man jeweils auf den Knopf Hinzufügen klicken muss. Es können Nebenbedingungen mit <=, = und >= definiert werden (die angebotenen Möglichkeiten int, bin und dif verwenden wir hier nicht). Bereits eingegebene Nebenbedingungen lassen sich durch die Tasten Ändern bzw. Löschen bearbeiten. Auf der linken Seite der Ungleichung werden jeweils die berechneten Werte für $N_{1}$, $N_{2}$ bzw. $N_{3}$ (also die Zellen C20, C21 und C22) ausgewiesen, während auf der rechten Seite die Zellen mit den Komponenten des Begrenzungsvektors stehen (also F10, F11 und F12). Bei diesem Beispiel treten ausnahmslos <= Nebenbedingungen auf. Im Feld Lösungsmethode auswählen stellen wir Simplex-LP ein und markieren die Checkbox Nicht eingeschränkte Variablen als nicht-negativ festlegen. Schließlich klicken wir noch auf das Feld Optionen und erhalten so die Möglichkeit, gewisse Einstellungen für die Berechnung durch den Solver vorzunehmen (siehe Abb. 10.3 ). Unter dem Reiter Alle Methoden markieren wir den Punkt Iterationsergebnisse anzeigen, so können die einzelnen Schritte des Simplex-Verfahrens verfolgt werden. Der Leser möge dies ausprobieren.

Abb. 10.3

Solver-Optionen für das LP

Die anderen wählbaren Parameter sind für lineare Optimierungsprobleme nicht relevant und müssen daher nicht verändert werden. Mit OK kehren wir in das Fenster Solver-Parameter zurück. Haben wir alle Daten eingegeben, reicht ein Klick auf die Taste Lösen, um den Solver zu starten. Da wir die Option Iterationsergebnisse anzeigen gewählt haben, hält der Solver nach jedem Zwischenergebnis an und fragt in einem separaten Fenster, ob die aktuellen Lösungswerte als Szenario gespeichert, die Berechnung gestoppt oder fortgesetzt werden soll (Abb. 10.4). Drücken wir jeweils die Taste Weiter, so endet das Verfahren nach endlich vielen Iterationsschritten, und als Resultat erscheint in einem Fenster eine Meldung mit dem Hinweis, ob eine optimale Lösung gefunden wurde oder nicht (Abb. 10.5). Wurde eine Lösung gefunden, so kann diese über die Taste OK auf Wunsch in das Arbeitsblatt übernommen werden (Abb. 10.6). Ist man an näheren Informationen interessiert, wählt man unter Berichte den Eintrag Antwort aus und erhält zusätzlich einen Antwortbericht (Abb. 10.7 ).

Abb. 10.4

Zwischenergebnisse

Abb. 10.5

Solver-Ergebnisse

Abb. 10.6

Lösung des LPs

Abb. 10.7

Antwortbericht für das LP

Für das Produktionsplanungsproblem lässt sich daraus ablesen, dass die erste Nebenbedingung für die Lösung nicht einschränkend ist, während die anderen Nebenbedingungen mit Gleichheit erfüllt sind. Man sagt auch, dass die erste Nebenbedingung als einzige inaktiv ist. Das bedeutet, dass die Kapazität der ersten Maschine im Gegensatz zu den anderen Maschinen nicht voll ausgenutzt wird.

Da wir uns zu Beginn der Berechnung für die Option Iterationsergebnisse anzeigen entschieden haben, können wir auch einen Szenariobericht (Abb. 10.8 ) erstellen lassen. Dies setzt voraus, dass jeder Iterationsschritt im Fenster Zwischenergebnis mit der Taste Szenario speichern unter Angabe eines Namens abgelegt wurde. Anhand des Szenarioberichts lässt sich der Rechenweg des Simplex-Verfahrens nachvollziehen. Er enthält Informationen darüber, wie sich das Verfahren vom Ausgangspunkt zur optimalen Lösung vorarbeitet. Zum Erstellen des Szenarioberichts muss man im Menü Daten unter der Rubrik Datentools die Was-wäre-wenn-Analyse und den darunter enthaltenden Szenario-Manager aufrufen.

Abb. 10.8

Szenariobericht für das LP

Wir empfehlen dem Leser, mit dem Solver zu experimentieren und auch die anderen Beispiele aus diesem Buch damit zu bearbeiten (siehe Aufgabe A.1). Der Excel-Solver ist aber keineswegs nur für kleine Beispielprobleme von Nutzen. Auch auf komplexe Problemstellungen lässt sich dieses Tool anwenden, wie wir bereits in Abschn. 9.2 erläutert haben.

10.2 Der Excel-Solver für Transportprobleme

Im vorigen Abschnitt haben wir gesehen, wie leicht es ist, ein Lineares Programm mithilfe des Excel-Solvers zu lösen. Da es sich bei Transportproblemen auch um Lineare Programme handelt, kann für sie im Prinzip mit dem Simplex-Verfahren und folglich auch mit dem Excel-Solver eine Lösung ermittelt werden. Dies möchten wir anhand des Beispiels 5.1 aus Kap. 5 demonstrieren, wobei wir die spezielle Darstellungsweise für Transportprobleme, die wir in Kap. 5 eingeführt haben, verwenden wollen. Dazu bilden wir zunächst auf einem Excel-Arbeitsblatt (Abb. 10.9) ein entsprechendes Transporttableau ab. Wir haben hier die zahlreichen Gestaltungsmöglichkeiten in Excel genutzt, eine wesentlich einfachere Darstellung wäre jedoch ebenso gut möglich.

Abb. 10.9

Ausgangstableau für das Transportproblem

Die veränderbaren Zellen in dem Arbeitsblatt sind die Zellen D7, E7 und F7 sowie D9, E9 und F9, in die wir zunächst den Wert null eintragen. Für den Solver definieren wir nun Hilfszellen, in denen wir jeweils die Summen der Zeilen und Spalten des Tableaus berechnen. Wir haben also:

D11 = D7 + D9 E11 = E7 + E9 F11 = F7 + F9 H7 = D7 + E7 + F7 H9 = D9 + E9 + F9

In einer weiteren Zelle kalkulieren wir die gesamten Transportkosten

G10 = D6*D7 + E6*E7 + F6*F7 + D8*D9 + E8*E9 + F8*F9.

Die Zelle G10 ist also unsere Zielzelle.

In Abb. 10.10 sind die Parameter für den Solver abzulesen. Wir haben es hier mit einem Minimumproblem zu tun, bei dem ausschließlich Gleichungen auftreten. Durch einen Klick auf Lösen im Fenster Solver-Parameter starten wir den Solver, der nach einigen Iterationsschritten die in Abb. 10.11 angegebene Lösung liefert. Auf Wunsch können wir uns auch hier einen Antwortbericht ausgeben lassen, siehe hierzu die Abb. 10.12. Die optimalen Transportkosten belaufen sich auf 440 Geldeinheiten.

Abb. 10.10

Solver-Parameter für das Transportproblem

Abb. 10.11

Lösung des Transportproblems

Abb. 10.12

Antwortbericht für das Transportproblem

Es sei noch einmal betont, dass wir hier lediglich die Darstellung des Transportproblems in Form des Tableaus gewählt haben. Der Excel-Solver verwendet jedoch nicht den speziellen Transportalgorithmus aus Kap. 5, sondern ein Simplex-Verfahren.

10.3 Der Excel-Solver für ganzzahlige Probleme

Auch Problemstellungen mit ganzzahligen Variablen lassen sich mithilfe des Excel-Solvers lösen. Die Vorgehensweise, die sich nur wenig von der bei der Lösung normaler Linearer Programme unterscheidet, soll wieder anhand eines Beispiels demonstriert werden. Wir betrachten hierzu die folgende Aufgabenstellung für $x_{1},x_{2},x_{3},x_{4}\in\mathbb{Z}$:

$$\begin{aligned}\displaystyle-x_{2}+4x_{3}+x_{4}&\displaystyle\rightarrow\text{Min!}\\ \displaystyle\text{u.\,d.\,N.}\quad{-}2x_{1}+2x_{2}+\phantom{2}x_{3}+x_{4}&\displaystyle=3\\ \displaystyle 5x_{1}-2x_{2}+3x_{3}+x_{4}&\displaystyle=8\\ \displaystyle x_{1},x_{2},x_{3},x_{4}&\displaystyle\geq 0\end{aligned}$$

Wie in Abschn. 10.1 wird zunächst ein Arbeitsblatt mit allen erforderlichen Daten angelegt (Abb.  10.13). Im Fenster Solver-Parameter müssen wir jetzt aber neben den beiden Gleichheitsrestriktionen noch zusätzlich angeben, dass alle Variablen ganzzahlig sind. Dazu fügen wir mit der Option int eine entsprechende Restriktion ein. Wir haben damit die in Abb. 10.14 angezeigten Nebenbedingungen festgelegt, wobei wir diesmal die Nichtnegativitätsbedingungen explizit definiert haben. Drücken wir jetzt den Knopf Lösen, so errechnet der Solver die optimale Lösung, die auf Wunsch in das Ausgangstableau übernommen werden kann (Abb. 10.15). Auch hier lassen wir uns wieder einen Antwortbericht ausgeben (Abb. 10.16) und erhalten die Information, welche Nebenbedingungen einschränkend oder nicht einschränkend sind. Wichtig ist natürlich in diesem Fall, dass die Ganzzahligkeitsbedingungen alle mit Einschränkend markiert sind.

Abb. 10.13

Ausgangstableau für das ganzzahlige Problem

Abb. 10.14

Solver-Parameter für das ganzzahlige Problem

Abb. 10.15

Lösung des ganzzahligen Problems

Außerdem sei noch erwähnt, dass wir bei dem ganzzahligen Problem auf die Verwendung der Solver-Option Iterationsergebnisse anzeigen verzichtet haben, da die Berechnung sehr viele Iterationsschritte erfordert. Wir überlassen es dem Leser, dies auszuprobieren und evtl. einen Szenariobericht zu erstellen.

Abb. 10.16

Antwortbericht für das ganzzahlige Problem

Aufgaben

Aufgabe A.1 (Excel-Solver)

Lösen Sie die Aufgaben 3.1, 3.1, 3.1 und 3.1 mit dem Excel-Solver.  □

Aufgabe A.2 (Excel-Solver)

Lösen Sie das folgende Lineare Programm mit dem Excel-Solver:

$$\begin{aligned}\displaystyle-x_{1}-x_{2}&\displaystyle\rightarrow\text{Min!}\\ \displaystyle\text{u.\,d.\,N.}\quad 2x_{1}+3x_{2}&\displaystyle\leq\;18\\ \displaystyle 2x_{1}+\phantom{3}x_{2}&\displaystyle\geq\phantom{-}4\\ \displaystyle 2x_{1}-3x_{2}&\displaystyle=-6\\ \displaystyle x_{1},x_{2}&\displaystyle\geq 0\end{aligned}$$

Lassen Sie anschließend einen Antwortbericht erstellen.  □

Aufgabe A.3 (Excel-Solver)

Lösen Sie die Aufgabe 1.1 mit dem Excel-Solver und beantworten Sie anschließend die folgenden Fragen:

• a) Wie groß ist der maximale Erlös?

• b) Welches Produkt sollte aus dem Lieferprogramm gestrichen werden?

• c) Welche Maschine ist am wenigsten ausgelastet?

 □

Aufgabe A.4 (Excel-Solver)

Lösen Sie das Transportproblem aus Aufgabe 5.1 mit dem Excel-Solver. Legen Sie dazu in einer Excel-Tabelle ein Transporttableau an, in das Sie mithilfe der Nordwesteckenregel eine zulässige Lösung eintragen.  □