Erkennen und Lösen eines Problems mithilfe von Solver

Beispiel einer Ermittlung von Werten mithilfe von Solver

Im folgenden Beispiel wird gezeigt, wie der Umfang der Werbemaßnahmen pro Quartal die Anzahl der verkauften Einheiten beeinflusst und damit mittelbar die Höhe des Verkaufsumsatzes, die anfallenden Kosten und den Gewinn bestimmt. In Solver werden die Quartalsetats für Werbemaßnahmen (Variablenzellen B5:C5) bis zu einem Gesamtetat von 20.000 € (Zelle F5) solange geändert, bis der Gesamtgewinn (Zielzelle F7) den höchstmöglichen Wert erreicht hat. Die Werte in den Variablenzellen werden zur Berechnung des Gewinns für jedes Quartal verwendet und beziehen sich daher auf die Formelzielzelle F7, =SUMME(Q1 Gewinn:Q2 Gewinn).

1. Urheberrecht Variablenzellen

2. Urheberrecht Nebenbedingung

3. Urheberrecht Zielzelle

Nachdem von Solver eine Lösung gefunden wurde, ergeben sich die folgenden neuen Werte:

Definieren und Lösen eines Problems

1. Wählen Sie auf der Registerkarte Daten in der Gruppe Analyse die Option Löser aus.
   

   Hinweis

   Wenn der Befehl "Solver " oder die Gruppe "Analysieren " nicht verfügbar ist, müssen Sie das Solver-Add-In aktivieren. Weitere Informationen finden Sie unter So aktivieren Sie das Solver-Add-In.

   

2. Geben Sie im Feld Ziel festlegen einen Zellbezug oder Namen für die Zielzelle ein. Die Zielzelle muss eine Formel enthalten.

3. Führen Sie einen der folgenden Schritte aus.

   • Wenn der Wert der Zielzelle so groß wie möglich sein soll, wählen Sie Max.
   • Wenn der Wert der Zielzelle so klein wie möglich sein soll, wählen Sie Min aus.
   • Wenn die Zielzelle ein bestimmter Wert sein soll, wählen Sie Wert von aus, und geben Sie dann den Wert in das Feld ein.
   • Geben Sie im Feld Durch Ändern von Variablenzellen einen Namen oder einen Bezug für jeden Variablenzellbereich ein. Trennen Sie nicht-angrenzende Bezüge durch Semikolons. Die Variablenzellen müssen einen direkten oder indirekten Bezug zu den Zielzellen haben. Sie können bis zu 200 Variablenzellen angeben.

4. Geben Sie im Feld Vorbehaltlich der Einschränkungen alle Einschränkungen ein, die Sie anwenden möchten, indem Sie die folgenden Schritte ausführen.

   1. Wählen Sie im Dialogfeld Solver-Parameterdie Option Hinzufügen aus.

   2. Geben Sie im Feld Zellbezug den Zellbezug oder Namen des Zellbereichs ein, dessen Wert durch Nebenbedingungen eingegrenzt werden soll.

   3. Wählen Sie die gewünschte Beziehung ( <=,=, >=, int, bin oder dif ) aus, die zwischen der Zelle, auf die verwiesen wird, und der Einschränkung stehen soll. Wenn Sie "int" auswählen, wird im Feld "Einschränkung" eine ganze Zahl angezeigt. Wenn Sie "bin" auswählen, wird "binär" im Feld "Einschränkung " angezeigt. Wenn Sie DIF auswählen, wird alldifferent im Feld Einschränkung angezeigt.

   4. Wenn Sie für die Beziehung im Feld Einschränkung den Eintrag "<=", "=" oder >"=" auswählen, geben Sie eine Zahl, einen Zellbezug oder einen Zellnamen oder eine Formel ein.

   5. Führen Sie einen der folgenden Schritte aus.

      • Um die Einschränkung zu akzeptieren und eine weitere hinzuzufügen, wählen Sie Hinzufügen aus.

      • Um die Einschränkung zu akzeptieren und zum Dialogfeld Solver-Parameter zurückzukehren, wählen Sie OK aus.

        Hinweis

        Sie können die Beziehungen "int", "bin" und "dif " nur in Einschränkungen auf Entscheidungsvariablenzellen anwenden.

   6. Sie können eine vorhandene Einschränkung ändern oder löschen, indem Sie die folgenden Aktionen ausführen.

      • Wählen Sie im Dialogfeld Solver-Parameter die Einschränkung aus, die Sie ändern oder löschen möchten.
      • Wählen Sie "Ändern " aus, und nehmen Sie dann Ihre Änderungen vor, oder wählen Sie "Löschen" aus.

