28

Die Funktionen Zielwertsuche und Solver

Excel hat viele Funktionen, die Sie bei der Datenanalyse von Tabellen unterstützen. Zwei wichtige Beispiele sind der Befehl Zielwertsuche im Menü Extras und das Add-In Solver. Der Befehl Zielwertsuche sucht den Eingabewert, der das von Ihnen gewünschte Ergebnis ergibt, in einer Formelzelle. Die Funktion Solver sucht nach den besten oder optimalen Werten für mehrere Eingaben und findet Antworten, die die Beschränkungen in bestimmten Formelzellen, sogenannte Nebenbedingungen, berücksichtigt und dabei die festgelegte Formelzelle, das sogenannte Ziel, maximiert oder minimiert. n

Wann sollte welche Funktion verwendet werden?

Jede der Excel-Funktionen eignet sich für jeweils andere Situationen der Problemlösung. Einige Funktionen, wie z. B. der Solver, sind Add-In-Programme.

Die Zielwertsuche und der Solver sind Funktionen, die speziell darauf ausgerichtet sind, Werte für eine oder mehrere Eingabezellen zu suchen, die den Wert einer von diesen Zellen abhängigen Formel optimieren.

Tabelle 28.1: Zur Verwendung von Zielwertsuche bzw. Solver.

Verwenden der Funktion Zielwertsuche

Wissen Sie die gewünschte Antwort bereits und müssen sich zurückarbeiten, um den Eingabewert zu finden, der diese Antwort ergibt, wählen Sie im Menü Extras den Befehl Zielwertsuche. Mit diesem Befehl geben Sie den Zielwert für eine Formelzelle (das Ziel) an; eine der Eingabezellen soll verändert werden, damit die Zielzelle das gewünschte Zielergebnis aufweist. Excel sucht den Eingabewert, der die von Ihnen gewünschte Antwort ergibt. Hierfür stellt die Funktion wiederholte wohlbegründete Vermutungen an und engt den genauen Wert ein.

Der Befehl Zielwertsuche erspart Ihnen Zeit, wenn Sie rückwärts verfahren müssen, um die Lösung zu finden. Sie können diesen Befehl z. B. verwenden, um die erforderliche Wachstumsrate festzulegen, um ein bestimmtes Verkaufsziel zu erreichen oder um festzulegen, wieviele Einheiten verkauft werden müssen, um kostendeckend zu arbeiten.

Verwenden Sie im Menü Extras den Befehl Zielwertsuche, muß die zu verändernde Zelle einen Wert enthalten (keine Formel) und muß die angegebene Zielzelle mit bedingen. Da Sie für den Befehl keine Nebenbedingungen festlegen können, kann es sein, daß Sie am Schluß unsinnige Eingabewerte erhalten oder daß Sie ein Ergebnis angeben, für das es keine Eingabewerte geben kann. In derartigen Situationen können Sie Mehrfachoperationen oder den Szenario-Manager verwenden und verschiedene Eingabewerte austesten oder Sie können den Solver verwenden, um unter Berücksichtigung der von Ihnen angegebenen Nebenbedingungen die optimale Lösung zu finden.

Zielwertsuche

Bild 28.1 zeigt ein einfaches Tabellenblatt mit Prognosen über den zukünftigen Umsatz, die Kosten/Ausgaben und den Nettogewinn. Die veränderbaren Dateneingabezellen sind die Änderungsrate in Zeile 16 und das Kostenverhältnis in den Zellen B18:B20. Die Änderungsraten dienen der Prognose von Umsatzzahlen und das Kostenverhältnis der Prognose von Kosten bzw. Ausgaben.

Angenommen, Sie wollen die Änderungsrate für die Rubrik Umsatz wissen, die 1998 erforderlich ist (Zelle D16), um im Jahr 2000 (Zelle F12) einen Nettogewinn von DM 3000,- zu erzielen. Die Zielwertsuche kann den Wert in Zelle D16 ändern, bis die Zelle F12 den Wert DM 3000,- ergibt.

Haben Sie im Menü Extras unter dem Befehl Optionen im Register Berechnen das Kontrollkästchen Genauigkeit wie angezeigt aktiviert, kann es sein, daß Excel das Ziel nicht exakt erreichen kann, auch wenn das eigentlich möglich ist. Deaktivieren Sie das Kontrollkästchen Genauigkeit wie angezeigt im Register Berechnen, bevor Sie den Befehl Zielwertsuche verwenden. Ist die Zielwertsuche abgeschlossen, können Sie das Kontrollkästchen wieder aktivieren.

Um mit der Zielwertsuche ein bereits festgelegtes Ergebnis zu bekommen, gehen Sie folgendermaßen vor:

• Markieren Sie eine Zielzelle, die eine Formel enthält, die einen bestimmten Wert ergeben soll. In diesem Beispiel ist die Zielzelle die Zelle F12.
• Wählen Sie im Menü Extras den Befehl Zielwertsuche. Das Dialogfeld Zielwertsuche wird angezeigt (es ist auch in Bild 28.1 zu sehen). Beachten Sie, daß das Textfeld Zielzelle die in Schritt 1 gewählte Zelle enthält.

Abbildung 28.1: Das Textfeld Zielzelle im Dialogfeld Zielwertsuche enthält automatisch die Adresse der aktiven Zelle.

• Im Textfeld Zielwert geben Sie den Zielwert ein, der das Ergebnis sein soll. In diesem Beispiel ist der gewünschte Zielwert DM 3000,-, also geben Sie 3000 ein.
• Im Textfeld Veränderbare Zelle geben Sie den Zellbezug der Eingabezelle ein. In diesem Beispiel ist die veränderbare Zelle die Zelle D16, also geben Sie D16 ein. In diesem Beispiel trägt die Zelle D16 nur indirekt zum Zielformelwert bei – sie hilft beim Festlegen der Umsatzwerte in Zeile 5 und die Umsatzzellen bedingen die Werte des Nettogewinns in Zeile 12 mit.

Bild 28.2 zeigt das vervollständigte Dialogfeld Zielwertsuche.

Abbildung 28.2: In diesem ausgefüllten Dialogfeld wird für die Zelle F12 der Wert 3000 festgelegt, wobei die zu verändernde Zelle D16 einen entsprechenden Wert erhalten soll.

• Aktivieren Sie die Schaltfläche OK.

Die Zielwertsuche beginnt mit dem Eingeben von Eingabewerten in Zelle D16. Es werden hohe und niedrige Werte eingegeben, die dann zusammengebracht werden, so daß die Lösung der gewünschten Lösung so nahe als möglich kommt.

• Wollen Sie bei einer umfassenden Zielwertsuche den Vorgang der Zielwertsuche unterbrechen oder abbrechen, aktivieren Sie im Dialogfeld Status der Zielwertsuche, das während der Berechnungen angezeigt wird, die Schaltfläche Pause oder Abbrechen. Um den Lösungsvorgang zu verfolgen, aktivieren Sie im Dialogfeld Status der Zielwertsuche die Schaltfläche Schritt. Der momentane Lösungswert wird dann im Dialogfeld angezeigt. Um nach der Pause den Vorgang der Zielwertsuche wieder im normalen Tempo fortzusetzen, aktivieren Sie die Schaltfläche Weiter.

Die in Schritt 4 angegebene Eingabezelle muß den Wert der Formel in der Zielzelle mitbedingen und darf selbst keine Formel enthalten. Um die Vorgängerzellen (informationszuführenden Zellen) der Zielzelle festzustellen, wählen Sie die Zielzelle. Wählen Sie im Menü Bearbeiten unter dem Befehl Gehe zu die Option Inhalte. Wird das Dialogfeld Inhalte auswählen angezeigt, wählen Sie die Optionsschaltflächen Vorgängerzellen und Alle Ebenen und aktivieren Sie die Schaltfläche OK. Alle Zellen, die zum Wert der Zielzelle beitragen, werden selektiert. Drücken Sie die Tasten (Tab) oder (Eingabetaste), um sich zwischen diesen Zellen zu bewegen; sie bleiben selektiert.

