运用EXCEL求解线性规划模型课件

运用EXCEL建模与求解2011.05学习背景知识介绍学习运筹学不仅拥有深厚的数学功底，而且要应用计算机完成模型的建立，并求解最优化方案。使用电子表格软件已经成为管理优化和运筹学教学的一个新的潮流。近年来，美国高校“运筹学”（管理优化）教学思想、内容、方法和手段有了根本的转变，主要表现在美国各大学已普遍采用“电子表格”这一全新的教学方法。国外最新教材《数据、模型与决策——运用电子表格建模与案例研究》（翻译版）详细介绍了各种运筹学模型及其在EXCEL软件中的实现方法。应用EXCEL求解线性规划模型线性规划模型的描述数据表格：车间单位产品的生产时间（小时）每周可获得的生产时间（小时）门窗11042021233218单位利润（元）300500线性规划模型的建立假设：每周各生产门和窗x1、x2个。建立线性规划模型如下：MaxZ=300x1+500x2x1≤42x2≤123x1+2x2≤18x1、x2≥0EXCEL求解线性规划模型一、在EXCEL电子表格中建立线性规划模型1、把相关数据输入到EXCEL电子表格中EXCEL求解线性规划模型2、主要求解结果■两种新产品每周的产量；■两种新产品每周各实际使用的工时（不能超过计划工时）；■两种新产品的总利润EXCEL求解线性规划模型2）复制、粘贴方法：在E7中输入：C7*$C$12+D7*$D$12复制E7单元格到E8、E9

3）公式法：在E7中输入：=SUMPRODUCT（C7：D7，$C$12：$D$12）复制E7单元格到E8、E9EXCEL求解线性规划模型收集问题数据；在电子表格中输入数据（数据单元格）；确定决策变量单元格（可变单元格）；输入约束条件左边的公式（输出单元格）使用SUMPRODUCT函数简化输入；输入目标函数公式（目标单元格）。使用SUMPRODUCT函数简化输入。在电子表格中建立线性规划模型步骤总结EXCEL求解线性规划模型2、约束的设置：单击“添加”，弹出“添加约束”，添加约束条件。EXCEL求解线性规划模型3、求解选项设置：单击“选项”，弹出“规划求解选项”对话框。选择“采用线性模型”和“假定非负”。EXCEL求解线性规划模型4、求解及结果单击“求解”，开始规划求解。弹出“规划求解结果”对话框。选择“保存规划求解结果”。EXCEL求解线性规划模型5、电子表格显示结果：单击“确定”，在电子表格的可变单元格、输出单元格及目标单元格出现求解结果。EXCEL求解线性规划模型课堂练习1：P251利用SUMPRODUCT函数求解例。EXCEL求解线性规划模型生产工序所需时间可用时间（小时）一二三四五成型346233600打磨435643950上漆233432800利润（百元）2.734.52.53EXCEL求解线性规划模型三、使用单元格命名方法求解线性规划模型

求解时首先给与公式和模型有关的四类单元格进行命名。

•数据单元格：单位利润（C4：D4）、可用工时（G7：G9）

•可变单元格：每周产量（C12：D12）•输出单元格：实际使用（E7：E9）•目标单元格：总利润（G12）EXCEL求解线性规划模型（2）查看、更改、删除◆路径：“插入”——“名称”——“定义”，进入“定义名称”界面。◆单击某个名称，可查看其引用位置。◆更改：先添加新名称，再删除原名称。也可修改原名称的引用位置。◆删除：选择欲删除单元格名称，单击“删除”。EXCEL求解线性规划模型查看、更改、删除操作界面EXCEL求解线性规划模型（4）将单元格名称粘贴到电子表格中▍选择空白单元格，输入“区域名称”和“单元格”。▍单击“区域名称”下的单元格。▍路径：“插入”——“名称”——“粘贴”。出现“粘贴名称”对话框。▍单击“粘贴列表”，在电子表格中的相应位置得出结果。EXCEL求解线性规划模型EXCEL求解线性规划模型（5）对结果进行修饰利用“替换”功能中的“全部替换”去掉“=Sheet1!”和“$”，得出区域名称和引用结果。EXCEL求解线性规划模型使用名称比使用字母更容易理解公式的含义在“规划求解参数”对话框中使用名称更容易理解线性规划模型的含义。增强了公式和模型的可读性使用单元格命名法有何好处？课堂练习3：P251输入数据标识数据每个数据对应唯一单元格在电子表格中显示完整模型数据、公式分离保持简单化使用区域名称使用相对和绝对地址简化公式并复制使用边框、底色区分单元格类型建模求解要点回顾某公司有100万元的资金可供投资,该公司有六个可选的投资项目,其各种数据见下表.该公司的目标:投资风险最小,每年红利至少6.5万元,最低平均增长率为12%,最低平均信用度为7.案例分析及求解：P3例2EXCEL求解线性规划模型投资项目风险(%)红利(%)增长率(%)信用度11842242657103109122447810512615468886EXCEL求解线性规划模型假设：xi为每种投资项目的投资额。建立线性规划模型如下：

MinZ=0.18x1+0.06x2+0.10x3+0.04x4+0.12x5+0.08x6

x1+x2+x3+x4+x5+x6

=100

