ecel求解线性规划问题

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

1、第五章 利用EXCEL求解线性规划问题 Ø 建立线性规划问题的模型 Ø 利用EXCEL求解线性规划问题 Ø 分析运算结果（敏感性分析） 目的： 一、EXCEL 基本知识 1、命名工作表 功能： 存储信息、进行计算、排序数据、用图或表的形 式显示数据、规划求解、财会分析、概率与统计分析等 等 （1）激活工作表1，单击sheet 1 标签 （2）命名工作表 或 （3）Format / sheet Rename 显示Rename sheet 对话框 （4）键入工作表名 2、工作表 4、进行计算 Excel-2003 256列 65535 行 Excel-2007 163

2、84列 1048576行 3、向单元格中输入数据 常数：不能改变的文字或其它数据 公式：输入到单元格中的数字值，或返回数字值的表达式 。 （1）输出数据时计算 A1 =456+789 （2）根据其它单元格的数据进行计算 A3456 B3-789 C3=A3+B3 5、在公式中引用其它单元格 在公式中可以引用本工作簿中任何单元格或单元格 组的数 据，也可以引用其它工作簿中任何单元格或 单元格组的数据。引用单元格数据后，公式的运算 值将随着被引用的单元格数据变化而变化。 （1）引用的类型 三种类型 ： 相对引用、 绝对引用、混合引用 格式： A3 、B6 （2） 相对引用 使用相对引用后，系统将会

3、记住建立公式的单元格和被 引用的单元格的相对位置，在复制这个公式时，新的公式单 元和被引用的单元仍然保持这种相对位置。 （3）绝对引用 绝对引用是指被引用的单元与引用的公式单元的位置 关系是绝对的，无论将这个公式复制到任何单元，公式所 引用的还是原来单元格的数据。 （4）混合引用 格式：$a$3 $d$5 格式： $A3 列是绝对的，行是相对的 B$ 3 列是相对的，行是绝对的 使用Excel进行求解 1.关于“规划求解” 2.如何加载“规划求解” 3. “规划求解”各参数设置 4. “规划求解”步骤 5. 利用“规划求解”解线性规划问题 2.如何加载“规划求解” 1) 在“工具”菜单上,单击

4、“加载宏” 2) 在弹出的对话框中的“可用加载宏”列表框中, 选定待添加的加载宏“规划求解”选项旁的复选 框,然后单击“确定”.单击“确定”后,“工具”菜单下 就会出现一项“规划求解” 3. “规划求解”各参数设置 单击“规划求解”按钮,将会出现以下规划求解 参数设置对话框 ? 单击“添加”,显示添加约束对话框 ? 选项:显示”规划求解选项”对话框.在其中可以 加载或保存规划求解模型,并对规划求解过程的 高级属性进行控制 线性规划求解步骤： 1. 确定目标函数系数存放单元格，并在这些单元格中输入目标 函数系数。 2. 确定决策变量存放单元格，并任意输入一组数据； 3. 确定约束条件中左端项系数

5、存放单元格，并输入约束条件左 端项系数； 4. 在约束条件左端项系数存放单元格右边的单元格中输入约束 条件左端项的计算公式，计算出约束条件左端项对应于目前决 策变量的函数值。 5. 在步骤4的的数据右边输入约束条件中右端项（即常数项） 6. 确定目标函数值存放单元格，并在该单元格中输入目标函数 值电容计算公式。 一个简单的例子 ? 某工厂计划生产两种产品，利润分别为2和3，已知生 产单位产品所需的设备台时和A、B两种原材料的消耗， 如表 产产品1产产品2 设备设备128台时时 原材料A4016KG 原材料B0412KG ? 目标是不超过资源限制的情况下，确定两产品产量， 得到最大利润。 建立数

6、学公式（步骤一） ? 在工作表的顶部输入数据 ? 确定每个决策变量所对应 的单元格位置 ? 选择单元格输入公式，找 到目标函数的值 ? 确定约束单元格输入公式 ，计算每个约束条件左边的 值 ? 确定约束单元格输入公式 ，计算每个约束条件右边的 值 可采用 复制粘贴 或 直 接输入 的方式导入数据。 建立数学公式（步骤二） ? 在工作表的顶部输入数据 ? 确定每个决策变量所对应 的单元格位置 ? 选择单元格输入公式，找 到目标函数的值 ? 选择一个单元格输入公式 ，计算每个约束条件左边的 值 ? 选择一个单元格输入公式 ，计算每个约束条件右边的 值 图中，规定B12、C12 为可变单元格 可变单

