Visual Basic-Makros in Excel 97 mithilfe von Excel Solver erstellen

Veralteter Haftungsausschluss für KB-Inhalte

Dieser Artikel wurde für Produkte geschrieben, für die Microsoft keinen Support mehr anbietet. Deshalb wird dieser Artikel im vorliegenden Zustand bereitgestellt und nicht mehr aktualisiert.

Zusammenfassung

Dieser Artikel beschreibt, wie Microsoft Excel Solver in Microsoft Excel 97 mit Microsoft Visual Basic-Makros. Microsoft Excel Solver wird ein Microsoft Excel-add-in.

Außerdem enthält dieser Artikel Informationen zum Entwerfen eines Makros Makros und zum Arbeiten mit Einschränkungen eines Makros. Dieser Artikel beschreibt auch den Algorithmus und Methoden, die von Microsoft Excel Solver verwendet werden. Die folgende Liste enthält die im Artikel behandelten Themen.

Einführung

Dieser Artikel enthält Informationen zu Microsoft Excel Solver.

Weitere Informationen

Beschreibung des Microsoft Excel Solver

Microsoft Excel Solver ist ein Microsoft Excel Add-in Microsoft Excel Solver hilft Sie den optimalen Wert für eine Formel in ein bestimmtes Ziel in einem Microsoft Excel-Arbeitsblatt zu bestimmen. Microsoft Excel Solver passt die Werte der Zellen, die Zielzelle im Zusammenhang mit einer Formel. Nach dem Erstellen einer Formel und von Parametern oder Einschränkungen für die Variablen in der Gleichung festlegen versucht Microsoft Excel Solver verschiedene Lösungen um eine Antwort zu erhalten, das alle Nebenbedingungen erfüllt. Microsoft Excel Solver verwendet die folgenden Elemente eine Formel "lösen":
  • Zielzelle - die Zielzelle ist das Ziel. Es ist die Zelle im Arbeitsblattmodell, das minimiert, maximiert oder auf einen bestimmten Wert festgelegt werden.
  • Veränderbare Zellen - veränderbare Zellen sind die entscheidungsvariablen. Diese Zellen auf den Wert der Zielzelle. Diese Zellen werden von Microsoft Excel Solver die optimale Lösung für die Zielzelle geändert.
  • Constraints - Nebenbedingungen werden auf den Inhalt der Zellen. Beispielsweise eine Zelle in einem Arbeitsblatt-Modell möglicherweise auf ganzzahlige Werte, während einer anderen Zelle auf kleiner als ein angegebener Wert.
Sie können die Erstellung und Bearbeitung von Microsoft Excel Solver-Modellen mithilfe einer Microsoft Visual Basic for Applications (VBA)-Makro automatisieren. Dieser Artikel beschreibt wie Sie VBA-Makrosprache Microsoft Excel Solver-Funktionen in Microsoft Excel 97 verwenden. Es wird vorausgesetzt, dass Sie Microsoft Visual Basic-Editor für Microsoft Excel 97 mit VBA-Sprache vertraut sind. Die in diesem Artikel verwendeten Beispiele stehen zum Download auf der folgenden Microsoft-Website:Hinweis Sie können auch Makros und die Beispiele in diesem Artikel in Microsoft Excel 5.0 und 7.0 beschrieben.

zurück zum Anfang

Verwendung von Microsoft Excel Solver-Funktionen in einem VBA-Makro

Um Microsoft Excel Solver-Add-in-Funktionen in einem VBA-Makro verwenden, müssen Sie das Add-in aus der Arbeitsmappe VBA-Projekt verweisen, die Makros enthält. Wenn Sie nicht Microsoft Excel Solver-add-in verweisen, erhalten Sie folgende Kompilierungsfehler beim Ausführen des Makros:
Kompilierungsfehler: Sub oder Funktion nicht definiert.
Microsoft Excel Solver-add-in für Makros in der Arbeitsmappe verweisen möchten, gehen Sie folgendermaßen vor:
  1. Öffnen Sie die Arbeitsmappe.
  2. Zeigen Sie im Menü Extras auf
    Makro, und klicken Sie dann auf Visual Basic-Editor.
  3. Klicken Sie im Menü Extras auf
    Verweise.
  4. Klicken Sie in der Liste Verfügbare Verweise das Kontrollkästchen Solver.xls und klicken Sie dann auf OK.

    Hinweis Wenn Solver.xls Verfügbare Verweise angezeigt wird
    Liste, klicken Sie auf Durchsuchen. Im Dialogfeld Verweis hinzufügen wählen Sie Solver.xla-Datei, und klicken Sie auf Öffnen. Solver.xla Datei befindet sich normalerweise im Unterordner C:\Program Files\Microsoft Office\Office\Library\Solver.
Sie können jetzt Microsoft Excel Solver-Funktionen in einem VBA-Makro verwenden.

