如何创建通过使用 Excel 的规划求解在 Excel 97 中的 vba 宏

文章翻译 文章翻译
文章编号: 843304 - 查看本文应用于的产品
展开全部 | 关闭全部

本文内容

概要

本文介绍了如何使用 Microsoft Excel 97 中的 Microsoft Excel 规划求解创建 Microsoft Visual Basic 宏。Microsoft Excel 规划求解是 Microsoft Excel 加载宏。

此外,这篇文章包含有关如何创建宏、 如何设计一个宏和如何使用宏的约束的信息。本文还讨论了算法和使用 Microsoft Excel 规划求解的方法。以下列表提供了在所讨论的所有主题
该文章。

简介

本文包含有关 Excel 的信息,规划求解。

更多信息

Microsoft Excel 规划求解的说明

Microsoft Excel 规划求解是 Microsoft Excel 的外接程序 Microsoft Excel 规划求解将帮助您确定在 Microsoft Excel 工作表上的特定目标单元格公式的最优值。Microsoft Excel 规划求解调整通过使用一个公式来与目标单元格相关的其他单元格的值。在构造公式并将等式中定义的参数或变量的约束集之后 Microsoft Excel 规划求解尝试各种解决方案来满足所有约束的答案。Microsoft Excel 规划求解来解决公式使用下列元素:
  • 目标单元格-此目标单元格是在实现目标方面所起的作用。它是在工作表模型中将最小化、 最大化,或设置为某个特定值的单元格。
  • 可变单元格 的可变单元格是决策变量。这些单元格会影响目标单元格的值。这些单元格会更改 Microsoft Excel 规划求解找到最佳的解决方案的目标单元格。
  • 约束 的约束是在 $ 内容上的单元格的限制。例如对于可能限制为给定的值小于另一个单元格,在工作表模型中的一个单元格可能会限制为整数值。
可以通过使用 Microsoft Visual Basic 应用程序 (VBA) 宏自动执行创建和 Microsoft Excel 规划求解模型的操作。本文介绍如何使用 Microsoft Excel 97 中的 Microsoft Excel 规划求解函数使用 VBA 宏语言。本文假定您熟悉 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 中的这篇文章中描述的示例。

如何使用 VBA 宏中的 Microsoft Excel 规划求解函数

若要在 VBA 宏使用 Microsoft Excel 规划求解加载项函数,必须引用该加载项从包含该宏的工作簿的 VBA 项目。如果您不引用 Microsoft Excel 规划求解加载项在您尝试运行该宏时将会收到以下编译错误:
编译错误: 子或函数未定义。
若要引用 Microsoft Excel 规划求解加载宏的工作簿中的宏,请使用以下步骤:
  1. 打开工作簿。
  2. 工具 菜单上指向 ,然后单击 Visual Basic 编辑器
  3. 工具 菜单上单击 引用
  4. 可用引用 列表中单击以选中 Solver.xls 复选框,然后单击 确定

    注意如果看不 Solver.xls 在 可用引用 列表中单击 浏览添加引用 对话框中定位和选择 Solver.xla 文件,然后单击 打开。通常,是 Files\Microsoft Office\Office\Library\Solver 子文件夹中找到 Solver.xla 文件。
您已准备好要在 VBA 宏使用 Microsoft Excel 规划求解函数。

如何设计一个 VBA 宏的创建,并解决了一个简单的 Microsoft Excel 规划求解模型

尽管 Microsoft Excel 规划求解提供了许多功能,下面的三个函数都是基本创建,并解决模型:
  • SolverOK 函数
  • SolverSolve 函数
  • SolverFinish 函数

SolverOK 函数

SolverOK 函数定义了一个基本的 Microsoft Excel 规划求解模型。SolverOK 函数通常是您将使用生成您的 Microsoft Excel 规划求解模型的第一个函数。SolverOK 函数等同于单击 规划求解工具 菜单,然后再指定是在 规划求解参数 选项对话框。以下是 SolverOK 函数语法:
SolverOK(SetCell, MaxMinVal, ValueOf, ByChange)
以下信息描述了 SolverOK 函数语法:
  • SetCell 指定目标单元格。
  • MaxMinVal 对应于您是否想要解决目标单元格的最大值 (1)、 最小值 (2) 或一个特定的值 (3)。
  • ValueOf 指定目标单元格相匹配的值。如果将 MaxMinVal 设置为 3,您必须指定此参数。如果设置为 1 或 2 MaxMinVal,您可以忽略此参数。
  • ByChange 指定单元格将被更改的单元格的区域。
