Wie Sie Visual Basic-Makros erstellen, mit Hilfe von Excel Solver in Excel 97

SPRACHE AUSWÄHLEN SPRACHE AUSWÄHLEN
Artikel-ID: 843304 - Produkte anzeigen, auf die sich dieser Artikel bezieht
Alles erweitern | Alles schließen

Auf dieser Seite

Zusammenfassung

Dieser Artikel beschreibt, wie Microsoft Excel Solver in Microsoft Excel 97 verwenden, um Microsoft Visual Basic-Makros zu erstellen. Microsoft Excel Solver ist eine Microsoft Excel-add-in.

Dieser Artikel enthält außerdem Informationen zum Erstellen von Makros, so entwerfen Sie ein Makro, 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 alle in 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 eine Microsoft Excel add-in Microsoft Excel Solver hilft Sie bestimmen, den optimalen Wert für eine Formel in einer bestimmten Zielzelle in einem Microsoft Excel-Arbeitsblatt. Microsoft Excel Solver passt die Werte anderer Zellen mit eine Formel mit der Zielzelle beziehen. Nachdem Sie eine Formel zu erstellen und definieren Sie einen Satz von Parametern oder Einschränkungen für die Variablen in der Gleichung versucht Microsoft Excel Solver verschiedene Lösungen zum Erreichen einer Diese Antwort ist, das alle Nebenbedingungen erfüllt. Microsoft Excel Solver verwendet die folgenden Elemente auf "Lösen Sie eine Formel:
  • Zielzelle - die Zielzelle ist das Ziel. Es ist die Zelle im Arbeitsblattmodell, das möglichst gering zu halten, maximiert oder auf einen bestimmten Wert festgelegt.
  • Die veränderbaren Zellen - veränderbare Zellen sind die Variablen Entscheidung. Diese Zellen beeinflusst der Wert für die Zielzelle. Diese Zellen sind geändert von Microsoft Excel Solver die optimale Lösung für die Zielzelle gefunden.
  • Einschränkungen - sind die Einschränkungen für den Inhalt der Zellen. Für Beispielsweise kann eine Zelle in einem Tabellenmodell auf ganzzahlige Werte beschränkt, während eine andere Zelle, die kleiner als der Wert einer bestimmten Beschränkungen unterliegen kann.
Sie können die Erstellung und die Manipulation von Microsoft Excel Solver-Modellen durch automatisieren. verwenden ein Microsoft Visual Basic for Applications (VBA)-Makro. Dieser Artikel beschreibt, wie Sie VBA-Makro-Sprache zu verwenden, die Microsoft Excel Solver-Funktionen in Microsoft Excel 97. In diesem Artikel wird davon ausgegangen, dass Sie die VBA-Programmiersprache und Microsoft Visual Basic-Editor für Microsoft Excel vertraut sind. 97. In den Beispielen, die in diesem Artikel verwendet werden, sind zum Download auf der folgenden Microsoft-Website zur Verfügung:
http://Download.Microsoft.com/Download/excel97win/solverex/1.0/WIN98Me/en-US/SolverEx.exe
Hinweis Sie können auch die Makros und die Beispiele, die in diesem beschrieben werden Artikel in Microsoft Excel-Versionen 5.0 und 7.0.

Gewusst wie: Verwenden Sie die Microsoft Excel Solver-Funktionen in einem VBA-Makro

Die Microsoft Excel Solver-add-in-Funktionen in einem VBA-Makro verwenden Sie muss das Add-in aus der Arbeitsmappe VBA-Projekt verweisen, die Makros enthält. Wenn Sie nicht das Microsoft Excel Solver-add-in verweisen, erhalten Sie die folgende Kompilierungsfehler, wenn Sie versuchen, das Makro auszuführen:
Kompilierungsfehler: Sub oder Function nicht definiert.
An Das Microsoft Excel Solver-add-in für Makros in Ihrer Arbeitsmappe verweisen Sie, verwenden Sie Folgendes Schritte:
  1. Öffnen Sie die Arbeitsmappe.
  2. Auf der Tools Menü, zeigen Sie aufMakro, und klicken Sie dann auf Visual Basic-Editor.
  3. Auf der Tools Menü, klicken Sie aufInformationsquellen.
  4. In der Verfügbare Verweise Liste, klicken Sie auf Wählen Sie die Solver.xls Kontrollkästchen Sie, und klicken Sie dann auf OK.

    Hinweis Wenn Solver.xls in nicht angezeigt wird, wird die Verfügbare Verweiseauf Durchsuchen. In der Hinzufügen Referenz Dialogfeld, zu suchen und wählen Sie die Datei Solver.xla und dann Klicken Sie auf Öffnen. Die Datei Solver.xla befindet sich in der Regel der C:\Program Files\Microsoft Office\Office\Library\Solver Unterordner.