zurück zum Anfang

Entwerfen ein VBA-Makros, das erstellt und löst ein einfaches Microsoft Excel Solver-Modell

Microsoft Excel Solver viele Funktionen bietet, sind die folgenden drei Funktionen grundlegende erstellen und Modelle:
  • SolverOK -Funktion
  • Solver
  • SolverFinish -Funktion

SolverOK-Funktion

Die Funktion SolverOK definiert Microsoft Excel Solver-Grundmodell. SolverOK -Funktion ist in der Regel die erste Funktion, mit der das Microsoft Excel Solver-Modell zu erstellen. SolverOK -Funktion entspricht dem Klicken auf Solver im Menü Extras und dann die Optionen im Dialogfeld Solver-Parameter angeben. Die Syntax für die SolverOK -Funktion lautet:
SolverOK (Argumentwerte SetCell, MaxMinVal ValueOf, ByChange)
Die folgenden Informationen beschreiben die Syntax für die SolverOK -Funktion:
  • Argumentwerte SetCell gibt die Zielzelle an.
  • MaxMinVal entspricht die Zielzelle für einen Höchstwert (1), einen Mindestwert (2) oder einen bestimmten Wert (3) lösen möchten.
  • ValueOf gibt den Wert, mit dem die Zielzelle verglichen wird. Wenn Sie MaxMinVal 3 festlegen, müssen Sie dieses Argument angeben. Wenn Sie MaxMinVal auf 1 oder 2 festlegen, können Sie dieses Argument weglassen.
  • ByChange gibt die Zelle oder der Zellbereich, der verändert wird.
Abbildung 1 ordnet die Argumente für die Funktion SolverOK Parameter im Dialogfeld Solver-Parameter .


Abbildung 1. Parameter, die die Argumente SolverOK zugeordnet sind

 Figure 1. Parameters that are associated with the SolverOK arguments

Solver

Solver löst Modell mit der Funktion SolverOK angegebenen Parameter. Ausführen von Solver entspricht dem Klicken auf lösen in dem Solver-Parameter
im Dialogfeld. Die Syntax für Solver lautet:
SolverSolve (UserFinish, ShowRef)

Die folgenden Informationen beschreiben die Syntax für Solver :
  • UserFinish gibt an, ob den Benutzer das Modell Fertig stellen möchten.

    Um die Ergebnisse zurückzukehren, ohne das Dialogfeld Ergebnis anzuzeigen, setzen Sie dieses Argument auf TRUE. Um das Ergebnis zurückgegeben und das Dialogfeld Ergebnis anzuzeigen, legen Sie dieses Argument auf FALSE
  • ShowRef identifiziert das Makro aufgerufen wird, wenn Microsoft Excel Solver eine Zwischenlösung zurückgibt.

    Das Argument ShowRef sollte verwendet werden, nur, wenn TRUE an das Argument StepThru der Funktion SolverOptions übergeben wird.

SolverFinish-Funktion

SolverFinish -Funktion gibt an, was mit dem Ergebnis geschehen und welche Art von Bericht zu erstellen, wenn der Lösungsvorgang abgeschlossen ist. Die Syntax für die SolverFinish -Funktion lautet:
SolverFinish (KeepFinal, ReportArray)

Die folgenden Informationen beschreiben die Syntax der SolverFinish -Funktion:
  • KeepFinal gibt an, was mit den endgültigen Ergebnissen führen. Ist KeepFinal auf 1 gesetzt, die endgültigen Lösungswerte in veränderbaren Zellen werden Werte ersetzen. Ist KeepFinal auf 2 gesetzt, werden die endgültigen Lösungswerte verworfen, und die vorherigen Werte werden wiederhergestellt.
  • ReportArray gibt ein Array der Berichtstyp angibt, den Microsoft Excel erstellen der Lösung. Wenn ReportArray auf 1 festgelegt ist, erstellt Microsoft Excel ein Antwortbericht. Wenn 2, Microsoft Excel erzeugt einen Bericht Empfindlichkeit und 3 Microsoft Excel erzeugt einen Bericht Grenzen. Weitere Informationen über diese Berichte finden Sie im Abschnitt "Generieren von Berichten für Solutions".
Abbildung 2. Microsoft Excel Solver Ergebnisoptionen SolverFinish Argumente

 Figure 2. Solver results options that are associated with SolverFinish arguments

