Excel2007 “规划求解”

Excel2007 “规划求解”

你的工资是每月3000元，工资系数（即工资增加率）是0.12，那么你的工资应该是多少？

在Excel2007中，这是很简单的。表示如图1：

图1

图1中只有B3:D4共6个单元格有数据，另外没有数据。其中只有D4单元格（黄色的那个单元格）有公式，在编辑栏可以看到这个公式。结果为3360元。

假设新工资要为3660元（即D4单元格为3360元），基础工资3000元不变，那么系数C4单元格为多少？

执行数据---分析---规划求解，如图2：

图2

可以看到，没有约束条件，单击“求解”即可以得出答案。如图3：

图3

可以看出，D4单元格（黄色），是目标单元格，是包含公式的。

C4单元格（灰色），是可变单元格，是一个数值，没有公式。求解过程：

规划求解就是给C4单元格一个值0.12，看D4单元格等于3360，把它和结果3660比较，计算机发现它不是答案，返回给C4赋予一个新值0.121，结果D4单元格为3363，不对，计算机又给C4赋予另一值，这样不断尝试，直到得到符合计算机要求的答案出现，才停止尝试。其实这个过程对人来说是很漫长和枯燥的，但是对计算机是非常快速和高效的，一下就完成了。这是规划求解采用的基本计算方法，它就是“牛顿逼近法”。

可以看出这个过程实际是一个公式，从它的结果---y，去反方向推测它的变量---x为多少的过程。

这个例子告诉我们，使用“规划求解”必须首先确定“目标单元格”。即对话框中第1个项，如图4：

图4

即黄色的那个单元格，它里面一定要包含公式。把它设置为黄色，这是我的习惯，可以让自己清楚知道“包含公式的单元格”，并且看一眼就能发现。你可以根据自己的习惯，设置一种自己喜欢的颜色填充。

其次，确定“可变单元格”，如图5，用红线圈住的部分：

图5

可以看出，可变单元格C4,灰色的那个单元格，没有公式，是一个普通的数值。在求解过程中，Ex cel会不断改变C4单元格的值，直到满足要求为止。我用灰色表示，是我的习惯，关键是让自己能一眼发现它。excel2007要求可变单元格最多为200个。

再看看D4单元格的公式为=B4*(1+C4),能从这个公式里面发现C4单元格（一定能发现可变单元格），即灰色的那个单元格。如果你把公式中C4单元格用数值0.12代替它，再运行规划求解，你会发现，无法得出答案。

但是如果你把公式中B4单元格用3000去替换，一样能采用”规划求解“得出正确答案。

可以得到这样一个结论：目标单元格（黄色的）和可变单元格（灰色的）是通过目标单元格的公式联系在一起的。就像函数y=f(x)一样，目标单元格就是y,可变单元格就是x，而＝f（x）就是公式。

你懂了“目标单元格”和“可变单元格”，那么“规划求解”就已经知道了大部分了。我认为这是“规划求解”最重要和基础的东西

实验案例：用Excel建立最优订货批量模型.

1.基本数据。假设某企业有四种存货需要采购,供应商也规定了各种存货的数量折扣,各种存货的基本数据如图1所示。

图1

2.最优订货批量求解分析区域的公式定义。在计算分析区域分别定义采购成本、储存成本、订货成本、总成本、综合成本、最佳订货次数、最佳订货周期和经济订货量占用资金的公式。定义方法是先定义B列的公式，然后复制到其他单元格，如图2所示。

图2

3.约束条件。供应商提供的条件是：甲、乙、丙、丁的订货批量分别为不小于400、350、500、和300。

根据以上条件，我们可以利用Excel提供的规划求解工具计算各种存货的最优批量。操作如下：

1.选择[工具]/<规划求解>命令，弹出规划求解参数对话框，如图3。（如果在[工具]菜单下没有“规划求解”命令，可以执行[工具]菜单下<加载宏>命令，从弹出的对话框中选择“规划求解”后即可。）

2.设定规划求解参数。如图3所示。

图3

3.求解。当目标单元格、可变单元格、约束条件不变时，无论基础数据如何改变，都不需要修改上述设置，直接进行求解。单击<求解>按钮，即可得出各种存货的最优批量，如图4所示。

图4

通过求解，求出了每种存货的最优批量，并自动计算出最优订货批量下的总成本、每年最佳订货次数和最佳订货周期等，同时，丰规划求解结果中，还提供了敏感性分析报告、运算结果报告和限制区域报告等，供企业了解经济订货批量求解的过程和结果。

在模型中，总成本与各要素之间建立了动态链接。当企业财务政策发生变化，如存货年需要量改变，或经济条件发生变化，如每次订货变动成本、单位储存成本或单价等发生变化，我们只需改变基本数据区的各项数据，使用规划求解功能，最优订货批量模型即可迅速计算出相应的结果。这跟高数中多元函数条件极值的问题相似。

第十节回归与规划求解

回归分析在试验设计数据处理有非常重要的作用，例如正交设计、均匀设计、配方设计、复合设计都需要通过回归分析来寻找因素与响应变量间的关系，而EXCEL的数据分析工具库中就提供了回归分析的工具。通过回归分析，一般会得到因素与响应变量间的拟合方程，那么怎样根据拟合方程来确定最优试验条件呢？这时就可以使用数据分析

工具库中的规划求解工具。

回归分析

在进行回归分析前，须将试验结果数据按规定的格式输入EXCEL表格中。现假设为了解某指标Y与因素X1、X2之间的影响关系，进行了10次试验，试验数据结果输入EXCEL

表中如图10-1。

图 10-1

试验数据输入完成后，在数据分析工具库中选择回归，出现如图10-2的对话框，

对话框内容如下：

图 10-2

1． Y值输入区域：选择响应变量试验结果数据所在的区域，可以包含标志。

2． X值输入区域：选择因素取值数据所在的区域，可以包含标志。.

3．如果选择数据时包含了标志则选择标志复选框。

4．如果强制拟合线通过坐标系原点则选择常数为零复选框。

5．置信度：分析置信度，一般选择95%。

6．输出选项：根据需要选择分析结果输出的位置。

7．残差选项：根据需要可选择分析结果中包含残差和标准残差以及残差图与线形

拟合图。

8．如果希望输出正态概率图则选择响应得复选框。

为了分析图10-1中的数据，按图10-2的内容输入对话框中，分析的数据结果如图10-3、10-4，图形结果如图10-5、10-6所示。