????? ????? ????? ????? Visual Basic ???????? Solver Excel ?? Excel 97

?????? ????????? ?????? ?????????
???? ???????: 843304 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

???? ??? ??????? ????? ??????? Microsoft Excel Solver ?? Microsoft Excel 97 ?????? ????? ????? Microsoft Visual Basic. Microsoft Excel Solver ????? ???????? Microsoft Excel.

???????? ??? ???? ????? ??? ??????? ??? ??????? ??? ????? ????? ????? ????? ????? ????? ????? ?????? ????? ?? ???? ?????. ?????? ??? ??????? ????? ?????????? ????????? ????????? ?? ??? Microsoft Excel Solver. ???? ??????? ??????? ???? ???????? ???????? ?? ???????.

????? ???

????? ??? ??????? ??? ??????? ??? Microsoft Excel Solver.

??????? ????

??? Solver Microsoft Excel

Microsoft Excel Solver Solver Excel Microsoft ????? ???? ????? Microsoft Excel ?????? ?????? ?????? ?????? ?????? ?? ???? ??? ????? ?? ???? ??? Microsoft Excel. ???? Microsoft Excel Solver ?????? ????? ?????? ??????? ???????? ??????? ????? ???????? ??????. ??? ????? ?????? ?????? ?????? ?? ?????? ?? ???? ????????? ?? ???????? ????? Microsoft Excel Solver ???? ???????? ??? ??? ????? ???? ???? ???? ??????. ?????? Microsoft Excel Solver ??????? ??????? ??? "??" ??????:
  • ?? ?????? ????? - ?????? ????? ?????. ??? ?????? ???????? ?? ????? ???? ???? ???? ????? ????? ?? ????? ??? ???? ?????.
  • ??????? ???????? - ?? ??????? ????? ??????? ??????. ???? ??? ??????? ??? ???? ?????? ?????. ??? ????? ??? ??????? ?? ??? Microsoft Excel Solver ?????? ??? ???? ?????? ?????? ?????.
  • ???? - ??? ???? ???? ??? ????????? ???????. ??? ???? ??????? ?? ???? ???? ????? ?? ????? ???? ??? ???? ??? ????? ????? ????? ???? ???? ?? ???? ????? ??? ????? ??? ?? ???? ?????.
???? ????? ??????? "?" ?????? ?????? Microsoft Excel Solver ???????? Microsoft Visual Basic ??????? Applications (VBA). ???? ??? ??????? ????? ??????? ??? ??????? VBA ??? ??????? ????? Microsoft Excel Solver ?? Microsoft Excel 97. ????? ??? ??????? ??? ????? ????? VBA ? ???? Microsoft Visual Basic Microsoft Excel 97. ????? ??????? ????????? ?? ??? ??????? ??????? ?? ???? Microsoft ?????? ??? ?????:
http://download.microsoft.com/download/excel97win/solverex/1.0/WIN98Me/EN-US/SolverEx.exe
?????? ????? ????? ??????? ????? ??????? ?????? ??????? ?? ??? ??????? ?? ??????? Microsoft Excel 5.0 ? 7.0.

????? ??????? ????? Solver Excel Microsoft ?? ????? VBA

???????? ????? ??????? ???????? Solver Excel Microsoft ?? ????? VBA ??? ?? ???? ??????? ???????? ?? ????? VBA ?? ?????? ???? ????? ??? ????? ???????. ??? ?? ???? ??????? ???????? Solver Excel Microsoft ? ?????? ??? ????? ??????? ??? ?????? ????? ???????:
??? ?? ??????? ???????: ?????? ?? ?????? ??? ??????.
?????? ??? Microsoft Excel ??????? ???????? Solver ?????? ??????? ?? ?????? ????? ??, ?????? ??????? ???????:
  1. ???? ?????? ????? ??.
  2. ?? ??????? ?????? ??? ??? ????? ? ?? ???? ??? ???? Visual Basic.
  3. ?? ??????? ????? ? ???? "?????".
  4. ?? ????? ??????? ??????? ???? ?????? ???? ???????? Solver.xls ?? ???? ??? ?????.

    ?????? ??? ?? ????? Solver.xls ?? ????? ??????? ??????? ???? ??? ???????. ?? ???? ?????? ??????? ????? ???? ??? Solver.xla ? ??????? ?? ???? ??? ???. ?? ?????? ??? ????? Solver.xla ???? ?? ?????? ?????? Office\Office\Library\Solver Files\Microsoft C:\Program.
