So verwenden Sie den Excel 2019-Solver

Obwohl die Excel- Befehle " Datentabelle" und " Zielsuche" für einfache Probleme, bei denen die direkte Beziehung zwischen den Eingaben und Ergebnissen in einer Formel bestimmt werden muss, problemlos funktionieren, müssen Sie bei komplexeren Problemen das Solver-Add-In verwenden. Verwenden Sie beispielsweise den Solver, um die beste Lösung zu finden, wenn Sie mehrere Eingabewerte in Ihrem Excel-Modell ändern und diesen Werten und/oder dem Ausgabewert Einschränkungen auferlegen müssen.

Das Solver-Add-In verwendet iterative Methoden, um die „beste“ Lösung anhand der Eingaben, der gewünschten Lösung und der von Ihnen auferlegten Einschränkungen zu finden. Bei jeder Iteration wendet das Programm eine Trial-and-Error-Methode an (basierend auf der Verwendung linearer oder nichtlinearer Gleichungen und Ungleichungen), die versucht, der optimalen Lösung näher zu kommen.

Denken Sie bei der Verwendung des Solver-Add-Ins daran, dass viele Probleme, insbesondere die komplizierteren, viele Lösungen haben. Obwohl der Solver angesichts der Startwerte, der Variablen, die sich ändern können, und der von Ihnen definierten Randbedingungen die optimale Lösung zurückgibt, ist diese Lösung oft nicht die einzig mögliche und möglicherweise nicht die beste Lösung für Sie. Um sicherzustellen, dass Sie die beste Lösung finden, sollten Sie den Solver mehr als einmal ausführen und die Anfangswerte jedes Mal anpassen, wenn Sie das Problem lösen.

Definieren Sie beim Einrichten des Problems für das Solver-Add-In in Ihrem Excel-Arbeitsblatt die folgenden Elemente:

• Zielzelle : Die Zielzelle in Ihrem Arbeitsblatt, deren Wert maximiert, minimiert oder auf einen bestimmten Wert gebracht werden soll. Beachten Sie, dass diese Zelle eine Formel enthalten muss.
• Variable Zellen: Die sich ändernden Zellen in Ihrem Arbeitsblatt, deren Werte angepasst werden sollen, bis die Antwort gefunden ist.
• Einschränkungszellen: Die Zellen, die die Grenzen enthalten, die Sie den sich ändernden Werten in den Variablenzellen und/oder der Zielzelle in der Zielzelle auferlegen.

Nachdem Sie das Problem mit diesen Parametern definiert haben und das Solver-Add-In das Problem lösen lässt, gibt das Programm die optimale Lösung zurück, indem es die Werte in Ihrem Arbeitsblatt ändert. An dieser Stelle können Sie die Änderungen im Arbeitsblatt beibehalten oder die ursprünglichen Werte im Arbeitsblatt wiederherstellen. Sie können die Lösung auch als Szenario speichern, um sie später anzuzeigen, bevor Sie die ursprünglichen Werte wiederherstellen.

Sie können das Solver-Add-In mit dem Szenario-Manager verwenden , um ein zu lösendes Problem einzurichten oder eine Lösung zu speichern, damit Sie sie zu einem späteren Zeitpunkt anzeigen können. Die sich ändernden Zellen, die Sie für den Szenario-Manager definieren, werden automatisch vom Solver übernommen und verwendet, wenn Sie diesen Befehl auswählen und umgekehrt. Außerdem können Sie die Lösung des Solvers für ein Problem als Szenario speichern (indem Sie im Dialogfeld Solver auf die Schaltfläche Szenario speichern klicken), das Sie dann mit dem Szenario-Manager anzeigen können.

Einrichten und Definieren des Problems in Excel 2019

Der erste Schritt beim Einrichten eines Problems, an dem der Solver arbeiten soll, besteht darin, das Arbeitsblattmodell zu erstellen, für das Sie die Zielzelle, die Variablenzellen und die Einschränkungszellen definieren.

