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

mydiannao Office&WPS 09-12 3248 0

（1）首先建立优化模型，（设x和y分别表示甲产品和乙产品的生产量）：

目标函数：max{销售利润}= (140－60)×x + (180－100)×y

约束条件：6x + 9y ≤ 360

7x + 4y ≤ 240

18x + 15y ≤ 850

y ≤ 30

x ≥ 0, y ≥ 0，且为整数

（2）单元格B11和C11为可变单元格，分别存放甲、乙产品的生产量。

（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）单元格E2：E4为变动单元格，存放方程组的解，其初值可设为零（空单元格）；

（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。