EXCEL规划求解案例分析
利用EXCE的规划求解进行求解威布尔分布参数
利用EXCE的规划求解进行求解威布尔分布参数
由于威布尔分布的可以描述独立同分布变量的分布,经常被用于不同
概率密度函数模型之间的相互比较,因此其参数估计一直是建模分析的重
要环节,使用EXCEL可以规划求解威布尔分布参数,我们以以下案例来求
解该分布参数:
假设有一组随机样本x(1),x(2),…,x(n),满足威布尔分布,想对α
和β参数进行估计,那么我们可以使用下面的方法:
1.首先,使用EXCEL编写对数似然函数,其表达式为:
lnL=ln[αβ^(α+n)]+α∑lnx-β∑x-nlnβ
这里α,β为待求参数。
2.编写规划过程求解α、β估计值。
具体而言,我们需要构建EXCEL规划模型,使得对数似然函数最大,而其估计值α、β即为结果。
我们以EXCEL求解威布尔分布参数为例,指导将这一过程编写如下:
1.首先,在EXCEL中编写对数似然函数,其表达式为:
lnL=ln[αβ^(α+n)]+α∑lnx-β∑x-nlnβ
这里α,β为待求参数,其取值范围通常设置为大于0小于100,因此,可以将参数α作为变量编写入EXCEL规划模型,即:
MIN = lnL
S.T.0 < α < 100 and0 < β < 100
2.在EXCEL中编写对数似然函数,其表达式为:
lnL=ln[αβ^(α+n)]+α∑lnx-β∑x-nlnβ
其中α,β为待求参数,α ∑ lnx 为样本的对数期望值, -β ∑x 为样本的期望值,而n ln β 为测量方差。
excelsolver(规划求解)的用法及例子
excelsolver(规划求解)的⽤法及例⼦Solve Linear Programming ProblemsCheck that Solver is installedOpen ExcelClick on the ‘tools’ menuIf Solver is listed, then go to Formulation.Otherwise, Solver needs to be installed, as follows:Again under ‘tools’ click ‘Add-ins..’.The window that appears lists the available add-ins,Click the box next to Solver so that it contains a tick, click ok.Solver should now appear under the ‘tools’ menuFormulationWhenever we formulate a worksheet model of a linear program, we perform the following steps (Par. problem as an example, see appendix):Step 1: Enter the data in the worksheetCells B7:C10 show the production requirements per unit for each product.Cells B5:C5 show the profit contributions per unit for the two products.Cells F7:F10 show the number of hours available in each department.Step 2: Specify cell locations for the decision variablesCells B4:C4.Step 3: Select a cell and enter a formulation for computing the objective value function.Cell D5: =B4*B5+C4*C5 or SUMPRODUCT($B$4:$C$4,$B5:$C5)Step 4: Select a cell and enter a formulation for computing the left-hand side of each constraint.Cell D7:=B4*B7+C4*C7 or SUMPRODUCT($B$4:$C$4,$B7:$C7) (copy from Cell D5)Cell D8:=B4*B8+C4*C8 or SUMPRODUCT($B$4:$C$4,$B8:$C8) (copy from Cell D5)Cell D9:=B4*B9+C4*C9 or SUMPRODUCT($B$4:$C$4,$B9:$C9) (copy from Cell D5)Cell D10:=B4*B10+C4*C10 or SUMPRODUCT($B$4:$C$4,$B10:$C10) (copy from Cell D5)Tips:(1)SUMPRODUCT function requires specifying two cell ranges of equal size, separated by a comma, such as SUMPRODUCT($B$4:$C$4,$B5:$C5). The SUMPRODUCT function computes the products of the first entries in each range, second entries in each range, and so on. It then sums these products.(2) The $ symbol in the cells keeps that cell reference fixed when we copy the formula. This is especially convenient since the formula for calculating the sum of the left-hand-side value for each constrain also follows the same structure as the objective function.Excel SolutionThe following steps show how Solver can be used to obtain the optimal solution to the Par, Inc., problem. Step 1: Select the Tools pull-down menu.Step 2: Select the Solver option.Step 3: When the Solver Parameters dialog box appears.Enter D5 into the Set Cell boxSelect the Equal to: Max optionEnter B4:C4 into the By Changing Variable Cells box.Select Add.Step 4: When the Add Constraint dialog box appears:Enter D7:D10 in the Cell Reference boxSelect <=Enter F7:F10 into the Constraint boxClick OKStep 5: When the Solver Parameters dialog box reappears:Choose Options.Step 6: When the Solver Options dialog box appears,Select Assume Linear Models and Assume Non-negativeClick OK.Step 7: When the Solver Parameters dialog box reappears:Choose Solve.Step 8: When the Solver Results dialog box appears:Select Keep Solver Solution, and choose Answer and Sensitivity from Reports box. The following table shows Excel layout for the Par. problem.The answer report for the Par. problem is:Answer the following questions:1.a.Which constraints are binding? Which are not binding?b.What is the range of optimality for the objective function coefficient associated with standard bags?c.What is the range of optimality for the objective function coefficient associated with deluxe bags?d.After the production, how many hours remain in finishing, and inspection and packaging department?e.What would be the impact on the production plan and profit if the objective function coefficientassociated with standard bags were to change to 12?f.What would be the impact on the production plan and profit if the number of sewing department were to decrease to 500?g.What would be the impact on the production plan and profit if the objective function coefficient associated with standard bags were to change to 9 while at the same time the objective function coefficient associated with deluxe bag were to change to 8?2. Solve M&D Problem. (Answer: Obj=800)3. Solve PM Problem. (Answer: Obj=216,300)4. Solve MSA Problem. (Answer: Obj=15,166)5. Solve Whole Wood Problem. (Answer: Obj=0.05)。
EXCEL规划求解案例分析 ppt课件
例1. 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具,由于该四种家具具 有不同的大小、形状、重量和风格,所以它们所需 要的主要原料(木材和玻璃)、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时,详细的数据资料见下表。
完整的模型描述:
第二步 在“工具”菜单中选择“规划求解”。
第三步 在“规划求解参数”对话框进行选择如下图。
第四步 点击“选项”按钮,弹出“规划求解选项”对话框
第五步 单击“求解”,即可解决此题。
最后结果如下页图所示。
实验内容:
分别运用 EXCEL和LINDO 求解, 学委 在下周上课前把电子版的结果收齐。
运用EXCEL求解线性规划问题
广东商学院 数学与计算科学学院
outline
1.关于“规划求解” 2.如何加载“规划求解” 3. “规划求解”各参数设置 4. “规划求解”步骤 5. 利用“规划求解”解线性规划问题
1. 关于“规划求解”
Microsoft Excel的“规划求解”工具取自德克 萨斯大学奥斯汀分校的Leon Lasdon和克里 夫兰州立大学的Allan Waren共同开发的 Generalized Reduced Gradient(GRG2)非线性 最优化代码. 线性和整数规划问题取自 Frontline Systems公司的John Waston和Dan Fylstra提供的有界变量单纯形法和分支定界 法
(木材约束)
6x1 2x2 x3 2x4 1000 (玻璃约束)
2x1 1x2 3x3 2x4 400 (劳动时间约束)
s.t
.
Excel规划求解简单例子
Excel规划求解简单例子
1、为了保证人们的健康,若干种养分的日供给量不得少于某个最低值,否则就会因营养缺乏而致病,为简单起见,假设需要三种养分A、B、C(例如蛋白质、维生素、微量元素),并假设人们的食谱由两类食物构成,有关数据如下表所示。
在满足营养要求情况下如何进行是的费用最少?
解:首先根据题意,建立线性方程组:
1、根据题意建立数据表。
然后在B3、D5、D6、D7单元格插入sumproduct函数(工具栏—插入—函数,在函数参数界面直接拖选单元格即行)。
B
2、C2分别代表X1、X2,B4、C4分别是目标函数的系数。
应用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:某工厂生产两种新产品:门和窗。经测算,每 生产一扇门需要在车间1加工1小时、在车间3加工3小 时;每生产一扇窗需要在车间2和车间3各加工2小时。 而车间1每周可用于生产这两种新产品的时间为4小 时、车间2为12小时、车间3为18小时。已知每扇门 的利润为300元,每扇窗的利润为500元。根据市场 调查得到的这两种新产品的市场需求状况可以确定, 按当前的定价可确保所有的新产品均能销售出去。 问:该工厂如何安排这两种新产品的生产计划,才 能使总利润最大?
$D$12) 复制E7单元格到E8、E9
EXCEL求解线性规划模型
(3)总利润计算: 在G12单元格输入公式: =C4*C12+D4*D12 或: =SUMPRODUCT(C4:D4,C12:D12)
EXCEL求解线性规划模型
在电子表格中建立线性规划模型步骤总结
收集问题数据; 在电子表格中输入数据(数据单元格); 确定决策变量单元格(可变单元格); 输入约束条件左边的公式(输出单元格)使用
EXCEL求解线性规划模型
2、主要求解结果 ■两种新产品每周的产量; ■两种新产品每周各实际使用的工时 (不能超过计划工时); ■两种新产品的总利润
EXCEL求解线性规划模型
3、主要结果的计算方法
(1)两种新产品的每周产量:C12、D12,初始 值为0。
(2)实际使用工时计算(三种方法) 1)分别在E7、E8、E9中输入相应的计算公 式:
例:车间2:12——13,车间3:18——17 例:车间2:12——16,车间3:18——15
EXCEL求解线性规划模型
5、aij变化 例:由于车间2采用新的生产工艺,生产
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进行线性规划求解功能,轻松找到问题的最优的解决方案
使用Excel进行线性规划求解功能,轻松找到问题的最优的解
决方案
在我们的工作中,规划求解是十分常见的应用场景,是一种研究线性约束条件下线性目标函数的极值问题的数学理论和方法。
比如在生产管理中,在人工、材料等等条件的约束下,如何安排才能使工厂利益的最大化问题就是典型的规划问题。
而对于此类问题的求解,如果使用手工求解的方式还是存在一定的困难,但是如果使用Excel这个工具的话,就能轻松的进行求解。
下面,我就通过一个工厂生产利润最大化的例子来给小伙伴们讲解下具体的使用方法。
题目:某家具生产厂可以生产A、B、C、D四种家具,四种家具所需要的人工、木材、玻璃等的量是不同的,同时由于市场
的限制,每种家具的最大销售量也是有限制的。
四种家具的所
需材料、市场限额、利润见下表:
根据上述要求,可以设该厂生产A、B、C、D四种家具的量分别为X1、X2、X3、X4,则利润为:maxZ=60X1+66X2+40X3+50X4。
约束条件如下:
根据以上条件,在Excel中做出以下求解模版:
根据以上分析,目标值单元格的公式如下:
=SUMPRODUCT(B13:E13,B6:E6)。
时间约束,木材约束,玻璃约束的使用量公式分别为:=SUMPRODUCT(B18:E18,$B$13:$E$13)
=SUMPRODUCT(B19:E19,$B$13:$E$13)
=SUMPRODUCT(B20:E20,$B$13:$E$13)
专栏
从进销存系统入门ExcelVBA编程。
利用Excel进行规划求解
利用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 中,将有关数据资料按一定的规范录入,最好按照资料表格录入。
用Excel求解线性规划问题实例
单位时耗(小时/吨) 资源设备
ⅠⅡ
每天现有工 时
搅拌机 成型机
34
15
21
5
烘箱
22
11
利润(百元/吨)
54
解:用变 量x 1 和 x 2 分别表 示光华食 品厂生产 饼干I和 饼干II的 数量。
目标函数 约束条件
max Z 5x1 4x2
3x1 4x2 15
s.t.
2 2
x1 x1
x2 5 2x2 11
x1, x2 0
资源设备
Ⅰ型饼干 Ⅱ型饼干 约束条件
搅拌机
3
4
15
<=
成型机
2
1
5
<=
烘箱2ຫໍສະໝຸດ 28<=
利润(百元/吨)
5
4
目标值
目标函数:maxZ=5x1+4x2 1
3
17
x1
x2
资源上限
15 5 11
资源设备
搅拌机 成型机
烘箱
利润
例1 资源利用问题的求解
光华食品厂主要生产葱油饼干(Ⅰ型)和苏打饼干(Ⅱ型),销售利润 分别为500元/吨和400元/吨。根据销售部门提供的信息可知,目前这两 种饼干在市场上都很畅销,该厂能生产多少,市场就能卖出多少。但从 生产部门得知,有三种关键设备即搅拌机、成型机、烘箱的生产能力, 限制了该厂的饼干生产。该公司每天生产这两种饼干的量应为多少,可 使其利润最大?其具体数据如表所示:
EXCEL规划求解案例分析
目标规划问题的提出
例1 某工厂生产两种产品;受到原材料供应和设备工 时的限制 在单件利润等有关数据已知的条件下;要求 制定一个获利最大的生产计划 具体数据如下:
产品
I
II
原材料kg/件 5
10
设备工时h/件 4
4
利润元/件
6
8
限量 60 40
问该公司应制造两种家电各多少件;使获取的利润为 最大
雅致家具厂生产4种小型家具;由于该四种家具具有 不同的大小 形状 重量和风格;所以它们所需要的主 要原料木材和玻璃 制作时间 最大销售量与利润均 不相同 该厂每天可提供的木材 玻璃和工人劳动时 间分别为600单位 1000单位与400小时;详细的数据 资料见下表
应如何安排这四种家具的日产量;使得该厂的日利润 最大
• 单击添加;显示添加约束对话框
• 选项:显示规划求解选项对话框 在其中可以加 载或保存规划求解模型;并对规划求解过程的 高级属性进行控制
4 规划求解步骤
⑴ 启用规划求解宏; ⑵ 输入数据; ⑶ 利用函数SUMPRODUCT引入约束与目标 ⑷ 对话框规划求解的各要素
例1 雅致家具厂生产计划优化问题
SUMPRODUCT函数
• SUMPRODUCT的意思是:乘积之和 • 在给定的几组数组中;将数组间对应的
元素相乘;并返回乘积之和 • 语法 • SUMPRODUCTarray1;array2;array3; • Array1;array2;array3; 为 2 到 30 个数
组;其相应元素需要进行相乘并求和
最后结果如下页图所示
用Excel求解得对应的敏感性报告灵敏度分析如下表所示
最优解
递减成本指目标函 数中决策变量的系 数必须改进多少才 能得到该决策变量 的正数解;改进对最 大值为增加;对最小
规划求解究竟有多好用?我用九个案例给你答案「全动图演示」
案例6:题目 接下来我们看下解题过程: 案例6:计算最优组合1
案例6:规划求解参数设置
解析:此题设置了三个约束条件,一为目标产量是整数,二是目标产量大于等于最低要求产 量,三是原料消耗总量小于等于现有原料。
案例 7:取最优组合 2。
案例1解题演示
案例1:规划求解的参数设置 下面给大家说说规划求解中,各约束条件的含义(约束条件是对可变单元格的值进行约束):
解析:案例1中,我们设置的约束条件为【bin】二进制,即符合条件的数据,在可变单元格中 显示1;不符合条件的数值,在可变单元格中显示0。
案例 2:解一元方程。公式【 3^x+6^x= 8^x】,求 x 的值
案例 5:趣味填数游戏 2,要求详见下图
案例5:题目
此题的解题过程如下: 案例5:趣味填数游戏2
案例5:规划求解参数设置 解析:此题的九宫格由于是连续区域,故不需要辅助单元格;目标值用的是各边之和的和 (120);约束条件有两条,一为九宫格区域为不重复值,二是用每边的和等于15(正常情况 下,应该还有一个约束条件,即九宫格区域额值<=9,由于设置了和为15,所以这个条件可以 忽略)。
案例9:题目 此题解其中一个的过程: 案例9解其中一题 但是本题数据较多,如果一道题、一道题的解,很浪费时间。由于此题比较有规律,就可以用 VBA来实现批量解题。在VBA中使用规划求解,需要引用Solver
引用Slover 然后用以下代码,就可以实现批量解题:
Sub 批量执行规划求解() Application.AlertBeforeOverwriting = False Dim a As Integer, Arr Arr = Range('F4:F' & Cells(Rows.Count, 1).End(xlUp).Row) Dim Rng1, Rng2 For a = 4 To UBound(Arr, 1) + 3 Rng1 = Range('$E$' & a).Address Rng2 = Range('$B$' & a & ':$D$' & a).Address SolverReset SolverOk SetCell:=Rng1, MaxMinVal:=3, ValueOf:=Arr(a - 3, 1), ByChange:=Rng2, Engine:=1, EngineDesc:='GRG Nonlinear' If Arr(a - 3, 1) = VBA.Int(Arr(a - 3, 1)) Then SolverAdd CellRef:=Rng2, Relation:=4, FormulaText:='整数' Else SolverAdd CellRef:=Rng2, Relation:=3, FormulaText:=0.1 End If SolverSolve Userfinish = False Next a Application.AlertBeforeOverwriting = True End Sub
利用excel软件求解线性规划问题分析
下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。
例1 公司通常需要确定每月(或每周)生产计划,列出每种产品必须生产的数量。
具体来说就是,产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。
产品组合通常必须满足以下约束:● 产品组合使用的资源不能超标。
● 对每种产品的需求都是有限的。
我们每月生产的产品不能超过需求的数量,因为生产过剩就是浪费(例如,易变质的药品)。
下面,我们来考虑让某医药公司的最优产品组合问题。
该公司有六种可以生产的药品,相关数据如下表所示。
设该公司生产药品1~6的产量分别为126,,,x x x L (磅),则最优产品组合的线性规划模型为123456123456123456123456max 6 5.3 5.4 4.2 3.8 1.86543 2.5 1.545003.2 2.6 1.50.80.70.316009609281041..977108410550,16j z x x x x x x x x x x x x x x x x x x x x x s t x x x x j =++++++++++≤⎧⎪+++++≤⎪⎪≤⎪≤⎪⎪≤⎨⎪≤⎪≤⎪⎪≤⎪⎪≥≤≤⎩下面用规划求解加载宏来求解这个问题: 首先,如下如所示,在Excel 工作表内输入目标函数的系数、约束方程的系数、右端常数项;其次,选定目标函数单元、可变单元、约束函数单元,定义目标函数、约束函数其中,劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”,原料约束函数的定义公式是“=MMULT(B4:G4,J5:J10)”,目标函数的定义公式是“MMULT(B5:G5, J5:J10)”。
注:函数MMULT(B3:G3, J5:J10)的意义是:单元区B3:G3表示的行向量与单元区J5:J10表示的列向量的内积。
这一要特别注意的是,第一格单元区必须是行,第二格单元区必须是列,并且两个单元区所含的单元格个数必须相等。
【案例】用Excel线性规划求解实现资源价值优化
【案例】用Excel线性规划求解实现资源价值优化线性规划(Linear programming,简称LP)是运筹学中研究较早、发展较快、应用广泛、方法较成熟的一个重要分支,它是辅助人们进行科学管理的一种数学方法。
研究线性约束条件下线性目标函数的极值问题的数学理论和方法。
英文缩写LP。
它是运筹学的一个重要分支,广泛应用于军事作战、经济分析、经营管理和工程技术等方面。
为合理地利用有限的人力、物力、财力等资源作出的最优决策,提供科学的依据。
对于LP问题,核心的一句就是:研究线性约束条件下线性目标函数的极值。
为了解决这样的问题,我们要做的其实就是两件事:1、找出约束条件和目标函数;2、在条件下找出目标的最优解(最大或最小值)。
图解法、单纯形法等都是解决这类问题的手算方法,然而随着计算机发展,我们已经有很多软件可以实现自动解决这类问题。
那么今天就用excel来说说,怎么设置,然后得出答案。
在开发工具里面(2007在“文件”---“选项”里),找到加载项,通过加载宏'规划分析求解项',然后我们就能看到下图选项。
【应用实例】生产安排模型:某工厂要安排生产Ⅰ、Ⅱ两种产品,已知生产单位产品所需的设备台时及A、B两种原材料的消耗,如表所示,表中右边一列是每日设备能力及原材料供应的限量,该工厂生产一单位产品Ⅰ可获利2元,生产一单位产品Ⅱ可获利3元,问应如何安排生产,使其获得最多?解:1.确定决策变量:设x1、x2为产品Ⅰ、Ⅱ的生产数量;2.明确目标函数:获利最大,即求2x1+3x2最大值;3.所满足的约束条件:设备限制:x1+2x2≤8原材料A限制:4x1≤16原材料B限制:4x2≤12基本要求:x1,x2≥0用max代替最大值,s.t.(subject to 的简写)代替约束条件,则该模型可记为:max z=2x1+3x2s.t. x1+2x2≤84x1≤164x2≤12x1,x2≥0通过设置函数,我们将上面条件列在约束列里,如下图4.用Excel求解点击求解之后,我们就完成了一次小型的规划求解了。
利用excel软件求解线性规划问题讲解
下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。
例1 公司通常需要确定每月(或每周)生产计划,列出每种产品必须生产的数量。
具体来说就是,产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。
产品组合通常必须满足以下约束:● 产品组合使用的资源不能超标。
● 对每种产品的需求都是有限的。
我们每月生产的产品不能超过需求的数量,因为生产过剩就是浪费(例如,易变质的药品)。
下面,我们来考虑让某医药公司的最优产品组合问题。
该公司有六种可以生产的药品,相关数据如下表所示。
设该公司生产药品1~6的产量分别为126,,,x x x (磅),则最优产品组合的线性规划模型为123456123456123456123456max 6 5.3 5.4 4.2 3.8 1.86543 2.5 1.545003.2 2.6 1.50.80.70.316009609281041..977108410550,16j z x x x x x x x x x x x x x x x x x x x x x s t x x x x j =++++++++++≤⎧⎪+++++≤⎪⎪≤⎪≤⎪⎪≤⎨⎪≤⎪≤⎪⎪≤⎪⎪≥≤≤⎩下面用规划求解加载宏来求解这个问题: 首先,如下如所示,在Excel 工作表内输入目标函数的系数、约束方程的系数、右端常数项;其次,选定目标函数单元、可变单元、约束函数单元,定义目标函数、约束函数其中,劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”,原料约束函数的定义公式是“=MMULT(B4:G4,J5:J10)”,目标函数的定义公式是“MMULT(B5:G5, J5:J10)”。
注:函数MMULT(B3:G3, J5:J10)的意义是:单元区B3:G3表示的行向量与单元区J5:J10表示的列向量的内积。
这一要特别注意的是,第一格单元区必须是行,第二格单元区必须是列,并且两个单元区所含的单元格个数必须相等。
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
2.如何加载“规划求解” 如何加载“规划求解” 如何加载
1) 在“工具”菜单上,单击“加载宏”
2) 在弹出的对话框中的“可用加载宏”列表框 中,选定待添加的加载宏“规划求解”选项旁 的复选框,然后单击“确定”.单击“确定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置 规划求解” 规划求解
x1 , x2 , x3 , x4 ,目标要求是日利润最大化,
约束条件为三种资源的供应量限制和产品销售量限制。 据此,列出下面的线性规划模型:
MaxZ = 60 x1 + 20 x2 + 40 x3 + 30 x4 (木材约束) 4 x1 + 2 x2 + x3 + 2 x4 ≤ 600 6 x 2 x x 2 x 1000 (玻璃约束) 1+ 2+ 3+ 4≤ 2 x1 + 1 x2 + 3 x3 + 2 x4 ≤ 400 (劳动时间约束) (家具1需求量约束) x1 ≤ 100 s.t . (家具2需求量约束) x2 ≤ 200 x3 ≤ 50 (家具3需求量约束) (家具4需求量约束) x4 ≤ 100 x1 , x2 , x3 , x4 ≥ 0 (非负约束)
调用函数“ 调用函数“SUMPRODUCT”
若不指定名称: 若不指定名称: 若指定名称: 若指定名称:
完整的模型描述: 完整的模型描述:
工具”菜单中选择“规划求解” 第二步 在“工具”菜单中选择“规划求解”。
第三步
在“规划求解参数”对话框进行选择如下图。 规划求解参数”对话框进行选择如下图。
运用EXCEL求解线性规划问题 求解线性规划问题 运用
广东商学院 数学与计算科学学院
outline
1.关于“规划求解” 关于“规划求解” 关于 2.如何加载“规划求解” 如何加载“规划求解” 如何加载 3. “规划求解”各参数设置 规划求解” 规划求解 4. “规划求解”步骤 规划求解” 规划求解 5. 利用“规划求解”解线性规划问题 利用“规划求解”
例1. 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具,由于该四种家具具 有不同的大小、形状、重量和风格,所以它们所需 要的主要原料(木材和玻璃)、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时,详细的数据资料见下表。 应如何安排这四种家具的日产量,使得该厂的日利 润最大?
表1 雅致家具厂基本数据 家 具 类 型
劳动时间(小时/件) 木材(单位/件)
1 2 4 6 60 100
2 3 1 2 2 20 200 3 1 1 40 50
4 2 2 2 30 100
可提供两 400小时 600单位 1000单位
玻璃(单位/件) 单位利润(元/件) 最大销售量(件)
解:依题意,设置四种家具的日产量分别为决策变量
1. 关于“规划求解” 关于“规划求解”
Microsoft Excel的“规划求解”工具取自德克 萨斯大学奥斯汀分校的Leon Lasdon和克里 夫兰州立大学的Allan Waren共同开发的 Generalized Reduced Gradient(GRG2)非线性 最优化代码. 线性和整数规划问题取自 Frontline Systems公司的John Waston和Dan Fylstra提供的有界变量单纯形法和分支定界 法
单击“规划求解”按钮,将会出现以下规划求 解参数设置对话框
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可 以加载或保存规划求解模型,并对规划求解过 程的高级属性进行控制
4. “规划求解”步骤 规划求解” 规划求解
⑴ 启用“规划求解”宏; 启用“规划求解” ⑵ 输入数据; 输入数据; ⑶ 利用函数“SUMPRODUCT”引入约束与目标 利用函数“SUMPRODUCT” ⑷ 对话框“规划求解”的各要素. 对话框“规划求解”的各要素.
其中
x1 , x2 , x3 , x4 分别为四种家具的日产量。
下面介绍用Excel中的“规划求解”功能求此题。 中的“规划求解”功能求此题。 下面介绍用 中的 中描述问题、 第一步 在Excel中描述问题、建立模型,如下图所示。 中描述问题 建立模型,如下图所示。
并将同一种类型的数据指定“名称” 并将同一种类型的数据指定“名称”
点击“选项”按钮,弹出“规划求解选项” 第四步 点击“选项”按钮,弹出“规划求解选项”对话框
单击“求解” 即可解决此题。 第五步 单击“求解”,即可解决此题。
பைடு நூலகம்
最后结果如下页图所示。 最后结果如下页图所示。
实验内容: 实验内容:
求解, 分别运用 EXCEL和LINDO 求解 学委 和 在下周上课前把电子版的结果收齐。 在下周上课前把电子版的结果收齐。