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 站点下载：
https://www.doczj.com/doc/8045281.html,/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("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
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:="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 规划求解所提供的报告类型：
?答案报告-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("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
SolverFinish KeepFinalVariant：= 2 下, 一步清除区域C1:C2 w.range("C1:C2").clear End 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 v aue 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("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 参数指定单元格或单元格的区域时使用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("E3:E7"), Relation:=1, _
FormulaText:="$D$3:$D$7"
' 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("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 参数指定单元格或单元格的区域时使用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:="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 参数指定单元格或单元格的区域时使用R 1 C 1 表示法。相反，Microsoft Excel 97，使用 A 1 样式表示法指定FormulaText 参数。
图9 显示了在保存的模型信息在工作表上的显示方式。
图9。由New_Employee_Schedule 宏保存的模型信息
New_Employee_Schedule 宏将保存到工作表的每个新的模型。
Load_Employee_Schedule 宏可以加载这些已保存模型之一。宏提示加载模型的用户，，然后搜索列我模型日期。如果找到模型日期，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 中的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 规划求解的方法
Microsoft Excel 规划求解使用通用减少渐变(GRG2) 非线性的优化代码Leon Lasdon，在奥斯汀市，大学的得克萨斯和Allan Waren，克利夫兰状态大学开发的。
有关使用Microsoft Excel 规划求解该算法单击下面的文章编号，以查看Microsoft 知识库中的相应：
82890规划求解使用通用减少
线性和整数的问题使用simplex 方法对于在的变量上的界限和由John Watson 和Dan
Fylstra，Frontline Systems，Inc 实现该分支绑定方法有关使用规划求解的内部求解过程的更多信息，请联系：
Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(702) 831-0300
Web site: https://www.doczj.com/doc/8045281.html,
Electronic mail: info@https://www.doczj.com/doc/8045281.html,
p.o.框4288 Incline Village，NV 89450-4288 (702) 831-0300 Web 站点：https://www.doczj.com/doc/8045281.html, 电子邮件：Info@https://www.doczj.com/doc/8045281.html,
选择Microsoft Excel Solver 程序代码是版权的1990 年、1991、1992 和Frontline Systems，Inc 的部分是1995 年的版权1989 最佳方法，Inc
请注意本文讨论在Microsoft Excel 规划求解加载宏"按原样"提供，我们执行不保证它可以在所有情况下。尽管Microsoft 支持专业人员可以帮助安装和此加载项的现有功能，它们不会修改外接程序提供新功能。
任何担保。该软件"作为-是，"不附带任何明示任何类型和任何使用此软件的产品是需要您自担风险。
利用规划求解在EXCEL中解方程 工具/原料：EXCEL 2007/2010(如果是EXCEL 2003，这些操作都是一样的，只是相对应的设置地方会不一样)，规划求解插件 步骤/方法 1首先我们来讲一下EXCEL里面内置的单变量求解。 2为了方便操作，一般我们会对单元格进行名称定义，点击公式——定义名称。 3我们设置了C3为变量x，那么在其它单元格上就可以直接输入带x的方程式了，并且EXCEL会自动调用此单元格内的数据。比如在B3内输入=x^3+27。 4点击数据——模拟分析——单变量求解。 5目标单元格为带有x变量的单元格，即要解方程的单元格。而目标值就设置为0了，其实我们在把方程变成f(x)=0的形式后可以节省很多设置时间。可变单元格，即为输出结果的单元格，这里我们设置成我们设置名称的单元格。然后点击确定即可计算出我们想要的结果。6有些朋友在问如果让输出结果随着我们方程的改变而自动进行计算呢？这里我们就要用到一个宏，首先我们进行录制宏，直接录制这个单变量求解的过程，不需要修改任何数据。点击视图——宏——录制宏，输入宏名(宏1)后直接进行单变量求解的过程录制。 7录制完成后，我们停止录制，再查看宏，对此宏进行编辑，此时EXCEL会打开宏编辑器。8双击你所在编辑的工作表，并输入如下代码： 9Private Sub Worksheet_Change(ByVal Target As Range) 宏1 End Sub 10 11保存后即可得到我们想要的结果了，随意更改公式就可以自动计算结果了。
12这样在EXCEL上完成解一元多次方程还是相当有效和好用的，不过它有一个缺点就是计算结果只是一个近似值，并且在方程中每两个数值或变量之间都必须用符号连接起来(如10*x是不能写成10x的形式的)。 13接下来就是利用规划求解插件进行多元方程组的解方程操作了，规划求解这个插件很好用，但似乎不能达成自动更改单元格之后自动计算的功能，当我们录制了宏之后，在宏中的代码都是红色的，表示错误的，因此我们先只来学习如何进行规划求解操作了，而不执行自动计算。 14将你下载好的规划求解插件解压到office相对应版本内的\Library文件夹里，并执行一下里面的SOLVER.XLA文件。同时打开EXCEL选项的加载项里面的——管理EXCEL加载项，并勾选规划求解。 15此时再打开EXCEL就会多出一个加载项，里面就有我们需要的规划求解插件了。 16点击规划求解，选择目标单元格为包含有所有变量的其中一个方程式，这里不能直接选择方程式，而需要选择等于此方程式的单元格，如图： 17 18可变单元格就是我们要计算结果的变量单元格，这里可以推测，而约束条件也就是约束计算结果或方程式的结果范围了，这里多用于不定式的求解，并且求解结果可选择为最大值，最小值，还是约定值，选项菜单可以设置一些计算精度或计算方式。 19利用此两个工具我们几乎可以求解出所有方程的近似解了，这样对于我们日后的学习和
实验二Ｅｘcel解决整数规划问题 一、问题的提出 某公司拟用集装箱托运甲、乙两种货物,这两种货物每件的体积、重量、获得利润以及托运所受限制如下表所示: 二模型得出 分析：这个问题是一个整数规划问题, 故应该确定决策变量、目标函数及约束条件。 设X1,X2分别为甲乙两种货物托运的件数,显然,X1,X2是非负的整数,这是一个纯整数规划问题，根据问题的要求可知 对于货物总体积的托运限制最大不得超过1365立方英尺,故应有约束条件: 195 Ｘ１+２7３Ｘ２≦1365 对于货物总重量的托运限制为最大不得超过140千克,故应有约束条件为: 4X1+40Ｘ２≦１40 同时有：Xｉ≥0，ｉ=1,2 希望货物托运的配置,使得可获得利润最大,即求W=2Ｘ1+3X2 的最大值 由分析可得如下模型: ＭaxＷ=2X1+3X2 (所获利润最大）约束条件如下 195 X1+2７3 X２≦1365 4X1+40X2≦１40 X i≥0, ｉ＝1,2 Ｘ1≦4 三、模型求解 1．建立规划求解工作表（如下图所示） ⑴．在可变单元格（B４:C４)中输入初始值（１，1） ⑵．在上图有关单元格输入如下公式 单元格地址公式 Ｃ６ =B２*B4+Ｃ2*C４
Ｃ７ =B3＊B4+C3＊C４ C8 =B5*Ｂ4+C5*C4 ⑶.求最佳组合解： ①．选取[工具]→［规划求解…]出现如下对话窗： ②．在“设置目标单元格”窗口，输入C8。 ③．选定“最大值”选项。 ④．在可变单元格中输入B4：C4。 ⑤．选取“添加”，出现“添加约束”窗口,在“添加约束”窗口输入: 单元格引用位置运算符号约束值 B4：Ｃ4 int 单击“添加”,再输入以下约束条件: B4：C４ >= 0 单击“添加”,再输入以下约束条件： B４ >＝ 4 单击“添加”，再输入以下约束条件： C6 <= １365 单击“添加”,再输入以下约束条件： C7 ＜= １40,单击“确定” ⑥在“规划求解参数”窗口，选择“求解。” ⑦选择“确定”,（计算结果如下表所示） ⑧在“规划求解结果”对话框中选定保存“规划求解结果”，单击“确定”。 于是我们就得到如下运算结果报告 四、报告分析 表1 Microｓｏｆt Excel ９.0 运算结果报告 目标函数的初值：当变量X＝(１，1）时目标函数的值。 目标函数的终值：经过运算后的目标函数的最优值。 此表说明函数的最优值为14。 表2可变单元格式 从此表看出我们的最优解（终值）为(4，2）。 --
应用EXCEL规划求解工具进行优化1.线性规划—生产规划： 步骤一：建立模型：每天生产甲乙两种产品分别为X1和X2，数学模型为：目标函数：minf（X1，X2）=60*X1+120*X2 约束条件：9*X1+4*X2<=360 3*X1+4*X2<=300 4*X1+5*X2<=200 -X1<=0 -X2<=0 用EXCEL建立模型如下： 步骤二：规划求解参数确定： 步骤三：选项参数确定：
步骤四：求解： 由上面求解过程可知：X1=20，X2=24时，可使目标函数值最小，即f（X1，X2）=4080. 2.工程下料问题规划求解： 由题意可列出下列方案： 步骤一：设使用8种方案的次数分别为X1，X2，X3，X4，X5，X6，X7和X8，且均为正整数，建立数学模型如下： 目标函数：f（X）=（5*X1+10*X2+25*X3+5*X4+30*X5+10*X6+25*X7+5*X8）/（（X1+X2+X3+X4+X5+X6+X7+X8）*180） 约束条件：gX1=2*X1+X2+X3+X4=100 gX1=2*X2+X3 +3*X5+2*X6+X7 gX1=X1+X3+33*X4 +2*X6+3*X7+5*X8 用EXCEL建立模型如下：
步骤二：规划求解参数确定： 步骤三：选项参数确定： 步骤四：求解： 由上面求解过程可知：X1=23，X2=50，X3=0，X4=4，X5=0，X6=0，X7=0和X8=3时，可使目标函数值最小，即f（X）=0.045139. 3．规划求解—工时安排： 某厂生产A B C三种产品，净利润分别为90元，75元，50元；使用的机时数分别为3h，手工时数分别为4h，3h，2h，由于数量和品种受到制约，机工最多为400h，手工为280h，数量最多不能超过50件，C至少要生产32件。求：如何安排A B C的数量以获得最大利润？
使用Excel规划求解解线性规划问题 引言 最近，开始学习运筹学，期望通过学习后能够解决许多困扰自已的难题。 刚开始时，选了很多教材，最后以Hamdy A.Taha著的《Operations Research:An Introduction》开始学习。（该书已由人民邮电出版社出版，书名《运筹学导论-初级篇（第8版）》，不知为什么，下载链接中只有该书配套的部分习题解答，而书中所说的光盘文件找不到下载的地方，因为中译本没有配光盘，因此也就错过了许多示例文件。不知道哪位有配套光盘文件，可否共享？？？） 线性规划求解的基本知识 线性规划模型由3个基本部分组成： ?决策变量（variable） ?目标函数（objective） ?约束条件（constraint） 示例：营养配方问题 （问题）某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成，含有以下成份： 特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤维。该农场希望确定每天最小成本的饲料配制。 （解答过程） 因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为： x1=每天混合饲料中玉米的重量（磅） x2=每天混合饲料中大豆粉的重量（磅） 目标函数是使配制这种饲料的每天总成本最小，因此表示为： min z=0.3×x1+0.9×x2 模型的约束条件是饲料的日需求量和对营养成份的需求量，具体表示为： x1+x2≥800 0.09×1+0.6×2≥0.3(x1+x2) 0.02×1+0.06×2≤0.05(x1+x2) 将上述不等式化简后，完整的模型为：
min z=0.3×1+0.9×2 s.t.x1+x2≥800 0.21×1-0.3×2≤0 0.03×1-0.01×2≥0 x1,x2≥0 可以使用图解法确定最优解。下面，我们介绍使用Excel的规划求解加载项求解该模型。使用Excel规划求解解线性规划问题 步骤1安装Excel规划求解加载项 单击“Office按钮——Excel选项——加载项——（Excel加载项）转到”，出现“加载宏”对话框，如下图所示。选择“规划求解加载项”，单击“确定”。 此时，在“数据”选项卡中出现带有“规划求解”按钮的“分析”组，如下图所示。 步骤2设计电子表格 使用Excel求解线性规划问题时，电子表格是输入和输出的载体，因此设计良好的电子表格，更加易于阅读。本例的电子表格设计如下图所示：
□财会月刊· 全国优秀经济期刊□·110·2014.8下 在传统财务运营管理中，营运决策包括确定最佳现金持有量、最优订货批量，或者只是考虑单个市场的生产与销售决策。企业集团全球运营管理涉及生产、运输、销售等环节，需要在实现集团利润最大化的同时，解决生产什么产品、在哪里生产、生产多少、运到哪个市场等诸多问题。显然，采用传统的运营管理方法会比较棘手。而Ex?cel 提供的规划求解工具，不但能非常迅速地求出多种营运决策模型的最优解，还可以给出敏感性分析报告，满足财务全球化运营管理的需求，有效提高公司决策效率，同时也能促进财务人员更多地参与到公司管理决策中。 一、问题描述 某跨国集团在中国和其他地区设立了四个工厂，分别为A 、B 、C 、D 厂，产品主要面向国际市场销售，分别销往北京、香港、纽约、东京四个城市。各个工厂的单位产品成本、固定成本、产能，各个市场的销售价格和需求量，以及各个工厂到每个市场的运输成本见图1。 在每个工厂产能允许同时最大限度满足市场需求的情况下，集团管理层希望财务部给出能够实现集团利润 最大化目标的年生产和运输预算的决策方案。 二、建立线性数学模型 1.定义决策变量。下文中，i （i=1，2，3，4）表示工厂，j 表示市场（j=1，2，3，4）；决策问题可以用图2表示。所以定义决策变量为X ij ：即在i 工厂生产的产品投放到j 市场。 2.确定目标函数。最大利润=收入-产品变动成本-其他成本最大利润=55500（X 11+X 21+X 31+X 41）+61100（X 12+X 22+X 32+X 42）+57800（X 13+X 23+X 33+X 43）+62650（X 14+X 24+X 34+X 44）-34900（X 11+X 12+X 13+X 14）-32200（X 21+X 22+X 23+X 24）-38350（X 31+X 32+X 33+X 34）-23400（X 41+X 42+X 43+X 44）-（500X 11+12225X 12+9075X 13+21450X 14+4500X 21+……+15150X 43+5925X 44）。 3.列出约束条件。 （1）产能约束：X 11+X 12+X 13+X 14≤101；X 21+X 22+X 23+X 24≤201；X 31+X 32+X 33+X 34≤121；X 41+X 42+X 43+X 44≤250。 （2）需求约束：X 11+X 21+X 31+X 41≤150；X 12+X 22+X 32+X 42≤75；X 13+X 23+X 33+X 43≤200；X 14+X 24+X 34+X 44≤100。 （3）非负约束：X ij ≥0。4.最优解：最大利润时的X ij 。 三、数据及公式准备 1.数据输入：把图1集团公司的决策数据输入新建的Excel 表中，如图3所示。 耿海利 （江西财经大学会计学院南昌330013） 【摘要】随着全球经济一体化的深入，企业运营管理方式发生了很大变化。本文通过一个实例，来探讨企业集团拥有多个生产子公司、多个产品市场并且各个产品市场价格不同的情况下，企业如何使用Excel 规划求解工具进行产品生产、运输和分配决策，以实现集团利润最大化。 【关键词】规划求解 企业集团全球运营决策敏感性分析 Excel 规划求解： 企业全球运营管理工具 图1 集团基本运营决策数据 图2决策问题
1、生产问题 某工厂生产I，II两种食品，现有80名熟练工人，己知一名熟练工人每小时可生产10千克食品I或8千克食品II。据合同预订，该两种食品每周的需求量将急剧上升，见下表。为此该厂决定到第8周末需培训出60名新的工人，两班生产。已知一名工人每周工作40小时，一名熟练工人用两周时间可培训出不多于三名新工人(培训期间熟练工人和培训人员均不参加生产)。熟练工人每周工资320元，新工人培训期间工资每周180元，培训结束参加工作后工资每周260元，生产效率同熟练工人。在培训的过渡期间，很多熟练工人愿加班工作，工厂决定安排部分工人每周工作80小时，工资每周480元。又若预订的食品不能按期交货，每推迟交货一周的赔偿费食品I为0.4元，食品II 为0.8元。在上述各种条件下，试建立该问题的线性规划模型，以便作出合理全面的安排，使各项费用的总和为最小。 建立该问题的电子表格模型，填写下列电子表格。
2、项目选择问题 某个制药公司需要开发四个新的研究项目，为了使所有项目的成功性最高，派了六位科学家来对这四个项目进行投标选择。每位科学家具有1000点可以投标，投标点数越大，表示科学家对该项目越感兴趣，成功的可能性就越高。投标具体情况如下表所 没有该领域的知识或其他原因而不能从事该项目的研究与开发。目标是使投标总点数最高，应该如何指派。建立该问题的电子表格模型，填写下列电子表格。
某物流公司希望以最小的成本完成一种物资的配送，其运出货物数量、分配量和各段线路单位运输成本如下表所示。另外，由于运输能力限制，从各个工厂到配送中心，以及由配送中心到各个仓库运输产品的数量均不超过60。 建立该问题的电子表格模型，填写下列电子表格。
E X C E L规划求解功能操 作说明 This model paper was revised by the Standardization Office on December 10, 2020
Excel规划求解功能操作说明以Microsoft Excel2003为例，说明使用Excel的求解线性规划问题功能的使用方法。 一、加载规划求解功能 1.点击【工具】按钮，在下拉菜单中选择【加载宏】功能。 2.在弹出的【可加载宏】选项卡中勾选【规划求解】，点击确定按钮。 此时，【工具】下拉菜单中增加规划求解功能，表示加载成功。 二、构造表格Excel表格并填入各项数据 以教材18页【例题2-8】为例，构造表格如下： 1.录入约束条件系数 约束条件（1）为5x 1+x 2 -x 3 +x 4 =3，则在约束系数的第一行的x 1 ,x 2 ,x 3 ,x 4 ,x 5 ，限制条 件，常数b列下分别录入5,1，-1,1,0，=，3如下图所示。 约束系数区的第二行录入约束条件（2）的系数、限制符号及常数b，即- 10,6,2,0,1，=，2； 约束系数区的第三行录入约束条件（3）（x1≥0）的系数、限制符号及常数b，即1,0,0,0,0，≥，0； 约束系数区的第四行录入约束条件（4）（x2≥0）的系数、限制符号及常数b，即0,1,0,0,0，≥，0；
约束系数区的第五行录入约束条件（5）（x3≥0）的系数、限制符号及常数b，即0,0,1,0,0，≥，0； 约束系数区的第六行录入约束条件（6）（x4≥0）的系数、限制符号及常数b，即0,0,0,1,0，≥，0； 约束系数区的第七行录入约束条件（7）（x5≥0）的系数、限制符号及常数b，即0,0,0,0,1，≥，0。如下图所示。 2.录入目标函数系数 目标函数为maxZ=4x 1-2x 2 -x 3 ，则在目标函数的x 1 ,x 2 ,x 3 ,x 4 ,x 5 列下分别录入4，-2，- 1,0,0，如下图所示。 3. 录入约束条件的计算公式 双击约束条件（1）行的“总和”单元格，录入以下内容： “=B3*B12+C3*C12+D3*D12+E3*E12+F3*F12” 说明：录入的内容即是约束条件（1）的计算公式，其中“B3*B12”代表5x 1 ; “C3*C12”代表1x 2；“D3*D12”代表-1x 3 ；“E3*E12”代表1x 4 ；“F3*F12”代表0x 5 。 整个计算公式即代表5x 1+1x 2 -1x 3 +1x 4 +0x 5 ，即约束条件（1）的计算公式。注意：单元格 B12，C12，D12，E12，F12分别代表x 1，x 2 ，x 3 ，x 4 ，x 5
下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。 例1 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束： ● 产品组合使用的资源不能超标。 ● 对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。 下面，我们来考虑让某医药公司的最优产品组合问题。该公司有六种可以生产的药品，相关数据如下表所示。 设该公司生产药品1～6的产量分别为126,,,x x x （磅），则最优产品组合的线性规划模型为 123456 123456123456123456max 6 5.3 5.4 4.2 3.8 1.86543 2.5 1.545003.2 2.6 1.50.80.70.316009609281041..977108410550,16j z x x x x x x x x x x x x x x x x x x x x x s t x x x x j =++++++++++≤??+++++≤??≤?≤??≤??≤?≤??≤??≥≤≤? 下面用规划求解加载宏来求解这个问题： 首先，如下如所示，在Excel 工作表内输入目标函数的系数、约束方程的系数、右端常数项；
其次，选定目标函数单元、可变单元、约束函数单元，定义目标函数、约束函数 其中，劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”，原料约束函数的定义公式是“＝MMULT(B4:G4,J5:J10)”，目标函数的定义公式是“MMULT(B5:G5, J5:J10)”。 注：函数MMULT(B3:G3, J5:J10)的意义是：单元区B3:G3表示的行向量与单元区J5:J10表示的列向量的内积。这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。 最后，打开规划求解参数设定对话框设定模型 （1）（2）目标函数和可边单元的设定很简单，在此就不再赘述 （3）约束条件的设定 (3.1) 约束条件1234561234566543 2.5 1.545003.2 2.6 1.50.80.70.31600x x x x x x x x x x x x +++++≤??+++++≤? 的设定： 系数矩阵 目标函数的系数 系数矩阵右端常数 可变单元 约束函数单元 目标函数单元
Excel规划求解功能操作说明 以Microsoft Excel2003为例，说明使用Excel的求解线性规划问题功能的使用方法。 一、加载规划求解功能 1.点击【工具】按钮，在下拉菜单中选择【加载宏】功能。 2.在弹出的【可加载宏】选项卡中勾选【规划求解】，点击确定按钮。
此时，【工具】下拉菜单中增加规划求解功能，表示加载成功。 二、构造表格Excel表格并填入各项数据
以教材18页【例题2-8】为例，构造表格如下： 标题栏 约束条件区 目标函数区 计算结果显示区 1.录入约束条件系数 约束条件（1）为5x1+x2-x3+x4=3，则在约束系数的第一行的x1,x2,x3,x4,x5， 限制条件，常数b列下分别录入5,1，-1,1,0，=，3如下图所示。 约束系数区的第二行录入约束条件（2）的系数、限制符号及常数b，即 -10,6,2,0,1，=，2； 约束系数区的第三行录入约束条件（3）（x1≥0）的系数、限制符号及常数
b，即1,0,0,0,0，≥，0； 约束系数区的第四行录入约束条件（4）（x2≥0）的系数、限制符号及常数b，即0,1,0,0,0，≥，0； 约束系数区的第五行录入约束条件（5）（x3≥0）的系数、限制符号及常数b，即0,0,1,0,0，≥，0； 约束系数区的第六行录入约束条件（6）（x4≥0）的系数、限制符号及常数b，即0,0,0,1,0，≥，0； 约束系数区的第七行录入约束条件（7）（x5≥0）的系数、限制符号及常数b，即0,0,0,0,1，≥，0。如下图所示。 2.录入目标函数系数 目标函数为maxZ=4x1-2x2-x3，则在目标函数的x1,x2,x3,x4,x5列下分别录入4，-2，-1,0,0，如下图所示。
Excel规划求解工具在多目标规划中的应用 摘要：多目标决策方法是从20世纪70年代中期发展起来的一种决策分析方法。该方法已广泛应用于人口、环境、教育、能源、交通、经济管理等多个领域。文章采用多目标决策方法中分层序列法的思想，应用excel的规划求解工具，对多目标规划问题进行应用研究，并以实例加以说明。 abstract： multi-objective decision method is a kind of decision analysis method from the mid 1970s. the method has been widely used in population， environment， education，energy， traffic， economic management， and other fields. this paper uses the lexicographic method of multi-objective decision method and makes some researches on the multi-objective problem using the excel solver tool and an example to illustrate. 关键词： excel规划求解；多目标规划；分层序列法 key words： excel solver；multi-objective programming；the lexicographic method 中图分类号：tp31 文献标识码：a 文章编号：1006-4311（2013）21-0204-02 0 引言 excel中的规划求解工具只能对单目标的问题进行求解。当遇到多目标问题时，可以把多目标问题先转化为单目标问题，然后求解。
实验报告 证券投资 学院名称 专业班级 提交日期 评阅人____________ 评阅分数____________
实验五：运用Excel规划求解进行最优投资组合的求解 【实验目的】 1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序； 2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合； 【实验条件】 1、个人计算机一台，预装Windows操作系统和浏览器； 2、计算机通过局域网形式接入互联网； 3、matlab或者Excel软件。 【知识准备】 理论知识：课本第三章收益与风险，第四章投资组合模型，第五章CAPM 实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章，第四章，第五章 【实验项目容】 请打开参考《金融建模—使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验 A．打开“实验五组合优化.xls”，翻到“用规划求解计算最优组合”子数据表； B．调用规划求解功能进行求解。 点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才能使用，如何加载见实验补充文档“EXCEL规划求解功能的安装”。 C．
D．在规划求解选项卡里面选择“选项”，再选择“非负”再运行一次，比较两次返回的投资比例值的正负。在实验报告中记录两次得到的最优投资组合，并说明投资比例是负值说明什么？ E．（选做）借助连续调用规划求解的VBA过程生成有效组合以及资本市场线。 参考实验参考电子书《金融建模—使用EXCEL和VBA》电子书第四章P83 F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？ 【实验项目步骤与结果】 A.
第三节 使用Excel 求解线性规划问题 利用单纯形法手工计算线性规划问题是很麻烦的。office 软件是一目前常用的软件，我们可以利用office 软件中的Excel 工作表来求解本书中的所有线性规划问题。对于大型线性规划问题，需要应用专业软件，如Matlab ，Lindo ，lingo 等，这些软件的使用这里我们不作介绍，有需要的，自己阅读有关文献资料。 用Excel 工作表求解线性规划问题，我们需要先设计一个工作表，将线性规划问题中的有关数据填入该工作表中。所需的工作表可按下列步骤操作： 步骤1 确定目标函数系数存放单元格，并在这些单元格中输入目标函数系数。 步骤2 确定决策变量存放单元格，并任意输入一组数据。 步骤3 确定约束条件中左端项系数存放单元格，并输入约束条件左端项系数。 步骤4 在约束条件左端项系数存放单元格右边的单元格中输入约束条件左端项的计算公式，计算出约束条件左端项对应于目前决策变量的函数值。 步骤5 在步骤4的数据右边输入约束条件中右端项（即常数项）。 步骤6 确定目标函数值存放单元格，并在该单元格中输入目标函数值的计算公式。 例 建立如下线性规划问题的Excell 工作表： 12 121 21212max 1502102310034120..55150,0 z x x x x x x s t x x x x =++≤??+≤??+≤??≥? 解：下表是按照上述步骤建立的线性规划问题的Excell 工作表。 其中： D4=B2*B4+C2*C4, D5=B2*B5+C2*C5 , D6=B2*B6+C2*C6, C7= B2*B1+C2*C1 。 建立了Excel 工作表后，就可以利用其中的规划求解功能求相应的线性规划问题的解。求解步骤如下： 步骤1 单击[工具]菜单中的[规划求解]命令。 步骤2 弹出[规划求解参数]对话框，在其中输入参数。置目标单元格文本框中输入目标单元格；[等于]框架中选中[最大值＼最小值］单选按钮。 步骤3 设置可变单元格区域，按Ctrl 键，用鼠标进行选取，或在每选一个连续区域后，在其后输入逗号“，”。 步骤4 单击[约束］框架中的[添加]按钮。 步骤5 在弹出的[添加约束]对话框个输入约束条件． 步骤6 单击[添加]按钮、完成一个约束条件的添加。重复第5步，直到添加完所有条件 步骤7 单击[确定]按钮，返回到[规划求解参数]对话框，完成条件输入的[规划
利用Excel 进行规划求解 Excel 具有规划求解的基本功能，包括线性规划和非线性规划。对于常规的线性规划问题，Excel 就可以给出求解结果。对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab 了。不过，大多数规划问题Mathcad 即可完成所赋予的任务。利用Excel 求解规划问题有些“罗嗦”，但也不难掌握。下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。 【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。有关生产情况的各种数据资料见下表。 品种 工效（h ） 储藏量（m 3） 定点量(件)* 收益（元） 果汁杯 6 h/百件 10 m 3/百件 600件 600元/百件 鸡尾酒杯 5 h/百件 20 m 3/百件 0件 400元/百件 *注：定点量为每周生产的最大数量。 若每周工作不超过50小时，且拥有储藏量为140m3的仓库。问： ⑴ 该个体户如何安排工作时间才能使得每周的收益最大？ ⑵ 若每周多干1小时，收益增大多少？ ⑶ 通过加班加点达到的收益极限是多少？ 解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下： 21400600)(Max x x x f += ???????≥≥≤≤+≤+0 ,06 14020105056 s.t.2112121x x x x x x x 显然，约束条件中的第三个式子x 1≤6可以表作1*x 1+0*x 2≤6，从而有如下矩阵 ??????=400600c ，??????=21x x x ，??????????=01201056A ，???? ??????=614050b 容易看到，上述模型表为矩阵形式便是： 目标函数为 []?? ????==21400600)(Max x x x c x f T 约束条件为 ???? ?????≥??????=??????????=≤??????????=061405001201056 s.t.21x x x b Ax
题目 如何利用EXC E L求解线性规划问题及其灵敏度分析 第 8 组 姓名学号 乐俊松 090960125 孙然 090960122 徐正超 090960121 崔凯 090960120 王炜垚 090960118 蔡淼 090960117 南京航空航天大学（贸易经济）系 2011年（5）月（3）日
摘要 线性规划是运筹学的重要组成部分，在工业、军事、经济计划等领域有着广泛的应用，但其手工求解方法的计算步骤繁琐复杂。本文以实际生产计划投资组合最优化问题为例详细介绍了Excel软件的”规划求解”和“solvertable”功能辅助求解线性规划模型的具体步骤，并对其进行了灵敏度分析。
目录 引言 (4) 软件的使用步骤 (4) 结果分析 (9) 结论与展望 (10) 参考文献 (11) 1. 引言
对于整个运筹学来说，线性规划(Linear Programming)是形成最早、最成熟的一个分支，是优化理论最基础的部分，也是运筹学最核心的内容之一。它是应用分析、量化的方法，在一定的约束条件下，对管理系统中的有限资源进行统筹规划，为决策者提供最优方案，以便产生最大的经济和社会效益。因此，将线性规划方法用于企业的产、销、研等过程成为了现代科学管理的重要手段之一。[1] Excel中的线性规划求解和solvertable功能并不作为命令直接显示在菜单中，因此，使用前需首先加载该模块。具体操作过程为：在Excel的菜单栏中选择“工具／加载宏”，然后在弹出的对话框中选择“规划求解”和“solvertable”，并用鼠标左键单击“确定”。加载成功后，在菜单栏中选择“工具／规划求解”，便会弹出“规划求解参数”对话框。在开始求解之前，需先在对话框中设置好各种参数，包括目标单元格、问题类型(求最大值还是最小值)、可变单元格以及约束条件等。 2 软件的使用步骤 “规划求解”可以解决数学、财务、金融、经济、统计等诸多实 际问题，在此我们只举一个简单的应用实例，说明其具体的操作 方法。 某人有一笔资金可用于长期投资,可供选择的投资机会包括购买国库券、公司债券、投资房地产、购买股票或银行保值储蓄等。投资者希望投资组合的平均年限不超过5年，平均的期望收益率不低于13%，风险系数不超过4，收益的增长潜力不低于10%。问在满足上述要求的前提下投资者该如何选择投资组合使平均年收益率最高？（不同的投资方式的具体参数如下表。）
突破EXCEL的规划求解局限 [摘要] 笔者以角钢下料为题，在EXCEL下调用LINGO函数构建模型并求解，在针对多原材料多零件条件下的一维下料方面取得了较好的效果，突破了EXCEL的规划求解局限。 [关键词] 规划局限 在实际生产生活中，经常会遇到将原材料通过切割、剪裁等手段加工成所需的大小零件的情况。如果对如何下料事先缺乏周密考虑，将会产生较多的边角废料，使材料利用率不高，利用Microsoft Excel的“规划求解”工具能够对很多小型规划进行求解。但是由于该“规划求解”工具是由第三方提供，其极限迭代次数仅为32767次，在运算一些大型规划时，难免会产生力不从心的感觉。而且，受EXCEL提供的函数在描述较大型的数学模型时没有较好的函数可以应用，常常需要逐个对约束条件进行列举描述。 采用美国LINDO公司开发的LINGO软件则没有这方面的限制。LINGO是一种最优化问题的建模语言，包括许多有用的函数供使用者监理优化模型时调用，并提供与其他数据文件的接口，能方便地输入、求解和分析数学规划的问题。 LINGO除了可用于求解线性规划及二次规划外，还可用于非线性规划求解、一些线性和非线性方程（组）的求解等。其最大特色在于它允许优化模型中的决策变量为整数（即整数规划），而且执行速度快。 在实际生产生活中，将EXCEL易用性与LINGO专业性结合起来，以EXCEL 作为人机界面，通过后台调用LINGO数学模型对问题进行求解可达到较满意的效果。 笔者以角钢下料为题，采用@ole函数在EXCEL下调用LINGO函数构建模型并求解，在针对多原材料多零件条件下的一维下料方面取得了较好的效果。 其LINGO模型运行步骤如下： 首先，获取单一规格下精确的零件需求数量与长度。 其次，获取市场上该规格所有原材料的长度。 再次，拟定可以接受的余料率（推荐经验数据为0.05至0.08之间，即可接受的下料备选方案材料利用率至少在92%以上。因为LINGO在计算整数非线性规划时速度较慢，如果放任其搜索全局最优解，则会由于整个求解过程耗时过长而失去该模型在实际工作中的指导意义。笔者在实证分析阶段，曾用十五个小时得到一个全局最优解，而这一个全局最优解的材料利用率仅比用七秒钟得到的一个局部最优解的材料利率提高不到一个百分点）。
使用Excel 规划求 解 2000 年10 月 Excel 规划求解的选项可以用来解决线性规划与非线性规划优化问题。可以设置决策变量为整数约束。规划求解可以用来解决最多有200 个变量，100 个外在约束和400 个简单约束（决策变量整数约束的上下边界）的问题。要调用规划求解，从主菜单中选择工具/求解。规划求解参数对话框如下所示。 规划求解参数对话框 规划求解参数对话框用来描述EXCEL 的优化问题。设置目标单元格应该包含正考虑问 题目标函数的单元格地址。选择最大或最小可以用来确定设定目标单元格的寻找最大或最小值。如果选择了值，规划求解将努力去寻找使目标单元格的值等于选项右侧框中的值。可变单元格框应该包含问题中决策变量的地址。最后，约束必须通过点击添加按钮在约束框中详细说明。修改按钮允许你对已经加入的约束进行修改，删除按钮允许你删除前面加入的约束。重新设置按钮清除当前问题，并且将参数重新设置为默认值。选项按钮调用规划求解对话框选项（下面就讨论）。推测按钮选项对于我们没有多大的用途，这里将不讨论。为了便于参考，规划求解参数对话框的相关条目标注如下：
当点击添加按钮时，增加约束对话框如下所示： 点击单元格引用框允许你说明单元格地址（通常是有公式的单元格）。约束形式可以选择下面的箭头（<=,>=,=,int,int 是指整数,或bin,指二元）。约束框可以含有单元格的公式，简单的单元引用，或者数值。添加按钮向现存模型增加当前描述的约束，返回添加约束对话框。 OK 按钮将当前的约束加入到模型中，并返回规划求解对话框。注意：规划求解并不假定决策变量是非负的。下面讨论的选项对话框能设定变量必须非负。如果从规划求解参数对话框选中了选项按钮，将会出现下面的对话框：
§9.6 Excel软件“规划求解”的使用 用Excel软件的“规划求解”功能可以方便地求解线性规划、整数规划和非线性规划问题。但如果安装Office 97时采用的是典型安装方法，则【工具】菜单中是无“规划求解”功能项的。可参照§2.8中介绍的方法将未安装的组件安装完整。 下面以第八章例8.1为例介绍用Excel求解线性规划的操作步骤和运行输出结果的分析。 一．求解线性规划的操作过程 1．输入数据、公式和说明文字 （1）在工作表中按图9.7所示格式输入必要的说明文字（图中粗体字部分）和LP模型的原始数据（图中虚线框所示单元格内，注意并不需要化为标准型）；图中F4是放置目标函数的单元格，B5:D5是放置决策变量X1、X2、X3（既“可变单元格”）的区域。 图9.7 （2）在F4单元格内输入目标函数X0的计算公式： =B4*B5+C4*C5+D4*D5 或=SUMPRODUCT（B4:D4,B5:D5） 其中SUMPRODUCT（）函数返回两个或多个区域（即数组）中对应单元格乘积之和的值。该函数可在Excel的“数学和三角函数”中找到。 （1）在E8单元格中输入第一个约束条件左端的计算公式： =B8*$B$5+ C8*$C$5+D8*$D$5
或= SUMPRODUCT（B8:D8,$B$5:$D$5） 然后拖曳E8的填充柄将公式复制到E9、E10单元格（注意公式中的B5、C5、D5或B5:D5要使用绝对引用）。 当模型中的变量数较多时，使用SUMPRODUCT（）函数可大大加快以上两个公式的输入速度。 说明：图中粗线框是表示要输入公式的单元格。用Excel求解线性规划的数据输入格式可由用户自行设计，但以上介绍的格式不仅与我们所熟悉的LP模型相似，便于理解和使用；而且便于在对话框中输入约束条件。按以上格式输入说明文字后，还可以使系统所输出的三个运行结果报告更具可读性。 2．选【工具】→“规划求解”，“打开规划求解参数”对话框，见图9.8。 图9.8 （1）在“设置目标单元格”文本框中输入目标单元格（建议用鼠标选定的方法输入，下同），并选系统默认的“最大值”单选纽； （2）在“可变单元格”文本框中输入B5:D5（既指定决策变量所在的单元格）； 3．单击“约束”框中的〈添加〉按纽，打开“添加约束”对话框，见图9.9。 图9.9 （1）在“单元格引用位置”文本框中输入E8:E10；打开约束类型下拉列表框，选“〈=”；在“约束值”文本框中输入F8:F10；
附录4 Excel“规划求解” 1. 在系统中安装“规划求解” 1、启动EXCEL。打开“工具”菜单。如果没有“规划求解”，单击“加载宏”。 弹出以下窗口： 2、在复选框中选中“规划求解”，单击“确定”后返回Excel。
这时在“工具”菜单中出现“规划求解”。关闭“工具”菜单 2. 在Excel中创建线性规划模型 1、输入线性规划模型的约束条件系数、右边常数和目标函数系数。定义线性规划的变量单元格、约束条件左边单元格和目标函数单元格。 2、定义“设备能力占用”（即约束条件左边）以及“总利润”的计算公式。 首先定义设备A的“能力占用”单元格（G3）的计算公式，界面如下：
其次定义设备B的“能力占用”单元格（G4）的计算公式，界面如下： 再次定义设备C的“能力占用”单元格（G5）的计算公式，界面如下：
最后定义“总利润”单元格（C8）的计算公式，界面如下： 3、将光标停留在“总利润”值的单元格（C8）中，打开“工具/规划求解”，弹出以 下窗口： 4、设置目标函数单元格：
检查“设置目标函数单元格”是否在“$C$8”，如不是，单击文本框右侧的图标，重新选定目标函数单元格，也可以直接单击Excel表中的“C8”。 5、设置变量： 单击“规划求解窗口”中“可变单元格”文本框，然后在Excel工作表中选定变量单元格（C7、D7、E7和F7），在文本框中出现“$C$7:$F$7”，如下图所示。 6、设置约束
单击“添加”，弹出以下窗口： 单击“单元格引用位置”文本框空白处，然后单击工作表G3单元格，“单元格引用位置”文本框中出现“$G$3”；打开“单元格引用位置”和“约束值”之间的下拉文本框，选定“<=”；单击“约束值”文本框空白处，然后单击工作表H3单元格。结果如下图所示。 单击“添加”，完成第一个约束设置。 继续设置第二、第三个约束，最后设置所有变量非负。约束设置完成以后，单
Solve Linear Programming Problems Check that Solver is installed Open Excel Click on the ‘tools’ menu If Solver is listed, then go to Formulation. Otherwise, Solver needs to be installed, as follows: Again under ‘tools’ click ‘Add-ins..’. The window that appears lists the available add-ins, Click the box next to Solver so that it contains a tick, click ok. Solver should now appear under the ‘tools’ menu Formulation Whenever we formulate a worksheet model of a linear program, we perform the following steps (Par. problem as an example, see appendix): Step 1: Enter the data in the worksheet Cells B7:C10 show the production requirements per unit for each product. Cells B5:C5 show the profit contributions per unit for the two products. Cells F7:F10 show the number of hours available in each department. Step 2: Specify cell locations for the decision variables Cells B4:C4. Step 3: Select a cell and enter a formulation for computing the objective value function. Cell D5: =B4*B5+C4*C5 or SUMPRODUCT($B$4:$C$4,$B5:$C5) Step 4: Select a cell and enter a formulation for computing the left-hand side of each constraint. Cell D7:=B4*B7+C4*C7 or SUMPRODUCT($B$4:$C$4,$B7:$C7) (copy from Cell D5) Cell D8:=B4*B8+C4*C8 or SUMPRODUCT($B$4:$C$4,$B8:$C8) (copy from Cell D5) Cell D9:=B4*B9+C4*C9 or SUMPRODUCT($B$4:$C$4,$B9:$C9) (copy from Cell D5) Cell D10:=B4*B10+C4*C10 or SUMPRODUCT($B$4:$C$4,$B10:$C10) (copy from Cell D5) Tips: (1)SUMPRODUCT function requires specifying two cell ranges of equal size, separated by a comma, such as SUMPRODUCT($B$4:$C$4,$B5:$C5). The SUMPRODUCT function computes the products of the first entries in each range, second entries in each range, and so on. It then sums these products. (2) The $ symbol in the cells keeps that cell reference fixed when we copy the formula. This is especially convenient since the formula for calculating the sum of the left-hand-side value for each constrain also follows the same structure as the objective function. Excel Solution The following steps show how Solver can be used to obtain the optimal solution to the Par, Inc., problem. Step 1: Select the Tools pull-down menu. Step 2: Select the Solver option. Step 3: When the Solver Parameters dialog box appears. Enter D5 into the Set Cell box Select the Equal to: Max option Enter B4:C4 into the By Changing Variable Cells box. Select Add. Step 4: When the Add Constraint dialog box appears: Enter D7:D10 in the Cell Reference box Select <= Enter F7:F10 into the Constraint box Click OK Step 5: When the Solver Parameters dialog box reappears: Choose Options. Step 6: When the Solver Options dialog box appears,