查找所有等於Excel中給定總和的數字組合

3種方法從總和到特定總數的一組數字的所有組合。

在數據分析中，查找值累加的值的組合是一個普遍的問題。例如，您可能想知道可以使用給定預算購買的所有可能組合，或者分配資源以滿足某些要求的所有可能方法。在本文中，我們將探討如何使用Excel Solver和VBA完成任務。我們還將討論處理大型數據集或複雜約束時可能出現的每種方法的一些潛在陷阱和局限性。

• 找到等於給定總和 - excel求解器的數字組合
• 獲取所有匯總到特定總數的組合 - 自定義功能
• 返回所有加起來給定值的組合 - 宏

找到與給定總和與Excel求解器相等的數字組合

遺憾的是，沒有一個內置的Excel功能無法幫助您確定加起來給定總數的數字。幸運的是，Excel為解決線性編程問題提供了特殊的加載項。所有版本的Excel都包含求解器加載項，但默認情況下未啟用。如果您不熟悉此工具，這是一篇關於如何在Excel中添加和使用Solver的精彩文章。

通過在Excel中激活求解器加載項後，繼續執行以下步驟：

1. 創建模型。

   為此，在一列中輸入您的數字集（在我們的情況下為A3：A12），並在您的數字右側添加空白列以獲得結果（B3：B12）。在單獨的單元格（B13）中，輸入類似於此類似的sumproduct公式：

   =SUMPRODUCT(A3:A12, B3:B12)

   

2. 運行求解器加載項。

   在分析組的數據選項卡上，單擊“求解器”按鈕。

   

3. 為求解器定義問題。

   在“求解器參數”對話框中，配置目標和可變單元格：

   • 在設定的目標框中，輸入公式單元的地址（B13）。
   • 在“到”部分中，選擇所需的總和值的值並鍵入所需的總和值（在此示例中為50）。
   • 在通過更改可變單元格框中，選擇要填充結果的範圍（B3：B12）。

   

4. 添加約束。

   要指定約束，即必須滿足的限製或條件，請單擊“添加”按鈕。在“添加約束”對話框窗口中，選擇結果範圍（B3：B12），然後從下拉列表中選擇垃圾箱。約束將自動設置為二進制。完成後，單擊確定。

   

5. 解決問題。

   回到求解器參​​數對話框窗口時，請查看您的設置，然後單擊“求解”按鈕。

   

   幾秒鐘（或幾分鐘）後，將會出現“求解器結果對話框”。如果成功，請選擇“ Keep solver解決方案”選項，然後單擊確定以退出對話框。

   

結果，您將在指定總和旁邊的數字旁邊插入1個。不是用戶友好的解決方案，但這是Excel可以做到的最好的解決方案。

出於可視化目的，我強調了在淺綠色中給出所需總和的細胞：

限制：Excel求解器最多只能找到等於特定總和的數字組合。

找到所有等於給定總和與自定義功能的組合

為了從給定的數字組合的一組數字中獲取所有可能的組合，您可以在下面使用自定義功能。如果您是UDF的新手，則在本指南中找到很多有用的信息：如何在Excel中創建自定義的用戶定義功能。

自定義功能以找到等於給定總和的所有組合

