EXCEL求解第一章线性规划和灵敏度分析.ppt

应用应用EXCEL求解运筹学问题求解运筹学问题一、EXCEL求解线性规划问题n求解线性规划n影子价格和灵敏度分析线性规划模型的描述线性规划模型的描述n例1：某工厂生产两种新产品：门和窗经测算，每生产一扇门需要在车间1加工1小时、在车间3加工3小时；每生产一扇窗需要在车间2和车间3各加工2小时而车间1每周可用于生产这两种新产品的时间为4小时、车间2为12小时、车间3为18小时已知每扇门的利润为300元，每扇窗的利润为500元根据市场调查得到的这两种新产品的市场需求状况可以确定，按当前的定价可确保所有的新产品均能销售出去问：该工厂如何安排这两种新产品的生产计划，才能使总利润最大？线性规划模型的描述线性规划模型的描述n数据表格：车间单位位产品的生品的生产时间（小（小时））每周可每周可获得的生得的生产时间（小（小时））门窗窗11042021233218单位利位利润（元）（元）300500线性规划模型的建立线性规划模型的建立n假设：每周各生产门和窗x1、x2个 建立线性规划模型如下： Max Z=300x1+500x2 x1≤4 2x2≤12 3x1+2x2≤18 x1、x2≥0EXCEL求解线性规划模型求解线性规划模型一、在EXCEL电子表格中建立线性规划模型预处理：加载—宏—规划求解1、把相关数据输入到EXCEL电子表格中EXCEL求解线性规划模型求解线性规划模型EXCEL表格模型有关的四类单元格： • 数据单元格：单位利润（C4：D4）、 可用工时（G7：G9） • 可变单元格：每周产量（C12：D12） • 输出单元格：实际使用（E7：E9） • 目标单元格：总利润 （G12）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求解线性规划模型求解线性规划模型n收集问题数据；n在电子表格中输入数据（数据单元格）；n确定决策变量单元格（可变单元格）；n输入约束条件左边的公式（输出单元格）使用SUMPRODUCT函数简化输入；n输入目标函数公式（目标单元格）。

使用SUMPRODUCT函数简化输入在电子表格中建立线性规划模型步骤总结EXCEL求解线性规划模型求解线性规划模型二、在EXCEL电子表格中求解线性规划模型1、求解参数设置： “工具”——”规划求解“，弹出“规划求解参数”对话框，设置求解相关参数EXCEL求解线性规划模型求解线性规划模型2、约束的设置： 单击 “添加”，弹出“添加约束”，添加约束条件EXCEL求解线性规划模型求解线性规划模型3、求解选项设置： 单击“选项”，弹出“规划求解选项”对话框选择“采用线性模型”和“假定非负”EXCEL求解线性规划模型求解线性规划模型4、求解及结果 单击“求解”，开始规划求解弹出“规划求解结果”对话框选择“保存规划求解结果”EXCEL求解线性规划模型求解线性规划模型5、电子表格显示结果： 单击“确定”，在电子表格的可变单元格、输出单元格及目标单元格出现求解结果练习n某家具厂生产5种不同规格的家具，每件家具要经过成型、打磨、上漆等几个主要生产工序每种家具的每道工序所需要的时间以及每道工序的可用时间、每种家具的利润如下表所示，问：工厂应该如何安排生产，才能使得总利润最大？生产工序 所需时间可用时间一二三四五成型346233600打磨435643950上漆233432800利润（百元）2.734.52.53n输入数据n标识数据n每个数据对应唯一单元格n在电子表格中显示完整模型n数据、公式分离n保持简单化n使用相对和绝对地址简化公式并复制n使用边框、底色区分单元格类型建模求解要点回顾线性规划问题解的讨论 1、线性规划问题解的种类？ 2、唯一解的表现是……？ 3、无穷解的表现是……？ 4、无可行域无解的表现是……？ 5、可行域无界的表现是……？ 上述结果用EXCEL建模求解的最后对话框提示不同。

