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. Da anhand der Werte in den Variablenzellen die Quartalsgewinne berechnet werden, sind die Werte mit der Formel in der Zielzelle F7, =SUMME(Q1 Gewinn:Q2 Gewinn), verbunden.

1. Variablenzellen

2. Nebenbedingung

3. Zielzelle

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

1. Klicken Sie auf der Registerkarte Daten in der Gruppe Analyse auf Solver.
   

   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 eine der folgenden Aktionen aus:

   • Wenn Sie für die Zielzelle den höchstmöglichen Wert definieren möchten, klicken Sie auf Max.

   • Wenn Sie für die Zielzelle den kleinstmöglichen Wert definieren möchten, klicken Sie auf Min.

   • Wenn Sie für die Zielzelle einen bestimmten Wert definieren möchten, klicken Sie auf Wert, und geben Sie dann den entsprechenden Wert in das Feld ein.

   • Geben Sie im Feld Durch Ändern von Variablenzellen einen Namen oder Zellbezug für jeden Variablenzellbereich ein. Trennen Sie nicht-angrenzende Bezüge durch Semikola. Die Variablenzellen müssen direkt oder indirekt auf die Zielzelle verweisen. Sie können bis zu 200 Variablenzellen definieren.

4. Geben Sie in das Feld Unterliegt den Nebenbedingungen die anzuwendenden Nebenbedingungen ein, indem Sie folgende Schritte ausführen:

   1. Klicken Sie im Dialogfeld Solver-Parameter auf Hinzufügen.

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

   3. Klicken Sie auf die Beziehung ( <=, =, >=, int, bin oder dif ), die Zwischen der Zelle, auf die verwiesen wird, und der Einschränkung. Wenn Sie auf int klicken, wird im Feld Einschränkungeine ganze Zahl angezeigt. Wenn Sie auf bin klicken, wird binär im Feld Einschränkung angezeigt. Wenn Sie auf dif klicken, 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 eine der folgenden Aktionen aus:

      • Wenn Sie die Nebenbedingung übernehmen und eine weitere hinzufügen möchten, klicken Sie auf Hinzufügen.

      • Wenn Sie die Nebenbedingung übernehmen und zum Dialogfeld Solver-Parameter zurückkehren möchten, klicken Sie auf OK.
        Hinweis    Sie können die Beziehungen int, bin und dif nur in Nebenbedingungen auf Variablenzellen anwenden.

        Sie können eine vorhandene Bedingung wie folgt ändern oder löschen:

   6. Klicken Sie im Dialogfeld Solver-Parameter auf die Nebenbedingung, die Sie ändern oder löschen möchten.

   7. Klicken Sie auf Ändern, und nehmen Sie dann Änderungen vor, oder klicken Sie auf Löschen.

5. Klicken Sie auf Lösen, und führen Sie dann eine der folgenden Aktionen aus:

   • Wenn die Ergebnisse auf dem Arbeitsblatt erhalten bleiben sollen, klicken Sie im Dialogfeld Solver-Ergebnisse auf Solver-Lösung akzeptieren.

   • Wenn die ursprünglichen Werte wiederhergestellt werden sollen, die vor dem Klicken auf Lösen angezeigt wurden, klicken Sie auf Ursprüngliche Werte wiederherstellen.

   • Sie können den Lösungsprozess mit ESC unterbrechen. Das Arbeitsblatt mit den zuletzt gefundenen Werten für die Variablenzellen wird in Excel neu berechnet.

   • Wenn von Solver eine Lösung gefunden wurde und Sie auf Grundlage dieser Lösung einen Bericht erstellen möchten, klicken Sie im Feld Berichte auf einen Berichtstyp und anschließend auf OK. 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.

   • Wenn Sie die Variablenzellwerte als Szenario speichern möchten, das Sie später anzeigen können, klicken Sie im Dialogfeld Ergebnis auf Szenario speichern. Geben Sie dann im Feld Szenarioname einen Namen für das Szenario ein.

1. Klicken Sie nach dem Definieren eines Problems im Dialogfeld Solver-Parameter auf Optionen.

2. Aktivieren Sie im Dialogfeld Optionen das Kontrollkästchen Iterationsergebnisse anzeigen, damit die Werte aller Zwischenergebnisse angezeigt werden. Klicken Sie dann auf OK.

3. Klicken Sie im Dialogfeld Solver-Parameter auf Lösen.

4. Führen Sie im Dialogfeld Zwischenergebnis eine der folgenden Aktionen aus:

   • Klicken Sie auf Beenden, um den Lösungsprozess zu beenden und das Dialogfeld Ergebnis anzuzeigen.

   • Klicken Sie auf Weiter, um den Lösungsprozess fortzusetzen und das nächste Zwischenergebnis anzuzeigen.

1. Klicken Sie im Dialogfeld Solver-Parameter auf Optionen.

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. Klicken Sie im Dialogfeld Solver-Parameter auf Laden/Speichern.

