EXCEL规划求解案例分析
Excel高级应用：Excel的规划求解功能
Excel的规划求解功能目录•引例•EXCEL中的规划求解工具•线性规划求解方法•对偶问题与影子价格•线性规划的敏感度分析•整数规划求解•非线性规划求解•目标规划问题求解•综合运用引例•生产两种风机（风机A和风机B）。
•生产风机A，需要工时3小时，用电4千瓦，钢材9吨；•生产风机B，需要工时7小时，用电5千瓦，钢材5吨。
•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。
•假设，两种产品的单位利润分别为200万元和210万元。
怎样安排两种产品的生产量，所获得的利润最大？规划求解就是用来解决这类问题的，其实就像是在做应用题，设未知数，然后写函数。
规划求解的第一步也是将所描述的问题数学化，模型化。
接下来按照解题格式来做一下上面的应用题。
引例•生产两种风机（风机A和风机B）。
生产风机A，需要工时3小时，用电4千瓦，钢材9吨；生产风机B，需要工时7小时，用电5千瓦，钢材5吨。
•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。
•假设，两种产品的单位利润分别为200万元和210万元。
怎样安排两种产品的生产量，所获得的利润最大？规划求解的第一步也是将所描述的问题数学化，模型化。
解：设风机A产量为x，风机B产量为y，最大利润为Pmax•x,y>=0•3x+7y<=300•4x+5y<=250•9x+5y<=420•Pmax=200x+200y引例•生产两种风机（风机A和风机B）。
生产风机A，需要工时3小时，用电4千瓦，钢材9吨；生产风机B，需要工时7小时，用电5千瓦，钢材5吨。
•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。
•假设，两种产品的单位利润分别为200万元和210万元。
怎样安排两种产品的生产量，所获得的利润最大？规划求解的第二步也是将数学模型，输入Excel表格，构建关系引例规划求解的第二步也是将数学模型，输入Excel表格，构建关系，并将约束条件输入规划求解参数表引例通过规划求解功能，找到答案引例•1939年，前苏联科学家康托洛维奇总结了他对生产组织的研究，写出了《生产组织与计划中的数学方法》一书，是线性规划应用于工业生产问题的经典著作。
Excel数据管理与图表分析 使用“规划求解”
Excel 数据管理与图表分析 使用“规划求解”在利用规划求解功能寻求最优解时，需要首先根据已知条件建立求解模型。
任何一个规划求解模型都必须包含有决策变量、可行值、约束条件以及目标函数几个要素。
本节将以混合生产为例，向用户介绍如何利用规划求解功能，寻求问题的最优解。
例如，某工厂生产A 、B 两种车刀，生产过程中使用两台机床，一台是车床，另一台是总装。
已知装配一把A 车刀所用的时间为1.5分钟，车削它需要1.25分钟；而装配一把B 车刀所用时间是1.75分钟，车削它需要0.5分钟。
另外，由于机床和工人的限制，车削工时为240分钟，总装工时为480分钟。
如果每把A 车刀的最大利润为7.75元，B 车刀利润为5.25元，又要求B 车刀的最高产量不超过200，那么该工厂该如何安排生产，才能使利润收入为最大？根据上述问题，将已知条件输入Excel 工作表中，创建该问题的求解模型，如图12-3所示。
在该模型中，应用“注释”单元格样式的两个单元格（B6和C6），表示该模型中要求计算的两个变量；而B7单元格则表示该问题的最大利润。
图12-3 建立规划求解模型求解模型建立完成之后，首先需要计算装配时间、车削时间和利润的合计值。
选择D3单元格，在【编辑栏】中输入“=SUMPRODUCT(B6:C6,B3:C3)”公式，并按Enter 键，如图12-4所示。
图12-4 装配时间合计值提 示默认情况下，求解模型中的变量值为0，因此，在计算装配时间的合计值时，得出的结果也为0。
在该公式中，使用了SUMPRODUCT 函数。
该函数的功能是在给定的几组数组中，将数据间对应的元素相乘，并返回乘积之和。
其语法格式为：SUMPRODUCT (array1, array2, array3, ...)其中，参数Array1, array2, array3, ...为2到255个数组，其相应元素需要进行相乘并求输入数据计算结果输入和。
注意使用SUMPRODUCT 函数时，数组参数必须具有相同的维数，否则将返回错误值#VALUE!。
excel规划求解经典案例
excel规划求解经典案例Excel规划求解经典案例。
在日常工作和学习中，我们经常会遇到一些需要使用Excel进行规划求解的经典案例。
Excel作为一款强大的电子表格软件，不仅可以进行数据的录入和整理，还可以进行各种复杂的规划求解操作，帮助我们高效地解决实际问题。
接下来，我们就来看几个经典案例，通过Excel进行规划求解的具体操作。
第一个案例是关于生产排程的问题。
假设某工厂有多个生产任务需要安排在不同的机器上进行加工，每个任务有不同的加工时间和截止日期，我们需要通过Excel进行规划求解，找到最优的生产排程方案。
首先，我们可以将每个任务的加工时间和截止日期录入到Excel表格中，然后利用Excel的求解功能，设置约束条件和目标函数，进行规划求解，得到最优的生产排程方案。
第二个案例是关于运输物流的问题。
假设某物流公司需要将货物从多个仓库运送到多个客户处，每个仓库到客户的运输距离和运输成本都不同，我们需要通过Excel进行规划求解，找到最优的运输路线和运输方案。
在这个案例中，我们可以利用Excel的规划求解工具，输入各个仓库到客户的运输距离和成本数据，设置约束条件和目标函数，进行规划求解，得到最优的运输路线和运输方案。
第三个案例是关于资源分配的问题。
假设某公司有多个项目需要进行资源分配，每个项目需要不同的人力、物力和财力资源，我们需要通过Excel进行规划求解，找到最优的资源分配方案。
在这个案例中，我们可以利用Excel的线性规划功能，输入各个项目所需的资源数据，设置约束条件和目标函数，进行规划求解，得到最优的资源分配方案。
通过以上几个经典案例的介绍，我们可以看到，在实际工作和学习中，Excel的规划求解功能可以帮助我们高效地解决各种实际问题，提高工作效率和决策水平。
因此，熟练掌握Excel的规划求解功能，对于我们提升自身能力和解决实际问题具有重要意义。
希望大家能够在实际工作和学习中，灵活运用Excel的规划求解功能，不断提升自己的规划求解能力。
Excel规划求解简单例子
Excel规划求解简单例子
1、为了保证人们的健康，若干种养分的日供给量不得少于某个最低值，否则就会因营养缺乏而致病，为简单起见，假设需要三种养分A、B、C（例如蛋白质、维生素、微量元素），并假设人们的食谱由两类食物构成，有关数据如下表所示。
在满足营养要求情况下如何进行是的费用最少？
解：首先根据题意，建立线性方程组：
1、根据题意建立数据表。
然后在B3、D5、D6、D7单元格插入sumproduct函数（工具栏—插入—函数，在函数参数界面直接拖选单元格即行）。
B
2、C2分别代表X1、X2，B4、C4分别是目标函数的系数。
用EXCEL规划求解测算土增税增值率临界点价格old
利用EXCEL的规划求解来测算土地增值税增值率临界点的价格由于三明在土地增值税计算过程中有个优惠政策，就是可以按收入来分摊地价和契税款。
所以，当任何一项销售价格变动时，普通住宅、非普通住宅、非住宅的增值率都会变动。
也就是说只有所有销售价格确定时，土增税的增值率才是确定的。
因此，加大了土增税增值率临界点价格测算的难度。
我一开始用VBA写代码来测算，用的是ACCESS数据库，方法是用穷尽法，所有的价格组合，每增加一百元测算一次，效果不是很好，一个是组合太多，要测算最低要几万甚至上亿次，一是运行程序的时间比较长，如果上亿次，可能要一两天的时间，再者ACCESS数据库能否支持上亿条的数据也未可知，三是计算的价格不够精确，虽然比较接近，但还是不够理想。
后面在网上逛的时侯发现EXCEL的规划求解的功能。
研究了一下，觉得还是挺好用的。
拿出来共享：我用的是OFFICE2010版。
一：要有规划求解这个菜单出现，点击文件-选项，见下图：然后点击加载项，然后点击转到，见下图：勾起规划求解加载项，然后点确定，见下图：这样规划求解菜单就出现了，在数据标签的最右边，见下图：二、要有计算土地增值率的表格，就是用任一价格，计算出普通住宅、非普通住宅、非住宅的增值率。
当然要用公式。
然后我们的目的是让EXCEL去不断的改变价格，等到增值率等于我们指定的增值率时停下来，保存价格。
所以我们要把如何算增值率的公式设好。
这个很简单，就是正常的画个表格，输入各项数据，然后用算增值率。
我这里有个例子，是设好的表格，当然，数值是测试数据，随便输入的。
见下图：注意上面这个图，上面的框框是价格，价格变动时，下面的三个圈要能自动变动，得到正确的增值率。
如果能这样，这个表格就算是设好了，就可以用来规划求解了。
这个就不细说了。
三、开始规划求解，假设我们想知道普通住宅增值率为1、非普通住宅增值率为1、非住宅增值率为1时的单价组合是什么，现在来操作：点击规划求解按钮，出现下图：设置目标为普通住宅增值率所在的单元格，然后点击目标值，输入目标增值率，这里设为1.见下图：在同一界面中设置“通过更改可变单元格”，选择要变动的单价所在的单元格，这里单元格的数量是任意的。
应用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 进行规划求解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软件求解规划问题的方法
用Excel 软件求解规划的方法Microsoft Excel 软件是当今十分流行的功能强大操作方便的软件。
在Microsoft Excel 软件中，具有规划求解功能。
如图1，在工具菜单下，一般有“规划求解”项，若未有，则应先运行“加载宏”项目把其安装上。
图1 图21 一般线性规划的求解现在让我们以下面的模型为例，介绍如何利用Microsoft Excel 软件求解线性规划模型的操作方法。
首先，打开Microsoft Excel 的一个工作簿，把模型的约束系数矩阵置于A1至B4范围，约束常数置于D1至D4范围，而利润系数则置于A5至B5范围。
选择A7至B7范围作可变单元（即这两个格相当于变量X1与X2），并输入初值０。
然后，在单元格C1处输入“=A1*A7+B1*B7”，即第一个约束不等式的左边；同理，在单元格C2处输入“=A2*A7+B2*B7”，即第二个约束不等式的左边；对C3与C4也同样处理。
最后，以单元格C5作目标单元格，输入“=A5*A7+B5*B7”。
如图2。
接下来，按下主菜单的工具处，再在下拉菜单处选择“规划求解”，则弹出窗口如图3。
⎪⎪⎪⎩⎪⎪⎪⎨⎧≥≥≤+≤≤≤0x 0,x x x x x x 4+x s.t. x +x =f max 21112121222700050122700075.182700025.56270000155.75.2图3 图4在“设置目标单元格”处输入“C5”,然后选“最大值”，再在“可变单元格”处输入“A7:B7”,在“约束”处按一下“添加”按钮，又弹出如图4的窗口。
在此，我们要添加5个约束：“C1 <= D1”、“C2 <= D2”、“C3 <= D3”、“C4 <= D4”、“A7：B7 >= 0”。
对第一个约束，在“单元格引用位置”处输入“C1”,在中间下拉框选择“<=”, 再在“约束值”处输入“D1”。
然后按“添加”按钮，再类似地添加其它约束。
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求解得对应的敏感性报告灵敏度分析如下表所示
最优解
递减成本指目标函 数中决策变量的系 数必须改进多少才 能得到该决策变量 的正数解;改进对最 大值为增加;对最小
运用EXCEL求解线性规划模型.概要
使用名称比使用字母更容易理解公式的含 义 在“规划求解参数”对话框中使用名称更 容易理解线性规划模型的含义。 增强了公式和模型的可读性
建模求解要点回顾
输入数据 标识数据 每个数据对应唯一单元格 在电子表格中显示完整模型 数据、公式分离 保持简单化 使用区域名称 使用相对和绝对地址简化公式并复制 使用边框、底色区分单元格类型
在电子表格中建立线性规划模型步骤总结
收集问题数据；
在电子表格中输入数据（数据单元格）；
确定决策变量单元格（可变单元格）； 输入约束条件左边的公式（输出单元格）使用 SUMPRODUCT函数简化输入； 输入目标函数公式（目标单元格）。使用SUMPRODUCT 函数简化输入。
EXCEL求解线性规划模型
EXCEL求解线性规划模型
EXCEL求解线性规划模型
课堂练习:
某公司受人委托,准备用120万元投资A和B两中基金,其 中:A基金的单位投资额为50万元,年回报率为10%, B基金 的单位投资额为100万元,年回报率为4%.委托人要求在每 年的年回报金额至少达到6万元的基础上要求投资风险最小. 据测定每单位A基金的投资风险指数为8,每单位B基金的投 资风险指数为3,风险指数越大表明投资风险越大.委托人要 求在基金B中的投资额不少于30万元.为了使总的投资风险 指数最小,该公司应该在基金A和B中各投资多少单位?这时 每年的回报金额是多少?
EXCEL求解线性规划模型
ห้องสมุดไป่ตู้
应用规则：
• 当允许增加量（减少量）为无穷大时，则对于任一
个增加量（减少量），其允许增加（或减少）的百 分比都看成零。
• 百分之一百法则是判断最优解变与不变的充分条件，
实验五_运用Excel规划求解进行最优投资组合的求解
实验报告证券投资学院名称专业班级提交日期评阅人 ____________评阅分数 ____________实验五：运用Excel规划求解进行最优投资组合的求解【实验目的】1、理解资产组合收益率和风险的计算方法.熟练掌握收益率与风险的计算程序；2、进一步理解最优投资组合模型.并据此构建多项资产的最优投资组合；【实验条件】1、个人计算机一台.预装Windows操作系统和浏览器；2、计算机通过局域网形式接入互联网；3、matlab或者Excel软件。
【知识准备】理论知识：课本第三章收益与风险.第四章投资组合模型.第五章 CAPM实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章.第四章.第五章【实验项目内容】请打开参考《金融建模—使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验A．打开“实验五组合优化.xls”.翻到“用规划求解计算最优组合”子数据表；B．调用规划求解功能进行求解。
点击“工具”在下拉菜单点击“规划求解”.如没有此选项说明需要加载规划求解后才能使用.如何加载见实验补充文档“EXCEL规划求解功能的安装”。
C．D．在规划求解选项卡里面选择“选项”.再选择“非负”再运行一次.比较两次返回的投资比例值的正负。
在实验报告中记录两次得到的最优投资组合.并说明投资比例是负值说明什么？E．（选做）借助连续调用规划求解的VBA过程生成有效组合以及资本市场线。
参考实验参考电子书《金融建模—使用EXCEL和VBA》电子书第四章P83F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？【实验项目步骤与结果】A.B．使用规划求解C．投资比例为负值说明该证券风险远远大于其收益率.已经不适合投资。
F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？通过可卖空和不可卖空有效前沿图的对比可以看到.在相同风险的时候可卖空的情况下期望回报要比不可卖空的情况要高.并且随着风险的增加可卖空曲线的期望回报增加程度明显比不可卖空曲线要大。
规划求解究竟有多好用？我用九个案例给你答案「全动图演示」
案例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求解线性规划问题
利用excel求解线性规划问题利用excel 求解线性规划问题“规划求解”示例例1 美佳公司计划制造Ⅰ、Ⅱ两种家电产品。
已知各制造一件时分别占用的设备A ，B 的台时、调试工序时间及每天可用于这两种家电的能力、各售出一件时的获利情况，如下表所示。
问该公司应制造两种家电各多少件，使获取的利润为最大。
1.建立数学模型2. 打开excel ，输入下列数据。
⎪⎪⎩⎪⎪⎨⎧>=<=+<=+<=+=0,52426155..2max 212121221x x x x x x x t s x x z3、如何在工作表中设置问题条件？先设置目标单元格，即最大利润，把它放在E1单元格上，可变单元格放置计划生产Ⅰ和Ⅱ产品的件数，这里把它放在C10：D10区域。
F4：F6是约束单元格，要对它们的值进行约束。
单击E1，在编辑框输入如图所示的公式。
注意，表示绝对引用的美元符号，可以单击F4功能键添加。
4、单击E4单击格式，在编辑栏上输入公式：=$C$4*$C$10+$D$4*$D$10。
绝对引用单元格有一个好处，显示的单元格位置变化时，引用的数据没改变。
5、单击E5单击格式，在编辑栏上输入公式：=$C$5*$C$10+$D$5*$D$10。
6、单击E6单击格式，在编辑栏上输入公式：=$C$6*$C$10+$D$6*$D$10。
7、如何使用规划求解功能？单击工具菜单，如果看不到规划求解选项不要慌，先选加载宏。
然后勾选规划求解，确定单击数据菜单——点击“模拟分析”——8、单击“规划求解”：指定目标单元格。
一种方法是先选中目标单元格E1，单击工具---规划求解。
另一种先单击工具---规划求解，再输入目标单元格名称。
输入可变单元格区域。
比较快的方法是，单击折叠框，用鼠标选中可变单元格区域：$C$11：$E$11。
注意勾选最大值哦。
设置目标： $E$1；点选“最大值”；设置：可变单元: $C$10：$D$109.设置条件不等式。
excel应用线性规划求解
建立規劃求解報表
• 產生報表 • 檢視報表
產生報表
• 請您開啟範例檔案 Ch19-05, 選取 C11 儲 存格並執行『工具/規劃求解』命令, 接著按 下規劃求解參數交談窗中的求解鈕後, 跟著 底下的步驟來建立報表：
產生報表
產生報表
檢視報表
• 若要檢視報表, 只需切換至該工作表中即可。 底下一一檢視剛才產生的 3 種規劃求解報 表：
變更規劃求解條件
• 修改規劃條件 • 修改求解限制式
修改規劃條件
• 假設此時高階主管修訂了部分生產計劃, 決 定投入更多的原料與時間來生產梅子烏龍 茶與無糖綠茶, 那麼這時候我們就要到工作 表中修改數據, 然後重新進行求解了。 • 請開啟範例檔案 Ch19-03：
修改規劃條件
修改規劃條件
• 由於應生產量和總收益是透過規劃求解功 能所計算出來的, 因此當我們更新工作表中 的資料後, 還必須選定目標儲存格 C11, 然 後重新執行『工具/規劃求解』命令, 才能得 到新的求解結果喔！
規劃求解的條件設定
• 最後還有時間配額的限制, 因此請再按 下新增鈕完成如下的設定：
規劃求解的條件設定
• 完成限制式的設定之後, 按下確定鈕, 即可回到規劃求解參數交談窗：
執行求解
• 現在, 請您按下求解鈕, 計算的結果即將呈 現在眼前：
執行求解
執行求解
• 原本感到複雜的生產問題, 經由規劃求解的 運算, 便可得知最佳的生產組合！尚泉公司 只要利用規劃的生產資源, 每個月生產梅子 烏龍茶 62.5 打、生產無糖綠茶 150 打, 就 可獲得最大利潤 25,750 元。
– 運算結果報表 – 敏感度報表 – 極限值報表
運算結果報表
• 運算結果報表會列出目標儲存格及變數儲 存格的初值、終值、及參數限制式的公式 內容：
excel神秘功能——规划求解解决实际规划问题，让决策在运筹帷幄之中
excel神秘功能——规划求解解决实际规划问题，让决策在运筹帷幄之中你好，我是小必，很高兴在这里遇见你。
某大型百货公司2018年的目标是大力推进智能手机、AR/VR、智能家装、智能小家电、智能大家电等五类产品，其中智能手机、智能大家电、AR/VR三种类型的产品的销售额不能低于总销售额的12%，25%与15%，智能家装的销售额不能高于销售额的10%，以于其他新产品的引入。
该大型百货公司有五家综合性商场，每个商场的每个品类科的促销人员需求人数、现有人数、最大人员配置及每个品类科的预期利润如下，该百货公司的五个商场的促销人员总共为300人。
假如你是运营经理,在保持预期利润最大化的情况下如何实现人力资源的优化配置？（在预期情况下每销售10000万的产品利润为以下表中的利润）以上问题的解决可以使用线性求解的方法来实现。
1、设定决策变量通过以上的案例我们可以知道以上五种品类的销量是变量，设10000万销售为一个单位销量，那么每个品类科的销量的单位变量分别为X1 , X2, X3, X4, X5,.2，设定目标函数从案例中知，我们是在预期利润最大化的情况下进行人员的配置，所以目标函数为利润最大化。
设总利润为Z,则目标函数为：3、设定约束条件函数在以上的例子中，每个商场都有都大的人数限制，并且智能手机、智能大家电、AR/VR和智能家装都有销售比例的限制。
根据商场1的最大人数限制，得到约束条件函数为：同理可以得到其他商场的条件约束函数为：根据销售比例的限制可以得到以下的约束条件：同时，X1 , X2, X3, X4, X5应该为非负数，得到约束条件为：综合以上各个条件下的约束条件，我们可以得到以下的约束条件：上述的约束条件（6），（7），（8），（9）是对于约束条件进行简化以后得出的。
4、建模求解根据以上条件的约束，我们在excel中建立如下的表格条件数据：在G3单元格中输入以下公式并向下填充：=SUMPRODUCT($B$17:$F$17,B3:F3)在G17单元格中输入公式：=G3。
利用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)。
规划问题的特点（共性）
一般来讲，规划问题都具有如下特点：
1. 所求问题都有单一的目标（如求生产的最低 成本，求运输的最佳路线，求产品的最大盈 利，求产品周期的最短时间），要求求目标 函数的最优解。 2. 对于问题涉及的对象（如路程、原材料等） 存在有明确的可以用不等式表达约束条件。 3. 问题的表达可以描述为：一组约束条件（不 等式），和一个目标方程。 4. 利用Excel技术可以简单的求得问题满足约束 条件求的目标最优解。
设产品I和II的产量分别为x1和x2，其数学 模型为：
max Z= 6x1 +8x2 5x1 + 10x2 ≤60 st. 4x1 + 4x2 ≤40 x1 , x2 ≥0
其最优解，即最优生产计划为x1＝8件， x2＝2件，max Z＝64元。
例2 假设在前面例子中，计划人员被要求 考虑如下意见： (1) 由于产品 II 销售疲软，故希望产品 II 的 产量不超过产品I 的一半； (2)原材料严重短缺，生产中应避免过量消 耗； (3)最好能节约4h设备工时； (4)计划利润不少于48元。
例1. 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具，由于该四种家具具 有不同的大小、形状、重量和风格，所以它们所需 要的主要原料（木材和玻璃）、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时，详细的数据资料见下表。 应如何安排这四种家具的日产量，使得该厂的日利 润最大？
表1 雅致家具厂基本数据 家 具 类 型
劳动时间（小时/件） 木材（单位/件）
1
2
3
4
可提供量 400小时 600单位 1000单位
2
4 6
1
2 2
3
1 1
2
2 2
玻璃（单位/件）
单位利润（元/件） 最大销售量（件）
60 100
20 200
40 50
30 100
解：依题意，设置四种家具的日产量分别为决策变量
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可 以加载或保存规划求解模型,并对规划求解过 程的高级属性进行控制
4. “规划求解”步骤
⑴ 启用“规划求解”宏；
⑵ 输入数据； ⑶ 利用函数“SUMPRODUCT”引入约束与目标 ⑷ 对话框“规划求解”的各要素.
2.如何加载“规划求解”
1) 在“工具”菜单上,单击“加载宏”
2) 在弹出的对话框中的“可用加载宏”列表框 中,选定待添加的加载宏“规划求解”选项旁 的复选框,然后单击“确定”.单击“确定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
单击“规划求解”按钮,将会出现以下规划求 解参数设置对话框
什么是规划问题?
在计划管理中常会遇到：人力资源的调 1、约束条件的表达 度、产品生产的安排、运输线路的规划、生 产材料的搭配、采购批次的确定等问题。 2、目标的数学描述 这类问题有一个共同点，即需要解决： 3、应用Excel的规划 如何合理利用各种存在约束的资源，而获得 求解工具对问题求解 最佳的经济效益，也就是达到利润最大、成 本最低等目标。这就是本节要解决的“在约 束条件下寻求目标函数最优解的规划问题”。
其中
x1, x2 , x3 , x4 分别为四种家具的日产量。
SUMPRODUCT函数
• SUMPRODUCT的意思是:乘积之和 • 在给定的几组数组中，将数组间对应 的元素相乘，并返回乘积之和。 • 语法 • SUMPRODUCT（array1,array2,array3, ...） • Array1,array2,array3, ... 为 2 到 30 个数 组，其相应元素需要进行相乘并求和。
下面介绍用Excel中的“规划求解”功能求此题。 第一步 在Excel中描述问题、建立模型，如下图所示。
=SUMPRODUCT(B6:E6,$B$15:$E$15)
第二步 在“工具”菜单中选择“规划求解”。
第三步
在“规划求解参数”对话框进行选择如下图。
第四步 点击“选项”按钮，弹出“规划求解选项”对话框
x1, x2 , x3 , x4 ，目标要求是日利润最大化，
约束条件为三种资源的供应量限制和产品销售量限制。 据此，列出下面的线性规划模型：
MaxZ 60 x1 20 x2 40 x3 30 x4 （木材约束） 4 x1 2 x2 x3 2 x4 600 6 x1 2 x2 x3 2 x4 1000 （玻璃约束） 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 （非负约束）
目标规划问题及其数学模型？？？
目标规划问题的提出 例1 某工厂生产两种产品，受到原材料供应和设备工 时的限制。在单件利润等有关数据已知的条件下，要 求制定一个获利最大的生产计划。具体数据如下：
产品 I II 限量
原材料(kg/件)
设备工时(h/件) 利润(元/件)
5
6
10
4 8
60
40
问该公司应制造两种家电各多少件，使获取的利润 为最大。
类似这样的多目标决策问题是典型的 目标规划问题。
运用EXCEL求解线性规划问题
outline
1.关于“规划求解”
2.如何加载“规划求解”
3. “规划求解”各参数设置
4. “规划求解”步骤
5. 敏感性分析
1. 关于“规划求解”
Microsoft Excel的“规划求解”工具取自德克 萨斯大学奥斯汀分校的Leon Lasdon和克里 夫兰州立大学的Allan Waren共同开发的 Generalized Reduced Gradient(GRG2)非线性 最优化代码. 线性和整数规划问题取自 Frontline Systems公司的John Waston和Dan Fylstra提供的有界变量单纯形法和分支定界 法