Definieren und lösen Sie ein Problem mit Solver
Solver ist ein Add-In-Programm für Microsoft Excel, das Sie für Was-wäre-wenn-Analysen verwenden können. Verwenden Sie Solver, um einen optimalen (maximalen oder minimalen) Wert für eine Formel in einer Zelle zu finden – die als Zielzelle bezeichnet wird – vorbehaltlich von Einschränkungen oder Grenzwerten für die Werte anderer Formelzellen auf einem Arbeitsblatt. Solver arbeitet mit einer Gruppe von Zellen, die als Entscheidungsvariablen oder einfach variable Zellen bezeichnet werden und zur Berechnung der Formeln in den Ziel- und Einschränkungszellen verwendet werden. Solver passt die Werte in den Entscheidungsvariablenzellen an, um die Grenzwerte für Einschränkungszellen zu erfüllen und das gewünschte Ergebnis für die Zielzelle zu erzeugen.
Einfach gesagt, können Sie Solver verwenden, um den maximalen oder minimalen Wert einer Zelle zu bestimmen, indem Sie andere Zellen ändern. Sie können beispielsweise die Höhe Ihres prognostizierten Werbebudgets ändern und die Auswirkung auf Ihre prognostizierte Gewinnhöhe sehen.
Hinweis: Solver-Versionen vor Excel 2007 bezeichneten die Zielzelle als „Zielzelle" und die Entscheidungsvariablenzellen als „veränderliche Zellen" oder „anpassbare Zellen". Am Solver-Add-In für Excel 2010 wurden viele Verbesserungen vorgenommen. Wenn Sie also Excel 2007 verwenden, wird Ihre Erfahrung etwas anders sein.
Im folgenden Beispiel wirkt sich die Werbeintensität in jedem Quartal auf die Anzahl der verkauften Einheiten aus und bestimmt indirekt die Höhe der Verkaufserlöse, der damit verbundenen Kosten und des Gewinns. Solver kann die vierteljährlichen Werbebudgets (Entscheidungsvariablenzellen B5:C5) bis zu einer Gesamtbudgetbeschränkung von 20.000 $ (Zelle F5) ändern, bis der Gesamtgewinn (Zielzelle F7) den maximal möglichen Betrag erreicht. Die Werte in den variablen Zellen werden verwendet, um den Gewinn für jedes Quartal zu berechnen, also beziehen sie sich auf die Formelzielzelle F7, =SUM (Q1 Profit:Q2 Profit).
1. Variable Zellen
2. Eingeschränkte Zelle
3. Zielzelle
Nachdem Solver ausgeführt wurde, lauten die neuen Werte wie folgt.
Klicken Sie auf der Registerkarte Daten in der Gruppe Analyse auf Solver .
Hinweis: Wenn der Befehl Solver oder die Gruppe Analyse nicht verfügbar sind, müssen Sie das Add-In Solver aktivieren. Siehe: So aktivieren Sie das Solver-Add-In.
Geben Sie im Feld Ziel festlegen einen Zellbezug oder Namen für die Zielzelle ein. Die Zielzelle muss eine Formel enthalten.
Führen Sie einen der folgenden Schritte aus:
Wenn der Wert der Zielzelle so groß wie möglich sein soll, klicken Sie auf Max .
Wenn der Wert der Zielzelle so klein wie möglich sein soll, klicken Sie auf Min .
Wenn die Zielzelle einen bestimmten Wert haben soll, klicken Sie auf Wert von und geben Sie dann den Wert in das Feld ein.
Geben Sie im Feld Durch Ändern von Variablenzellen einen Namen oder eine Referenz für jeden Zellbereich der Entscheidungsvariablen ein. Trennen Sie die nicht benachbarten Referenzen durch Kommas. Die variablen Zellen müssen direkt oder indirekt mit der Zielzelle in Beziehung stehen. Sie können bis zu 200 variable Zellen angeben.
Geben Sie im Feld Den Einschränkungen unterliegen alle Einschränkungen ein, die Sie anwenden möchten, indem Sie wie folgt vorgehen:
Klicken Sie im Dialogfeld Solver-Parameter auf Hinzufügen .
Geben Sie im Feld Zellbezug den Zellbezug oder Namen des Zellbereichs ein, für den Sie den Wert einschränken möchten.
Klicken Sie auf die gewünschte Beziehung ( <= , = , >= , int , bin oder dif ) zwischen der referenzierten Zelle und der Einschränkung. Wenn Sie auf int klicken, wird Integer im Feld Einschränkung angezeigt. Wenn Sie auf bin klicken, wird Binary im Feld Constraint angezeigt. Wenn Sie auf dif klicken, wird alldifferent im Feld Constraint angezeigt.
Wenn Sie <=, = oder >= für die Beziehung im Feld Einschränkung auswählen, geben Sie eine Zahl, einen Zellbezug oder Namen oder eine Formel ein.
Führen Sie einen der folgenden Schritte aus:
Um die Einschränkung zu akzeptieren und eine weitere hinzuzufügen, klicken Sie auf Hinzufügen .
Um die Einschränkung zu akzeptieren und zum Dialogfeld Solver-Parameter zurückzukehren, klicken Sie auf OK .
Hinweis Sie können die Beziehungen int , bin und dif nur in Einschränkungen auf Entscheidungsvariablenzellen anwenden.Sie können eine vorhandene Einschränkung ändern oder löschen, indem Sie wie folgt vorgehen:
Klicken Sie im Dialogfeld Solver-Parameter auf die Einschränkung, die Sie ändern oder löschen möchten.
Klicken Sie auf Ändern und nehmen Sie dann Ihre Änderungen vor oder klicken Sie auf Löschen .
Klicken Sie auf „ Lösen" und führen Sie einen der folgenden Schritte aus:
Um die Lösungswerte auf dem Arbeitsblatt beizubehalten, klicken Sie im Dialogfeld Solver-Ergebnisse auf Solver- Lösung beibehalten .
Um die ursprünglichen Werte wiederherzustellen, bevor Sie auf Lösen geklickt haben, klicken Sie auf Ursprüngliche Werte wiederherstellen.
Sie können den Lösungsvorgang unterbrechen, indem Sie Esc drücken. Excel berechnet das Arbeitsblatt mit den letzten Werten neu, die für die Zellen der Entscheidungsvariablen gefunden wurden.
Um einen Bericht zu erstellen, der auf Ihrer Lösung basiert, nachdem Solver eine Lösung gefunden hat, können Sie im Feld Berichte auf einen Berichtstyp klicken und dann auf OK klicken. Der Bericht wird auf einem neuen Arbeitsblatt in Ihrer Arbeitsmappe erstellt. Wenn Solver keine Lösung findet, sind nur bestimmte Berichte oder keine Berichte verfügbar.
Um Ihre Zellenwerte der Entscheidungsvariablen als Szenario zu speichern, das Sie später anzeigen können, klicken Sie im Dialogfeld Solver-Ergebnisse auf Szenario speichern und geben Sie dann einen Namen für das Szenario in das Feld Szenarioname ein.
Nachdem Sie ein Problem definiert haben, klicken Sie im Dialogfeld Solver-Parameter auf Optionen .
Aktivieren Sie im Dialogfeld Optionen das Kontrollkästchen Iterationsergebnisse anzeigen , um die Werte jeder Testlösung anzuzeigen, und klicken Sie dann auf OK .
Klicken Sie im Dialogfeld Solver-Parameter auf Lösen .
Führen Sie im Dialogfeld „ Testlösung anzeigen" einen der folgenden Schritte aus:
Um den Lösungsprozess zu stoppen und das Dialogfeld Solver-Ergebnisse anzuzeigen, klicken Sie auf Stopp .
Um den Lösungsprozess fortzusetzen und die nächste Testlösung anzuzeigen, klicken Sie auf Weiter .
Klicken Sie im Dialogfeld Solver-Parameter auf Optionen .
Wählen Sie Werte für beliebige Optionen auf den Registerkarten Alle Methoden , GRG Nichtlinear und Evolutionär im Dialogfeld aus oder geben Sie sie ein.
Klicken Sie im Dialogfeld Solver-Parameter auf Laden/Speichern .
Geben Sie einen Zellbereich für den Modellbereich ein und klicken Sie entweder auf Speichern oder auf Laden .
Geben Sie beim Speichern eines Modells die Referenz für die erste Zelle eines vertikalen Bereichs leerer Zellen ein, in der Sie das Problemmodell platzieren möchten. Geben Sie beim Laden eines Modells die Referenz für den gesamten Zellbereich ein, der das Problemmodell enthält.
Tipp: Sie können die letzte Auswahl im Dialogfeld Solver-Parameter mit einem Arbeitsblatt speichern, indem Sie die Arbeitsmappe speichern. Jedes Arbeitsblatt in einer Arbeitsmappe kann seine eigenen Solver-Auswahlen haben, und alle werden gespeichert. Sie können auch mehr als ein Problem für ein Arbeitsblatt definieren, indem Sie auf Laden/Speichern klicken, um Probleme einzeln zu speichern.
Sie können einen der folgenden drei Algorithmen oder Lösungsmethoden im Dialogfeld Solver-Parameter auswählen:
Verallgemeinerter reduzierter Gradient (GRG) Nichtlinear Wird für Probleme verwendet, die glatt nichtlinear sind.
LP Simplex Wird für lineare Probleme verwendet.
Evolutionary Use für Probleme, die nicht glatt sind.
Wichtig: Sie sollten zuerst das Solver-Add-In aktivieren. Weitere Informationen finden Sie unter Laden des Solver-Add-Ins .
Im folgenden Beispiel wirkt sich die Werbeintensität in jedem Quartal auf die Anzahl der verkauften Einheiten aus und bestimmt indirekt die Höhe der Verkaufserlöse, der damit verbundenen Kosten und des Gewinns. Solver kann die vierteljährlichen Werbebudgets (Entscheidungsvariablenzellen B5:C5) bis zu einer Gesamtbudgetbeschränkung von 20.000 $ (Zelle D5) ändern, bis der Gesamtgewinn (Zielzelle D7) den maximal möglichen Betrag erreicht. Die Werte in den variablen Zellen werden verwendet, um den Gewinn für jedes Quartal zu berechnen, also beziehen sie sich auf die Formelzielzelle D7, =SUM(Q1 Profit:Q2 Profit).
Variable Zellen
Eingeschränkte Zelle
Objektive Zelle
Nachdem Solver ausgeführt wurde, lauten die neuen Werte wie folgt.
In Excel 2016 für Mac: Klicken Sie auf Daten > Solver .
In Excel für Mac 2011: Klicken Sie auf die Registerkarte Daten und dann unter Analyse auf Solver .
Geben Sie unter Ziel festlegen einen Zellbezug oder Namen für die Zielzelle ein.
Hinweis: Die Zielzelle muss eine Formel enthalten.
Führen Sie einen der folgenden Schritte aus:
Zu
Mach das
Machen Sie den Wert der Zielzelle so groß wie möglich
Klicken Sie auf Max .
Machen Sie den Wert der Zielzelle so klein wie möglich
Klicken Sie auf Min .
Setzen Sie die Zielzelle auf einen bestimmten Wert
Klicken Sie auf Wert von und geben Sie dann den Wert in das Feld ein.
Geben Sie im Feld Durch Ändern von Variablenzellen einen Namen oder eine Referenz für jeden Zellbereich der Entscheidungsvariablen ein. Trennen Sie die nicht benachbarten Referenzen durch Kommas.
Die variablen Zellen müssen direkt oder indirekt mit der Zielzelle in Beziehung stehen. Sie können bis zu 200 variable Zellen angeben.
Fügen Sie im Feld Den Einschränkungen unterliegen alle Einschränkungen hinzu, die Sie anwenden möchten.
Gehen Sie folgendermaßen vor, um eine Einschränkung hinzuzufügen:
Klicken Sie im Dialogfeld Solver-Parameter auf Hinzufügen .
Geben Sie im Feld Zellbezug den Zellbezug oder Namen des Zellbereichs ein, für den Sie den Wert einschränken möchten.
Wählen Sie im Einblendmenü < = -Beziehung die gewünschte Beziehung zwischen der referenzierten Zelle und der Einschränkung aus. Wenn Sie <= , = oder >= auswählen, geben Sie im Feld Einschränkung eine Zahl, eine Zellreferenz oder einen Namen ein , oder eine Formel.
Hinweis: Sie können die int-, bin- und dif-Beziehungen nur in Einschränkungen auf Entscheidungsvariablenzellen anwenden.
Führen Sie einen der folgenden Schritte aus:
Zu
Mach das
Akzeptieren Sie die Einschränkung und fügen Sie eine weitere hinzu
Klicken Sie auf Hinzufügen .
Akzeptieren Sie die Einschränkung und kehren Sie zum Dialogfeld Solver-Parameter zurück
Klicken Sie auf OK .
Klicken Sie auf Lösen und führen Sie dann einen der folgenden Schritte aus:
Zu
Mach das
Halten Sie die Lösungswerte auf dem Blatt fest
Klicken Sie im Dialogfeld Solver-Ergebnisse auf Solver- Lösung beibehalten.
Stellen Sie die ursprünglichen Daten wieder her
Klicken Sie auf Ursprüngliche Werte wiederherstellen .
Anmerkungen:
Um den Lösungsvorgang zu unterbrechen, drücken Sie ESC . Excel berechnet das Blatt mit den letzten gefundenen Werten für die anpassbaren Zellen neu.
Um einen Bericht zu erstellen, der auf Ihrer Lösung basiert, nachdem Solver eine Lösung gefunden hat, können Sie im Feld Berichte auf einen Berichtstyp klicken und dann auf OK klicken. Der Bericht wird auf einem neuen Blatt in Ihrer Arbeitsmappe erstellt. Wenn Solver keine Lösung findet, ist die Option zum Erstellen eines Berichts nicht verfügbar.
Um Ihre angepassten Zellenwerte als Szenario zu speichern, das Sie später anzeigen können, klicken Sie im Dialogfeld Solver-Ergebnisse auf Szenario speichern , und geben Sie dann einen Namen für das Szenario in das Feld Szenarioname ein.
In Excel 2016 für Mac: Klicken Sie auf Daten > Solver .
In Excel für Mac 2011: Klicken Sie auf die Registerkarte Daten und dann unter Analyse auf Solver .
Nachdem Sie ein Problem definiert haben, klicken Sie im Dialogfeld Solver-Parameter auf Optionen .
Aktivieren Sie das Kontrollkästchen Iterationsergebnisse anzeigen , um die Werte jeder Testlösung anzuzeigen, und klicken Sie dann auf OK .
Klicken Sie im Dialogfeld Solver-Parameter auf Lösen .
Führen Sie im Dialogfeld „ Testlösung anzeigen" einen der folgenden Schritte aus:
Zu
Mach das
Stoppen Sie den Lösungsprozess und zeigen Sie das Dialogfeld Solver-Ergebnisse an
Klicken Sie auf Stopp .
Setzen Sie den Lösungsprozess fort und zeigen Sie die nächste Testlösung an
Klicken Sie auf Weiter .
In Excel 2016 für Mac: Klicken Sie auf Daten > Solver .
In Excel für Mac 2011: Klicken Sie auf die Registerkarte Daten und dann unter Analyse auf Solver .
Klicken Sie auf Optionen und wählen Sie dann im Dialogfeld Optionen oder Solver-Optionen eine oder mehrere der folgenden Optionen aus:
Zu
Mach das
Legen Sie Lösungszeit und Iterationen fest
Geben Sie auf der Registerkarte Alle Methoden unter Solving Limits im Feld Max Time (Seconds) die Anzahl der Sekunden ein, die Sie für die Lösungszeit zulassen möchten. Geben Sie dann im Feld Iterationen die maximale Anzahl an Iterationen ein, die Sie zulassen möchten.
Hinweis: Wenn der Lösungsprozess die maximale Zeit oder Anzahl der Iterationen erreicht, bevor Solver eine Lösung findet, zeigt Solver das Dialogfeld Testlösung anzeigen an.
Legen Sie den Genauigkeitsgrad fest
Geben Sie auf der Registerkarte Alle Methoden im Feld Einschränkungsgenauigkeit den gewünschten Genauigkeitsgrad ein. Je kleiner die Zahl, desto höher die Genauigkeit.
Stellen Sie den Grad der Konvergenz ein
Geben Sie auf der Registerkarte GRG Nichtlinear oder Evolutionär im Feld Konvergenz den Betrag der relativen Änderung ein, den Sie in den letzten fünf Iterationen zulassen möchten, bevor Solver mit einer Lösung stoppt. Je kleiner die Zahl, desto weniger relative Änderung ist zulässig.
Klicken Sie auf OK .
Klicken Sie im Dialogfeld Solver-Parameter auf Lösen oder Schließen .
In Excel 2016 für Mac: Klicken Sie auf Daten > Solver .
In Excel für Mac 2011: Klicken Sie auf die Registerkarte Daten und dann unter Analyse auf Solver .
Klicken Sie auf Laden/Speichern , geben Sie einen Zellbereich für den Modellbereich ein und klicken Sie dann entweder auf Speichern oder Laden .
Geben Sie beim Speichern eines Modells die Referenz für die erste Zelle eines vertikalen Bereichs leerer Zellen ein, in der Sie das Problemmodell platzieren möchten. Geben Sie beim Laden eines Modells die Referenz für den gesamten Zellbereich ein, der das Problemmodell enthält.
Tipp: Sie können die letzte Auswahl im Dialogfeld Solver-Parameter mit einem Arbeitsblatt speichern, indem Sie die Arbeitsmappe speichern. Jedes Blatt in einer Arbeitsmappe kann seine eigenen Solver-Auswahlen haben, und alle werden gespeichert. Sie können auch mehr als ein Problem für ein Blatt definieren, indem Sie auf Laden/Speichern klicken, um Probleme einzeln zu speichern.
In Excel 2016 für Mac: Klicken Sie auf Daten > Solver .
In Excel für Mac 2011: Klicken Sie auf die Registerkarte Daten und dann unter Analyse auf Solver .
Wählen Sie im Popup-Menü „Lösungsmethode auswählen" eine der folgenden Optionen:
Lösungsmethode | Beschreibung |
|---|---|
GRG (Generalized Reduced Gradient) Nichtlinear | Die Standardauswahl für Modelle, die die meisten anderen Excel-Funktionen als IF, CHOOSE, LOOKUP und andere „Schritt"-Funktionen verwenden. |
Simplex-LP | Verwenden Sie diese Methode für Probleme der linearen Programmierung. Ihr Modell sollte SUM, SUMPRODUCT, + - und * in Formeln verwenden, die von den variablen Zellen abhängen. |
Evolutionär | Diese auf genetischen Algorithmen basierende Methode eignet sich am besten, wenn Ihr Modell IF, CHOOSE oder LOOKUP mit Argumenten verwendet, die von den variablen Zellen abhängen. |
Hinweis: Teile des Solver-Programmcodes unterliegen dem Copyright 1990–2010 von Frontline Systems, Inc. Teile unterliegen dem Copyright 1989 von Optimal Methods, Inc.
Da Add-In-Programme in Excel für das Web nicht unterstützt werden, können Sie das Solver-Add-In nicht verwenden, um Was-wäre-wenn-Analysen für Ihre Daten auszuführen, damit Sie optimale Lösungen finden können.
Wenn Sie über die Excel-Desktopanwendung verfügen, können Sie die Schaltfläche In Excel öffnen verwenden, um Ihre Arbeitsmappe zu öffnen und das Solver-Add-In zu verwenden .
Weitere Hilfe zur Verwendung von Solver
Für detailliertere Hilfe zu Solver wenden Sie sich bitte an:
Frontline Systems, Inc.
Postfach 4288
Incline Village, NV 89450-4288
(775) 831-0300
Website: http://www.solver.com
E-Mail: info@solver.com
Solver-Hilfe unter www.solver.com .
Teile des Solver-Programmcodes unterliegen dem Copyright 1990-2009 von Frontline Systems, Inc. Teile unterliegen dem Copyright 1989 von Optimal Methods, Inc.
Benötigen Sie weitere Hilfe?
Sie können jederzeit einen Experten in der Excel Tech Community fragen oder Unterstützung in der Answers-Community erhalten.
Siehe auch
Verwenden von Solver für die Kapitalbudgetierung
Mit Solver den optimalen Produktmix ermitteln
Einführung in die Was-wäre-wenn-Analyse
Übersicht über Formeln in Excel
So vermeiden Sie fehlerhafte Formeln
No comments:
Post a Comment