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编程。
- 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 求解 学委 和 在下周上课前把电子版的结果收齐。 在下周上课前把电子版的结果收齐。