Dieser Artikel beschreibt, wie ein einfaches Microsoft Excel Solver-Modell interaktiv erstellen. Der erste Schritt ist das Arbeitsblatt für das Modell zu erstellen. Das Arbeitsblatt enthält einige Datenzellen und mindestens eine Zelle mit der Formel. Dabei hängt die Zellen im Arbeitsblatt. Nachdem Sie das Arbeitsblatt eingerichtet, klicken Sie auf
Solver im Menü Extras . Geben Sie im Dialogfeld Solver-Parameter der Zielzelle, den Wert für lösen den Zellbereich, der verändert wird und Nebenbedingungen. Klicken Sie auf lösen , um die Projektmappe zu starten. Nach Microsoft Excel Solver eine Lösung gefunden hat, die Ergebnisse werden im Arbeitsblatt, und Microsoft Excel Solver wird eine Meldung angezeigt, die Sie auffordert, möchten Sie die endgültigen Ergebnisse oder verwerfen möchten. Wenn Sie eine der folgenden Optionen klicken, wird Microsoft Excel Solver beendet.

Abbildung 3 zeigt ein einfaches Modell mit diesen Schritten erstellen.

Abbildung 3. Ein einfaches Modell: die Quadratwurzel Modell

 Figure 3. A simple model: The Square Root model

In diesem Beispiel Zelle A1 ändern enthält die Formel = A1 ^ 2, um einen Wert an, die Zelle A2 wird gleich einen Wert von 50. In anderen Worten suchen Sie Quadratwurzel 50. Im Quadratwurzel Modell sind keine Einschränkungen. Find_Square_Root -Makro führt die folgenden Aufgaben:
  • Ein Modell, das den Wert der Zelle A2 den Wert 50 gelöst wird, indem der Wert der Zelle A1 festgelegt.
  • Das Modell ausgewertet.
  • Es speichert die Ergebnisse in das Arbeitsblatt ohne das Dialogfeld Ergebnis anzuzeigen.
Dieses einfache Makro erstellt ein Microsoft Excel Solver-Modell und ohne Benutzereingriff löst. Der folgende Code beschreibt das Find_Square_Root -Makro:
    Sub Find_Square_Root()
' Set up the parameters for the model.
' Set the target cell A2 to a value of 50 by changing cell A1.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=50, _
ByChange:=Range("A1")

' Solve the model but do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub

Das Makro Find_Square_Root2 ist eine modifizierte Version des Makros Find_Square_Root . Bei Verwendung die InputBox -Funktion aufgefordert das Makro Find_Square_Root2 für den Wert für die Zielzelle gelöst werden sollen. Nachdem Sie einen Wert eingeben, das Find_Square_Root2 -Makro legt diesen Parameter als Wert des ArgumentsValueof SolverOK, löst das Problem, speichert die Ergebnisse in die Variable Quadratwurzel verwirft die Lösung und wird der Wert in dem Arbeitsblatt in den ursprünglichen Zustand wiederhergestellt. Im Grunde veranschaulicht das Find_Square_Root2 -Makro speichern der Ergebnisse in eine oder mehrere Variablen und die veränderbaren Zellen auf ihren ursprünglichen Wert wiederherstellen.


Der folgende Code beschreibt das Find_Square_Root2 -Makro:
    Sub Find_Square_Root2()
Dim val
Dim sqroot

' Request the value for which you want to obtain the square root.
val = Application.InputBox( _
prompt:="Please enter the value for which you want " & _
"to find the square root:", Type:=1)

' Set up the parameters for the model.
SolverOK SetCell:=Range("A2"), MaxMinVal:=3, ValueOf:=val, _
ByChange:=Range("A1")

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Save the value of cell A1 (the changing cell) before you discard
' the results.
sqroot = Range("a1")

' Finish and discard the results.
SolverFinish KeepFinal:=2

' Show the result in a message box.
MsgBox "The square root of " & val & " is " & Format(sqroot, "0.00")

End Sub

zurück zum Anfang

Zum Generieren von Berichten für Projektmappen

Microsoft Excel Solver bietet verschiedene Berichte, die beschreiben, wie die Ergebnisse geändert und wie nah die Nebenbedingungen kritische Werte haben. Jeder Bericht wird in einem eigenen Arbeitsblatt in der Arbeitsmappe. Folgende sind die Berichtstypen, die Microsoft Excel Solver bietet:
  • Antwortbericht - Bericht der Antwort listet die Zielzelle und die veränderbaren Zellen mit entsprechenden Ausgangs- und Endwerten, Nebenbedingungen und Informationen zu den Nebenbedingungen.
  • Empfindlichkeit Bericht - der Empfindlichkeit Bericht enthält Informationen wie die Lösung auf kleine Änderung in der Formel für die Zielzelle reagiert.
  • Grenzwerte Bericht - die Grenzwerte Bericht listet die Zielzelle und die veränderbaren Zellen mit den jeweiligen Werten, oberen und unteren Grenzwerten und Zielwerte.
Geben Sie ein Array von Werten für das ReportArray -Argument der SolverFinish -Funktion zum Erstellen von Berichten für die Modelle. Weitere Informationen zum Argument ReportArray finden Sie im Abschnitt "SolverFinish (KeepFinal, ReportArray)". Beispielsweise ändern Sie SolverFinish -Funktion im Makro ggf. einen Bericht Grenzwerte für das Modell generieren, das Find_Square_Root2 -Makro erstellt und löst, damit die folgenden Beispielcode ähnelt:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
Um mehrere Berichte ändern Sie SolverFinish -Funktion folgenden Code ähnelt:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)
zurück zum Anfang

