Microsoft Office Excel 2007 - Das Handbuch

Kapitel 26

Den Solver und weitere Add-Ins einsetzen

982

Was macht der Solver?

Der Solver berechnet Lösungen für »Was-wäre-wenn-Szenarios« auf der Grundlage von veränderba-

ren Zellen und Zellen mit Nebenbedingungen. Somit bietet sich der Solver immer dann an, wenn

ein Ergebnis gesucht wird, das von mehreren Variablen abhängt. Es ist eine Art Zielwertsuche wie sie

weiter unten noch beschrieben wird. Nur ist die Funktionalität des Solvers wesentlich umfangrei-

cher.

Mit Hilfe des Solvers können Sie den optimalen Wert für eine Formel ermitteln, die Sie in eine als

Zielzelle bezeichnete Tabellenzelle eingegeben haben. Der Solver arbeitet mit einer Gruppe von Zel-

len, die in die Formel der Zielzelle eingehen. Er passt die Werte, die Sie als veränderbare Zellen ein-

geben, so lange an, bis er das Ergebnis erreicht hat, das Sie für die Zielzelle vorgegeben haben. Sie

können Nebenbedingungen zur Eingrenzung der in einem Modell verwendbaren Werte definieren,

wobei die Nebenbedingungen auf andere Zellen, die Einfluss auf die Formel in der Zielzelle haben,

verweisen können.

Damit Sie die folgenden Beispiele nachvollziehen können, muss das Solver-Add-In

geladen sein.

Wie funktioniert der Solver?

Der Solver hilft immer dann, wenn es einen Wert zu optimieren gilt. Dabei wird eine Zielfunktion

zugrunde gelegt. Diese Zielfunktion gilt es zu optimieren, d.h. es wird eine Lösung mit kleinst-

möglichem bzw. größtmöglichem Zielfunktionswert gesucht. Der Solver kann nach zwei unter-

schiedlichen Verfahren arbeiten. Für die zugrunde liegenden Entscheidungsgrößen werden dabei

weitere Bedingungen aufgestellt, z.B. dass die Werte nicht negativ sein dürfen. Die Lösung derar-

tiger Probleme wird durch lineares Optimieren erreicht. Der Solver verwendet für die Lösung

derartiger (linearer und ganzzahliger) Probleme das Simplex-Verfahren.

Einige Probleme setzen voraus, dass jede veränderliche Größe (Variable) positiv oder Null sein

muss (Nichtnegativitätsbedingung). Bei einem Entscheidungsproblem in einem produzierenden

Betrieb macht es z.B. keinen Sinn, eine negative Anzahl an herzustellenden Produkten zu erhal-

ten. Sie setzen oft auch voraus, dass die Entscheidungsgrößen ganzzahlig sein müssen. Zur

Lösung dieser Probleme setzt der Solver das Branch-and-bound-Verfahren ein. Voraussetzung

dafür ist eine Verzweigungsregel und ein Lösungsverfahren für das jeweilige Teilproblem. Dann

wird zunächst eine Lösung für das Problem gesucht, ohne die Ganzzahligkeitsbedingung zu

beachten. Im gefundenen Lösungsraum wird das Ergebnis einer einzelnen Variablen weiter

betrachtet. In dieser Verzweigung (Branch) werden die besten Grenzwerte (Bound) der anderen

Variablen ermittelt und anschließend die nächste Verzweigung wieder in die Tiefe abgearbeitet.

Dieser Vorgang wird mehrmals wiederholt bis eine zulässige Lösung erreicht ist oder aber fest-

steht, dass es keine zulässige Lösung gibt.

HINWEIS

Was macht der Solver?

983

Planung und Prognose

Ein einfaches Problem lösen

Die Grundfunktionalität des Solvers lässt sich am besten anhand eines einfachen Beispiels erklären:

Für die fünf Kinder einer Familie sollen Handys beschafft werden. Dabei stehen drei verschiedene

Typen mit unterschiedlichen Preisen zur Auswahl. Nun geht es ans Bezahlen und da gibt es unter-

schiedliche Standpunkte. Geht es nach dem Vater, werden für 145 € die günstigsten Geräte ange-

schafft. Geht es nach den Kindern, sind die teuersten Geräte für insgesamt 1.125 € gerade richtig.

Man will sich bei den Freunden ja nicht blamieren. Oma wiederum erklärt sich bereit, aus ihrem

Sparstrumpf einen Betrag von 400 € zuzuschießen. Der Vater erklärt daraufhin, dass er nicht bereit

ist, mehr als 200 € auszugeben, insgesamt könnten damit 600 € angelegt werden. Es gilt also, unter-

schiedliche Lösungen zu betrachten. Wie kann der Solver dabei helfen?

Bevor der Solver die Arbeit aufnehmen kann, muss das Problem in einer Tabelle formuliert werden.

Das ist in der Tat die schwierigste Aufgabe bei der Verwendung des Solvers. Hier sind Sie gefordert,

die vorliegende Aufgabe in ein Tabellenmodel umzusetzen. Dabei enthält das Modell Zellen mit

bekannten und unbekannten Werten sowie Zellen mit Formeln. Eine dieser Zellen, die eine Formel

enthält, gilt es zu optimieren. Dabei können die Eingangswerte anderer Zellen verändert werden.

Genau diese Aufgabe erledigt dann der Solver.

Allgemein sind folgende Schritte erforderlich:

쐍 Zuerst die Aufgabe beschreiben,

쐍 dann die Aufgabe in eine Tabelle übertragen,

쐍 das Rechenmodell mit den Restriktionen und der Zielfunktion aufbauen und

쐍 schließlich das Optimierungsproblem mit dem Solver lösen.

Tabellenmodell aufbauen

In Abbildung 26.6 ist der Bereich C7:E11 für den Eintrag einer Zahl vorgesehen. Genauer gesagt soll

hier im Schnittpunkt eines Namens und eines Handytyps eine 1 stehen, wenn das betreffende Kind

ein Handy des entsprechenden Typs erhalten soll.

Wollen Sie das folgende Beispiel selbst nachvollziehen, verwenden Sie bitte das Arbeitsblatt

Handy der Beispieldatei Kap26_Übung.xlsx aus dem Ordner \Buch\Kap26 auf der Buch-CD.

In F7 berechnet die Formel

die Kosten für das Handy von Frank. Die Anzahl der Handys in G7 berechnet die Formel

Eine Minimalforderung, die es zu lösen gilt, ist, dass hier als Ergebnis eine 1 steht. Dann erhält jedes

Kind ein Handy. Diese beiden Formeln werden bis in Zeile 11 nach unten kopiert.

Die Summe der Kosten in F12 liefert die Formel

=C7*$C$6+D7*$D$6+E7*$E$6

=SUMME(C7:E7)