Beachten Sie, dass der Solver ein Add-In-Dienstprogramm ist. Dies bedeutet, dass Sie vor der Verwendung sicherstellen müssen, dass das Solver-Add-In-Programm noch geladen ist, wie durch das Erscheinen der Solver-Schaltfläche in der Gruppe Analyse am Ende der Registerkarte Daten auf der Multifunktionsleiste angezeigt. Wenn diese Schaltfläche fehlt, können Sie Solver laden, indem Sie die Registerkarte Add-Ins des Dialogfelds Excel-Optionen öffnen (Alt+FTAA) und dann auf die Schaltfläche Go klicken, nachdem Sie sichergestellt haben, dass Excel-Add-Ins in der Dropdown-Liste Verwalten angezeigt wird Listenfeld direkt links davon. Aktivieren Sie dann das Kontrollkästchen Solver-Add-In im Dialogfeld Add-Ins, um ein Häkchen zu setzen, bevor Sie auf OK klicken, um das Dialogfeld zu schließen und das Add-In neu zu laden.

Um ein Problem mit dem Solver-Add-In zu definieren und zu lösen, nachdem Sie das Add-In geladen und Ihr Arbeitsblattmodell erstellt haben, gehen Sie folgendermaßen vor:

Klicken Sie auf die Solver-Befehlsschaltfläche in der Gruppe Analysieren am Ende der Registerkarte Daten der Multifunktionsleiste.

Excel öffnet das Dialogfeld Solver-Parameter.

Angeben der Parameter, die auf das Modell im Dialogfeld Solver-Parameter angewendet werden sollen.

Klicken Sie auf die Zielzelle im Arbeitsblatt oder geben Sie den Zellbezug oder den Bereichsnamen in das Textfeld Ziel festlegen ein.

Als nächstes müssen Sie die Einstellung An auswählen. Klicken Sie auf die Optionsschaltfläche Max, wenn der Wert der Zielzelle so groß wie möglich sein soll. Klicken Sie auf die Optionsschaltfläche Min, wenn der Wert der Zielzelle so klein wie möglich sein soll. Klicken Sie auf die Optionsschaltfläche Wert von und geben Sie dann einen Wert in das zugehörige Textfeld ein, wenn der Wert der Zielzelle einen bestimmten Wert erreichen soll.

Klicken Sie im Abschnitt An des Dialogfelds auf die entsprechende Optionsschaltfläche. Wenn Sie die Optionsschaltfläche Wert von auswählen, geben Sie den entsprechenden Wert in das zugehörige Textfeld ein.

Bestimmen Sie als Nächstes die variablen Zellen – d. h. diejenigen, die Solver ändern kann, um Ihr Gleich-Ziel zu erreichen.

Klicken Sie auf das Textfeld Durch Ändern variabler Zellen und wählen Sie dann die Zellen aus, die Sie im Arbeitsblatt ändern möchten, oder geben Sie ihre Zellbezüge oder den Bereichsnamen in das Textfeld ein.

Denken Sie daran, dass Sie zum Auswählen nicht benachbarter Zellen im Arbeitsblatt die Strg-Taste gedrückt halten müssen, während Sie auf jede Zelle in der Auswahl klicken. Damit Excel die sich ändernden Zellen basierend auf der ausgewählten Zielzelle für Sie auswählt, klicken Sie auf die Schaltfläche Rate rechts neben diesem Textfeld.

Bevor Solver Ihr Modell anpasst, können Sie Einschränkungen für die Zielzelle oder eine der sich ändernden Zellen hinzufügen, die ihre Grenzen beim Anpassen der Werte bestimmen.

(Optional) Klicken Sie im Dialogfeld Solver-Parameter rechts neben dem Listenfeld Betreff der Abhängigkeiten auf die Schaltfläche Hinzufügen.

Diese Aktion öffnet das Dialogfeld Einschränkung hinzufügen. Wählen Sie beim Definieren einer Einschränkung die Zelle aus, deren Wert Sie einschränken möchten, oder wählen Sie die Zelle im Arbeitsblatt aus oder geben Sie ihren Zellbezug in das Textfeld Zellbezug ein. Wählen Sie dann die Beziehung (=, <=,>= oder int für Integer oder bin für Binär) aus dem Dropdown-Listenfeld rechts aus und geben Sie (es sei denn, Sie haben int oder bin ) den entsprechenden Wert oder die Zellreferenz in das Einschränkungstextfeld.