Verwendung die Microsoft ExcelSolver Funktionen in einer Schleife Makro

In vielen Situationen ist es eine gute Idee, Microsoft Excel Solver die Zielzelle für mehrere Werte zu lösen. Sie können im Allgemeinen dazu mithilfe einer Schleifenstrukturen, die in VBA verfügbar sind.


Das Create_Square_Root_Table -Makro veranschaulicht, wie Microsoft Excel Solver in einer Schleife Makro funktioniert. Das Create_Square_Root_Table -Makro erstellt eine Tabelle in ein neues Arbeitsblatt. 1 bis 10 und die entsprechenden Quadratwurzel jede Zahl eingefügt. Das Create_Square_Root_Table -Makro erstellt die Tabelle mit einer For -Schleife durchlaufen die Zahlen 1 bis 10 zu der Zielzelle Quadratwurzel Modell für einen Wert, der die Anzahl der Iteration entspricht. Der folgende Code beschreibt das Create_Square_Root_Table -Makro:
    Sub Create_Square_Root_Table()
' Add a new worksheet to the workbook.
Set w = Worksheets.Add

' Put the value 2 in cell C1 and the formula =C1^2 in cell C2.
w.Range("C1").Value = 2
w.Range("C2").Formula = "=C1^2"

' A loop that will make 10 iterations, starting with the number 1,
' and finishing at the number 10.
For i = 1 To 10

' Set the Solver parameters that indicate that Solver should
' solve the cell C2 for the value of i (where i is the number
' of the iteration) by changing cell C1.
SolverOk SetCell:=Range("C2"), ByChange:=Range("C1"), _
MaxMinVal:=3, ValueOf:=i

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Save the value of i in column A and the results of the
' changing cell in column B.
w.Cells(i, 1) = i
w.Cells(i, 2) = Range("C1")

' Finish and discard the final results.
SolverFinish KeepFinal:=2

Next

' Clear the range C1:C2
w.Range("C1:C2").Clear

End Sub


Das Create_Square_Root_Table -Makro wird in Abbildung 4 dargestellte Tabelle generiert.


Abbildung 4. Ausgabe von Create_Square_Root_Table -Makro


Figure 4. Output that is generated by the Create_Square_Root_Table macro

zurück zum Anfang

Arbeiten mit Nebenbedingungen

Eine Einschränkung ist eine Beschränkung für den Inhalt von einer oder mehreren Zellen. Ein Modell kann eine oder mehrere Nebenbedingungen haben. Die Einschränkung ist eine Ungleichheit oder eine Gleichheiten, die Kombinationen der Werte für die entscheidungsvariablen aus der Projektmappe entfernen. Beispielsweise erfordern eine Einschränkung einer Zelle größer als NULL sein und diese andere Zelle enthalten nur einen ganzzahligen Wert.

Quadratwurzel-Modell, das wir bisher begegnet ist ein einfaches Modell, das Einschränkungen nicht enthält. Abbildung 5 zeigt ein Modell, das Integritätsregeln verwendet. Dieses Modell ist die optimale Produktkombination für maximalen Gewinn suchen.

Abbildung 5. Produktmix mit abnehmender Gewinnspanne

Figure 5. Product mix with diminishing profit margin

Wenn beispielsweise ein Unternehmen produziert, Fernseher, Stereoanlagen und Lautsprecher und eine gemeinsame Teile Inventur Stromversorgungen, Lautsprechermembranen und usw. verwendet. Die Teile werden in begrenzten Mengen. Ihr Ziel ist die Profitabelste Produktmix zu bestimmen. Gewinn pro Einheit mit verringert, da zusätzliche Anreize Vertriebsweg geladen werden. Abnehmende Exponent ist 0.9. Diese Exponenten wird verwendet, um den Gewinn berechnen nach Produkt im Bereich G11:I11.

Ihr Ziel ist den maximalen Gewinn (Zelle G14) suchen. Werte, die Sie der maximale Gewinn zu ändern sind die Anzahl der Einheiten, die Sie erstellen. Bereich G9:G11 stellt die veränderbaren Zellen in diesem Modell. Die einzige Einschränkung ist, dass die Anzahl der verwendeten Teile Anzahl Teile höchstens Lagerbestand haben. Mit Microsoft Excel Solver wird diese Einschränkung als E3:E7 < = B3: B7. Würden Sie dieses Microsoft Excel Solver interaktiv erstellen, sieht die Microsoft Excel Solver-Parameter ähnlich, die in Abbildung 6 sind.

Abbildung 6. Microsoft Excel Solver-Parameter für Produkt-Mix mit Gewinnspanne verringern

