Excel求解器使用字符串 [英] Excel Solver Using Strings

问题描述

现在我有一个包含足球运动员名单的电子表格，其中每个都有本周的分配薪水和预计总分。

我的目标是使用Solver或其他一些方法来确定玩家的最佳组合以最大化投影点总数

在这个例子中，我有4个单独的玩家列表，如下所示：

QB ：玩家A，玩家B，玩家C ...玩家N
RB：玩家a，玩家b，玩家c ...玩家N
WR：玩家X，玩家Y，玩家Z ...玩家N
TE：玩家x，玩家y，玩家z ...玩家N

我需要最好的组合，包括2个QB，2个RB，2个WR，1 TE和2Flex，这意味着任何RB / WR / TE。

我已经尝试使用Solver来最大化投影点总数，但是在这种情况下，变量字段将是播放器的名称，它似乎是变量字幕d需要一个数字，而不是字符串列表。

任何想法？

解决方案

我最喜欢的问题：）

这是模型设置：

< img src =https://i.stack.imgur.com/ekTEk.pngalt =enter image description here>

• 顶部表格显示 决策变量 ： = 1 如果玩家 i = A，B ，...，N 列表 L = QB，..，TE 被选中， = 0 否则。


• 列 R 中的条目（最上面的表格）是每一行的总和。这些必须用列 T 中的数字约束。单元格 R7 是玩家的总和，应该是9：2灵活和7根据个人列表要求。


• 中间表显示薪水（随机产生在50,000到150,000之间）。 薪金之和公式是 = SUMPRODUCT（C11：P14，C3：P6）。这里的想法是只考虑选择的玩家的薪水。这个 SUMPRODUCT 应该受到预算的约束，这是在单元格 T14 中。对于我的实验，我把它等于所有工资总额的 80％。


• 目标：底表显示每个玩家的投影点数。单元格 R22 中的公式是 = SUMPRODUCT（C19：P22，C3：P6）（与上述工资相同的逻辑）。这是要最大化的值。

求解器模型如下所示：

我建议选择整数优化设置为零（0），然后将选项

结果：

Solver设法找到最佳解决方案。问题真的很小，很快。解决方案最多可以处理200个变量和100个约束，对于大型问题，您将需要（商业）扩展版本：

当然，您可以只订购真实的玩家名称，使其适合设置。例如，如果您按字母顺序排列每个列表的玩家，那么（玩家A，QB）=团队QB的首位玩家等。

我希望这有帮助！让我知道，如果你想让我上传你的文件。

最佳，
约阿尼斯

I'm going to try to explain this the best that I can.

Right now I have a spreadsheet with a list of football players, each of which has an assigned salary and projected point total for the week.

My goal is to use Solver or some other method to determine the best combination of players to maximize the projected point total while staying under a salary cap.

In this example I have 4 separate player lists, like this:

QB: Player A, Player B, Player C...Player N RB: Player a, Player b, Player c...Player N WR: Player X, Player Y, Player Z...Player N TE: Player x, Player y, Player z...Player N

I need the best combination that includes 2 QBs, 2 RBs, 2 WRs, 1 TE, and 2 "Flex", which means any of RB/WR/TE.

I have tried using Solver to maximize the projected point total, but the variable fields in this case would be the Player's Names and it seems like the variable field needs to be a number, not a list of strings.

Any ideas?

解决方案

My favorite kind of question :)

Here is the model setup:

• Top table shows the decision variables: = 1 if player i = A, B, ..., N of list L = QB, .., TE is selected, =0 otherwise.
• Entries in column R, (next to the top table) are the sums of each row. These must be constrained with the numbers in column T. Cell R7 is the total sum of players, which should be 9: 2 flexible and 7 as per the individual list requirements.
• Middle table shows the salaries (randomly generated between 50,000 and 150,000). The Sum of Salaries formula is =SUMPRODUCT(C11:P14,C3:P6). The idea here is that only the salaries of players that are selected are taken into account. This SUMPRODUCT should be constrained with the budget, which is in cell T14. For my experiment, I put it equal to 80% of the total sum of all salaries.
• Objective: Bottom table shows the projected points for each player. The formula in cell R22 is =SUMPRODUCT(C19:P22,C3:P6) (same logic as with salaries above). This is the value to be maximized.

Solver Model shown below:

I suggest selecting Simplex LP and going to Options and setting the Integer Optimality to zero (0).

Result:

Solver manages to find an optimal solution. The problem is really small and it is very quick. Solver works with up to 200 variables and 100 constraints, for large problems you will need the (commercial) extended version:

Of course, you can just order the real player names so that they fit this setting. For example, if you sort the players of each list alphabetically, then (Player A, QB) = first player of team QB, etc.

I hope this helps! Let me know if you would like me to upload the file for you.

Best, Ioannis

这篇关于Excel求解器使用字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！