Sie können nun verwenden Sie die Microsoft Excel Solver-Funktionen in einer VBA-Makro.

So entwerfen Sie ein VBA-Makro, das erstellt und löst ein einfaches Microsoft Excel Solver-Modell

Obwohl Microsoft Excel Solver viele Funktionen, die folgenden drei bietet Funktionen sind von grundlegender Bedeutung zu erstellen und zur Lösung eines Modells:
  • Die SolverOK -Funktion
  • Die Funktion SolverSolve
  • SolverFinish -Funktion

Die SolverOK-Funktion

Die SolverOK -Funktion definiert eine Microsoft Excel Solver-Grundmodell. Die SolverOK -Funktion ist im Allgemeinen die erste Funktion, mit denen Sie das Microsoft Excel Solver-Modell zu erstellen. Die SolverOK -Funktion entspricht dem Klicken auf Solver auf der ToolsMenü und dann angeben, die Optionen in der Solver-Parameterim Dialogfeld. Der folgende Code ist die Syntax für die SolverOK -Funktion:
SolverOK (Argumentwerte SetCell, MaxMinVal, ValueOf, ByChange)
Die folgende Informationen beschreibt die Syntax für die SolverOK -Funktion:
  • Argumentwerte SetCell definiert die Zielzelle.
  • MaxMinVal entspricht, ob Sie die Zielzelle lösen möchten für einen maximalen Wert (1), einen Mindestwert (2) oder einen bestimmten Wert (3).
  • ValueOf gibt den Wert mit dem die Zielzelle verglichen wird. Wenn Sie Legen Sie MaxMinVal 3, 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 Zellbereich, die geändert werden.
Abbildung 1 ordnet die Argumente für die SolverOK -Funktion mit der Parameter in der Solver-Parameter im Dialogfeld.

Abbildung 1. Parameter, die die SolverOK zugeordnet sind Argumente

Bild minimierenBild vergrößern
 Abbildung 1. Parameter, die verknüpft sind

		  mit den Argumenten SolverOK


Die Funktion SolverSolve

Die Funktion SolverSolve löst das Modell mit den Parametern dass Sie mit der Funktion SolverOK angegeben. Ausführung der Funktion SolverSolve entspricht mit dem Klicken auf Lösen Klicken Sie im Dialogfeld Solver-Parameter im Dialogfeld. Der folgende Code ist die Syntax für die Funktion SolverSolve :
SolverSolve (UserFinish, ShowRef)
Die folgende Informationen beschreibt die Syntax für die Funktion SolverSolve :
  • UserFinish gibt an, ob den Benutzer wieder lösen soll die Modell.

    Die Ergebnisse zurückgeben, ohne dass die Solver Ergebnisse Dialog box, dieses Argument auf TRUE festgelegt. Die Ergebnisse zurückgegeben und zeigen die Ergebnis Dialog box, legen Sie dieses Argument auf FALSE
  • ShowRef identifiziert das Makro, das aufgerufen wird, wenn Microsoft Excel Solver gibt ein temporäre Lösung.

    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 den Ergebnissen führen, und welche Art von Bericht zu erstellen, nachdem der Lösungsvorgang abgeschlossen ist. Der folgende Code ist die Syntax für die SolverFinish -Funktion:
SolverFinish (KeepFinal, ReportArray)
Die folgende Informationen beschreibt die Syntax für die SolverFinish -Funktion:
  • KeepFinal gibt an, was mit den Endergebnissen zu tun. Ist KeepFinal auf 1 gesetzt, werden die endgültigen Lösungswerte in veränderbaren Zellen gehalten, die Werte ersetzt werden. Ist KeepFinal auf 2 gesetzt, werden die endgültigen Lösungswerte verworfen, und der ehemaligen Werte werden wiederhergestellt.
  • ReportArray gibt ein Array, womit die Art des Berichts Microsoft Excel wird erstellt, wenn die Lösung erreicht ist. Wenn ReportArray auf 1 festgelegt ist, wird Microsoft Excel ein Antwortbericht erstellt. Wenn auf festgelegt 2, Microsoft Excel erstellt einen Bericht Empfindlichkeit und 3 Microsoft fest, wenn Excel erstellt einen Bericht Grenzen. Weitere Informationen zu diesen Berichten finden Sie unter "Gewusst wie: Generieren von Berichten für Lösungen"Abschnitt.