Figure 6. Microsoft Excel Solver parameters for the product mix with Diminishing Profit Margin model

Erstellen und Lösen der Produktmix mit Gewinnspanne verringern, verwenden Sie eine neue Funktion, die SolverAdd -Funktion neben Microsoft Excel Solver VBA-Funktionen, die zuvor beschrieben wurden. Die SolverAdd -Funktion fügt die Einschränkung auf das Modell. Ausführung SolverAdd -Funktion entspricht dem Klicken auf Hinzufügen
Schaltfläche im Dialogfeld Solver-Parameter . Die
SolverAdd -Funktion hat die folgende Syntax:
SolverAdd (CellRef Beziehung FormulaText)

Die folgenden Informationen beschreiben die Syntax für die SolverAdd -Funktion:
  • CellRef verweist auf eine oder mehrere Zellen, die Links der Integritätsregel bilden.
  • Beziehung ist die arithmetische Beziehung zwischen der linken und rechten Rand einer Einschränkung.
  • Beziehung kann einen Wert zwischen 1 und 5 wie im folgenden Beispiel:
    • Der Wert 1 ist kleiner oder gleich (< =).
    • Der Wert 2 ist gleich (=).
    • Der Wert 3 ist größer oder gleich (> =).
    • Der Wert 4 ist eine Ganzzahl.
    • Der Wert 5 ist die Binärdatei (ein Wert von NULL oder eins).
  • FormulaText verweist auf eine oder mehrere Zellen, die rechts neben dem constraint.* *
** Wenn Sie einen Zellbereich für das Argument FormulaText SolverAdd -Funktion angeben, prüfen Sie, ob der Verweis relativ oder absolut ist. Im Allgemeinen müssen Sie einen absoluten Bezug FormulaText Argument angeben. Wenn Sie relative Verweise für das Argument FormulaText angeben, erkennen Sie, dass der Verweis der Zielzelle und nicht die aktive Zelle aus.

Hinweis Verwenden Sie in Microsoft Excel 5.0 und 7.0 Z1S1-Notation, wenn Sie eine Zelle oder einen Zellbereich FormulaText Argument angeben. Im Gegensatz dazu in Microsoft Excel 97 mithilfe der A1-Schreibweise FormulaText Argument an.

Abbildung 7. Felder, die zugeordnet sind die
SolverAdd -Argumente

 Figure 7. Fields that are associated with the SolverAdd arguments

Das Maximum_Profit -Makro, das ein Modell für das Produkt mit abnehmenden generiert. Dieses Makro führt die folgenden Funktionen Argumente:

  • SolverOK -Funktion richtet die Zielzelle Maximalwert und gibt die Zellen ändern.
  • Die SolverAdd -Funktion fügt die Einschränkung auf das Modell.
  • Solver eine Lösung gefunden, ohne dass die
    Das Dialogfeld Ergebnis .
  • SolverFinish -Funktion gibt die Ergebnisse im Arbeitsblatt.
Der folgende Code beschreibt das Maximum_Profit -Makro:
    Sub Maximum_Profit()
' Set up the parameters for the model.
' Determine the maximum value for the sum of profits in cell G14
' by changing the number of units to build in cells G9:I9.
Solverok setcell:=Range("G14"), maxminval:=1, _
bychange:=Range("G9:I9")

' Add the constraint for the model. The only constraint is that the
' number of parts used does not exceed the parts on hand--
' E3:E7<=B3:B7
SolverAdd CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$B$3:$B$7"

' Do not display the Solver Results dialog box.
SolverSolve UserFinish:=True

' Finish and keep the final results.
SolverFinish KeepFinal:=1

End Sub


Hinweis Verwenden Sie in Microsoft Excel 5.0 und 7.0 Z1S1-Notation, wenn Sie eine Zelle oder einen Zellbereich FormulaText Argument angeben. Im Gegensatz dazu in Microsoft Excel 97 mithilfe der A1-Schreibweise FormulaText Argument an.

Wenn Sie das Maximum_Profit -Makro ausführen, wird Microsoft Excel Solver 160 Fernseher, Stereoanlagen 200 und 80 Lautsprecher für maximalen Gewinn von 14.917 Dollar lösen.


zurück zum Anfang

Ändern und Löschen von Integritätsregeln

Integritätsregeln in Ihrem Modell können programmgesteuert geändert oder gelöscht werden. Einschränkungen werden durch ihre Argumente CellRef und Relation identifiziert.

Um eine vorhandene Einschränkung programmgesteuert zu ändern, verwenden Sie SolverChange -Funktion. Die Syntax für die SolverChange -Funktion lautet:
SolverChange (CellRef Beziehung FormulaText)
Beachten Sie, dass die Argumente für die Funktion SolverChange identisch mit denen, die mit SolverAdd -Funktion verwenden.

