Microsoft - Excel - 规划求解的说明

Microsoft Excel 规划求解的说明

Microsoft Excel 规划求解是一个 Microsoft Excel Add-in Microsoft Excel Solver 有助于您确定 Microsoft Excel 工作表上的特定目标单元格中公式的最优值。 Microsoft Excel 规划求解调整其他单元格使用的公式与目标单元格的值。 在构建一个公式，并定义公式中的参数或变量的约束的一组后，Microsoft Excel 规划求解尝试到达满足所有约束的应答的各种解决方案。 Microsoft Excel 规划求解使用下列元素来\解决公式：

? 目标单元格 的程序的目标单元格的目标。 它是在工作表模型将最小化、 最大化，或设置

为特定值的单元格。

? 更改单元格 的 Changing 单元格为决策变量。 这些单元格会影响目标单元格的值。 这些

单元格更改 Microsoft Excel 规划求解查找目标单元格的最佳解决方案。

? 约束 的约束是限制内容的单元格。 是例如尽管另一个单元格可能限制为在给定的值小于，

可能限制为整数的值工作表模型中的一个单元格。

可以通过使用 Microsoft Visual Basic for Applications (VBA) 宏自动执行创建和 Microsoft Excel 规划求解模型的操作。 本文介绍如何使用 VBA 宏语言在 Microsoft Excel 97 中使用 Microsoft Excel 规划求解函数。 本文假定您熟悉 VBA 语言和用于 Microsoft Excel 97，Microsoft Visual Basic 编辑器。 本文中使用的示例有以下 Microsoft Web 站点下载： 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 Solver 加载项函数，您必须从包含宏的工作簿的 VBA 项目引用加载项。 如果不引用 Microsoft Excel 规划求解加载中，您将收到以下编译错误，当您尝试运行宏时：

编译错误： 子或函数未定义。

若要引用 Microsoft Excel 规划求解加载在工作簿中的宏的使用下列步骤：

1. 打开工作簿。

2. 在 工具 菜单上指向 宏 ，然后单击 Visual Basic 编辑器 。 3. 在 工具 菜单中上, 单击 引用 。

4. 在 可用的引用 列表中单击以选中 Solver.xls 复选框，然后单击 确定 。

请注意 如果在 可用引用 列表中，不执行看到 Solver.xls，单击 浏览 。 在 添加引用 对话框查找并选择 Solver.xla 文件，并单击 打开 。 通常 C: \\ Program 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，您必须指定该

参数。 如果将 MaxMinVal 设置为 1 或 2，可以省略此参数。

? ByChange 指定单元格或将更改的单元格区域。

图 1 使用在 规划求解参数 对话框的参数，将相关联， SolverOK 函数参数。

图 1。 参数与 SolverOK 参数

SolverSolve 函数

SolverSolve 函数解决了模型使用与 SolverOK 函数指定的参数。 执行 SolverSolve 函数等效于单击 求解 规划求解参数 对话框中。 下面是 SolverSolve 函数的语法： SolverSolve(UserFinish, ShowRef) 以下信息介绍 SolverSolve 函数的语法：

? UserFinish 指定是否在用户完成求解该模型。

若要返回结果，而不显示在 规划求解结果 对话框框时，请将此参数设置为 TRUE。 返回结果，，显示 规划求解结果 对话框，设置此参数为 FALSE

? ShowRef 标识 Microsoft Excel 规划求解返回一个中间解决方案时调用的宏。

仅当 TRUE SolverOptions 函数的 StepThru 参数传递时，应使用 ShowRef 参数。

SolverFinish 函数

SolverFinish 函数指示如何处理该的结果和报表以创建解决方案过程完成后的种类。 下面是 SolverFinish 函数的语法：

SolverFinish (KeepFinalVariant，ReportArray) 以下信息介绍 SolverFinish 函数的语法：

? KeepFinalVariant 指示如何处理最终的结果。 如果 KeepFinalVariant 为 1，则最

终的求解值将保存该更改的单元格中中，替换值。 如果 KeepFinalVariant 为 2，丢弃最终就还原以前的值。

? ReportArray 指定数组表示的达到该解决方案时，Microsoft Excel 将创建的报告类型。

如果 ReportArray 被设置为 1，Microsoft Excel 将创建的答案报告。 如果设置为 2，Microsoft Excel 将创建一个敏感度报告并设置为 3 的如果 Microsoft Excel 创建限制报表。 有关这些报表，请参阅\How to generate reports for solutions\一节。

图 2。 Microsoft Excel 规划求解结果与 SolverFinish 参数相关联的选项

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

图 3 说明了可以通过使用以下步骤创建简单的模型。

图 3。 简单的模型： 平方根模型 展开这个图片

