Erkennen und Lösen eines Problems 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 verwendet, um den Gewinn für jedes Quartal zu berechnen, sodass sie mit der Formelzielzelle F7, =SUMME (Q1 Profit:Q2 Profit) verknüpft sind.

1. Variablenzellen

2. Nebenbedingung

3. Zielzelle

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

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

   Hinweis: Wenn der Solver-Befehl oder die Gruppe Analyse nicht verfügbar ist, müssen Sie die Solver- Add-In aktivieren. Weitere Informationen finden Sie unter Aktivieren des Solver-Add-Ins.

   

2. Geben Sie im Feld Ziel festlegen einen Zellbezug oder einen Name 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. aus.

   • 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 Einschränkungen unterliegen 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 ) zwischen der Zelle, auf die verwiesen wird, und der Einschränkung aus. Wenn Sie int auswählen, wird im Feld Einschränkungeine 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 Nebenbedingung <=, = oder >= auswählen, dann geben Sie eine Zahl, einen Zellbezug, den Namen einer Zelle 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 Parameters 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 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 beizubehalten, wählen Sie im Dialogfeld Solver-Ergebnisse die Option Solver-Lösung beibehalten aus.

   • Um die ursprünglichen Werte wiederherzustellen, bevor Sie Solve ausgewählt haben, wählen Sie Ursprüngliche Werte wiederherstellen aus.

   • Sie können den Lösungsprozess mit ESC unterbrechen. Excel berechnet das Arbeitsblatt mit den letzten Werten neu, die es für die Entscheidungsvariablenzellen gefunden hat.

   • Um einen Bericht zu erstellen, der auf Ihrer Lösung basiert, nachdem Solver eine Lösung gefunden hat, wählen Sie im Feld Berichte einen Berichtstyp aus, und wählen Sie 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 die Zellenwerte ihrer Entscheidungsvariablen als 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.

1. Nachdem Sie ein Problem definiert haben, wählen Sie im Dialogfeld Solver-Parameter die Option Optionen 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-Parameterdie Option Lösen aus.

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

   • Um den Lösungsprozess zu beenden 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.

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.

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

2. Geben Sie einen Zellbereich für den Modellbereich ein, und wählen Sie entweder Speichern oder Laden aus.

   Wenn Sie ein Modell speichern, geben Sie den Verweis für die erste Zelle eines vertikalen Bereichs leerer Zellen ein, an dem 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 eigene Solver-Auswahlen verfügen, die alle gespeichert werden. Sie können auch mehrere Probleme für ein Arbeitsblatt definieren, indem Sie Laden/Speichern auswählen, um Probleme einzeln zu speichern.

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

• Generalized Reduced Gradient (GRG) Nonlinear:    Für kontinuierliche nichtlineare Probleme.

• LP Simplex:    Für lineare Probleme.

• Evolutionären:    Für nicht kontinuierliche Probleme.

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 verwendet, um den Gewinn für jedes Quartal zu berechnen, sodass sie mit der Formelzielzelle D7, =SUMME(Q1 Profit:Q2 Profit) verknüpft sind.

Variablenzellen

Eingeschränkte Zelle

Zielzelle

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

1. Wählen Sie Data > Solver aus.

   

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

   Hinweis: Die Zielzelle muss eine Formel enthalten.

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

   Zweck	Aktion
   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.

   Führen Sie die folgenden Schritte aus, 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. Wählen Sie im Popupmenü <= Beziehung die gewünschte Beziehung zwischen der Zelle, auf die verwiesen wird, und der Einschränkung aus. Wenn Sie <=, =, oder >=auswählen, geben Sie im Feld Einschränkung eine Zahl, einen Zellbezug oder -namen oder eine Formel ein.

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

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

   Zweck	Aktion
   Ü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.

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

1. Wählen Sie Data > Solver 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-Parameterdie Option Lösen aus.

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

   Zweck	Aktion
   Beenden des Lösungsprozesses und Anzeigen des Dialogfelds Solver-Ergebnisse	Wählen Sie Beenden aus.
   Fortsetzen des Lösungsprozesses und Anzeigen des nächsten Zwischenergebnisses	Wählen Sie Weiter aus.

1. Wählen Sie Data > Solver 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:

   Zweck	Aktion
   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 maximal zulässige Zeit oder die Anzahl der Iterationen erreicht, bevor Solver eine Lösung gefunden hat, zeigt Solver das Dialogfeld Zwischenergebnis 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-Parameterdie Option Lösen oder Schließen aus.

1. Wählen Sie Data > Solver aus.

   

2. Wählen Sie Laden/Speichern aus, geben Sie einen Zellbereich für den Modellbereich ein, und wählen Sie dann entweder Speichern oder Laden aus.

   Wenn Sie ein Modell speichern, geben Sie den Verweis für die erste Zelle eines vertikalen Bereichs leerer Zellen ein, an dem 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 mehrere Probleme für ein Blatt definieren, indem Sie Laden/Speichern auswählen, um Probleme einzeln zu speichern.

1. Wählen Sie Data > Solver aus.

   

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