下载文档原格式
Excel 规划求解工具解决优化问题实例
一、安装和运行规划求解
要安装规划求解，请单击“工具”菜单上的“加载宏”，然后选择“规划求解”加载宏复选框。
单击“确定”，Excel 将安装规划求解。
安装该加载宏后，您可以通过单击“工具”菜单上的“规划求解”来运行规划求解。
二、定义优化模型
优化模型包括三部分：目标单元格、可变单元格和约束。
目标单元格代表目的或目标。
我们需要最小化或最大化目标单元格。
可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格。
约束是您置于可变单元格中的限制条件。
三、实例运用
已知条件：
运费B1 B2 B3 B4
A1 3 11 3 12
A2 1 9 2 8
A3 7 4 10 5
根据上述条件，怎样调运使总运费最少？
答案如图1、图2所示。
解题步骤如下：
1、确定目标单元格，如图2所示，“总费用”所在列的最后一格
2、确定可变单元格，如图2所示，“运量”所在列为可变单元格。
3、确定约束，如图2所示，“产量”、“销量”所在列的单元格。
附：约束条件单元格运算公式：
B9=SUM(D9+D13+D17)=3 C12=SUM(D9+D10+D11+D12)=7 B10=SUM(D10+D14+D18)=6 C16=SUM(D13+D14+D15+D16)=4 B11=SUM(D11+D15+D19)=5 C20=SUM(D17+D18+D19+D20)=9 B12=SUM(D12+D16+D20=8
目标单元格运算公式：
总费用=运量*运输费F21=SUM(总费用)
图1
图2。
解答Excel之规划求解
2006-09-30 23:38
一个工厂接了一批鼠标,键盘的订单,用现在的设备来生产,鼠标每个\1分钟,键盘第个\1.5分钟,1个鼠标的毛利是50元,1个键盘的毛利是75元,成本价鼠标为15元,键盘为20元,鼠标每日要生产最少200个,一天成本控制在10000元以下,每天10小时,这个工厂每天生产多少个鼠标?多少个键盘才能赚到最大的利润?
步骤1、首先在Excel表中输入如下内容：
其中“计划产量”中的值是自己随便输入的初始值。
最后3行是公式。
总时间：各自产量*各自单位时间；
总成本：各自产量*各自成本，然后相加求和；
总利润：各自产量*各自单位毛利，然后相加求和；
步骤2、设定规划求解参数。
工具->规划求解。
（如果没有，则工具—>加载宏，选择规划求解），设置参数如下图：
其中：“设置目标单元格”是所求的最大利润；
可变单元格是鼠标键盘的各自计划产量，即通过改变产量搭配，以实现在满足约束条件情况下得到最大利润；
几个约束条件的解释：
1)、鼠标、键盘的各自生产总时间不超过10小时（600分钟）；
2)、总成本不超过最大成本10000；
3)、鼠标产量不小于200；
点击“选项”，在弹出窗口中勾选“采用线性模型”和“假定非负”，然后单击“确定”。
步骤3、设置完成后，点击“求解”，规划求解将计算出一个最佳解决方案（如果有）。
本题中，我求得的结果是：
计划产量：鼠标200，键盘350；
最大利润：36250。
应用EXCEL规划求解工具进行优化1.线性规划—生产规划：步骤一：建立模型：每天生产甲乙两种产品分别为X1和X2，数学模型为：目标函数：minf（X1，X2）=60*X1+120*X2约束条件：9*X1+4*X2<=3603*X1+4*X2<=3004*X1+5*X2<=200-X1<=0-X2<=0用EXCEL建立模型如下：步骤二：规划求解参数确定：步骤三：选项参数确定：步骤四：求解：由上面求解过程可知：X1=20，X2=24时，可使目标函数值最小，即f（X1，X2）=4080. 2.工程下料问题规划求解：由题意可列出下列方案：步骤一：设使用8种方案的次数分别为X1，X2，X3，X4，X5，X6，X7和X8，且均为正整数，建立数学模型如下：目标函数：f（X）=（5*X1+10*X2+25*X3+5*X4+30*X5+10*X6+25*X7+5*X8）/（（X1+X2+X3+X4+X5+X6+X7+X8）*180）约束条件：gX1=2*X1+X2+X3+X4=100gX1=2*X2+X3 +3*X5+2*X6+X7gX1=X1+X3+33*X4 +2*X6+3*X7+5*X8用EXCEL建立模型如下：步骤二：规划求解参数确定：步骤三：选项参数确定：步骤四：求解：由上面求解过程可知：X1=23，X2=50，X3=0，X4=4，X5=0，X6=0，X7=0和X8=3时，可使目标函数值最小，即f（X）=0.045139.3．规划求解—工时安排：某厂生产A B C三种产品，净利润分别为90元，75元，50元；使用的机时数分别为3h，手工时数分别为4h，3h，2h，由于数量和品种受到制约，机工最多为400h，手工为280h，数量最多不能超过50件，C至少要生产32件。
求：如何安排A B C的数量以获得最大利润？解：建立数学模型：A、B、C三种产品的数量分别为X1，X2和X3，其利润为f（X）：目标函数：maxf（X）=90*X1+75*X2+50*X3约束条件：3*X1+4*X2+5*X3<=4004*X1+3*X2+2*X3<=280X1<=50X2>=32用EXCEL建立模型如下：步骤一：建立模型：步骤二：规划求解参数确定：步骤三：选项参数确定：步骤四：求解：由上面求解过程可知：X1=0，X2=93，X3=0时，可使目标函数值最大，即f（X）=11160.4.FORTRAN语言解读：C ======================SUBROUTINE FFX(N,X,FX) ；（目标函数定义）C ======================DIMENSION X(N)COMMON /ONE/ I1,I2,I3,I4,NFX,I6NFX=NFX+1P0=ACOS(((1.0+X(1))**2-X(2)**2+25.0)/(10.0*(1.0+X(1))))；（输入角初始值）Q0=ACOS(((1.0+X(1))**2-X(2)**2-25.0)/(10.0*X(2)))；（输出角初始值）T=90.0*3.1415926/(180.0*30.0) ；（将输入角30等分后每一份值）FX=0.0 ；（目标函数初始值）DO 10 K=0,30 ；（循环程序入口，循环次数30次）PI=P0+K*T ；（计算每一次循环后的输入角）QE=Q0+2.0*(PI-P0)**2/(3.0*3.1415926)；（计算每一次循环后的理想输出角）D=SQRT(26.0-10.0*COS(PI)) ；（与L1和L4相邻的连杆四边形对角线长度r）AL=ACOS((D*D+X(2)*X(2)-X(1)*X(1))/(2.0*D*X(2)))；（L3和r的夹角）BT=ACOS((D*D+24.0)/(10.0*D)) ；（L4和r的夹角）IF (PI.GE.0.0 .AND. PI.LT.3.1415926) THEN；（判断输入角是否在0到pi之间，计算实际输出角）QI=3.1415926-AL-BTELSEQI=3.1415926-AL+BTENDIFIF(K.NE.0 .OR. k.NE.30) THEN ；（判断循环次数是否在30次内,计算目标函数）FX=FX+(QI-QE)**2*T；ELSEFX=FX+(QI-QE)**2*T/2.0ENDIF10 CONTINUE ；（继续循环）END ；（程序段结束）C =========================SUBROUTINE GGX(N,KG,X,GX) ；（约束条件函数子程序）C =========================DIMENSION X(N),GX(KG) ；（定义GX<=0的约束条件函数）GX(1)=-X(1) ；（杆长L2>=0）GX(2)=-X(2) ；（杆长L1>=0）GX(3)=-(X(1)+X(2))+6.0 ；（最短杆L1和杆L4之和小于另两杆之和）GX(4)=-(X(2)+4.0)+X(1) ；（最短L1和杆L2之和小于另两杆之和条件）GX(5)=-(4.0+X(1))+X(2) ；（最短L1和杆L3之和小于另两杆之和条件）GX(6)=-(1.4142*X(1)*X(2)-X(1)**2-X(2)**2)-16.0 ；（传动角大于45度）GX(7)=-(X(1)**2+X(2)**2+1.4142*X(1)*X(2))+36.0；（传动角小于135度）ENDC =========================SUBROUTINE HHX(N,KH,X,HX) ；（约束条件函数子程序）C =========================DIMENSION X(N),HX(KH) ；（定义HX=0的约束条件函数）X(1)=X(1)END5.学习心得：这次作业让我收获了很多，通过课堂上的学习，让我对优化设计有了一个充分的认识，老师的讲解细致入微，也让我对这门课充满了兴趣。
利用Excel 进行规划求解Excel 具有规划求解的基本功能，包括线性规划和非线性规划。
对于常规的线性规划问题，Excel 就可以给出求解结果。
对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab 了。
不过，大多数规划问题Mathcad 即可完成所赋予的任务。
利用Excel 求解规划问题有些“罗嗦”，但也不难掌握。
下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。
【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。
有关生产情况的各种数据资料见下表。
3 果汁杯6 h/百件 10 m 3/百件 600件 600元/百件 鸡尾酒杯 5 h/百件 20 m 3/百件 0件 400元/百件 *注：定点量为每周生产的最大数量。
若每周工作不超过50小时，且拥有储藏量为140m3的仓库。
问：⑴ 该个体户如何安排工作时间才能使得每周的收益最大？⑵ 若每周多干1小时，收益增大多少？⑶ 通过加班加点达到的收益极限是多少？解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下：21400600)(Max x x x f +=⎪⎪⎩⎪⎪⎨⎧≥≥≤≤+≤+0,0614020105056 s.t.2112121x x x x x x x 显然，约束条件中的第三个式子x 1≤6可以表作1*x 1+0*x 2≤6，从而有如下矩阵⎥⎦⎤⎢⎣⎡=400600c ，⎥⎦⎤⎢⎣⎡=21x x x ，⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=01201056A ，⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=614050b 容易看到，上述模型表为矩阵形式便是：目标函数为[]⎥⎦⎤⎢⎣⎡==21400600)(Max x x x c x f T 约束条件为⎪⎪⎪⎩⎪⎪⎪⎨⎧≥⎥⎦⎤⎢⎣⎡=⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=≤⎥⎥⎥⎦⎤⎢⎢⎢⎣⎡=061405001201056 s.t.21x x x b Ax下面是利用Excel 求解规划结果的详细步骤：第一步，录入数据，定义有关单元格在Excel 中，将有关数据资料按一定的规范录入，最好按照资料表格录入。
1、生产问题
某工厂生产I，II两种食品，现有80名熟练工人，己知一名熟练工人每小时可生产10千克食品I或8千克食品II。
据合同预订，该两种食品每周的需求量将急剧上升，见下表。
为此该厂决定到第8周末需培训出60名新的工人，两班生产。
已知一名工人每周工作40小时，一名熟练工人用两周时间可培训出不多于三名新工人(培训期间熟练工人和培训人员均不参加生产)。
熟练工人每周工资320元，新工人培训期间工资每周180元，培训结束参加工作后工资每周260元，生产效率同熟练工人。
在培训的过渡期间，很多熟练工人愿加班工作，工厂决定安排部分工人每周工作80小时，工资每周480元。
又若预订的食品不能按期交货，每推迟交货一周的赔偿费食品I为0.4元，食品II 为0.8元。
在上述各种条件下，试建立该问题的线性规划模型，以便作出合理全面的安排，使各项费用的总和为最小。
建立该问题的电子表格模型，填写下列电子表格。
2、项目选择问题
某个制药公司需要开发四个新的研究项目，为了使所有项目的成功性最高，派了六位科学家来对这四个项目进行投标选择。
每位科学家具有1000点可以投标，投标点数越大，表示科学家对该项目越感兴趣，成功的可能性就越高。
投标具体情况如下表所
没有该领域的知识或其他原因而不能从事该项目的研究与开发。
目标是使投标总点数最高，应该如何指派。
建立该问题的电子表格模型，填写下列电子表格。
某物流公司希望以最小的成本完成一种物资的配送，其运出货物数量、分配量和各段线路单位运输成本如下表所示。
另外，由于运输能力限制，从各个工厂到配送中心，以及由配送中心到各个仓库运输产品的数量均不超过60。
建立该问题的电子表格模型，填写下列电子表格。
4、租赁问题
因为资金和管理水平的限制，某公司想以相同的价格和不同的租期（工时）租赁另一公司的甲、乙、丙、丁四个车间中的两个来生产新开发的五种产品A,B,C,D,E中的三种。
由于不同车间的机床和工人的经验不同，因此生产不同产品的效率也不同，导致不同的产品所用的工时也不同。
每种产品的单位利润和租期内的最大销售量以及各车间在租期内的总工时等数据如下表。
求公司管理者应如何选择车间和产品，才能使租期内所获得的利润最大？
产品工时消耗及相关数据
建立该问题的电子表格模型，填写下列电子表格。
5、载货问题
一艘运货的飞机有三个用于存放货物的机舱：前、中、后。
这些机舱有载货的重量与体积限制，如下表所示：
此外，在每个机舱里实际装载的货物重量的比例必须与载货量的比例相同，以保持飞机的平衡。
下面是为一架飞机所准备的四种货物：
目标是要确定每种货物的运载量以及在各个机舱中如何分配，使得一个航班的收益最大化。
建立该问题的电子表格模型，填写下列电子表格。
