EXCEL规划求解问题

《EXCEL规划求解问题》由会员分享，可在线阅读，更多相关《EXCEL规划求解问题（7页珍藏版）》请在金锄头文库上搜索。

1、使用 Excel 规划求解2000 年 10 月Excel 规划求解的选项可以用来解决线性规划与非线性规划优化问题。可以设置决策变 量为整数约束。规划求解可以用来解决最多有 200 个变量，100 个外在约束和 400 个简单约 束（决策变量整数约束的上下边界）的问题。要调用规划求解，从主菜单中选择工具/求解。 规划求解参数对话框如下所示。规划求解参数对话框规划求解参数对话框用来描述 EXCEL 的优化问题。 设置目标单元格应该包含正考虑问 题目标函数的单元格地址。 选择最大或最小可以用来确定设定目标单元格的寻找最大或最小 值。如果选择了值，规划求解将努力去寻找使目标单元格的值等于选项右侧框中

2、的值。可变 单元格框应该包含问题中决策变量的地址。 最后， 约束必须通过点击添加按钮在约束框中详 细说明。 修改按钮允许你对已经加入的约束进行修改， 删除按钮允许你删除前面加入的约束。 重新设置按钮清除当前问题， 并且将参数重新设置为默认值。 选项按钮调用规划求解对话框 选项（下面就讨论） 。推测按钮选项对于我们没有多大的用途，这里将不讨论。为了便于参 考，规划求解参数对话框的相关条目标注如下：当点击添加按钮时，增加约束对话框如下所示：点击单元格引用框允许你说明单元格地址（通常是有公式的单元格） 。约束形式可以选 择下面的箭头（=,=,int,int 是指整数,或 bin,指二元） 。约束框可

3、以含有单元格的公式，简 单的单元引用， 或者数值。 添加按钮向现存模型增加当前描述的约束， 返回添加约束对话框。 OK 按钮将当前的约束加入到模型中，并返回规划求解对话框。注意：规划求解并不假定决 策变量是非负的。下面讨论的选项对话框能设定变量必须非负。 如果从规划求解参数对话框选中了选项按钮，将会出现下面的对话框：最长时间允许在规划求解停止之前设定秒数。 类似于最长时间， 迭代次数允许设定迭代 的最大数量（规划求解算法的步骤） 。精确度是规划求解算法的准确程度（例如，在等于约 束右端项之前，约束左边项的值如何接近） 。允许误差被用于整数规划。这指定了在多大百 分比内的解是最优的。如果寻求最优

4、解，允许误差必须被设置为 0。如果运行时间太长，希 望将此设置为一个更大的值（如果可以在最优性的百分比内接受该解） 。 如果模型是线性规划或者线性整数规划， 应该选择线性模型假定。 这样规划求解就用单 纯形法而不是更耗时间的非线性算法进行求解（一般简约梯度法） 。如果希望所有的可变单 元格都=0，应该选择非负性。如果希望察看逐次迭代的信息（这将使速度降低） ，选中显 示迭代结果。如果模型比例比较差，启用自动按比例缩放（如果输入的数量级显著不同） 。 最后，对话框的底部是关于非线性算法的选项，即，如何估计非线性，如何估计变化速率和 应用的搜索技术。 一般说来，大多数参数的默认值运行得很有效。重要

5、的是，如果是线性规划或者整数规 划， 要记住选择线性模型假设。 如果希望可变单元格仅仅取非负值， 可以选择非负性。 同时， 如果解整数规划问题，并求最优解，要确定允许误差为 0%。例子：科罗拉多牛公司1 科罗拉多牛公司可以从批发商购买 3 种原料。公司对于奶牛喂养的脂肪、蛋白质、钙 和铁有一定的营养要求。每头奶牛每天至少要 10 单位钙，不多于 7.5 单位脂肪，至少 12 单 位铁和至少 15 单位蛋白质。下面的表格给出了 3 种养料每磅中脂肪、蛋白质、钙和铁的含 量。等级 1 的养料成本为每磅$0.25，等级 2 成本为每磅$0.1，等级 3 成本为每磅$0.08。牛 可以混合 3 种养料

