Excel Solver: Ergebnisse optimieren, Einschränkungen hinzufügen und Lösungen als Szenarien speichern

Sie richten Ihr Excel-Solver- Modell mithilfe des Dialogfelds Solver-Parameter ein. Sie verwenden das Feld Ziel festlegen, um die Zielzelle anzugeben, und Sie verwenden die Gruppe An, um Excel Solver mitzuteilen, was Sie von der Zielzelle erwarten möchten: den maximal möglichen Wert; der minimal mögliche Wert; oder einen bestimmten Wert. Schließlich verwenden Sie das Feld Durch Ändern von Variablenzellen, um die Zellen anzugeben, die Solver verwenden kann, um Werte einzufügen, um das Ergebnis zu optimieren.

Optimieren von Excel Solver-Ergebnissen

Wenn Solver eine Lösung findet, können Sie entweder Solver-Lösung beibehalten oder Originalwerte wiederherstellen auswählen. Wenn Sie Solver-Lösung beibehalten auswählen, ändert Excel das Arbeitsblatt dauerhaft. Sie können die Änderungen nicht rückgängig machen.

Wenn Ihr Solver-bereites Arbeitsblattmodell einsatzbereit ist, folgen Sie den folgenden Schritten, um mit Solver ein optimales Ergebnis für Ihr Modell zu erzielen:

Wählen Sie Daten → Solver.
Excel öffnet das Dialogfeld Solver-Parameter.

Geben Sie im Feld Ziel festlegen die Adresse der Zielzelle Ihres Modells ein.
Beachten Sie, dass Solver automatisch eine absolute Zellenadresse eingibt, wenn Sie auf die Zelle klicken, um sie einzugeben (z. B. $B$14 statt B14). Solver funktioniert so oder so.

Wählen Sie in der Gruppe An eine Option aus:

• Max: Gibt den maximal möglichen Wert zurück.
• Min: Gibt den minimal möglichen Wert zurück.
• Wert von: Geben Sie eine Zahl ein, um die Zielzelle auf diese Zahl festzulegen.

  Für das Beispielmodell wurde Wert von ausgewählt und 0 in das Textfeld eingegeben.

Geben Sie im Feld Durch Ändern variabler Zellen die Adressen der Zellen ein, die Solver ändern soll, während es nach einer Lösung sucht.
Im Beispiel sind die sich ändernden Zellen B4 und C4. Die folgende Abbildung zeigt das ausgefüllte Dialogfeld Solver-Parameter.

Das ausgefüllte Dialogfeld Solver-Parameter.

Klicken Sie auf Lösen.
Solver geht zur Sache. Während Solver an dem Problem arbeitet, werden die Dialogfelder Testlösung anzeigen möglicherweise ein- oder mehrmals angezeigt.

Klicken Sie in einem beliebigen angezeigten Dialogfeld Testlösung anzeigen auf Weiter, um die Dinge fortzusetzen.
Wenn die Optimierung abgeschlossen ist, zeigt Excel das Dialogfeld Solver-Ergebnisse an.

Das Dialogfeld Solver-Ergebnisse und die Lösung für das Break-Even-Problem.

Wählen Sie die Option Solver-Lösung beibehalten.
Wenn Sie das Ergebnis nicht akzeptieren möchten, wählen Sie stattdessen die Option Originalwerte wiederherstellen.

OK klicken.

Sie können Solver bitten, einen oder mehrere Berichte anzuzeigen, die Ihnen zusätzliche Informationen zu den Ergebnissen liefern. Verwenden Sie im Dialogfeld Solver-Ergebnisse die Liste Berichte, um jeden anzuzeigenden Bericht auszuwählen:

• Antwort: Zeigt Informationen über die Zielzelle, Variablenzellen und Einschränkungen des Modells an. Für die Zielzelle und die Variablenzellen zeigt Solver die ursprünglichen und endgültigen Werte an.
• Empfindlichkeit: Versucht zu zeigen, wie empfindlich eine Lösung auf Änderungen in den Formeln des Modells reagiert. Das Layout des Sensitivitätsberichts hängt vom verwendeten Modelltyp ab.
• Grenzen: Zeigt die Zielzelle und ihren Wert sowie die Variablenzellen und ihre Adressen, Namen und Werte an.