7、元格存放决策变量的取值，可变单元格数目等于决策变量个数 建立数学公式（步骤三） ? 在工作表的顶部输入数据 ? 确定每个决策变量所对应 的单元格位置 ? 选择单元格输入公式，找 到目标函数的值 ? 确定约束单元格输入公式 ，计算每个约束条件左边的 值 ? 确定约束单元格输入公式 ，计算每个约束条件右边的 值 在目标单元格中，需要填入计算目标函数值的公式。 建立数学公式（步骤四） ? 在工作表的顶部输入数据 ? 确定每个决策变量所对应 的单元格位置 ? 选择单元格输入公式，找 到目标函数的值 ? 确定约束单元格输入公式 ，计算每个约束条件左边的 值 ? 确定约束单元格输入公式 ，计算每个约束条件

8、右边的 值 在约束单元格中，需要填入计算约束函数值的公式。 建立数学公式（步骤五） ? 在工作表的顶部输入数据 ? 确定每个决策变量所对应 的单元格位置 ? 选择单元格输入公式，找 到目标函数的值 ? 确定约束单元格输入公式 ，计算每个约束条件左边的 值 ? 确定约束单元格输入公式 ，计算每个约束条件右边的 值 调用 规划求解 模块 ? 选择工具下拉菜单 ? 选择规划求解选项（事先 需用Office安装盘安装规划 求解的功能） 填写目标单元格和可变单元格 ? 出现规划求解参数对话框 在目标单元格中输入B14 在等于选择最大 在可变单元格中输入 B12:C12 选择添加 在上图显示的界面中，需要

9、输入目标单元格、可变单元格， 添加约束条件，另外还可能需要进行选项设置。 添加约束 ? 在添加约束对话框中，在 单元格引用位置中输入B17 ，选择=，在约束值中输 入D17。选择添加 ? 第三个条件添加完毕后， 选择确定 ? 当规划求解参数对话框重 新出现时，选择选项 “选项”设置 ? 当选项对话框出现时，选 择假设非负。选择确定 用Excel求解 ? 出现规划求解参数对话框 ，选择求解。 保存求解结果 ?当求解结果对话框出现时，选 择保存规划求解结果。选择确定 。 运算结果报告 列出目标单元格和可变单 元格以及它们的初始值、 最终结果、约束条件和有 关约束条件的信息。 初值和终值分别指单元格

10、 在本次求解前的数值和求 解后的数值。 敏感性分析报告（1） 可变单元格中 ?“单元格”指决策变量所在单元格的地址 ?“名字”是决策变量的名称 ?“终值”是决策变量的终值,即最优值 ?“递减成本”指最优解中等于0的变量，对应的目标函数中的系数增 加或减少多少，最优解不再为0 ?“目标式系数”目标函数中的系数，为已知条件 ?“允许的增量”与“允许的减量”表示目标函数中的系数在增量与减量 的变化范围内变化时，最优解保持不变（注：最优值发生变化） 敏感性分析报告（2） 约束单元格 ?“单元格”指约束条件左边所在单元格的地址 ?“名字”是约束条件左边的名称 ?“终值”是约束条件左边的值 ?“阴影价格”

11、指约束条件右边增加或减少一个单位，目标函数 值增加或减少的数量 ?“约束的限制值”指约束条件右边的值，为已知条件 ?“允许的增量，减量”表示约束条件右边在允许的范围内变化 时，影子价格不变，即约束条件右边的值每增加1个单位，目 标函数值的增加仍然为影子价格的大小。因此，右端项在一定 范围内变化时，影子价格不变，目标函数值的变动等于右端项 变动值乘以影子价格 极限值报告解释 列出目标单元格和可变单元格以及它们的数值、上下限和目标 值。含有整数约束条件的模型不能生成本报告。其中，下限是 在满足约束条件和保持其它可变单元格数值不变的情况下，某 个可变单元格可以取到的最小值。上限是在这种情况下可以取

