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

规划求解是 Microsoft Excel 加载项程序,可用于模拟分析。 使用“规划求解”查找一个单元格 (称为目标单元格 )中公式的优化(最大或最小)值,受限或受制于工作表上其他公式单元格的值。 “规划求解”与一组用于计算目标和约束单元格中公式的单元格(称为决策变量或变量单元格)一起工作。 “规划求解”调整决策变量单元格中的值以满足约束单元格上的限制,并产生您对目标单元格期望的结果。

简单来说,使用“规划求解”可通过更改其他单元格来确定一个单元格的最大值或最小值。 例如,你可以更改计划的广告预算金额,并查看对计划利润额产生的影响。

注意: 2007 Excel之前的规划求解版本将目标单元格称为"目标单元格",决策变量单元格称为"更改单元格"或"可调整单元格"。 对 Excel 2010 的规划求解加载项进行了许多改进,因此,如果使用 Excel 2007,体验将略有不同。

在以下示例中,每个季度的广告级别影响销售的单位数,间接确定销售收入、关联费用和利润。 “规划求解”可以更改广告的季度预算(决策变量单元格 B5:C5),最多 200,000 人民币的总预算限制(单元格 F5),直到总利润(目标单元格 F7)达到最大可能数量。 变量单元格中的值用于计算每个季度的利润,因此它们与公式目标单元格 F7、=SUM (Q1 Profit:Q2 Profit) 相关。

使用“规划求解”之前

1. 变量单元格

2. 约束条件单元格

3. 目标单元格

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

使用“规划求解”之后

  1. 在“数据”选项卡的“分析”组中,单击“规划求解”。
    Excel 功能区图像

    注意: 如果“规划求解”命令或“分析”组不可用,则需要激活“规划求解”加载项。 请参阅:如何激活规划求解加载项

    Excel 2010 +“规划求解”对话框的图像
  2. 在“设置目标”框中,输入目标单元格的单元格引用或名称。 目标单元格必须包含公式。

  3. 执行下列操作之一:

    • 若要使目标单元格的值尽可能大,请单击“最大值”。

    • 若要使目标单元格的值尽可能小,请单击“最小值”。

    • 若要使目标单元格为确定值,请单击“”,然后在框中键入数值。

    • 在“通过更改可变单元格”框中,输入每个决策变量单元格区域的名称或引用。 用逗号分隔不相邻的引用。 可变单元格必须直接或间接与目标单元格相关联。 最多可以指定 200 个可变单元格。

  4. 在“遵守约束”框中,通过执行下列操作输入任何要应用的约束:

    1. 在“规划求解参数”对话框中,单击“添加”。

    2. 在“单元格引用”框中,输入要对其中数值进行约束的单元格区域的单元格引用或名称。

    3. 单击 (<=、=、>=、int、bindif ) 引用单元格和约束之间的关系。 如果单击int,"约束"框中将显示整数。 如果单击 bin则二进制文件 将显示在" 约束" 框中。 如果单击dif,"约束"框中将显示 alldifferent。

    4. 如果在“约束”框中选择关系 <=、= 或 >=,请键入数字、单元格引用或名称、公式。

    5. 执行下列操作之一:

      • 要接受约束并添加另一个约束,请单击“添加”。

      • 要接受约束条件并返回“规划求解参数”对话框,请单击“确定”。
        注意    只能为决策变量单元格上的约束条件应用 intbindif 关系。

        通过执行下列操作可以更改或删除现有的约束:

    6. 在“规划求解参数”对话框中,单击要更改或删除的约束条件。

    7. 单击“更改”并进行更改,或单击“删除”。

  5. 单击“求解”,再执行下列操作之一:

    • 若要在工作表中保存求解值,请在“规划求解结果”对话框中单击“保存规划求解的解”。

    • 若要在单击“求解”之前恢复原值,请单击“恢复原值”。

    • 您可以按 Esc 键中断求解过程。 Excel 利用找到的有关决策变量单元格的最后值重新计算工作表。

    • 要在“规划求解”找到解决方案后创建基于您的解决方案的报告,您可以单击“报表”框中的报告类型,然后单击“确定”。 此报告是在工作簿中的一个新工作表上创建的。 如果“规划求解”未找到解决方案,则只有部分报表可用或全部不可用。

    • 要将决策变量单元格值保存为可以稍后显示的方案,请在“规划求解结果”对话框中单击“保存方案”,然后在“方案名”框中键入方案的名称。

  1. 定义了问题之后,请在“规划求解参数”对话框中单击“选项”。

  2. 在“选项”对话框中,选中“显示迭代结果”复选框以查看每个试解的结果,然后单击“确定”。

  3. 在“规划求解参数”对话框中,单击“求解”。

  4. 在“显示中间结果”对话框中,请执行下列操作之一:

    • 要停止求解过程并显示“规划求解结果”对话框,请单击“停止”。

    • 要继续求解过程并显示下一个中间结果,请单击“继续”。

  1. 在“规划求解参数”对话框中,单击“选项”。

  2. 为对话框中“所有方法”、“GRG 非线性”和“进化”选项卡上的任意选项选择或输入值。

  1. 在“规划求解参数”对话框中,单击“加载/保存”。

  2. 为模型范围输入单元格区域,然后单击“保存”或“加载”。

    在保存模型时,为要放置该问题模型的空单元格区域中垂直范围的第一个单元格输入引用。 装入模型时,输入包含问题模型的整个单元格区域的引用。

    提示: 您可以通过保存工作簿,在包含工作表的"规划 求解 参数"对话框中保存最后一个选定区域。 工作簿中的每个工作表可能有其自己的规划求解选择,并且所有这些选择都保存。 您也可以通过单击"加载 / 保存"来单独保存问题,为工作表定义多个问题。