EXCEL求解线性规划模型求解线性规划模型n图解法解得分析：解的结果解的结果有可行域有可行域无可行域无可行域可行域有界可行域有界可行域无界可行域无界唯一解唯一解无穷解无穷解唯一解唯一解无穷解无穷解无解无解一定无解一定无解练习1练习2练习3综合练习n某公司生产甲、乙、丙三种产品，都需要经过铸造、机加工和装配三个车间甲、乙两种产品的铸件可以外包协作，亦可以自行生产，但产品丙必须本厂铸造才能保证质量数据如下表问：公司为了获得最大利润，甲、乙、丙三种产品各生产多少件？甲、乙两种产品的铸造中，由本公司铸造和由外包协作各应多少件？产品甲产品甲产品乙产品乙产品丙产品丙工时限制工时限制单件铸造工时（小时）单件铸造工时（小时）51078000单件机加工工时（小时）单件机加工工时（小时）64812000单件装配工时（小时）单件装配工时（小时）32210000自产铸件成本（元自产铸件成本（元/件）件）354外协铸件成本（元外协铸件成本（元/件）件）56-机加工成本（元机加工成本（元/件）件）213装配成本（元装配成本（元/件）件）322产品售价（元产品售价（元/件）件）231816数学模型n决策变量nx1,x2,x3 分别为三道工序都由本公司加工的甲、乙、丙三种产品的件数，n x4,x5 分别为由外协铸造再由本公司机加工和装配的甲、乙两种产品的件数。

n目标函数：Max 15x1 + 10x2 + 7x3 + 13x4 + 9x5n约束条件：5x1 + 10x2 + 7x3 ≤ 80006x1 + 4x2 + 8x3 + 6x4 + 4x5 ≤ 120003x1 + 2x2 + 2x3 + 3x4 + 2x5 ≤ 10000x1,x2,x3,x4,x5 ≥ 0灵敏度分析n线性规划问题的灵敏度分析是在求出最优解的基础上，进一步讨论当ai，bi和ci发生变化时，对最优解的影响n判断某一参数发生变化时，原最优解是否发生变化？n怎样得出使原最优解不变的参数变化范围n当最优解发生变化时，怎样求出新的最优解单个cj变动n比如当门的单位利润由原来的300元提升到500元，最优解是否发生变化，对总利润产生什么影响？n方法1：利用电子表格分析，改变参数重新计算，观察结果n方法2：灵敏度分析方法1：电子表格分析最优解不变，总利润上升方法2：灵敏度分析n对原电子表格模型运行“规划求解”功能，得出“规划求解结果”对话框，选择“敏感性报告”选项，得出结果比较2、多个cj变动n例如把门的利润由300提高到450，同时把窗的单位利润由500减少到400，原来的最优解和最优值是否会发生变化。

n方法1：电子表格分析，改变参数n方法2：灵敏度分析电子表格分析n最优解不变，总利润下降300灵敏度分析n应用敏感性报告以及百分之一百法则进行分析n百分之一百法则：n对于所有变化的目标函数决策变量系数（或约束条件右边常数），当其所有允许增加百分比和允许减少百分比之和不超过百分之一百时，最优解不变EXCEL求解线性规划模型求解线性规划模型n允许增加量百分比= 实际增加量（上限-现在值）/允许增加量n允许减少量百分比= 实际减少量（现在值-下限）/允许减少量n例：门300——450；窗500——400n例：门300——600；窗500——300n如果超过100%，用电子表格模型重新求解n思考：当结果刚好为100%时，最优解变吗？EXCEL求解线性规划模型求解线性规划模型n应用规则：n当允许增加量（减少量）为无穷大时，则对于任一个增加量（减少量），其允许增加（或减少）的百分比都看成零n百分之一百法则是判断最优解变与不变的充分条件，但不是必要条件n不能应用于目标函数决策变量系数和约束条件右端常数同时变化的情况EXCEL求解线性规划模型求解线性规划模型3、单个bi变动n例：如果车间2的可用工时由12小时增加到13小时，原来的最优解和最优值是否发生变化？n方法1：应用电子表格进行分析 改变电子表格模型中相应的参数，再运行EXCEL”规划求解”功能，得出结果，看其是否对原最优解、最优值有影响。