Abbildung 2. Microsoft Excel Solver-Ergebnisse-Optionen, die verknüpft sind SolverFinish Argumente

Bild minimierenBild vergrößern
 Abbildung 2. Optionen für Ergebnisse

		  SolverFinish Argumente zugeordnet sind


Dieser Artikel beschreibt, wie ein einfaches Microsoft Excel Solver-Modell interaktiv zu erstellen. Der erste Schritt besteht darin das Arbeitsblatt für das Modell zu erstellen. Das Arbeitsblatt wird einige Datenzellen enthalten. und mindestens eine Zelle, die eine Formel enthält. Mit dieser Formel abhängt, auf der anderen Zellen im Arbeitsblatt. Nachdem Sie das Arbeitsblatt eingerichtet haben, klicken Sie auf Solver auf der Tools Menü. In derSolver-Parameter Dialogfeld im Feld, geben Sie die Zielzelle, den Wert, Sie sind für den Bereich der Zellen, die geändert werden, lösen und die Einschränkungen. Klicken Sie auf Lösen um den Lösungsprozess zu starten. Nachdem Microsoft Excel Solver eine Lösung gefunden hat, werden die Ergebnisse im Arbeitsblatt und die Microsoft Excel Solver Zeigt ein Meldungsfeld, das Sie auffordert, wenn Sie die endgültigen Ergebnisse behalten möchten oder Wenn Sie sie verwerfen möchten. Wenn Sie eine der folgenden Optionen klicken, schließt Microsoft Excel Solver.

Abbildung 3 veranschaulicht ein einfaches Modell, das Sie mithilfe dieser Schritte erstellen können.

Abbildung 3. Ein einfaches Modell: der Quadratwurzel Modell

Bild minimierenBild vergrößern
 Abbildung 3. A

		  einfaches Modell: der Quadratwurzel Modell


In diesem Beispiel ändern Zelle A1 angezeigt, die die Formel enthält, = A1 ^ 2 auf einen Wert, mit denen Zelle A2 gleich einem Wert von 50. Mit anderen Worten, finden Sie die Quadratwurzel 50. Es gibt keine Einschränkungen in der Quadratwurzel-Modell. Das Find_Square_Root -Makro führt die folgenden Aufgaben:
  • Es richtet ein Modell, das den Wert der Zelle A2 nach einem Wert von 50 durch lösen ändern den Wert der Zelle A1.
  • Es löst das Modell.
  • Es speichert die endgültigen Ergebnisse in das Tabellenblatt ohne Anzeigen der Ergebnis im Dialogfeld.
Dieses einfache Makro erstellt ein Microsoft Excel Solver-Modell und gelöst er ohne Eingriff des Benutzers. 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 Find_Square_Root2 -Makro ist eine modifizierte Version des Makros Find_Square_Root . Wenn Sie die InputBox -Funktion, fordert das Find_Square_Root2 -Makro Sie für der Wert, den Sie für die Zielzelle lösen möchten. Nachdem Sie einen Wert eingeben, das Find_Square_Root2 -Makro legt dieser Parameter als Wert des ArgumentsValueofSolverOK, löst das Problem, speichert die Ergebnisse in der Variablen Quadratwurzel, verwirft die Lösung und der Wert im Arbeitsblatt wiederhergestellt in den ursprünglichen Zustand. Im Grunde zeigt das Find_Square_Root2 -Makro, wie Sie die Ergebnisse in einem oder mehreren sparen können Variablen und dann das Ändern von auf ihren ursprünglichen Wert Zellen 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

Gewusst wie: Generieren von Berichten für Lösungen

Microsoft Excel Solver bietet verschiedene Arten von Berichten, die beschreiben, wie die Ergebnisse geändert und wie nah die Einschränkungen auf ihre kritischen Werte kam. Jeder Bericht wird in einem separaten Arbeitsblatt in Ihrer Arbeitsmappe eingefügt. Diese folgenden sind die Typen von Berichten Microsoft Excel Solver bietet:
  • Antwortbericht - die Antwortbericht Listet die Zielzelle und die veränderbaren Zellen mit ihren entsprechenden Ausgangs- und Endwerten, Einschränkungen und Informationen zu den Nebenbedingungen.
  • Sensitivitätsbericht - der Empfindlichkeit Report enthält Informationen wie empfindlich die Lösung besteht darin, kleine Änderungen in der Formel für die Zielzelle.
  • Grenzenwertbericht - die Grenzwertbericht Listet die Zielzelle und die veränderbaren Zellen mit ihren jeweiligen Werten, die oberen und unteren Grenzwerten und das Ziel Werte.