Wenn Sie die Einschränkung des Produktmix mit abnehmenden ändern möchten, verwenden Sie die SolverChange -Funktion. Beispielsweise derzeit die Einschränkung angegeben wird ist die Anzahl der verwendeten Teile kleiner oder gleich der Anzahl der Teile auf Seite (E3:E7 < = B3: B7). Möchten Sie diese Einschränkung ändern, um die Anzahl der verwendeten Teile kleiner oder gleich der Anzahl der Teile projizierten (Anzahl der Teile auf Lager) plus der Anzahl der bestellten Teile. Diese neue Einschränkung sieht wie E3:E7 < = D3:D7. Das folgende Makro ändern würde die vorhandene Einschränkung E3:E7 < = B3: B7, E3:E7 < = D3:D7 und eine Lösung zu lösen.

Der folgende Code beschreibt das Change_Constraint_and_Solve -Makro:
    Sub Change_Constraint_and_Solve()
' Change the constraint.
SolverChange CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$D$3:$D$7"

' Return the results and display the Solver Results dialog box.
SolverSolve UserFinish:=False

End Sub


Da Constraints CellRef und Relation Argumente identifiziert werden, können Sie nur FormulaText Argument für die Einschränkung SolverChange Funktion ändern. Falls CellRef und Relation Werte nicht mit eine bestehende Nebenbedingung übereinstimmen, müssen Sie die Einschränkung löschen und fügen Sie die geänderte Einschränkung. Verwenden Sie zum Löschen einer Beschränkung SolverDelete -Funktion. Die Syntax für die SolverDelete -Funktion lautet:


SolverDelete (CellRef Beziehung FormulaText)



Beachten Sie, dass die Argumente für die Funktion SolverDelete sind, die mit den Funktionen SolverChange und SolverAdd verwenden.


Das folgende Makro veranschaulicht das Löschen und Hinzufügen einer Einschränkung. In diesem Beispiel das Change_Constraint_and_Solve2 -Makro entfernt die Einschränkung E3:E7 < = B3: B7 aus der Produktmix mit abnehmender Rückgaben und fügt eine neue Einschränkung. Die neue Einschränkung ist nur eine Änderung der ursprünglichen Einschränkung, links und rechts der Einschränkung storniert.

Der folgende Code beschreibt das Change_Constraint_and_Solve2 -Makro:
    Sub Change_Constraint_and_Solve2()
' Reverse the left and right sides of the constraint...
' Delete the constraint E3:E7<=B3:B7 and add the
' constraint B3:B7>=E3:E7.
SolverDelete CellRef:=Range("E3:E7"), Relation:=1, _
FormulaText:="$B$3:$B$7"
SolverAdd CellRef:=Range("B3:B7"), Relation:=3, _
FormulaText:="$E$3:$E$7"

' Return the results and display the Solver Results dialog box.
SolverSolve UserFinish:=False

End Sub


Hinweis Verwenden Sie in Microsoft Excel 5.0 und 7.0 Z1S1-Notation, wenn Sie eine Zelle oder einen Zellbereich FormulaText Argument angeben. Im Gegensatz dazu in Microsoft Excel 97 mithilfe der A1-Schreibweise FormulaText Argument an.

zurück zum Anfang

Laden und Speichern von Modellen

Beim Speichern der Arbeitsmappe werden im Dialogfeld Solver-Parameter angegebenen letzten Parameter mit der Arbeitsmappe gespeichert. Daher sind die Parameter beim Öffnen der Arbeitsmappe beim letzten der Arbeitsmappe speichern identisch.

Sie können mehr als ein Problem für ein Arbeitsblatt definieren. Jedes Problem besteht der Zellen und die Nebenbedingungen in Solver-Parameter und den Dialogfeldern Optionen eingeben. Da das letzte Problem mit dem Arbeitsblatt gespeichert wird, verlieren Sie alle Probleme, sofern Sie nicht explizit speichern. Klicken sie im Dialogfeld Optionen Modell speichern . Auch die gespeicherten Parameter wiederherstellen möchten, klicken Sie auf Modell laden im Dialogfeld Optionen .

Solver-Modellen werden in einem Bereich von Zellen in einem Arbeitsblatt gespeichert. Die erste Zelle im Bereich enthält die Formel für die Zielzelle. Die zweite Zelle in dem Bereich enthält die Formel, die veränderbaren Zellen im Modell identifiziert. Die letzte Zelle im Bereich enthält ein Array, das die Optionen im Dialogfeld Optionen darstellt. Die Zellen der zweiten Zelle bis zur letzten Zelle enthalten Formeln, die die Nebenbedingungen für das Modell darstellen.

Abbildung 8 zeigt ein Modell für die Mitarbeiter planen. Angenommen Sie, Sie für eine kleine Hersteller arbeiten. Stundensatz des Mitarbeiters Lohn, die Anzahl der Stunden geplant werden und voraussichtliche Anzahl produzieren jeden Mitarbeiter in einer Stunde kann Tabelle. Ihr Ziel ist ein bestimmtes Kontingent für die Anzahl der gleichzeitig die Kosten der Arbeit produzierten Einheiten entsprechen.