分析：2——10——18——20的不同结果？EXCEL求解线性规划模型求解线性规划模型n方法2：应用敏感性报告寻找允许变化范围 对原电子表格模型运行EXCEL”规划求解”功能，得出“规划求解结果”对话框，选择右端“敏感性报告”选项，得出相应结果EXCEL求解线性规划模型求解线性规划模型4、多个bi变动n例：如果车间2的可用工时由12小时增加到13小时，车间3的可用工时由18小时减少到17小时，原来的最优解和最优值是否发生变化？n方法1：应用电子表格进行分析 改变电子表格模型中相应的参数，再运行EXCEL”规划求解”功能，得出结果，看其是否对原最优解、最优值有影响EXCEL求解线性规划模型求解线性规划模型n方法2：应用敏感性报告及百分之百法则进行分析 对原电子表格模型，运行EXCEL”规划求解”功能，得出“规划求解结果”对话框，选择右端“敏感性报告”选项，得出相应结果运用百分之百法则进行判断n例：车间2：12——13，车间3：18——17n例：车间2：12——16，车间3：18——15EXCEL求解线性规划模型求解线性规划模型5、aij变化n例：由于车间2采用新的生产工艺，生产一扇窗由原来的2小时下降到小时，原来的最优解和最优值是否发生变化？n解决方法：改变电子表格模型中相应的参数，再运行EXCEL”规划求解”功能，得出结果，看其是否对原最优解、最优值有影响。

EXCEL求解线性规划模型求解线性规划模型6、增加一个新变量n例：由于市场变化，工厂考虑增加一种新产品——防盗门的生产，假设每周产量为x3，单位利润为400元，生产一个防盗门占用车间1、2、3各2、1、1工时其最优解和最优值是多少？n解决方法：在原电子表格模型中增加一列，输入防盗门的相关数据，修改相应的计算公式形成新的电子表格模型，再运行EXCEL”规划求解”功能，得出新的最优解、最优值EXCEL求解线性规划模型求解线性规划模型7、增加一个约束条件n例：由于电力紧张，在原来生产计划中增加一个约束条件，假设两种产品每件需要消耗电力为20kw、10kw，工厂总供电能力为90kw其最优解和最优值是多少？n解决方法：在原电子表格模型中增加一行，输入电力消耗的相关数据，修改相应的计算公式形成新的电子表格模型，再运行EXCEL”规划求解”功能，得出新的最优解、最优值EXCEL求解线性规划模型求解线性规划模型8、影子价格的应用 案例分析 例2.3 练习（模型求解）n某工厂生产A、B两种产品，均需经过两道工序，每生产一吨A产品需要经过第一道工序2小时，第二道工序加工3小时，每生产一吨B产品需要经过第一道工序加工3小时，第二道工序加工4小时。

可利用的第一道工序工时为15小时，第二道工序为25小时n生产产品B的同时可产出副产品C，每生产一吨产品B，可同时得到2吨产品C而不需要增加任何额外费用副产品C一部分可以盈利，但剩下的只能报废，报废需要一定的费用n各项费用的情况为：出售产品A每吨能够获利400元，出售产品B每吨能够获利800元，每销售一吨副产品C能够获利300元，当剩余的产品C报废时，每吨损失费为200元n经市场预测，在计划期内产品C的最大销量为5吨n问：如何安排A和B的生产可使工厂总盈利最大？模型n决策变量为生产A数量x1，生产B数量x2，C盈利部分数量为x3，报废部分数量为x4；n目标函数n约束条件n工序1n工序2n产品B和C的关系n产品C的销量n非负约束练习（灵敏度）n某工厂利用原材料甲、乙、丙生产产品A、B和C，有关资料如右上表所示：问n怎样安排生产，才能使得利润最大？n若增加一公斤原材料甲，利润能增加多少？n设原材料乙的市场价格为元/公斤，若要转卖原材料乙，工厂应该至少叫价多少，为什么？n单位产品利润分别在什么范围内变化，原生产利润不变n由于市场变化，产品B和C的单件利润变为2元，4元，这时应该如何调整生产计划？ABC每月可用原材料 甲211200乙123500丙221600单件利润413综合案例n某咨询公司，受厂商的委托，对新上市的一种新产品进行消费者反应的调查。

该公司采用了挨户调查的方法，委托他们调查的厂商以及该公司的市场调研专家对该调查提出几点要求：n至少调查2000户人家n在晚上调查的人数和白天调查的人数相等n至少调查700户有孩子的家庭n至少调查450户无孩子的家庭n每会见一户家庭，进行调查所需费用如上表所示，问n利用线性规划确定白天晚上各调查多少家庭，才能使得调查费用最小？n分别对白天、晚上会见两种家庭的费用进行灵敏度分析n对调查的总户数、有孩子和无孩子家庭的最少调查数进行灵敏度分析家庭白天会见晚上会见有孩子25元30元无孩子20元24元建模n决策变量n白天调查的有孩子的家庭数量x1n晚上调查的有孩子的家庭数量x2n白天调查的没孩子的家庭数量x3n晚上调查的没孩子的家庭数量x4n目标函数n约束条件。