图 1 将 SolverOK 函数参数使用 规划求解参数 对话框中的参数相关联。

图 1。参数与 SolverOK 相关联的参数

收起这个图片展开这个图片
 Figure 1. Parameters that are associated
		  with the SolverOK arguments


SolverSolve 函数

SolverSolve 函数求解该模型,使用 SolverOK 函数使用指定的参数。执行 SolverSolve 函数等同于单击 求解规划求解参数 对话框中。以下是 SolverSolve 函数语法:
SolverSolve(UserFinish, ShowRef)
以下信息描述了 SolverSolve 函数语法:
  • UserFinish 指明是否希望在用户完成解决模型。

    若要返回的结果而不显示该 规划求解结果 对话框框中,此参数设置为 TRUE。要返回的结果,并显示 规划求解结果 对话框将此参数设置为 FALSE
  • ShowRef 标识 Microsoft Excel 规划求解返回一种中间解决方案时所调用的宏。

    仅当 TRUE StepThruSolverOptions 函数的参数传递时,应使用 ShowRef 参数。

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。Microsoft Excel 规划求解结果与 SolverFinish 参数相关联的选项

收起这个图片展开这个图片
 Figure 2. Solver results options
		  that are associated with SolverFinish arguments


本文介绍如何以交互方式创建一个简单的 Microsoft Excel 规划求解模型。第一步是创建工作表中的模型。在工作表将包含某些数据单元格和至少一个包含公式的单元格。此公式取决于其他单元格在工作表中。设置您的工作表后,单击在 工具 菜单上的 规划求解。在 规划求解参数 对话框中指定目标单元格、 您解决有关的值、 将更改的单元格区域和约束。单击 求解 以启动求解过程。后 Microsoft Excel 规划求解已找到一个解决方案结果将出现在您的工作表中,Microsoft Excel 规划求解会显示一个消息框,提示您如果要使最终结果,或者如果您希望放弃更改。当您单击其中的一个选项时,Microsoft Excel 规划求解将完成。

图 3 说明了通过使用这些步骤,可以创建一个简单的模型。

图 3。简单的模型: 的方形根模型

收起这个图片展开这个图片
 Figure 3. A
		  simple model: The Square Root model


本示例中,更改单元格 A1,其中包含公式,= A1 ^2 中的可将单元格 A2 的值等于值 50。也就可以找到 50 的平方根。方形根模型中没有任何约束。Find_Square_Root 宏可以完成以下任务:
  • 它会设置一种模型,并解决通过更改单元格 A1 的值的单元格 A2 的值为 50 的值。
  • 它求解该模型。
  • 不显示 规划求解结果 对话框的情况下,它将最终的结果保存到工作表中。
此简单的宏创建 Microsoft Excel 规划求解模型,并没有任何用户干预的情况下解决它。下面的代码描述 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 宏将此参数设置为 SolverOKvalueof 参数的值、 后问题解决了、 将结果保存在该变量的正方形根,然后丢弃该解决方案并将该工作表中的值恢复为其原始状态。基本上,Find_Square_Root2 宏说明如何将结果保存在一个或多个变量,然后还原为其原始值的可变单元格。

下面的代码描述 Find_Square_Root2 宏:
    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 规划求解提供了几种类型的报告,描述了在结果如何更改和如何关闭约束为其关键值一起提供。 每个报表放在您的工作簿中单独的工作表上。以下这些是 Microsoft Excel 规划求解提供的报告的类型:
  • 答案报告 — 在应答报告列出目标单元格和可变单元格及其对应的初始值和最终结果、 约束和有关约束条件的信息。
  • 敏感度报告-的灵敏度报告提供有关如何敏感解决方法是很小的目标单元格公式中的更改的信息。
  • 限制报表 — 的限制报表列出目标单元格和可变单元格及其各自的值、 在下限和上限限制和目标值。