Ist eine Lösung gefunden, aktivieren Sie die Schaltfläche OK, um die Werte in den Originaltabellen mit den neuen auf dem Bildschirm angezeigten Werten zu ersetzen oder aktivieren Sie die Schaltfläche Abbrechen, um die Ursprungswerte beizubehalten.

Diagramm-Markierungen zur Zielwertsuche bewegen

Sie können ein Diagramm zur Zielwertsuche verwenden. Dazu müssen Sie sich in einem 2D-Säulen-, Balken- oder Liniendiagramm befinden. Ziehen Sie eine der Markierungen an eine neue Wertposition, werden das Dialogfeld Zielwertsuche und das Tabellenblatt angezeigt. Excel fragt, welcher der Eingabewertzellen geändert werden soll, damit der neue Wert der Diagramm-Markierung in der entsprechenden Tabellenzelle angezeigt wird.

Um mit einem Diagramm eine grafische Lösung zu erstellen, gehen Sie folgendermaßen vor:

• Öffnen Sie das Tabellenblatt und das Diagramm, das Sie bearbeiten wollen. Aktivieren Sie das Diagramm.
• Klicken Sie auf den Rand der Datenreihenmarkierungen (dargestellt als Säule, Balken oder Linie), für die der Wert geändert werden soll, indem Sie zuerst die Datenreihe und dann durch erneutes Klicken die einzelne Markierung markieren. Schwarze Ziehpunkte werden an der Markierung angezeigt (siehe Bild 28.3).

Abbildung 28.3: Klicken Sie einmal auf die Markierung, um die ganze Datenreihe zu selektieren, dann klicken Sie erneut auf den Rand der Markierung, um die einzelne Markierung zu selektieren.

• Ziehen Sie an den schwarzen Ziehpunkten, um das Ende der Markierungen zu einem neuen Wert zu ziehen. In diesem Beispiel ziehen Sie die Markierungen nach oben oder unten, um die Höhe der Säule zu ändern.

Beim Ziehen der Markierungen werden Sie feststellen, daß die Zahlenwerte der Markierung in einem mit der Markierung verbundenen Quick-Info angezeigt werden. Somit können Sie die Werte ablesen, während Sie die Markierung neu positionieren.

Beim Loslassen der Maustaste wird das Dialogfeld Zielwertsuche angezeigt (siehe Bild 28.4).

Abbildung 28.4: Ziehen Sie eine der Diagramm-Markierungen zu einem neuen Wert, wird das Dialogfeld Zielwertsuche angezeigt, wenn die Diagramm-Markierung mit einer Zelle verknüpft ist, die eine Formel enthält.

Im Dialogfeld Zielwertsuche enthält das Textfeld Zielwert den neuen Wert der Diagramm-Markierung.

Ist die Diagramm-Markierung mit einer Zelle verknüpft, die eine Zahl und keine Formel enthält, so wird das Dialogfeld Zielwertsuche nicht angezeigt. Statt dessen ändert sich die Zahl im Tabellenblatt und reflektiert den neuen Wert der Markierung. Diese Funktion erleichtert Ihnen die Eingabe von Werten in ein Tabellenblatt, wenn diese Werte eine bestimmte Diagramm-Konfiguration wiedergeben sollen.

• Ändern Sie den Wert im Textfeld Zielwert, wenn Sie einen anderen Wert wünschen. Das Textfeld Zielzelle enthält die mit der Diagramm-Markierung verknüpfte Tabellenzelle.
• Im Textfeld Veränderbare Zelle geben Sie den Zellbezug ein, der geändert werden soll oder klicken Sie mit der Maus auf die Eingabezelle.
• Aktivieren Sie die Schaltfläche OK.

Sie können die in Schritt 6 der vorherigen Anweisungen angegebenen Optionen für die Zielwertsuche verwenden, während Excel den Eingabewert sucht, der den von Ihnen gewünschten Wert für die Diagramm-Markierung am ehesten ergibt.

Finden der Optimallösung mit dem SolverOptimalloesungen finden

In Bild 28.1 haben Sie nur die Umsatzwachstumsrate für 1998 geändert, um den Zielwert für den Nettogewinn als Ergebnis zu erhalten. Warum sollten nicht auch die Wachstumsraten für andere Jahre und auch die Kosten- und Ausgabenfaktoren geändert werden? Und warum sollte man nicht nach dem maximal möglichen Nettogewinn suchen? Solche Probleme können Sie mit dem Solver analysieren.

Anders als bei der Funktion Zielwertsuche, die ein bestimmtes Ergebnis sucht, sucht der Solver nach einer optimalen Lösung, indem er mehrere Eingabezellen variiert und gleichzeitig sicherstellt, daß andere Formeln in der Tabelle innerhalb der festgelegten Beschränkungen bleiben. Der Solver funktioniert so, wie auch Probleme in Wirklichkeit funktionieren – es müssen mehrere Variablen geändert werden, um zu einer Antwort zu gelangen. Gleichzeitig müssen andere Aspekte des Problems genau beobachtet werden, um sicherzustellen, daß sie im Rahmen realistischer Beschränkungen bleiben.

Oft muß das Tabellenblatt erst angepaßt werden, damit es dem Modelltyp entspricht, mit dem der Solver am besten arbeiten kann. Würden Sie in der Tabelle in Bild 28.1 den Solver auffordern, den Nettogewinn durch Ändern der Umsatzwachstumsraten und der Ausgabenposten zu ändern, würde er die Wachstumsraten einfach sehr groß und die Ausgabenposten negativ machen. Damit das Ganze sinnvoll ist, müssen Sie Beschränkungen hinzufügen oder Bedingungen für die Werte der Ausgabenposten und eventuell auch Formeln festlegen, die realistische Umsatzwachstumsraten auf der Grundlage anderer Eingaben berechnen. Um ein solches Tabellenblatt zu erstellen, sollten Sie die tatsächliche Situation, die Ihr Modell repräsentiert, gut kennen. Der Solver weiß Sie für diese Mühe zu belohnen. Der Solver kann Ihnen ersparen, daß Sie Ressourcen durch schlechte Planung verschwenden. Durch bessere Finanzplanung kann er Ihnen zu größeren Einnahmen verhelfen und Ihnen zeigen, welche Kombination aus Fertigung, Lagerplanung und Produkten Ihnen den höchsten Profit einbringt.

Wann sollte man den Solver verwenden?

Die meisten praktischen Solver-Anwendungsbereiche haben mit der Allokation beschränkter Ressourcen zu tun. Es geht darum, ein bestimmtes Ziel wie den Maximalgewinn, ein Minimum an Kosten oder einen möglichst hohen Standard zu erreichen. Ressourcen sind Rohstoffe, Arbeitsstunden, Maschinenbelegungszeit, Geld oder andere Faktoren, von denen ein begrenztes Angebot herrscht. Die Nebenbedingungen für den Solver geben die mengenmäßigen Beschränkungen an. Der Solver sucht nach der besten Ressourcen-Allokation, die diese Bedingungen erfüllt.

Oft lassen sich die beschränkten Ressourcen auf verschiedene Weise verwenden; und der Solver findet die beste Kombination für die Ressourcenverwendung. Zum Beispiel können die gleichen Rohstoffe zur Herstellung verschiedener Produkte verwendet werden, die gleichen Mitarbeiter können in verschiedenen Schichten arbeiten oder die gleichen Ressourcen können zu unterschiedlichen Zeiten genutzt werden. Für die manuelle Analyse ist die Vielfalt möglicher Kombinationen viel zu umfangreich – aber der Solver kann die beste Kombination automatisch finden.

