运用Excel 2007“规划求解”定义并解答问题

前言

“规划求解”是一组命令的组成部分（有时也称作假设分析 （假设分析：该过程通过更改单元格中的值来查看这些更改对工作表中公式结果的影响。例如，更改分期支付表中的利率可以调整支付金额。）工具）。借助“规划求解”，可求得工作表上某个单元格（称为目标单元格）中公式 （公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）的最优值。“规划求解”将对直接或间接与目标单元格中的公式相关的一组单元格进行处理。“规划求解”将调整所指定的变动单元格（称为可变单元格）中的值，从目标单元格公式中求得所指定的结果。您可以应用约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）来限制“规划求解”可在模型中使用的值，而且约束条件可以引用影响目标单元格公式的其他单元格。

概述

使用“规划求解”可通过更改其他单元格来确定某个单元格的最大值或最小值。例如，您可更改计划广告预算额来查看对您的计划收益额的影响。

“规划求解”示例

在 下面的示例中，每个季度的“广告费”影响到“销售数量”，从而间接决定了销售收入以及相关的成本和利润。通过“规划求解”可以更改季度广告费用预算（在单 元格区域 B5:C5 中），以使总利润达到最大值。其中，总预算不能超过 ￥20,000（在单元格 F5 中）。可变单元格中的数值用来计算每个季度的利润，所以它们与目标单元格 F7 中的公式 =SUM(一季度利润:二季度利润) 相关联。

可变单元格

约束条件单元格

目标单元格

运行“规划求解”后得到的新数值如下。

定义并求解问题。

1. 在“数据”选项卡上的“分析”组中，单击“规划求解”。

   如果“规划求解”命令或“分析”组不可用，则需要加载“规划求解”加载宏 （加载项：为 Microsoft Office 提供自定义命令或自定义功能的补充程序。）程序。

   如何加载“规划求解”加载宏程序

   1. 单击“Microsoft Office 按钮”，单击“Excel 选项”，然后单击“加载项”类别。
   2. 在“管理”框中，单击“Excel 加载宏”，然后单击“搜索”。
   3. 在“可用加载宏”框中，选中“规划求解加载项”复选框，然后单击“确定”。
2. 在“设置目标单元格”框中，输入目标单元格的单元格引用 （单元格引用：用于表示单元格在工作表上所处位置的坐标集。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）或名称 （名称：代表单元格、单元格区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。）。目标单元格必须包含公式 （公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）。
3. 请执行下列操作之一：
   • 若要使目标单元格的值尽可能大，请单击“最大值”。
   • 若要使目标单元格的值尽可能小，请单击“最小值”。
   • 若要使目标单元格为确定值，请单击“值”，然后在框中键入数值。
4. 在“可变单元格”框中，输入每个可变单元格的名称或引用，用逗号分隔不相邻的引用。可变单元格必须直接或间接与目标单元格相关。最多可以指定 200 个可变单元格。
5. 如果要使“规划求解”根据目标单元格自动建议可变单元格，请单击“猜测”。
6. 在“约束”框中，输入任何要应用的任何约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）。

   如何添加、更改或删除约束条件

   添加约束条件

   1. 在“规划求解参数”对话框的“约束”下，单击“添加”。
   2. 在“单元格引用”框中，输入要对其中数值进行约束的单元格区域的单元格引用 （单元格引用：用于表示单元格在工作表上所处位置的坐标集。例如，显示在第 B 列和第 3 行交叉处的单元格，其引用形式为“B3”。）或名称 （名称：代表单元格、单元格区域、公式或常量值的单词或字符串。名称更易于理解，例如，“产品”可以引用难于理解的区域“Sales!C20:C30”。）。
   3. 单击希望在引用单元格和约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）之间使用的关系（“<=”、“=”、“>=”、“int”或“bin”）。如果单击“int”，则“约束值”框中会显示“整数”；如果单击“bin”，则“约束值”框中会显示“二进制”。
   4. 在“约束值”框中，键入数字、单元格引用或名称，或键入公式 （公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 (=) 开始。）。
   5. 请执行下列操作之一：
      • 要接受约束条件并添加另一个约束条件，请单击“添加”。
      • 要接受约束条件并返回“规划求解参数”对话框，请单击“确定”。

    注释 

   • 只能在对可变单元格的约束条件中应用“int”和“bin”关系。
   • 当“规划求解选项”对话框中的“采用线性模型”复选框处于选中状态时，对约束条件的数量没有限制。对于非线性问题，每个可变单元格除了变量的范围和整数限制外，还可以有多达 100 个约束条件。

   更改或删除约束条件

   1. 在“规划求解参数”对话框的“约束”下，单击要更改或删除的约束条件 （约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。）。
   2. 单击“更改”并进行更改，或单击“删除”。