若要进行您的模型的报告指定为 ReportArraySolverFinish 函数的参数的值的数组。有关 ReportArray 参数的详细信息,请参阅"SolverFinish (KeepFinal, ReportArray) "一节。例如对于如果要生成的 Find_Square_Root2 宏创建,并解决了模型限制报告修改 SolverFinish 函数在宏,使它看起来类似于下面的代码示例:
    SolverFinish KeepFinal:=2, ReportArray:= Array(3)
到生成多个报表,修改 SolverFinish 函数,以使它看起来类似于下面的代码示例:
    SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)

如何在循环宏使用 Microsoft ExcelSolver 函数

在很多的情况下它是一个好主意,则 Microsoft Excel 规划求解后解决了目标单元格的多个值。您通常可以使用 VBA 的循环结构之一完成此操作。

Create_Square_Root_Table 宏演示 Microsoft Excel 规划求解中循环宏的工作方式。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


如何使用约束

约束是一个或多个单元格的内容限制。一个模型可以有一个或多个约束。约束集是一套不等式或 equalities 从解决方案中移除某些决策变量的值的组合的一组。例如对于约束可能需要一个单元格是大于零,并且另一个单元格只能包含一个整数值。

我们已经讨论到目前为止该正方形根模型是一个简单的模型不包含任何约束。图 5 说明了使用约束的模型。此模型的目的是要查找的产品的最佳组合的最大利润。

图 5。产品与削弱利润率混合使用

收起这个图片展开这个图片
Figure 5. Product mix with
		  diminishing profit margin


例如,如果某个公司生产电视、 立体和扬声器,并使用的电源、 扬声器圆锥等的公共部分库存。该部件是有限的设备中。您的目标是确定生成的产品的最具盈利能力的组合。您单位的利润减少了与卷,因为其他价格奖励需要加载的分发渠道。diminishing 返回指数是 0.9。此指数用于计算利润的 G11:I11 范围内的产品。

您实现目标方面所起的作用是找到最大利润 (单元格 G14)。您将更改以查找最大利润的值是在生成的单位数。范围 G9:G11 表示在此模型中的可变单元格。您唯一的约束条件是您使用的部分数不能超过您手边有的部分数。与 Microsoft Excel 规划求解该约束显示为 E3:E7 < = B3:B7。如果您要构建此 Microsoft Excel 规划求解模型以交互方式,Microsoft Excel 规划求解参数可能会类似于图 6 中的这些。

图 6。削弱利润率模型与混合使用产品的 Microsoft Excel 规划求解参数

收起这个图片展开这个图片
Figure 6. Microsoft Excel Solver parameters for
		  the product mix with Diminishing Profit Margin model


创建,并解决产品组合,与削弱利润率模型中,您将使用一个新函数 SolverAdd 函数的中,除了前面描述的 Microsoft Excel 规划求解 VBA 函数。SolverAdd 函数将约束添加到模型中。 执行 SolverAdd 函数等同于单击 规划求解参数 对话框中的 添加 按钮。SolverAdd 函数的语法如下:
SolverAdd (CellRef、 关系、 FormulaText)
以下信息描述了 SolverAdd 函数语法:
  • CellRef 引用构成约束条件的左边的一个或多个单元格。
  • 关系 是左边和右边构成的一个约束之间算术关系。
  • 关系 可以是介于 1 和 5,如下例所示:
    • 值为 1,小于或等于 (< =)。
    • vaue 2 等于 (=)。
    • 值为 3 是大于或等于 (> =)。
    • 一个整数,值 4。
    • 值 5 是二进制文件 (值为零个或一个)。
  • FormulaText 引用窗体的 constraint.* * 右侧的一个或多个单元格
** 您在指定为 FormulaText 参数 SolverAdd 函数的单元格区域时请注意该引用是相对还是绝对。通常,您必须指定为 FormulaText 参数的绝对引用。但是,如果指定为 FormulaText 参数的相对引用,意识到该引用将相对于目标单元格和不活动的单元格。

