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

规划求解是 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. 单击所引用的单元格和约束之间所需的关系 ( <==>=intbindif )。如果单击 " 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), 最大为 $20000 (单元格 D5) 的总预算限制, 直到总利润 (客观元格 D7) 达到最大可能值。 可变单元格中的值用于计算每个季度的利润, 因此它们与公式目标单元格 D7 (即, 第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 (一般化精简渐变) 非线性

默认选项, 对于使用除 IF 之外的大多数 Excel 函数的模型, 请选择 "查找" 和其他 "步骤" 函数。

单工 LP

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

进化

当模型使用依赖于可变单元格的参数时, 根据遗传算法, 此方法最适合使用遗传算法。

注意: "规划求解" 程序代码的部分内容是版权所有 1990-2010 (由 Frontline Systems)。部分是版权所有 1989, 由最佳方法, Inc. 提供。

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

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

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

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

Frontline 系统, Inc.。
邮政信箱 4288
倾斜村, 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 2016 for Windows 中的键盘快捷方式

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

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

注意:  本页面是自动翻译的,可能包含语法错误或不准确之处。 我们的目的是使此内容能对你有所帮助。 能否告知我们此信息是否有所帮助? 下面是该参考内容的英文版

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

此信息是否有帮助?

谢谢您的反馈!

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

×