Excel2007之---规划求解

yanjie

先安装好“规划求解”加载宏，怎么安装“规划求解”可以参见此贴：http://club.excelhome.net/viewthread.php?tid=326958&px=0

学习“规划求解”，基本理论知识请参考帮助文件。我认为最好是有一个好的例子，理解透，是学习的最好方法。

下面是一个很简单的例子：

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

在excel中，这是很简单的。表示如下图：

9MaYXaqz.rar (6.17 KB, 下载次数: 755)

2008-6-5 22:12 上传

点击文件名下载附件
Excel2007之---规划求解

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

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

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

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

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

[此贴子已经被作者于2008-6-5 23:08:54编辑过]

kenbeyond

你咋知道是牛顿逼近？
你上面的例子很极端啊，没有什么约束，需要用规划求解么？不就是一元一次方程么？
实际的例子就是因为约束条件比较复杂，很多约束不是简单的等于，往往是大于，小于，所以不容易计算，

一般线性规划是用单纯形法，用计算机来解决的话，其实是矩阵运算，不是微积分
感兴趣的话，可以找本运筹学的书看看，开篇就是线性规划

[此贴子已经被作者于2008-6-5 23:02:07编辑过]

yanjie

你咋知道是牛顿逼近？

单击对话框的选项就知道了，如图：

就是采用一个极其简单的例子，尽力破解它，分析它，我认为是最好的学习方法。下面的例子都极其简单。

[此贴子已经被作者于2008-6-5 23:05:52编辑过]

yanjie

下面是一个简单的储蓄问题：

假如你在7年后可能需要30万元买房子，如果现在就存入一笔钱，按一年期整存整取利息4.14％，那么你现在需要存入多少钱？

首先用excel表示出   本金，利率，年份，本息合计，

所求为“本金”，对于它通常假设为一个任意可能的数值，例如1000，按我的习惯，用灰色单元格表示。它是一个变量。在规划求解对话框中，它就是“可变单元格”。

利率，按国家现在规定的4.14％。它是一个常量。

年份，由已知可以得到，它也是一个常量。

本息合计，即假设1000元以一年期存7年，本金和利息合计是多少？

回想起中学时代，这样一个“复利”的计算过程是很复杂的，它表示为1000*（1+4.14％）^7,求解过程非常复杂，现在有了excel的规划求解，一切变得多么简单！

从中看出，规划求解是一种反方向的求解，即从公式的结果反推它的变量是什么，变量往往是问题所要求解的未知数。

传上文件： 6WtZR2Vq.rar (134.26 KB, 下载次数: 339)

2008-6-9 21:43 上传

点击文件名下载附件

[此贴子已经被作者于2008-6-9 21:52:48编辑过]

yanjie

可以使用“规划求解”来解方程！

一个方程就是用符号来说明两个数学关系---左手边（LHS)和右手边(RHS)之间是相等的，即它的外显形式：

LHS=RHS

方程的内含形式为：LHS-RHS=0

就规划求解来说，内含形式常常更有用。LHS和RHS都是由若干项组成，已知项既可以是数字，也可以是代表方程参数的符号。未知项是方程的变量。从根本上来说，解方程的过程需要确定使方程的LHS和RHS相等的变量值为多少，或者说是使LHS和RHS之差等于零的变量数值。

理解这些理论对规划求解方程至关重要，下面举例说明：

我们分析方程   2x＝6  是它的外显形式，那么它的内显形式是2x-6＝0，我们可以用纸和笔得出x＝3。

那么excel的解法是怎么样的呢？先在excel中输入各种参数和数据，如图：

excel从x的初值1出发，因为它的精度为0.000001，所以第一个尝试的值为1.000001，那么2x-6＝3.999998

结果不满足要求为零，于是excel又开始尝试第2个值1.000002，那么2x-6＝3.999996，如此尝试下去......，直到x=3,则2x-6＝0，恰好满足要求！

excel把从中观察x选择值影响的单元格叫做目标单元格，此例为黄色的单元格B4,把正在寻找的影响结果叫做目标单元格数值（此例为零），最后，我们把正在改变以便实现目标的单元格叫做可变单元格。如图，用红色圈圈住的是这3个地方：

[此贴子已经被作者于2008-6-11 22:20:16编辑过]

yanjie

用规划求解解更复杂的方程

现在考虑方程2^x＝8. 因为2³＝8,所以显然x＝3，现在分析这个模型，以此获得宝贵的解题经验！

第一步   在A1单元格输入“x的数值”，通常我们把自变量x（即一般是要求解的未知数，即规划求解中称为的可变单元格）列出来。就是列出未知数。

因为这是一个等式，前面我们已经讲过等式的左边表示为LHS，右边表示为RHS，其中LHS＝RHS是它的外显表示形式，而LHS-RHS＝0是它的内含表现形式，它的内含表现形式往往更有用，特别是在规划求解中。所以，

第二步   就是列出这个方程的左边表达式LHS。

顺着这个思路来，

第三步   就是列出这个方程的右边表达式RHS。

我们知道，规划求解中的“目标单元格”就是那个包含公式的单元格，公式中必然引用了可变单元格。前面列出了规划求解的可变单元格是A1，那么就应该确定“规划求解”的目标单元格。所以，

第四步   就是确定规划求解的目标单元格（包含公式的单元格）LHS-RHS，即那一个黄色的单元格。

在本例中即B4单元格，公式为＝B2-B3

然后用鼠标选中B4单元格，执行  数据---分析---规划求解，如图：

正如我们前面讲的，规划求解为我们解方程，必须要向它提供3条信息：

目标单元格为B4,

目标单元格（必要的）值----在此例中为0。

可变单元格为B1。

我们一一列出了它们！

假如我们需要解方程2^x ＝10，只要把B3单元格的数值改为10，反映新的RHS数据，再启用规划求解即可。

这里我们可以看出列出RHS放置方程的右边等式的好处，而不必去修改“目标单元格”B4中的公式，因为有些公式是复杂的，而且容易错。在这里我们只需要改变B3单元格的值就行了。请体会这个细微的地方。

[此贴子已经被作者于2008-6-18 21:11:21编辑过]

yanjie

下面是一个稍微复杂的方程，请充分运用前面讲解的技术，你将把解方程视为一种特别轻松愉快的事情。题目如下：

2^x +x³ ＝16

如果用笔和纸来计算，不会是很简单的。如果你掌握了Excel技术的工作原理，并且理解了前面我讲解的，那么就变得非常轻松愉快了！如图：

HSVQGFCC.rar (263.26 KB, 下载次数: 259)

2008-6-18 21:28 上传

点击文件名下载附件
Excel2007之---规划求解

asmartgirl

又长见识了！

版主辛苦了！致敬~~~

everee

没“约束”时，跟“单变量求解”几乎一样。

naomiwa

有约束条件的怎么做呀
例如: 求解

目标函数 max Z = 130X1 + 60X2 + 50X3
30X1 + 15X2 + 10X3 ≤ 400
St.     9X1 +  3X2 + 4X3 ≤ 100
X1            ≤ 5
X1,X2,X3 ≥ 0