在Excel中使用变量优化列表 [英] Optimization of a list in Excel with Variables

问题描述

我有153个高尔夫球手的清单，其中列出了相关的薪水和平均得分.

I have a list of 153 golfers with associated salaries and average scores.

我想找到6个高尔夫球手的组合，这些组合可以优化平均得分并将工资保持在50,000美元以下.

I want to find the combination of 6 golfers that optimizes avg score and keeps salary under $50,000.

我尝试使用规划求解，但被卡住了！有人可以帮忙吗? :)

I've tried using Solver, but I am stuck! Can anyone help please? :)

推荐答案

说明与@ErwinKalvelagen建议的解决方案非常接近的解决方案.

Illustrating a solution that is pretty close to what @ErwinKalvelagen suggested.

1. A列是153名高尔夫球手的姓名
2. B列是高尔夫球手的薪水(由=RANDBETWEEN(50, 125)*100生成，先填写，然后复制/粘贴值)
3. C列是高尔夫球手的平均得分(由=RANDBETWEEN(70, 85)生成，先填写，然后复制/粘贴值)
4. D列为0或1，表示是否包括高尔夫球手.
5. 单元格F2是=SUMPRODUCT(B2:B154,D2:D154)
给出的总工资
6. 单元格G2是高尔夫球手的人数，由=SUM(D2:D154)
给出
7. 单元格H2是团队的平均得分，由=SUMPRODUCT(C2:C154,D2:D154)/G2
给出

1. Column A is the names of the 153 golfers
2. Column B is the golfers salaries (generated by =RANDBETWEEN(50, 125)*100, filled down, then Copy/Paste Values)
3. Column C is the golfers average scores (generated by =RANDBETWEEN(70, 85), filled down, then Copy/Paste Values)
4. Column D is a 0 or 1 to indicate if the golfer is included.
5. Cell F2 is the total salary, given by =SUMPRODUCT(B2:B154,D2:D154)
6. Cell G2 is the number of golfers, given by =SUM(D2:D154)
7. Cell H2 is the average score of the team, given by =SUMPRODUCT(C2:C154,D2:D154)/G2

在设置求解器之前，页面如下所示.

The page looks like this, before setting up Solver ...

规划求解设置如下...

The Solver setup looks like this ...

根据帮助，它说使用Evolutionary引擎解决非平滑问题.在选项"中，我需要将最长时间"从30改善到300(可能已经足够了).

According to the help, it says to use Evolutionary engine for non-smooth problems. In Options, I needed to increase the Maximum Time without improvement from 30 to 300 (60 may have been good enough).

花了几分钟才完成.它很快就达到了70的解决方案，但花了更多时间寻找更好的答案.

It took a couple of minutes for it to complete. It reached the solution of 70 fairly quickly, but spent more time looking for a better answer.

这是它想出的六个高尔夫球手.

And here are the six golfers it came up with.

在平均70岁的高尔夫球手中，它的薪水可能更低.

Of the golfers with an average of 70, it could have found a lower salary.

在单元格I2中添加了公式=F2+F2*(H2-70)，该公式本质上是通过将平均得分提高到70以上来惩罚薪水的...

In Cell I2 added the formula =F2+F2*(H2-70) which is essentially salary penalized by increases in average score above 70 ...

...并使用相同的求解器设置，除了最小化单元格I2而不是H2 ...

... and use the same Solver setup, except to minimize Cell I2 instead of H2 ...

这些是它选择的高尔夫球手...

and these are the golfers it chose ...

再次-似乎还有更好的解决方案.它可能选择了Name97而不是Name96.

Again - it looks like there is still a better solution. It could have picked Name97 instead of Name96.

这篇关于在Excel中使用变量优化列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！