|
你可能听说过著名的“鸡兔同笼”问题：已知有鸡和兔共有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规划求解也并非傻傻地寻找答案，其中也应用了一些先进算法，不过，具体细节不是我们普通Excel用户关注的范围，为了便于理解，我们暂且就认为它“傻”吧！ 了解了Excel规划求解工具的基本工作原理之后，现在继续完成我们的规划求解过程。 在“规划求解参数”对话框中的“设置目标”文本框处，选取目标单元格E5，在“目标值”文本框中输入目标值1000；在“通过更改可变单元格”文本框中，选取我们的可变单元格区域C5:C9，设置结果如下图所示。 现在开始在“遵守约束”中设置我们的约束条件，单击“遵守约束”列表框右侧的“添加”按钮，弹出“添加约束”对话框。
在这里我们要告诉规划求解工具在搜索答案时必须遵守的约束条件：每个可变单元格必须大于等于1（每件商品至少买一件），如图所示。然后单击“确定”按钮，回到“规划求解参数”对话框。
这时我们发现，“遵守约束”列表框中出现了刚刚添加的约束条件。下面继续添加另外一个约束条件，单击“遵守约束”列表框右侧的“添加”按钮，弹出“添加约束”对话框，如图所示。
这一次，我们要在“添加约束”对话框中告诉Excel规划求解工具，可变单元格必须是整数，我们做如下图所示设定，这里的“Int”是英文Integer的缩写，意思是“整数”。然后单击“确定”按钮回到“规划求解参数”对话框。
这时我们发现，“遵守约束”列表框中出现了我们刚刚添加的两个约束条件，如图下所示。 单击“规划求解参数”对话框下方的“求解”按钮，开始Excel规划求解运算，Excel弹出“规划求解结果”对话框，显示“规划求解找到一个在误差范围内的整数解。可满足所有约束”，这表示规划求解工具已经找到了一个问题的答案。我们在该消息的下方选择“保留规划求解的解”单选框，然后单击“确定”按钮关闭该对话框，这时找到的一组解，如图所示。
从上面的问题的求解过程我们可以感觉到，Excel规划求解工具相当强大。它能够把复杂的问题简化成Excel能够解决的问题，通过建立相对简单的规划求解模型，快速找到问题的答案。 现在我们改变问题的要求（即改变问题的约束条件），看一看Excel规划求解工具有没有能力应付。新的问题是：每种东西必须买3件以上，怎么才能正好把1000元钱一分不剩正好花掉呢？ 很简单，我们只需把问题的第二个约束条件变成“可变单元格>=3”，求解即可，如图所示。
下面是我们得到的改变了约束条件的新答案，选择“保留规划求解的解”单选框，然后单击“确定”按钮把规划求解的答案保存起来，如图所示。
直觉告诉我们，对于这个花钱的问题，可能有多种答案，但遗憾的是，Excel规划求解工具每次只能找到问题的一组解决方案，如果我们尝试找出另外一组答案，可以通过修改Excel规划求解工具的约束条件，排除已经找到的那一个答案的方式来解决。 下面回顾一下规划求解的整个工作过程。 （1）建立Excel规划求解模型：把实际问题用Excel表达出来，确定可变单元格、目标单元格和约束条件所对应的单元格区域。 （2）用Excel公式和函数建立可变单元格和目标单元格之间的联系。 （3）设置规划求解工具对话框：即设置目标单元格、可变单元格和遵守的约束。 我们可以看到，Excel规划求解工具把复杂的问题的求解过程变得相当简单。使用Excel规划求解工具的核心是建立实际问题的Excel规划求解模型，即建立可变单元格和目标单元格之间的联系。如果模型建好了，问题就解决了一大半。 关于Excel规划求解工具的其他设置，这里不再介绍。读者有兴趣的话，可上网搜索相关帮助。 Excel规划求解虽然有很多局限，但是其“易获得性”和“易用性”使得我们能够随时用它解决一些管理问题，只要告诉Excel规划求解工具解决问题的思路，把复杂的求解过程交给Excel规划求解工具处理就可以了！ |
|
|