6、来喂养。科罗拉多牛公司希望尽可能的以低廉的成本喂养牛。 科罗拉多牛公司数据喂养成分（单位/磅）等级 1等级 2等级 3钙0.70.80铁0.90.80.8蛋白质0.81.50.9脂肪0.50.60.4- 该问题的一个线性规划模型如下： 令grade1=每天一头牛所用的等级 1 的养料数量（磅） grade2=每天一头牛所用的等级 2 的养料数量（磅） grade3=每天一头牛所用的等级 3 的养料数量（磅） Minimize0.25grade1+0.1grade2+0.08grade3Subject to0.7grade1+0.8grade2+0grade3=10(钙) 0.9grade1+0

7、.8grade2+0.8grade3=12(铁) 0.8grade1+1.5grade2+0.9grade3=15(蛋白质) 0.5grade1+0.6grade2+0.4grade3=0该问题的 EXCEL 工作表模型如下：1 该例子源于 Camm,J.D.与 Evans,J.R 的管理科学，模型，分析与理解，西南出版公司，新新那提，俄亥俄 州，1996 年。本工作表中的单元格公式仅仅出现在模型的输出（即约束）部分：SUMPRODUCT函数计算点积。例如应用钙的数量为SUMPRODUCT(B7:D7,B15:D15) = ( B7*B15 + C7*C15 + D7*D15 )。运用下面的步

8、骤可以建立线性规划模型： 1.选择Tool，然后选择Solver。2.点击设置目标单元格，键入B26。 3.点击Min。 4.点击By Changing Cell框，在工作表中，点击并从B15到D15（或者键入B15:D15）脱 拽鼠标。 5.点击Add按钮调用Add Constraint对话框。6.键入最低要求约束：在单元格引用框中点击并键入B20:B22，选择=类，然后点击 约束框并且键入F7:F9。点击Add。 7.键入最高要求约束：在单元格引用框中点击并键入B24，选择=类，然后点击约束 框并且键入G10。点击OK按钮。 8.最后选择Options，假定非负性和线性模型。在Solver

9、 Parameter对话框中完成的模型如下所示：要解该模型，点击Solver按钮。当问题解决以后，Solver Result框将会出现：你可以选择工作表中由Solver确定的解或者重新保存元数据。同时，如Report框中所示， 可以自动产生3种报告。在框中点击可以选择其中的任何报告，在下面的部分中，将逐一描 述报告。 规划求解报告Golver产生了线性规划的3份报告，答案、灵敏度分析和极限报告。如果从Solver结果框 里选中了Keep Solver Solution选项，那么最优解将保存在工作表中。对于科罗拉多牛公司问 题，将会有下面的结果。 答案报告给出了目标单元格和所有可变单元格的初始值

10、和最终值， 以及约束和约束状态 的列表（注意：术语松弛可用来描述松弛变量和剩余变量） 。科罗拉多牛公司模型的答案报 告见下页。注意EXCEL通过单元格左侧和上面的第一个文本框标记目标单元格，可变单元 格和约束。 Microsoft Excel8.0答案报告 工作表：CCC.xlsCCC 报告创建：12/11/978：33：07AM灵敏度分析报告给出了每个可变单元格的最优值、 检验数、 目标函数系数以及当前最优 解不变时目标函数系数的增减量（其他保持不变） 。例如，在科罗拉多牛公司问题的灵敏度 分析报告中，grade1的成本可以最多减少0.1425，而当前解仍然最优。每个约束的影子价格（约束右端

11、项每增加一单位引起的目标函数增加） 根据该营子价格有效时的右端项值的增减 给出。例如，在下面给出的科罗拉多牛公司问题的灵敏度分析报告中，脂肪量增加0.5单位（在约束1的允许增建范围内）将会导致目标函数的变化为(.5)x(-1.14) = -.57。Microsoft Excel8.0灵敏度分析报告 工作表：CCC.xlsCCC 报告创建：12/11/978：33：07AM极限报告 （科罗拉多牛公司问题的极限报告如下所示） 给出了当其他可变单元格保持当 前值时， 每个可变单元格满足约束的最低和最大变动限度。 下面的报告显示科罗拉多牛公司 问题的解是很紧的 （两个可变单元格固定时， 第三个单元格的最低和最大变动限度等于目标 值） 。Microsoft Excel8.0极限报告 工作表：CCC.xlsCCC 报告创建：12/11/978：33：07AM解整数与非线性规划解整数线性模型、非线性模型、以及非线性整数模型可以运用前面叙述的技术来完成。 要指定约束为整数，调用Add Constraint对话框，选择Cell Reference中的可变单元格，选择 int(整数)类型（对于二元变量选择bin） 。对于非线性模型，那么就不要在Option对话框中选 中Assume Linear Model。