注意当您使用 FormulaText 参数指定一个单元格区域时,请在 Excel 5.0 或 7.0,版本使用 R1C1 表示法。在与之相反,在 Microsoft Excel 97,使用 A1-样式表示法来指定 FormulaText 参数。

图 7。与 SolverAdd 参数相关联的字段

收起这个图片展开这个图片
 Figure 7. Fields that are associated with
		  the SolverAdd arguments


Maximum_Profit 宏为产品组合,与 Diminishing 返回模型中生成一个模型。该宏执行以下的函数或参数:

  • SolverOK 函数设置目标单元格的最大值,并指定要更改单元格。
  • SolverAdd 函数将约束添加到模型中。
  • SolverSolve 函数而不显示 规划求解结果 对话框查找一个解决方案。
  • 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,FormulaText 参数与指定单元格的单元格区域时,请使用 R1C1 表示法。在与之相反,在 Microsoft Excel 97,使用 A1-样式表示法来指定 FormulaText 参数。

您在运行 Maximum_Profit 宏时 Microsoft Excel 规划求解将寻找生成 160 电视设置、 200 的立体声和最大的利润的美元 14,917 美元的 80 扬声器的解决方案。

如何更改和删除约束

可以以编程方式更改或删除模型中的约束。约束是由其 CellRef关系 参数标识的。

若要编程方式更改现有的约束条件,使用 $ SolverChange 函数。以下是该语法 SolverChange 函数:
SolverChange (CellRef、 关系、 FormulaText)
请注意 SolverChange 函数的参数是那些与 SolverAdd 函数一起使用的相同。

如果您要更改该产品组合使用 Diminishing 返回模型中的约束,可使用 SolverChange 函数。例如对于当前指定的约束是的数字部分使用的是小于或等于该数字部分的现有库存 (E3:E7 < = B3:B7)。如果要更改此约束,以使各个部分使用小于或等于该数字的部件预计 (数上现有的部件) 加上部分订购的数量。此新的约束将类似 E3:E7 < = D3:D7。下面的宏会更改现有的约束 E3:E7 < = 到 E3:E7 B3:B7 < = 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 函数的参数是为那些使用 SolverAddSolverChange 函数相同的。

下面的宏说明如何删除和添加约束。 在此的示例 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,FormulaText 参数与指定单元格的单元格区域时,请使用 R1C1 表示法。在与之相反,在 Microsoft Excel 97,使用 A1-样式表示法来指定 FormulaText 参数。

如何加载和保存您的模型

当您保存工作簿时,您在 规划求解参数 对话框中指定的最后一个参数是随工作簿一起保存。因此,当您打开工作簿,参数将与上一次保存工作簿时相同。

您可以定义为工作表的多个问题。每个问题是由组成单元格和 规划求解参数规划求解选项 对话框中输入的约束。因为只有最后一个问题与工作表保存,将丢失所有其他问题,除非您显式地将其保存。单击将其保存的 保存模型 中该 规划求解选项 对话框。同样时要还原以前保存的参数, 单击 装入模型规划求解选项 对话框中。

规划求解模型存储在工作表上的单元格的单元格区域中。第一个单元格区域中的包含目标单元格的公式。第二个单元格区域中的包含用于标识模型中不断变化的单元格的公式。最后一个单元格区域中的包含一个数组类型的值,该值代表在 规划求解选项 对话框中设置该选项。第二个单元格和最后一个单元格之间的单元格包含表示模型中的约束的公式。

图 8 说明了计划的员工的模型。假定您在一个小制造商工作。此表显示了每个员工的付薪、 它们计划的小时数和一个预计每个雇员可以产生在一小时中的单位数的每小时工资费率。您的目标是人工的以满足特定配额的同时最大限度地减少所需成本产生的单位数。

图 8。员工计划模式
l
收起这个图片展开这个图片
 Figure 8. Employee Scheduling
		  model