12、到的最大值。 延伸 下面对目标系数同时变动以及约束右端值同时变动的情况分别作延伸 。 （1）目标系数c同时变动的百分之百法则： 如果目标函数系数同时变动，计算出每一系数变动量占该系数同方向 可容许变动范围的百分比，而后将各个系数的变动百分比相加，如果 所得的和不超过百分之一百，最优解不会改变；如果超过百分之一百 ，则不能确定最优解是否改变。 如果x1对应的目标系数c1由2变为1.8，x2对应的目标系数c2由3变为3.5，运 用百分之百法则，根据敏感性分析报告 c1由2变为1占允许减量的百分比为 (2-1.8)/0.5=40% C2由3变为3.5占允许增加量的百分比为 (3.5-3)/1=50%

13、. 改变的百分比和为 90%，没有超过100%，因此最优解不变。 （2）约束右端值b同时变动的百分之百法则： 同时改变几个或所有函数约束的约束右端值，如果这些变动的幅度 不大，那么可以用影子价格预测变动产生的影响。为了判别这些变 动的幅度是否允许，计算每一变动占同方向可容许变动范围的百分 比，如果所有的百分比之和不超过百分之一百，那么影子价格还是 有效的；如果所有的百分比之和超过百分之一百，那就无法确定影 子价格是否有效。 如果右端值b分别由8, 16, 12变为8.5,15，和11，运用百分之百法则: b1由8变为8.5占允许增量的百分比为 (8.5-8)/2=25% b2由16变为15占允

14、许减量的百分比为 (16-15)/8=12.5% b3由12变为11占允许减量的百分比为 (12-11)/4=25%. 改变的百分比和为 62.5%，没有超过100%，因此影子价格仍然有效。 单元格变量名最终 值 减少成本目标系数允许增加 值 允许减少 值 $B$9x102.862.81E+30 $C$9x212.4097.83.4 $D$9x39.50122.61.5 已知一个线性规划问题的灵敏度分析报告如下 变动单元格 单元格名 称最终值影子价格右端值允许增加值允许减少值 $E$4第一资源约束580701E+3012 $E$5第二资源约束372.8371531 $E$6第三资源约束601.

15、2601250 约束条件 2012年12月管理创新实验班期末考试题 （1）写出该问题的最优解 （2）分析当x1的目标系数减少5，同时x2的目标系数增加4时，最优解是否改变。 （3）分析当第一资源约束右端值增加30，同时第二资源约束右端值增加4和第三资源 约束右端值减少15时，目标函数值的改变量。 解：（1）最优解为x1=0, x2=12.4, x3=9.5 （2） x1的目标系数减少5，占允许减少的百分比=5/=0%，x2 的目标系数增加4,占允许增加的百分比=4/7.8=51.2%。 改变的百分比和为51.2%，没有超过100%，因此最优解不变。 （3）第一资源约束右端值增加30，占允许增加

16、的30 /=0%， 第二资源约束右端值增加4 ，占允许增加的4/15=26.7%， 第三资源约束右端值减少15，占允许减少的15/50=30%。 改变的百分比和为56.7%，没有超过100%，因此影子价格仍然 有效。因此目标函数值改变量为 30 × 0 + 4 × 2.8 15 × 1.2=11.2 18= 6.8 练习：话务员排班问题 ? 某寻呼公司雇用了多名话务员工作，他们 每天工作3节，每节3小时，每节开始时间为 午夜、凌晨3点钟、凌晨6点钟，上午9点、中 午12点、下午3点、6点、9点，为方便话务员 上下班，管理层安排每位话务员每天连续工 作3节，根据调查，对于不同的时间，由于业 务量不同，需要的话务员的人数也不相同， 公司付的薪水也不相同，有关数据见表。 ? 问：如何安排话务员才能保证服务人数， 又使总成本最低？ ? 解：这个问题实际上是一个成本效益平衡 问题。管理层在向客户提供满意服务水平的 同时要控制成本，因此必须寻找成本与效益 的平衡。由于每节工作时间为3小时，一天被 分为8班，每人连续工作3节，各