您可以在“规划求解参数”对话框中选择以下三种算法或求解方法中的任意一种:

  • 广义简约梯度 (GRG) 非线性    用于平滑非线性问题。

  • LP Simplex    用于线性问题。

  • 进化    用于非平滑问题。

重要: 应先启用规划求解加载项。 有关详细信息,请参阅 加载规划求解加载项

在以下示例中,每个季度的广告级别影响销售的单位数,间接确定销售收入、关联费用和利润。 规划求解可以更改用于广告 (决策变量单元格 B5:C5) 的季度预算,直到总预算约束为 20,000 (单元格 D5) ,直到总利润 (目标单元格 D7) 达到可能的最大金额。 变量单元格中的值用于计算每个季度利润,因此它们与公式目标单元格 D7、=SUM (Q1 Profit:Q2 Profit) 。

示例规划求解计算

标注 1 变量单元格

标注 2 约束单元格

标注 3 目标单元格

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

使用新值进行示例规划求解计算

  1. 在Excel 2016 for Mac:单击"数据>规划求解"。

    规划求解

    在Excel for Mac 2011:单击"数据"选项卡,在"分析"下,单击"规划求解"。

    "数据"选项卡、"分析"组、规划求解加载项

  2. "设置目标"中, 单元格引用 单元格的名称或名称。

    注意: 目标单元格必须包含公式。

  3. 执行下列操作之一:

    若要

    执行此操作

    使目标单元格的值尽可能大

    单击"最大值"。

    使目标单元格的值尽可能小

    单击"最小"。

    将目标单元格设置为特定值

    单击 "值",然后在框中键入值。

  4. 在“通过更改可变单元格”框中,输入每个决策变量单元格区域的名称或引用。 使用逗号分隔不相关引用。

    可变单元格必须直接或间接与目标单元格相关联。 最多可以指定 200 个可变单元格。

  5. "限制对象 "框中,添加要应用的任何约束。

    若要添加约束,请执行以下步骤:

    1. 在“规划求解参数”对话框中,单击“添加”。

    2. 在“单元格引用”框中,输入要对其中数值进行约束的单元格区域的单元格引用或名称。

    3. "<=关系"弹出菜单上,选择引用的单元格和约束之间的关系。如果选择"<=、=>=,在"约束"框中,键入数字、单元格引用或名称或公式。

      注意: 只能在决策变量单元格的约束中应用 int、bin 和 dif 关系。

    4. 执行下列操作之一:

    若要

    执行此操作

    接受约束并添加另一个约束

    单击“添加”。

    接受约束并返回到"规划 求解参数" 对话框

    单击“确定”。

  6. 单击 "求解",然后执行下列操作之一:

    若要

    执行此操作

    在工作表中保留解决方案值

    "规划求解结果" 对话框中单击"保留 规划求解 解决方案"。

    还原原始数据

    单击"还原原始值"。

