如何在Excel中查找等于给定总和的所有组合？

如何在Excel中查找等于给定总和的所有组合？

例如，我有以下数字列表，现在，我想知道列表中的数字组合总计为480，在下面的屏幕截图中，您可以看到有五组可能的组合加起来等于到480，例如300 + 60 + 120、300 + 60 + 40 + 80等。本文，我将讨论一些方法来查找哪些单元格在Excel中求和成特定值。

首先，您需要创建一些范围名称，然后应用数组公式来查找求和为目标值的单元格，请按以下步骤操作：

1。 选择数字列表并为该列表定义范围名称- 范围1 到 名称框，然后按 输入 完成定义的范围名称的键，请参见屏幕截图：

2。 为数字列表定义范围名称后，您需要在列表中再创建两个范围名称 名称管理员 框，请点击 公式 > 名称管理员在 名称管理员 对话框，单击 全新 按钮，请参见屏幕截图：

3。 在弹出 新名字 对话框中，输入名称 List1 到 Name 字段，然后键入此公式 = ROW（INDIRECT（“ 1：”＆ROWS（Range1））） (范围1 是您在步骤1）中创建的范围名称 指 字段，请参见屏幕截图：

4。 点击 OK 回到了 名称管理员 对话框，然后继续单击 全新 按钮可在其中创建另一个范围名称 新名字 对话框中，输入名称 List2 到 Name 字段，然后键入此公式 = ROW（INDIRECT（“ 1：”＆2 ^ ROWS（Range1））） (范围1 是您在步骤1）中创建的范围名称 指 字段，请参见屏幕截图：

5。 创建范围名称后，请将以下数组公式应用于单元格B2中：

=IF(ISNUMBER(MATCH(ROWS(:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C,0),),TRANSPOSE(List1)),0)),"X","")，然后按 Shift + Ctrl + 输入 键在一起，然后将填充手柄向下拖动到列表的最后一个数字B8单元格，您可以看到总数为480的数字在B列中标记为X，请参见屏幕截图：

如果您对上述方法感到困惑，则Excel包含 求解器加载项 功能，通过使用此加载项，您还可以标识总数等于给定值的数字。

1. 首先，您需要激活它 求解 添加请去 文件 > 附加选项在 Excel选项 对话框，单击 加载项 在左窗格中，然后单击 求解器加载项 来自 不活动的应用程序加载项 部分，请参见屏幕截图：

2。 然后点击 Go 按钮进入 加载项 对话框，检查 求解器加载项 选项，然后单击 OK 成功安装此加载项。

3。 激活规划求解加载项后，然后需要在单元格B9中输入以下公式： = SUMPRODUCT（B2：B9，A2：A9）（B2：B9 是您的号码列表旁边的空白列单元格，并且 A2：A9 是您使用的号码列表。 ），然后按 输入 键，请参见屏幕截图：

4. 然后点击 Data > 求解 去 求解器参数 对话框中，在对话框中，请执行以下操作：

（1.）点击  按钮选择单元格 B10 您的公式来自 设定目标 部分;

（2.）然后在 至 部分，选择 的价值，然后输入您的目标值 480 根据需要

（3.）在 通过更改可变单元格 部分，请点击 按钮选择单元格范围 B2：B9 在哪里标记您的相应数字。

5. 然后点击 地址 按钮去 添加约束 对话框，单击 按钮选择单元格范围 B2：B9，然后选择 箱子 从下拉列表中，查看屏幕截图：

6。 点击 OK 回去 求解器参数 对话框，然后单击 解决 几分钟后， 求解结果 弹出对话框，您可以看到等于给定总和480的单元格组合被标记为1。 求解结果 对话框，请选择 保持求解器解决方案 选项，然后单击 OK 退出对话框。 看截图：

对于大多数Excel用户来说，前两种方法都很复杂，在这里，我可以创建一个VBA代码来快速轻松地解决此问题。

为了获得正确的结果，您必须先按降序对数字列表进行排序。 然后执行以下步骤：

1。 按住 ALT + F11 键打开 Microsoft Visual Basic应用程序 窗口。

2。 点击 插页 > 模块，然后将以下代码粘贴到 模块 窗口。

VBA代码：查找等于给定总和的单元格组合：

3。 然后保存并关闭此代码窗口，然后返回工作表，并输入此公式 = getcombination（A2：A9，C2） 放入空白单元格，然后按 输入 键，您将得到以下结果，该结果显示等于给定总和的组合号，请参见屏幕截图：

也许上述所有方法对您来说都有些困难，在这里，我将介绍一个功能强大的工具， Kutools for Excel，其 组成一个数字 功能，您可以快速获得等于给定总和的所有组合。

安装后 Kutools for Excel，请这样做：

1。 点击 Kutools > 内容 > 组成一个数字，请参见屏幕截图：

2。 然后，在 补数 对话框，请单击 按钮从中选择要使用的号码列表 数据源，然后将总数输入到 总和 文本框，请参见屏幕截图：

3。 然后，单击 OK 按钮，将弹出提示框，提醒您选择一个单元格以找到结果，请参见屏幕截图：

4.然后，单击 OK，现在，所有等于该给定数字的组合都已显示，如下面的屏幕截图所示：

点击下载 Kutools for Excel 现在免费试用！