如何在R中复制Excel求解器 [英] How to replicate excel solver in R

问题描述

我使用excel求解器来解决优化问题，并且试图在R中复制它.

I used excel solver to solve an optimization problem, and I am trying to replicate it in R.

我发现了很多软件包，例如optim，ROI等，但是似乎所有软件包都只将矢量作为要优化的对象，并允许变量采用任何连续值.就我而言，我有一个约束矩阵也需要满足，并且我的变量只能采用二进制值.

I found many packages like optim, ROI etc., but it seems all of them only take a vector as the object to optimize and allow the variables to take any continuous value. In my case, I have a constraint matrix that also needs to be satisfied and my variables can only take binary values.

这是我要解决的问题:

A-D是机器，1-3是任务，第一矩阵中的数字是使用X机器执行Y任务所生成的值.约束是:A-D可以执行并且只能执行一项任务(无法拆分)；每个任务都可以完成，只能在一台机器上完成.

A-D are machines, 1-3 are tasks and the number in the first matrix is the value generated by using X machine to do Y task. The constraints are: A-D can do and only can do one task (cannot split); each task can be worked and only be worked by one machine.

这是我正在使用的代码:

Here is the code I am using:

par = rep(c(0,1),6)

mat <- matrix(c(9,10,11,4,5,10,1,3,5,7,5,4), nrow = 3)

fr <- function(x) {  
  y= matrix(x,nrow = 4)
  sum(mat %*% y)
}

a = optim(par, fr)

一些问题:如何优化最大值，似乎此函数默认优化最小值?如何在其中添加约束?如何限制二进制变量?

Some questions: How can I optimize maximum, seems this function default optimize minimum? How can I add constraints into it? How can I limit to binary variables?

推荐答案

您需要为目标函数和约束矩阵构造一个向量，最后使用R LP求解器之一进行求解:

You need to construct a vector for the objective function and a constraint matrix, finally solving with one of the R LP solvers:

library(lpSolve)
costs <- matrix(c(9, 10, 11, 4, 5, 10, 1, 3, 5, 7, 5, 4), nrow=3)
nr <- nrow(costs)
nc <- ncol(costs)
columns <- t(sapply(1:nc, function(x) rep(c(0, 1, 0), c(nr*(x-1), nr, nr*(nc-x)))))
rows <- t(sapply(1:nr, function(x) rep(rep(c(0, 1, 0), c(x-1, 1, nr-x)), nc)))
mod <- lp("max", as.vector(costs), rbind(columns, rows), "<=", rep(1, nr+nc), binary.vec=rep(TRUE, nr*nc))

现在您可以获取解决方案和目标函数:

Now you can grab the solution and the objective function:

mod$objval
# [1] 27
matrix(mod$solution, nrow=nr)
#      [,1] [,2] [,3] [,4]
# [1,]    0    0    0    1
# [2,]    1    0    0    0
# [3,]    0    1    0    0

请注意，像optim这样的函数不太适合此问题，这是因为它们没有考虑约束矩阵，也因为它们不能限制为二进制变量值.

Note that functions like optim are not well suited for this problem both because they don't consider matrices of constraints and also because they cannot limit to binary variable values.

这篇关于如何在R中复制Excel求解器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！