Excel规划求解从数字集的子集中查找目标总和 [英] Excel Solver Finding a Target Sum from Subset of Number Set
问题描述
我想使用求解器从较大的子集中找到目标总和. (16个数字的10个数字子集).我对规划求解不是很熟悉，也不确定设置约束的最佳方法.
I want to use Solver to find a target sum from a subset of a larger set. (10 number subset of 16 numbers). I'm not very familiar with Solver and I'm unsure of the best way to set my constraints.
我在集合A，B，C，D，E中有5个类别的数字.这16个集合可能包括这些类别的不同组合，但是目标子集总数具有定义的构成: A，B，B，C，C，C，(B或C)，(B或C)，D，E
I have 5 categories of numbers within the set, A, B, C, D, E. The set of 16 may include different combinations of these categories, but the target subset total has a defined makeup: A, B, B, C, C, C, (B or C), (B or C),D, E
所以我的问题是，如何将这些规则引入求解器?
So my question is, how would I introduce these rules into Solver?
其次，我知道Solver提出了1个解决方案，有没有办法获得最接近目标的一系列解决方案?因此，如果我有一个不能完全产生目标的集合，我会看到3个最接近目标(正负)的解决方案吗?
Secondarily, I know Solver comes up with 1 solution, is there a way to get a range of solutions that are closest to the target? So, if I have a set that doesn't quite produce the target, I could see 3 solutions that are nearest the target (plus or minus)?
谢谢.
推荐答案
TL/DR -这是设置问题的一种方法.获得一系列接近"的答案超出了Solver的范围.如果您定义了其他搜索条件，则可以使用VBA进行操作. (编辑-添加了用于定义妆容的设置)
TL/DR - Here's a way to setup the problem you have. Getting a series of answers that are "close" is out of scope for Solver. You could do it with VBA, if you define additional criteria to search around. (Edit - added set up for defined makeup)
评论
似乎没有关于必须从每个类别中选择一个数字"或必须从类别X中选择一个数字"的标准.相反，您有一堆数字，每个数字都属于一个类别，但是您想要查找提供一定总数的数字组合-然后再调查类别.
It seems there is no criteria about "a number must be selected from each category", or "a number must be selected from category X". Rather, you have a bunch of numbers, each belongs to a category, but you want to find the combination of numbers that provide a certain total - then investigate the categories afterward.
初始问题设置
根据您在评论中提供的信息，我整理了以下内容...
From the information you provided in your comment, I put together the following ...
A列(类别)和B列(值)是您提供的信息. C列(选定)仅为0或1.D列(结果)是向下填充的乘法=B2*C2.
Column A (Category) and Column B (Value) is the information you provided. Column C (Selected) is just 0 or 1. Column D (Result) is the multiplication =B2*C2 filled down.
F2是D列的总和.G2是该总和的目标值(如您所提供的). H2是计算=(F2-G2)^2的平方误差.
F2 is the sum of Column D. G2 is the target value for this sum (as you provided). H2 is the squared error of the calculation =(F2-G2)^2.
这是求解器设置...
This is the solver setup ...
- 设置目标:设置为
$H$2-计算的平方误差 - 收件人:设置为最小值".您可以选择Value Of:0，但如果没有确切答案，则可能会失败.
- 通过更改可变单元格:设置为
$C$2:$C$17或列C. - 受约束:包括
$C$2:$C$17 = binary.这会强制将值设为0或1. - 选择一种求解方法:设置为Evolutionary. GRG Nonlinear有时可以解决此类问题，但所需时间比Evolutionary长得多.
- Set Objective: is set to
$H$2- the squared error of the calculation - To: is set to Min. You could choose Value Of: 0, but if there is no exact answer, it may fail.
- By Changing Variable Cells: is set to
$C$2:$C$17or Column C. - Subject to the Constraints: includes
$C$2:$C$17 = binary. This forces the values to be either 0 or 1. - Select a Solving Method: is set to Evolutionary. GRG Nonlinear can sometimes solve this type of problem, but it takes much longer than Evolutionary.
以下是结果-与您报告的结果不同.当我强制您的结果时，我得到CalcTotal的106.61(可能是某个地方的转录错误)...
Below is the result - different from what you reported. When I force your result, I get 106.61 for CalcTotal (perhaps a transcription error somewhere) ...
查找临近的结果
Solver是优化程序，因此它将找到最适合您目标的答案".
Solver is an optimizer, so it will find "the answer" that best fits your objective.
为了让它为您提供不同的答案，您需要为它提供不同的目标.
In order for it to provide you with different answers, you need to provide it with different objectives.
例如，您可能需要强制使用C1的解决方案，但仍然最接近您的目标.设置可能看起来像这样...
For example, you may want to force a solution that uses C1, but is still closest to your objective. The setup might look like this...
...使用此解决方案...
... with this solution ...
在该示例中，我从通过更改变量单元格:"和受约束对象:"字段中排除了$C$10.完成同一件事的另一种方法是保持原始字段的值不变，但增加$C$10 = 1的附加约束.
In that example, I excluded $C$10 from the "By Changing Variable Cells:" and from the "Subject To Constraints:" fields. Another way to accomplish the same thing would have been to keep the original field values unchanged, but to add an additional constraint of $C$10 = 1.
或者，作为另一个示例，也许您想要一个从类别B中精确选择两个值的解决方案.您可以修改设置以包括从每个类别中选择的值之和，并添加约束.这是设置...
Or, as another example, perhaps you want a solution that chooses exactly two values from Category B. You could modify the setup to include sums of values selected from each category, and add a constraint. Here is the setup ...
...以及相应的结果...
... and the corresponding result ...
制定算法
如果确定要寻找的解决方案范围的标准，则可以设置VBA子以循环设置求解器"，获取解决方案并存储选定列"的结果以供查看.
If you determine criteria for the range of solutions you are looking for, you could setup a VBA sub to loop through setting up Solver, getting the solution, and storing the results of the "Selected Column" for review.
如果您想继续前进，建议您查看本网站上提供的其他解决方案，尝试进行设置，并在遇到问题时提出新的问题.
If you want to pursue this track, I suggest you look at other solutions provided on this site, try to set something up, and ask a new question if you have problems.
修改-错过了您的条件之一
我错过了您的发言-目标子集总数具有定义的构成:A，B，B，C，C，C，(B或C)，(B或C)，D，E"
I missed addressing your statement - "the target subset total has a defined makeup: A, B, B, C, C, C, (B or C), (B or C),D, E"
在这种情况下，使用包含每个类别的总和的设置，您可以指定这些约束...
In this instance, using the setup containing sums for each category, you can specify these constraints ...
- $ G $ 4 = 1
- $ G $ 5< = 4
- $ G $ 5> = 2
- $ G $ 6< = 5
- $ G $ 6> = 3
- $ G $ 7 = 1
- $ G $ 8 = 1
在第一遍中，它提供了以下结果:A1，B2，B3，C1，C3，C4，C5，C6，D1，E1，总计为106.38.
On first pass, it provided this result: A1, B2, B3, C1, C3, C4, C5, C6, D1, E1 with a total of 106.38.
这篇关于Excel规划求解从数字集的子集中查找目标总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
