Microsoft Office Excel 2007 - Das Handbuch

Fortgeschrittene Funktionen des Solvers

989

Planung und Prognose

Fortgeschrittene Funktionen des Solvers

Nachdem Sie sich mit dem Solver vertraut gemacht und vielleicht auch selbst ein wenig experimen-

tiert haben, sollen nun anhand eines anderen Beispiels weitere Einstellungen im Solver demonstriert

werden:

Enorme Regenfälle führen dazu, dass das Lager eines Elektrohändlers in wenigen Stunden unter

Wasser stehen wird. Um den Schaden so gering wie möglich zu halten, sollen die teuersten Geräte

mit einem Lieferwagen abtransportiert werden. Die Lagerbestände für Fernsehgeräte, Videorecor-

der, Stereoanlagen, PCs und Beamer sind bekannt. Die Geräte, die transportiert werden sollen,

sollen den höchstmöglichen Wert haben. Dabei müssen wir berücksichtigen, dass unser Transport-

Fahrzeug nur ein begrenztes Volumen aufnehmen kann und das zulässige Gesamtgewicht des Fahr-

zeugs nicht überschritten werden darf.

Das oben beschriebene Szenario ist ein typisches »Rucksack-Problem«. Hier geht es darum, in einen

Behälter Dinge zu tun, wobei die Kapazität und das Tragvermögen berücksichtigt werden müssen.

Als Resultat wird in der Regel der höchste Wert gewünscht. Die Abbildung 26.12 zeigt das Problem

in Tabellenform.

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

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

ROM zum Buch.

Abbildg. 26.12

Auch für diese Lösung muss zunächst die Aufgabe in einer Tabelle formuliert werden

Hier die Lösungsschritte für das Transportproblem:

1. In einem leeren Arbeitsblatt tragen Sie in Zeile 5 die Gegenstände ein, die von Interesse sind.

2. Die Zeile 6 enthält den Lagerbestand dieser Güter.

3. Die Zeile 7 bleibt vorerst leer oder wird mit 0 belegt. Hier soll der Solver die Anzahl der mitzu-

nehmenden Geräte eintragen.

Kapitel 26

Den Solver und weitere Add-Ins einsetzen

990

4. Das Gewicht der einzelnen Geräte tragen Sie in Zeile 8 ein. Dann können Sie in Zeile 9 das

Gesamtgewicht berechnen. Tragen Sie in Zelle C9 die Formel

=C$7*C8

ein und kopieren Sie die

Formel nach rechts bis G9.

5. Das Gewicht aller Geräte wird in H9 mit der folgenden Formel berechnet

=SUMME(C9:G9)

.

6. Das Volumen der einzelnen Geräte tragen Sie in Zeile 10 ein. Nun können Sie die Formel aus C9

nach C11 kopieren. Voraussetzung ist, dass die relativen und absoluten Bezüge korrekt sind. Mit

dem Ausfüllkästchen kopieren Sie die Formel bis Spalte G und setzen in H11 wieder die Zeilen-

summe ein.

7. Entsprechend wird beim Wert verfahren: Geben Sie in Zeile 12 zuerst die Werte der einzelnen

Geräte ein und kopieren Sie anschließend wieder die Formel aus C9 in die Zeile 13.

8. In H13 steht die Zeilensumme, für diese Zelle wird der Zielwert gesucht. Der Gesamtwert soll

möglichst hoch sein.

9. Die maximale Zuladung des Fahrzeugs wird mit 450 kg (C16) bei einem Volumen von 3 m

3

(C17) berücksichtigt.

Das fertige Beispiel finden Sie im Arbeitsblatt Transportproblem in der Datei Kap26_Lösung.xlsx

im Ordner \Buch\Kap26 auf der CD-ROM zu diesem Buch.

Lösung mit dem Solver

Wir wollen den höchstmöglichen Wert aus dem Lager holen. Die Zelle H13 soll also maximiert wer-

den. Die Kapazität des Fahrzeugs (also Volumen und Gewicht) darf nicht überschritten werden. Das

wären die Nebenbedingungen.

Beginnen wir mit der Lösung des Problems:

1. Starten Sie den Solver mit dem Befehl Solver auf der Registerkarte Daten in der Gruppe Analyse.

2. Geben Sie für die Zielzelle H13 an und als Zielwert-Option Max.

3. Die veränderbaren Zellen befinden sich im Bereich C7:G7. Das entspricht der Anzahl der mitzu-

nehmenden Geräte.

4. Definieren Sie die Nebenbedingungen: Klicken Sie auf Hinzufügen und markieren Sie die Zelle

H9. Diese muss kleiner oder gleich C16 sein, sonst überladen wir unser Fahrzeug. Klicken Sie

erneut auf Hinzufügen.

5. Markieren Sie als Nächstes H11. Das Resultat dieser Zelle muss kleiner oder gleich C17 sein,

sonst passen die Geräte nicht mehr in den Transporter. Klicken Sie dann auf OK.

6. Mit den vorgenommenen Einstellungen sollte der Solver eine Lösung finden können. Klicken

Sie daher auf Lösen.

Der Solver meldet Werte der Zielzelle konvergieren nicht. Diese Meldung deutet darauf hin, dass der

Wert der Zielzelle gegen Unendlich wächst oder fällt, obwohl alle Nebenbedingungen eingehalten

wurden. Dies geschieht immer dann, wenn bei der Problemdefinition eine oder mehrere Nebenbe-

dingungen ausgelassen wurden.