如何在Excel中查找等於給定總和的所有組合？
例如，我有以下數字列表，現在，我想知道列表中的數字組合總計為480，在下面的屏幕截圖中，您可以看到有五組可能的組合加起來等於到480，例如300 + 60 + 120、300 + 60 + 40 + 80等。本文，我將討論一些方法來查找哪些單元格在Excel中求和成特定值。
用公式找到等於給定總和的單元格組合
首先，您需要創建一些範圍名稱，然後應用數組公式來查找求和為目標值的單元格，請按以下步驟操作：
1。 選擇數字列表並為該列表定義範圍名稱- 範圍1 到 名稱框，然後按 Enter 完成定義的範圍名稱的鍵，請參見屏幕截圖：
2。 為數字列表定義範圍名稱後，您需要在列表中再創建兩個範圍名稱 名稱管理員 框，請點擊 公式 > 名稱管理員在 名稱管理員 對話框，單擊 全新 按鈕，請參閱屏幕截圖：
 |
 |
 |
3。 在彈出 新名字 對話框中，輸入名稱 List1 到 姓名 字段，然後鍵入此公式 = ROW（INDIRECT（“ 1：”＆ROWS（Range1））） (範圍1 是您在步驟1）中創建的範圍名稱 指 字段，請參見屏幕截圖：
4。 點擊 OK 回到了 名稱管理員 對話框，然後繼續單擊 全新 按鈕可在其中創建另一個範圍名稱 新名字 對話框中，輸入名稱 List2 到 姓名 字段，然後鍵入此公式 = ROW（INDIRECT（“ 1：”＆2 ^ ROWS（Range1））） (範圍1 是您在步驟1）中創建的範圍名稱 指 字段，請參見屏幕截圖：
5。 創建範圍名稱後，請將以下數組公式應用於單元格B2中：
=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X","")，然後按 Shift + Ctrl + 輸入 鍵在一起，然後將填充手柄向下拖動到列表的最後一個數字B8單元格，您可以看到總數為480的數字在B列中標記為X，請參見屏幕截圖：
- 筆記：
- 在上面的長公式中： List1, List2 及 範圍1 是您在之前的步驟中創建的範圍名稱， C2 是您希望數字加起來的特定值。
- 如果多個值組合的總和等於特定值，則僅列出一種組合。
在Excel中快速輕鬆地查找並列出等於給定總和的所有組合
Excel的Kutools's 組成一個數字 實用程序可以幫助您快速輕鬆地查找和列出等於給定總數的所有組合和特定組合。 點擊下載Kutools for Excel！
Excel的Kutools: 擁有多個方便的 Excel 加載項，免費試用，天數無限制。 立即下載並免費試用！
使用規劃求解加載項查找等於給定總和的單元格組合
如果您對上述方法感到困惑，則Excel包含 求解器加載項 功能，通過使用此加載項，您還可以標識總數等於給定值的數字。
1. 首先，您需要激活它 求解 添加請去 文件 > 選項在 Excel選項 對話框，單擊 加載項 在左窗格中，然後單擊 求解器加載項 來自 不活動的應用程序加載項 部分，請參見屏幕截圖：
2。 然後點擊 Go 按鈕進入 加載項 對話框，檢查 求解器加載項 選項，然後單擊 OK 成功安裝此加載項。
3。 激活規劃求解加載項後，然後需要在單元格B9中輸入以下公式： = SUMPRODUCT（B2：B9，A2：A9）（B2：B9 是您的號碼列表旁邊的空白列單元格，並且 A2：A9 是您使用的號碼列表。 ），然後按 Enter 鍵，請參見屏幕截圖：
4. 然後點擊 數據 > 求解 去 求解器參數 對話框中，在對話框中，請執行以下操作：
（1.）點擊 
按鈕選擇單元格 B10 您的公式來自 設定目標 部分;
（2.）然後在 至 部分，選擇 的價值，然後輸入您的目標值 480 根據需要
（3.）在 通過更改可變單元格 部分，請點擊 按鈕選擇單元格範圍 B2：B9 在哪裡標記您的相應數字。
5. 然後點擊 加入 按鈕去 添加約束 對話框，單擊 按鈕選擇單元格範圍 B2：B9，然後選擇 箱子 從下拉列表中，查看屏幕截圖：
6。 點擊 OK 回去 求解器參數 對話框，然後單擊 解決 幾分鐘後， 求解結果 彈出對話框，您可以看到等於給定總和480的單元格組合被標記為1。 求解結果 對話框，請選擇 保持求解器解決方案 選項，然後單擊 OK 退出對話框。 看截圖：
備註：如果存在多個組合且其值等於特定值的組合，則此方法也只能獲得一個組合單元格。
使用用戶定義函數查找等於給定總和的像元組合
對於大多數Excel用戶來說，前兩種方法都很複雜，在這裡，我可以創建一個VBA代碼來快速輕鬆地解決此問題。
為了獲得正確的結果，您必須先按降序對數字列表進行排序。 然後執行以下步驟：
1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。
2。 點擊 插入 > 模塊，然後將以下代碼粘貼到 模塊 窗口。
VBA代碼：查找等於給定總和的單元格組合：
Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
Dim xStr As String
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
For Each xCell In CoinsRange
If Not (xSum / xCell < 1) Then
xStr = xStr & Int(xSum / xCell) & " of " & xCell & " "
xSum = xSum - (Int(xSum / xCell)) * xCell
End If
Next
GetCombination = xStr
End Function
3。 然後保存並關閉此代碼窗口，然後返回工作表，並輸入此公式 = getcombination（A2：A9，C2） 放入空白單元格，然後按 Enter 鍵，您將得到以下結果，該結果顯示等於給定總和的組合號，請參見屏幕截圖：
- 筆記：
- 在上式中 A2：A9 是數字範圍，並且 C2 包含您要等於的目標值。
- 如果多個值組合的總和等於特定值，則僅列出一種組合。
查找等於給定總和且具有驚人功能的所有組合
也許上述所有方法對您來說都有些困難，在這裡，我將介紹一個功能強大的工具， Excel的Kutools，其 組成一個數字 功能，您可以快速獲得等於給定總和的所有組合。
提示：要應用此 組成一個數字 功能，首先，您應該下載 Excel的Kutools，然後快速輕鬆地應用該功能。
安裝後 Excel的Kutools，請這樣做：
1。 點擊 庫工具 > 內容 > 組成一個數字，請參見屏幕截圖：
2。 然後，在 補數 對話框，請單擊 
按鈕從中選擇要使用的號碼列表 數據源，然後將總數輸入到 總和 文本框，請參見屏幕截圖：
3。 然後，單擊 OK 按鈕，將彈出提示框，提醒您選擇一個單元格以找到結果，請參見屏幕截圖：
4.然後，單擊 OK，現在，已顯示等於該給定數字的所有組合，如下所示的屏幕截圖：
演示：在Excel中查找等於給定總和的單元格組合
最佳辦公效率工具
Kutools for Excel 解決了你的大部分問題，並將你的生產力提高了 80%
- 重用: 快速插入 複雜的公式，圖表 以及您以前使用過的任何東西； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
- 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
- 合併單元格/行/列 不會丟失數據； 拆分單元格內容； 合併重複的行/列...防止細胞重複； 比較範圍...
- 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
- 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
- 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
- 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
- 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
- 超過 300 項強大的功能. 支持 Office / Excel 2007-2021 和 365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能 30 天免費試用。 60 天退款保證。
Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆
- 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
