Optimierung mit Excel Solver

Solver ist ein Microsoft Excel-Zusatzprogramm, das Sie zur Optimierung der Was-wäre-wenn-Analyse verwenden können.

Laut O'Brien und Marakas optimization analysisist eine komplexere Erweiterung der Zielsuchanalyse. Anstatt einen bestimmten Zielwert für eine Variable festzulegen, besteht das Ziel darin, unter bestimmten Einschränkungen den optimalen Wert für eine oder mehrere Zielvariablen zu finden. Anschließend werden eine oder mehrere andere Variablen unter Berücksichtigung der angegebenen Einschränkungen wiederholt geändert, bis Sie die besten Werte für die Zielvariablen ermitteln.

In Excel können Sie verwenden Solver zu finden optimal value (Maximum oder Minimum oder ein bestimmter Wert) für eine Formel in einer Zelle, die als Zielzelle bezeichnet wird, unter bestimmten Einschränkungen oder Grenzen für die Werte anderer Formelzellen im Arbeitsblatt.

Dies bedeutet, dass der Solver mit einer Gruppe von Zellen arbeitet, die als Entscheidungsvariablen bezeichnet werden und zur Berechnung der Formeln in den Ziel- und Einschränkungszellen verwendet werden. Solver passt die Werte in den Zellen der Entscheidungsvariablen an, um die Grenzwerte für Einschränkungszellen zu erfüllen und das gewünschte Ergebnis für die Zielzelle zu erzielen.

Mit Solver können Sie optimale Lösungen für verschiedene Probleme finden, z.

Ermittlung des monatlichen Produktmixes für eine Arzneimittelherstellungseinheit, die die Rentabilität maximiert.
Planen der Belegschaft in einer Organisation.
Transportprobleme lösen.
Finanzplanung und Budgetierung.

Solver-Add-In aktivieren

Bevor Sie mit der Suche nach einer Lösung für ein Problem mit Solver fortfahren, stellen Sie sicher, dass die Solver Add-in wird in Excel wie folgt aktiviert -

Klicken Sie auf der Multifunktionsleiste auf die Registerkarte DATEN. DasSolver Der Befehl sollte in der Analysegruppe angezeigt werden (siehe unten).

Falls Sie den Solver-Befehl nicht finden, aktivieren Sie ihn wie folgt:

Klicken Sie auf die Registerkarte DATEI.
Klicken Sie im linken Bereich auf Optionen. Das Dialogfeld Excel-Optionen wird angezeigt.
Klicken Sie im linken Bereich auf Add-Ins.
Wählen Sie im Feld Verwalten die Option Excel-Add-Ins aus und klicken Sie auf Los.

Das Dialogfeld Add-Ins wird angezeigt. PrüfenSolver Add-inund klicken Sie auf OK. Jetzt sollten Sie den Solver-Befehl in der Multifunktionsleiste auf der Registerkarte DATEN finden können.

Von Solver verwendete Lösungsmethoden

Sie können je nach Art des Problems eine der folgenden drei von Excel Solver unterstützten Lösungsmethoden auswählen:

LP Simplex

Wird für lineare Probleme verwendet. EINSolver Modell ist unter den folgenden Bedingungen linear -

Die Zielzelle wird berechnet, indem die Terme der (sich ändernden Zelle) * (konstanten) Form addiert werden.
Jede Einschränkung erfüllt die Anforderung des linearen Modells. Dies bedeutet, dass jede Einschränkung bewertet wird, indem die Terme der (sich ändernden Zelle) * (Konstanten) -Form addiert und die Summen mit einer Konstanten verglichen werden.

Generalized Reduced Gradient (GRG) Nichtlinear

Wird für glatte nichtlineare Probleme verwendet. Wenn Ihre Zielzelle, eine Ihrer Einschränkungen oder beide Verweise auf sich ändernde Zellen enthalten, die nicht die Form (sich ändernde Zelle) * (konstant) haben, haben Sie ein nichtlineares Modell.

Evolutionär

Grundlegendes zur Solver-Bewertung

Der Solver benötigt die folgenden Parameter:

Entscheidungsvariable Zellen
Zwangszellen
Objektive Zellen
Lösungsmethode

