《规划求解工具》由会员分享，可在线阅读，更多相关《规划求解工具（12页珍藏版）》请在金锄头文库上搜索。
1、EXCEL 竟然能干这个！ 规划求解工具！MrExcel 2016-03-06 18:29:44阅读数：3685你可能听说过著名的“鸡兔同笼”问题：已知有鸡和兔共有 15 只，共有 40 只脚，问鸡和兔各有几只？你可能听说过著名的“鸡兔同笼”问题：已知有鸡和兔共有 15 只，共有 40 只脚，问鸡和兔各有几只？这里有一个有趣的算法：假设鸡和兔都经过特殊培训，我们大喝一声，它们都抬起一只脚，现在挨着地的脚的数量是：40-15=25。我们再大喝一声，它们又都抬起一只脚，现在挨着地的脚的数量是：25-15=10，这时鸡都是两脚朝天地躺地上了，兔子还两只脚立着。所以，兔子有 102=5 只，鸡有15-
2、5=10 只。不过，现实生活中遇到的问题往往要比这个复杂得多，而且绝大多数实际问题都不太可能用这种取巧的方法解答的。幸好我们有 Excel，Excel 中的“规划求解”是解决这类问题的简便易用的工具之一。在默认情况下，我们在 Excel 界面中是找不到规划求解工具的。在 Excel 2010 中，选择“文件选项加载项”命令，在“查看和管理 Microsoft Office 加载项”对话框最下方的“管理”下拉框中选择“Excel 加载项”，然后单击对话框右侧的“转到”按钮，弹出“加载宏”对话框，如下图所示。在弹出的“加载宏”对话框中的“可用加载宏”选项列表中，勾选“规划求解加载项”复选框，然后单
3、击“确定”按钮添加加载项，同时关闭对话框，完成 Excel 规划求解加载项的加载。现在 Excel 规划求解加载项已经加载到 Excel 中了，选择功能区中的“数据”功能标签，你会发现其中多了一个叫做“分析”的功能组。“规划求解”按钮就在那里。Excel 中的规划求解工具能够帮助我们解决很多复杂的问题，下面让我们结合一个有趣的案例来了解Excel 规划求解工具的强大能力。假如我们有 1000 元钱，要买 5 种商品（每种商品至少要买一件或以上），每种商品的价格如下图所示，如果希望正好把 1000 元钱用掉，每种商品应该各买多少件？作为使用 Excel 规划求解工具第一步，也是最重要的一步，我们
4、必须把问题用 Excel 表达出来，变成 Excel 规划求解工具能够理解的模型。如图所示，我们把 C5:C9 单元格命名为“可变单元格”，把 E5 单元格命名为“目标单元格”。在目标单元格中输入公式：“=SUMPRODUCT(B5:B9*C5:C9)”，用来计算当前商品的总价。该公式也可以写成“=SUMPRODUCT(B5:B9,C5:C9)”。建立规划求解模型的关键在于，首先必须使用 Excel 函数和公式建立起“可变单元格”和“目标单元格”之间的联系，也就是所谓的建立规划求解模型。根据实际问题建立起“可变单元格”和“目标单元格”之间的联系后，就可以试着通过改变可变单元格中的每个数值，可以
5、观察目标单元格的计算结果，看一看什么时候能正好凑到 1000 元钱。如果问题比较复杂，人工试探凑数的过程是相当漫长、几乎不能完成的，不过幸好有计算机，幸好有 Excel，幸好 Excel 中有规划求解工具，Excel 中的规划求解工具能够帮助我们自动完成这个任务。单击“数据分析规划求解”命令，弹出“规划求解参数”对话框，如下图所示。这个对话框看起来很复杂，其实我们要做的只有三方面内容：（1）设置目标；（2）设置可变单元格；（3）设置需要遵守的约束条件。下面具体分析每个步骤的具体操作。（1）设置目标参数：即图中的第 1 部分，在这里要告诉 Excel 规划求解工具，那个已经与“可变单元格”建立了
6、联系的“目标单元格”的位置，以及期望目标单元格达到的目标值，目标值可以是求解问题可能实现的最大值、最小值和我们所指定的特定值，在本例中我们使用指定的值 1000。（2）设置可变单元格：即图中的第 2 部分，在这里要告诉 Excel 规划求解工具哪些是驱动目标单元格变化的可变单元格。（3）设置需要遵守的约束条件：即图中的第 3 部分，在很多问题中，可变单元格的变化不是天马行空，不受任何约束的。比如在本案例中，可变单元格必须是整数（商品不能买半个）和每件商品的数量必须大于一件。在该对话框的“遵守约束”中可以为 Excel 规划求解工具指定约束条件。利用 Excel 规划求解工具解决管理实践问题的关
7、键是“用 Excel 公式和函数建立可变单元格和目标单元格之间的关系”，用专业一点的术语来说就是建立可变量和目标量之间 Excel 模型。这样 Excel 就可以利用计算机超快的计算能力，在遵守约束条件的前提下，按照某种算法，在不断改变可变单元格中数值的同时观察比较目标单元格中的数值，直到找到一组符合条件的答案！规划求解的求解方法看起来很笨，但是计算机的运算速度弥补了它的“笨”！其实，在细节上，Excel规划求解也并非傻傻地寻找答案，其中也应用了一些先进算法，不过，具体细节不是我们普通 Excel用户关注的范围，为了便于理解，我们暂且就认为它“傻”吧！了解了 Excel 规划求解工具的基本工作
8、原理之后，现在继续完成我们的规划求解过程。在“规划求解参数”对话框中的“设置目标”文本框处，选取目标单元格 E5，在“目标值”文本框中输入目标值 1000；在“通过更改可变单元格”文本框中，选取我们的可变单元格区域 C5:C9，设置结果如下图所示。现在开始在“遵守约束”中设置我们的约束条件，单击“遵守约束”列表框右侧的“添加”按钮，弹出“添加约束”对话框。在这里我们要告诉规划求解工具在搜索答案时必须遵守的约束条件：每个可变单元格必须大于等于1（每件商品至少买一件），如图所示。然后单击“确定”按钮，回到“规划求解参数”对话框。这时我们发现，“遵守约束”列表框中出现了刚刚添加的约束条件。下面继续添
9、加另外一个约束条件，单击“遵守约束”列表框右侧的“添加”按钮，弹出“添加约束”对话框，如图所示。这一次，我们要在“添加约束”对话框中告诉 Excel 规划求解工具，可变单元格必须是整数，我们做如下图所示设定，这里的“Int”是英文 Integer 的缩写，意思是“整数”。然后单击“确定”按钮回到“规划求解参数”对话框。这时我们发现，“遵守约束”列表框中出现了我们刚刚添加的两个约束条件，如图下所示。单击“规划求解参数”对话框下方的“求解”按钮，开始 Excel 规划求解运算，Excel 弹出“规划求解结果”对话框，显示“规划求解找到一个在误差范围内的整数解。可满足所有约束”，这表示规划求解工具已
10、经找到了一个问题的答案。我们在该消息的下方选择“保留规划求解的解”单选框，然后单击“确定”按钮关闭该对话框，这时找到的一组解，如图所示。从上面的问题的求解过程我们可以感觉到，Excel 规划求解工具相当强大。它能够把复杂的问题简化成 Excel 能够解决的问题，通过建立相对简单的规划求解模型，快速找到问题的答案。现在我们改变问题的要求（即改变问题的约束条件），看一看 Excel 规划求解工具有没有能力应付。新的问题是：每种东西必须买 3 件以上，怎么才能正好把 1000 元钱一分不剩正好花掉呢？很简单，我们只需把问题的第二个约束条件变成“可变单元格=3”，求解即可，如图所示。下面是我们得到的改
11、变了约束条件的新答案，选择“保留规划求解的解”单选框，然后单击“确定”按钮把规划求解的答案保存起来，如图所示。直觉告诉我们，对于这个花钱的问题，可能有多种答案，但遗憾的是，Excel 规划求解工具每次只能找到问题的一组解决方案，如果我们尝试找出另外一组答案，可以通过修改 Excel 规划求解工具的约束条件，排除已经找到的那一个答案的方式来解决。下面回顾一下规划求解的整个工作过程。（1）建立 Excel 规划求解模型：把实际问题用 Excel 表达出来，确定可变单元格、目标单元格和约束条件所对应的单元格区域。（2）用 Excel 公式和函数建立可变单元格和目标单元格之间的联系。（3）设置规划求解工具对话框：即设置目标单元格、可变单元格和遵守的约束。我们可以看到，Excel 规划求解工具把复杂的问题的求解过程变得相当简单。使用 Excel 规划求解工具的核心是建立实际问题的 Excel 规划求解模型，即建立可变单元格和目标单元格之间的联系。如果模型建好了，问题就解决了一大半。关于 Excel 规划求解工具的其他设置，这里不再介绍。读者有兴趣的话，可上网搜索相关帮助。Excel 规划求解虽然有很多局限，但是其“易获得性”和“易用性”使得我们能够随时用它解决一些管理问题，只要告诉 Excel 规划求解工具解决问题的思路，把复杂的求解过程交给 Excel 规划求解工具处理就可以了！
