如何在 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 實用技巧？我們的網站提供數千篇教學文章，助您輕鬆提升效率！感謝您的閱讀，期待未來為您帶來更多實用資訊！

相關文章：