Die Solver-Bewertung basiert auf Folgendem:

Die Werte in den Zellen der Entscheidungsvariablen werden durch die Werte in den Einschränkungszellen eingeschränkt.
Die Berechnung des Wertes in der Zielzelle umfasst die Werte in den Zellen der Entscheidungsvariablen.
Solver verwendet die ausgewählte Lösungsmethode, um den optimalen Wert in der Zielzelle zu erhalten.

Ein Problem definieren

Angenommen, Sie analysieren die Gewinne eines Unternehmens, das ein bestimmtes Produkt herstellt und verkauft. Sie werden gebeten, den Betrag zu ermitteln, der in den nächsten zwei Quartalen für Werbung ausgegeben werden kann, maximal jedoch 20.000. Die Höhe der Werbung in jedem Quartal wirkt sich auf Folgendes aus:

Die Anzahl der verkauften Einheiten, die indirekt die Höhe der Verkaufserlöse bestimmt.
Die damit verbundenen Kosten und
Der Profit.

Sie können das Problem wie folgt definieren:

Finden Sie Stückkosten.
Finden Sie die Werbekosten pro Einheit.
Stückpreis finden.

Stellen Sie als Nächstes die Zellen für die erforderlichen Berechnungen wie unten angegeben ein.

Wie Sie sehen können, werden die Berechnungen für Quartal 1 und Quartal 2 durchgeführt, die in Betracht gezogen werden:

Die Anzahl der im ersten Quartal zum Verkauf angebotenen Einheiten beträgt 400 und im zweiten Quartal 600 (Zellen - C7 und D7).
Die Anfangswerte für das Werbebudget werden auf 10000 pro Quartal festgelegt (Zellen - C8 und D8).
Die Anzahl der verkauften Einheiten hängt von den Werbekosten pro Einheit ab und ist daher das Budget für das Quartal / Adv. Kosten pro Einheit. Beachten Sie, dass wir die Min-Funktion verwendet haben, um sicherzustellen, dass die Nr. Anzahl verkaufter Einheiten in <= Nr. Anzahl verfügbarer Einheiten. (Zellen - C9 und D9).
Der Umsatz wird als Stückpreis * Anzahl der verkauften Einheiten (Zellen - C10 und D10) berechnet.
Die Ausgaben werden berechnet als Stückkosten * Anzahl der verfügbaren Einheiten + Adv. Kosten für dieses Quartal (Zellen - C11 und D12).
Gewinn ist Umsatz - Ausgaben (Zellen C12 und D12).
Der Gesamtgewinn ist der Gewinn in Quartal 1 + der Gewinn in Quartal 2 (Zelle - D3).

Als nächstes können Sie die Parameter für Solver wie folgt einstellen:

Wie Sie sehen können, sind die Parameter für Solver -

Die Zielzelle ist D3, die den Gesamtgewinn enthält, den Sie maximieren möchten.
Entscheidungsvariable Zellen sind C8 und D8, die die Budgets für die beiden Quartale - Quartal1 und Quartal2 - enthalten.
Es gibt drei Constraint-Zellen - C14, C15 und C16.
- Zelle C14, die das Gesamtbudget enthält, soll die Einschränkung von 20000 festlegen (Zelle D14).
- Zelle C15, die die Nr. Enthält. Bei den im ersten Quartal verkauften Einheiten wird die Einschränkung <= Nr. Anzahl der in Quartal 1 verfügbaren Einheiten (Zelle D15).
- Zelle C16, die die Nr. Enthält. Bei den im zweiten Quartal verkauften Einheiten wird die Einschränkung <= Nr. Anzahl der in Quartal 2 verfügbaren Einheiten (Zelle D16).

Lösung des Problems

Der nächste Schritt besteht darin, Solver zu verwenden, um die Lösung wie folgt zu finden:

Step 1- Gehen Sie auf der Multifunktionsleiste zu DATEN> Analyse> Löser. Das Dialogfeld Solver-Parameter wird angezeigt.

Step 2 - Wählen Sie im Feld Ziel festlegen die Zelle D3 aus.

Step 3 - Wählen Sie max.

Step 4 - Wählen Sie im Bereich C8: D8 By Changing Variable Cells Box.