Option Explicit Public Function FindSumCombinations(rngNumbers As Range, lTargetSum As Long ) Dim arNumbers() As Long , part() As Long Dim arRes() As String Dim indI As Long Dim cellCurr As Range ReDim arRes(0) If rngNumbers.Count > 1 Then ReDim arNumbers(rngNumbers.Count - 1) indI = 0 For Each cellCurr In rngNumbers arNumbers(indI) = CLng(cellCurr.Value) indI = indI 1 Next cellCurr Call SumUpRecursiveCombinations(arNumbers, lTargetSum, part(), arRes()) End If ReDim Preserve arRes(0 To UBound (arRes) - 1) FindSumCombinations = arRes End Function Private Sub SumUpRecursiveCombinations(Numbers() As Long , target As long ，part（）長時間， byref arres（） as string ）昏暗s ， i，i， j ，j as Long ，j as n him hem gy as Long ，nubs tim long dim dim dim dim dim dim dim hong，partrec（） as Long S = sumArray （part）如果s = s = s = in nifres the Indres the Indres = ubound （ arres ） redim preserve （ res ） Not Not Numbers) 0 Then For i = 0 To UBound (Numbers) Erase remaining() num = Numbers(i) For j = i 1 To UBound (Numbers) AddToArray remaining, Numbers(j) Next j Erase partRec() CopyArray partRec, part AddToArray partRec, num SumUpRecursiveCombinations remaining, target, partRec, arRes Next i End If End Sub Private Function ArrayToString(x() As Long ) As String Dim n As Long , result As String result = x(n) For n = LBound (x) 1 To UBound (x) result = result & "," & x(n) Next n ArrayToString = result End Function Private Function SumArray(x() As Long ) As Long Dim n As Long SumArray = 0 If ( Not Not x) 0 Then For n = LBound (x) To UBound (x) SumArray = SumArray x(n) Next n End If End Function Private Sub AddToArray(arr() As Long , x As Long ) If ( Not Not arr) 0 Then ReDim Preserve arr(0 To UBound (arr) 1) Else ReDim Preserve arr(0 To 0) End If arr( UBound (arr)) = x End Sub Private Sub CopyArray(destination() As Long , source() As Long ) Dim n As Long If ( Not Not source) 0，然後n = 0到Ubound （ source ）AddToArray目標，源（N）下一個N End如果END SUB

此功能如何工作

主要函數（查找combinations ）調用了一些實現較小子任務的子公司函數。名為sumupRecursivecombinations的功能執行核心算法，該算法在指定範圍內找到所有可能的總和，並過濾到目標的範圍。 ArrayTostring函數控制輸出字符串的形式。另外三個函數（ sumarray ， addtoArray和copyArray ）負責處理中間數組：每次創建一個臨時數組時，將一個元素從源數組中添加到其上，並檢查是否達到目標總和。核心算法是從這個stackoverflow線程中獲取的，謝謝你們的分享！

句法

從用戶的角度來看，我們的自定義功能的語法與此一樣簡單：

findsumcombinations（範圍，總和）

在哪裡：

• 範圍是要測試的數字範圍。
• 總和是目標和。

筆記！自定義功能僅在Dynamic Array Excel 365和2021中起作用。

如何使用FindsumCombinations功能：

1. 將上述代碼插入工作簿的代碼模塊中，並將其保存為宏支持的工作簿（.XLSM）。分步說明在這裡。
2. 在任何空白單元格中，輸入FindsumCombinations公式，然後按Enter鍵。確保有足夠的空單元格向右輸出所有組合，否則公式將返回#spill錯誤。

例如，為了找到等於A3中總和的範圍A6：A15中數字的所有組合，該公式為：

=FindSumCombinations(A6:A15, A3)

與任何其他動態陣列函數一樣，您進入公式僅是一個單元格（在下圖中的C6），並且在必要時將結果填充到盡可能多的單元格中。默認情況下，逗號分隔的字符串連續輸出：

要將結果返回列中，請將自定義函數包裹在這樣的轉換中：

=TRANSPOSE(FindSumCombinations(A6:A15, A3))

要以捲曲括號中封閉的數組的形式輸出字符串，請按以下方式修改陣列弦函數：

Private Function ArrayToString(x() As Long ) As String Dim n As Long , result As String result = "{" & x(n) For n = LBound (x) 1 To UBound (x) result = result & "," & x(n) Next n result = result & "}" ArrayToString = result End Function

結果看起來與此相似：

限制：此自定義功能僅在Dynamic Array Excel 365和Excel 2021中起作用。

獲取所有等於給定總和VBA宏的組合

上面描述的自定義功能返回數字的組合作為字符串。如果您希望在單獨的單元格中將每個數字都有，則此宏將有所幫助。該代碼是由另一位Excel專家Alexander Trifuntov編寫的，他一直在積極幫助用戶解決此博客上的各種Excel問題。

首先將以下代碼添加到您的工作簿中。有關詳細的步驟，請查看如何在Excel中插入VBA代碼。

宏以查找所有組合，加起來給定值