2. Geben Sie einen Zellbereich für den Modellbereich ein, und klicken Sie dann auf Speichern oder Laden.

   Wenn Sie ein Modell speichern, geben Sie den Zellbezug für die erste Zelle eines einspaltigen Zellbereichs mit leeren Zellen ein, in 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 einer Arbeitsmappe kann eigene Solver-Optionen aufweisen, die alle gespeichert werden. Sie können für ein Arbeitsblatt auch mehr als ein Problem definieren, indem Sie auf Laden/Speichern klicken, um die Probleme einzeln zu speichern.

Sie können eine beliebige der drei folgenden Lösungsmethoden im Dialogfeld Solver-Parameter verwenden:

• GRG-Nichtlinear    Für kontinuierliche nichtlineare Probleme.

• Simplex-LP    Für lineare Probleme.

• EA (Evolutionärer Algorithmus)    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. Da anhand der Werte in den Variablenzellen die Quartalsgewinne berechnet werden, sind die Werte mit der Formel in der Zielzelle D7, =SUMME(Q1 Gewinn:Q2 Gewinn), verbunden.

Variablenzellen

Eingeschränkte Zelle

Zielzelle

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

1. Klicken Sie auf Daten > Solver.

   

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 eine der folgenden Aktionen aus:

   Zweck	Aktion
   Definieren des höchstmöglichen Werts für die Zielzelle	Klicken Sie auf Max.
   Definieren des kleinstmöglichen Werts für die Zielzelle	Klicken Sie auf Min.
   Festlegen der Zielzelle auf einen bestimmten Wert	Klicken Sie auf Wert, und geben Sie 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.

   Wenn Sie eine Nebenbedingung hinzufügen möchten, gehen Sie folgendermaßen vor:

   1. Klicken Sie im Dialogfeld Solver-Parameter auf Hinzufügen.

   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ü für Beziehungen <= die Beziehung aus, die zwischen der Zelle, auf die verwiesen wird, und der Nebenbedingung verwendet werden soll. Wenn Sie <=, = oder >= auswählen, geben Sie im Feld Nebenbedingung einen Wert, einen Zellbezug oder Namen bzw. eine Formel ein.

      Hinweis: Die Beziehungen "int", "bin" und "dif" können nur in Nebenbedingungen für Variablenzellen verwendet werden.

   4. Führen Sie einen der folgenden Schritte aus:

   Zweck	Aktion
   Übernehmen der Nebenbedingung und Hinzufügen einer weiteren Nebenbedingung	Klicken Sie auf Hinzufügen.
   Übernehmen der Nebenbedingung und Zurückkehren zum Dialogfeld Solver-Parameter	Klicken Sie auf OK.

6. Klicken Sie auf Lösen, und führen Sie dann eine der folgenden Aktionen aus:

   Ziel	Aktion
   Beibehalten der Lösungswerte auf dem Blatt	Klicken Sie im Dialogfeld Solver-Lösung auf Solver-Lösung akzeptieren.
   Wiederherstellen der ursprünglichen Daten	Klicken Sie auf Ursprüngliche Werte wiederherstellen.

1. Klicken Sie auf Daten > Solver.

   

2. Klicken Sie nach dem Definieren eines Problems im Dialogfeld Solver-Parameter auf Optionen.

3. Aktivieren Sie das Kontrollkästchen Iterationsergebnisse anzeigen, damit die Werte aller Zwischenergebnisse angezeigt werden. Klicken Sie dann auf OK.

4. Klicken Sie im Dialogfeld Solver-Parameter auf Lösen.

5. Führen Sie im Dialogfeld Zwischenergebnis anzeigen eine der folgenden Aktionen aus:

   Ziel	Aktion
   Beenden des Lösungsprozesses und Anzeigen des Dialogfelds Solver-Ergebnisse	Klicken Sie auf Beenden.
   Fortsetzen des Lösungsprozesses und Anzeigen des nächsten Zwischenergebnisses	Klicken Sie auf Weiter.

1. Klicken Sie auf Daten > Solver.

   

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

   Ziel	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. Klicken Sie auf OK.

4. Klicken Sie im Dialogfeld Solver-Parameter auf Lösen oder Schließen.

1. Klicken Sie auf Daten > Solver.

   

2. Klicken Sie auf Laden/Speichern, geben Sie einen Zellbereich für den Modellbereich ein, und klicken Sie dann auf Speichern oder Laden.

   Wenn Sie ein Modell speichern, geben Sie den Zellbezug für die erste Zelle eines einspaltigen Zellbereichs mit leeren Zellen ein, in 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 für ein Arbeitsblatt auch mehr als ein Problem definieren, indem Sie auf Laden/Speichern klicken, um die Probleme einzeln zu speichern.

1. Klicken Sie auf Daten > Solver.

   

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