Step 5 - Klicken Sie anschließend auf die Schaltfläche Hinzufügen, um die drei von Ihnen identifizierten Einschränkungen hinzuzufügen.

Step 6- Das Dialogfeld Einschränkung hinzufügen wird angezeigt. Legen Sie die unten angegebene Einschränkung für das Gesamtbudget fest und klicken Sie auf Hinzufügen.

Step 7- Legen Sie die Einschränkung für die Gesamtzahl fest. der im ersten Quartal verkauften Einheiten wie unten angegeben und klicken Sie auf Hinzufügen.

Step 8- Legen Sie die Einschränkung für die Gesamtzahl fest. der im zweiten Quartal verkauften Einheiten wie unten angegeben und klicken Sie auf OK.

Das Dialogfeld Solver-Parameter wird mit den drei Einschränkungen angezeigt, die im Feld –Subject to the Constraints hinzugefügt wurden.

Step 9 - In der Select a Solving Method Wählen Sie im Feld Simplex LP aus.

Step 10- Klicken Sie auf die Schaltfläche Lösen. Das Dialogfeld Solver-Ergebnisse wird angezeigt. WählenKeep Solver Solution und klicken Sie auf OK.

Die Ergebnisse werden in Ihrem Arbeitsblatt angezeigt.

Wie Sie sehen können, ist die optimale Lösung, die unter den gegebenen Bedingungen einen maximalen Gesamtgewinn erzielt, die folgende:

Gesamtgewinn - 30000.
Adv. Budget für Quartal 1 - 8000.
Adv. Budget für Quartal 2 - 12000.

Schritt durch Solver-Testlösungen

Sie können die Solver-Testlösungen schrittweise durchgehen und sich die Iterationsergebnisse ansehen.

Step 1 - Klicken Sie im Dialogfeld Solver-Parameter auf die Schaltfläche Optionen.

Das Options Das Dialogfeld wird angezeigt.

Step 2 - Aktivieren Sie das Feld Iterationsergebnisse anzeigen und klicken Sie auf OK.

Step 3 - Die Solver ParametersDas Dialogfeld wird angezeigt. KlickenSolve.

Step 4 - Die Show Trial Solution Das Dialogfeld wird angezeigt und zeigt die Meldung an - Solver paused, current solution values displayed on worksheet.

Wie Sie sehen können, werden die aktuellen Iterationswerte in Ihren Arbeitszellen angezeigt. Sie können entweder verhindern, dass der Solver die aktuellen Ergebnisse akzeptiert, oder mit dem Solver fortfahren, in weiteren Schritten eine Lösung zu finden.

Step 5 - Klicken Sie auf Weiter.

Das Show Trial SolutionDas Dialogfeld wird bei jedem Schritt angezeigt. Nachdem die optimale Lösung gefunden wurde, wird das Dialogfeld Solver-Ergebnisse angezeigt. Ihr Arbeitsblatt wird bei jedem Schritt aktualisiert und zeigt schließlich die Ergebniswerte an.

Speichern der Solver-Auswahl

Sie haben die folgenden Speicheroptionen für die Probleme, die Sie mit Solver lösen:

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 eine eigene Solver-Auswahl haben. Alle werden beim Speichern der Arbeitsmappe gespeichert.
Sie können auch mehr als ein Problem in einem Arbeitsblatt definieren, jedes mit seiner eigenen Solver-Auswahl. In einem solchen Fall können Sie Probleme einzeln mit dem Dialogfeld Laden / Speichern im Solver-Parameter laden und speichern.
- Drücke den Load/SaveTaste. Das Dialogfeld Laden / Speichern wird angezeigt.
- Geben Sie zum Speichern eines Problemmodells die Referenz für die erste Zelle eines vertikalen Bereichs leerer Zellen ein, in die Sie das Problemmodell einfügen möchten. Klicken Sie auf Speichern.

Das Problemmodell (der festgelegte Solver-Parameter) wird ab der Zelle angezeigt, die Sie als Referenz angegeben haben.

Geben Sie zum Laden eines Problemmodells die Referenz für den gesamten Zellenbereich ein, der das Problemmodell enthält. Klicken Sie dann auf die Schaltfläche Laden.