5. Wählen Sie "Lösen" aus, und führen Sie eine der folgenden Aktionen aus.

   • Um die Lösungswerte auf dem Arbeitsblatt zu behalten, wählen Sie im Dialogfeld Solver-Ergebnisse die Option Solver-Lösung beibehalten.
   • Um die ursprünglichen Werte wiederherzustellen, bevor Sie "Lösen" ausgewählt haben, wählen Sie "Ursprüngliche Werte wiederherstellen" aus.
   • Sie können den Lösungsprozess unterbrechen, indem Sie die ESC-TASTE drücken. Excel berechnet das Arbeitsblatt mit den letzten gefundenen Werten für die Zellen mit den Entscheidungsvariablen neu.
   • Um einen Bericht zu erstellen, der auf Ihrer Lösung basiert, nachdem der Solver eine Lösung gefunden hat, wählen Sie einen Berichtstyp im Feld Berichte und dann OK aus. Der Bericht wird auf einem neuen Arbeitsblatt in Ihrer Arbeitsmappe erstellt. Wenn keine Lösung gefunden wurde, sind keine oder nur bestimmte Berichte verfügbar.
   • Um Ihre Zellwerte der Entscheidungsvariablen als ein Szenario zu speichern, das Sie später anzeigen können, wählen Sie im Dialogfeld Solver-Ergebnisse die Option Szenario speichern aus, und geben Sie dann einen Namen für das Szenario in das Feld Szenarioname ein.

Schrittweises Anzeigen der von Solver gelieferten Zwischenergebnisse

1. Nachdem Sie ein Problem definiert haben, wählen Sie Optionen im Dialogfeld Solver-Parameter aus.

2. Aktivieren Sie im Dialogfeld Optionen das Kontrollkästchen Iterationsergebnisse anzeigen , um die Werte der einzelnen Testlösungen anzuzeigen, und wählen Sie dann OK aus.

3. Wählen Sie im Dialogfeld Solver-Parameter die Option Lösen aus.

4. Führen Sie im Dialogfeld "Testlösung anzeigen " eine der folgenden Aktionen aus.

   • Um den Lösungsprozess anzuhalten und das Dialogfeld Solver-Ergebnisse anzuzeigen, wählen Sie Beenden aus.
   • Um den Lösungsprozess fortzusetzen und die nächste Testlösung anzuzeigen, wählen Sie Weiter aus.

Ändern der Lösungsfindung für Solver

1. Wählen Sie im Dialogfeld Solver-Parameterdie Option Optionen aus.
2. Wählen Sie für alle Optionen auf den Registerkarten Alle Methoden, GRG-Nichtlinear und EA (Evolutionärer Algorithmus) im Dialogfeld Werte aus, oder geben Sie Werte ein.

Speichern oder Laden eines Problemmodells

1. Wählen Sie im Dialogfeld Solver-Parameter die Option Laden/Speichern aus.

2. Geben Sie einen Zellbereich für den Modellbereich ein und wählen Sie entweder Speichern oder Laden aus.
   Geben Sie beim Speichern eines Modells den Bezug für die erste Zelle eines vertikalen Bereichs leerer Zellen ein, in der Sie das Problemmodell platzieren möchten. Wenn Sie ein Modell laden, geben Sie den Bezug für den gesamten Zellbereich ein, der das Problemmodell enthält.

   Tipp

   Sie können die zuletzt im Dialogfeld Solver-Parameter ausgewählten Optionen mit einem Arbeitsblatt speichern, indem Sie die Arbeitsmappe speichern. Jedes Arbeitsblatt in einer Arbeitsmappe kann über eine eigene Solver-Auswahl verfügen, und alle werden gespeichert. Sie können auch mehr als eine Aufgabe für ein Arbeitsblatt definieren, indem Sie Laden/Speichern auswählen, um die Probleme einzeln zu speichern.

Von Solver verwendete Lösungsmethoden