Geben Sie zum Erstellen von Berichten für die Modelle ein Array von Werten für das Argument ReportArraySolverFinish -Funktion. Weitere Informationen zu Das Argument ReportArray finden Sie in der "SolverFinish (KeepFinal, ReportArray)"Abschnitt. Z. B. einen Grenzwerte für Bericht das Modell, das das Find_Square_Root2 -Makro erstellt und behoben werden kann, ändern Sie die SolverFinish -Funktion in der Makro so ähnlich wie im folgenden Beispielcode aussehen:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
Um mehrere Berichte zu erstellen, ändern Sie die SolverFinish -Funktion Damit sie den folgenden Beispielcode ähnelt:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)

Gewusst wie: Verwenden Sie die Microsoft-ExcelSolver-Funktionen in einer Schleife Makro

In vielen Situationen ist es eine gute Idee, Microsoft Excel Solver lösen die Zielzelle für mehrere Werte. Hierzu können Sie in der Regel mit einer der die Schleifenstrukturen, die mit VBA verfügbar sind.

Das Create_Square_Root_Table -Makro veranschaulicht, wie Microsoft Excel Solver in einer Schleife Makro funktioniert. Create_Square_Root_Table Makro erstellt eine Tabelle in einem neuen Arbeitsblatt. Er fügt die Zahlen eine bis zehn und die entsprechenden Quadratwurzel jede Zahl. Das Create_Square_Root_Table -Makro wird die Tabelle erstellt. mithilfe einer For -Schleife zum Durchlaufen der Zahlen 1 bis 10 und lösen die Zielzelle im Modell Quadratwurzel für einen Wert, die Anzahl entspricht, der Iteration. 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 die in Abbildung 4 dargestellte Tabelle generiert.

Abbildung 4. Durch das Create_Square_Root_Table -Makro generierte

Bild minimierenBild vergrößern
Abbildung 4. Ausgabe, die von generiert die

		  Create Square Root Table-Makro


Zum Arbeiten mit Einschränkungen

Eine Einschränkung ist eine Einschränkung für den Inhalt eines oder mehrerer Zellen. Ein Modell kann eine oder mehrere Einschränkungen haben. Der Randbedingungssatz ist eine Festlegen von Ungleichheiten oder eine Gruppe von Gleichheiten, die bestimmte Kombinationen von Werten zu entfernen für die Entscheidung Variablen aus der Projektmappe. Beispielsweise kann eine Einschränkung erfordern Sie, dass eine Zelle größer als 0 (null) sein und enthalten nur, dass eine andere Zelle Ein ganzzahliger Wert.

Das Quadratwurzel-Modell, das bis zu diesem Zeitpunkt besprochen ist ein einfaches Modell, das keine Einschränkungen enthält. Abbildung 5 zeigt ein Modell, das Einschränkungen verwendet. Der Zweck dieses Modells ist die optimale gefunden Kombination von Produkten für maximale Gewinn.

Abbildung 5. Produkt-mix mit abnehmender Gewinnspanne

Bild minimierenBild vergrößern
Abbildung 5. Produkt-Mix mit

		  Sinkende Gewinnspanne


Wenn beispielsweise ein Unternehmen TV-Geräte produziert, Stereoanlagen, Lautsprechern, und es verwendet eine gemeinsame Teile Bestandsaufnahme der Netzteile, Lautsprecher mehr, und So weiter. Die Teile sind in begrenzten Mengen. Ihr Ziel ist Bestimmen der rentabelsten Mischung der Produkte zu erstellen. Der Gewinn pro Einheit mit Volume verringert, da zusätzliche Anreizen erforderlich sind, laden die Distribution Channel. Der rückläufige Exponent ist 0.9. Dieser Exponent ist verwendet, um den Gewinn nach Produkt im Bereich von G11:I11 zu berechnen.

Ihre Ziel ist den maximalen Gewinn (Zelle G14) suchen. Die Werte, die Sie der maximale Gewinn sind die Anzahl der Einheiten, die Sie erstellen, ändern. Die Bereich G9:G11 stellt die veränderbaren Zellen in diesem Modell dar. Die einzige Einschränkung besteht darin, dass die Anzahl der Teile, die Sie verwenden die Anzahl der Teile Geschwindigkeitsbegrenzer müssen Sie am Lager. Mit Hilfe von Microsoft Excel Solver wird diese Einschränkung als E3:E7 angezeigt<=B3:B7. if="" you="" were="" to="" build="" this="" microsoft="" excel="" solver="" model="" interactively,="" the="" microsoft="" excel="" solver="" parameters="" would="" look="" similar="" to="" those="" that="" are="" in="" figure="" 6.=""></=B3:B7.>

