EXCEL原生演化算法功能

Excel 里的规划求解插件中提供了一种求解方法，模拟大自然"物竞天择、适者生存"的方式来找最优解。（零代码零插件）

本文将介绍：

1. 算法原理、应用场景；

2. 介绍界面功能、原理以及推荐参数（可速查）；

3. 结合具体优化示例，演示调用方法。

您的点赞是对我最大的支持，感谢感谢~

演化算法（遗传算法GA为例）

遗传算法，一种模拟生物进化“优胜劣汰”规律的全局优化算法。

核心逻辑可以参考本号的过往文章Matlab遗传算法-GA函数简介II。

这种算法可以自动地迭代多个变量，类似于“让一群猴子找山底”，可贵的是它不要求函数平滑可导。

在Excel中调用演化算法，不需要代码，也不需要额外下载插件，适用于需要快速迭代的场景，或者直接套用到具体的业务表格中。

本质上，就是在Excel中将需要迭代的业务场景翻译为：目标+可变单元格+约束。然后再由算法一遍遍筛选出最优解。

设想一下，

  在人员排班场景里，需要调整员工搭配，找到满足工时和成本最低的组合；

  在仓库选址时，希望通过迭代划定仓库可选的区域以确保运输距离最优；

  考虑生产配料问题时，迭代不同营养成分达标且性价比最高的配方；

  计算资源分配场景中，通过设定最大运行时间和收敛容差，在有限预算内不断迭代，直到产出接近理论最大值。

解锁EXCEL的“演化”算法（规划求解）

Excel内置的规划求解（Solver）

First：加载规划求解插件

1. 1. 点击 文件 -> 选项
2. 2. 选择加载项
3. 3. 在底部“管理”下拉菜单中选择Excel 加载项，点击转到。
4. 

1. 4. 在弹出的窗口中勾选规划求解加载项。
2. 

1. 5. 点击确定后，数据选项卡最右侧就会出现规划求解按钮。

界面功能以及“演化”求解

1. 设置目标

• • 位置：界面顶部输入框。
• • 含义：填入代表“得分”的单元格引用。
• • 目标：
• • 最大值：最大化目标单元格
• • 最小值：最小化目标单元格
• • 目标值：接近一个固定的数值

2. 可变单元格

• • 含义：设置可以自由变动的单元格，作为迭代的输入量。

3. 遵守约束

• • 位置：中间大片空白列表区域以及右侧添加按钮。
• • 含义：为输入量设置约束

4. 使无约束变量为非负数

• • 位置：左下角的复选框
• • 含义：勾选后，所有未设置“约束”的变量强制限制为≥0。

5. 选择求解方法

• • 位置：下拉菜单
• • 选项：算法参数，如可调整 GA 的内部参数，如种群大小、变异率、收敛阈值等。（一般情况下，保持默认即可满足大部分需求。）

6. 执行求解

• • 设置完毕后，点击右下角的“求解”按钮
• 

• • Excel 会弹出一个结果报告窗口，选择 保留规划求解的解，点击确定，“可变单元格”里就会填充上算出的最优数值，否则还原初值。
• • 右侧“报告”生成区求解完成后，Excel 可以将底层数据转化为三类分析报表，双击工作表即可查看：
• • 运算结果报告：
• 

• • 内容：列出最终的目标单元格数值、可变单元格的最终取值。
• • 亮点：包含一个“规划求解敏感性报告”的链接（仅限线性问题），用于分析约束条件的影子价格。
• • 运算性报告：
• • 内容：展示求解过程的微观数据，如每次迭代的目标值变化曲线、变量取值的波动范围。
• • 用途：用于诊断算法是否陷入停滞或震荡。
• • 极限值报告：
• • 内容：分析当目标函数系数发生微小变动时，最优解是如何随之变化的。
• • 用途：进行敏感性分析和决策风险预估。
• • 保存方案（进阶功能）：
• 

• • 当存在多个局部最优解或需要进行情景对比时，此功能可将当前的变量状态保存为一个“方案”。后续可在：“数据”->“模拟分析”->“方案管理器”中一键切换对比。
• • 制作报告大纲：
• • 将结果和报告生成为一个结构化的 Excel 大纲，便于打印或提交正式报告。
• • 返回“规划求解参数”对话框：
• • 勾选后点击确定，会重新弹出参数设置窗口。这在需要微调约束条件并再次求解时非常高效，省去了重新点击菜单的步骤。

“演化“算法以及参数设置

演化算法是一种算法家族，Excel内置的算法是一种变体的遗传算法

具体可以参考 [solver.com帮助文档]

https://www.solver.com/genetic-evolutionary-introduction

算法设置

在”选项“设置中控制演化算法（默认参数一般够用）

1. 1. 通用设置
2. 
   A 设置计算资源限制• 最大时间（秒） & 迭代次数：含义：止损线。无论问题是否有解，程序强制停止。• 最大子问题数目 & 最大可行解数目：含义：用于带有整数约束的混合整数规划。• 约束精确度：含义：处理浮点数时的精度误差。用来判断“约束条件是否被满足了”的容忍度。例如要求 A+B=C，只要 Abs(A+B-C) < 0.000001，就算等式成立。B 整数约束相关设置• 忽略整数约束：含义：勾选该项，求解器就会无视整数要求。通常是为了进行对比测试。• 整数最优性 (%)：含义：决定在多大程度上追求整数的绝对完美。数值越低，计算速度越快，但整数解的精度可能会低。
3. 2. 算法参数
4. 
   • 收敛：含义：耐心。当种群中优秀个体的目标函数值已经非常接近，它们之间的相对差异小于这个数值时，提前停止。• 突变速率：含义：演化过程中发生基因突变的概率。决定了候选解的各个数值发生随机大幅跳动的频率。速率太高（如 >0.2），种群会变得混乱；速率太低（如 <0.01），容易陷入局部最优解。• 总体大小：含义：参与进化的候选解的总数量（即“种群规模”）。越大越好，但算力消耗呈线性增长。复杂问题可以尝试增大到 200 或 500，以增加找到全局最优解的概率；简单问题则无需改动。• 随机种子：含义：随机数生成器的初始密码。设为 0（默认）时，每次点击求解都是全新的随机探索，结果可能会有微小差异。如果希望每次得到的结果一模一样，可手动输入一个正整数。• 无改进的最大时间：含义：如果在连续的一段时间内，算法连一个更好的解都没找到，它会主动放弃并停止。这是一种动态的“早停机制”，能在问题无解或陷入僵局时快速结束浪费时间的计算。• 需要使用变量的界限：含义：演化算法非常依赖“边界”。勾选此项后，如果漏掉了给某些变量设置上下限，求解器会直接报错提示。

场景自查

最后，附一下场景自查表，在某些简单场景可以通过其他模型更高效地解决问题。

优化范例

下面是Excel中调用算法的操作演示。

在Excel中定义输入量和输出量，如果所示A2到E2单元格都是输入量，G2单元格为输出量。

优化目标是调整输入量的值，让输出量达到目标值400。

G2==A2+B2+C2*D2-E2

在规划求解界面中，定义输入输出

然后，在下方约束界面中定义约束

设置以后，选择优化的目标以及算法

点击“求解”得到优化结果。