Excel:查找加起来达到给定总数的数字子集?

Excel:查找加起来达到给定总数的数字子集?

我有一列数字(假设它是 A1:A100),我需要找到其中总和达到一定总数的子集。

答案1

使用 Solver 插件* 可以实现。以下步骤在 Excel 2007 和 2010 中对我有用。

  1. 指定一个单元格来保存结果(本例中为 C1)-这是目标单元格,也是 Excel 可用于草稿工作的列(本例中为 B1:B100)
  2. 在目标单元格中​​,输入公式“=SUMPRODUCT(A1:A100,B1:B100)”(无引号)。这将计算 A1*B1+A2*B2+...等的总和
  3. 选择打开求解器(数据选项卡,分析组)
  4. 目标单元格应该是显而易见的(本例中为 $1 加元)
  5. 对于“等于:”,选择“值:”并输入所需值
  6. 在“通过更改单元格”中输入“$B$1:$B$100”(不带引号,可能需要自己将这些值初始化为 0)
  7. 为可以更改的单元格添加约束。在下拉菜单中选择“bin”(二进制)。这会将这些单元格的值限制为 0(从总和中移除相应的 A 单元格)或 1(将相应的 A 单元格添加到总和中)。
  8. 点击“解决”并等待。您正在寻找的子集的数字在 B 列中为 1

例子


如果求解器花费很长时间,您可以通过删除显然不起作用的行来帮助它(总计以美元计算,并且只有一行非零美分)


额外奖励:您可以让 Excel 自动突出显示您要查找的单元格,只需向这些单元格添加条件格式即可。选择要格式化的所有单元格,然后从(主页选项卡)>>(样式组)>>条件格式>>新规则中选择“使用公式确定要格式化的单元格”。在公式中,输入“=$B1=1”(无引号),如果 B 列中相应的行为 1,则计算结果为真。对于格式,您可以添加任何您想要的格式(粗体、斜体、绿色填充等)。

找到重要行的另一种简单方法是按 B 列 Z->A 进行排序,这样所有的 1 都会出现在顶部。


*可以通过以下步骤安装求解器插件

  1. 单击“Microsoft Office 按钮”,然后单击“Excel 选项”。
  2. 单击“加载项”,然后在“管理”框中选择“Excel 加载项”。
  3. 单击“前往”。
  4. 在“可用加载项”框中,选中“规划求解加载项”复选框,然后单击“确定”。 (如果“可用加载项”框中未列出“规划求解加载项”,请单击“浏览”找到该加载项。)
  5. 如果系统提示您计算机上当前未安装规划求解插件,请单击“是”进行安装。

答案2

有一个低成本的 Excel 插件和匹配,它将突出显示达到目标总和的数字子集。

在此处输入图片描述

相关内容