如何在 Excel 中找出所有总和等于指定值的组合？

在列表中找出所有加总等于特定数值的数字组合，是许多 Excel 用户在预算编制、规划或数据分析中常遇到的挑战。

在本例中，我们有一组数字，目标是从该列表中找出总和等于 480 的所有组合。如截图所示，共有五组符合条件的组合，例如 300+120+60、250+120+60+50 等。本文将为您介绍多种方法，助您在 Excel 中精准定位列表中总和等于指定值的数字组合。

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

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

• 使用用户自定义函数
• 使用智能功能——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，并从下拉列表中选择 bin。最后，单击确定按钮。参见截图：
   
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 实用技巧，我们的网站提供数千篇教程，助您轻松提升效率！感谢您的阅读，我们期待未来为您带来更多实用内容！

相关文章：