Excel SOLVER优化问题：如何基于预设约束实现单元格值最小化及选值重排

问题本质拆解 #

你的目标等价于最大化选中的预设约束值的总和——因为最终总和是「所有常量值的总和」减去「选中的约束值的总和」，常量值总和是固定值，要让结果最小，就得让选中的约束值总和尽可能大。核心是从预设约束列表里选值、重新配对常量值，满足这个优化目标。

方法1：Excel规划求解（适合非编程场景） #

1. 数据准备
   • 列A：输入全部常量值
   • 列B：留空，用于存放最终配对的预设约束值
   • 列C：输入所有预设约束值（作为可选值池）
2. 设置目标公式
   • 在任意空白单元格（比如D1）输入：=SUM(A:A)-SUM(B:B)，将这个单元格设为规划求解的目标单元格，目标类型选「最小值」
3. 添加约束条件
   • 列B的每个单元格值必须属于列C的预设值集合
   • 列C的每个值只能被选一次（如果要求不重复选值）
4. 运行求解
   • 打开「数据」选项卡的「规划求解」（无此功能需先加载「规划求解加载项」），配置完成后点击求解，即可得到最优的约束值配对排序

方法2：Python代码实现（适合编程场景） #

用整数规划库pulp实现，直接输出最优配对结果：

import pulp

# 替换成你的实际数据
constant_values = [10, 15, 20, 25]
preset_constraints = [8, 12, 18, 22]

# 初始化优化问题，目标为最小化总和
prob = pulp.LpProblem("Minimize_Target_Sum", pulp.LpMinimize)

# 创建二进制变量：x[i][j] = 1 表示第i个常量值配对第j个约束值
x = pulp.LpVariable.dicts(
    "Pair",
    [(i, j) for i in range(len(constant_values)) for j in range(len(preset_constraints))],
    cat="Binary"
)

# 定义目标函数：Σ(常量值[i] - 约束值[j]) * 配对标记
prob += pulp.lpSum(
    [(constant_values[i] - preset_constraints[j]) * x[i][j]
     for i in range(len(constant_values))
     for j in range(len(preset_constraints))]
)

# 约束1：每个常量值必须配对一个约束值
for i in range(len(constant_values)):
    prob += pulp.lpSum([x[i][j] for j in range(len(preset_constraints))]) == 1

# 约束2：每个约束值只能被配对一次（若允许重复则删除此约束）
for j in range(len(preset_constraints)):
    prob += pulp.lpSum([x[i][j] for i in range(len(constant_values))]) == 1

# 执行求解
prob.solve()

# 输出结果
print("最优配对方案：")
for i in range(len(constant_values)):
    for j in range(len(preset_constraints)):
        if pulp.value(x[i][j]) == 1:
            print(f"常量值 {constant_values[i]} → 预设约束值 {preset_constraints[j]}")
print(f"最小化后的目标总和：{pulp.value(prob.objective)}")

适配不同场景的调整 #

• 若预设约束值数量多于常量值：将约束2改为<=1，同时新增约束确保选中的约束值数量等于常量值数量
• 若允许重复使用预设约束值：直接删除约束2即可

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