学生用-实验指导书-excel线性规划实验

.实验指导书管理决策模型与方法学院（部）管理学院指导教师金玉兰Word 文档.实验 1EXCEL 线性规划实验一、实验目的1、掌握应用 Excel软件求解线性规划问题；2、掌握应用 Excel软件对线性规划问题进行灵敏度分析；3、掌握应用 Excel软件求解整数规划问题；4、掌握应用 Excel软件求解 0-1 整数规划问题。二、实验设备、仪器及所需材料配置在Pentium，内存128M以上的电脑；装有MicrosoftWindows操作系统及Microsoft Office 2003工作软件。三、实验原理“规划求解”是Microsoft Excel 中的一个加载宏，借助它可以求解许多运筹学中的数学规划问题。安装 Office 2003 的时候，系统默认的安装方式不会安装该宏程序，需要用户自己选择安装。安装方法为：从 Excel 菜单中选择“工具”“加载宏”，打开如下对话框：选择其中的“规划求解”后单击“确定”按钮，会出现提示：“这项功能目前尚未安装，是否现在安装 ?”，选择“是”，系统要你插入Office 的安装光盘，准备好后单击确定，很快就会安装完毕。 于是，你会发现在“工具”菜单下多出一个名为“规划求解”的子菜单， 说明“规划求解”功能已经成功安装。在 EXCEl2007版本中，通过点击“ office 按钮”，“EXCEL选项”“加载项”转到EXCEL“加载Word 文档.项”，然后加载【规划求解加载项】便可以加载规划求解的宏。在 EXCEl2010版本中，通过点击 “ 文件 ”选项卡打开 “ Excel选项 ” 对话框，单击左侧“加载项 ”标签，在右侧单击 “ 转到 ” 按钮，打开 “加载宏 ”对话框， 勾选 “规划求解加载项” 复选框， 单击 “ 确定 ” 按钮，即可在工具栏的“ 数据 ” 选项卡中出现“分析 ” 选项组，上面就有了 “ 规划求解 ” 按钮。利用“规划求解 ” 功能，就可以进行线性规划问题的求解。例如：用 EXCEL求解数学规划问题maxZ2 x13x2x12x28st.4x1164x212x10, x20步骤：1 将模型中的目标函数和约束条件的系数输入到单元格中； 为了使我们在操作过程中看得更清楚，可以附带输入相应的标识符，并给表格加上边框。如下图所示：Word 文档.2在 E4 单元格（目标值） 输入“=SUMPRODUCT($C$3 :$D$3, C4 :D4) ”；其中， SUMPRODUCT函数的功能是将数组间对应的元素相乘，并返回乘积之和，即SUMPRODUCT($C$3 :$D$3,C4:D4) C3C4D3 D4 ； $C$3 :$D$3 表示这几个单元格为绝对引用。3用拖动的方式将E4 单元格的公式复制到E5 E7，如下图：4从“工具”选择“规划求解”，打开“规划求解参数”窗口。Office 2007 中，从“数据”选择“规划求解”，打开“规划求解参数”窗口。5将窗口中的 目标单元格 设为 $E$4，可变单元格 设为 $C$3:$D$3 ，目标为求 最大值 ，如下图：6单击“添加”按钮，打开“添加约束”对话框；将单元格引用位置设为 $E$5:$E$7，约束值 设为 $G$5:$G$7，不等式符号为= ，如下图：Word 文档.7选择“确定”返回“规划求解参数”窗口；单击“选项”按钮，弹出“规划求解选项”窗口，并选中其中的“采用线性模型 ”和假“定非负 ”两项，其余选项可保留默认值。如下图：8选择“确定”按钮返回“规划求解参数”窗口；单击“求解”按钮，系统弹出“规划求解结果”对话框，如下图：9将对话框右边“ 报告 ”下的全部内容选中（也可不选）后，单击“确定”按钮完成计算，结果如下图。可知该问题的最优解为：1 4， 2 2，max Z=14 。xxWord 文档.四、实验内容（一）用 EXCEL求解线性规划问题maxZ12x110x22x1x21601 3 x1 1 3 x2 40st.2x22603x1x10, x20（二）用 EXCEL求解线性规划问题，并进行灵敏度分析maxZ20x130x22x1x240st.x12x240x1x225x10, x20（三）用 EXCEL求解整数规划问题maxZ3x12x2x10.5x24.5st.2x13x214xi0且为整数 , i1,2（四）用 EXCEL求解 01 整数规划问题如果每一个客户只需要一个项目负责人， 那么怎么进行分配才能使项目负责人完成这三个项目的所需的总时间最短？下表总结每个人完成各任务所需时间。项目负责人客户（任务）1231.特瑞10159Word 文档.2.卡尔91853.迈克孟德6143（五）用 EXCEL求解下列资源分配问题某昼夜服务的公交线路每天各时间段内所需司机人数如下：班次时间所需人数16： 00 10：00802 10： 00 14 ： 00 703 14： 00 18 ： 00 804 18： 00 22 ： 00 50522： 00 2：002062： 00 6： 0030设司机在各时间段一开始时上班，并连续工作八小时，问该公交线路该怎样安排司机人数，既能满足工作需要，又配备最少司机？数学模型如下，求用EXCEL求解。minZx1x2x3x4x5x6x1x680x1x270x2x380st.x3x450x4x520x5x630xi0且为整数 , i1,2,3, 4,5,6（六）请各位同学按照学号选做以下题目要求：请至少完成与学号尾数相对应题号的题目。选做其他题目，则按题目数量和准确率进行加分。学号尾数必做题 A必做题 B学号尾数必做题 A必做题 B0105552118663226771Word 文档39384749题目 1：maxZ5x12x23x3x4x5x12x23x3x48st.3x14x2x3x5 =7x1 , x2 , x3 , x4 , x50题目 2：minZx12x2x1x34st.x2x432x2+x58x1xi0, i1,2,3, 4,5题目 3：.84910Word 文档.maxZx12x2x1 + x22st.x1x21x23x10, x20题目 4：maxZ40x190x29x17x256s.t7x120x270xi0且都为整数 , i1,2题目 5：maxZ5x18x2x1x26st.5x19x245xi0且为整数 , i1,2Word 文档.题目 6：maxZ4x13x25x3x13x22x332x1x23x35s.t 2x1x224x22x38xi =0或1，i1,2,3题目 7：由甲、乙、丙、丁四人去完成 A、B、C、D 四项工作，每人做且做其中一项工作，每人完成各项工作的工时由下表给出。 怎样指派工作， 才能使四个人完成四个项目的总时间最少。请用 excel 求解该问题。工作 A工作 B工作 C工作 D甲312106乙1461511丙781613丁8101510题目 8：由甲、乙、丙、丁四人去完成 A、B、C、D 四项工作，每人做且做其中一项工作，每人完成各项工作的工时由下表给出。 怎样指派工作， 才能使四个人完成四个项目的总时间最少。请用 excel 求解该问题。工作 A工作 B工作 C工作 D甲25283134乙29333228丙36272629丁29313327Word 文档.题目 9：maxZ3x12x2 102x1x2+x432s.t x12x2x352xi0, i1,2,3, 4x2 , x3取整数题目 10：minZ 2x15x2 +3x3 +4 x44x1 x2x3 +x4 0s.t2x14x23x3 +4x44x1x2 x3 +x4 1xi=0或1，i 1,2,3, 4五、实验报告要求1、封面：写明实验的名称，班级、姓名及实验时间。2、实验报告：按统一格式，采用统一报告纸。报告内容应包括实验名称、目的、原理、内容、实验成果和实验心得等。实验成果是指能否理解 Excel 函数，并应用 Excel 求解线性规划问题。3、备注：实验成果和实验心得请手写。Word 文档