如何在Excel中查找等于给定总和的所有组合?
无论是出于预算、规划还是数据分析的目的,许多 Excel 用户可能会遇到一个挑战,即找出列表中所有可能的数字组合,使其总和达到特定总和。
在此示例中,我们有一个数字列表,目标是确定该列表中哪些组合的总和为 480。提供的屏幕截图显示有五组可能的组合可以达到此总和,包括 300+120 等组合+60、250+120+60+50 等等。在本文中,我们将探索各种方法来查明列表中总计为 Excel 中指定值的特定数字组合。
使用 Solver 函数查找等于给定总和的单元格组合
深入 Excel 中查找加起来等于特定数字的单元格组合可能看起来令人畏惧,但 Solver 插件使这一切变得轻而易举。我们将引导您完成设置求解器的简单步骤并找到正确的单元组合,使看似复杂的任务变得简单可行。
第 1 步:启用求解器插件
- 请进入 文件 > 附加选项在 Excel选项 对话框,单击 加载项 从左窗格中,然后单击 Go 按钮。 看截图:
- 然后, 加载项 出现对话框,检查 求解器加载项 选项,然后单击 OK 成功安装此加载项。
步骤2:输入公式
激活 Solver 插件后,您需要在单元格 B11 中输入以下公式:
=SUMPRODUCT(B2:B10,A2:A10)
步骤 3:配置并运行 Solver 以获得结果
- 点击 时间 > 求解 去 求解器参数 对话框中,在对话框中,请执行以下操作:
- (1.)点击 按钮选择单元格 B11 你的公式所在的位置 设定目标 部分;
- (2.)然后在 至 部分,选择 的价值,然后输入您的目标值 480 根据需要
- (3.)在 通过更改可变单元格 部分,请点击 按钮选择单元格范围 B2:B10 在哪里标记您的相应数字。
- (4.)然后,单击 地址 按钮。
- 然后, 添加约束 弹出对话框,点击 按钮选择单元格范围 B2:B10,然后选择 箱子 从下拉列表中。最后,点击 OK 按钮。 看截图:
- 在 求解器参数 对话框中,单击 解决 几分钟后, 求解结果 弹出对话框,可以看到B列中等于给定和480的单元格组合被标记为1。 求解结果 对话框,请选择 保持求解器解决方案 选项,然后单击 OK 退出对话框。 看截图:
使用用户定义函数获取等于给定总和的所有数字组合
为了从特定集合中找出所有可能的数字组合,使其共同达到给定值,下面概述的自定义函数可以作为有效的工具。
第 1 步:打开 VBA 模块编辑器并复制代码
- 按住 ALT + F11 键,然后打开 Microsoft Visual Basic应用程序 窗口。
- 点击 插页 > 模块,然后将以下代码粘贴到“模块窗口”中。
VBA代码:获取等于给定总和的所有数字组合Public Function MakeupANumber(xNumbers As Range, xCount As Long) 'updateby Extendoffice Dim arrNumbers() As Long Dim arrRes() As String Dim ArrTemp() As Long Dim xIndex As Long Dim rg As Range MakeupANumber = "" If xNumbers.CountLarge = 0 Then Exit Function ReDim arrNumbers(xNumbers.CountLarge - 1) xIndex = 0 For Each rg In xNumbers If IsNumeric(rg.Value) Then arrNumbers(xIndex) = CLng(rg.Value) xIndex = xIndex + 1 End If Next rg If xIndex = 0 Then Exit Function ReDim Preserve arrNumbers(0 To xIndex - 1) ReDim arrRes(0) Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes()) ReDim Preserve arrRes(0 To UBound(arrRes) - 1) MakeupANumber = arrRes End Function Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String) Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long Dim remainingNumbers() As Long, newCombination() As Long currentSum = 0 If (Not Not ArrTemp) <> 0 Then For i = LBound(ArrTemp) To UBound(ArrTemp) currentSum = currentSum + ArrTemp(i) Next i End If If currentSum = Count Then indRes = UBound(arrRes) ReDim Preserve arrRes(0 To indRes + 1) arrRes(indRes) = ArrTemp(0) For i = LBound(ArrTemp) + 1 To UBound(ArrTemp) arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i) Next i End If If currentSum > Count Then Exit Sub If (Not Not Numbers) = 0 Then Exit Sub For i = 0 To UBound(Numbers) Erase remainingNumbers() num = Numbers(i) For j = i + 1 To UBound(Numbers) If (Not Not remainingNumbers) <> 0 Then ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1) Else ReDim Preserve remainingNumbers(0 To 0) End If remainingNumbers(UBound(remainingNumbers)) = Numbers(j) Next j Erase newCombination() If (Not Not ArrTemp) <> 0 Then For k = 0 To UBound(ArrTemp) If (Not Not newCombination) <> 0 Then ReDim Preserve newCombination(0 To UBound(newCombination) + 1) Else ReDim Preserve newCombination(0 To 0) End If newCombination(UBound(newCombination)) = ArrTemp(k) Next k End If If (Not Not newCombination) <> 0 Then ReDim Preserve newCombination(0 To UBound(newCombination) + 1) Else ReDim Preserve newCombination(0 To 0) End If newCombination(UBound(newCombination)) = num Combinations remainingNumbers, Count, newCombination, arrRes Next i End Sub
第二步:输入自定义公式得到结果
粘贴代码后,关闭代码窗口以返回到工作表。在空白单元格中输入以下公式输出结果,然后按 输入 获得所有组合的关键。看截图:
=MakeupANumber(A2:A10,B2)
=TRANSPOSE(MakeupANumber(A2:A10,B2))
- 此自定义函数仅适用于 Excel 365 和 2021。
- 该方法仅对正数有效;小数值会自动四舍五入到最接近的整数,负数将导致错误。
使用强大的功能获取等于给定总和的所有数字组合
鉴于上述功能的局限性,我们推荐一个快速且全面的解决方案:Kutools for Excel's 组成数字功能,它与任何版本的 Excel 兼容。这种替代方案可以有效地处理正数、小数和负数。通过此功能,您可以快速获得等于给定总和的所有组合。
- 点击 库工具 > 内容 > 组成一个数字,请参见屏幕截图:
- 那么,在 补数 对话框,请单击 按钮从中选择要使用的号码列表 数据源,然后将总数输入到 总和 文本框。 最后,点击 OK 按钮,请参见屏幕截图:
- 然后会弹出提示框,提醒您选择一个单元格来定位结果,然后单击 OK,请参见屏幕截图:
- 现在,所有等于给定数字的组合都已显示,如下图所示:
使用VBA代码获取总和在一定范围内的所有数字组合
有时,您可能会发现自己需要识别所有可能的数字组合,这些数字加起来等于特定范围内的总和。例如,您可能想要找到总数在 470 到 480 之间的所有可能的数字分组。
在 Excel 中,发现总和为特定范围内的值的所有可能的数字组合是一项令人着迷且高度实用的挑战。本节将介绍用于解决此任务的 VBA 代码。
第 1 步:打开 VBA 模块编辑器并复制代码
- 按住 ALT + F11 键,然后打开 Microsoft Visual Basic应用程序 窗口。
- 点击 插页 > 模块,然后将以下代码粘贴到“模块窗口”中。
VBA代码:获取总和达到特定范围的所有数字组合Sub Getall_combinations() 'Updateby Extendoffice Dim xNumbers As Variant Dim Output As Collection Dim rngSelection As Range Dim OutputCell As Range Dim LowLimit As Long, HiLimit As Long Dim i As Long, j As Long Dim TotalCombinations As Long Dim CombTotal As Double Set Output = New Collection On Error Resume Next Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8) If rngSelection Is Nothing Then MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel" Exit Sub End If On Error GoTo 0 xNumbers = rngSelection.Value LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1) HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1) On Error Resume Next Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8) If OutputCell Is Nothing Then MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel" Exit Sub End If On Error GoTo 0 TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2)) For i = 1 To TotalCombinations - 1 Dim tempArr() As Double ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2)) CombTotal = 0 Dim k As Long: k = 0 For j = 1 To UBound(xNumbers, 1) If i And (2 ^ (j - 1)) Then k = k + 1 tempArr(k) = xNumbers(j, 1) CombTotal = CombTotal + xNumbers(j, 1) End If Next j If CombTotal >= LowLimit And CombTotal <= HiLimit Then ReDim Preserve tempArr(1 To k) Output.Add tempArr End If Next i Dim rowOffset As Long rowOffset = 0 Dim item As Variant For Each item In Output For j = 1 To UBound(item) OutputCell.Offset(rowOffset, j - 1).Value = item(j) Next j rowOffset = rowOffset + 1 Next item End Sub
第2步:执行代码
- 粘贴代码后,按 F5 键运行此代码,在第一个弹出的对话框中,选择要使用的数字范围,然后单击 OK。 看截图:
- 在第二个提示框中,选择或输入下限数字,然后单击 OK。 看截图:
- 在第三个提示框中,选择或输入上限数,然后单击 OK。 看截图:
- 在最后一个提示框中,选择一个输出单元格,即开始输出结果的位置。然后点击 OK。 看截图:
结果
现在,每个合格组合都将从您选择的输出单元格开始列在工作表中的连续行中。
Excel 为您提供了多种方法来查找加起来达到特定总数的一组数字,每种方法的工作原理都不同,因此您可以根据您对 Excel 的熟悉程度以及您的项目需求来选择一种方法。如果您有兴趣探索更多 Excel 提示和技巧,我们的网站提供了数千个教程,请 单击此处访问它们。 感谢您的阅读,我们期待在未来为您提供更多有用的信息!
相关文章:
- 列出或生成所有可能的组合
- 假设我有以下两列数据,现在,我想基于两个值列表生成所有可能组合的列表,如左图所示。 也许,如果值很少,则可以一一列出所有组合,但是,如果有几列需要列出多个值,可能的组合就可以通过下面的一些快速技巧来解决。 。
- 列出单列中所有可能的组合
- 如果您想从单列数据中返回所有可能的组合以获得如下屏幕截图所示的结果,您是否有任何快速的方法可以在 Excel 中处理此任务?
- 生成 3 列或多列的所有组合
- 假设我有3列数据,现在,我想在这3列中生成或列出数据的所有组合,如下图所示。 您有什么好的方法可以在Excel中解决此任务?
- 生成所有可能的 4 位数字组合的列表
- 在某些情况下,我们可能需要生成一个由4到0的所有可能的9位数字组成的列表,这意味着要生成一个0000、0001、0002…9999的列表。 为了快速解决Excel中的列表任务,我为您介绍了一些技巧。
最佳办公生产力工具
🤖 | Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用 Kutools 函数... |
热门特色: 查找、突出显示或识别重复项 | 删除空白行 | 合并列或单元格而不丢失数据 | 不使用公式进行四舍五入 ... | |
超级查询: 多条件VLookup | 多值VLookup | 跨多个工作表的 VLookup | 模糊查询 .... | |
高级下拉列表: 快速创建下拉列表 | 依赖下拉列表 | 多选下拉列表 .... | |
列管理器: 添加特定数量的列 | 移动列 | 切换隐藏列的可见性状态 | 比较范围和列 ... | |
特色功能: 网格焦点 | 设计图 | 大方程式酒吧 | 工作簿和工作表管理器 | 资源库 (自动文本) | 日期选择器 | 合并工作表 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊过滤器 (过滤粗体/斜体/删除线...)... | |
前 15 个工具集: 12 文本 工具 (添加文本, 删除字符,...) | 50+ 图表 类型 (甘特图,...) | 40+ 实用 公式 (根据生日计算年龄,...) | 19 插入 工具 (插入二维码, 从路径插入图片,...) | 12 转化 工具 (小写金额转大写, 货币兑换,...) | 7 合并与拆分 工具 (高级组合行, 分裂细胞,...) | ... 和更多 |
使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。 单击此处获取您最需要的功能...
Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松
- 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
- 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
- 每天将您的工作效率提高50%,并减少数百次鼠标单击!