To continue adding constraints for other cells used by the Solver, click the Add button to add the constraint and clear the text boxes in the Add Constraint dialog box. Then, repeat Step 5 to add a new constraint. After you finish defining constraints for the target cell and changing values in the model, click OK to close the Add Constraint dialog box and return to the Solver Parameters dialog box (which now lists your constraints in the Subject to the Constraints list box).

(Optional) Deselect the Make Unconstrained Variables Non-Negative check box if you want to allow negative values when the variable cells are not subject to constraints.
By default, the Solver Add-in employs the GRG (Generalized Reduced Gradient) Nonlinear method in solving the model whose parameters you’re setting known as a very efficient way to solve smooth nonlinear problems. To use the LP Simplex method (for Linear Programming following the Simplex algorithm) or Evolutionary engine for solving non-smooth problems, you need to follow Step 7.

(Optional) Select LP Simplex or Evolutionary from the Select a Solving Method drop-down list to use either one of these methods solving nonsmooth problems.

Click the Solve button to have the Solver solve the problem as you’ve defined it in the Solver Parameters dialog box.

Solving the problem with Excel’s Solver

When you click the Solve button, the Solver Parameters dialog box disappears, and the status bar indicates that the Solver is setting up the problem and then keeps you informed of the progress in solving the problem by showing the number of the intermediate (or trial) solutions as they are tried. To interrupt the solution process at any time before Excel calculates the last iteration, press the Esc key. Excel then displays the Show Trial Solution dialog box, informing you that the solution process has been paused. To continue the solution process, click the Continue button. To abort the solution process, click the Stop button.

When Excel finishes the solution process, the Solver Results dialog box appears. This dialog box informs you whether the Solver was able to find a solution, given the target cell, changing cells, and constraints defined for the problem. To retain the changes that the Solver makes in your worksheet model, leave the Keep Solver Solution option button selected and click OK to close the Solver Results dialog box. To return the original values to the worksheet, click the Restore Original Values option button instead. To save the changes as a scenario before you restore the original values, click the Save Scenario button and assign a name to the current scenario before you click the Restore Original Values option and OK button.

The Solver Results dialog box showing that Solver found a solution to the problem.

Unlike when using the Goal Seek command, after clicking the Keep Solver Solution option button in the Solver Results dialog box, you can’t use the Undo command button on the Quick Access toolbar to restore the original values to your worksheet. If you want to be able to switch between the “before” and “after” views of your worksheet, you must save the changes with the Save Scenario button and then select the Restore Original Values option button. That way, you can retain the “before” view in the original worksheet and use the Scenario Manager to display the “after” view created by the Solver.

Changing Excel’s Solver options

For most of the problems, the default options used by the Solver are adequate. In some situations, however, you may want to change some of the Solver options before you begin the solution process. To change the solution options, click the Options button in the Solver Parameters dialog box. Excel then opens the Options dialog box with the All Methods tab selected where you can make all necessary changes.

Modifying the solution options in the Options dialog box.

Excel 2019 Solver Option Settings

After changing the options, click OK to return to the Solver Parameters dialog box; from here, you can then click the Solve button to begin the solution process with the new solution settings that you just changed.

When you use the default GRG (Generalized Reduced Gradient) Nonlinear or Evolutionary method, you can set additional Solver settings using the options on the GRG Nonlinear and Evolutionary tabs of the Options dialog box. These options include changing the Converge, Population Size, and Random Seed settings for either of these particular methods.

Saving and loading a model problem in Excel 2019

The objective cell, variable cells, constraint cells, and Solver options that you most recently used are saved as part of the Excel worksheet when you click the Save button on the Quick Access toolbar (Ctrl+S). When you define other problems for the same worksheet that you want to save, you must click the Save Model button in the Solver Options dialog box and indicate the cell reference or name of the range in the active worksheet where you want the problem’s parameters to be inserted.

Wenn Sie auf die Schaltfläche Laden/Speichern klicken, öffnet Excel das Dialogfeld Modell laden/speichern, das ein Textfeld Modellbereich auswählen enthält. Dieses Textfeld enthält die Zellbezüge für einen Bereich, der groß genug ist, um alle Parameter des Problems aufzunehmen, beginnend mit der aktiven Zelle. Um die Parameter des Problems in diesem Bereich zu speichern, klicken Sie auf OK. Wenn dieser Bereich Zellen mit vorhandenen Daten enthält, müssen Sie den Zellbezug in diesem Textfeld ändern, bevor Sie auf OK klicken, um zu verhindern, dass Excel die vorhandenen Daten ersetzt.

