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

重要:  本文是由机器翻译的,请参阅免责声明。请在 此处 中查找本文的英文版本以便参考。

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

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

注意: 版本的 Excel 2007 之前的规划求解称为"目标单元格"称为"可变单元格"决策变量单元格和"可变单元格"作为目标单元格。许多都进行了改进的规划求解加载项中的 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”、“bin”或“dif”)。如果单击“int”,则“约束”框中会显示“整数”。如果您单击“bin”,则“二进制”将出现在“约束”框中。如果您单击“dif”,则“alldifferent”将出现在“约束”框中。

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

    5. 执行下列操作之一:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    提示: 您可以保存工作簿,工作表具有规划求解参数对话框中保存的最后一个选项。工作簿中的每个工作表可能具有其自己的规划求解选项,并且它们全部会保存。您还可以通过单击加载/保存分别保存定义多个工作表的问题。

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

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

  • LP Simplex    用于线性问题。

  • 进化    用于非平滑问题。

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

在下面的示例中,每个季度中的广告级别影响销售量,从而间接决定了销售收入的相关联的费用和利润数。规划求解可以向上 20000 (单元格 D5) 的总预算限制更改季度预算的广告 (决策变量单元格区域 b5: c5),直到总利润 (目标单元格 D7) 达到最大值。可变单元格区域中的值用于计算每个季度的利润相关到公式目标单元格 D7,以便 = SUM (第 1 季度利润: 第 2 季度利润)。

示例规划求解计算

标注 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 函数,如果、 选择、 查找和其他"步骤"的函数。

LP simplex

使用此方法的线性编程问题。您的模型应使用 SUM,SUMPRODUCT,+-和 * 依赖于可变单元格的公式中。

进化

此方法时,基于遗传算法,最好,当您的模型使用,如果选择或查找依赖于可变单元格的参数。

注意: 规划求解程序代码部分是通过 Frontline Systems 的版权 1990年 2010年,Inc.部分是通过最佳方法,Inc.版权 1989

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

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

Frontline Systems, Inc.
P.O.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 上建议新功能或功能改进。

注意: 机器翻译免责声明:本文是由无人工介入的计算机系统翻译的。Microsoft 提供机器翻译是为了帮助非英语国家/地区用户方便阅读有关 Microsoft 产品、服务和技术的内容。由于机器翻译的原因,本文可能包含词汇、语法或文法方面的错误。

另请参阅

使用“规划求解”确定资本预算

使用“规划求解”进行财务规划

使用“规划求解”确定最优产品组合

使用规划求解工具执行模拟分析

模拟分析简介

Excel 中的公式概述

如何避免损坏的公式

使用错误检查检测公式中的错误

Excel 2016 for Windows 中的键盘快捷方式

Excel 2016 for Mac 中的键盘快捷方式

Excel 函数(按字母顺序)

Excel 函数(按类别)

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

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×