??? ???? ???? ???????? ????? Solver Excel Microsoft ?? ????? VBA.

????? ????? ????? VBA ???? ???? ?????? ??? ???? Microsoft Excel Solver ?????

??? ????? ?? ?? ???? Microsoft Excel Solver ?????? ?? ??????? ? ???? ?????? ?????? ?? ???? ??????? ??????? ???????:
  • ???? SolverOK
  • ???? SolverSolve
  • ???? SolverFinish

???? SolverOK

????? ?????? SolverOK ???? Microsoft Excel Solver ??????. ???? SolverOK ???? ??? ?????? ????? ???? ??? ??????? ?????? ????? Microsoft Excel Solver. ????? ????? ??? Solver ??? ????? ?????? SolverOK ??????? ??? ?? ????? ???????? ???????? ?? ?????? Solver ???? ??????. ?????? ?? ???? ???? ?????? SolverOK:
SolverOK(SetCell, MaxMinVal, ValueOf, ByChange)
???? ????????? ??????? ???? ???? ?????? SolverOK:
  • SetCell ????? ?????? ?????.
  • MaxMinVal ????? ?? ??? ??? ???? ?? ?? ?????? ????? ???? ?????? (1) ?? ???? ?????? (2) ?? ???? ????? (3).
  • ????? ValueOf ???? ?????? ????? ??????. ??? ??? ?????? MaxMinVal ??? 3 ??? ????? ??? ???????. ??? ??? ?????? MaxMinVal ??? 1 ?? 2 ????? ????? ??? ???????.
  • ????? ByChange ???? ?? ???? ?? ??????? ???? ???? ?????.
????? ??? 1 ????? ?????? ?????? SolverOK ?? ???????? ?? ???? ?????? ?????? Solver.

????? ??? 1. ???????? ???? ????? SolverOK ???????

?? ??? ??????????? ??? ??????
 Figure 1. Parameters that are associated
		  with the SolverOK arguments


???? SolverSolve

?? ?????? SolverSolve ?????? ???????? ???????? ???? ?????? ?? ?????? SolverOK. ????? ???? SolverSolve ????? ????? ??? Solve ?? ???? ?????? ?????? Solver. ?????? ?? ???? ???? ?????? SolverSolve:
SolverSolve(UserFinish, ShowRef)
???? ????????? ??????? ???? ???? ?????? SolverSolve:
  • UserFinish ??????? ??? ?? ??? ??? ???? ?? ?? ???? ???????? ?????? ?? ??????.

    ?????? ??????? ??? ??? Solver ????? ?????? ? ?? ?????? ??? ??????? ??? TRUE. ????? ??????? ???? ???? ?????? Solver Results (????? Solver) ????? ??? ??????? ??? FALSE
  • ????? ShowRef ??????? ???? ???? ??? ????? Solver Excel Microsoft ?? ??????.

    ??? ?? ??? ??????? ??????? ShowRef ??? ????? ??? ????? TRUE ??? ??????? StepThru ?????? SolverOptions.

???? SolverFinish

???? ?????? SolverFinish ??? ?? ??? ?????? ?? ?? ??????? ?? ??? ??????? ?????? ??? ?????? ????? ????. ?????? ?? ???? ???? ?????? SolverFinish:
SolverFinish (KeepFinal ? ReportArray)
???? ????????? ??????? ???? ???? ?????? SolverFinish:
  • ??????? ??? KeepFinal ?? ??? ?????? ?? ?? ??????? ????????. ??? ??? KeepFinal 1? ????? ???????? ???? ??? ???????? ?? ??????? ???????? ??????? ?????. ??? ??? KeepFinal 2 ? ??? ????? ????? ???????? ???? ?? ??? ??????? ??? ???????.
  • ????? ReportArray ???? ???? ???? ??? ??? ????? ?? Microsoft Excel ????? ??? ?????? ??? ????. ??? ?? ????? ReportArray ??? 1 ? Microsoft Excel ?????? "?????" ????????. ?? ???? ????? ??? 2 Microsoft Excel ?????? "????? ????????" ? ????? ??? 3 ??? ??? Microsoft Excel ?????? "????? ????". ?????? ??? ???? ?? ????????? ??? ??? ???????? ???? ??? "How to generate reports for solutions".
