如何在Microsoft 365 Excel中优化易货系统的贸易路线？

一、基础场景（一州一专精/需求产品）的Excel Solver设置 #

1. 数据准备 #

先整理核心数据到Excel表：

• 州信息表：列包含州ID、专精产品、折扣售价、需求产品、溢价收购价
• 盈利系数矩阵：提前计算任意两州间的套利收益，(i,j)单元格值 = 若州j需求产品为州i专精产品，则取值为「州j收购价 - 州i售价」，否则为0（无套利空间）

2. 定义变量 #

推荐用邻接矩阵（比行程序列更适配Solver规划）：创建30×30的矩阵区域（比如B1:AE30），每个单元格代表从对应行的州到对应列的州的行程次数，取值为非负整数（允许重复访问）。

3. 目标函数 #

计算总盈利，直接用矩阵乘积：

=SUMPRODUCT(邻接矩阵区域, 盈利系数矩阵区域)

该公式自动累加所有有效行程的套利收益。

4. 约束条件 #

• 总行程次数限制：SUM(邻接矩阵区域) ≤ 20
• 变量类型约束：邻接矩阵所有单元格为非负整数（避免出现小数次行程）
• 无效行程约束：可通过盈利系数矩阵自动过滤（无效行程收益为0，Solver会自动忽略），无需额外设置

5. Solver参数配置 #

• 目标：选择总盈利单元格，设置为「最大化」
• 可变单元格：选中邻接矩阵的全部单元格
• 约束：
  • 添加SUM(邻接矩阵区域) ≤ 20
  • 添加邻接矩阵单元格的「整数」「≥0」约束
• 求解方法：选择整数规划（Integer Programming），点击求解即可

二、扩展到一州多产品的复杂场景 #

完全可以扩展，只需调整以下核心部分：

1. 数据结构升级 #

• 州信息表拆分：新增专精产品明细（含对应折扣价）、需求产品明细（含对应收购价）两表，关联州ID
• 新增库存跟踪区：用20行×25列的区域记录每段行程后的各产品库存数量

2. 变量与目标函数调整 #

• 变量新增：在邻接矩阵基础上，添加「每段行程中购买/出售的产品数量」变量（非负整数）
• 目标函数：改为所有交易的「（收购价-成本价）×交易数量」总和，需结合库存流转逻辑计算

3. 约束增强 #

• 库存平衡约束：每段行程结束后，某产品库存 = 上一段行程库存 + 本次购买数量 - 本次出售数量
• 交易匹配约束：在某州只能购买其专精产品、出售其需求产品
• 行程次数约束仍保留≤20

4. Solver适配 #

此时问题属于混合整数线性规划（MILP），原生Excel Solver可能性能不足或求解失败，建议启用免费扩展OpenSolver，它能处理更复杂的多变量组合优化问题。

三、原生Solver失败的常见排查点 #

• 约束冲突：检查是否误设了矛盾约束（比如强制要求访问所有州，但行程次数不足）
• 非线性函数：目标或约束中使用了IF等非线性函数，Solver无法处理，需替换为线性逻辑（比如用预计算的系数矩阵）
• 变量类型错误：未设置行程次数、交易数量为整数，导致Solver返回非可行解

内容的提问来源于stack exchange，提问作者Noodle