Abbildung 8. Mitarbeiter Scheduling-Modus
l
 Figure 8. Employee Scheduling model

Zwei Faktoren (oder Einschränkungen) zu beachten sind, sind minimale/maximale Anzahl Stunden, die ein Mitarbeiter arbeiten kann und die Anzahl der Einheiten, die Sie erstellen möchten. Für eine angegebene Woche 3975 Einheiten werden müssen und jeder Mitarbeiter zwischen 30 und 45 Stunden würde Microsoft Excel Solver-Parameter in der folgenden Tabelle beschriebenen aussehen:

ParameterBereichBeschreibung
Zielzelle$D$12Kosten der Arbeit.
Veränderbare Zellen$C$2:$C$8Arbeitsstunden pro Mitarbeiter.
Nebenbedingungen$C$ 2: $C$ 8 < = 45Maximale Stunden pro Mitarbeiter ist 45.
$C$ 2: $C$ 8 > = 30Minimale Stunden pro Mitarbeiter ist 35.
$G 12 = 3975Anzahl der Einheiten ist 3975.


Ihre Ziele sind zu für optimale Arbeitskosten wöchentlich, jedes Modell wöchentlich speichern und wöchentliche Modell laden bei Bedarf.

In einem Makro können mit SolverSave und SolverLoad -Funktionen Microsoft Excel Solver-Parameter für ein Modell gespeichert und geladen werden. SolverSave und SolverLoad Funktionen haben folgende Syntax:

SolverSave (SaveArea)

SolverLoad (LoadArea)


SolverSave und SolverLoad Funktionen haben nur ein Argument SaveArea und LoadArea Argumente. Diese Argumente geben Sie einen Bereich in einem Arbeitsblatt, in dem die Informationen gespeichert.

Das folgende Makro New_Employee_Schedule veranschaulicht erstellen, lösen und Speichern eines Modells basierend auf Benutzereingaben. Der Benutzer wird aufgefordert, geben Sie das Datum des Modells, die Anzahl der Einheiten gefertigt und die minimale und maximale Anzahl der Stunden pro Mitarbeiter. Diese Daten werden dann zum Erstellen des Modells verwendet. Das Modell ist gelöst und dann mit den Benutzereingaben gespeichert.

Der folgende Code beschreibt das New_Employee_Schedule -Makro:
    Sub New_Employee_Schedule()    
' Prompt the user for the date of the model, the units to produce,
' and the maximum and minimum number of hours per employee.
ModelDate = Application.InputBox( _
Prompt:="Date of Model:", Type:=2)
Units = Application.InputBox( _
Prompt:="Projected Number of Units:", Type:=1)
MaxHrs = Application.InputBox( _
Prompt:="Maximum Number of Hours Per Employee:", Type:=1)
MinHrs = Application.InputBox( _
Prompt:="Minimum Number of Hours Per Employee:", Type:=1)

' Clear any previous Solver settings.
SolverReset

' Set the target cell, D12, to a minimum value by changing
' the range, C2:C8.
SolverOk SetCell:=Range("$D$12"), MaxMinVal:=2, _
ByChange:=Range("C2:C8")

' Add the constraint that number of hours worked <= MaxHrs.
SolverAdd CellRef:=Range("C2:C8"), Relation:=1, FormulaText:=MaxHrs

' Add the constraint that number of hours worked >=MinHrs.
SolverAdd CellRef:=Range("C2:C8"), Relation:=3, FormulaText:=MinHrs

' Add the constraint that number of units produced = Units.
SolverAdd CellRef:=Range("G12"), Relation:=2, FormulaText:=Units

' Solve the model and keep the final results.
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

' Save the input values for ModelDate, MaxHrs, MinHrs, and Units
' in columns I:L.
Set ModelRange = Range("I2:R2").CurrentRegion.Offset( _
Range("I2:R2").CurrentRegion.Rows.Count).Resize(1, 1)
ModelRange.Resize(1, 4) = Array("'" & Format(ModelDate, "m/d/yy"), _
Units, MaxHrs, MinHrs)

' Save the model parameters to the range M:R in the worksheet.
SolverSave SaveArea:=ModelRange.Offset(, 4).Resize(1, 6)

End Sub

Hinweis Verwenden Sie in Microsoft Excel 5.0 und 7.0 Z1S1-Notation, wenn Sie eine Zelle oder einen Zellbereich FormulaText Argument angeben. Im Gegensatz dazu in Microsoft Excel 97 mithilfe der A1-Schreibweise FormulaText Argument an.

Abbildung 9 zeigt, wie die gespeicherten Informationen auf dem Arbeitsblatt angezeigt wird.