????? ??? 2. ?????? ???????? ?? ???????? SolverFinish ????? Microsoft Excel Solver

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


???? ??? ??????? ????? ????? ???? Microsoft Excel Solver ????? ???? ??????. ?????? ?????? ????? ???? ????? ??????. ????? ???? ????? ??? ??? ????? ???????? ????? ????? ??? ????? ????? ??? ????. ????? ??? ?????? ??? ??????? ?????? ???????? ?? ???? ?????. ??? ????? ???? ????? ???? ??? Solver ?? ??????? ?????. ?? ???? ?????? Solver Parameters ????? ?????? ????? ?????? ???? ??? ?? ?? ???? ??????? ???? ???? ????? ???????. ???? ??? Solve ???? ????? ????. ??? ??? Microsoft Excel Solver ?? ?????? ??? ?? ???? ??????? ?? ???? ????? ?? ???? Solver Excel Microsoft ???? ????? ?????? ??? ??? ???? ???????? ??????? ???????? ?? ??? ??? ???? ????? ???. ??? ????? ??? ??? ???????? ??????? ????? Microsoft Excel Solver.

????? 3 ???? ???? ???? ????? ????? ???????? ??????? ???????.

????? ??? 3. ???? ????: ???? "????? ????"

?? ??? ??????????? ??? ??????
 Figure 3. A
		  simple model: The Square Root model


?? ??? ??????? ????? ???? A1? ????? ????? ??? ?????? = A1 ^ 2 ??? ???? ???? ???? ??? ?? ?????? A2 ????? ???? 50. ????? ???? ?????? ????? ???????? ?? 50. ?? ???? ??? ???? ?? ???? ???? ?????. ??????? Find_Square_Root accomplishes ?????? ???????:
  • ???? ?????? ????? ???? ???? ?? ???? ?????? A2 ????? ?? 50 ?? ???? ????? ???? ?????? A1.
  • ??? ??? ??????.
  • ???? ???? ??????? ??????? ?? ???? ????? ???? ??? ???? ?????? Solver Results (????? Solver).
????? ????? Microsoft Excel Solver ??? ??????? ????? ?? ?? ??? ?? ???? ?? ????????. ???? ???????? ???????? ??????? ??????? Find_Square_Root:
    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
??????? Find_Square_Root2 ? ????? ???????? ?? ??????? Find_Square_Root. ??? ??? ?????? ?????? InputBox ?????? ??????? Find_Square_Root2 ?????? ???? ???? ???? ?????? ?????. ??? ????? ???? ? ??????? Find_Square_Root2 ????? ??? ??????? ???? ??????? valueofSolverOK ? ??? ??? ?? ???????, ??? ??????? ?? ????? ????? ???????? ?? ?????? ???? ? ????? ?????? ?? ???? ????? ??? ????? ???????. ???? ?????, ??????? Find_Square_Root2 ???? ??? ???? ??? ??????? ?? ????? ???? ?? ???? ?? ?? ??????? ??????? ???????? ??? ?????? ???????.

???? ???????? ???????? ??????? ??????? Find_Square_Root2: sqroot ?????
    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

????? ????? ?????? ?? ????