Um den Solver zu verwenden, müssen Sie die Variablen oder Eingabezellen in Ihrem Modell (normalerweise sind das die Mengen an Ressourcen), das Ziel oder die objektive Formel, die maximiert oder minimiert werden soll, und die Nebenbedingungen, die Ober- und/oder Untergrenzen für die Ergebnisse, die bestimmte Formeln als Lösung anbieten, identifizieren können.

Nebenbedingungen bei Solver-Modellen

Nebenbedingungen sind Bezüge wie A1<=B1, A1=A2 oder A1>=0. Sie setzen den Werten, die festgelegte Formelzellen bei der Lösung haben dürfen, bestimmte Grenzen. Mindestens eine der für eine Nebenbedingung verwendeten Formelzellen muß von einer der Variablenzellen abhängig sein; sonst wird die Nebenbedingung beim Suchen der Optimallösung nicht berücksichtigt – sie ist dann entweder erfüllt oder nicht erfüllt, noch bevor der Solver startet. Nebenbedingungen werden normalerweise in Blöcken oder Gruppen eingegeben und der Solver gibt Ihnen die Möglichkeit, sie in einem der Schritte schriftlich festzulegen, wie z. B. A1:A10<=B1:B10 oder A1:E1>=0.

Das Festlegen der richtigen Nebenbedingungen ist für das Solver-Modell entscheidend. Einige Nebenbedingungen, wie ein beschränkter Lagerbestand an Rohstoffen, sind leicht festzustellen. Andere sind schwerer festzulegen. Zum Beispiel:

• Für ein Modell über mehreren Zeiträume mag eine Bedingung notwendig sein, die festlegt, daß der Anfangsbestand (der Kassenbestand o.ä.) einer neuen Periode gleich dem Endbestand (bzw. Kassenbestand) der früheren Periode sein muß.
• Für ein Versandmodell mag eine Bedingung erforderlich sein, die festlegt, daß der Anfangsbestand plus den versandten Produkten gleich dem Endbestand plus der versandten Produkte sein muß.
• Viele Modelle machen eine Bedingung erforderlich, die »die Nutzung aller Ressourcen« festlegt. Zum Beispiel muß der Gesamtbetrag der für verschiedene Bestände bereitgestellten Gelder gleich dem Gesamtkapitel im Portefeuille sein.
• Eine sehr häufige Bedingung ist die Bedingung der Nicht-Negativität: sie bestimmt, daß variable Zellen, die Mengen wie z. B. versandte Artikel repräsentieren, nicht negativ sein können.

Bedingungen versus logische Formeln

Bedingungen verwenden den gleichen Aufbau wie logische Formeln. Aber die Funktion Solver geht mit Bedingungen etwas anders um: Für die Bedingung gibt es immer eine gewisse Toleranz (der Standard, die sogenannte Genauigkeit, liegt bei 0,000001). Das heißt, eine Bedingung wie z. B. A1>=0 gilt als erfüllt (oder WAHR), auch wenn A1 den Wert -0,0000005 ergibt. Eine logische Formel wie z. B. =A1>=0 führt in diesem Fall zur Anzeige FALSCH. Der Solver geht so vor, weil die komplexen Berechnungen keine absolut genauen Ergebnisse ergeben können, wenn der Computer nur eine begrenzte Anzahl von Präzisionsziffern speichert. Aus dem gleichen Grund nimmt der Solver auch keine Bedingungen wie z. B. A1>0 an – sie wäre von der Bedingung A1>=0 nicht zu unterscheiden.

Mathematische Bezüge bei Solver-Modellen

Die mathematische Form der Bezüge zwischen Tabellenzellein- und -ausgaben kann die Lösungsfindungsmöglichkeiten des Solver stark beeinflussen. In der einfachsten Form sind die Ausgaben Linearfunktionen der Eingaben. Bei grafischer Darstellung ist die Linearfunktion einer Variable eine Gerade, und die Funktion von zwei Variablen eine flache Ebene. Linearfunktionen können immer die folgende Form haben:

X=A*Y1+B*Y2+C*Y3...

In diesem Aufbau ist X das Ergebnis; A, B und C sind Konstanten; und Y1, Y2 und Y3 sind Variablen oder Eingabezellen. A, B und C können eine beliebige komplexe Berechnung in Ihrem Tabellenblatt repräsentieren, solange die Berechnung nicht von einer der Variablen Y1, Y2 oder Y3 abhängig ist. Da der Solver nur die von Ihnen festgelegten Variablen oder Eingabezellen ändert, sind Formeln, die nicht von den Variablen abhängen, im Solver-Modell konstant.

Sind die Zielzelle und alle Bedingungen Linearfunktionen und Sie wählen die in Tabelle 28.3 beschriebene Option Lineares Modell voraussetzen, kann der Solver schnellere und zuverlässigere Methoden zur Lösungsfindung verwenden. Bei Nichtaktivierung dieser Option betrachtet der Solver das Modell als nichtlinear und verwendet allgemeinere Methoden, die langsamer und weniger zuverlässig sind – auch wenn die alle Bezüge tatsächlich linear sind.

Der Solver kann auch Aufgaben lösen, bei denen nichtlineare Bezüge zwischen den Ein- und Ausgaben geglättet werden müssen. Eine nichtlineare Beziehung ist alles, was nicht linear ist – mit anderen Worten jede beliebige Funktion, die nicht in der oben gezeigten Form ausgedrückt werden kann oder jede beliebige Funktion, deren grafische Darstellung eine Kurve enthält. Beispiele für nichtlineare Funktionen:

• Die Umsatzzahlen reichen bis an ein bestimmtes Volumen heran und flachen dann wieder ab.
• Die Produktqualität wird normalerweise nach Varianzen oder Standardabweichungen gemessen, die von quadrierten Differenzen mit der Norm abhängen.
• Die Resonanz auf eine Werbekampagne steigt anfangs rapide, im weiteren jedoch langsamer an.
• Die Produktkosten variieren bei unterschiedlichem Umsatzvolumen.

Einige Formen für nichtlineare Bezüge sind unter anderem:

X=Y1/Y2

X=Y1^.5 (die Quadratwurzel von Y1)

X=A+Y1*Y2

Hier ist X das Ergebnis, A eine Konstante und Y1 und Y2 sind Eingabewerte. Der Solver kann viele verschiedene nichlineare Funktionen wie diese bearbeiten.

Der Solver hat Probleme bei Aufgaben, in denen die Bezüge zwischen Ein- und Ausgaben nicht gleichmäßig (d. h. nicht »glatt«) verlaufen. Bei grafischer Darstellung weist eine nicht kontinuierliche Funktion einen plötzlichen Wertrückgang oder Wertsprünge auf. Am häufigsten kommt das bei den WENN-Formeln von Excel vor. Der Solver hat z. B. oft Schwierigkeiten, eine Lösung für eine Aufgabe mit einem Ziel oder einer Nebenbedingung wie

=WENN(Y1>0;A*Y1;B*Y1)

zu finden, wobei Y1 in dieser Aufgabe als Variable auftritt. Wie im oberen Beispiel können Sie WENN-Formeln in Ihrem Solver-Modell verwenden, solange der bedingende Teil der WENN-Formeln nicht von einer der Variablen abhängig ist und daher für den Solver als Konstante auftritt. Andere Funktionen, die dem Solver Schwierigkeiten bereiten können, sind die Funktionen VERWEIS und WAHL, und gelegentlich auch Funktionen wie ABS, GANZZAHL und RUNDEN.

Installieren der Solver-Funktion

