规划求解工具.docVIP

EXCEL竟然能干这个！ 规划求解工具！ MrExcel??2016-03-06 18:29:44 阅读数：3685 你可能听说过著名的“鸡兔同笼”问题：已知有鸡和兔共有15只，共有40只脚，问鸡和兔各有几只？ 你可能听说过著名的“鸡兔同笼”问题：已知有鸡和兔共有15只，共有40只脚，问鸡和兔各有几只？ 这里有一个有趣的算法：假设鸡和兔都经过特殊培训，我们大喝一声，它们都抬起一只脚，现在挨着地的脚的数量是：40-15=25。我们再大喝一声，它们又都抬起一只脚，现在挨着地的脚的数量是：25-15=10，这时鸡都是两脚朝天地躺地上了，兔子还两只脚立着。所以，兔子有10÷2=5只，鸡有15-5=10只。 不过，现实生活中遇到的问题往往要比这个复杂得多，而且绝大多数实际问题都不太可能用这种取巧的方法解答的。幸好我们有Excel，Excel中的“规划求解”是解决这类问题的简便易用的工具之一。 在默认情况下，我们在Excel界面中是找不到规划求解工具的。在Excel 2010中，选择“文件→选项→加载项”命令，在“查看和管理Microsoft Office加载项”对话框最下方的“管理”下拉框中选择“Excel加载项”，然后单击对话框右侧的“转到”按钮，弹出“加载宏”对话框，如下图所示。 在弹出的“加载宏”对话框中的“可用加载宏”选项列表中，勾选“规划求解加载项”复选框，然后单击“确定”按钮添加加载项，同时关闭对话框，完成Excel规划求解加载项的加载。 现在Excel规划求解加载项已经加载到Excel中了，选择功能区中的“数据”功能标签，你会发现其中多了一个叫做“分析”的功能组。“规划求解”按钮就在那里。 Excel中的规划求解工具能够帮助我们解决很多复杂的问题，下面让我们结合一个有趣的案例来了解Excel规划求解工具的强大能力。 假如我们有1000元钱，要买5种商品（每种商品至少要买一件或以上），每种商品的价格如下图所示，如果希望正好把1000元钱用掉，每种商品应该各买多少件？ 作为使用Excel规划求解工具第一步，也是最重要的一步，我们必须把问题用Excel表达出来，变成Excel规划求解工具能够理解的模型。 如图所示，我们把C5:C9单元格命名为“可变单元格”，把E5单元格命名为“目标单元格”。在目标单元格中输入公式：“=SUMPRODUCT(B5:B9*C5:C9)”，用来计算当前商品的总价。该公式也可以写成“=SUMPRODUCT(B5:B9,C5:C9)”。 建立规划求解模型的关键在于，首先必须使用Excel函数和公式建立起“可变单元格”和“目标单元格”之间的联系，也就是所谓的建立规划求解模型。根据实际问题建立起“可变单元格”和“目标单元格”之间的联系后，就可以试着通过改变可变单元格中的每个数值，可以观察目标单元格的计算结果，看一看什么时候能正好凑到1000元钱。 如果问题比较复杂，人工试探凑数的过程是相当漫长、几乎不能完成的，不过幸好有计算机，幸好有Excel，幸好Excel中有规划求解工具，Excel中的规划求解工具能够帮助我们自动完成这个任务。 单击“数据→分析→规划求解”命令，弹出“规划求解参数”对话框，如下图所示。 这个对话框看起来很复杂，其实我们要做的只有三方面内容： （1）设置目标； （2）设置可变单元格； （3）设置需要遵守的约束条件。 下面具体分析每个步骤的具体操作。 （1）设置目标参数：即图中的第1部分，在这里要告诉Excel规划求解工具，那个已经与“可变单元格”建立了联系的“目标单元格”的位置，以及期望目标单元格达到的目标值，目标值可以是求解问题可能实现的最大值、最小值和我们所指定的特定值，在本例中我们使用指定的值1000。 （2）设置可变单元格：即图中的第2部分，在这里要告诉Excel规划求解工具哪些是驱动目标单元格变化的可变单元格。 （3）设置需要遵守的约束条件：即图中的第3部分，在很多问题中，可变单元格的变化不是天马行空，不受任何约束的。比如在本案例中，可变单元格必须是整数（商品不能买半个）和每件商品的数量必须大于一件。在该对话框的“遵守约束”中可以为Excel规划求解工具指定约束条件。 利用Excel规划求解工具解决管理实践问题的关键是“用Excel公式和函数建立可变单元格和目标单元格之间的关系”，用专业一点的术语来说就是建立可变量和目标量之间Excel模型。这样Excel就可以利用计算机超快的计算能力，在遵守约束条件的前提下，按照某种算法，在不断改变可变单元格中数值的同时观察比较目标单元格中的数值，直到找到一组符合条件的答案！ 规划求解的求解方法看起来很笨，但是计算机的运算速度弥补了它的“笨”！其实，在细节上，Excel规划求解也并非傻傻地寻找答案，其中也应用了一些先进算法，不过