如何获得不同的细胞组，每个细胞组加起来一定数量 [英] how to get different cell groups each add up to a certain number

问题描述

我有一个excel文件，其中的一列包含代表文件大小的数字，这些数字中的每一个都是浮点数，最大小数位数为2(例如:23.04).总单元数为(277个单元格).我想自动将总计( 1000 )或与该数字(1000)尽可能接近的单元格分组.所有单元格的总和为(5739.49)，因此， 应有(4)个单元格组 ，每个单元格总计为(1000)，而 最后一个(第五组)没关系 .现在:

I have an excel file that has one column which contains numbers that represent file sizes, each of those numbers is a floating point number with a max decimal places of two (Ex: 23.04). the total cells count is (277 cells).i want to automatically group cells that add up to (1000) or as close as possible to that number (1000). the sum of all the cells is (5739.49) so, there should be (4) groups of cells, each adds up to (1000) and the last one (5th group) doesn't matter how much it adds up to. now :

• 我知道可以在每个小组的excel中使用"求解器"解决问题，但是我不确定我应该使用哪些约束以及解决方法(即单纯形，GRG非线性或演化)的方法，因为我对此不熟悉

• I know that this can be solved using "THE SOLVER" in excel for each group But i'm not sure what constraints i should use and which solving method (i.e, the simplex, GRG nonlinear or the evolutionary) methods because i'm not familiar with it

先谢谢您

推荐答案

我在评论中提到，Excel中的标准求解器对决策变量的最大限制为200.

As I mentioned in the comment, the standard solver in Excel has a maximum limit for decision variables of 200.

此示例限于200个变量-如果您具有高级求解器，则可以扩展解决方案.这是使用Excel 2016完成的.

This example is limited to 200 variables - if you have the premium solver, you can expand the solution. This was done using Excel 2016.

工作表设置

1. A列包含尺寸数据.
2. B列包含A列中每个成员所属的组".最初使用公式 = RANDBETWEEN(1,5)进行填充.然后使用复制/选择性粘贴-值"将其覆盖.
3. D列包含标记为1到5的聚合组.
4. E列包含每个组中的合计大小.通过 = SUMIF($ B $ 2:$ B $ 201，"="& D2，$ A $ 2:$ A $ 201)
计算得出
5. F列包含每个组的目标值(第1组到第4组为1000，第5组为忽略).
6. G列包含每个组的目标值的平方误差.它是用 =(E2-F2)^ 2
计算的
7. 单元格G7将是设置目标"单元格，并且是误差平方和.它是用 = SUM(G2:G5)
计算的

1. Column A contains the size data.
2. Column B contains the "Group" that each member in Column A will belong to. It is initially populated using the formula =RANDBETWEEN(1,5). It was then overridden using Copy / Paste Special - Values.
3. Column D contains the aggregated groups, labelled 1 through 5.
4. Column E contains the aggregated size within each group. It is calculated with =SUMIF($B$2:$B$201,"="&D2,$A$2:$A$201)
5. Column F contains the target value for each group (1000 for groups 1 through 4, ignored for group 5).
6. Column G contains the squared error from the target value for each group. It is calculated with =(E2-F2)^2
7. Cell G7 will be the "Set Objective" cell and is the sum of error squared. It is calculated with =SUM(G2:G5)

下面是使用求解器之前设置工作表的屏幕截图.

Below is a screen shot of the setup worksheet, before using solver.

求解器设置

已按照以下条件设置了求解器...

Solver is setup with the following criteria ...

1. 设置目标:"为 $ G $ 7
2. 收件人:"为最小值"
3. 通过更改可变单元格:"为 $ B $ 2:$ B $ 201
4. 受约束条件约束:"包括: $ B $ 2:$ B $ 201< = 5 ， $ B $ 2:$ B $ 201 =整数和 $ B $ 2:$ B $ 201> = 1
5. 选择一种求解方法:"是 Evolutionary .注意:您可以使用GRG，但是会慢很多.
6. 在选项"的所有方法"选项卡上的解决整数约束"区域中，确保未选中未忽略忽略整数约束.

1. "Set Objective:" is $G$7
2. "To:" is "Min"
3. "By Changing Variable Cells:" is $B$2:$B$201
4. "Subject to the Constraints:" includes: $B$2:$B$201 <= 5, $B$2:$B$201 = integer, and $B$2:$B$201 >= 1
5. "Select a Solving Method:" is Evolutionary. Note: You could use GRG, but it will be much slower.
6. In "Options", on the "All Methods" tab, in the "Solving with Integer Constraints" area, make sure Ignore Integer Constraints is not checked.

下面是求解器参数"对话框的屏幕截图:

Below is a screen shot of the Solver Parameters dialog:

解决方案

您的最佳区域可能是平坦的"，因此有多种可能的解决方案.

Your optimal region may be "flat" so there are multiple possible solutions.

下面是我生成的解决方案的屏幕截图...

Below is a screen shot of a solution I generated ...

这篇关于如何获得不同的细胞组，每个细胞组加起来一定数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！