Der Solver befindet sich auf einer Add-In-Arbeitsmappe und arbeitet mit einer Dynamic Link Library (DLL), die sich mit Excel verträgt. Haben Sie den Solver beim Installieren von Excel nicht mitinstalliert, können Sie den Installationsvorgang von Excel wiederholen und dabei die Option zum Installieren des Solver wählen. Sie müssen Excel nicht ganz neu installieren.

Ist der Solver installiert, so steht er Ihnen als Excel-Add-In zur Verfügung. Sie haben leichter Zugriff auf den Solver, wenn Sie ihn als Add-In wählen.

Damit der Solver beim Starten von Excel geladen wird, wählen Sie ihn als Add-In. Dabei gehen Sie folgendermaßen vor:

• Wählen Sie im Menü Extras den Befehl Add-In-Manager. Wählen Sie im Dialogfeld Add-In-Manager aus der Liste Verfügbare Add-Ins die Option Solver. Können Sie den Solver in der Liste nicht finden, verwenden Sie die Schaltfläche Durchsuchen, um die Datei SOLVER.XLS zu wählen. Sie befindet sich normalerweise im Verzeichnis ***\PROGRAMME\MICROSOFTOFFICE\OFFICE\MAKRO\SOLVER***.
• Aktivieren Sie die Schaltfläche OK.

Sie müssen nun nichts weiter tun und können den Solver jederzeit verwenden. Wählen sie im Menü Extras den Befehl Solver wird der Solver gestartet und das Hauptdialogfeld angezeigt. Beim Ersten Öffnen wird die Datei Solver-Add-In automatisch im Arbeitsspeicher gespeichert.

Erstellen der Beispieltabelle

Das Tabellenblatt in Bild 28.5 zeigt ein einfaches Modell, das für den Solver erstellt wurde. In diesem Beispiel hat die Stadtverwaltung das Dienstleistungsunternehmen Spottbillig GmbH ins Leben gerufen. Das Unternehmen verwendet vorhandene Ressourcen, um bietet der Stadt zu eine Einkommensquelle.

Die Spottbillig Gmbh hat ein Programm zum Einsammeln von organischen Abfällen, abgeschnittenem Buschwerk, Weihnachtsbäumen usw. Das Unternehmen mulcht oder kompostiert diese und setzt ihnen Boden- und Mineralzusätze hinzu, um hochwertige Erde für Garten und Topfpflanzen zu produzieren. Die Arbeit wird teilweise auf kostenloser Basis verrichtet bis auf die Sammlungskosten sind die Materialkosten niedrig.

Der Solver sucht nach der besten Kombination von Rohmaterialien, um die höchste Gewinnspanne zu erwirtschaften (siehe Zelle I17). Im wirklichen Leben sind die Problemmodelle meist nicht so einfach, aber der Solver kommt mit den Beschränkungen im wirklichen Leben zurecht und kann die Optimallösung bei sich ändernden Bedingungen neu berechnen.

Abbildung 28.5: Der Solver verwendet das Tabellenblatt der Firma Spottbillig, um die Kombination aus Rohmaterialien zu suchen, mit der die größtmögliche Gewinnspanne erzielt werden kann.

Das Tabellenblatt der Firma Spottbillig besteht weitgehend aus Text und konstanten Zahlen. Um das Tabellenblatt als Grundstruktur zu verwenden, geben Sie den in Bild 28.5 gezeigten Text ein. Dann geben Sie folgende Zahlen und Formeln ein:

Tabelle 28.2: Tabellenblatteingaben. (Forts.)

Der Input der Firma Spottbillig sind die Rohstoffe Erde, Kompost, Mineralien und Mulch, die in den Spalten C bis F dargestellt sind. Der Output sind die Bodenmischprodukte Gartenerde, Rasenerde, Ackerkrume und Gemüseanbau, die in den Zeilen 5 bis 8 dargestellt sind.

In diesem Modell sind die Werte von C5: F5 die Mengen an Rohstoffen, die notwendig sind, um eine Einheit des Produkts Gartenerde herzustellen. Der Einzelhandelspreis für eine Einheit Gartenerde beträgt DM 105,–. Der Solver sucht nach der Optimalmenge, die vom Produkt Gartenerde hergestellt werden soll (G5). Hat der die Optimalmenge gefunden, wird der Umsatzbetrag in Zelle I5 berechnet, indem G5 mit H5 multipliziert wird. Diese Methode wird für jedes der Produkte angewandt.

Eine Nebenbedingung ist die begrenzte Anzahl verfügbarer Rohstoffe. Der momentane Materialbestand – Erde, Kompost, Mineralien und Mulch – ist in den Zellen C12:F12 angegeben. In den Zellen C13:F13 wird die Menge angezeigt, die von jedem Material verbraucht wird. Natürlich darf die Menge verbrauchter Materialien nicht größer sein als die Menge vorhandener Materialien.

Die Kosten der verbrauchten Materialien werden berechnet, indem die Einheitskosten der Materialien (C16:F16) mit den Mengen verbrauchter Materialien (C13:F13) multipliziert werden. Das Ergebnis dieser Kostenformeln wird in den Zellen C17:F17 angezeigt.

Die Einnahmen werden in der Zelle I15 berechnet, indem die Umsatzzahlen aus I5:I8 addiert werden. Die Produktkosten in Zelle I16 sind das Gesamtergebnis der einzelnen Kostenfaktoren (C17:F17). Die Gewinnspanne in Zelle I17 stellt die Gesamteinnahmen minus der Gesamtkosten dar.

Bevor Sie den Solver starten, speichern Sie die Tabelle mit dem Befehl Speichern unter im Menü Datei.

Suchen der Optimallösung

Angenommen, die Stadtverwaltung verlangt, die optimale Gewinnspanne festzustellen, d. h. Ziel des Modells soll sein, nach den optimalen (maximalen) DM-Einnahmen in Zelle I17 zu suchen. Mit dieser Zielsetzung könnte das von der Firma Spottbillig durchgeführte Recycling und Kompostieren weiter ausgebaut und Steuern reduziert werden.

Die Eingabewerte, die zur Feststellung der größten Gewinnspanne verändert werden müssen, sind die Produktionsmengen für jedes der Erdprodukte. Zu diesem Zeitpunkt kann die Stadt alle hergestellten Produkte verkaufen und muß sich darum keine Gedanken machen, die Produktionsmenge für ein Produkt zu beschränken. Die Eingabewerte, mit denen der Solver arbeitet, befinden sich in den Zellen G5:G8. In diesem Beispiel sind alle Anfangseingabewerte 0. Bei Modellen mit langer Berechnungszeit läßt sich die Berechnungszeit reduzieren, wenn man mit Eingabewerten beginnt, die nach eigenem Wissen der Optimallösung sehr naheliegen.

Als Bedingung für die mögliche Lösung muß gelten, daß der verbrauchte Bestand nicht größer sein darf als der vorhandene Bestand. Für das Tabellenblatt bedeutet das, daß die berechneten Ergebnisse aus den Zellen C13:F13 nicht größer sein dürfen als die entsprechenden Werte in den Zellen C12:F12. Zusätzlich müssen die Werte in den Zellen G5:G8 größer als 0 sein, weil keine negative Anzahl an Erdprodukten hergestellt werden kann.

Haben Sie diese Bedingungen (die Zielzelle, die zu verändernden Zellen und die Nebenbedingungen für die Lösung) in das Dialogfeld Solver-Parameter eingegeben, sieht das Dialogfeld aus wie in Bild 28.6.

Abbildung 28.6: Das ausgefüllte Dialogfeld Solver-Parameter zur Maximierung des Gewinn-spannenwertes in Zelle I17, als Nebenbedingung gilt die Einschränkung durch den vorhandenen Bestand.

Um die Optimallösung zu finden, gehen Sie folgendermaßen vor:

• Markieren Sie die Zelle, die optimiert werden soll (in diesem Beispiel die Zelle I17).
• Wählen sie im Menü Extras den Befehl Solver. Das leere Dialogfeld Solver-Parameter wird angezeigt (siehe Bild 28.7).

Abbildung 28.7: Das Dialogfeld Solver-Parameter vor Eingabe der Angaben
zur Problemlösung.

• Im Textfeld Zielzelle geben Sie den Zellbezug für die Zelle, ein die optimiert werden soll.
• Legen Sie den Bezugstyp zwischen der Zielzelle und einen Lösungswert fest, indem Sie eine der folgenden Optionen in der Gruppe Zielwert wählen:

In diesem Beispiel wählen Sie die Option Max.

• Wählen Sie im Bearbeitungsfeld Veränderbare Zellen die veränderbaren Zellen, die der Solver ändern sollte, um die optimale Antwort zu finden. In diesem Beispiel sind das die Zellen $G$5:$G$8. Sie können die Eingabe eintippen, mit der Tastatur jede der Zellen markieren oder mit der Maus über die Zellen ziehen. Sind die benötigten Zellen nicht sichtbar, können Sie die Schaltfläche zur Bereichswahl rechts in diesem Bearbeitungsfeld anklicken und einen Bildlauf durch die Tabelle durchführen.
• Um der Liste Nebenbedingungen Nebenbedingungen hinzuzufügen, aktivieren Sie die Schaltfläche Hinzufügen. Das Dialogfeld Nebenbedingungen hinzufügen wird angezeigt (siehe Bild 28.8).

Abbildung 28.8: Das Dialogfeld Nebenbedingungen hinzufügen
wird für jede Nebenbedingung im Solver-Modell verwendet.

• Geben Sie die erste Nebenbedingung ein. In diesem Beispiel müssen die Werte in $G$5:$G$8 größer als 0 sein. Diese Nebenbedingung stellt sicher, daß der Solver nur die Lösungen in Erwägung zieht, die einen positiven oder 0-Wert für Erde ergeben.

Im Textfeld Zellbezug geben Sie $G$5:$G$8 ein. Sie können den Zellbezug eingeben, mit der Tastatur markieren oder mit der Maus über die Zellen ziehen. Sind die benötigten Zellen nicht sichtbar, können Sie die Schaltfläche zur Bereichswahl anklicken und einen Bildlauf durch die Tabelle durchführen.

Verwenden Sie die (Tab)-Taste oder klicken Sie auf den Pfeil, um zur Drop-down-Liste mit den Operatorsymbolen zu gelangen. Für dieses Beispiel wählen Sie den Vergleichsoperator >=.

Im Textfeld Nebenbedingung geben Sie 0 ein.

Das ausgefüllte Dialogfeld Nebenbedingungen hinzufügen für dieses Beispiel wird in Bild 28.9 angezeigt.

Nebenbedingungen für Eingabezellen wie G5:G8>=0 gelten für viele Solver-Modelle. Um diese festzulegen, bietet Excel 97 mit dem Kontrollkästchens Nicht-Negativ voraussetzen im Dialogfeld Optionen einen Shortcut.

Abbildung 28.9: Mit einer Nebenbedingung können Sie dem Wert, der bei der Lösung in den jeweiligen Zellen des Feldes Zellbezug angezeigt wird, Beschränkungen auferlegen.

• Aktivieren Sie die Schaltfläche Hinzufügen, um weitere Nebenbedingungen hinzu-zufügen. Wird das Dialogfeld Nebenbedingungen hinzufügen erneut angezeigt, geben Sie die nächste Nebenbedingung ein. In diesem Beispiel ist die Nebenbedingung: $C$13:$F$13<=$C$12:$F$12. Das heißt, daß der verbrauchte Bestand immer weniger oder gleich dem vorhandenen Bestand sein muß.
• Aktivieren Sie die Schaltfläche OK. Das ausgefüllte Dialogfeld Solver-Parameter wird angezeigt (siehe Bild 28.6).
• Aktivieren Sie die Schaltfläche Lösen, um den Solver zu starten, um mit der Suche nach der optimalen Kombination von Erdprodukten (die die höchste Gewinnspanne erbringt) zu beginnen.

Findet der Solver eine Lösung wird das Dialogfeld Ergebnis angezeigt (siehe Bild 28.10).

• Wählen Sie die Option Lösung verwenden, um die vorgeschlagene Lösung, die auch im Tabellenblatt angezeigt ist, zu übernehmen. Wählen Sie die Option Ausgangswerte wiederherstellen, um zu den ursprünglichen Tabellenwerten zurückzukehren. In diesem Beispiel wählen Sie die Option Lösung verwenden und aktivieren die Schaltfläche OK. In diesem Dialogfeld können Sie auch festlegen, welche Berichte erstellt werden sollen.

Abbildung 28.10: Das Dialogfeld Ergebnis bietet Ihnen Optionen für
die Verwendung der vom Solver berechneten Lösung.

Der Solver teilt Ihnen mit, daß Sie die höchste Gewinnspanne erreichen, wenn Sie 11 Einheiten Gartenerde, 49 Einheiten Rasenerde, keine Ackerkrume und 21 Einheiten Gemüseanbau herstellen. Bei dieser Kombination beträgt die Maximalgewinnspanne DM 4425,89.

Sie können eine lange Solver-Berechnung unterbrechen bzw. eine Pause machen, indem Sie die (Esc)-Taste drücken.

Der Solver speichert die Einstellungen aus dem Dialogfeld in der Tabelle mit dem Modell. Weil das Dialogfeld Solver-Parameter frühere Einstellungen speichert, können Sie den Solver ohne weiteres mit anderen Nebenbedingungen erneut laufen lassen. Sie können z. B. das Tabellenblatt neu einstellen, indem Sie in den Zellen G5:G8 Nullwerte eingeben und den Solver erneut starten. Sie können die Einstellungen Ihrer vorhergehenden Problemstellung und die dazugehörige Lösung sehen.

Nach Finden einer Lösung können Sie die Bezüge, die Sie im Feld Veränderbare Zellen verwendet haben, speichern, um sie im Feld Veränderbare Zellen des Szenario-Manager zu verwenden. Wollen Sie den Szenario-Manager verwenden, um die vom Solver gefundene Lösung zu speichern, aktivieren Sie die Schaltfläche Szenario speichern (siehe Bild 28.10). Wird das Dialogfeld Szenario speichern angezeigt, geben Sie den gewünschten Namen ein. Dieses so benannte Szenario speichert die Lösungswerte für die im Textfeld Veränderbare Zellen festgelegten Zellen, die der Solver gefunden hat. Sie können mehrere Antwort-Szenarios speichern und sie dann mit dem Szenario-Manager prüfen und vergleichen.

Wollen Sie Einstellungen speichern, ohne den Solver zu starten, geben Sie die Einstellungen wie in den oben gegebenen Anweisungen ein, wählen Sie die Optionen und aktivieren Sie sofort die Schaltfläche OK und dann die Schaltfläche Schliessen.

Ändern von beschränkten Ressourcen

In wirklichen Leben kommt es vor, daß sich Ressourcenbeschränkungen ändern. Sie können die Auswirkungen einer solchen Änderung auf Solver-Lösungen sehen, wenn Sie die Nebenbedingungen im Tabellenblatt ändern und den Solver erneut starten.

Angenommen, die Verantwortlichen der Firma Spottbillig erhalten einen Telefonanruf mit dem Angebot, daß sie 100 Kilo Mineralien bekommen können, und nur die Benzinkosten für den Transport übernehmen müßten. Für DM 10,- könne die Firma weitere 100 Kilo Mineralien bekommen – das wären Minimalkosten von 10 Pfennigen pro Kilo. Sollte die Firma diese Gelegenheit wahrnehmen?