Abbildung 6. Microsoft Excel Solver-Parameter für die Produkt-Mix mit Gewinnspanne vermindern Modell

Bild minimierenBild vergrößern
Abbildung 6. Microsoft Excel Solver-Parameter für

		  die Produkt-Mix mit Gewinnspanne vermindern Modell


Erstellen und die Produkt-Mix mit Gewinnspanne vermindern Modell zu lösen, verwenden Sie ein neue Funktion, die SolverAdd -Funktion, zusätzlich zu den Microsoft Excel Solver VBA-Funktionen, die Waren weiter oben beschrieben. Die SolverAdd -Funktion fügt die Einschränkung auf das Modell. Ausführung SolverAdd -Funktion entspricht dem Klicken auf die Hinzufügen die Schaltfläche in der Solver-Parameter im Dialogfeld. Die SolverAdd -Funktion hat die folgende Syntax:
SolverAdd (CellRef, Beziehung, FormulaText)
Die folgende Informationen beschreibt die Syntax für die SolverAdd -Funktion:
  • CellRef verweist auf eine oder mehrere Zellen, die der linken Seite des bilden die Einschränkung.
  • Beziehung ist die arithmetische Beziehung zwischen der linken und rechten Seiten der eine Einschränkung.
  • Beziehung kann einen Wert zwischen 1 und 5 wie im folgenden Beispiel:
    • Der Wert 1 ist kleiner als oder gleich)<>
    • Vaue 2 ist gleich (=).
    • Der Wert 3 ist größer als oder gleich (> =).
    • Der Wert 4 ist eine ganze Zahl.
    • Der Wert 5 ist die Binärdatei (ein Wert von NULL oder eins).
  • FormulaText verweist auf eine oder mehrere Zellen, die rechts neben der Constraint.* *
** Wenn Sie einen Bereich von Zellen für das Argument FormulaTextSolverAdd -Funktion angeben, beachten Sie, ob der Verweis ist relativ oder absolut. Im Allgemeinen müssen Sie einen absoluten angeben. Referenz für das Argument FormulaText . Jedoch Wenn Sie relative Verweise für das Argument FormulaText angeben, erkennen Sie, dass der Verweis relativ zu die Zielzelle und nicht die aktive Zelle.

Hinweis Verwenden Sie in Microsoft Excel, Versionen 5.0 und 7.0 die Z1S1-notation Wenn Sie eine Zelle oder einen Zellbereich mit dem Argument FormulaText angeben. Im Gegensatz dazu in Microsoft Excel 97 verwenden Sie die A1-Schreibweise Schreibweise für das Argument FormulaText angeben.

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

Bild minimierenBild vergrößern
 Abbildung 7. Felder, die zugeordnet sind

		  die SolverAdd-Argumente


Das Maximum_Profit -Makro, das ein Modell für die Produkt-Mix mit abnehmender generiert werden. Modell gibt. Dieses Makro führt die folgenden Funktionen oder Argumente:

  • Die SolverOK -Funktion richtet die Zielzelle für einen maximalen Wert und Gibt die Zellen zu ändern.
  • Die SolverAdd -Funktion fügt die Einschränkung auf das Modell.
  • Die Funktion SolverSolve findet eine Lösung ohne Anzeige derErgebnis im Dialogfeld.
  • Die SolverFinish -Funktion gibt die endgültigen Ergebnisse auf dem Arbeitsblatt.
Der folgende Code beschreibt die für 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, Versionen 5.0 und 7.0 die Z1S1-notation Wenn Sie eine Zelle oder einen Zellbereich mit dem Argument FormulaText angeben. Im Gegensatz dazu in Microsoft Excel 97 verwenden Sie die A1-Schreibweise Schreibweise für das Argument FormulaText angeben.

Wenn Sie das Maximum_Profit -Makro ausführen, findet Microsoft Excel Solver eine Lösung erstellen 160 Fernsehgeräte, 200 Stereoanlagen und 80 Lautsprecher für einen maximalen Gewinn von 14,917 US-Dollar.

Wie ändern und Löschen von Einschränkungen

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

Zum programmgesteuerten Ändern einer vorhandenen Einschränkung, verwenden Sie die SolverChange -Funktion. Der folgende Code ist die Syntax für die SolverChange Funktion:
SolverChange (CellRef, Beziehung, FormulaText)
Beachten Sie, dass die Argumente für die SolverChange -Funktion identisch mit denen, die Sie mit der SolverAdd -Funktion verwenden.