Sie können einen der folgenden drei Algorithmen oder Lösungsmethoden im Dialogfeld Solver-Parameter auswählen.

• Verallgemeinerter reduzierter Gradient (GRG) Nichtlinear: Verwenden Sie diese Option für Probleme, die glatt oder nichtlinear sind.
• LP Simplex: Verwenden bei linearen Problemen.
• Evolutionär: Bei Problemen verwenden, die nicht glatt sind.

Beispiel einer Ermittlung von Werten mithilfe von Solver

Im folgenden Beispiel wird gezeigt, wie der Umfang der Werbemaßnahmen pro Quartal die Anzahl der verkauften Einheiten beeinflusst und damit indirekt die Höhe des Verkaufsumsatzes, die anfallenden Kosten und den Gewinn bestimmt. In Solver werden die Quartalsetats für Werbemaßnahmen (Variablenzellen B5:C5) bis zu einem Gesamtetat von 20.000 € (Zelle D5) solange geändert, bis der Gesamtgewinn (Zielzelle D7) den höchstmöglichen Wert erreicht hat. Die Werte in den Variablenzellen werden zur Berechnung des Gewinns für jedes Quartal verwendet und beziehen sich daher auf die Formel Zielzelle D7, =SUMME(Q1 Gewinn:Q2 Gewinn).

Variablenzellen

Eingeschränkte Zelle

Zielzelle

Nachdem von Solver eine Lösung gefunden wurde, ergeben sich die folgenden neuen Werte:

Definieren und Lösen eines Problems

1. Wählen SieDatenlöser> aus.

   

2. Geben Sie unter Ziel festlegen einen Zellbezug oder Namen für die Zielzelle ein.

   Hinweis

   Die Zielzelle muss eine Formel enthalten.

3. Führen Sie einen der folgenden Schritte aus.

   Gewünschte Aktion	Geste
   Definieren des höchstmöglichen Werts für die Zielzelle	Wählen Sie "Max" aus.
   Definieren des kleinstmöglichen Werts für die Zielzelle	Wählen Sie "Min" aus.
   Festlegen der Zielzelle auf einen bestimmten Wert	Wählen Sie Wert von aus, und geben Sie dann den Wert in das Feld ein.

4. Geben Sie im Feld Durch Ändern von Variablenzellen einen Namen oder einen Bezug für jeden Variablenzellbereich ein. Trennen Sie nicht-angrenzende Bezüge durch Semikolons.
   Die Variablenzellen müssen einen direkten oder indirekten Bezug zu den Zielzellen haben. Sie können bis zu 200 Variablenzellen angeben.

5. Fügen Sie im Feld Unterliegt den Nebenbedingungen evtl. anzuwendende Nebenbedingungen hinzu.
   Gehen Sie folgendermaßen vor, um eine Einschränkung hinzuzufügen.

   1. Wählen Sie im Dialogfeld Solver-Parameterdie Option Hinzufügen aus.

   2. Geben Sie im Feld Zellbezug den Zellbezug oder Namen des Zellbereichs ein, dessen Wert durch Nebenbedingungen eingegrenzt werden soll.

   3. Klicken Sie im< Popupmenü "=-Beziehung" auf die gewünschte Beziehung zwischen der Zelle, auf die verwiesen wird, und der Nebenbedingung. Wenn Sie "=", "=" oder >"=" auswählen<, können Sie im Feld "Einschränkung" eine Zahl, einen Zellbezug oder einen Zellnamen oder eine Formel eingeben.

      Hinweis

      Sie können die int-, bin- und dif-Beziehungen nur in Einschränkungen auf Entscheidungsvariablenzellen anwenden.

   4. Führen Sie eine der folgenden Aktionen aus.

   Gewünschte Aktion	Geste
   Übernehmen der Nebenbedingung und Hinzufügen einer weiteren Nebenbedingung	Wählen Sie Hinzufügen aus.
   Übernehmen der Nebenbedingung und Zurückkehren zum Dialogfeld Solver-Parameter	Wählen Sie OK aus.

6. Wählen Sie "Lösen" aus, und führen Sie dann eine der folgenden Aktionen aus.

   Gewünschte Aktion	Geste
   Beibehalten der Lösungswerte auf dem Blatt	Wählen Sie im Dialogfeld Solver-Ergebnissedie Option Solver-Lösung beibehalten aus.
   Wiederherstellen der ursprünglichen Daten	Wählen Sie Ursprüngliche Werte wiederherstellen aus.