0.04x1+0.05x2+0.09x3+0.07x4+0.06x5+0.08x6≥6.50.22x1+0.07x2+0.12x3+0.08x4+0.15x5+0.08x6≥124x1+10x2+2x3+10x4+4x5+6x6≥700x1、x2、x3、x4、x5、x6≥0EXCEL求解线性规划模型EXCEL求解线性规划模型课堂练习:某公司受人委托,准备用120万元投资A和B两中基金,其中:A基金的单位投资额为50万元,年回报率为10%,B基金的单位投资额为100万元,年回报率为4%.委托人要求在每年的年回报金额至少达到6万元的基础上要求投资风险最小.据测定每单位A基金的投资风险指数为8,每单位B基金的投资风险指数为3,风险指数越大表明投资风险越大.委托人要求在基金B中的投资额不少于30万元.为了使总的投资风险指数最小,该公司应该在基金A和B中各投资多少单位?这时每年的回报金额是多少?EXCEL求解线性规划模型四、线性规划问题解的讨论

1、线性规划问题解的种类？2、唯一解的表现是……？3、无穷解的表现是……？4、无可行域无解的表现是……？5、可行域无界的表现是……？

上述结果用EXCEL建模求解的最后对话框提示不同。EXCEL求解线性规划模型图解法解得分析：解的结果有可行域无可行域可行域有界可行域无界唯一解无穷解唯一解无穷解无解一定无解EXCEL求解线性规划模型五、线性规划问题的灵敏度分析

线性规划问题的灵敏度分析是在求出最优解的基础上，进一步讨论当cj、bi、aij发生变化时，对最优解的影响。

●判断某一参数发生变化，原最优解是否发生变化？

●怎样得出使原最优解不变的参数变化范围

●当最优解发生变化时，怎样求出新的最优解。EXCEL求解线性规划模型1、单个cj变动例：如果门的单位利润由原来的300元提升到500元，最优解是否会改变？对总利润会产生怎样的影响？方法1：应用电子表格进行分析改变电子表格模型中相应的参数，再运行EXCEL”规划求解”功能，得出结果，看其是否对原最优解有影响。EXCEL求解线性规划模型求得结果如下。最优解不变，总利润上升400元。EXCEL求解线性规划模型方法2：应用敏感性报告寻找允许变化范围

对原电子表格模型运行EXCEL”规划求解”功能，得出“规划求解结果”对话框，选择右端“敏感性报告”选项，得出相应结果。EXCEL求解线性规划模型比较两种方法的用途？EXCEL求解线性规划模型2、多个cj变动例：如果把门的单位利润由300元提高到450元，同时把窗的单位利润由500元减少到400元，原来的最优解和最优值是否会发生变化？方法1：应用电子表格进行分析改变电子表格模型中相应的参数，再运行EXCEL”规划求解”功能，得出结果，看其是否对原最优解、最优值有影响。EXCEL求解线性规划模型求得结果如下。最优解不变，总利润下降300元。EXCEL求解线性规划模型方法2：应用敏感性报告及百分之百法则进行分析对原电子表格模型，运行EXCEL”规划求解”功能，得出“规划求解结果”对话框，选择右端“敏感性报告”选项，得出相应结果。运用百分之百法则进行判断。百分之一百法则：对于所有变化的目标函数决策变量系数（或约束条件右边常数），当其所有允许增加百分比和允许减少百分比之和不超过百分之一百时，最优解不变。EXCEL求解线性规划模型允许增加量百分比=实际增加量（上限-现在值）/允许增加量允许减少量百分比=实际减少量（现在值-下限）/允许减少量例：门300——450；窗500——400例：门300——600；窗500——300如果超过100%，用电子表格模型重新求解思考：当结果刚好为100%时，最优解变吗？EXCEL求解线性规划模型应用规则：当允许增加量（减少量）为无穷大时，则对于任一个增加量（减少量），其允许增加（或减少）的百分比都看成零。百分之一百法则是判断最优解变与不变的充分条件，但不是必要条件。不能应用于目标函数决策变量系数和约束条件右端常数同时变化的情况。EXCEL求解线性规划模型3、单个bi变动例：如果车间2的可用工时由12小时增加到13小时，原来的最优解和最优值是否发生变化？(最优解肯定变，范围就是可行性是否变)方法1：应用电子表格进行分析改变电子表格模型中相应的参数，再运行EXCEL”规划求解”功能，得出结果，看其是否对原最优解、最优值有影响。分析：2——10——18——20的不同结果？EXCEL求解线性规划模型方法2：应用敏感性报告寻找允许变化范围对原电子表格模型运行EXCEL”规划求解”功能，得出“规划求解结果”对话框，选择右端“敏感性报告”选项，得出相应结果。EXCEL求解线性规划模型4、多个bi变动例：如果车间2的可用工时由12小时增加到13小时，车间3的可用工时由18小时减少到17小时，原来的最优解和最优值是否发生变化？方法1：应用电子表格进行分析改变电子表格模型中相应的参数，再运行EXCEL”规划求解”功能，得出结果，看其是否对原最优解、最优值有影响。EXCEL求解线性规划模型方法2：应用敏感性报告及百分之百法则进行分析对原电子表格模型，运行EXCEL”规划求解”功能，得出“规划求解结果”对话框，选择右端“敏感性报告”选项，得出相应结果。运用百分之百法则进行判断。例：车间2：12——13，车间3：18——17例：车间2：12——16，车间3：18——15（4/6+3/6=7/6）EXCEL求解线性规划模型5、aij变化例：由于车间2采用新的生产工艺，生产一扇窗由原来的2小时下降到1.5小时，原来的最优解和最优值是否发生变化？解决方法：改变电子表格模型中相应的参数，再运行EXCEL”规划求解”功能，得出结果，看其是否对原最优解、最优值有影响。EXCEL求解线性规划模型6、增加一个新变量例：由于市场变化，工厂考虑增加一种新产品——防盗门的生产，假设每周产量为x3，单位利润为400元，生产一个防盗门占用车间1、2、3各2、1、1工时。其最优解和最优值是多少？解决方法：在原电子表格模型中增加一列，输