Wenn Sie möchten So ändern Sie die Einschränkung des Produktmix mit abnehmender Rückgaben Modell Sie würde die SolverChange -Funktion verwenden. Z. B. aktuell die Einschränkung, wird angegeben, dass die Anzahl der verwendeten Teile ist kleiner als oder gleich der Anzahl der Teile auf Lager (E3:E7<= b3:b7).="" if="" you="" want="" to="" change="" this="" constraint="" so="" that="" the="" number="" of="" parts="" used="" is="" less="" than="" or="" equal="" to="" the="" number="" of="" parts="" projected="" (number="" of="" parts="" on="" hand="" plus="" number="" of="" parts="" ordered).="" this="" new="" constraint="" would="" look="" like="" e3:e7=""></=><= d3:d7.="" the="" following="" macro="" would="" change="" the="" existing="" constraint=""></=><=B3:B7 to="" e3:e7=""></=B3:B7><= d3:d7="" and="" solve="" for="" a="" solution.=""></=>

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 Einschränkungen durch die CellRef und Relation Argumente identifiziert werden, können Sie das FormulaText Argument für die Einschränkung nur ändern, mit die SolverChange -Funktion. Falls CellRef und Relation -Werte nicht mit eine bestehende Nebenbedingung übereinstimmen, müssen Sie löschen die Einschränkung und fügen Sie die geänderte Einschränkung hinzu. Verwenden Sie zum Löschen einer Einschränkung die Funktionen SolverDelete -Funktion. Der folgende Code ist die Syntax für die SolverDelete -Funktion:

SolverDelete (CellRef, Beziehung, FormulaText)

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

Das folgende Makro veranschaulicht, wie löschen und Hinzufügen einer Einschränkung. In diesem Beispiel entfernt das Change_Constraint_and_Solve2 -Makro die Einschränkung E3:E7<=B3:B7 from="" the="" product="" mix="" with="" diminishing="" returns="" model="" and="" adds="" a="" new="" constraint.="" the="" new="" constraint="" is="" just="" a="" modification="" of="" the="" original="" constraint,="" where="" the="" left="" and="" right="" sides="" of="" the="" constraint="" are="" reversed.=""></=B3:B7>

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, Versionen 5.0 und 7.0 die Z1S1-notation Wenn Sie eine Zelle oder einen Zellbereich mit dem Argument FormulaText angeben. Im Gegensatz dazu in Microsoft Excel 97 verwenden Sie die A1-Schreibweise Schreibweise für das Argument FormulaText angeben.

Das Laden und speichern Sie Ihre Modelle

Beim Speichern der Arbeitsmappe, die letzten Parameter, die Sie gemäß der Solver-Parameter im Dialogfeld werden mit gespeichert. die Arbeitsmappe. Wenn Sie die Arbeitsmappe öffnen, die Parameter werden daher die gleiche als wenn Sie die Arbeitsmappe zuletzt gespeichert.

Sie können definieren mehr als ein Problem für ein Arbeitsblatt. Jedes Problem besteht aus Zellen und Einschränkungen, die Eingabe in die Solver-Parameter und die Solver-Optionen Dialogfelder. Da nur das letzte Problem mit der gespeichert wird das Arbeitsblatt andere Probleme verloren, wenn Sie explizit speichern Diese. Um diese zu speichern, klicken Sie auf Modell speichern Klicken Sie im Dialogfeld Solver Optionen im Dialogfeld. Auf ähnliche Weise, wenn Sie wiederherstellen möchten die zuvor Klicken Sie auf gespeicherte Parameter Modell laden Klicken Sie im Dialogfeld Solver Optionen im Dialogfeld.

Solver-Modellen werden in einem Bereich von gespeichert. Zellen in einem Arbeitsblatt. Die erste Zelle im Bereich enthält die Formel für die Zielzelle. Die zweite Zelle im Bereich enthält die Formel, die identifiziert die veränderbaren Zellen im Modell. Die letzte Zelle des Bereichs enthält ein array darstellender die Optionen der Solver-Optionen Dialogfeld im Feld. Die Formeln enthalten, die die Zellen zwischen der zweiten Zelle und die letzte Zelle die Einschränkungen im Modell darstellen.

Abbildung 8 zeigt ein Modell für Mitarbeiter planen. Genommen Sie an, Sie für ein kleines arbeiten Hersteller. Diese Tabelle zeigt jeden Mitarbeiter-Stundensatz von Zahlen, die Anzahl Sie werden von Stunden geplant und eine voraussichtliche Anzahl der Einheiten jeder Mitarbeiter kann in einer Stunde erzeugt. Ihr Ziel ist es, ein bestimmtes Kontingent für die Anzahl der zu erfüllen Einheiten gefertigt bei gleichzeitiger Minimierung der Kosten der Arbeit.