???? Microsoft Excel Solver ??? ????? ???????? ???? ??? ????? ??????? ????? ?????? ????? ?????? ??????? ??? ????? ??????. ??? ??? ?? ????? ?? ???? ??? ?????? ?? ?????? ????? ??. ??? ??????? ?? ????? ???????? ???? ???? Solver Microsoft Excel:
  • ???? ???????? ?????-"????? ???????" ?????? ????????? ? ??????? ???????? ?? ?????? ??? ????? ???????? ?????? ? ???????? ? ???? ? ??????? ??? ??????.
  • ????? ??????-"????? ????????" ???? ??????? ??? ??? ?????? ???? ??? ????????? ??????? ?? ?????? ?? ?????? ?????.
  • ???? ??????? ????-"????? ????" ?????? ????????? ? ??????? ???????? ?? ????? ??????? ???? ????? ???? ?????.
?????? ???????? ?????? ?? ?????? ????? ???? ?? ????? ?? ??? ??????? ReportArray ?????? SolverFinish. ????? ?? ????????? ??? ??????? ReportArray ???? ??? "SolverFinish (KeepFinal, ReportArray) ". ??? ???? ??????? ??? ??? ???? ????? "????? ????" ??????? ???? ???? ?????? ????? Find_Square_Root2 ? ??? ????? ?????? SolverFinish ?? ??????? ???? ???? ??????? ???? ???????? ???????? ???????? ?? ?????? ??????:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
??? ????? ?????? ?????? ? ????? ?????? SolverFinish ???? ???? ??? ????? ????????? ???????? ??????:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)

????? ??????? ????? ExcelSolver Microsoft ?? ????? looping

?? ?????? ?? ???????? ?? ???????? ?? Microsoft Excel Solver ?? ?????? ????? ??? ??????. ????? ???? ??? ????? ??? ???????? ????? ?? ????? looping ???????? ?? VBA.

???? ??????? Create_Square_Root_Table ??? ???? Microsoft Excel Solver ?? ????? looping. Create_Square_Root_Table ??????? ?????? ???? ?? ???? ??? ?????. ???? ?????? ????? ??? ???? 10 ????? ???????? ??????? ?? ???. ????? ????? Create_Square_Root_Table ?????? ???????? ???? For ? ??? ?????? ????? ?? ???? ????? ???? ????? ????? ??? ??????? ????? ???? ??????? ?? 1 ??? 10. ???? ???????? ???????? ??????? ??????? Create_Square_Root_Table:
    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

??????? Create_Square_Root_Table ?????? ???? ????? ?? ????? 4.

????? ??? 4. ??????? ???? ?? ?????? ?????? ??????? Create_Square_Root_Table

?? ??? ??????????? ??? ??????
Figure 4. Output that is generated by the
		  Create Square Root Table macro


????? ????? ?? ????

??? ????? ??? ????????? ?? ???? ????? ?? ????. ???? ?? ????? ???? ???? ?? ??? ??????. ?????? ????? ?? ?????? ?? inequalities ?? ?????? ?? equalities ????? ??????? ????? ?? ????? ????????? ???? ?? ????. ??? ???? ??????? ?? ????? ??? ???? ????? ???? ???? ?? ????? ? ????? ??? ?????? ??? ??? ???? ??? ????.

???? ????? ???? ???? ???? ???? ???????? ??? ??? ?????? ?? ???? ????? ???? ?? ????? ??? ??? ????. ???? ????? 5 ????? ?????? ??????. ????? ?? ??? ?????? ?????? ??? ???? ?????? ?????? ????? ????.

????? ??? 5. ??? ?????? ?? diminishing ???? ?????

?? ??? ??????????? ??? ??????
Figure 5. Product mix with
		  diminishing profit margin


??? ???? ??????? ??? ???? ????? ??????? ?????? ??????? ?????? ????? ????? ????? ?? ????????? ?????? cones ???? ????? ?????. ??????? ?? ????? ??????. ??? ????? ?????? ??? ??????? ???? ???????? ??????. ????? ????? ??? ???? ?? ???? ??????? ???? ????? ??? ?????? ?????? ????? ?????? ???????. ?? ???? ????? diminishing ??????. ??? ??????? ??? ???? ????? ????? ?? ???? ?? ???? G11:I11.