Um die Gewinnspanne für weitere 100 Kilo Mineralien zu suchen, ändern Sie den Bestand an Mineralien in Zelle E12 von 3500 auf 3600. Starten Sie den Solver zur erneuten Berechnung und verwenden Sie dabei die gleichen Einstellungen wie bei dem vorherigen Modell. Speichern Sie die Lösung, so daß Sie mit Ihrem Tabellenblatt für das nächste Modell gerüstet sind.

Das Hinzufügen von 100 Kilo Mineralien hat die Gewinnspanne von DM 4425,89 bei 3500 Kilo Mineralien auf DM 4464,24 bei 3600 Kilo Mineralien erhöht. Die Mineralien kosten DM 10,–, steuern aber DM 38,35 zum Gewinn bei. Das Ganze lohnt sich also.

Tatsächlich hätten Sie diese Frage auch beantworten können, ohne eine Neuberechnung mit dem Solver durchzuführen, wenn Sie über die ersten Ergebnisse einen Sensitivitätsbericht erstellt hätten. Der Sensitivitätsbericht zeigt Ihnen unter anderem die Auswirkungen von kleinen Änderungen in den Nebenbedingungen: Diese Werte werden als Dualwerte oder Schattenpreise oder (bei nichtlinearen Aufgaben) als Lagrangescher Multiplikatoren bezeichnet. In diesem Fall ist der Dualwert, der für die Nebenbedingung bzgl. der Mineralien berichtet wird 0,3835 oder 38,35 Pfennige pro Kilo.

Ändern von Nebenbedingungen

Im wirklichen Leben bleibt nicht vieles lange gleich. Alles befindet sich im Zustand dauernder Veränderung. Mit dem Solver können Sie jedoch sicher sein, daß Sie schnell zur Optimallösung gelangen, auch wenn sich die Bedingungen ändern.

Angenommen, einer der Hauptabnehmer der Produkte der Firma Spottbillig ruft an und teilt mit, daß 10 Einheiten der Mischung Ackerkrume benötigt werden. Die Leiterin der Firma prüft das Tabellenblatt und stellt fest, daß diesmal gar keine Mischung Ackerkrume produziert wird. Sie beschließt, eine Nebenbedingung hinzuzufügen, daß 10 Einheiten der Mischung Ackerkrume für diesen Kunden produziert werden müssen. Wie wirkt sich diese Änderung auf die Gewinnspanne aus?

Um den Effekt der Bereitstellung von 10 Einheiten Erde der Sorte Ackerkrume zu sehen, wählen Sie im Menü Extras den Befehl Solver, um das Dialogfeld Solver-Parameter zu öffnen. Sie müssen die neue Nebenbedingung hinzufügen und dann den Solver erneut laufen lassen. Um die Nebenbedingung hinzuzufügen, gehen Sie folgendermaßen vor:

• Wählen Sie Hinzufügen und geben Sie dann die Nebenbedingung

$G$7>=010

ein. Diese Formel legt fest, daß mindestens 10 Einheiten der Sorte Ackerkrume hergestellt werden müssen. Aktivieren Sie die Schaltfläche OK.

• Aktivieren Sie die Schaltfläche Lösen, um die optimale Gewinnspanne anzuzeigen.

Die neue Lösung, die eine niedrigere Grenze von 10 Einheiten für die Sorte Ackerkrume verwendet und die zusätzlichen 100 Kilo Mineralien mit berücksichtigt, führt zu einem Ergebnis von DM 4039,10. Dieser Betrag liegt um DM 425,14 unter der Gewinnspanne, die nach Hinzufügen der 100 Kilo Mineralien erzielt worden war. Das heißt, wird der Wunsch des langjährigen Kunden berücksichtigt, verursacht das kurzfristig Kosten, mag aber langfristig die Loyalität des Kunden stärken und das Ansehen der Firma bei der Kundschaft erhöhen. Die geringfügigen Kosten für die Produktion der Sorte Ackerkrume ist auch im Sensitivitätsbericht reflektiert. Der mit der Sorte Ackerkrume verbundene Dualwert, auch bezeichnet als Reduktionskosten oder (bei nichtlinearen Modellen) Reduktionsgradient beträgt 42,514. Mit anderen Worten gibt die Firma eine Gewinnspanne von DM 42,51 pro Kilo produzierter Sorte Ackerkrume auf (anstatt die Rohstoffe zur Herstellung anderer Produkte zu verwenden).

Sie können Nebenbedingungen löschen, indem Sie diese selektieren und die Schaltfläche Löschen aktivieren. Wählen Sie die Option Zurücksetzen, um alle Einstellungen im Dialogfeld Solver-Parameter zu löschen.

Einstellen von Ganzzahlen-Bedingungen

Laut Zelle G5 empfiehlt Ihnen der Solver momentan, 1 Einheit der Gartenmischung herzustellen. Wählen Sie die Zelle G5 und betrachten die Bearbeitungsleiste, sehen Sie, daß der Solver tatsächlich einen Optimalwert von ***0,8796080261316*** berechnet hat. Der angezeigte Wert wurde aufgrund der Formatierung der Zellen auf eine Ganzzahl gerundet. Die Antwort in der Zelle Gewinnspanne enthält das Einkommen aus diesem Teil einer Einheit der Mischung Gartenmischung. Angenommen, das Produkt Gartenmischung kann nur in ganzen Einheiten verpackt und verkauft werden. Um den Solver zu zwingen, nur ganze Werte für die Einheiten zuzulassen, wählen sie im Menü Extras den Befehl Solver, um das Dialogfeld Solver-Parameter zu öffnen. Dann fügen Sie eine Ganzzahl-Nebenbedingung hinzu und starten den Solver erneut. Um die Ganzzahlbedingung hinzuzufügen, gehen Sie folgendermaßen vor:

• Im Dialogfeld Solver-Parameter wählen sie die Option Hinzufügen.
• Wählen Sie als Zellbezug die Zellen $G$5:$G$8.
• Wählen Sie in der Drop-down-Liste mit Vergleichssymbolen die Option ganzz. Der Inhalt des Feldes Nebenbedingung ändert sich in Ganzzahlig.
• Wählen Sie die Schaltfläche OK. Die Nebenbedingung $G$5:$G$8 = Ganzzahlig wird in der Liste Nebenbedingungen angezeigt.
• Aktivieren Sie die Schaltfläche Lösen. Der Solver sucht eine Lösung, bei der alle Einheitswerte Ganzzahlen sind.

Die neue Gewinnspanne hat als Ergebnis DM 4023,11 und die empfohlene Produktionsmenge für die Sorte Gartenmischung ist nun Null. Beachten Sie, daß der Solver nicht einfach die frühere Lösung auf die nächste Ganzzahl rundet. In diesem Problemmodell würde er damit die Grenzen der Nebenbedingungen überschreiten; bei anderen Problemmodellen können andere Kombinationen von Ganzzahlwerten für die Variablen günstiger sein als das einfache Runden. Beachten Sie auch, daß die Gewinnspanne niedriger ist als vorher: Zusätzliche Nebenbedingungen, darunter auch die Bedingung der Ganzzahlen, können das Ergebnis nur verschlechtern oder gleich belassen.

Ganzzahlenbedingungen finden weite Verwendung, die über den Rahmen dieses Beispiels weit hinaus gehen. Setzen Sie ein Untergrenze bei 0 und eine Obergrenze bei 1 und legen eine Ganzzahlbedingung für die gleiche variable Zelle fest, so muß die Zelle entweder 0 oder 1 als Optimallösung anzeigen. Eine solche Variable kann eine Ja- oder Nein-Entscheidung darstellen, wie z. B. die Frage, ob eine Maschine erneut eingesetzt werden soll oder eine neue Fabrik gebaut werden soll, wobei damit verbundene Fixkosten anfallen können. Obwohl der Solver Problemmodelle mit ungleichmäßigen Funktionen (wie die Funktionen WENN und VERWEIS) nicht direkt handhaben kann, wird der gleiche Effekt oft mit Ganzzahlbedingungen ersichtlich und mit diesen kann der Solver umgehen.

