如何在Microsoft Excel中使用规划求解插件
您需要解决与线性规划、非线性规划和优化相关的数学问题吗?您必须学习如何在 Excel 中使用规划求解。
各种 Excel 工具都可以轻松驾驭复杂的数据分析领域,其中 Solver 最为突出。它可以帮助您进行假设分析,您可以在其中输入目标、约束和解析模型,这样 Excel 就可以为您完成繁重的工作。
了解如何有效使用 Solver 可以改变数据分析和数据科学领域的格局。无论您是分析师、学生还是专业人士,了解其机制都可以帮助您解决复杂的优化问题。
阅读本文直到最后,以了解 Excel 中规划求解的基础知识、其用户界面、算法以及可以有效使用它的实际场景。
推荐:自动化WordPress工作流程插件WP Scheduled Posts Pro
Excel 规划求解插件是什么?
Excel Solver 插件是一款假设分析工具,用于解决电子表格中的优化问题。它充当数学引擎,旨在为给定的一组约束找到最佳解决方案,例如,找到最大值或最小值。
通过调整选定的变量,规划求解可以最大化或最小化目标单元格,并遵守定义的限制。这种动态功能在资源分配、财务规划和调度等场景中非常有用。
Excel Solver 具有处理复杂计算的能力,可让您根据定量分析做出明智的决策。
自 Excel 2007 以来,Microsoft 一直在 Excel 中提供 Solver。这是一款免费的数据分析工具,不需要任何复杂的设置过程。但是,该工具的学习难度很高。
您必须创建一个可行的数据模型,在其中可以应用规划求解工具的数学和统计分析功能。
在 Excel 中使用规划求解的原因
以下是您可能希望在 Excel 中使用规划求解的原因:
- 规划求解通过在遵守给定约束的同时最大化或最小化目标来帮助找到最佳结果。
- 它解决涉及多个变量和约束的复杂数学问题,这些问题很难手动解决。
- 它对于有效分配资源很有用,无论是在财务规划、项目管理还是库存控制方面。
- Excel 中的规划求解可以帮助您根据定量分析和数学模型做出明智的决策。
- Excel Solver 还允许通过改变变量并观察其对结果的影响来测试各种场景。
- Excel 规划求解工具可自动寻找最佳解决方案,从而节省大量时间和精力。
- 它还减少了复杂计算中的人为错误,确保了结果的精确。
了解求解器插件用户界面
上图显示了 Microsoft 365 桌面应用程序的 Excel 上的典型规划求解用户界面。
您只需与以下 UI 元素交互即可解决标准优化问题:
设定目标
在这里,您可以选择 Excel 工作表上的计算单元格。您可以选择目标来 最大限度, 分钟或您想要实现的自定义目标,方法是在 的价值 场地。
例如,你想赚取 2,000 美元 通过提供远程和自由职业服务,每月可实现 1000 万美元的收入,如上例所示。
通过更改变量单元格
![通过更改变量单元格](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/By-changing-variable-cells-1024x581.png)
您还必须指出 Excel 可以操作哪些单元格或单元格区域来实现您上面设定的目标。这些单元格区域可以是计算单元格或固定值单元格。
在上例中,每个项目的支出是固定的。因此,我需要承接更多项目才能实现目标收入 2,000 美元。因此,我输入了有效范围 B栏, 喜欢 B2:B5
作为变量单元格。
受到限制
这些是您在项目中必须遵循的限制或规定,然后您希望达到优化值。
![如何在 Excel 中使用规划求解并遵守约束](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Constraints-1-1024x589.png)
在前面的例子中,我必须遵守以下约束:
- 写入最大值 (
B2
) 10个项目 - 图形设计最大值(
B3
) 8个项目 - 网络发布(
B4
) 无限项目 - 编辑最大值 (
B5
) 100 个项目 - 支出 (
C2:C5
)固定,因此未输入模型
因此,我在标有方框的相应字段中输入了约束。您可以单击 添加 将新约束添加到模型中。此外,选择一个现有约束并单击 改变 修改逻辑。最后,选择一个约束并点击 删除 按钮来解除约束。
加载/保存
加载/保存 按钮允许您使用之前配置的求解器模型或保存当前求解器模型。您主要保存或加载在模型中创建的约束。
![加载已保存的规划求解模型](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Loading-a-saved-Solver-model.png)
要加载,请单击 加载/保存 然后突出显示包含任何先前保存的 Excel Solver 模型的单元格区域。
![保存 Excel 求解器模型](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Save-an-Excel-solver-model.png)
要保存,请点击 载入/保存。然后,突出显示包含最小行数的空白单元格区域(在 加载/保存模型 对话框。
此求解器元素可帮助您尝试多组约束和模型以选择最佳一组。
选择解决方法
![解决方法](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Solving-methods.png)
Excel Solver 插件允许您从三种不同的算法中进行选择,如下所示:
推荐:Email自动化营销插件Mail Mint Pro
GRG非线性
GRG 的缩写代表广义梯度下降非线性。这是一种用于解决非线性问题的优化技术。它们可以涵盖多个可行区域或在变量单元内显示一组类似的值,同时保持约束满足。
单工LP
Solver 中使用的单纯形 LP 算法是一种解决线性规划问题的优化技术。它迭代地沿着可行区域的边缘移动,以最大化/最小化线性目标函数。
虽然在许多情况下它很有效,但它可能难以处理高度复杂的问题或退化的情况。只有当您确定优化问题属于线性类型时,您才必须选择此算法。
进化
Solver 中的进化算法模拟自然选择来优化复杂问题。它生成一组潜在解决方案,应用变异和交叉操作,并评估适应度。经过几代,更适合的解决方案得以生存并产生改进的后代,收敛到最佳或接近最佳的解决方案,适用于各种具有挑战性的优化场景。
求解器结果
![求解结果窗口](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Solver-results-window-1.png)
这 求解器结果 对话框允许您执行以下操作:
- 使用 Solver 生成的数据集替换现有数据集。选择 保留求解器解决方案 选项并点击 好的。
- 如果需要生成详细报告,请选择 报告 右侧菜单,然后点击 好的。
- 点击 保存场景 通过赋予场景名称来保存规划求解结果。
如何在 Microsoft 365 桌面应用程序版 Excel 中添加规划求解
在 Windows PC 上将 Solver 添加到 Excel 是一个简单的过程。您可以在此 Excel 中 Solver 的出色介绍中找到详细的分步说明:
在 Microsoft Excel 中安装规划求解的 2 种方法
Solver 可以在 Excel for Web 上运行吗?
如果您需要在 Excel for the Web 上探索非线性和线性规划以及数学优化问题的最佳解决方案,很遗憾,它无法在那里工作。在撰写本文时,Excel for the Web 不支持任何 Excel 插件。因此,Solver 无法在那里工作。
如何在 Mac 版 Excel 上安装规划求解插件
Excel Solver 插件还兼容各种 Excel for Mac 版本,例如:
- Microsoft 365 Mac 版专属 Excel
- Excel 2021 for Mac
- Excel 2019 for Mac
- Excel 2016 for Mac
- Excel for Mac 2011
![如何在 Mac 上的 Excel 中安装 Solver 插件](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/How-to-install-solver-add-in-excel-on-mac.png)
如果您在 Macbook 或 iMac 上使用上述任何 Excel 应用程序,请按照以下步骤在 Excel 上启用规划求解工具:
- 打开 Mac 版 Excel 应用程序来自 码头 或者 聚光灯。
- 前往 数据 功能区菜单上的选项卡。
- 点击 分析工具 按钮。
- 勾选 求解器插件 在 – 的里面 可用插件 菜单。
- 点击 好的 保存所做的更改。
![Mac 上的求解器插件](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Solver-on-Mac.png)
这 解算器 工具将显示为下方的可点击按钮 分析工具 按钮 数据 标签。
![单击 Excel 菜单首选项并选择查看](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Click-view.png)
如果你没有看到 分析工具 按钮 数据 选项卡,点击 Excel 菜单上的 Mac 工具栏。去那里 系统设置 或者 优先 菜单。
![激活 Excel 开发人员选项卡](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Checkmark-developer-tab.png)
点击 看法 菜单内的 创作 部分并勾选 “开发人员”选项卡 在下面 在功能区中,显示 部分。
如何在 Excel 中使用规划求解
以下是可以使用规划求解来最大化收入的真实数据集和场景。
实现收入目标
该规划求解场景将指导您需要承担多少个项目才能实现每周或每月的收入目标。
![项目状态](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Project-status.png)
查找当前项目、项目领域、支出和总收入。
![](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Project-constraints.png)
在接受客户的项目时,您也需要遵循上述限制。
![导航至数据求解器插件](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Navigate-to-data-solver-add-in.png)
现在,按照以下步骤将上述数据集放入规划求解中,并找出增加收入的最佳策略 2,000 美元:
- 最终的收入目标是
D6
,应该是一个公式。例如,我使用了以下内容:
=SUM(D2:D5)
- 现在,点击 数据 选项卡并选择功能区最右侧的“规划求解”插件。
- 这 Excel 规划求解 界面应该会出现。
![求解器参数目标、目标值和变量](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Solver-parameters-Objectives-goal-value-and-variables.png)
现在,您必须按照以下步骤配置 Solver 模型:
- 在 求解器参数 对话框中,单击 设定目标 字段并输入
$D$6
。这就是该模型的目标。 - 选择 选择的价值 并输入目标收入,即 2,000 美元。
- 点击 通过更改变量单元格 字段并突出显示工作表上的单元格范围。例如,
$B$2:$B$5
。 - 如果想要包含多个变量单元格范围,请放置 Excel 分隔符或逗号,然后在工作表上选择下一个单元格范围。
您可以按照以下方式将约束输入到模型中:
![受限制菜单](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Subject-to-constraints-menu.png)
- 进入 受到限制 字段中,您必须输入为实现目标所必须遵循的各种限制。
- 要添加新约束,请点击 添加 按钮。
![添加约束对话框](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Add-constraints-dialog-box.png)
- 在 添加约束 对话框中,选择一个单元格引用,在中间设置一个逻辑运算符,并以负数或正数输入约束值。
- 点击 好的 来保存它。
![选择求解引擎](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Selecting-the-solving-engine.png)
现在,是时候计算一下你必须承担多少个项目才能实现目标了。计算方法如下:
- 放 GRG非线性 作为默认的求解算法。如果您确信这些算法会产生更好的结果,则可以将其更改为备选选项。
- 点击 解决 按钮。
![保留求解器解决方案](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/Keep-solver-solution.png)
- 如果结果页面显示“规划求解找到了一个整数解……”消息,点击 好的 将值输入到数据集中。
![如何使用 Excel 中的规划求解优化线性和非线性问题](https://cdn-5a6cb102f911c811e474f1cd.closte.com/wp-content/uploads/2023/08/How-to-use-Solver-in-Excel-to-optimize-linear-and-non-linear-problems.png)
上图显示了您为实现设定的收入目标必须实施的优化。
结论
现在,您知道如何使用 Excel Solver 插件了。按照本 Excel 教程中概述的说明使用 Excel 的这个强大的优化工具。
不要忘记在下面评论您使用 Excel Solver 工具时的体验。如果您发现任何上面未提及的 Solver 技巧,请在您的评论中写下来。