Microsoft_Excel_Solver_的说明.docx

Microsoft Excel Solver 的说明Microsoft Excel Solver 是一个 Microsoft Excel 增益集。Microsoft Excel Solver 会帮助您决定 Microsoft Excel 工作表上，特定目标储存格内之公式的最佳值。Microsoft Excel Solver 会使用方程式，来调整与目标储存格相关之其他储存格的值。您建立好方程式，并定义方程式中之变量的一组参数或条件约束之后，Microsoft Excel Solver 就会尝试各种解法，以得到满足所有条件约束的答案。Microsoft Excel Solver 会使用下列元素来解方程式： 目标储存格 - 目标储存格是最终目的。它是工作表模型中要最小化、最大化或设定为特定值的储存格。 变量储存格 - 变量储存格是做出判断的变量。这些储存格会影响目标储存格的值。Microsoft Excel Solver 会变更这些储存格，以寻找目标储存格的最佳解法。 条件约束 - 条件约束是对储存格内容的限制。例如，工作表模型中某个储存格可能限制为整数值，而另一个储存格可能限制为小于某个指定值。您可以使用 Microsoft Visual Basic for Applications (VBA) 巨集，以自动执行 Microsoft Excel Solver 模型的建立与操作。本文将告诉您如何使用 VBA 巨集语言，在 Microsoft Excel 97 中使用 Microsoft Excel Solver 函数。本文假设您已熟悉 VBA 语言和 Microsoft Excel 97 的 Microsoft Visual Basic 编辑器。其中使用的范例可以从下列 Microsoft 网站下载： /download/excel97win/solverex/1.0/WIN98Me/EN-US/SolverEx.exe (/download/excel97win/solverex/1.0/win98me/en-us/solverex.exe) 注意 本文中说明的巨集与范例，也可以在 Microsoft Excel 5.0 与 7.0 版中使用。回此页最上方如何在 VBA 巨集中使用 Microsoft Excel Solver 函数若要在 VBA 巨集中使用 Microsoft Excel Solver 增益集函数，您必须从包含巨集之活页簿的 VBA 项目参照增益集。如果未参照 Microsoft Excel Solver 增益集，则在尝试执行巨集时，将会收到下列编译错误： Compile Error:Sub or Function not defined (编译错误:Sub 或 Function 未定义)。若要在活页簿中参照巨集的 Microsoft Excel Solver 增益集，请依照下列步骤执行： 1. 开启活页簿。 2. 在 工具 功能表上，指向 巨集，然后按一下 Visual Basic 编辑器。 3. 按一下 工具 功能表上的 设定引用项目。 4. 在 可引用的项目 清单中，按一下以选取 Solver.xls 核取方块，然后按一下 确定。注意 如果在 可引用的项目 清单中看不到 Solver.xls，请按一下 浏览。在 新增引用项目 对话方块中，找出并选取 Solver.xla 档案，然后按一下 开启。Solver.xla 档案通常位于 C:Program FilesMicrosoft OfficeOfficeLibrarySolver 子资料夹下。您现在已经可以在 VBA 巨集中使用 Microsoft Excel Solver 函数。回此页最上方如何设计可建立并解决简单 Microsoft Excel Solver 模型的 VBA 巨集虽然 Microsoft Excel Solver 提供许多函数，不过下列这三个函数是建立和解决模型的基础： SolverOK 函数 SolverSolve 函数 SolverFinish 函数SolverOK 函数 SolverOK 函数会定义基本的 Microsoft Excel Solver 模型。SolverOK 函数通常是建立 Microsoft Excel Solver 模型时，所使用的第一个函数。SolverOK 函数等于按一下 工具 功能表上的 规划求解，然后指定 Solver Parameters 对话方块中的选项。以下是 SolverOK 函数的语法： SolverOK(SetCell, MaxMinVal, ValueOf, ByChange)以下信息说明 SolverOK 函数的语法： SetCell 指定目标储存格。 MaxMinVal 对应至您要将目标储存格解到最大值 (1)、最小值 (2) 或特定值 (3)。 ValueOf 指定目标储存格要符合的值。如果将 MaxMinVal 设定为 3，就必须指定这个自变量。如果将 MaxMinVal 设定为 1 或 2，则可以省略这个自变量。 ByChange 指定要变更的一个储存格或某个范围内的储存格。图 1 显示 SolverOK 函数的自变量与 Solver Parameters 对话方块中的参数之间的关联性。 图 1. 与 SolverOK 自变量关联的参数摺叠此图像展开此图像SolverSolve 函数SolverSolve 函数会使用您以 SolverOK 函数指定的参数来解决模型。执行 SolverSolve 函数等于按一下 Solver Parameters 对话方块中的 解决。以下是 SolverSolve 函数的语法： SolverSolve(UserFinish, ShowRef) 下列信息说明 SolverSolve 函数的语法： UserFinish 指出您是否要使用者完成解决模型。若要传回结果但不显示 Solver Results 对话方块，请将这个自变量设定为 TRUE。若要传回结果并显示 Solver Results 对话方块，请将这个自变量设定为 FALSE ShowRef 识别 Microsoft Excel Solver 传回中继解法时呼叫的巨集。唯有传递 TRUE 到 SolverOptions 函数的 StepThru 自变量时，才应该使用 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 会建立限制报告。如需这些报告的详细信息，请参阅如何产生解法的报告一节。图 2. 与 SolverFinish 自变量关联的 Microsoft Excel Solver 结果选项摺叠此图像展开此图像本文说明如何互动地建立简单的 Microsoft Excel Solver 模型。第一步是建立模型的工作表。工作表会包含一些资料储存格，而至少有一个储存格会包含公式。这个公式会依赖工作表中其他的储存格。设定好工作表之后，按一下 工具 功能表上的 规划求解。在 Solver Parameters 对话方块中指定目标储存格、您要解答的值、将要变更之储存格的范围，以及条件约束。按一下 解决 开始解答程序。Microsoft Excel Solver 找到解答之后，会在您的工作表中显示结果，而且 Microsoft Excel Solver 会显示一个讯息方块，提示您决定要保留最终结果，或者要舍弃结果。您按一下其中一个选项之后，Microsoft Excel Solver 即告完成。图 3 说明可以使用这些步骤建立的简单模型。图 3. 简单模型：平方根模型摺叠此图像展开此图像按一下以展开此图片在这个范例中，将包含公式 =A12 的储存格 A1 变更为一个值，这个值会让储存格 A2 的值等于 50。也就是寻找 50 的平方根。平方根模型中没有条件约束。Find_Square_Root 巨集会完成下列工作： 设定一个模型，这个模型会变更储存格 A1 的值，求出会使 A2 的值等于 50 的值。 它会解决模型。 将最终结果储存到工作表，不显示 Solver Results 对话方块。这个简单的巨集会建立 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 SubFind_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 Solver 提供许多类型的报告，这些报告说明如何变更结果，以及如何关闭已达到关键值的条件约束。每一份报告都会放在活页簿中单独的一份工作表上。以下是 Microsoft Excel Solver 提供的报告类型： 答案报告 - 答案报告会列出目标储存格和变量储存格，以及它们的对应原始与最终值、条件约束，还有条件约束的相关信息。 敏感度报告 - 敏感度报告会提供目标储存格之解答对公式中，对于微小变化之敏感度的相关信息。 限制报告 - 限制报告会列出目标储存格与变量储存格，以及它们个别的值、下限与上限和目标值。若要建立模型的报告，请为 SolverFinish 函数的 ReportArray 自变量指定值阵列。如需 ReportArray 自变量的详细信息，请参阅SolverFinish (KeepFinal, ReportArray) 一节。例如，您若是想要产生 Find_Square_Root2 巨集建立及解决之模型的限制报告，请修改巨集中的 SolverFinish 函数，使其类似以下的范例程序码： SolverFinish KeepFinal:=2, ReportArray:= Array(3)若要产生多个报告，请修改 SolverFinish 函数，使其类似以下的范例程序码： SolverFinish KeepFinal:=2, ReportArray:= Array(1,2)回此页最上方如何在循环巨集中使用 Microsoft Excel Solver 函数在许多情况下，让 Microsoft Excel Solver 解决目标储存格的多个值，是很好的做法。这通常可以使用 VBA 提供的循环结构之一来完成。 Create_Square_Root_Table 巨集示范 Microsoft Excel Solver 在循环巨集中作用的情形。Create_Square_Root_Table 巨集会在新的工作表中建立一个表格。它会插入一到十的数字，以及每个数字的对应平方根。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 =C12 in cell C2. w.Range(C1).Value = 2 w.Range(C2).Formula = =C12 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 SubCreate_Square_Root_Table 巨集会产生 图 4 中说明的表格。 图 4. Create_Square_Root_Table 巨集产生的输出 摺叠此图像展开此图像回此页最上方如何处理条件约束条件约束是对一个或多个储存格之内容的限制。一个模型可以有一个或多个条件约束。条件约束集是一组不等式或一组等式，会从解答中移除决定变量的特定值组合。例如，条件约束可能要求某个储存格必须大于零，而另一个储存格只能包含整数值。我们到目前为止一直讨论的平方根模型是一个简单模型，并未包含任何条件约束。图 5 说明使用条件约束的模型。这个模型的目的，是要寻找获利最高之产品的最佳组合。图 5. 渐减利润率产品矩阵摺叠此图像展开此图像例如，某家公司若是制造电视机、立体声音响和扬声器，而且使用电源供应器、扬声器锥盆.等等的共同零件库存。零件的供应有限。您的目标，是要决定所制造之产品的最佳获利组合。数量增加，每一单位的利润就会减少，因为填满销售通路需要更多的价格优惠。渐减利润指数为 0.9。这个指数用于在 G11:I11 的范围中依产品计算利润。您的目标是要找出最大的利润 (储存格 G14)。要寻找最大利润所要变更的值，是您制造的单位数目。范围 G9:G11 代表这个模型中的变量储存格。您唯一的条件约束，是所使用的零件数目不能超过库存的零件数目。使用 Microsoft Excel Solver，这个条件约束应为 E3:E7=B3:B7。如果您是要互动地建立这个 Microsoft Excel Solver 模型，Microsoft Excel Solver 参数应类似 图 6 中的参数。图 6. 使用渐减利润率产品矩阵模型的 Microsoft Excel Solver 参数摺叠此图像展开此图像若要建立及解决渐减利润率产品矩阵模型，除了先前讨论过的 Microsoft Excel Solver VBA 函数之外，还要使用新的 SolverAdd 函数。SolverAdd 函数会新增条件约束到模型。执行 SolverAdd 函数等于按一下 Solver Parameters 对话方块中的 新增 按钮。SolverAdd 函数的语法如下： SolverAdd (CellRef, Relation, FormulaText) 下列信息说明 SolverAdd 函数的语法： CellRef 参考构成条件约束左边的一个或多个储存格。 Relation 是条件约束左边和右边之间的算术关系。 Relation 可以是 1 到 5 之间的值，如以下范例中所示：o 值 1 是小于或等于 (=)。 o 值 4 是整数。 o 值 5 是二进制数 (零或一的值)。 FormulaText 参考构成条件约束右边的一个或多个储存格。* 您指定 SolverAdd 函数之 FormulaText 自变量的储存格范围时，请注意参考是相对或绝对参考。通常必须为 FormulaText 自变量指定绝对参考。但是，如果为 FormulaText 自变量指定相对参考，应了解参考是与目标储存格相对的，而非与作用储存格相对。注意 在 Microsoft Excel 5.0 与 7.0 版中，使用 FormulaText 自变量指定一个储存格或一个范围内的储存格时，使用的是 R1C1 表示法。而在 Microsoft Excel 97 中，则是使用 A1 表示法指定 FormulaText 自变量。图 7. 与 SolverAdd 自变量相关的栏位摺叠此图像展开此图像产生渐减利润产品矩阵模型之模型的 Maximum_Profit 巨集。这个巨集执行下列函数或自变量： SolverOK 函数会设定目标储存格的最大值，并指定要变更的储存格。 SolverAdd 函数会新增条件约束到模型。 SolverSolve 函数会寻找解答，但不会显示 Solver Results 对话方块。 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 Solver 会寻找制造 160 台电视机、200 台立体声音响及 80 组扬声器，最大利润为 14,917 美元的解答。 回此页最上方如何变更及删除条件约束模型中的条件约束可以程序设计的方式变更或删除。条件约束是利用其 CellRef 和 Relation 自变量识别。若要利用程序变更现有的条件约束，请使用 SolverChange 函数。以下是 SolverChange 函数的语法： SolverChange (CellRef, Relation, FormulaText)请注意，SolverChange 函数的自变量和用于 SolverAdd 函数的自变量相同。如果想要变更渐减利润产品矩阵模型中的条件约束，要使用 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 和 Relation 自变量识别，因此只能使用 SolverChange 函数来变更条件约束的 FormulaText 自变量。如果 CellRef 和 Relation 值与现有的条件约束不符，您必须删除条件约束，再新增修改过的条件约束。若要删除条件约束，请使用 SolverDelete 函数。以下是 SolverDelete 函数的语法： SolverDelete (CellRef, Relation, FormulaText) 请注意，SolverDelete 函数的自变量和配合 SolverAdd 与 SolverChange 函数使用的自变量相同。 以下巨集说明如何删除及新增条件约束。在这个范例中，Change_Constraint_and_Solve2 巨集会从渐减利润产品阵列模型移除条件约束 E3:E7=B3:B7，再新增新的条件约束。新条件约束只是原始条件约束的修改，将条件约束的左右两边对调。下列程序码说明 Change_Constraint_and_Solve2 巨集： Sub Change_Constraint_and_Solve2() Reverse the left and right sides of the constraint. Delete the constraint E3:E7=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 自变量。回此页最上方如何载入及储存模型您储存活页簿时，在 Solver Parameters 对话方块中指定的最后参数会随活页簿一起储存。因此，当您开启活页簿时，参数是和您上次储存活页簿时的参数相同。您可以为工作表定义一个以上的问题。每个问题都由您在 Solver Parameter 和 Solver Options 对话方块中，所输入的储存格和条件约束构成。由于只有最后一个问题会随工作表一起储存，因此除非您明确地储存，否则将会遗失其他所有的问题。若要储存这些问题，请按一下 Solver Options 对话方块中的 Save Model。同样地，当您要还原先前储存的参数时，请按一下 Solver Options 对话方块中的 Load Model。Solver 模型会储存在工作表上某个范围的储存格中。范围中的第一个储存格包含目标储存格的公式。范围中的第二个储存格包含识别模型中之变量储存格的公式。范围中最后一个储存格包含代表 Solver Options 对话方块中设定之选项的阵列。第二个储存格和最后一个储存格之间的储存格，则包含代表模型中之条件约束的公式。图 8 说明员工排程的模型。假设您在一家小型制造商工作。这个表格显示每一位员工的时薪、排定的时数，以及每一位员工每一小时可以生产的预计单位数。您的目标是符合制造单位数的特定配额，同时要降低人工成本。图 8. 员工排程模型摺叠此图像展开此图像您必须考虑另外两个因子 (或条件约束)，一个是任一员工可以工作的时数下限/上限，另一个是您想要生产的单位数。如果您在指定的某一周必须生产 3975 个单位，而您希望每一位员工工作 30 到 45 小时，那么 Microsoft Excel Solver 参数应类似下表中列出的参数：摺叠此表格展开此表格参数储存格范围描述目标储存格$D$12人工成本。变量储存格$C$2:$C$8每一位员工的工作时数。条件约束$C$2:$C$8 = 30每一位员工的时数下限为 35。$G$12 = 3975单位数是 3975。您的目标是求出每周的最佳人工成本、每周储存每一个模型，以及在需要时能够载入任一个每周模型。在巨集中，可以分别使用 SolverSave 和 SolverLoad 函数，来储存及载入模型的 Microsoft Excel Solver 参数。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 =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 巨集所储存的模型信息摺叠此图像展开此图像New_Employee_Schedule 巨集会将每个新模型储存到工作表。Load_Employee_Schedule 巨集可以载入这些储存模