EXCEL规划求解题
Excel 规划求解工具解决优化问题实例
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线性规划求解
To Calculate Total LHS, 選擇SUMPRODUCT
1 2
選定第一列範圍
選定第二列範圍
SUMPRODUCT(F6:G6,G3:G3)=F6*F3 + G6*G3
Then repeat the same steps for constraint #2 and #3
輸入Slack 公式
求最小值
1.輸入變數x1, x2的值所在的儲存格 2.『新增』限制式
1.輸入限制式左邊及右邊的儲存格 2.選擇適當的符號
左邊
右邊
1.選擇後之結果 2.按『新增』
1.此限制式表示 constraint #1and #2 2.再新增 constraint #3
Constraint #3的左邊，右邊及符號
1.前三項限制式(constraints#1,#2,#3) 2.選擇『選項』
新增 constraint #4: x1 >=0 constraint #5: x2 >=0
按『求解』後的結果
想將此圖轉貼於 EXCEL工作表上 1.選此按鈕 2.回到EXCEL
1.按貼上的按鈕或 『編輯』下的貼上， 即顯示圖案。 2. 將之移至適當位置
完成
线性规划求解
Linear Programming Problem
輸入公式Βιβλιοθήκη 列出EXCEL的求解方式: Min:Minimum Objective Value=F4*F3+G4*G3 LHS: Left Hand Side RHS: Right Hand Side Slack: RHS - LHS for “<=“ LHS- RHS for “>=“
EXCEL求解线性规划问题
约束右端值降低15时，目旳函数值旳变化量。
解：（1）最优解为x1=0, x2=12.4, x3=9.5
（2） x1旳目旳系数降低5，占允许降低旳百分比=5/∞=0%，x2 旳目旳系数增长4,占允许增长旳百分比=4/7.8=51.2%。
变化旳百分比和为51.2%，没有超出100%，所以最优解不变。
（3）第一资源约束右端值增长30，占允许增长旳30 /∞=0%， 第二资源约束右端值增长4 ，占允许增长旳4/15=26.7%，
•初值和终值分别指 单元格在此次求解 前旳数值和求解后 旳数值。
敏感性分析报告（1）
可变单元格中 • “单元格”指决策变量所在单元格旳地址 • “名字”是决策变量旳名称 • “终值”是决策变量旳终值,即最优值 • “递减成本”指最优解中档于0旳变量，相应旳目旳函数中旳系数
增长或降低多少，最优解不再为0 • “目旳式系数”目旳函数中旳系数，为已知条件 • “允许旳增量”与“允许旳减量”表达目旳函数中旳系数在增量
（1）引用旳类型
三种类型 ：
相对引用、 绝对引用、混合引用
（2） 相对引用
格式： A3 、B6
使用相对引用后，系统将会记住建立公式旳单元格和被 引用旳单元格旳相对位置，在复制这个公式时，新旳公式单 元和被引用旳单元依然保持这种相对位置。
（3）绝对引用 格式：$a$3 $d$5
绝对引用是指被引用旳单元与引用旳公式单元旳位置 关系是绝正确，不论将这个公式复制到任何单元，公式所 引用旳还是原来单元格旳数据。
2) 在弹出旳对话框中旳“可用加载宏”列表框 中,选定待添加旳加载宏“规划求解”选项旁 旳复选框,然后单击“拟定”.单击“拟定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
解答Excel之规划求解(说明)
解答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规划求解实例
应用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规划求解案例分析
什么是规划问题?
在计划管理中常会遇到：人力资源的调 1、约束条件的表达 度、产品生产的安排、运输线路的规划、生 产材料的搭配、采购批次的确定等问题。 2、目标的数学描述 这类问题有一个共同点，即需要解决： 3、应用Excel的规划 如何合理利用各种存在约束的资源，而获得 求解工具对问题求解 最佳的经济效益，也就是达到利润最大、成 本最低等目标。这就是本节要解决的“在约 束条件下寻求目标函数最优解的规划问题”。
第五步 选择“采用线性模型”和“假定非负”， 单击“确定”，返回下图。单击“求解”，即可解 决此题。
最后结果如下页图所示。
用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。
递减成本指目标函 数中决策变量的系 数必须改进多少才 能得到该决策变量 的正数解，改进对 最大值为增加，对 最小值为减少。 最优解
c
+ △c
-△c
实际使用量
对偶最 优解
b
+△
b
-△
b
5. 敏感性分析
• 在实际问题中，规划模型中的大多数数 据是测量、统计、评估或决策而得出来的。 因此有必要分析当这些数据发生波动时会 对最优解和最优值产生什么影响。这就是 灵敏度分析。
出现以下假设，上述案例如何决策？？?
• （2）家具厂是否愿意出10元的加班费，让 某工人加班1小时？
产品 I II 限量
原材料(kg/件)
设备工时(h/件) 利润(元/件)
5
4 6
10
4 8
60
40
问该公司应制造两种家电各多少件，使获取的利润 为最大。
设产品I和II的产量分别为x1和x2，其数学 模型为：
max Z= 6x1 +8x2 5x1 + 10x2 ≤60 st. 4x1 + 4x2 ≤40 x1 , x2 ≥0
Excel表格法求解路径规划问题
实验四： Excel 表格法求解路径规划问题
根据题意及决策变量与目标函数得出本问题的 线性规划模型。
minY=6×X11+3×X12+2×X13+5×X14+7×X21+5×X2 2+8×X23+4×X24+3×X31+2×X32 +9×X33+7×X34
s.t. X11+ X12+ X13+ X14=5（满足A1矿的产量） X21+ X22+ X23+ X24=2（满足A2矿的产量） X31+ X32+ X33+ X34=3（满足A3矿的产量） X11+ X21+ X31 =2（满足B1厂的需求量） X12+ X22 +X32 =3（满足B2矿的需求量） X13+ X23 +X33 =1（满足B3矿的需求量） X14+ X24 +X34 =4（满足B4矿的需求量） Xij >=0(i=1,2,3,j=1,2,3,4)(决策变量非负约束)
执行【工具】→【加载宏】 菜单命令，这时将出现 “加载宏”的对话框，在 “可用加载宏”窗口的 “规划求解”选项上打 “√”。当需要进行规划求 解操作时，直接进行该命 令，就可进入“规划求解 参数”对话框。
实验四： Excel 表格法求解路径规划问题
根据题意，设置本问题的决策变量和目标 函数。 设：Xij为每天从Ai矿运往Bj厂的矿石数量 （百吨），Y为总运费 Y=6×X11+3×X12+2×X13+5×X14+7 ×X21+5×X22+8×X23+4×X24+3×X3 1+2×X32 +9×X33+7×X34 ，则本问题 表格法求解路径规划问题
Excel(4)---规划求解多变量求解。
三、约束条件
（1）女运动鞋销售量不要超过5200 双；（2）男运动鞋的销量不可超过总 量58%；（3）边际利润在200万元以上 。
四、最大边际利润
男运动鞋（双） 女运动鞋（双）
初始数据 销售量
7180 5200
运动鞋的总生产量 每双运动鞋统一单价
12380 500
成本 350 元 300 元
成本 350 元 300 元
销售数量 成本
玻璃纤维
4279
铝合金
碳纤维
8854
总计
请利用单 变量求解 工具，调 整铝合金 网球拍的 销售数 量，调整 至总成本 刚好等于 投入金额 150万元 。
此时的铝 合金网球 拍的销售 数量是多 少？总利 润是多 少？
合计成本
利润
1350
பைடு நூலகம்
5776650
480
850
7525900
13302550
1、利用 规划求解 工具求解 系列数学 方程式的 变量x和y 的值。
X变量 Y变量 所得值
5.104615234 2.807822282 -1.8573E-07
2、某运 动用品公 司生产3 种不同材 质的网球 拍，已知 玻璃纤维 网球拍每 支成本 1350元， 利润350 元；
铝合 金网球拍 每支成本 480元， 利润100 元；碳纤 维网球拍 每支成本 850元， 利润200 元。
此时的铝 合金网球 拍的销售 数量是多 少？总利 润是多 少？
合计成本
利润
1350
5776650
480
850
7525900
13302550
合计利润 350 1497650 100 200 1770800
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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、载货问题
一艘运货的飞机有三个用于存放货物的机舱：前、中、后。
这些机舱有载货的重量与体积限制，如下表所示：
此外，在每个机舱里实际装载的货物重量的比例必须与载货量的比例相同，以保持飞机的平衡。
下面是为一架飞机所准备的四种货物：
目标是要确定每种货物的运载量以及在各个机舱中如何分配，使得一个航班的收益最大化。
建立该问题的电子表格模型，填写下列电子表格。