Abbildung 8. Mitarbeiter Terminplanungsart
l
Bild minimierenBild vergrößern
 Abbildung 8. Mitarbeiter planen

		  Modell


Zwei zusätzliche Faktoren (oder Einschränkungen), die absolut erforderlich Betrachten Sie die minimale/maximale Anzahl der Stunden, die alle einen Mitarbeiter kann arbeiten sind und die Anzahl der Einheiten, die Sie erzeugen möchten. Wenn Sie eine angegebene Woche müssen Um 3975 produzieren Einheiten und möchten jedem Mitarbeiter zwischen 30 und 45 funktioniert Stunden, würde die Microsoft Excel Solver-Parameter im folgenden beschriebenen Verfahren ähnlich aussehen Tabelle:

Tabelle minimierenTabelle vergrößern
ParameterZellbereichBeschreibung
Zielzelle $D$ 12 Kosten der Arbeit.
Veränderbare Zellen $C$ 2:$ C$ 8 Arbeitsstunden pro der Mitarbeiter.
Einschränkungen $C$ 2:$ C$ 8<=></=> Maximum Stunden pro Mitarbeiter ist 45.
$C$ 2:$ C$ 8 > = 30 Minimale Stunden pro Mitarbeiter ist 35.
$G$ 12 = 3975 Anzahl der Einheiten ist 3975.


Ihre Ziele sind für eine optimale Lohnkosten auf lösen ein Jedes Modell wöchentlich zu speichern und laden alle wöchentlich können wöchentlich Modell, wenn Sie ihn benötigen.

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

SolverSave (SaveArea)

SolverLoad (LoadArea)

SolverSave und SolverLoad -Funktionen haben jeweils nur ein Argument, SaveArea und LoadArea Argumente. Diese Argumente geben Sie einen Bereich in einem Arbeitsblatt Wo ist die Modellinformationen gespeichert.

Das folgende New_Employee_Schedule -Makro veranschaulicht, wie erstellen, zu lösen und ein basierendes Modell speichern auf der Benutzereingabe. Der Benutzer wird aufgefordert, geben Sie das Datum des Modells, die Anzahl der Einheiten zu produzieren, und die minimale und maximale Anzahl der Stunden pro Mitarbeiter. Diese Daten werden dann zum Erstellen des Modells verwendet. Das Modell wird 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, Versionen 5.0 und 7.0 die Z1S1-notation Wenn Sie eine Zelle oder einen Zellbereich mit dem Argument FormulaText angeben. Im Gegensatz dazu in Microsoft Excel 97 verwenden Sie die A1-Schreibweise Schreibweise für das Argument FormulaText angeben.

Abbildung 9 zeigt, wie das Modell gespeichert Informationen auf dem Arbeitsblatt angezeigt.

Abbildung 9. Modellinformationen wird durch das New_Employee_Schedule-Makro gespeichert.

Bild minimierenBild vergrößern
Abbildung 9. Modellinformationen, die gespeichert wird

		  durch das New Employee Schedule-Makro


Das New_Employee_Schedule -Makro speichert jedes neues Modell in das Arbeitsblatt. Das Makro Load_Employee_Schedule kann eine dieser gespeicherten Modelle geladen werden. Das Makro fordert die Benutzer für das Modell zu laden und anschließend sucht Spalte I für das Modell Datum. If das Modell Datum gefunden wird, das Makro Load_Employee_Schedule lädt das entsprechende Modell, löst es, und dann hält die endgültigen Ergebnisse.

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 verwendet werden, so löschen Sie alle Zellmarkierungen und Nebenbedingungen in der Solver-Parameter Das Dialogfeld und alle Einstellungen zurücksetzen Die SolverReset -Funktion hat keine Argumente.

So finden Weitere Informationen zu Microsoft Excel Solver

