如何分配权重以同时最大化逼近两个目标值？求R/Python/Excel方案

给定一组权重，需将每个权重分配至两个组中，使两组权重和分别尽可能逼近对应目标值。以下是R、Python、Excel三种实现方案：

R语言实现 #

利用整数规划将双目标转化为最小化两组偏差的加权和，使用lpSolve包实现：

# 安装并加载依赖包
install.packages("lpSolve")
library(lpSolve)

# 输入数据
weights <- c(4.528, 4.773, 4.253, 4.688, 4.21, 3.841, 4.005, 4.545, 3.825, 5.123, 4.757)
target1 <- 22.08
target2 <- 21.37
total_sum <- sum(weights)

# 构建目标函数：前11位为0-1分配变量，后两位为偏差变量，最小化偏差和
obj <- c(rep(0, length(weights)), 1, 1)

# 约束条件：控制两组和与目标值的偏差范围
constraints <- rbind(
  c(weights, -1, 0),        # 组1和 - 目标1 ≤ 偏差1
  c(-weights, -1, 0),       # 目标1 - 组1和 ≤ 偏差1
  c(-weights, 0, -1),       # 组2和 - 目标2 ≤ 偏差2
  c(weights, 0, -1)         # 目标2 - 组2和 ≤ 偏差2
)
const_dir <- c("<=", "<=", "<=", "<=")
const_rhs <- c(target1, -target1, target2 - total_sum, total_sum - target2)

# 变量类型：分配变量为二进制，偏差变量为非负实数
var_types <- c(rep("binary", length(weights)), "real", "real")

# 求解整数规划
lp_result <- lp("min", obj, constraints, const_dir, const_rhs, int.vec = 1:length(weights))

# 提取并输出结果
x <- lp_result$solution[1:length(weights)]
group1 <- weights[x == 1]
group2 <- weights[x == 0]
sum1 <- sum(group1)
sum2 <- sum(group2)

cat("组1权重：", paste(group1, collapse = ", "), "\n")
cat("组1和：", round(sum1, 3), "，与目标偏差：", round(abs(sum1 - target1), 3), "\n")
cat("组2权重：", paste(group2, collapse = ", "), "\n")
cat("组2和：", round(sum2, 3), "，与目标偏差：", round(abs(sum2 - target2), 3), "\n")

Python实现 #

使用pulp库构建整数规划模型，思路与R一致：

from pulp import LpProblem, LpVariable, LpMinimize, lpSum

# 输入数据
weights = [4.528, 4.773, 4.253, 4.688, 4.21, 3.841, 4.005, 4.545, 3.825, 5.123, 4.757]
target1 = 22.08
target2 = 21.37
total_sum = sum(weights)

# 创建最小化问题
prob = LpProblem("Weight_Allocation", LpMinimize)

# 定义变量：x_i为0-1分配变量，d1、d2为非负偏差变量
x = [LpVariable(f"x{i}", cat="Binary") for i in range(len(weights))]
d1 = LpVariable("d1", lowBound=0)
d2 = LpVariable("d2", lowBound=0)

# 目标函数：最小化两组偏差之和
prob += d1 + d2

# 添加约束条件
prob += lpSum([weights[i] * x[i] for i in range(len(weights))]) - target1 <= d1
prob += target1 - lpSum([weights[i] * x[i] for i in range(len(weights))]) <= d1
prob += (total_sum - lpSum([weights[i] * x[i] for i in range(len(weights))])) - target2 <= d2
prob += target2 - (total_sum - lpSum([weights[i] * x[i] for i in range(len(weights))])) <= d2

# 求解模型
prob.solve()

# 提取结果并输出
group1 = [weights[i] for i in range(len(weights)) if x[i].value() == 1]
group2 = [weights[i] for i in range(len(weights)) if x[i].value() == 0]
sum1 = sum(group1)
sum2 = sum(group2)

print(f"组1权重：{', '.join(map(str, group1))}")
print(f"组1和：{round(sum1, 3)}，与目标偏差：{round(abs(sum1 - target1), 3)}")
print(f"组2权重：{', '.join(map(str, group2))}")
print(f"组2和：{round(sum2, 3)}，与目标偏差：{round(abs(sum2 - target2), 3)}")

Excel实现 #

Excel Solver不支持原生双目标，可通过以下两种方法转化处理：

方法1：加权偏差求和法 #

1. 将权重列在A1:A11，目标值22.08和21.37分别放在C1和C2。
2. 在B1:B11输入0/1（1表示分到组1，0分到组2），作为决策变量。
3. 计算组1和：=SUMPRODUCT(A1:A11,B1:B11)（放在D1）；组2和：=SUM(A1:A11)-D1（放在D2）。
4. 计算偏差：=ABS(D1-C1)（E1），=ABS(D2-C2)（E2）；目标单元格E3设为=E1+E2（可根据优先级调整权重，如=0.6*E1+0.4*E2）。
5. 打开Solver：设置目标单元格为E3，选择最小化，可变单元格为B1:B11，添加约束B1:B11 为二进制。
6. 运行Solver得到分配结果。

方法2：优先级迭代法 #

若需优先满足某一目标：

1. 先用Solver最小化E1（组1偏差），得到初始解。
2. 添加约束E1<=当前最小偏差值，再用Solver最小化E2（组2偏差），得到在满足第一目标前提下的最优解。

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