Schrittweises Anzeigen der von Solver gelieferten Zwischenergebnisse

1. Wählen SieDatenlöser> aus.

   

2. Nachdem Sie ein Problem definiert haben, wählen Sie im Dialogfeld Solver-Parameterdie Option Optionen aus.

3. Aktivieren Sie das Kontrollkästchen Iterationsergebnisse anzeigen , um die Werte der einzelnen Testlösungen anzuzeigen, und wählen Sie dann OK aus.

4. Wählen Sie im Dialogfeld Solver-Parameter die Option Lösen aus.

5. Führen Sie im Dialogfeld "Testlösung anzeigen " eine der folgenden Aktionen aus.

   Gewünschte Aktion	Geste
   Beenden des Lösungsprozesses und Anzeigen des Dialogfelds Solver-Ergebnisse	Wählen Sie Stopp aus.
   Fortsetzen des Lösungsprozesses und Anzeigen des nächsten Zwischenergebnisses	Wählen Sie Weiter aus.

Ändern der Lösungsfindung für Solver

1. Wählen SieDatenlöser> aus.

   

2. Wählen Sie Optionen aus, und wählen Sie dann im Dialogfeld Optionen oder Solver-Optionen eine oder mehrere der folgenden Optionen aus:

   Gewünschte Aktion	Geste
   Festlegen von Lösungszeit und Iterationen	Geben Sie auf der Registerkarte Alle Methoden unter Lösungsgrenzwerte im Feld Höchstzeit (Sekunden) die Anzahl der Sekunden ein, die Sie als Lösungszeit zulassen möchten. Geben Sie dann im Feld Iterationen die maximale Anzahl der Iterationen ein, die Sie zulassen möchten. Hinweis: Wenn der Lösungsprozess die maximale Zeit oder Anzahl von Iterationen erreicht, bevor der Löser eine Lösung findet, zeigt der Löser das Dialogfeld "Testlösung anzeigen " an.
   Festlegen des Grads der Genauigkeit	Geben Sie auf der Registerkarte Alle Methoden im Feld Nebenbedingungsgenauigkeit den Grad der gewünschten Genauigkeit ein. Je kleiner der Wert, desto höher die Genauigkeit.
   Festlegen des Grads der Konvergenz	Geben Sie auf der Registerkarte GRG-Nichtlinear oder EA (Evolutionärer Algorithmus) im Feld Konvergenz den Wert der relativen Änderung ein, die Sie in den letzten fünf Iterationen zulassen möchten, bevor Solver den Prozess mit einer Lösung beendet. Je kleiner die Zahl, desto weniger relative Änderung ist zulässig.

3. Wählen Sie OK aus.

4. Wählen Sie im Dialogfeld "Solver-Parameter " die Option "Lösen " oder "Schließen" aus.

Speichern oder Laden eines Problemmodells

1. Wählen SieDatenlöser> aus.

   

2. Wählen Sie Laden/Speichern, geben Sie einen Zellbereich für den Modellbereich ein, und wählen Sie dann entweder Speichern oder Laden aus.
   Geben Sie beim Speichern eines Modells den Bezug für die erste Zelle eines vertikalen Bereichs leerer Zellen ein, in der Sie das Problemmodell platzieren möchten. Wenn Sie ein Modell laden, geben Sie den Bezug für den gesamten Zellbereich ein, der das Problemmodell enthält.

   Tipp

   Sie können die zuletzt im Dialogfeld Solver-Parameter ausgewählten Optionen mit einem Arbeitsblatt speichern, indem Sie die Arbeitsmappe speichern. Jedes Arbeitsblatt in einer Arbeitsmappe kann eigene Solver-Optionen aufweisen, die alle gespeichert werden können. Sie können auch mehr als ein Problem für ein Blatt definieren, indem Sie Laden/Speichern auswählen, um Probleme einzeln zu speichern.

Von Solver verwendete Lösungsmethoden

1. Wählen SieDatenlöser> aus.

   

2. Wählen Sie im Popupmenü Lösungsmethode auswählen eine der folgenden Methoden aus:

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 durchzuführen, die Ihnen helfen, optimale Lösungen zu finden.

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.