下载文档原格式
实验一、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。
Excel最多可解200个变量、600个约束条件的问题。
下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。
一、实验目的1、掌握如何建立线性规划模型。
2、掌握用Excel求解线性规划模型的方法。
3、掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。
4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。
二、实验内容1、[工具][规划求解]命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。
在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。
使用[规划求解]命令的一般步骤为：第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。
图1“规划求解参数”对话框选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。
如利润问题，选取“最大值”可变单元格决策变量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。
图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。
图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。
让利益最大化——关于皇氏乳业加工奶制品的生产计划摘要：如今乳制品的市场竞争越来越强，原料成本正在增加，为了提高皇氏乳业的竞争力，提高公司的利润，公司决定开发新产品，原料奶油及中老年奶粉。
先对皇氏乳业的原料成本，生产时间，产品利润等做了一系列调查，建立了线性规划模型，在对模型求解并进行灵敏度分析后，给出具体的对策建议。
关键词：线性规划；生产成本；最优生产计划一、问题的提出经过调查，每一桶牛奶的生产成本和利润如下表：每天至多加工50桶牛奶，机器最多使用480小时，至多加工100kg奶油A1。
（一）如何制定生产计划，使每天获利最大？（二） 35元可以买到一桶牛奶，买吗？若买，每天最多买多少？（三）可聘用临时工人，付出的工资最多是每小时几元？（四）奶油A1的获利增加到30元/公斤，是否改变生产计划？1.问题分析首先，工厂的经济效益主要取决于原料，劳动时间，产品利润等，至于劳动机械磨损，工人熟练程度等，均不予考虑。
所以我们主要研究原料成本，劳动时间，产品利润与工厂经济效益的关系。
2.数据的收集整理对于奶油A1、奶粉A2的产量，询问工厂管理人员得知。
对于加工时间，可以通人力资源管理部门查询。
对于利润，通过近期一个月的销售成绩，综合分析得出。
二、运筹模型1、模型的建立设X1桶牛奶生产奶油A1，X2桶牛奶生产奶粉A2。
Maxz=72X1+64X2St. X1+X2<=5012X1+8X2<=4803X1<=100X1,X2>=02、模型的求解应用EXCEL软件进行求解。
3、灵敏度分析包括对于目标系数（桶数）变化的灵敏度分析结果表和对于约束条件，如原料供应，劳动时间，加工能力等变化的灵敏度分析结果表。
4、结果分析（一）当20桶牛奶生产奶油A1，30桶生产奶粉A2，利润达到3360元，是最大值。
（二）原料增加1单位，利润增加48。
35元<48元，应该买（三）时间增加1单位，利润增加2元，能力增减不影响，所以临时雇用临时工人每小时不超过2元。
《EXCELL辅助决策实验》选修指导书本章以举例的方式介绍用Spreadsheet方法解决各种管理问题。
第一节线性规划问题建模和求解例雅致家具厂生产计划优化问题雅致家具厂生产4种小型家具，由于该四种家具具有不同的大小、形状、重量和风格，所以它们所需要的主要原料（木材和玻璃）、制作时间、最大销售量与利润均不相同。
该厂每天可提供的木材、玻璃和工人劳动时间分别为600单位、1000单位与400小时，详细的数据资料见下表。
问：（1）应如何安排这四种家具的日产量，使得该厂的日利润最大？（2）家具厂是否愿意出10元的加班费，让某工人加班1小时？（3）如果可提供的工人劳动时间变为398小时，该厂的日利润有何变化？（4）该厂应优先考虑购买何种资源？（5）若因市场变化，第一种家具的单位利润从60元下降到55元，问该厂的生产计划及日利润将如何变化？表1 雅致家具厂基本数据解：依题意，设置四种家具的日产量分别为决策变量x1,x2,x3,x4,目标要求是日利润最大化，约束条件为三种资源的供应量限制和产品销售量限制。
博客地址：/ 1博客地址：/2据此，列出下面的线性规划模型：其中X1,X2,X3,X4分别为四种家具的日产量。
下面介绍用Excel 中的“规划求解”功能求此题。
第一步 在Excel 中描述问题、建立模型，如下图所示。
①②③④⑤⑥⑦⑧ ⎪⎪⎪⎪⎪⎩⎪⎪⎪⎪⎪⎨⎧≥≤≤≤≤≤+++≤+++≤++++++=（非负约束）需求量约束）（家具需求量约束）（家具需求量约束）（家具需求量约束）（家具（劳动时间约束）（玻璃约束）（木材约束）0,,,41003502200110040023121000226600224..30402060432143214321432143214321x x x x x x x x x x x x x x x x x x x x t s x x x x MaxZ第二步在“工具”菜单中选择“规划求解”。
数学与信息科学学院Excel求解线性规划问题实验教程二零一三零八月目录1.关于“规划求解” (1)2.如何加载“规划求解” (2)3.“规划求解”各参数解释和设置 (3)4.“规划求解”的步骤 (6)5.Excel求解线性规划问题 (8)6.Excel求解运输问题 (14)7.Excel求解目标规划问题 (18)8.Excel求解整数规划问题 (22)1.关于“规划求解”“规划求解”是Excel中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式（公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。
公式总是以等号（=）开始）的最优值。
“规划求解”将对直接或间接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。
“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。
在创建模型过程中，可以对“规划求解”中的可变单元格数值应用约束条件（约束条件：“规划求解”中设置的限制条件。
可以将约束条件应用于可变单元格、目标单元格或其它与目标单元格直接或间接相关的单元格。
而且约束条件可以引用其它影响目标单元格公式的单元格。
使用“规划求解”可通过更改其它单元格来确定某个单元格的最大值或最小值。
）Microsoft Excel的“规划求解”工具取自德克萨斯大学奥斯汀分校的Leon Lasdon 和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码。
线性和整数规划问题取自Frontline Systems公司的John Watson 和Dan Fylstra提供的有界变量单纯形法和分支边界法。
2.如何加载“规划求解”安装office的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。
下面是加载“规划求解”宏的步骤：（1）在“工具”菜单上，单击“加载宏”。
线性规划作业（数学规划作业一）1、用两种编程方式求解下列问题2、将下述问题化成标准线性规划问题3、奶制品的生产销售计划一奶制品加工厂用牛奶生产A 1、A 2两种奶制品，1桶牛奶可以在甲类生产设备上用12h 加工成3kg A 1种奶制品，或在在乙类生产设备上用8h 加工成4kg A 2种奶制品.若A 1、A 2两种奶制品全部能售出,且甲种奶制品售价24元/kg, 乙种奶制品售价16元/kg 。
现在工厂每天能得到50桶牛奶,每天正式工人总的劳动时间为480h,且甲类生产设备每天至多加工100kg 甲种奶制品, 乙类生产设备每天加工乙种奶制品没有限制.为了增加工厂的获利，开发了奶制品的深加工技术，用2h 和3元加工费，可将1kg A 1加工成0.8kg 高级奶制品B 1;也可将1kg A 2加工成0.75kg 高级奶制品B 2，B 1与B 2售价分别为44元与32元，试为该工厂制订一个生产计划,使每天获利最大.并进一步讨论以下3 个问题:（1）、若用30元买一桶牛奶，投资3元可以增加1h 劳动时间，是否投资？若每天投资150元，可获利多少？（2）、每kg 高级奶制品B 1与B 2的获利经常有10%的波动，对制订生产销售计划有影响？若B 2的获利下降10%，计划是否变化？（3）、若工厂已签订了每天销售10kg A 1的合同并且必须满足，该合同对工厂的获利有什么影响？4、供水问题某市从A 、B 、C 三个水库向甲、乙、丙、丁四个生活区供应自来水，C 不能向丁区供水.四个生活区每天的基本生活用水分别为30，70，10，10（单位103t ），并且每天申请了额外的用水量分别为50,70,20,40（单位103t ）;三个水库每天最多只能供应50,60,50（单位103t ）.由于地理位置不同,向各区送水所需的引水管理费不同(表1),其他管理费每单位(103t)450元,但向各区都统一收取每单位(103t)900元.问怎样制定供水方案,才能使获利最大?为了增加供水量,拟对水库进行改造,使各水库的最大供水量增加1倍,问怎样制定供水方案,才能使获利最大?表1 引水管理费(元/103t)⎪⎩⎪⎨⎧-≤+---≤-+--≤+--2143214321432132132..x x x x x x x x x x x x t s 4321432min x x x x z +++=),,,{min(max 11211i mi im i mi i i mi i x x a x a x a i∑∑∑=== ⎩⎨⎧=≥=+++m i x x x x t s im ,,2,1,01.215、货物装运问题某架货机有前、中、后三个货舱,所能装载的货物的最大重量和体积都有限制(见表1),且三个货舱实载货物的重量与其最大限载重量成比例.表1 最大限载量现有四类货物由本机装载,信息如表2:表2 四类货物装载信息问应如何装运,使本架货机获利最大?。
附1：用LINGO求解线性规划的例子一奶制品加工厂用牛奶生产A1、A2附1:用LINGO求解线性规划的例子一奶制品加工厂用牛奶生产A、A两种奶制品，1桶牛奶可以在设备甲上用12小时加工成3公斤A，121或者在设备乙上用8小时加工成4公斤A。
根据市场需求，生产的A、A能全部售出，且每公斤A获利212124元，每公斤A获利16元。
现在加工厂每天能得到50桶牛奶的供应，每天正式工人总的劳动时间为4802 小时，并且设备甲每天至多能加工100公斤A，设备乙的加工能力没有限制。
试为该厂制定一个生产计划，1使每天获利最大，并进一步讨论以下3个附加问题:1)若用35元可以购买到1桶牛奶，应否作这项投资,若投资，每天最多购买多少桶牛奶,2)若可以聘用临时工人以增加劳动时间，付给临时工人的工资最多是每小时几元,3)由于市场需求变化，每公斤A的获利增加到30元，应否改变生产计划, 1数学模型:设每天用x桶牛奶生产A1 ，用x桶牛奶生产A2 12目标函数:设每天获利为z元。
x桶牛奶可生产3x公斤A1，获利24*3x，x桶牛奶可生产4*x公11122斤A2，获利16*4x，故z=72x+64x212约束条件:原料供应:生产A、A的原料(牛奶)总量不超过每天的供应50桶，即 12x+x?50 12劳动时间:生产A、A的总加工时间不超过每天正式工人总的劳动时间480小时，即 1212x+8x?480 12设备能力:A的产量不得超过设备甲每天的加工能力100小时，即 13x?100 1非负约束:x、x均不能为负值，即x?0，x?0 2121综上所述可得max z=72x+64x 12s.t.x+x?50 1212x+8x?480 123x?100 1x?0，x?0 21显然，目标函数和约束条件都是线性的，这是一个线性规划(LP)，求出的最优解将给出使净利润最大的生产计划，要讨论的问题需要考虑参数的变化对最优解和影响，一般称为敏感性(或灵敏度)分析。
