如何将Excel Solver的分组小时Score最大化方案迁移至R？

嘿，我来帮你把这个Excel Solver的优化问题搬到R里！这是个典型的0-1整数规划问题，咱们一步步拆解搞定它。

问题明确 #

你的需求是：为每个小时挑选5个数据行，最大化Score的总和，同时满足两个约束：

• 同一group的行最多被选中2次
• 同一hour的行最多被选中5次

准备工作：加载数据与工具包 #

首先咱们先把数据加载好，然后用lpSolve包来做规划求解——这是R里处理线性/整数规划的常用工具，上手很简单。

# 加载你提供的数据
df1 <- structure(list(group = structure(c(1L, 1L, 3L, 3L, 5L, 5L, 7L, 7L, 9L, 9L, 11L, 11L, 13L, 13L, 15L, 15L, 17L, 17L, 2L, 2L, 4L, 4L, 6L, 6L, 8L, 8L, 10L, 10L, 12L, 12L, 14L, 14L, 16L, 16L, 18L, 18L, 1L, 3L, 5L, 7L, 9L, 11L, 13L, 15L, 17L, 2L, 4L, 6L, 8L, 10L, 12L, 14L, 16L, 18L), .Label = c("a", "aa", "b", "bb", "c", "cc", "d", "dd", "e", "ee", "f", "ff", "g", "gg", "h", "hh", "j", "jj"), class = "factor"), hour = c(1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), Score = c(1000L, 1231L, 12312L, 6438L, 3033L, 6535L, 4283L, 4957L, 9507L, 5115L, 1914L, 9278L, 5362L, 8408L, 4640L, 4296L, 8115L, 1143L, 3242L, 3695L, 3908L, 2540L, 6438L, 2170L, 6497L, 3327L, 5067L, 6614L, 5140L, 9858L, 8061L, 2316L, 7848L, 3525L, 8259L, 9014L, 31100L, 111100L, 87200L, 60700L, 50600L, 74300L, 97400L, 28900L, 25900L, 55600L, 38200L, 58500L, 51300L, 84000L, 83700L, 74200L, 19700L, 62800L)), class = "data.frame", row.names = c(NA, -54L))

# 安装并加载lpSolve包
if (!require(lpSolve)) {
  install.packages("lpSolve")
  library(lpSolve)
}

构建优化模型 #

咱们把问题转化为整数规划的标准形式：

• 决策变量：每个数据行对应一个0-1变量（1代表选中该行，0代表不选）
• 目标函数：最大化选中行的Score总和
• 约束条件：
  1. 每个hour对应的变量之和 ≤5（每小时最多选5个）
  2. 每个group对应的变量之和 ≤2（每组最多选2个）

1. 定义目标函数系数 #

目标函数的系数就是每行的Score值，直接提取即可：

obj <- df1$Score

2. 构建约束矩阵 #

我们需要分别创建小时约束和组约束的矩阵，再合并到一起。

小时约束矩阵

对每个小时，生成一行标记：该行属于当前小时则为1，否则为0：

hour_constraints <- lapply(unique(df1$hour), function(h) {
  as.integer(df1$hour == h)
})
hour_constraints <- do.call(rbind, hour_constraints)

组约束矩阵

对每个组，生成一行标记：该行属于当前组则为1，否则为0：

group_constraints <- lapply(unique(df1$group), function(g) {
  as.integer(df1$group == g)
})
group_constraints <- do.call(rbind, group_constraints)

合并所有约束

把两种约束矩阵合并，同时定义约束方向和右侧的限制值：

# 合并约束矩阵
constraint_matrix <- rbind(hour_constraints, group_constraints)

# 约束方向：所有约束都是"小于等于"
constraint_dir <- c(rep("<=", length(unique(df1$hour))), rep("<=", length(unique(df1$group))))

# 约束右侧值：小时约束是5，组约束是2
constraint_rhs <- c(rep(5, length(unique(df1$hour))), rep(2, length(unique(df1$group))))

运行求解 #

用lp()函数执行0-1整数规划求解，注意设置all.bin=TRUE来指定变量是0或1：

solution <- lp(
  direction = "max",  # 目标是最大化
  objective.in = obj,
  const.mat = constraint_matrix,
  const.dir = constraint_dir,
  const.rhs = constraint_rhs,
  all.bin = TRUE  # 所有变量为0或1
)

查看与验证结果 #

求解完成后，咱们来看看结果是否符合预期：

# 查看求解状态（0表示求解成功）
cat("求解状态：", solution$status, "\n")

# 查看最大Score总和
cat("最大Score总和：", solution$objval, "\n")

# 提取选中的行
selected_rows <- df1[solution$solution == 1, ]
cat("\n选中的行：\n")
print(selected_rows)

# 验证约束是否满足
cat("\n每小时选中数量：\n")
print(table(selected_rows$hour))

cat("\n每组选中数量：\n")
print(table(selected_rows$group))

这样就完成了从Excel Solver到R的迁移！如果后续需要更复杂的建模，也可以试试ompr或ROI这类更现代化的规划包，但lpSolve对于这个问题已经完全够用啦。

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