excel2007 “规划求解”

《excel2007 “规划求解”》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、Excel2007“规划求解”你的工资是每月3000元，工资系数（即工资增加率）是0.12，那么你的工资应该是多少？在Excel2007中，这是很简单的。表示如图1：图1图1中只有B3:D4共6个单元格有数据，另外没有数据。其中只有D4单元格（黄色的那个单元格）有公式，在编辑栏可以看到这个公式。结果为3360元。假设新工资要为3660元（即D4单元格为3360元），基础工资3000元不变，那么系数C4单元格为多少？执行 数据---分析---规划求解，如图2：图2可以看到，没有约束条件，单击“求解”即可以得出答案。如图3：图3可以看出，D4单元格（黄色），是目标单元格，是包含公式的。C4单元

2、格（灰色），是可变单元格，是一个数值，没有公式。求解过程：规划求解就是给C4单元格一个值0.12，看D4单元格等于3360，把它和结果3660比较，计算机发现它不是答案，返回给C4赋予一个新值0.121，结果D4单元格为3363，不对，计算机又给C4赋予另一值，这样不断尝试，直到得到符合计算机要求的答案出现，才停止尝试。其实这个过程对人来说是很漫长和枯燥的，但是对计算机是非常快速和高效的，一下就完成了。这是规划求解采用的基本计算方法，它就是“牛顿逼近法”。可以看出这个过程实际是一个公式，从它的结果---y，去反方向推测它的变量---x为多少的过程。这个例子告诉我们，使用“规划求解”必须首先

3、确定“目标单元格”。即对话框中第1个项，如图4：图4即黄色的那个单元格，它里面一定要包含公式。把它设置为黄色，这是我的习惯，可以让自己清楚知道“包含公式的单元格”，并且看一眼就能发现。你可以根据自己的习惯，设置一种自己喜欢的颜色填充。其次，确定“可变单元格”，如图5，用红线圈住的部分：图5可以看出，可变单元格C4,灰色的那个单元格，没有公式，是一个普通的数值。在求解过程中，Excel会不断改变C4单元格的值，直到满足要求为止。我用灰色表示，是我的习惯，关键是让自己能一眼发现它。excel2007要求可变单元格最多为200个。再看看D4单元格的公式为=B4*(1+C4),能从这个公式里面发现

4、C4单元格（一定能发现可变单元格），即灰色的那个单元格。如果你把公式中C4单元格用数值0.12代替它，再运行规划求解，你会发现，无法得出答案。但是如果你把公式中B4单元格用3000去替换，一样能采用”规划求解“得出正确答案。可以得到这样一个结论：目标单元格（黄色的）和可变单元格（灰色的）是通过目标单元格的公式联系在一起的。就像函数y=f(x)一样，目标单元格就是y,可变单元格就是x，而＝f（x）就是公式。你懂了“目标单元格”和“可变单元格”，那么“规划求解”就已经知道了大部分了。我认为这是“规划求解”最重要和基础的东西实验案例：用Excel建立最优订货批量模型.1.基本数据。假设某企业有四

5、种存货需要采购,供应商也规定了各种存货的数量折扣,各种存货的基本数据如图1所示。                                            图12.最优订货批量求解分析区域的公式定义。在计算分析区域分别定义采购成本、储存成本、订货成本、总成本、综合成本、最佳订货次数、最佳订货周期和经济订货量占用资金的公式。定义方法是先定义B列的公式，然后复制到其他单元格，如图2所示。                                                              图23.约束条件。供应商提供的条件是：甲、乙、丙、丁的订货批量分别为不小于

6、400、350、500、和300。根据以上条件，我们可以利用Excel提供的规划求解工具计算各种存货的最优批量。操作如下：1.选择[工具]/<规划求解>命令，弹出规划求解参数对话框，如图3。（如果在[工具]菜单下没有“规划求解”命令，可以执行[工具]菜单下<加载宏>命令，从弹出的对话框中选择“规划求解”后即可。）2.设定规划求解参数。如图3所示。                图33.求解。当目标单元格、可变单元格、约束条件不变时，无论基础数据如何改变，都不需要修改上述设置，直接进行求解。单击<求解>按钮，即可得出各种存货的最优批量，如图4所示。                      

7、图4通过求解，求出了每种存货的最优批量，并自动计算出最优订货批量下的总成本、每年最佳订货次数和最佳订货周期等，同时，丰规划求解结果中，还提供了敏感性分析报告、运算结果报告和限制区域报告等，供企业了解经济订货批量求解的过程和结果。在模型中，总成本与各要素之间建立了动态链接。当企业财务政策发生变化，如存货年需要量改变，或经济条件发生变化，如每次订货变动成本、单位储存成本或单价等发生变化，我们只需改变基本数据区的各项数据，使用