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

发现列表中相加等于特定总和的所有可能数字组合，是许多Excel用户可能会遇到的挑战，无论是用于预算编制、规划还是数据分析目的。

在此示例中，我们有一个数字列表，目标是从该列表中找出哪些组合的总和为480。提供的截图显示有五组可能的组合可以达到此总和，包括像300+120+60、250+120+60+50这样的组合。在本文中，我们将探讨各种方法来确定列表中总计为指定值的特定数字组合。

使用求解器功能查找等于给定总和的数字组合

获取等于给定总和的所有数字组合

• 通过使用用户定义函数
• 通过一个智能功能 – Kutools for Excel

使用VBA代码获取范围内具有总和的所有数字组合

使用求解器功能查找等于给定总和的单元格组合

深入研究Excel以找到加起来等于特定数字的单元格组合可能看起来令人生畏，但求解器插件使这一过程变得轻而易举。我们将引导您完成设置求解器并找到正确单元格组合的简单步骤，让看似复杂的任务变得简单且可行。

步骤1：启用求解器插件

1. 请前往 文件 > 选项，在 Excel选项 对话框中，点击 加载项 从左侧窗格中选择，然后点击 转到 按钮。见截图：
   
2. 然后， 加载项 对话框出现，请勾选 求解器加载项 选项，并点击 确定 以成功安装此加载项。
   

步骤2：输入公式

激活求解器加载项后，您需要将此公式输入到单元格B11中：

=SUMPRODUCT(B2:B10,A2:A10)

步骤3：配置并运行求解器以获取结果

1. 点击 数据 > 求解器 进入 求解器参数 对话框，在对话框中，请执行以下操作：
   • (1.) 点击 按钮以选择单元格 B11 即公式所在的位置，从 设置目标 部分；
   • (2.) 然后在“至”部分，选择“值为”，并根据需要输入目标值 480；
   • (3.) 在 通过更改可变单元格 部分，请点击 按钮以选择单元格范围 B2:B10 这将标记您的相应数字。
   • (4.) 然后，点击添加按钮。
   • 
2. 然后， 添加约束 对话框显示，点击 按钮以选择单元格范围 B2:B10，并从下拉列表中选择 二进制 最后，点击 确定 按钮。见截图：
   
3. 在 求解器参数 对话框中，点击 求解 按钮，几分钟后，弹出一个 求解器结果 对话框，您可以看见等于给定总和480的单元格组合在列B中标记为1。在 求解器结果 对话框中，请选择 保留求解器解决方案 选项，并点击 确定 退出对话框。见截图：
   

获取等于给定总和的所有数字组合

探索Excel更深层次的功能可以让您找到每个与特定总和匹配的数字组合，而且比您想象的要容易。本节将向您展示两种方法来找到所有等于给定总和的数字组合。

使用用户定义函数获取等于给定总和的所有数字组合

为了揭示来自特定集合中的每个可能的数字组合，这些组合共同达到给定值，下面概述的自定义函数是一个有效的工具。

步骤1：打开VBA模块编辑器并复制代码

1. 在Excel中按住ALT + F11键，打开Microsoft Visual Basic for Applications窗口。
2. 点击 插入 > 模块，并将以下代码粘贴到模块窗口中。
   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
   

步骤2：输入自定义公式以获取结果

粘贴代码后，关闭代码窗口返回工作表。在空白单元格中输入以下公式以输出结果，然后按Enter键获取所有组合。见截图：

=MakeupANumber(A2:A10,B2)

提示：如果您想在列中垂直列出组合结果，请应用以下公式：

=TRANSPOSE(MakeupANumber(A2:A10,B2))

使用强大功能获取等于给定总和的所有数字组合

鉴于上述功能的局限性，我们推荐一种快速且全面的解决方案：Kutools for Excel的凑数功能，兼容任何版本的Excel。此替代方案可以有效处理正数、小数和负数。使用此功能，您可以快速获取所有等于给定总和的组合。

1. 点击 Kutools > 文本 > 凑数，见截图：
   
2. 然后，在 凑数 对话框中，请点击 按钮以从 数据源中选择您要使用的数字列表，然后在 总和 文本框中输入总数。最后，点击 确定 按钮，见截图：
   
3. 然后，会弹出一个提示框提醒您选择一个单元格以放置结果，然后点击 确定，见截图：
   
4. 现在，所有等于给定数字的组合都已显示如下截图所示：
   

使用VBA代码获取范围内具有总和的所有数字组合

有时，您可能会发现自己处于需要识别所有可能的数字组合的情况下，这些数字的总和落在特定范围内。例如，您可能希望找到每种可能的数字分组，其中总和介于470和480之间。

发现总和落在特定范围内的所有可能数字组合代表了Excel中一个引人入胜且高度实用的挑战。本节将介绍一个VBA代码来解决此任务。

步骤1：打开VBA模块编辑器并复制代码

1. 在Excel中按住ALT + F11键，打开Microsoft Visual Basic for Applications窗口。
2. 点击 插入 > 模块，并将以下代码粘贴到模块窗口中。
   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：执行代码

1. 粘贴代码后，按 F5 键运行此代码，在第一个弹出的对话框中，选择您要使用的数字范围，并点击 确定。见截图：
   
2. 在第二个提示框中，选择或输入低限数字，并点击 确定。见截图：
   
3. 在第三个提示框中，选择或输入高限数字，并点击 确定。见截图：
   
4. 在最后一个提示框中，选择一个输出单元格，这是结果开始输出的地方。然后点击 确定。见截图：
   

结果

现在，每个符合条件的组合将在工作表的连续行中列出，从您选择的输出单元格开始。

Excel提供了几种方法来找到加起来等于某个总和的数字组，每种方法的工作方式不同，因此您可以根据对Excel的熟悉程度以及项目需求选择一种。如果您有兴趣探索更多Excel技巧和窍门，我们的网站提供了数千个教程。感谢您的阅读，我们期待未来为您提供更多有用的信息！

相关文章：