7. 单击“求解”，再执行下列操作之一：
   • 若要在工作表中保存求解值，请单击“规划求解结果”对话框中的“保存规划求解结果”。
   • 若要恢复原始数据，请单击“恢复为原值”。

    注释 

   • 按 Esc 可以中止求解过程，Microsoft Office Excel 将按最后找到的可变单元格的数值重新计算工作表。
   • 要在“规划求解”求出解后基于求解结果创建报表，您可以单击“报表”框中的一种报表类型，然后单击“确定”。创建的报表位于工作簿中的一个新工作表上。如果“规划求解”未求出解，用于创建报表的选项将不可用。
   • 要将调整单元格值保存为可以稍后显示的方案，请在“规划求解结果”对话框中单击“保存方案”，然后在“方案名称”框中键入方案的名称。

单步执行“规划求解”试解

1. 定义了问题之后，请在“规划求解参数”对话框中单击“选项”。
2. 在“规划求解选项”对话框中，选中“显示迭代结果”复选框以查看每个试解的结果，然后单击“确定”。
3. 在“规划求解参数”对话框中，单击“求解”。
4. 在“显示试解”对话框中，执行下列操作之一：
   • 要停止求解过程并显示“规划求解结果”对话框，请单击“停止”。
   • 要继续求解过程并显示下一个试解，请单击“继续”。

更改“规划求解”的求解方法

1. 在“规划求解参数”对话框中，单击“选项”。
2. 在“规划求解选项”对话框中，选择下列一个或多个选项：

   求解时间与迭代次数

   1. 在“最长运算时间”框中，键入限定的最长求解时间（秒数）。
   2. 在“迭代次数”框中，键入要限定的最大迭代次数。

    注释   如果求解过程在“规划求解”求出结果之前即达到最长求解时间或最大迭代次数，“规划求解”会显示“显示试解”对话框。

   精度

   • 在“精度”框中，键入所需的精度。该数值越小，精度就越高。

   整数允许误差

   • 在“允许误差”框中，键入要在求解中限定的误差百分比。

   收敛度

   • 在“收敛度”框中，键入“规划求解”最后五次迭代值之间相对变化量的限定值（相对变化量小于此值时即结束求解，得出结果）。此数值越小，相邻迭代结果之间允许的相对变化就越小。

    注释   可单击对话框中的“帮助”按钮来获取有关其他选项的详细信息。

3. 单击“确定”。
4. 在“规划求解参数”对话框中，单击“求解”或“关闭”。

保存或装入问题模型

1. 单击“选项”。
2. 单击“保存模型”或“装入模型”。
3. 在保存模型时，输入对要在其中放置问题模型的垂直空白单元格区域中第一个单元格的引用。在装入模型时，输入对包含问题模型的整个单元格区域的引用。

 提示   通过单击“Microsoft Office 按钮”，然后单击“保存”，您可以将在“规划求解参数”对话框中最后选择的内容随工作表一起保存。通过单击“保存模型”分别保存各个问题，您还可以为工作表定义多个问题。

“规划求解”使用的算法与方法

Microsoft Office Excel 的“规划求解”工具取自德克萨斯大学奥斯汀分校的 Leon Lasdon 和克里夫兰州立大学的 Alan Waren 共同开发的 Generalized Reduced Gradient (GRG2) 非线性最优化代码。

线性和整数规划问题取自 Frontline Systems, Inc. 公司的 John Watson 和 Daniel Fylstra 提供的有界变量单纯形法和分支边界法。有关“规划求解”使用的内部求解过程的详细信息，请与下列地址联系：

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
网站：https://www.solver.com
电子邮件：info@solver.com

Microsoft Office Excel“规划求解”程序代码的部分为 Frontline Systems, Inc 公司 1990、1991、1992 和 1995 年版权所有，部分为 Optimal Methods, Inc 公司 1989 年版权所有。

声明：欢迎各大网站转载本站文章，还请保留一条能直接指向本站的超级链接，谢谢！

时间:2007-07-10 02:57:04,点击:65824