在此示例，更改单元格 A 1，包含该的公式 = A 1 ^2，以一个值，它将单元格 A 2 等于 50 的值。 也就是说，查找 50 的平方根。 平方根模型中有无约束。 Find_Square_Root 宏完成在下面的任务：

? 它会设置一个模型，通过更改单元格 A 1 的值会解决 50 的值的单元格 A 2 的值。 ? 它求解该模型。

? 而不显示 规划求解结果 对话框，它将最终结果保存到工作表中。

以下简单的宏创建 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(\ ByChange:=Range(\

' 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

SolverFinish KeepFinalVariant： = 1 End Sub Find_Square_Root2 宏，

Find_Square_Root 宏的修改的版本。 如果使用 InputBox 函数， Find_Square_Root2 宏将提示您为您要在其中解决目标单元格的值。 输入值后， Find_Square_Root2 宏将此参数设置为 SolverOK valueof 参数的值，解决问题、 将结果保存在变量数据平方根丢弃该解决方案然后将工作表中的值恢复其原始状态。 基本， 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:=\ \

' Set up the parameters for the model.

SolverOK SetCell:=Range(\ ByChange:=Range(\

' 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(\

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

' Show the result in a message box.

MsgBox \\

End Sub

回到顶端

如何生成报告的解决方案

Microsoft Excel 规划求解提供了多种描述结果更改的信息以及如何关闭约束的报告类型是为其关键值。 每个报表将放在单独的工作表在工作簿中上。 以下这些是 Microsoft Excel 规划求解所提供的报告类型：

? 答案报告 -The 答案报告列出目标单元格和可变单元格的其对应的原始和最终值、 约束和

约束的信息。

? 敏感度报告 -The Sensitivity 报告提供有关如何敏感解决方案是小写更改目标单元格公式

的信息。

? 限制报告 -The 限制报告列出目标单元格和可变单元格及其相应值、 和下限的限制和目标

值。

若要创建您的模型的报表，指定为 ReportArray 参数 SolverFinish 函数的值的数组。 有关 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(\

w.Range(\

' 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(\ 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(\

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

Next

' Clear the range C1:C2 w.Range(\

End Sub

SolverFinish KeepFinalVariant： = 2 下, 一步清除区域 C1:C2 w.range(\Sub Create_Square_Root_Table 宏生成图 4 所示的表。

图 4。 由 Create_Square_Root_Table 宏生成的输出

回到顶端

如何使用约束

约束是一个或多个单元格的内容限制。 一种模型可以具有一个或多个的限制。 约束集是一组 inequalities 或一组解决方案中删除的决策变量的值的某些组合的 equalities。 是例如约束可能需要一个单元格是大于零的数，以及另一个单元格只包含整数值。

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

图 5。 与削减 Profit Margin 的产品组合

例如，如果公司制造电视、 stereos 和扬声器，并使用常见的部件库存电源、 扬声器 cones，等。 部分位于受限的供应。 您的目标是确定生成的产品的最盈利组合。 您单位的利润减少与卷，因为其他价格激励需要加载分发渠道。 0.9 diminishing 返回指数。 此指数用于计算利润按产品范围 G11:I11 中。

您的目标是找到最大利润 （单元格 G14）。 利润，这些值，您将更改以查找最大值是生成的单位数。 该区域 G9:G11 代表此模型中的更改单元格。 您唯一的约束是您使用的部分数不能超过各个部分有现货。 与 Microsoft Excel Solver，该约束显示为 E3:E7 < = B3:B7。 如果您要以交互方式生成此 Microsoft Excel 规划求解模型，Microsoft Excel 规划求解参数将类似于图 6 中的那些。

图 6。 产品的 Microsoft Excel 规划求解参数组合使用削减利润距模型

若要创建和解决产品组合使用削减利润距模型，您将使用一个新函数 SolverAdd 函数的除了前面介绍在 Microsoft Excel 规划求解 VBA 函数。 SolverAdd 函数将约束添加到模型中。 执行 SolverAdd 函数等效于单击 规划求解参数 对话框中的 添加 按钮。 SolverAdd 函数的语法如下：

SolverAdd (CellRef、 关系、 FormulaText） 以下信息介绍 SolverAdd 函数语法：

? CellRef 引用一个或多个约束的左侧的单元格。 ? 关系 是左和约束的右边之间算术关系。

? 关系 可以是一个介于 1 和 5 为下面的示例中的值：

o 值 1 小于或等于 (< =)。 o vaue 2 等于 (=)。 o 值 3 为大于或等于 (> =)。 o 值 4 是一个整数。

o 值 5 是二进制文件 （零个或一个的值）。

? FormulaText 引用一个或多个在 constraint.* * 右侧的单元格

**When 指定为 FormulaText 参数 SolverAdd 函数的单元格的范围时，请注意是否引用是相对还是绝对。 通常情况下，您必须指定 FormulaText 参数的绝对引用。 但是，如果您执行指定 FormulaText 参数的相对引用，意识到则引用将相对目标单元格和不活动的单元格。

请注意 在 Microsoft Excel，版本 5.0 和 7.0，请使用 FormulaText 参数指定一个单元格或单元格的区域时使用 R 1 C 1 表示法。 相反，Microsoft Excel 97，使用 A 1 样式表示法指定 FormulaText 参数。

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

为该产品组合使用 Diminishing 返回模型生成模型的 Maximum_Profit 宏。 此宏执行以下的函数或参数：

? SolverOK 函数设置目标单元格为最大值，并指定要更改单元格。 ? SolverAdd 函数将约束添加到模型中。

? SolverSolve 函数而不显示 规划求解结果 对话框查找一个解决方案。 ? SolverFinish 函数返回到工作表的最终结果。

以下代码描述了在 for 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(\ bychange:=Range(\

' 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(\ FormulaText:=\

' 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 参数指定单元格或单元格的区域时使用 R 1 C 1 表示法。 相反，Microsoft Excel 97，使用 A 1 样式表示法指定 FormulaText 参数。

当您运行 Maximum_Profit 宏时, Microsoft Excel 规划求解将找到生成 160 个电视设置、 200 stereos，和最大的利润的 $ 14,917 美元的 80 扬声器的解决方案。 回到顶端

如何更改和删除约束

在您的模型中的限制都能以编程方式更改或删除。 约束由其 CellRef 和 Relation 参数标识。

若要编程方式更改现有的约束，使用 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(\ FormulaText:=\

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

End Sub

SolverSolve UserFinish: = False End Sub 因为约束由 CellRef 和 Relation 参数标识，只能通过使用 SolverChange 函数更改为约束 FormulaText 参数。 如果 CellRef 和 Relation 值与现有的约束不匹配，您必须删除该约束，并再添加已修改的约束。 要删除约束条件，使用 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(\

FormulaText:=\

SolverAdd CellRef:=Range(\ FormulaText:=\ ' Return the results and display the Solver Results dialog box. SolverSolve UserFinish:=False

End Sub

注意 在 Microsoft Excel，版本 5.0 和 7.0， FormulaText 参数指定单元格或单元格的区域时使用 R 1 C 1 表示法。 相反，Microsoft Excel 97，使用 A 1 样式表示法指定 FormulaText 参数。 回到顶端

如何加载和保存您的模型

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

可以定义多个工作表的问题。 每个问题由单元格和 规划求解参数 和 规划求解选项 对话框中输入的约束组成。 因为只有最后一个问题与工作表保存的您将丢失所有其他问题，除非显式保存。 若要保存它们一点，单击 保存模型 在 规划求解选项 对话框。 同样，如果希望还原以前保存的参数，单击 规划求解选项 对话框中 加载模型 。

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

图 8 显示了计划编制的员工的模型。 假设您在工作小的制造商。 下表显示付薪、 的约会计划的小时数和计划每个雇员可能会产生一个小时内的单位数的每个员工已经小时的速率。 您的目标是人工的满足特定的配额的最小化成本时生成的单位数。

图 8。 员工计划模式 l

两个其他因素 （或约束） 您必须考虑是最小 / 最大可任意一个员工的小时数和要生成的单位数。 如果在指定的周，您需要生成 3975 单位，并且您希望每个员工数 30 和 45 之间的工作，Microsoft Excel 规划求解参数将类似于下面的表中列出的：

参数

单元格区域

说明 人工成本。

工作每个员工的小时数。

目标单元格 $ D $12 更改单元格 $C$2： $C$8 约束

$C$2： $C$8 < = 45 每个雇员的最大小时是 45。 $C$2： $C$8 > = 30 每个雇员的最小小时是 35。 $ G $ 12 = 3975

单位的数量为 3975。

您的目标是为在每周基础以每周，保存每个模型，并能够在需要时，加载任何每周的模型上的最佳的人工成本解决。

一个的宏 Microsoft Excel 规划求解参数为模型可以被保存并加载分别使用 SolverSave 和

SolverReset 函数。 SolverSave 和 SolverReset 函数采用以下语法：

SolverSave (SaveArea)

SolverLoad (LoadArea)

SolverSave 和 SolverReset 函数每个分别具有只有一个参数、 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:=\

Units = Application.InputBox( _

Prompt:=\ MaxHrs = Application.InputBox( _

Prompt:=\ MinHrs = Application.InputBox( _

Prompt:=\

' Clear any previous Solver settings. SolverReset

' Set the target cell, D12, to a minimum value by changing ' the range, C2:C8.

SolverOk SetCell:=Range(\ ByChange:=Range(\

' Add the constraint that number of hours worked <= MaxHrs. SolverAdd CellRef:=Range(\FormulaText:=MaxHrs

本文来源：https://www.bwwdw.com/article/7okv.html