Abbildung 9. Informationen, die durch das New_Employee_Schedule-Makro gespeichert

Figure 9. Model information that is saved by the New_Employee_Schedule macro

Das New_Employee_Schedule -Makro speichert jedes neues Modell im Arbeitsblatt. Das Makro Load_Employee_Schedule kann diese gespeicherten Modelle laden. Das Makro fordert den Benutzer für das Modell geladen und dann nach Spalte I für das Modell Datum. Wenn das Modell Datum gefunden wird, das Makro Load_Employee_Schedule lädt das entsprechende Modell löst und hält Endergebnis.

Der folgende Code beschreibt das New_Employee_Schedule-Makro:
    Sub Load_Employee_Schedule()    
' Prompt for the date of the model.
ModelDate = Application.InputBox( _
Prompt:="Date of Model to Load:", Type:=2)

' Locate the date in column I.
Set DateRange = Range("I2").CurrentRegion.Resize(, 1)
r = Application.Match(ModelDate, DateRange, 0)

If IsError(r) Then
' Display a message if the model date is not found
MsgBox "Cannot find a model with the date " & ModelDate
Else
' If the model date is found, load the model into Solver,
' solve the model, and keep the final results.
SolverLoad LoadArea:=DateRange.Offset(r - 1, 4).Resize(1, 6)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End If

End Sub

Das New_Employee_Schedule -Makro führt die SolverReset -Funktion. Die SolverReset -Funktion kann zum Löschen aller ausgewählte Zellen und Nebenbedingungen der
Im Dialogfeld Solver-Parameter und auf alle Einstellungen in SolverReset -Funktion hat keine Argumente.

zurück zum Anfang

So finden Sie weitere Informationen zu Microsoft Excel Solver

Die folgenden Ressourcen enthalten Informationen zur Verwendung des Microsoft Excel Solver-add-Ins.

  • Hilfe zu bestimmten Solver Nachrichten finden Sie unter
    Frontline-Systeme.
  • Hinweise zum Erstellen von lesbaren verwaltbare Modelle finden Sie unter
    Frontline-Systeme.
  • Weitere Solver-Grenzen für Integritätsregeln und finden Sie im folgenden Artikel der Microsoft Knowledge Base:

    75714 Solver Grenzwerte für Integritätsregeln

  • Beispiele, die das Microsoft Excel Solver-add-in in Microsoft Excel verwenden, finden Sie in der Beispieldatei mit.
  • Folgendes ist der Standardspeicherort der Beispieldatei, die mit Microsoft Excel 97 enthalten ist:
    \Program Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
  • Folgendes ist der Standardspeicherort der Microsoft Excel 7.0 enthaltene Beispieldatei:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • Folgendes ist der Standardspeicherort der Beispieldatei in Microsoft Excel 5.0 enthalten ist:
    \Excel\Examples\Solver\SolvSamp.xls
zurück zum Anfang

Wie Sie weitere Informationen zu den Algorithmen und Methoden, die von Microsoft Excel Solver verwendet werden

Microsoft Excel Solver verwendet den reduzierten GRG2 (Generalized Gradient) nichtlinearen Optimierung Code, der von Leon Lasdon, University of Texas in Austin, und Allan Waren, Cleveland State University entwickelt wurde.

Weitere Informationen über von Microsoft Excel Solver verwendeten Algorithmus klicken Sie auf die folgenden Artikelnummer der Microsoft Knowledge Base:

82890 Solver verwendet generalized reduziert



Linear und Integer Probleme verwenden die simplex Methode mit Grenzen auf den Variablen und die Branch-and-Bound Methode von John Watson und Dan Fylstra, Frontline Systems, Inc. implementiert Weitere Informationen zu von Solver verwendeten internen Lösungsprozessen erhalten:

Frontline Systems, Inc.P.O. Box 4288
Incline Village, NV 89450-4288
(702) 831-0300
Web site: http://www.frontsys.com
Electronic mail: info@frontsys.com



Auswahl des Microsoft Excel Solver-Programmcodes sind copyright 1990, 1991, 1992 und 1995 von Frontline Systems, Inc. Teile von optimale Methoden, Inc., copyright 1989

Hinweis Das in diesem Artikel beschriebenen Microsoft Excel Solver-add-in wird "wie besehen" bereitgestellt, und wir garantieren nicht, dass es in allen Situationen verwendet werden kann. Obwohl Microsoft-Supportmitarbeiter bei der Installation und Funktionalität dieses Add-Ins helfen, ändert sie nicht das Add-in dar.


Keine Garantie. Die Software bereitgestellt "als-ist" ohne Gewährleistung jeglicher Art und Verwendung dieser Software Produkt ist auf eigene Gefahr.


zurück zum Anfang
Eigenschaften

Artikelnummer: 843304 – Letzte Überarbeitung: 16.01.2017 – Revision: 1

Feedback