???? ?? ????? ?? ????? ???? (?????? G14). ?? ????? ???? ???? ????? ????? ?? ????? ???? ????? ?? ??????? ?????. ???? G9:G11 ?????? ??????? ???????? ?? ??? ??????. ?? ????? ??? ??? ????? ?? ??????? ??????? ?? ???? ?? ?????? ????? ?? ??????? ???? ??? ??????. ?????? Microsoft Excel Solver? ??? ????? ???? E3:E7 < = B3:B7. ??? ??? ???? ????? ??? ?????? Microsoft Excel Solver ???? ?????? ???? ?????? ???? ???????? ?? ????? 6 ?????? Microsoft Excel Solver.

????? ??? 6. ??? ?????? Solver Excel Microsoft ?????? ?? ???? Diminishing ???? ?????

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


????? ????? "??? ??????" ?? ???? Diminishing ???? ????? ????? ??????? ???? ???? ?????? SolverAdd ???????? ??? ????? VBA Solver Excel Microsoft ???? ?? ???? ??????. ????? ???? SolverAdd ????? ??????. ????? ???? SolverAdd ????? ????? ??? ???? "?????" ?? ???? ?????? ?????? Solver. ????? ?????? SolverAdd ??? ???? ?????? ??????:
SolverAdd (CellRef ????? ? FormulaText)
???? ????????? ??????? ???? ???? ?????? SolverAdd:
  • ????? CellRef ???? ????? ?? ???? ???? ???? ?????? ?????? ?????.
  • ?? ??????? ????? ??????? ??? ?????? ???????? ?????? ?? ???.
  • ???? ?? ???? ??????? ???? ??? 1 ? 5 ??? ?? ?????? ??????:
    • ?????? 1 ??? ?? ?? ????? (< =).
    • vaue 2 ????? (=).
    • ?????? 3 ???? ?? ?? ????? (> =).
    • ?????? 4 ??? ????.
    • ?? ?????? 5 ??????? (????? ??? ?? ????).
  • FormulaText ????? ???? ????? ?? ???? ???? ???? ?????? ?????? ?? constraint.* *
** ????? ???? ?????? ???? ?? ??????? ??????? ??????? FormulaText ?????? SolverAdd ? ???? ?? ??? ??? ?????? ????? ?? ?????. ???? ???? ??? ???? ????? ???? ???? ?????? FormulaText. ??? ???? ?? ???? ????? ????? ????? ?????? FormulaText ???? ??? ???? ?????? ???? ??? ?????? ????? ? ?????? ??????.

?????? ?? Microsoft Excel? ????????? 5.0 ? 7.0 ? ??????? ???? R1C1 ??? ????? ???? ?? ???? ?? ??????? ???????? ????? FormulaText. ???? ?????? ?? Microsoft Excel 97 ??????? ??? A1 ???? ?????? ????? FormulaText.

????? ??? 7. ?????? ???????? ???????? ??????? SolverAdd

?? ??? ??????????? ??? ??????
 Figure 7. Fields that are associated with
		  the SolverAdd arguments


??????? Maximum_Profit ???? ???? ????? ?? ??? "??? ??????" ?? ???? ????? Diminishing. ????? ??? ??????? ??????? ?? ??????? ???????:

  • ???? SolverOK ?????? ?????? ????? ???? ?????? ????? ?????? ??????? ??????.
  • ????? ???? SolverAdd ????? ??????.
  • ??? ?????? SolverSolve ??? ??? ???? ?????? Solver Results (????? Solver).
  • ???? ?????? SolverFinish ??????? ??????? ?? ???? ?????.
???? ??? ??????? ???????? ???????? ??????? ??????? Maximum_Profit:
    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

?????? "?? Microsoft Excel?" ????????? 5.0 ? 7.0 ??????? ???? R1C1 ??? ????? ???? ?? ???? ?? ??????? ???????? ????? FormulaText. ???? ?????? ?? Microsoft Excel 97 ??????? ??? A1 ???? ?????? ????? FormulaText.

??? ????? ??????? Maximum_Profit ????? Microsoft Excel Solver ?? ?????? ??? ?? ????? ??????? ????????? 160 ?????? 200 ??????? 80 ????? ???? ???? ?? $ 14,917 ?????.