注意: 

  1. 若要中断解决方案过程,请按 ESC。 Excel为可调整单元格找到的最后一个值重新计算工作表。

  2. 要在“规划求解”找到解决方案后创建基于您的解决方案的报告,您可以单击“报表”框中的报告类型,然后单击“确定”。 报表在工作簿中的新工作表上创建。 如果"规划求解"找不到解决方案,则用于创建报表的选项不可用。

  3. 若要将调整单元格值另存为稍后可显示的方案,请单击"规划求解结果"对话框中的"保存方案",然后在"方案名称"框中键入方案的名称。

  1. 在Excel 2016 for Mac:单击"数据>规划求解"。

    规划求解

    在Excel for Mac 2011:单击"数据"选项卡,在"分析"下,单击"规划求解"。

    "数据"选项卡、"分析"组、规划求解加载项

  2. 定义问题后,在"规划求解参数"对话框中,单击"选项"。

  3. 选中"显示迭代结果"复选框以查看每个试用解决方案的值,然后单击"确定"。

  4. 在“规划求解参数”对话框中,单击“求解”。

  5. 在" 显示试用 解决方案"对话框中,执行下列操作之一:

    若要

    执行此操作

    停止解决方案过程并显示"规划 求解结果" 对话框

    单击"停止"。

    继续解决方案过程并显示下一个试用解决方案

    单击“继续”。

  1. 在Excel 2016 for Mac:单击"数据>规划求解"。

    规划求解

    在Excel for Mac 2011:单击"数据"选项卡,在"分析"下,单击"规划求解"。

    "数据"选项卡、"分析"组、规划求解加载项

  2. 单击 "选项",然后在" 选项"或 "规划求解 选项"对话框中 ,选择以下一个或多个选项:

    若要

    执行此操作

    设置解决方案时间和迭代

    在"所有方法"选项卡上的"求解限制"下,在"最大 () "框中,键入要允许用于解决方案时间秒数的秒数。 然后, 在"迭代 "框中,键入要允许的最大迭代数。

    注意: 如果解决方案过程在规划求解找到解决方案之前达到最大迭代时间或迭代次数,"规划求解"将显示" 显示试用解决方案" 对话框。

    设置精度

    在" 所有方法" 选项卡上的" 约束 精度"框中,键入您需要的精度。 数字越小,精度越高。

    设置收敛程度

    "GRG 非线性"或"进化"选项卡上的"收敛"框中,键入在规划求解停止使用解决方案之前,您希望在前五次迭代中允许的相对更改量。 数字越小,允许的相对更改越小。

  3. 单击“确定”。

  4. 在"规划求解参数"对话框中,单击"求解"或"关闭"。

  1. 在Excel 2016 for Mac:单击"数据>规划求解"。

    规划求解

    在Excel for Mac 2011:单击"数据"选项卡,在"分析"下,单击"规划求解"。

    "数据"选项卡、"分析"组、规划求解加载项

  2. 单击"加载/保存",输入模型区域单元格区域,然后单击"保存"或"加载"。

    在保存模型时,为要放置该问题模型的空单元格区域中垂直范围的第一个单元格输入引用。 装入模型时,输入包含问题模型的整个单元格区域的引用。

    提示: 您可以通过保存工作簿,在包含工作表的"规划 求解 参数"对话框中保存最后的选择。 工作簿中的每个工作表可能有其自己的"规划求解"选项,并保存所有这些选择。 您也可以通过单击"加载 / 保存"来单独保存问题,为工作表定义多个问题。

  1. 在Excel 2016 for Mac:单击"数据>规划求解"。

    规划求解

    在Excel for Mac 2011:单击"数据"选项卡,在"分析"下,单击"规划求解"。

    "数据"选项卡、"分析"组、规划求解加载项

  2. 在" 选择求解方法 "弹出菜单上,选择下列选项之一:

求解方法

说明

GRG (非线性通用化) 渐变

对于使用大多数函数的模型,默认Excel IF、CHOOSE、LOOKUP 和其他"step"函数。

Simplex LP

使用此方法解决线性编程问题。 模型应在依赖于变量单元格的公式中使用 SUM、SUMPRODUCT、+ 和 *。

进化

当模型对依赖于可变单元格的参数使用 IF、CHOOSE 或 LOOKUP 时,基于算法的此方法最佳。

注意: 规划求解程序代码的一部分由 Frontline Systems, Inc. 版权所有 1990-2010。 部分由 Optimal Methods, Inc.

由于加载项程序在 Excel 网页版 中不受支持,因此你将无法使用规划求解加载项对数据运行 if-if 分析,以帮助你找到最佳解决方案。

如果您有 Excel桌面应用程序,您可以使用"在 Excel 中打开"按钮打开工作簿以使用规划求解加载项

有关使用“规划求解”的更多帮助

有关“规划求解”的更详细帮助,请联系:

Frontline Systems, Inc.
P.O. Box 4288
Incline Village, NV 89450-4288
(775) 831-0300
网站:http://www.solver.com
电子邮件:info@solver.com
规划求解帮助 www.solver.com。

“规划求解”程序代码的部分为 Frontline Systems, Inc 公司 1990-2009 年版权所有,部分为 Optimal Methods, Inc 公司 1989 年版权所有。

需要更多帮助吗?

可随时在 Excel 技术社区中咨询专家,在解答社区获得支持,或在 Excel User Voice 上建议新功能或功能改进。

另请参阅

使用规划求解进行资本预算

使用规划求解确定最佳产品组合

模拟分析简介

Excel 中的公式概述

如何避免损坏的公式

检测公式中的错误

Excel 中的键盘快捷方式

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

需要更多帮助?

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

×