如何在 Excel 中找到所有等於給定總和的組合？

發現列表中加起來等於特定總和的所有可能數字組合，是許多 Excel 使用者可能會遇到的挑戰，無論是為了預算、規劃還是數據分析的目的。

在此範例中，我們有一個數字列表，目標是找出列表中哪些組合的總和為 480。提供的截圖顯示有五組可能的組合達到此總和，包括像 300+120+60、250+120+60+50 等組合。在本文中，我們將探討各種方法來確定列表中總和為指定值的特定數字組合。

使用求解器函數找到等於給定總和的數字組合

獲取所有等於給定總和的數字組合

• 通過用戶自定義函數
• 通過一個智能功能 – Kutools for Excel

使用 VBA 代碼獲取總和在某範圍內的所有數字組合

使用求解器函數找到等於給定總和的單元格組合

深入研究 Excel 找到加起來等於特定數字的單元格組合可能看似困難，但求解器外掛程式讓這變得輕而易舉。我們將引導您完成簡單的步驟來設置求解器並找到正確的單元格組合，讓看似複雜的任務變得簡單且可行。

步驟 1：啟用求解器外掛程式

1. 請前往 File > Options，在 Excel Options 對話框中，從左側窗格點擊 Add-Ins，然後點擊 Go 按鈕。參見截圖：
   
2. 接著，出現 Add-Ins 對話框，勾選 Solver Add-in 選項，並點擊 OK 安裝該外掛程式。
   

步驟 2：輸入公式

啟動求解器外掛程式後，您需要在 B11 單元格中輸入以下公式：

=SUMPRODUCT(B2:B10,A2:A10)

步驟 3：配置並運行求解器以獲得結果

1. 點擊 Data > Solver 進入 Solver Parameter 對話框，在對話框中，請執行以下操作：
   • (1.) 點擊 按鈕選擇單元格 B11 您的公式所在的位置，位於 Set Objective 部分；
   • (2.) 然後在 To 部分，選擇 Value Of，並根據需要輸入您的目標值 480；
   • (3.) 在 By Changing Variable Cells 部分，請點擊 按鈕選擇單元格範圍 B2:B10 將標記出相應的數字。
   • (4.) 接著，點擊 Add 按鈕。
   • 
2. 然後，出現 Add Constraint 對話框，點擊 按鈕選擇單元格範圍 B2:B10，並選擇 bin 從下拉列表中。最後，點擊 OK 按鈕。參見截圖：
   
3. 在 Solver Parameter 對話框中，點擊 Solve 按鈕，幾分鐘後，會彈出 Solver Results 對話框，您可以看到等於給定總和 480 的單元格組合在 B 列中被標記為 1。在 Solver Results 對話框中，請選擇 Keep Solver Solution 選項，並點擊 OK 離開對話框。參見截圖：
   

獲取所有等於給定總和的數字組合

探索 Excel 更深層的功能，您可以找到每個匹配特定總和的數字組合，而且比您想像的要容易。本節將向您展示兩種方法來找到所有等於給定總和的數字組合。

使用用戶自定義函數獲取所有等於給定總和的數字組合

為了揭示來自特定集合的所有可能數字組合，這些組合共同達到給定值，下面概述的自定義函數作為一種有效的工具。

步驟 1：打開 VBA 模塊編輯器並複製代碼

1. 在 Excel 中按住 ALT + F11 鍵，打開 Microsoft Visual Basic for Applications 窗口。
2. 點擊 Insert > Module，並將以下代碼粘貼到模塊窗口中。
   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 的 Make up a Number 功能，該功能兼容任何版本的 Excel。這個替代方案可以有效地處理正數、小數和負數。使用此功能，您可以快速獲取所有等於給定總和的組合。

1. 點擊 Kutools > Content > Make Up A Number，參見截圖：
   
2. 然後，在 Make up a number 對話框中，請點擊 按鈕選擇您想使用的數字列表，來自 Data Source，然後在 Sum 文本框中輸入總數。最後，點擊 OK 按鈕，參見截圖：
   
3. 然後，會彈出一個提示框提醒您選擇一個單元格來放置結果，然後點擊 OK，參見截圖：
   
4. 現在，所有等於給定數字的組合都已顯示如下截圖所示：
   

使用 VBA 代碼獲取總和在某範圍內的所有數字組合

有時，您可能會遇到需要識別所有可能的數字組合的情況，這些數字的總和落在特定範圍內。例如，您可能希望找到每一個可能的數字組合，其中總和介於 470 和 480 之間。

發現總和落在特定範圍內的所有可能數字組合代表了 Excel 中一個有趣且非常實用的挑戰。本節將介紹一種解決此任務的 VBA 代碼。

步驟 1：打開 VBA 模塊編輯器並複製代碼

1. 在 Excel 中按住 ALT + F11 鍵，打開 Microsoft Visual Basic for Applications 窗口。
2. 點擊 Insert > Module，並將以下代碼粘貼到模塊窗口中。
   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 鍵運行此代碼，在第一個彈出的對話框中，選擇您想使用的數字範圍，然後點擊 OK。參見截圖：
   
2. 在第二個提示框中，選擇或輸入最低限值數字，然後點擊 OK。參見截圖：
   
3. 在第三個提示框中，選擇或輸入最高限值數字，然後點擊 OK。參見截圖：
   
4. 在最後一個提示框中，選擇一個輸出單元格，這是結果開始輸出的地方。然後點擊 OK。參見截圖：
   

結果

現在，每個符合條件的組合將在工作表中的連續行中列出，從您選擇的輸出單元格開始。

Excel 提供了幾種方法來找到加起來等於特定總數的數字組合，每種方法的工作方式不同，因此您可以根據自己對 Excel 的熟悉程度以及項目需求選擇一種。如果您對探索更多 Excel 技巧感興趣，我們的網站提供了數千個教程。感謝您的閱讀，我們期待未來為您提供更多有用的信息！

相關文章：