????? ????? ???? ??????

???? ????? ?????? ?? ?????? ????? ?? ??????? ?? ????. ??? ????? ???? ?????? CellRef ???????? ?????.

?????? ??????? ??? ?????? ?????? ?????? SolverChange. ???? ???? ?????? ?? ??? SolverChange ?????? ??????:
SolverChange (CellRef ????? ? FormulaText)
???? ?? ??????? ?????? ????? SolverChange ?? ??? ??? ??? ???? ???????? ?? ?????? SolverAdd.

??? ??? ???? ?? ????? ????? ?? "??? ??????" ?? ???? ????? Diminishing ?????? ?????? SolverChange. ??? ???? ??????? ?????? ????? ?????? ???? ????? ??????? ??????? ??? ?? ?? ????? ????? ?? ??????? ?? ??????? (E3:E7 < = B3:B7). ??? ???? ????? ??? ????? ???? ??? ??????? ???? ?? ????????? ??? ?? ?? ????? ????? ????? ??????? (??? ??????? ??? ??? ??) ???????? ??? ??? ?? ??????? ???? ?? ?????. ??? ????? ?????? ????? E3:E7 < = D3:D7. ??????? ?????? ?????? E3:E7 ????? ??????? < = B3:B7 E3:E7 < = D3:D7 ????? ?? ??.

???? ???????? ???????? ??????? ??????? Change_Constraint_and_Solve:
    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

??? ??? ????? ???? ?????? ????????? CellRef "?" ????? "? ????? ??? ????? ????? FormulaText ????? ???????? ?????? SolverChange. ?? ???? ??? ????? CellRef ???? ????? ??? ????? ? ??? ??? ????? ?? ?? ?????? ????? ???????. ??? ???, ?????? ?????? SolverDelete. ?????? ?? ???? ???? ?????? SolverDelete:

SolverDelete (CellRef ????? ? FormulaText)

???? ?? ??????? ?????? ????? SolverDelete ?? ??? ???? ??????? ?? SolverAdd ???????? SolverChange.

??????? ?????? ???? ????? ??? ????? ???. ?? ??? ??????? ??????? Change_Constraint_and_Solve2 ????? ????? E3:E7 < = B3:B7 ?? "??? ??????" ?? ???? ????? Diminishing ?????? ??? ????. ??? ???? ??? ??? ????? ??? ?????? ??? ??? ?????? ????? ???????? ?????? ?????.

???? ???????? ???????? ??????? ??????? Change_Constraint_and_Solve2:
    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

?????? "?? Microsoft Excel?" ????????? 5.0 ? 7.0 ??????? ???? R1C1 ??? ????? ???? ?? ???? ?? ??????? ???????? ????? FormulaText. ???? ?????? ?? Microsoft Excel 97 ??????? ??? A1 ???? ?????? ????? FormulaText.

????? ????? ???? ????? ????? ??

??? ??? ?????? ????? ?? ??? ??? ?????? ?????? ???? ??? ??????? ?? ???? ?????? Solver Parameters ?? ??????. ????? ??? ??? ?????? ???????? ?? ??? ????? ??? ???? ?????? ??????.

????? ????? ???? ?? ????? ????? ???. ????? ?? ????? ?? ??????? ??????? ???? ?????? ?? ?????? ?????? Solver Options "?" ????? Solver. ???? ??? ??? ??? ??????? ??? ?? ???? ?????, ???? ???? ???? ????? ???? ?? ?? ???? ??????. ???? ??? ???? ??? ??? ????? ?? Solver ?????? ???? ??????. ????? ???????? ??? ??? ???? ?? ??????? ?????? ???????? ?????? ???? ??? Load Model ?? ???? ?????? Solver Options.

