如何在Excel中查找等於給定總和的所有組合?
無論是出於預算、規劃還是資料分析目的,許多 Excel 使用者可能會遇到一個挑戰,即發現清單中所有可能的數字組合,總和達到特定總和。
在此範例中,我們有一個數字列表,目標是確定該列表中哪些組合的總和為 480。提供的螢幕截圖顯示有五組可能的組合可以達到此總和,包括 300+120 等組合+60、250 +120+60+50 等等。在本文中,我們將探索各種方法來找出清單中總計為 Excel 中指定值的特定數字組合。
使用 Solver 函數尋找等於給定總和的儲存格組合
深入 Excel 中尋找加起來等於特定數字的單元格組合可能看起來令人畏懼,但 Solver 插件使這一切變得輕而易舉。我們將引導您完成設定求解器的簡單步驟並找到正確的單元組合,使看似複雜的任務變得簡單可行。
第 1 步:啟用求解器插件
- 請去 文件 > 選項在 Excel選項 對話框,單擊 加載項 從左窗格中,然後按一下 Go 按鈕。 看截圖:
- 然後, 加載項 出現對話框,檢查 求解器加載項 選項,然後單擊 OK 成功安裝此加載項。
步驟2:輸入公式
啟動 Solver 外掛後,您需要在儲存格 B11 中輸入以下公式:
=SUMPRODUCT(B2:B10,A2:A10)
步驟 3:配置並運行 Solver 以獲得結果
- 點擊 數據 > 求解 去 求解器參數 對話框中,在對話框中,請執行以下操作:
- (1.)點擊 按鈕選擇單元格 B11 你的公式所在的位置 設定目標 部分;
- (2.)然後在 至 部分,選擇 的價值,然後輸入您的目標值 480 根據需要
- (3.)在 通過更改可變單元格 部分,請點擊 按鈕選擇單元格範圍 B2:B10 在哪裡標記您的相應數字。
- (4.)然後,單擊 加入 按鈕。
- 然後, 添加約束 彈出對話框,點擊 按鈕選擇單元格範圍 B2:B10,然後選擇 箱子 從下拉清單中。最後,點擊 OK 按鈕。 看截圖:
- 在 求解器參數 對話框中,單擊 解決 幾分鐘後, 求解結果 彈出對話框,可以看到B列中等於給定和480的儲存格組合被標記為1。 求解結果 對話框,請選擇 保持求解器解決方案 選項,然後單擊 OK 退出對話框。 看截圖:
使用使用者定義函數取得等於給定總和的所有數字組合
為了從特定集合中找出所有可能的數字組合,使其共同達到給定值,下面概述的自訂函數可以作為有效的工具。
第 1 步:打開 VBA 模塊編輯器並複制代碼
- 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications 窗口。
- 點擊 插入 > 模塊,然後將以下代碼粘貼到“模塊窗口”中。
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))
- 此自訂函數僅適用於 Excel 365 和 2021。
- 此方法僅對正數有效;小數值會自動四捨五入到最接近的整數,負數會導致錯誤。
使用強大的功能來獲得等於給定總和的所有數字組合
鑑於上述功能的局限性,我們建議一個快速且全面的解決方案:Kutools for Excel's 組成數字功能,它與任何版本的 Excel 相容。這種替代方案可以有效地處理正數、小數和負數。透過此功能,您可以快速獲得等於給定總和的所有組合。
- 點擊 庫工具 > 內容 > 組成一個數字,請參見屏幕截圖:
- 那麼,在 補數 對話框,請單擊 按鈕從中選擇要使用的號碼列表 數據源,然後將總數輸入到 總和 文本框。 最後,點擊 OK 按鈕,請參見屏幕截圖:
- 然後會彈出提示框,提醒您選擇一個儲存格來定位結果,然後按一下 OK,請參見屏幕截圖:
- 現在,所有等於給定數字的組合都已顯示,如下圖所示:
使用VBA程式碼取得總和在一定範圍內的所有數字組合
有時,您可能會發現自己需要識別所有可能的數字組合,這些數字加起來等於特定範圍內的總和。例如,您可能想要找出總數在 470 到 480 之間的所有可能的數字分組。
在 Excel 中,發現總和為特定範圍內的值的所有可能的數字組合是一項令人著迷且高度實用的挑戰。本節將介紹用於解決此任務的 VBA 程式碼。
第 1 步:打開 VBA 模塊編輯器並複制代碼
- 按住 ALT + F11 鍵,然後打開 Microsoft Visual Basic for Applications 窗口。
- 點擊 插入 > 模塊,然後將以下代碼粘貼到“模塊窗口”中。
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步:執行程式碼
- 粘貼代碼後,按 F5 鍵執行此程式碼,在第一個彈出的對話方塊中,選擇要使用的數字範圍,然後按一下 OK。 看截圖:
- 在第二個提示方塊中,選擇或輸入下限數字,然後按一下 OK。 看截圖:
- 在第三個提示方塊中,選擇或輸入上限數,然後按一下 OK。 看截圖:
- 在最後一個提示框中,選擇一個輸出儲存格,即開始輸出結果的位置。然後點擊 OK。 看截圖:
結果
現在,每個合格組合將從您選擇的輸出儲存格開始列在工作表中的連續行中。
Excel 為您提供了多種方法來尋找加起來等於特定總數的數字群組,每種方法的工作方式不同,因此您可以根據您對 Excel 的熟悉程度以及您的專案需求來選擇一種方法。如果您有興趣探索更多 Excel 提示和技巧,我們的網站提供了數千個教程,請 點擊此處訪問它們。 感謝您的閱讀,我們期待在未來為您提供更多有用的信息!
相關文章:
- 列出或產生所有可能的組合
- 假設我有以下兩列數據,現在,我想基於兩個值列表生成所有可能組合的列表,如左圖所示。 也許,如果值很少,則可以一一列出所有組合,但是,如果有幾列需要列出多個值,則可能是組合,這裡有一些快速技巧可以幫助您解決Excel中的此問題。
- 列出單列中所有可能的組合
- 如果您想從單列數據中返回所有可能的組合以獲得如下屏幕截圖所示的結果,您是否有任何快速的方法可以在 Excel 中處理此任務?
- 產生 3 列或多列的所有組合
- 假設我有3列數據,現在,我想在這3列中生成或列出數據的所有組合,如下圖所示。 您有什麼好的方法可以在Excel中解決此任務?
- 產生所有可能的 4 位數字組合的列表
- 在某些情況下,我們可能需要生成一個由4到0的所有可能的9位數字組成的列表,這意味著要生成一個0000、0001、0002…9999的列表。 為了快速解決Excel中的列表任務,我為您介紹了一些技巧。
最佳辦公生產力工具
🤖 | Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行 | 生成代碼 | 建立自訂公式 | 分析數據並產生圖表 | 呼叫 Kutools 函數... |
熱門特色: 尋找、突出顯示或識別重複項 | 刪除空白行 | 合併列或儲存格而不遺失數據 | 沒有公式的回合 ... | |
超級查詢: 多條件VLookup | 多值VLookup | 跨多個工作表的 VLookup | 模糊查詢 .... | |
高級下拉列表: 快速建立下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
欄目經理: 新增特定數量的列 | 移動列 | 切換隱藏列的可見性狀態 | 比較範圍和列 ... | |
特色功能: 網格焦點 | 設計圖 | 大方程式酒吧 | 工作簿和工作表管理器 | 資源庫 (自動文字) | 日期選擇器 | 合併工作表 | 加密/解密單元格 | 按清單發送電子郵件 | 超級濾鏡 | 特殊過濾器 (過濾粗體/斜體/刪除線...)... | |
前 15 個工具集: 12 文本 工具 (添加文本, 刪除字符,...) | 50+ 圖表 類型 (甘特圖,...) | 40+ 實用 公式 (根據生日計算年齡,...) | 19 插入 工具 (插入二維碼, 從路徑插入圖片,...) | 12 轉化 工具 (數字到單詞, 貨幣兌換,...) | 7 合併與拆分 工具 (高級合併行, 分裂細胞,...) | ... 和更多 |
使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。 點擊此處獲取您最需要的功能...
Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆
- 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!