Die folgenden Ressourcen bieten Informationen zur Verwendung von Microsoft Excel Solver Add-in.

  • Hilfe bei bestimmten Solver Nachrichten finden Sie unterFrontline Systeme.
  • Hinweise zum Erstellen von lesbaren verwaltbare Modelle finden Sie unterFrontline Systeme.
  • Weitere Informationen zu den Gleichungslöser für Einschränkungen beschränkt, und klicken Sie auf die folgenden Artikelnummer der Microsoft Knowledge Base:
    75714Solver Grenzwerte für Einschränkungen
  • Für mehrere Beispiele, in denen das Microsoft Excel Solver-add-in in Microsoft Excel, finden Sie in der Beispieldatei mit.
  • Die folgenden ist der Standardspeicherort der Beispieldatei, die in enthalten ist Microsoft Excel 97:
    \Programme\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
  • Die folgenden ist der Standardspeicherort der Beispieldatei, die in enthalten ist Microsoft Excel 7.0:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • Die folgenden ist der Standardspeicherort der Beispieldatei, die in enthalten ist Microsoft Excel 5.0:
    \Excel\Examples\Solver\SolvSamp.xls

Wie erfahren mehr über den Algorithmus und Methoden, die von Microsoft Excel Solver verwendet werden

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

Weitere Informationen über den Algorithmus von Microsoft Excel Solver verwendet klicken Sie auf die folgende Artikelnummer klicken, um den Artikel in der Microsoft Knowledge Base anzuzeigen:
82890Solver verwendet generalized reduzierte


Linearen und ganzzahligen Problemen verwenden die simplex-Methode mit Grenzen auf die Variablen und die Branch-and-Bound-Methode implementiert, durch John Watson und Dan Fylstra, Frontline Systems, Inc. Weitere Informationen zu von Solver, Kontakt verwendeten internen Lösungsprozessen:

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 von Microsoft Excel Solver Programmcode sind copyright 1990, 1991, 1992 und 1995 von Frontline Systems, Inc. Teile werden von optimale Methoden, Inc. copyright 1989.

Hinweis Die Wird Microsoft Excel Solver-add-in, das in diesem Artikel beschrieben wird "wie besehen" bereitgestellt, und wir nicht garantieren Sie, dass sie in allen Situationen verwendet werden kann. Zwar Microsoft unterstützt Spezialisten können bei der Installation und die vorhandene Funktionalität unterstützen. Add-in können sie nicht das Add-in um neue Funktionen bieten ändern.

Keine Garantie. Die Software wird bereitgestellt "als-ist," ohne Gewährleistung jeglicher Art, und die Verwendung dieses Softwareprodukts erfolgt auf Ihr eigenes Risiko.

Eigenschaften

Artikel-ID: 843304 - Geändert am: Donnerstag, 21. März 2013 - Version: 3.0
Die Informationen in diesem Artikel beziehen sich auf:
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbhowto kbmacroexample kberrmsg kbaddin kbvba kbprogramming kbinfo kbmt KB843304 KbMtde
Maschinell übersetzter Artikel
Wichtig: Dieser Artikel wurde maschinell und nicht von einem Menschen übersetzt. Die Microsoft Knowledge Base ist sehr umfangreich und ihre Inhalte werden ständig ergänzt beziehungsweise überarbeitet. Um Ihnen dennoch alle Inhalte auf Deutsch anbieten zu können, werden viele Artikel nicht von Menschen, sondern von Übersetzungsprogrammen übersetzt, die kontinuierlich optimiert werden. Doch noch sind maschinell übersetzte Texte in der Regel nicht perfekt, insbesondere hinsichtlich Grammatik und des Einsatzes von Fremdwörtern sowie Fachbegriffen. Microsoft übernimmt keine Gewähr für die sprachliche Qualität oder die technische Richtigkeit der Übersetzungen und ist nicht für Probleme haftbar, die direkt oder indirekt durch Übersetzungsfehler oder die Verwendung der übersetzten Inhalte durch Kunden entstehen könnten.
Den englischen Originalartikel können Sie über folgenden Link abrufen: 843304
Microsoft stellt Ihnen die in der Knowledge Base angebotenen Artikel und Informationen als Service-Leistung zur Verfügung. Microsoft übernimmt keinerlei Gewährleistung dafür, dass die angebotenen Artikel und Informationen auch in Ihrer Einsatzumgebung die erwünschten Ergebnisse erzielen. Die Entscheidung darüber, ob und in welcher Form Sie die angebotenen Artikel und Informationen nutzen, liegt daher allein bei Ihnen. Mit Ausnahme der gesetzlichen Haftung für Vorsatz ist jede Haftung von Microsoft im Zusammenhang mit Ihrer Nutzung dieser Artikel oder Informationen ausgeschlossen.
Disclaimer zu nicht mehr gepflegten KB-Inhalten
Dieser Artikel wurde für Produkte verfasst, für die Microsoft keinen Support mehr anbietet. Der Artikel wird deshalb in der vorliegenden Form bereitgestellt und nicht mehr weiter aktualisiert.

Ihr Feedback an uns

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com