Excel Solver kann eine von mehreren Lösungsmethoden verwenden. Verwenden Sie im Dialogfeld Solver-Parameter die Liste Lösungsmethode auswählen, um eine der folgenden Optionen auszuwählen:

• Simplex LP: Verwenden Sie diese Option, wenn Ihr Arbeitsblattmodell linear ist. Einfach ausgedrückt ist ein lineares Modell ein Modell, bei dem die Variablen nicht potenziert werden und keine der sogenannten transzendenten Funktionen – wie SIN und COS – verwendet werden.
• GRG Nichtlinear: Verwenden Sie diese Option, wenn Ihr Arbeitsblattmodell nichtlinear und glatt ist. Im Allgemeinen ist ein glattes Modell ein Modell, bei dem ein Graph der verwendeten Gleichung keine scharfen Kanten oder Brüche aufweist.
• Evolutionär: Verwenden Sie diese Option, wenn Ihr Arbeitsblattmodell nichtlinear und nicht glatt ist.

Müssen Sie sich um all das Sorgen machen? Mit ziemlicher Sicherheit nicht. Excel Solver verwendet standardmäßig GRG Nonlinear, und das sollte für fast alles funktionieren, was Sie mit Solver tun.

Hinzufügen von Einschränkungen zu Excel Solver

Die reale Welt stellt Formeln Einschränkungen und Bedingungen auf. Eine Fabrik kann eine maximale Kapazität von 10.000 Einheiten pro Tag haben, die Anzahl der Mitarbeiter in einem Unternehmen darf nicht negativ sein und Ihre Werbekosten können auf 10 Prozent der Gesamtausgaben begrenzt sein.

Angenommen, Sie führen eine Break-Even-Analyse für zwei Produkte durch. Wenn Sie die Optimierung ohne Einschränkungen durchführen, kann Solver einen Gesamtgewinn von 0 erreichen, indem ein Produkt mit geringem Verlust und das andere mit geringem Gewinn eingestellt wird, wobei sich Verlust und Gewinn gegenseitig aufheben. Wenn Sie sich das vorherige Bild genau ansehen, hat Solver genau das getan. Um eine echte Break-Even-Lösung zu erhalten, ziehen Sie es möglicherweise vor, beide Produktgewinnwerte als 0 anzuzeigen.

Solche Einschränkungen und Bedingungen sind Beispiele für das, was Solver als Einschränkungen bezeichnet. Das Hinzufügen von Einschränkungen weist Solver an, eine Lösung zu finden, damit diese Bedingungen nicht verletzt werden.

So führen Sie Solver mit Einschränkungen aus, die der Optimierung hinzugefügt wurden:

Wählen Sie Daten → Solver.
Excel öffnet das Dialogfeld Solver-Parameter.

Verwenden Sie das Feld Ziel festlegen, die Gruppe An und das Feld Durch Ändern von Variablenzellen, um Solver wie oben beschrieben einzurichten.

Klicken Sie auf Hinzufügen.
Excel zeigt das Dialogfeld Einschränkung hinzufügen an.

Geben Sie im Feld Zellreferenz die Adresse der Zelle ein, die Sie einschränken möchten.
Sie können die Adresse eingeben oder die Zelle auf dem Arbeitsblatt auswählen.

Wählen Sie in der Dropdown-Liste den Operator aus, den Sie verwenden möchten.
Meistens verwenden Sie einen Vergleichsoperator, z. B. gleich (=) oder größer als (>). Verwenden Sie den int-Operator (Ganzzahl), wenn eine Einschränkung wie die Gesamtzahl der Mitarbeiter ein ganzzahliger Wert anstelle einer reellen Zahl sein soll (d. h. eine Zahl mit einer Dezimalkomponente; Sie können keine 10,5 Mitarbeiter haben!). Verwenden Sie den bin (binären) Operator, wenn Sie eine Einschränkung haben, die entweder WAHR oder FALSCH (oder 1 oder 0) sein muss.

