EXCEL规划求解案例分析

利用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（规划求解）的⽤法及例⼦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规划求解简单例子
1、为了保证人们的健康，若干种养分的日供给量不得少于某个最低值，否则就会因营养缺乏而致病，为简单起见，假设需要三种养分A、B、C（例如蛋白质、维生素、微量元素），并假设人们的食谱由两类食物构成，有关数据如下表所示。

在满足营养要求情况下如何进行是的费用最少？
解：首先根据题意，建立线性方程组：
1、根据题意建立数据表。

然后在B3、D5、D6、D7单元格插入sumproduct函数（工具栏—插入—函数，在函数参数界面直接拖选单元格即行）。

B
2、C2分别代表X1、X2，B4、C4分别是目标函数的系数。

应用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这个工具的话，就能轻松的进行求解。

下面，我就通过一个工厂生产利润最大化的例子来给小伙伴们讲解下具体的使用方法。

题目：某家具生产厂可以生产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 就可以给出求解结果。

对于比较复杂的问题，那就需要用到较难掌握的数学软件如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 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。

具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。

产品组合通常必须满足以下约束：● 产品组合使用的资源不能超标。

● 对每种产品的需求都是有限的。

我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。

下面，我们来考虑让某医药公司的最优产品组合问题。

该公司有六种可以生产的药品，相关数据如下表所示。

设该公司生产药品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线性规划求解实现资源价值优化线性规划（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求解点击求解之后，我们就完成了一次小型的规划求解了。

下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。

例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表示的列向量的内积。

这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。