首页 Office&WPS正文

EXCEL数据分析工具的应用(2)

mydiannao Office&WPS 09-12 3243 0

1)首先建立优化模型,(设xy分别表示甲产品和乙产品的生产量):

目标函数:max{销售利润}= (14060)×x + (180100)×y

约束条件:6x + 9y 360

          7x + 4y 240

          18x + 15y 850

          y 30

          x 0,  y 0,且为整数

2)单元格B11C11为可变单元格,分别存放甲、乙产品的生产量。

3)单元格B12为目标单元格(销售利润),计算公式为“=SUMPRODUCT(B4:C4-B5:C5,B11:C11)”;

4)在单元格B14中输入产品消耗工时合计计算公式“=SUMPRODUCT(B6:C6,B11:C11)”。在单元格B15中输入产品消耗材料合计计算公式“=SUMPRODUCT(B7:C7,B11:C11)”,在单元格B16中输入产品消耗能源合计计算公式“=SUMPRODUCT(B8:C8,B11:C11)”。

5)单击【工具】菜单,选择【规划求解】项,则系统弹出【规划求解参数】对话框,如图2-67

2-67  【规划求解参数】对话框

6)在【规划求解参数】对话框中,【设置目标单元格】中输入“$B$12”;【等于】选“最大值”;【可变单元格】中输入“$B$11:$C$11”;在【约束】中添加以下的约束条件:“$B$11:$C$11=整数”、“$B$11:$C$11>=0”、“$B$14<=$E$3”、“$B$15<=$E$4”、“$B$16<=$E$5”、“$B$11<=$C$9”;

这里,添加约束条件的方法是:单击【添加】按钮,系统会弹出【添加约束】对话框,如图2-68所示,输入完毕一个约束条件后,单击【添加】按钮,则又弹出空白的【添加约束】对话框,再输入第二个约束条件。当所有约束条件都输入完毕后,单击【确定】按钮,则系统返回到【规划求解参数】对话框。

2-68  【添加约束】对话框

如果发现输入的约束条件有错误,还可以对其进行修改,方法是:选中要修改的约束条件,单击【更改】按钮,则系统弹出【改变约束】对话框,如图2-69所示,再进行修改即可。

2-69  【改变约束】对话框

输入完毕约束条件后,若还需要添加约束条件,单击【添加】按钮,在弹出的【添加约束】对话框中输入约束条件即可。

7)如果需要,还可以设置有关的项目,即单击【选项】按钮,弹出【规划求解选项】对话框,如图2-70所示,对其中的有关项目进行设置即可;

2-70  【规划求解选项】对话框

8)在建立好所有的规划求解参数后,单击【求解】,则系统将显示如图2-71所示的【规划求解结果】对话框,选择【保存规划求解结果】项,单击【确定】,则求解结果显示在工作表上,如图2-66所示。

2-71  【规划求解结果】对话框

9)如果需要,还可以单击【规划求解结果】对话框中的【保存方案】,以便于对运算结果做进一步的分析。

2.4.3.2  求解方程组

利用规划求解工具还可以求解线性或非线性方程组,下面举例说明:

【例2-16有如下的非线性方程组:

则利用规划求解工具求解方程组的解步骤如下:

1)设计工作表格,如图2-72所示;

2-72  利用规划求解工具求解方程组

2)单元格E2E4为变动单元格,存放方程组的解,其初值可设为零(空单元格);

3)在单元格B2中输入求和公式“=3*E2^2+2*E3^2-2*E4-8”;在单元格B3中输入求和公式“=E2^2+(E2+1)*E3-3*E2+E4^2-5”;在单元格B4中输入求和公式“=E2*E4^2+3*E2+4*E3*E4-10”;

4)可以任意选取一个方程的求和作为目标函数,而其它两个方程的求和作为约束条件,这里选取方程1的求和作为目标函数,方程2和方程3的求和作为约束条件,故在单元格C2中输入目标函数公式“=B2”;

5)在【规划求解参数】对话框中,【设置目标单元格】设置为单元格“$C$2”;【等于】设置为“值为0”;【可变单元格】设置为“$E$2:$E$4”;【约束】中添加“$B$3=0”、“$B$4=0”。如有必要,还可以对“选项”的有关参数进行设置,如“迭代次数”、“精度”等,这里精度设置为10-11

7)单击【求解】,即可得到方程组的解,如图2-72所示。

利用规划求解工具还可以求解一元方程的解,此时仅有一个可变单元格,方法同上。

本站公告

【企业、公司、工作室、出租屋、家庭】
远程解决:网络异常,路由器配置,打印机设置,驱动安装,局域网文件共享,监控调试,电脑故障等电脑周边问题解决,欢迎咨询!
微信:1633694989