If you chose a comparison operator in Step 5, in the Constraint box, enter the value by which you want to restrict the cell.
This image shows an example of a completed Add Constraint dialog box. In the example model, this constraint tells Solver to find a solution such that the product profit of the Inflatable Dartboard (cell B12) is equal to 0.

The completed Add Constraint dialog box.

To specify more constraints, click Add and repeat Steps 4 through 6, as needed.

For the example, you add a constraint that asks for the Dog Polisher product profit (cell C12) to be 0.

Click OK.

Excel returns to the Solver Parameters dialog box and displays your constraints in the Subject to the Constraints list box.

Click Solve.

Klicken Sie in einem beliebigen angezeigten Dialogfeld Testlösung anzeigen auf Weiter, um die Dinge fortzusetzen.
Das Bild unten zeigt das Beispiel einer Break-Even-Lösung mit den hinzugefügten Einschränkungen. Beachten Sie, dass nicht nur die Gesamtgewinnzelle (B14) auf 0 gesetzt ist, sondern auch die beiden Produktgewinnzellen (B12 und C12).

Das Dialogfeld Solver-Ergebnisse und die endgültige Lösung für das Break-Even-Problem.

Wählen Sie die Option Solver-Lösung beibehalten.
Wenn Sie das Ergebnis nicht akzeptieren möchten, wählen Sie stattdessen die Option Originalwerte wiederherstellen.

OK klicken.

Sie können maximal 100 Einschränkungen hinzufügen. Wenn Sie außerdem eine Abhängigkeit ändern müssen, bevor Sie mit der Lösung beginnen, wählen Sie die Abhängigkeit im Listenfeld Abhängig von den Abhängigkeiten aus, klicken Sie auf Ändern, und nehmen Sie dann Ihre Anpassungen im angezeigten Dialogfeld Abhängigkeit ändern vor. Wenn Sie eine nicht mehr benötigte Einschränkung löschen möchten, wählen Sie die Einschränkung aus und klicken Sie dann auf Löschen.

Speichern Sie eine Excel Solver-Lösung als Szenario

Immer wenn Sie ein Tabellenkalkulationsmodell haben, das einen zusammenhängenden Satz von Eingabewerten verwendet – bekannt als sich ändernde Zellen – haben Sie das, was Excel als Szenario bezeichnet. Bei Solver sind diese sich ändernden Zellen seine variablen Zellen, so dass eine Solver-Lösung einer Art Szenario in Excel gleichkommt . Solver bietet Ihnen jedoch keine einfache Möglichkeit, eine bestimmte Lösung zu speichern und erneut auszuführen. Um dieses Problem zu umgehen, können Sie eine Lösung als Szenario speichern, das Sie später mithilfe der Szenario-Manager-Funktion von Excel abrufen können.

Führen Sie diese Schritte aus, um eine Solver-Lösung als Szenario zu speichern:

Wählen Sie Daten → Solver.
Excel öffnet das Dialogfeld Solver-Parameter.

Verwenden Sie das Feld Ziel festlegen, die Gruppe An, das Feld Durch das Ändern von Variablenzellen und die Liste Abhängig von den Einschränkungen, um Solver wie oben beschrieben einzurichten.

Klicken Sie auf Lösen.

Jedes Mal, wenn das Dialogfeld Testlösung anzeigen angezeigt wird, wählen Sie Weiter.
Wenn die Optimierung abgeschlossen ist, zeigt Excel das Dialogfeld Solver-Ergebnisse an.

Klicken Sie auf Szenario speichern.
Excel zeigt das Dialogfeld Szenario speichern an.

Geben Sie im Dialogfeld Szenarioname einen Namen für das Szenario ein, und klicken Sie dann auf OK.
Excel führt Sie zum Dialogfeld Solver-Ergebnisse zurück.

Wählen Sie die Option Solver-Lösung beibehalten.
Wenn Sie das Ergebnis nicht akzeptieren möchten, wählen Sie stattdessen die Option Originalwerte wiederherstellen.

OK klicken.