Google电子表格与SQL查询-寻找最佳组合

Question

我有一个谷歌电子表格，我的游戏信息。它包含2张纸-一张用于怪物信息，另一张用于团队。

怪物信息表包含怪物的攻击值、防御值和法力消耗。它就像一个我可以召唤的怪物数据库。

小组工作表做了以下工作：

1. 请求我现在拥有的魔法量。
2. 计算最多5个怪物的列表，我可以召唤(它可以小于5个)。
3. 每个怪物都有自己的法力消耗，因此总法力消耗不能超过我在第1点给予的魔法量。
4. 列表应该给我一支拥有最高组合攻击价值的球队。不管有多少怪物被召唤。但是每个怪物不能被召唤两次。

我一直在考虑使用query()函数，以便能够使用SQL语句。(这样我就可以直接检索列表)

Sample: Monster Info
            A           B          C          D
1           Monster     Attack     Defense    Cost
2           MonA        1200       1200       35
3           MonB        1400       1300       50
... ...

Sample: Team
            A           B          C          D
1           Mana        120        
2
3                Attack Team
4           Monster     Attack     Cost      Total Attack
5           MonB        1400       50        1400
6           MonA        1200       35        2600
7           ... ...

我在“团队”单子里有这些公式

• A5：=查询(‘Monster Info'!$A$:$D，“按query限制5选择A、B、D顺序”)
• B5：=继续(A5，1，2)
• C5：=继续(A5，1，3)
• D5：=C5
• A6：=继续(A5，2，1)
• B6：=继续(A5，2，2)
• C6：=继续(A5，2，3)
• D6：=D5+C6

这只会得到5个最好的攻击怪物，而不考虑魔法消耗。如何做到既考虑攻击值又考虑法力成本值？下面的示例显示了另一个问题：

Example: (simplified version, without defense value etc)
Monster        Attack     Cost
MonA           1400       50
MonB           1200       35
MonC           1100       30
MonD           900        25
MonE           500        20
MonF           400        15
MonG           350        10
MonH           250        5

如果我有160个法力，那么最明显的是A+B+C+D+E (5100攻击)。

如果我有150法力，它就变成A+B+C+D+G (4950攻击)。

如果我有140个法力，它就变成A+B+C+D (4600攻击)。

如果我有130个法力，它会变成B+C+D+E+F (4100攻击使用125个法力)或A+B+C+F (4100攻击使用所有130个法力)。

如果我有120个法力，它就变成B+C+D+E+G (4050攻击)。

如果我有110个法力，它就变成B+C+D+F+H (3850攻击)。

正如您所看到的，结果中并没有真正的模式。

有专家愿意分享他们对此的见解吗？

Answer 1

我已经处理这个问题一个小时了，我在这里只有一个解决办法。您的问题似乎是一个标准的线性规划任务，应该可以很容易地解决“解决”软件。谷歌电子表格中曾经有一个所谓的“解决程序”，但不幸的是，它被从最新版本中删除了。如果你不坚持谷歌解决方案，你应该尝试在一个解决方案支持的电子表格管理软件。

我尝试了MS (它有一个解决方案插件，安装指南：http://office.microsoft.com/en-001/excel-help/load-the-solver-add-in-HP010342660.aspx)。

在运行解决程序之前，您应该准备一些原始数据集，其中包含辅助列和单元格。

1. 在“成本”列旁边添加一个新列(假设它是“D”列)，并在它下面放置每一行0或1。该列将告诉您攻击团队是否选择了怪物。
2. 增加两列(分别为“E”和"F“)。这些栏目将分别是攻击和成本的产物。因此，您应该编写一个函数到E2单元格：=b2*d2，以及F2单元格：=c2*d2。这样，如果选择了一个怪物(请记住，D列告诉它)，相应的E和F单元将是非零值，否则它们将是0。
3. 在最后一行下创建一个SUM行，并分别为D、E、F列创建一个汇总函数。因此，在我的电子表格中，D10单元格获得它的值，如：=sum(d2:d9)，依此类推。

我创建了一个电子表格来显示以下步骤：yJ44MysK9hMsj0ijPEn18/edit?usp=sharing

在启动解决程序之前，请记住将此工作表复制到MS Office工作表中。

现在，您已经准备好启动解决方案了。(数据菜单，中的解决程序)。您可以在这里看到使用Solver：https://www.youtube.com/watch?v=Oyc0k9kiD7o的视频

这并不像看上去那么难，但在本例中，我将描述在哪里写什么：

1. Set Objective：您应该选择"E10“单元格，因为它代表了所有攻击点的总和。
2. 检查“最大”无线电按钮，因为我们想要最大限度的价值的攻击。
3. 通过更改可变单元格：选择"d2:d9“间隔，因为这些单元格表示是否选择了怪物。求解者将尝试调整这些值(0或1)，以最大化和攻击。
4. 受约束：在这里我们应该添加一些约束。单击Add按钮，然后：

- First we should ensure that d2:d9 are all binary values. So "Cell reference" should be "d2:d9" and from the dropdown menu, select "bin" as binary.
- Another constraint should be that the sum of the selected monsters should not exceed 5. So select the cell where the sum of the selected monsters is represented (D10) and add "<=" and the value "5"
- Finally we cannot use more manna that we have, so select the cell in which you store the sum of used manna (F2), and "<=", and add the  whole amount of manna we can spend  in my case it's in the I2 cell).

​

好了。它应该能起作用，至少在我的情况下是起作用的。

希望能帮上忙。