無論是出於預算、規劃還是資料分析目的，許多 Excel 使用者可能會遇到一個挑戰，即發現清單中所有可能的數字組合，總和達到特定總和。

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

使用 Solver 函數尋找等於給定總和的數字組合

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

• 通過使用用戶定義函數
• 透過智慧功能 – Kutools for Excel

使用VBA程式碼取得總和在一定範圍內的所有數字組合

使用 Solver 函數尋找等於給定總和的儲存格組合

深入 Excel 中尋找加起來等於特定數字的單元格組合可能看起來令人畏懼，但 Solver 插件使這一切變得輕而易舉。我們將引導您完成設定求解器的簡單步驟並找到正確的單元組合，使看似複雜的任務變得簡單可行。

第 1 步：啟用求解器插件

1. 請去 文件 > 選項在 Excel選項 對話框，單擊 加載項 從左窗格中，然後按一下 Go 按鈕。 看截圖：
   
2. 然後， 加載項 出現對話框，檢查 求解器加載項 選項，然後單擊 OK 成功安裝此加載項。
   

步驟2：輸入公式

啟動 Solver 外掛後，您需要在儲存格 B11 中輸入以下公式：

=SUMPRODUCT(B2:B10,A2:A10)

步驟 3：配置並運行 Solver 以獲得結果

1. 點擊 數據 > 求解 去 求解器參數 對話框中，在對話框中，請執行以下操作：
   • （1.）點擊 按鈕選擇單元格 B11 你的公式所在的位置 設定目標 部分;
   • （2.）然後在 至 部分，選擇 的價值，然後輸入您的目標值 480 根據需要
   • （3.）在 通過更改可變單​​元格 部分，請點擊 按鈕選擇單元格範圍 B2：B10 在哪裡標記您的相應數字。
   • （4.）然後，單擊 加入 按鈕。
   • 
2. 然後， 添加約束 彈出對話框，點擊 按鈕選擇單元格範圍 B2：B10，然後選擇 箱子 從下拉清單中。最後，點擊 OK 按鈕。 看截圖：
   
3. 在 求解器參數 對話框中，單擊 解決 幾分鐘後， 求解結果 彈出對話框，可以看到B列中等於給定和480的儲存格組合被標記為1。 求解結果 對話框，請選擇 保持求解器解決方案 選項，然後單擊 OK 退出對話框。 看截圖：
   

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

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

使用使用者定義函數取得等於給定總和的所有數字組合

為了從特定集合中找出所有可能的數字組合，使其共同達到給定值，下面概述的自訂函數可以作為有效的工具。

第 1 步：打開 VBA 模塊編輯器並複制代碼

1. 按住 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
   

第二步：輸入自訂公式得到結果

貼上代碼後，關閉代碼視窗以返回工作表。在空白儲存格中輸入以下公式輸出結果，然後按 Enter 獲得所有組合的關鍵。看截圖：

=MakeupANumber(A2:A10,B2)

尖端：如果要在列中垂直列出組合結果，請套用下列公式：

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

使用強大的功能來獲得等於給定總和的所有數字組合

鑑於上述功能的局限性，我們建議一個快速且全面的解決方案：Kutools for Excel's 組成數字功能，它與任何版本的 Excel 相容。這種替代方案可以有效地處理正數、小數和負數。透過此功能，您可以快速獲得等於給定總和的所有組合。

1. 點擊 庫工具 > 內容 > 組成一個數字，請參見屏幕截圖：
   
2. 那麼，在 補數 對話框，請單擊 按鈕從中選擇要使用的號碼列表 數據源，然後將總數輸入到 總和 文本框。 最後，點擊 OK 按鈕，請參見屏幕截圖：
   
3. 然後會彈出提示框，提醒您選擇一個儲存格來定位結果，然後按一下 OK，請參見屏幕截圖：
   
4. 現在，所有等於給定數字的組合都已顯示，如下圖所示：
   

使用VBA程式碼取得總和在一定範圍內的所有數字組合

有時，您可能會發現自己需要識別所有可能的數字組合，這些數字加起來等於特定範圍內的總和。例如，您可能想要找出總數在 470 到 480 之間的所有可能的數字分組。

在 Excel 中，發現總和為特定範圍內的值的所有可能的數字組合是一項令人著迷且高度實用的挑戰。本節將介紹用於解決此任務的 VBA 程式碼。

第 1 步：打開 VBA 模塊編輯器並複制代碼

1. 按住 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 鍵執行此程式碼，在第一個彈出的對話方塊中，選擇要使用的數字範圍，然後按一下 OK。 看截圖：
   
2. 在第二個提示方塊中，選擇或輸入下限數字，然後按一下 OK。 看截圖：
   
3. 在第三個提示方塊中，選擇或輸入上限數，然後按一下 OK。 看截圖：
   
4. 在最後一個提示框中，選擇一個輸出儲存格，即開始輸出結果的位置。然後點擊 OK。 看截圖：
   

結果

現在，每個合格組合將從您選擇的輸出儲存格開始列在工作表中的連續行中。

Excel 為您提供了多種方法來尋找加起來等於特定總數的數字群組，每種方法的工作方式不同，因此您可以根據您對 Excel 的熟悉程度以及您的專案需求來選擇一種方法。如果您有興趣探索更多 Excel 提示和技巧，我們的網站提供了數千個教程，請 點擊此處訪問它們。 感謝您的閱讀，我們期待在未來為您提供更多有用的信息！

相關文章：