两个其他因素 (或约束) 您必须考虑是最小/最大可以处理任意一个员工的小时数和要生成的单位数。如果您要在一指定周产生 3975 单位,并且您希望工作 30 到 45 小时之间的每个员工 Microsoft Excel 规划求解参数可能会类似于下表中列出的那些:

收起该表格展开该表格
参数单元格区域说明
目标单元格$ D $ 12人工成本。
可变单元格$ C $ 2: $ C $ 8每个员工工时。
约束$ C $ 2: $ C $ 8 < = 45 每个雇员的最大小时是 45。
$ C $ 2: $ C $ 8 > = 30 每个雇员的最小小时是 35。
$ $ 12 = 3975 G3975 的单位数。


您的目标是为获得最佳的劳动力成本,以每周,保存每个模型,并能够在需要时加载任何每周的模型在每周基础上解决。

在一个宏中可以保存和加载分别使用 SolverSaveSolverLoad 函数 Microsoft Excel 规划求解参数为模型。SolverSaveSolverLoad 函数具有下面的语法:

SolverSave (SaveArea)

solverLoad (LoadArea)

每个 SolverSaveSolverLoad 函数都分别有只能有一个参数、 SaveAreaLoadArea 参数。这些参数指定的模型信息的存储位置在工作表上的范围。

下面的 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,FormulaText 参数与指定单元格的单元格区域时,请使用 R1C1 表示法。在与之相反,在 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 函数可用于删除所有单元格选定区域和 规划求解参数 对话框中的约束和重置 SolverReset 函数中的所有设置都有没有参数。

如何查找有关 Microsoft Excel 规划求解的详细信息

以下资源提供了有关如何使用 Microsoft Excel 规划求解加载项。

  • 有关特定规划求解消息的帮助,请参阅 Frontline Systems
  • 有关构建可读的提示,易于管理的模型,请参阅 Frontline Systems
  • 有关在规划求解的其他信息对于约束的限制,然后单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
    75714规划求解限制的约束
  • 有关使用 Microsoft Excel 规划求解加载宏在 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 规划求解的方法的详细信息

Microsoft Excel 规划求解使用通用缩减渐变由 Leon Lasdon 在奥斯汀,大学德克萨斯和伦 Waren Cleveland 州立大学开发的 (GRG2) 非线性的优化代码。

Microsoft Excel 规划求解使用该算法有关的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
82890规划求解使用通用化降低


线性和整数问题使用单工方法与在的变量上边界和由 John Watson 和陶建明 Fylstra,Frontline 系统,Inc.实现该分支和绑定方法有关规划求解使用的内部求解过程的详细信息,请联系:

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 


选择的程序代码是在 Microsoft Excel 规划求解版权 1990年、 1991年、 1992,和 1995 通过前线系统,Inc.部分是通过最佳方法,Inc.的版权 1989

注意Microsoft Excel 规划求解加载项在这篇文章中讨论的是"按原样提供,我们并不能保证它可以在所有情况下使用。尽管 Microsoft 支持专业人员可以通过安装和现有的功能,该外接程序的帮助,它们将不会修改外接程序提供新的功能。

任何担保。本软件提供"作为的是,"没有任何类型和任何使用此软件的产品是需要您自担风险。

属性

文章编号: 843304 - 最后修改: 2006年10月11日 - 修订: 1.2
这篇文章中的信息适用于:
  • Microsoft Excel 97 标准版
关键字:?
kbmt kbhowto kbmacroexample kberrmsg kbaddin kbvba kbprogramming kbinfo KB843304 KbMtzh
机器翻译
注意:这篇文章是由无人工介入的微软自动的机器翻译软件翻译完成。微软很高兴能同时提供给您由人工翻译的和由机器翻译的文章, 以使您能使用您的语言访问所有的知识库文章。然而由机器翻译的文章并不总是完美的。它可能存在词汇,语法或文法的问题,就像是一个外国人在说中文时总是可能犯这样的错误。虽然我们经常升级机器翻译软件以提高翻译质量,但是我们不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的错误使用所引起的任何直接的, 或间接的可能的问题负责。
点击这里察看该文章的英文版: 843304
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。
不再更新的 KB 内容免责声明
本文介绍那些 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