??? ????? ?????? Solver ?? ???? ?? ??????? ?? ???? ???. ?????? ?????? ?? ?????? ????? ??? ???? ?????? ?????. ?????? ??????? ?? ?????? ????? ??? ???? ????? ??????? ???????? ?? ??????. ????? ?????? ??????? ?? ?????? ??? ???? ???? ???? ?????? ????? ?? ???? ?????? Solver Options. ????? ??????? ??? ?????? ??????? ? ?????? ??????? ??? ??? ???? ?????? ???????? ?? ??????.

???? ????? 8 ???? ???? ???????. ????? ??? ???? ?????? ??????? ????. ????? ??? ?????? ??? ?? ???? ??? ???? ?????? ??? ??????? ???? ??? ????? ???? ???????? ?? ??????? ???? ???? ?? ???? ?? ???? ?? ???? ?????. ??? ????? ?????? ????? ????? ????? ?????? ??? ??? ??????? ???? ?? ??????? ????? ????? ????? ?????.

????? 8. ?????? ??? ???????
l
?? ??? ??????????? ??? ??????
 Figure 8. Employee Scheduling
		  model


????? ?????? ?????? (?? ????) ???? ??? ?????? ?? ???? ??????/???? ?????? ??? ????? ????? ?? ???? ???? "?" ??? ??????? ???? ???? ?? ?????. ??? ??? ?????? ???? ????? ?????? ????? 3975 ????? ??? ???? ????? ??? 30 ? 45 ???? ?????? Solver Excel Microsoft ???? ?????? ???? ??????? ?? ?????? ??????:

?? ??? ??????????? ??? ??????
??????????? ?????????
?????? ????? D $ $ 12 ????? ?????.
??????? ???????? $ C $ 2: $ C $ 8 ????? ????? ??? ????.
???? $ C $ 2: $ C $ 8 < = 45 ?? ???? ?????? ?? ??????? ??? ???? 45.
$ C $ 2: $ C $ 8 > = 30 ???? ?????? ??????? ??? ???? ?? 35.
G $ $ 12 = 3975 ?? ??? ??????? 3975.


???? ?????? ??? ?????? ????? ?????? ??? ???? ??????? ???? ??? ???? ????????? ?? ????? ?????? ??? ????? ?? ???? ????????? ????? ????? ?????.

?? ?????? ???? ??? ?????? Solver Excel Microsoft ????? ?????? ????? ???????? SolverSave ???????? SolverLoad ??? ???????. SolverSave ???????? SolverLoad ?? ???? ?????? ??????:

SolverSave (SaveArea)

solverLoad (LoadArea)

SolverSave ????? SolverLoad ?? ???? ????? ????? ??? "?" SaveArea "?" ??????? LoadArea ??? ???????. ????? ??????? ??????? ???? ?? ???? ??? ??? ??? ????? ??????? ??????.

??????? New_Employee_Schedule ?????? ???? ????? ????? ?? ??? ???? ????? ???????? ??? ????? ????????. ?????? ???????? ?? ????? ????? ?????? ??? ??????? ?????? ??? ???? ?????? ????? ?????? ??? ??????? ??? ????. ?? ?????? ??? ???????? ?????? ??????. ??? ?? ?????? ?? ????? ?? ????? ????????.

???? ???????? ???????? ??????? ??????? New_Employee_Schedule:
    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
?????? "?? Microsoft Excel?" ????????? 5.0 ? 7.0 ??????? ???? R1C1 ??? ????? ???? ?? ???? ?? ??????? ???????? ????? FormulaText. ???? ?????? ?? Microsoft Excel 97 ??????? ??? A1 ???? ?????? ????? FormulaText.

???? ????? 9 ????? ???? ??????? ?????? ?????? ?? ???? ?????.

????? ??? 9. ??????? ?????? ???? ?? ???? ?????? ??????? New_Employee_Schedule

?? ??? ??????????? ??? ??????
Figure 9. Model information that is saved
		  by the New Employee Schedule macro


??? ??????? New_Employee_Schedule ?? ???? ???? ??? ???? ?????. ???? ????? ??????? Load_Employee_Schedule ??? ??? ??????? ????????. ????? ???????? ?????? ????? ??????? ??? ?? ???? ?????? I ??????? ??????. ??? ?? ?????? ??? ????? ???? ??????? Load_Employee_Schedule ?????? ???? ??????? ??? ???? ? ?? ????? ??????? ????????.