Nachdem Sie auf OK geklickt haben, kopiert Excel die Parameter des Problems in den angegebenen Bereich. Diese Werte werden dann beim nächsten Speichern der Arbeitsmappe als Teil des Arbeitsblatts gespeichert. Um diese Problemparameter beim Lösen eines Problems wiederzuverwenden, müssen Sie lediglich das Dialogfeld Solver-Optionen öffnen, auf die Schaltfläche Laden/Speichern klicken, um das Dialogfeld Modell laden/speichern zu öffnen, auf die Schaltfläche Laden klicken und dann den Bereich auswählen, der das gespeicherte . enthält Problemparameter bzw. Wenn Sie im Dialogfeld Modell laden auf OK klicken, lädt Excel die Parameter aus diesem Zellbereich in die entsprechenden Textfelder im Dialogfeld Solver-Parameter. Sie können dann das Dialogfeld Solver Options schließen, indem Sie auf OK klicken, und Sie können das Problem lösen, indem Sie diese Parameter verwenden, indem Sie auf die Befehlsschaltfläche Lösen klicken.

Denken Sie daran, dass Sie die Schaltfläche Alle zurücksetzen verwenden können, wenn Sie alle für das vorherige Problem definierten Parameter löschen und die Solver-Optionen auf ihre Standardwerte zurücksetzen möchten.

Erstellen von Solver-Berichten in Excel 2019

Sie können mit dem Solver drei verschiedene Arten von Berichten erstellen:

• Antwortbericht: Listet die Zielzelle und die sich ändernden Zellen mit ihren ursprünglichen und endgültigen Werten zusammen mit den Einschränkungen auf, die bei der Lösung des Problems verwendet wurden.
• Empfindlichkeitsbericht: Gibt an, wie empfindlich eine optimale Lösung auf Änderungen in den Formeln reagiert, die die Zielzelle und Einschränkungen berechnen. Der Bericht zeigt die sich ändernden Zellen mit ihren Endwerten und dem reduzierten Gradienten für jede Zelle. (Der reduzierte Gradient misst das Ziel pro Einheitserhöhung in der sich ändernden Zelle.) Wenn Sie Einschränkungen definiert haben, werden diese im Sensitivitätsbericht mit ihren Endwerten und dem Lagrange-Multiplikator für jede Einschränkung aufgeführt. (Der Lagrange-Multiplikator misst das Ziel pro Einheitserhöhung, das auf der rechten Seite der Einschränkungsgleichung erscheint.)
• Grenzwertbericht: Zeigt die Zielzelle und die sich ändernden Zellen mit ihren Werten, Unter- und Obergrenzen und Zielergebnissen an. Die untere Grenze stellt den niedrigsten Wert dar, den eine sich ändernde Zelle haben kann, während die Werte aller anderen Zellen fixiert werden und dennoch die Einschränkungen erfüllt werden. Die Obergrenze stellt den höchsten Wert dar, der dies tut.

Excel platziert jeden Bericht, den Sie für ein Solver-Problem generieren, in einem separaten Arbeitsblatt in der Arbeitsmappe. Um einen (oder alle) dieser Berichte zu erstellen, wählen Sie den Berichtstyp (Antwort, Sensitivität oder Grenzen) aus dem Listenfeld Berichte des Dialogfelds Solver-Ergebnisse aus. Um mehr als einen Bericht auszuwählen, klicken Sie einfach auf den Namen des Berichts.

Wenn Sie auf OK klicken, um das Dialogfeld Solver-Ergebnisse zu schließen (nachdem Sie zwischen den Optionen Solver-Lösung beibehalten und Originalwerte wiederherstellen gewählt haben), generiert Excel den ausgewählten Bericht (oder die Berichte) in einem neuen Arbeitsblatt, das am Anfang der Arbeitsmappe hinzugefügt wird . (Die Registerkarten des Berichtsblatts sind nach Berichtstyp benannt, wie in Antwortbericht 1, Sensitivitätsbericht 1 und Grenzwertbericht 1. )