ARTICLE · 1087651
Excel-规划求解
“规划求解”是 Excel 的一个加载项，用于在给定约束条件下，通过调整多个可变单元格来找到目标单元格的最优解（最大值、最小值或特定值）。它适合处理像“在预算限制下，如何分配资源使利润最大化”这类无法简单套用公式的问题。
说人话：
1、可以解方程：利用Excel单元格，照着原方程式输入，不需要手工计算。
2、迭代计算：约定条件下，需要重复计算的问题。
📥 加载规划求解
“规划求解”默认不显示在功能区，需要先手动启用。
Windows 系统：
点击 Excel 左上角的 “文件”，选择 “选项”。
在弹出的窗口中，点击左侧的 “加载项”。
在窗口底部，确保“管理”下拉框选择的是 “Excel 加载项”，然后点击 “转到”。
在可用加载项列表中，勾选 “规划求解加载项”，点击 “确定”。如果系统提示未安装，选择“是”进行安装。
启用后，在 Excel 的 “数据” 选项卡下，“分析” 组中会显示 “规划求解” 按钮。
macOS 系统：
点击顶部菜单栏的 “工具”，选择 “Excel 加载项”。
勾选 “规划求解加载项”，点击 “确定”。
同样，按钮会出现在 “数据” 选项卡下。
🎯 规划求解的核心组件
在开始使用前，需要理解规划求解模型通常包含的三大要素：
组件 | 对应你场景中的概念 | 说明 |
|---|---|---|
目标单元格 (Objective Cell) | 你想优化的结果，比如“最终利润”或“误差” | 必须是包含公式的单元格。你希望这个公式的结果达到最大、最小或某个特定值。 |
可变单元格 (Variable Cells) | 你要求解的未知数，比如“原始浆料重量 x”和“固含量 y” | 规划求解会不断调整这些单元格的数值，来尝试达成目标。 |
约束条件 (Constraints) | 问题必须遵守的规则，比如“固含量=47%” | 限制可变单元格的取值范围，确保求解结果符合实际逻辑。 |
约束条件详解
在 Excel 规划求解的“添加约束”对话框中，关系下拉列表里的 int、bin、dif 是三种特殊约束，分别代表：
| int | |||
| bin | |||
| dif |
下面详细解释。
1. int —— 整数约束
含义：被约束的单元格只能取整数，不能有小数。
典型用途：
人数、产品数量、机器台数等必须是整数的变量。
例如：生产计划中，A 产品生产 3 件、B 产品生产 5 件，不能是 3.7 件。
设置方法：
在“添加约束”中，单元格引用选择可变单元格（或包含可变单元格的公式单元格）。
关系下拉选择 int。
右侧“约束”框会自动变成“整数”，不需要填数值。
点击“确定”。
注意：
整数约束会让问题变成整数规划，求解时间通常比连续变量长很多。
如果模型是线性的，Solver 会用分支定界法；如果模型是非线性的，通常需要改用“演化”求解器。
2. bin —— 二进制约束
含义：被约束的单元格只能取 0 或 1。
典型用途：
表示“是/否”“选/不选”“开/关”的决策变量。
例如：是否投资某个项目（1=投资，0=不投资）、是否开启某条生产线。
设置方法：
在“添加约束”中，单元格引用选择可变单元格。
关系下拉选择 bin。
右侧“约束”框会自动变成“二进制”，不需要填数值。
点击“确定”。
注意：
bin 是 int 的特例（整数且限定在 0 和 1）。
常用于 0-1 规划、背包问题、选址问题等。
3. dif —— 全不同约束
含义：被约束区域内的所有单元格，其值必须两两不同（互不相等）。
典型用途：
分配问题：给 5 个员工分配 5 个不同的班次编号。
排序问题：一组变量的取值不能重复。
例如：A1:A5 是 5 个可变单元格，要求它们分别代表 1~5 的某个排列，就可以用 dif 约束。
设置方法：
在“添加约束”中，单元格引用选择一个区域（例如
A1:A5）。关系下拉选择 dif。
右侧“约束”框会自动变成“全不同”，不需要填数值。
点击“确定”。
注意：
dif 约束通常需要与 int 约束配合使用，否则连续变量很容易满足“互不相同”，但结果没有实际意义。
dif 是高度非线性的组合约束，求解难度大，一般需要使用 “演化”求解器。
所选区域必须是可变单元格区域，不能是公式单元格。
如果区域较大，求解可能非常慢甚至找不到解。
总结对比
| int | |||
| bin | |||
| dif |
补充说明
在 Excel 中文版界面中，这些选项可能直接显示为“整数”“二进制”“全不同”，而不是英文缩写。如果界面是英文，则对应 int、bin、dif。
添加这些约束后，规划求解的求解方法可能需要调整：
纯线性 + int/bin → 可用“单纯形线性规划”配合分支定界。
非线性 + int/bin/dif → 通常必须选“演化”求解器。
整数、二进制、全不同约束都会显著增加求解复杂度，实际使用时尽量缩小可变单元格范围，并给出合理的初始值或上下界，以提高求解成功率。
🚀 应用示例1：解方程，以非线性二元方程为例
以下步骤展示了如何用规划求解解出以下两个方程：
方程式1：(xy+15)/(x+65)=15.36%
方程式2：(xy+72.49)/(X+122.49)=47%
这两个方程式，是一个调整浆料固含量的问题，题目如下：
有个原始浆料，重量是x，单位kg，固含量是y，
加入50kg的溶剂和15kg的粉料，固含量为15.36%，
再加入57.49kg的粉料，固含量为47%。
求原始浆料的重量x和固含量y
第一步：在工作表中搭建模型
按照你的物理过程，直接在单元格里“翻译”题目，不需要手工整理方程。
单元格 | 内容 | 说明 |
|---|---|---|
B1 |
| 可变单元格：原始浆料重量 |
B2 |
| 可变单元格：原始浆料固含量 |
B5 |
| 第一阶段固含量计算公式，左边是题目原式 |
B7 |
| 第二阶段固含量计算公式 |
D5 |
| 方程1的误差 |
D7 |
| 方程2的误差 |
第二步：打开“规划求解”并设置参数
点击“数据”选项卡下的“规划求解”按钮，在弹出的对话框中进行设置：
设置目标：选择
D5单元格。选择 “值”，并输入0。这表示我们希望方程1的误差为0。通过更改可变单元格：选择
B1:B2区域。这就是规划求解可以调整的未知数x和y。遵守约束：点击“添加”，设置
D7 = 0。这表示方程2的误差也必须为0。选择求解方法：选择 “GRG 非线性”。因为你的方程含有
xy项，是非线性的。
第三步：求解并保存结果
点击 “求解”。Excel 会开始迭代计算。完成后，会弹出“规划求解结果”对话框，选择 “保留规划求解的解”，然后点击“确定”。此时，B1 和 B2 单元格中的数值就是求解出的 x 和 y。
⚠️ 注意事项
非线性问题与初始值：对于非线性问题，规划求解可能找到的是“局部最优解”而非“全局最优解”。如果结果不合理，可以尝试给可变单元格
B1和B2填入一个更接近真实情况的初始值（比如基于物理直觉的猜测），再重新求解。留空单元格：在“通过更改可变单元格”中留空，Excel 会默认将其视为
0作为搜索的起点。留空通常是可以工作的，但填入一个合理的初始值有时能提高收敛速度。
方程式在结构上属于非线性问题，使用“GRG 非线性”方法正是为了处理这类问题。如果求解结果不符合预期，可以尝试调整初始值再试。
3种物品，给出单价，要算出物品各出多少数量，合计金额是10000：
这里，物品的数量是整数，可以用到约束条件：int（整数）
26种物品，给出单价，每种物品最多只能取1个，合计金额是1000：
这里，物品的数量是0或1，可以用到约束条件：bin（二进制）
