运用EXCEL求解线性规划模型分析报告.ppt

运用EXCEL建模与求解 2011.05 学习背景知识介绍 学习运筹学不仅拥有深厚的数学功底，而且要应用计算机完成模型的建立，并求解最优化方案。 使用电子表格软件已经成为管理优化和运筹学教学的一个新的潮流。 近年来，美国高校“运筹学”（管理优化）教学思想、内容、方法和手段有了根本的转变，主要表现在美国各大学已普遍采用“电子表格”这一全新的教学方法。 国外必威体育精装版教材《数据、模型与决策——运用电子表格建模与案例研究》（翻译版）详细介绍了各种运筹学模型及其在EXCEL软件中的实现方法。 应用EXCEL求解线性规划模型 线性规划模型的描述 例：某工厂生产两种新产品：门和窗。经测算，每生产一扇门需要在车间1加工1小时、在车间3加工3小时；每生产一扇窗需要在车间2和车间3各加工2小时。而车间1每周可用于生产这两种新产品的时间为4小时、车间2为12小时、车间3为18小时。已知每扇门的利润为300元，每扇窗的利润为500元。根据市场调查得到的这两种新产品的市场需求状况可以确定，按当前的定价可确保所有的新产品均能销售出去。问：该工厂如何安排这两种新产品的生产计划，才能使总利润最大？ 线性规划模型的描述 数据表格： 线性规划模型的建立 假设：每周各生产门和窗x1、x2个。 建立线性规划模型如下： Max Z=300x1+500x2 x1≤4 2x2≤12 3x1+2x2≤18 x1、x2≥0 EXCEL求解线性规划模型 一、在EXCEL电子表格中建立线性规划模型 1、把相关数据输入到EXCEL电子表格中 EXCEL求解线性规划模型 2、主要求解结果 ■两种新产品每周的产量； ■两种新产品每周各实际使用的工时（不能超过计划工时）； ■两种新产品的总利润 EXCEL求解线性规划模型 3、主要结果的计算方法 （1）两种新产品的每周产量：C12、D12，试验解为0。 （2）实际使用工时计算（三种方法） 1）分别在E7、E8、E9中输入相应的计算公式： E7：C7*C12+D7*D12 E8：C8*C12+D8*D12 E9：C9*C12+D9*D12 EXCEL求解线性规划模型 2）复制、粘贴方法： 在E7中输入：C7*$C$12+D7*$D$12 复制E7单元格到E8、E9 3）公式法： 在E7中输入： =SUMPRODUCT（C7：D7，$C$12：$D$12） 复制E7单元格到E8、E9 EXCEL求解线性规划模型 （3）总利润计算： 在G12单元格输入公式： =C4*C12+D4*D12 或： =SUMPRODUCT（C4：D4，C12：D12） EXCEL求解线性规划模型 收集问题数据； 在电子表格中输入数据（数据单元格）； 确定决策变量单元格（可变单元格）； 输入约束条件左边的公式（输出单元格）使用SUMPRODUCT函数简化输入； 输入目标函数公式（目标单元格）。使用SUMPRODUCT函数简化输入。 EXCEL求解线性规划模型 二、在EXCEL电子表格中求解线性规划模型 1、求解参数设置： “工具”——”规划求解“，弹出“规划求解参数”对话框，设置求解相关参数。 EXCEL求解线性规划模型 2、约束的设置： 单击 “添加”，弹出“添加约束”，添加约束条件。 EXCEL求解线性规划模型 3、求解选项设置： 单击“选项”，弹出“规划求解选项”对话框。选择“采用线性模型”和“假定非负”。 EXCEL求解线性规划模型 4、求解及结果 单击“求解”，开始规划求解。弹出“规划求解结果”对话框。选择“保存规划求解结果”。 EXCEL求解线性规划模型 5、电子表格显示结果： 单击“确定”，在电子表格的可变单元格、输出单元格及目标单元格出现求解结果。 EXCEL求解线性规划模型 课堂练习1：P25 1 利用SUMPRODUCT函数求解例。 EXCEL求解线性规划模型 某家具制造厂生产五种不同规格的家具.每件家具都要经过机械成型、打磨、上漆等几个主要生产工序.每件家具的每道工序所使用的时间及每道工序的可用时间、每种家具的利润等数据如下表。问工厂应如何安排生产，才能使总利润最大？ EXCEL求解线性规划模型 EXCEL求解线性规划模型 三、使用单元格命名方法求解线性规划模型 求解时首先给与公式和模型有关的四类单元格进行命名。