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

注意: 我们希望能够尽快以你的语言为你提供最新的帮助内容。本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。可以在本页面底部告诉我们此信息是否对你有帮助吗?请在此处查看本文的英文版本以便参考。

规划求解是 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. 执行下列操作之一:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    提示: 您可以通过保存工作簿, 将 "规划求解参数" 对话框中的最后一项内容保存到工作表中。工作簿中的每个工作表都可以有自己的规划求解选择, 所有这些都保存。你还可以为工作表定义多个问题, 方法是单击 "加载/保存" 以单独保存问题。

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

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

  • LP Simplex    用于线性问题。

  • 进化    用于非平滑问题。

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

在下面的示例中, 每个季度中的广告级别影响销售的单位数量、间接确定销售额额、相关费用和利润。"规划求解" 可以更改广告的季度预算 (决策变量单元格 B5: C5), 最大为 $20000 (单元格 D5) 的总预算限制, 直到总利润 (目标单元格 D7) 达到最大可能值。可变单元格中的值用于计算每个季度的利润, 因此它们与公式目标单元格 D7、= SUM (Q1 利润: 第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.。

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

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

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

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

Excel 函数(按字母顺序)

Excel 函数(按类别列出)

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

此信息是否有帮助?

谢谢您的反馈!

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

×