???? ???????? ???????? ??????? ??????? New_Employee_Schedule:
    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
??????? New_Employee_Schedule ???? ?????? SolverReset. SolverReset ???? ??????? ?????? ???? ???? ????????? ?????? ??????? ???????? ?? ???? ?????? Solver Parameters ????? ?????? ????? ???? ????????? ?? ?????? SolverReset ???? ?????.

????? ?????? ??? ???? ?? ????????? ??? Microsoft Excel Solver

???? ??????? ??????? ??????? ??? ????? ??????? Solver Microsoft Excel ??????? ????????.

  • ?????? ??? ??????? ??? ????? ????? Solver ???? Frontline Systems.
  • ?????? ??? ??????? ??? ???? ??????? ?????? ???? ??????? ???? Frontline Systems.
  • ?????? ??? ??????? ?????? ??? Solver ??? ???? ?? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
    75714???? Solver ??????
  • ?????? ??? ?????? ?? ??????? ???? ?????? ??????? ???????? Solver Excel Microsoft ?? Microsoft Excel ? ???? Solvsamp.xls ????? ?????.
  • ?????? ?? ?????? ????????? ???? ????? ??????? ?? Microsoft Excel 97:
    \Program Files\Microsoft Office\Office\Examples\Solver\SolvSamp.xls
  • ?????? ?? ?????? ????????? ???? ????? ??????? ?? Microsoft Excel 7.0:
    \MSOffice\Excel\Examples\Solver\SolvSamp.xls
  • ?????? ?? ?????? ????????? ???? ????? ??????? ?? Microsoft Excel 5.0:
    \Excel\Examples\Solver\SolvSamp.xls

????? ????? ?????? ??? ?????????? ????????? ????????? ?? ??? Microsoft Excel Solver

?????? Microsoft Excel Solver ?????? ??????? ??????? ???? ????????? ???????? ?????? ???? (GRG2) ???? ?? ??????? ?????? Lasdon ???? ????? Texas ?? Austin ? Allan Waren ????? ???? Cleveland.

?????? ??? ??????? ?????? ??? ?????????? ????????? ?? ??? Microsoft Excel Solver ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
82890?????? Solver generalized ???????


????? ??? "?" ??? ???? ??????? ????? simplex ?? ???? ??? ????????? ? ??????? ????? ? ???????? ?????? John Watson ? Dan Fylstra, Frontline Systems, Inc. ????? ?? ????????? ??? ????? ???? ???????? ????????? ?? ??? Solver ??? ???????:

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 


??????? Solver Excel Microsoft ???? ????????? ???????? ???????? ???? ????? 1990 ? 1991 1992 ? 1995 ?????? Frontline Systems, Inc. ????? ??? ???? ????? 1989 ??? Optimal ??????, Inc.

?????? Microsoft Excel ??????? ???????? Solver ?????? ?? ??? ??????? ?? ????? "??? ??" ??? ???? ?? ???? ??? ???? ???????? ?? ???? ???????. ??? ????? ?? ?? ????? ??????? ????? ??? Microsoft ?? ??????? ???????? ???????? ??? ??????? ???????? "? ?? ?????? ?????? ??????? ???????? ??? ????? ????? ?????.

???? NO. ??? ????? ???????? "???-," ??? ???? ?? ?? ??? ???????? ?? ?? ??? ???????? ?????? ??? ???????? ??????.

???????

???? ???????: 843304 - ????? ??? ??????: 18/?????/1427 - ??????: 1.2
????? ???
  • Microsoft Excel 97 Standard Edition
????? ??????: 
kbmt kbhowto kbmacroexample kberrmsg kbaddin kbvba kbprogramming kbinfo KB843304 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????843304
????? ??????? ?? ????? ???? ?? ????? ???????
?? ????? ????? ?????? ???? ???? ???? ??? ??????? ??? ?? ? ?? ??? ??????? Microsoft ??? ????? ??? ??????? ????? ?????? ?????

????? ???????

 

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