Excel2007《规划求解》.docVIP

Excel2007 “规划求解” 你的工资是每月3000元，工资系数（即工资增加率）是0.12，那么你的工资应该是多少？ 在 HYPERLINK /bkhtml/c118/ \o Excel教程 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,灰色的那个单元格，没有公式，是一个普通的数值。在求解过程中， HYPERLINK /bkhtml/c118/ \o Excel教程 Excel会不断改变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 通过求解，求出了每种存货的最优批量，并自动计算出最优订货批量下的总成本、每年最佳订货次数和最佳订货周期等，同时，丰规划求解结果中，还提供了敏感性分析报告