如何在Excel Solver中设置电缆选型优先级以优化成本

针对你遇到的Solver直接选用最高规格的问题，核心是要给4mmsq→6mmsq→10mmsq设置明确的优先级，以下是三种可行的实现方式：

一、分阶段分步求解（最易操作） #

完全贴合你的需求逻辑，分三步逐步放宽规格选择：

1. 优先锁定4mmsq
   • 先将所有行的4mmsq设为1，其余为0，计算平均电压降。如果满足≤3%，直接用此方案，无需再求解。
   • 若不满足，打开Solver：
     • 目标：最小化总成本（或直接最大化选中4mmsq的行数）
     • 约束：
       • 每行的三个规格单元格之和=1（仅选一种）
       • 平均电压降≤3%
       • 所有行仅允许选4mmsq或6mmsq（给10mmsq的单元格添加约束=0）
     • 求解后，若电压降达标，结束；若仍不满足，进入下一步。
2. 启用10mmsq补充
   • 保持目标为最小化总成本，放开约束：允许行选择10mmsq，重新求解，直到电压降满足要求。

二、调整目标函数，加入优先级权重 #

通过给不同规格的选中次数设置远大于成本差异的权重，让Solver优先选择低规格：

• 假设：
  • 用X4表示选中4mmsq的行数，X6表示6mmsq，X10表示10mmsq
  • 4mmsq单根成本为C4，6为C6，10为C10（C4<C6<C10）
• 目标函数设置为：

  Min = (C4*X4 + C6*X6 + C10*X10) + 100*(C10-C4)*X10 + 50*(C6-C4)*X6
  

  这里的100*(C10-C4)和50*(C6-C4)是优先级权重，数值要远大于单根成本差，确保Solver会优先减少X10和X6的数量，也就是优先选X4。
• 约束条件：
  • 每行仅选一种规格（对应单元格之和=1）
  • 平均电压降≤3%
  • X4+X6+X10=总行数

三、二进制变量+多阶段优先级约束 #

给每行的三个规格设置二进制变量（0=未选，1=选中），比如A_i=1表示第i行选4mmsq，B_i=1表示选6，C_i=1表示选10：

1. 第一阶段：最大化4mmsq的使用量
   • 目标：Max SUM(A_i)
   • 约束：
     • A_i + B_i + C_i = 1（所有行i）
     • 平均电压降≤3%
   • 记录此阶段的SUM(A_i)最大值为MaxA
2. 第二阶段：最大化6mmsq的使用量
   • 目标：Max SUM(B_i)
   • 约束：
     • A_i + B_i + C_i = 1
     • 平均电压降≤3%
     • SUM(A_i) = MaxA
   • 记录此阶段的SUM(B_i)最大值为MaxB
3. 第三阶段：最小化总成本
   • 目标：Min (C4*SUM(A_i) + C6*SUM(B_i) + C10*SUM(C_i))
   • 约束：
     • A_i + B_i + C_i = 1
     • 平均电压降≤3%
     • SUM(A_i) = MaxA
     • SUM(B_i) = MaxB

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