Excel 97 bietet zusätzliche Auswahlmöglichkeiten in der Drop-down-Liste der Vergleichssymbole: Die Option bin, die im Feld Nebenbedingung als Binär angezeigt wird, ist eine Abkürzung für die Bedingungen a>=0, a<=1 und der Bedingung ganzz für die Zellen der gleichen Variablen. Man bezeichnet sie oft als »0-1« oder »binär-Ganzzahl«-Variable.

Bedenken Sie jedoch, daß Ganzzahlbedingungen die Lösungszeit enorm verlängern können. Es ist bekannt, daß schon mit einigen hundert Ganzzahlvariablen Probleme auftreten, die so schwierig sind, daß sie noch nie optimal gelöst werden konnten, nicht einmal mit den schnellsten Supercomputern.

Ändern von Solver-Optionen

Sie können die Methoden, die der Solver zur Antwortsuche verwendet, sowie die Bearbeitungszeit des Solvers ändern oder die Genauigkeit der Lösung festlegen. Aktivieren Sie im Dialogfeld Solver-Parameter die Schaltfläche Optionen, wird das Dialogfeld Optionen angezeigt (siehe Bild 28.11). Verwenden Sie diese Optionen, um die Arbeitsweise des Solvers zu steuern. Die Standardeinstellungen eignen sich für die meisten Problemmodelle. Die Tabelle 28.3 zeigt aber noch andere Optionen und Möglichkeiten.

Abbildung 28.11: Im Dialogfeld Optionen können Sie auf verschiedene
Weise die Arbeitsweise des Solvers bei der Lösungsberechnung steuern.

Tabelle 28.3: Solver-Einstellungen im Dialogfeld Optionen. (Forts.)

Die Werte für die Option Genauigkeit sollten nicht sehr viel kleiner sein als der Standard. Aufgrund der Genauigkeitseinstellungen von Computerberechnungen muß man davon ausgehen, daß die vom Solver berechneten Werte sich von den erwarteten oder »wahren« Werten um einen kleinen Betrag unterscheiden. Geben Sie für die Genauigkeit andererseits einen zu hohen Wert ein, hätte das zur Folge, daß die Nebenbedingungen allzu leicht erfüllt werden. Werden Ihre Nebenbedingungen nicht erfüllt, weil die Werte, die Sie berechnen, zu hoch sind, wie z. B. DM in Millionenhöhe, wählen Sie die Option Automatische Skalierung anwenden anstatt die Einstellungen für die Option Genauigkeit zu ändern.

Bei Excel 97 können Sie die Option Automatische Skalierung anwenden sowohl für lineare als auch für nichtlineare Modelle verwenden. In den früheren Excel-Versionen konnte diese Option nur für nichtlineare Problemmodelle verwendet werden. Als Verbesserung, die hiermit in Zusammenhang steht, verwendet der Solver in den Tests zur Prüfung, ob ein Modell linear ist, ebenfalls die automatische Skalierung. Frühere Versionen des Solver gaben manchmal die Rückmeldung, daß lineare Modelle mit Werten von sehr unterschiedlicher Größenordnung nicht linear seien; bei dieser neuen Version des Solvers sollte dieses Problem nun nicht mehr auftreten.

Erstellen von Solver-Berichten

Der Solver kann Berichte erstellen, in denen er die Lösungsergebnisse zusammenfaßt. Diese Berichte sind hilfreich, um verschiedene Lösungen zu vergleichen oder um eine Lösung auf ihre Sensitivität gegenüber kleinen Veränderungen der Nebenbedingungen oder der Abhängigkeit der Zielzelle von den Variablen zu prüfen.

Der Solver kann drei Typen von Berichten erstellen: Den Antwortbericht, den Sensitivitätsbericht oder den Grenzwertbericht. Um nach dem Lösen eines Modells einen Bericht zu erstellen, wählen Sie im Dialogfeld Ergebnis in der Liste Berichte einen oder mehrere Berichte aus. (siehe Bild 28.10). Aktivieren Sie die Schaltfläche Hilfe, um weitere Informationen über den Inhalt des jeweiligen Berichtetyps zu erhalten. Jeder Bericht wird auf einem eigenen Tabellenblatt erstellt. Wählen Sie ein Blattregister oder drücken Sie die Tasten (Strg)+(Bild ab) und (Strg)+(Bild auf), um die Berichte und das Originalmodellblatt durchzugehen.

Der Antwortbericht faßt die Ursprungs- und Endwerte der Variablen, die Zielzelle und die Nebenbedingungen zusammen. Der Sensitivitätsbericht bringt Informationen über »Minimalveränderungen«, u. a. über Dualwerte. Der Grenzwertbericht zeigt, wie die Lösung sich verändert, wenn jeweils eine Variable maximiert oder minimiert wird, die anderen jedoch konstant bleiben.

Das Speichern und Laden von Solver-Daten

Der Solver speichert die letzten Einstellungen, die Sie zum Lösen eines Problemmodells verwendet haben (sie wurden in den Dialogfeldern Solver-Parameter und Optionen angezeigt. Die letzten Einstellungen werden unter Namen gespeichert, die nicht angezeigt sind und werden mit dem Tabellenblatt gespeichert, wenn sie den Befehl Datei speichern wählen.) Beim nächsten Öffnen des Tabellenblattes und Starten des Solver wird das Dialogfeld Solver-Parameter so angezeigt, wie Sie es zuletzt verwendet haben. Jedes Tabellenblatt in einer Arbeitsmappe zeigt noch die letzten Solver-Einstellungen an, die für dieses Tabellenblatt verwendet wurden.

Sie können festgelegte Einstellungen für ein Solver-Problemmodell auch speichern. Sie können z. B. die Einstellung bestimmter Nebenbedingungen speichern. Jede dieser Nebenbedingungen kann in Zellen im Tabellenblatt gespeichert werden und Sie haben somit schnellen Zugriff auf die benötigten Einstellungen.

Sie können verschiedene Solver-Modelle (Einstellungen) speichern und laden, indem Sie im Dialogfeld Solver-Parameter die Schaltfläche Optionen aktivieren. Um die Solver-Einstellungen zu speichern, gehen Sie folgendermaßen vor:

• Zeigen Sie die Dialogfelder Solver-Parameter und Optionen mit den Einstellungen an, die Sie speichern wollen.
• Haben Sie das Dialogfeld Optionen noch nicht angezeigt, wählen Sie die Schaltfläche Optionen.
• Wählen Sie die Option Modell speichern.
• Im Dialogfeld Modell speichern wählen Sie einen Zellbereich im Tabellenblatt, der der Anzahl von Nebenbedingungen entspricht plus drei zusätzliche Zellen.

Der Bereich kann eine bestimmte Form haben. Ist er zu groß, macht das nichts. Excel weist Sie darauf hin, wenn der Bereich nicht groß genug ist. Wählen Sie eine einzelne Zelle, erweitert Excel den Bereich automatisch nach unten um die erforderliche Zellenanzahl.

• Aktivieren Sie die Schaltfläche OK, um den im Textfeld Modellbereich auswählen angegebenen Bereich zuzuordnen und aktivieren Sie die Schaltfläche OK erneut, wenn das Dialogfeld Solver-Optionen wieder angezeigt wird.
• Aktivieren Sie im Dialogfeld Solver-Parameter die Schaltfläche Schliessen, um zum Tabellenblatt zurückzukehren.