Public RefArray1 As String Public DS As Variant Public TargetSum As Long Public TargetCol As Integer Public TargetRow As Integer Sub Combination() UserForm1.Show End Sub Function GrayCode(Items As Variant ) As String Dim CodeVector() As Integer Dim i, kk, rr, col1, row1, n1, e As Integer Dim lower As Integer , upper As Integer Dim SubList As String Dim NewSub As String Dim done作為布爾型奇數步驟，如布爾值dim dim dim dim targearray（）作為字符串kk = tartscol rr = targetrow col1 = tartscol 3 row1 = tartsrow oddStep = true lower porth = true plose = lbound = lbound = lbound = ubound = ubound （ ubound = ubound（ upr -1，kk） 1, kk).Font.Bold = True Cells(rr - 1, kk 1).Font.Bold = True ReDim CodeVector(lower To upper) 'it starts all 0 Do Until done NewSub = "" For i = lower To upper If CodeVector(i) = 1 Then If NewSub = "" Then NewSub = "," & Items(i) SSS = SSS Items(i) Else NewSub = NewSub & "," & Items(i) SSS = sss項目（i）如果下一個i如果newsub = “” then newSub = “ {}” '空置sublist = sublist＆vbcrlf＆newsub如果sss = targetsum，則cellis（rr，kk）.numberformat =“ @ cells（rr，kk）=“ newn＆mid（newsb）news， E = 0到N1單元格（row1，col1）= targetArray（e）行（e）row1 = row1下一個E Col1 = col1 = col1 = rr = rr 1結束，如果sss = 0'如果現在更新代碼，如果odd odd odd odd fript first first low low do = 1 -priest （ CodeVector(i) 1 i = i 1 Loop 'done if i = upper: If i = upper Then done = True Else 'if not done then flip the *next* bit: i = i 1 CodeVector(i) = 1 - CodeVector(i) End If End If OddStep = Not OddStep 'toggles between even and odd steps Loop GrayCode = SubList End Function

接下來，使用以下設計和屬性創建一個用戶形式（插入> userform ）：

完成表格的設計後，添加表單的代碼。為此，右鍵單擊項目Explorer中的表單，然後選擇“視圖代碼：”：

用戶形式的代碼

private sub commandbutton1_click（） dim b dim b dim dim dim dim dim dim dim a（）作為變體i作為整數dim a aS integer ds aS integer ds = range（refedit1）targetsum = targetsum = textbox1.value範圍（RefedIt2）。選擇targetCol = selection.column targetrow =選擇。​​​​​​​​​​

提示。您可以在本文末尾下載我們的示例工作簿，而不是從那裡重新創建表單，並從那裡複製代碼模塊和用戶形式（請參閱如何將宏複製到另一個工作簿）。要使所有工作簿中的宏可訪問，請將其複製到個人宏觀工作簿中。

使用代碼和表單，按Alt F8並運行FindallCombinations Macro：

在彈出的表單上，定義以下內容：

• 帶有源編號的範圍（A4：A13）
• 目標總和（50）
• 目的地範圍（C4）的左上方單元格。

指定輸出範圍的左上方單元格時，請確保上面至少有一個空白行（對於標頭），並且向下和向右有足夠的空白單元格。如果空白單元格不足，您的現有數據將被覆蓋。

單擊確定將產生結果，如下面的屏幕截圖所示：

• 在C4：C6中，您將數字組合為逗號分隔的值。
• F，G和H列保持相同的數字組合，每個數字都位於單獨的單元格中。
• 在D4中，您有目標總和。

這種輸出形式使檢查結果更加容易 - 只需在單元格F13中輸入總和公式，將其向右邊的兩個單元格拖動，您會發現每個數字的組合都累加到指定的值（50）。

限制：對於大量數字，宏可能需要一些時間來生成所有可能的組合。

優點：在Excel 2010-365的所有版本中都可以使用；在單獨的單元格中提供兩種形式的輸出 - 逗號分隔值和數字的字符串。

總之，找到等於一定值的值的所有組合是解決廣泛的數據分析問題的強大工具。希望本文為您提供了進一步探索此主題並處理自己工作中類似問題的有用起點。謝謝您的閱讀！

練習工作簿

查找等於給定總和的組合 - 示例（.xlsm文件）

以上是查找所有等於Excel中給定總和的數字組合的詳細內容。更多資訊請關注PHP中文網其他相關文章！