Der Bereich enthält nun die Einstellungen aus den Dialogfeldern Solver-Parameter und Optionen. Bild 28.12 zeigt ein Beispiel für gespeicherte Einstellungen.

Verwenden Sie die angegebenen Schritte zum Speichern eines Modells, haben Sie alle Informationen, die der Solver benötigt, gespeichert: die veränderbaren Zellbezüge, den Optimierungstyp, die Nebenbedingungen und sämtliche Optionen. Mit diesen Informationen können Sie Probleme aus völlig unterschiedlichen Blickwinkeln betrachten. Hat der Solver die Berechnung fertiggestellt, können Sie mit der Option Modell speichern nur die Werte für die veränderbaren Zellen speichern. Sie können den Szenario-Manager verwenden, um sich schnell zwischen unterschiedlichen Ergebnissen von unterschiedlichen Solver-Problemmodellen zu bewegen, oder um einen Bericht über die verschiedenen Lösungsmöglichkeiten zu erstellen.

Um Einstellungen zu laden oder um ein gespeichertes Solver-Modell erneut zu starten, gehen Sie folgendermaßen vor:

• Wählen Sie im Menü Extras die Option Solver.
• Aktivieren Sie die Schaltfläche Optionen.
• Aktivieren Sie die Schaltfläche Modell laden.
• Wählen Sie den Zellbereich, in dem sich das Modell befindet und aktivieren Sie die Schaltfläche OK. Sind frühere Einstellungen vorhanden, wird eine Warnmeldung angezeigt. Aktivieren Sie die Schaltfläche OK.
• Wählen Sie die Option Lösen, damit der Solver mit dem geladenen Modell arbeitet oder wählen Sie Schliessen, um das Modell später zu verwenden.

Abbildung 28.12: Die Einstellungen der Dialogfelder Solver-Parameter und Optionen können als Zelleinträge gespeichert werden, um die Verwendung der gleichen Einstellungen zu einem späteren Zeitpunkt zu erleichtern.

Das Übertragen von Solver-Problemmodellen zwischen Lotus 1-2-3 und Excel

Die früheren Versionen von Lotus 1-2-3 und von Excel waren in der Lage, Tabellenblätter beider Dateiformate zu laden und zu speichern – allerdings war es nicht ohne weiteres möglich, ein Solver-Modell (mit den Einstellungen für Variablen, Nebenbedingungen und Zielzelle) von einem Programm in das andere zu übertragen. Mit Lotus 1-2-3 97 und Excel 97 können Sie jedoch nun auch dies relativ einfach bewerkstelligen. Obwohl keines der Programme die »aktuellen« mit jedem Tabellenblatt gespeicherten Einstellungen des anderen Programms übernimmt, können sie das jeweilige Solver-Modell mit den Funktionen Modell aden und Modell speichern übertragen.

Von Lotus 1-2-3 nach Excel übertragen

Um ein Solver-Modell von Lotus 1-2-3 97 auf Excel 97 zu übertragen, gehen sie folgendermaßen vor:

• Starten Sie im Lotus 1-2-3 Dialogfeld Solver-Optionen und wählen Sie die Option Modell speichern und speichern Sie ihre Solver-Modelleinstellungen in einem Zellbereich.
• Wählen Sie im Menü Datei den Befehl Speichern unter und speichern Sie die Arbeitsmappe im WK4-Format der Lotus 1-2-3 Version 5.
• Öffnen sie die gespeicherte WK4-Datei unter Excel.
• Wählen Sie im Menü Extras den Befehl Solver und aktivieren Sie die Schaltfläche Optionen, um das Dialogfeld Solver-Optionen anzuzeigen.
• Wählen Sie die Option Modell laden und wählen Sie den Zellbereich, in dem sich die Einstellungen des Solver-Modells befinden. Dann aktivieren Sie die Schaltfläche OK.

Sie können dann im Dialogfeld Optionen die Schaltfläche OK aktivieren und sofort die Option Lösen wählen.

Von Excel nach Lotus 1-2-3 übertragen

Sie können Solver-Modelle auch in anderer Richtung, von Excel nach Lotus 1-2-3 übertragen:

• Wählen Sie im Menü Datei den Befehl Speichern unter und speichern Sie die Excel-Arbeitsmappe im WK4-Format. Dieser Schritt gibt dem Excel-Solver einen Hinweis, für die Einstellungen ein Format zu verwenden, daß sowohl von Excel als auch von Lotus 1-2-3 gelesen werden kann.
• Sie beginnen im Dialogfeld Optionen bei Excel und wählen die Option Modell speichern und speichern die Einstellungen des Solver-Modells in einem Zellbereich.
• Wählen Sie im Menü Datei den Befehl Speichern, um die WK4-Datei zusammen mit den Einstellungen des Solver-Modells zu speichern.
• Öffnen Sie die gespeicherte WK4-Datei unter Lotus 1-2-3 97.
• Wählen Sie die Optionen Bereich, Analysieren, Solver und wählen Sie Optionen, um das Dialogfeld Solver-Optionen anzuzeigen.
• Wählen Sie Modell laden und markieren Sie den Zellbereich, der die Modelleinstellungen enthält. Dann aktivieren Sie die Schaltfläche OK.

Auch hier können Sie im Dialogfeld Optionen die Schaltfläche OK aktivieren und dann sofort die Option Lösen wählen.

Verwenden der Beispielmodelle

Excel wird mit Beispieltabellen geliefert, die den Solver zum Suchen eine Optimal- oder Bestlösung verwenden. Obwohl diese Beispiele vereinfacht sind, enthalten Sie viele der Modelltypen, auf die der Solver zugeschnitten ist. Bei diesen Modellen bedeutet die Verwendung des Solver im Vergleich zur empirischen Lösungsmethode eine Zeitersparnis.

Die Beispieldateien befinden sich im Ordner \EXCEL\BEISPIEL\SOLVER in den Dateien SOLVERMP und SOLVTAB. Der Ordner \EXCEL befindet sich unter dem Verzeichnis, unter dem MS Office 97 installiert wurde. Verwenden Sie im Menü Datei den Befehl Öffnen, um die Beispieldatei zu öffnen. Es gibt sieben Beispielblätter. Wählen Sie das jeweilige Blattregister und wählen Sie dann im Menü Extras den Befehl Solver. Sie können die Einstellungen im Dialogfeld Solver-Parameter prüfen. Diese Einstellungen sind auch kurz in dem Text beschrieben, der unten in jedem Tabellenblattmodell angezeigt ist. Sie können den Solver mit diesen Beispielen verwenden und dabei die in diesem Kapitel erklärten Anweisungen verwenden. Verwenden Sie die Beispiele, um mehr über die Verwendung des Solver im Umgang mit Problemmodellen des wirklichen Lebens zu erfahren.

Um den Solver für ein Beispiel zu verwenden, öffnen Sie ein Tabellenblatt, wählen Sie im Menü Extras den Befehl Solver und wählen Sie die Option Lösen. Wollen Sie die gefundenen Lösungen erhalten, so speichern Sie das Tabellenblatt unter einem anderen Namen, um das Originalbeispiel zu erhalten.

Weitere Informationen über die Fähigkeiten und Anwendungsmöglichkeiten des Solver sowie über weiter entwickelte Versionen des Solver erhalten Sie im Web unter folgender Adresse: http://www.frontsys.com (englischsprachig) Das ist die HomePage der Firma Frontline Systems, die den Solver für Microsoft entwickelt hat. Unter dieser Adresse finden Sie über 60 Seiten nützliche Informationen und viele weitere Beispiele für Solver-Tabellenmodelle, die Sie bei Bedarf für Ihren Gebrauch übernehmen können.