如何在Excel中查找等于給定總和的所有組合

如何在Excel中查找等于給定總和的所有組合？

例如，我有以下數字列表，現在，我想知道列表中的數字組合總計為480，在下面的屏幕截圖中，您可以看到有五組可能的組合加起來等于到480，例如300 + 60 + 120、300 + 60 + 40 + 80等。本文，我將討論一些方法來查找哪些單元格在Excel中求和成特定值。

用公式找到等于給定總和的單元格組合使用規劃求解加載項查找等于給定總和的單元格組合使用用戶定義函數查找等于給定總和的像元組合查找等于給定總和且具有驚人功能的所有組合 用公式找到等于給定總和的單元格組合

首先，您需要創建一些范圍名稱，然后應用數組公式來查找求和為目標值的單元格，請按以下步驟操作：

1。選擇數字列表并為該列表定義范圍名稱- 范圍1 到 名稱框，然后按輸入完成定義的范圍名稱的鍵，請參見屏幕截圖：

2。為數字列表定義范圍名稱后，您需要在列表中再創建兩個范圍名稱 名稱管理員 框，請點擊公式 > 名稱管理員在 名稱管理員 對話框，單擊全新按鈕，請參見屏幕截圖：

3。在彈出 新名字 對話框中，輸入名稱 List1 到 Name 字段，然后鍵入此公式 = ROW（INDIRECT（“ 1：”＆ROWS（Range1））） (范圍1 是您在步驟1）中創建的范圍名稱指字段，請參見屏幕截圖：

4。點擊 OK 回到了 名稱管理員 對話框，然后繼續單擊全新按鈕可在其中創建另一個范圍名稱 新名字 對話框中，輸入名稱 List2 到 Name 字段，然后鍵入此公式 = ROW（INDIRECT（“ 1：”＆2 ^ ROWS（Range1））） (范圍1 是您在步驟1）中創建的范圍名稱指字段，請參見屏幕截圖：

5。創建范圍名稱后，請將以下數組公式應用于單元格B2中：

=IF(ISNUMBER(MATCH(ROWS(: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,0),),TRANSPOSE(List1)),0)),"X","")，然后按 Shift + Ctrl + 輸入 鍵在一起，然后將填充手柄向下拖動到列表的最后一個數字B8單元格，您可以看到總數為480的數字在B列中標記為X，請參見屏幕截圖：

筆記：在上面的長公式中： List1, List2 和 范圍1 是您在之前的步驟中創建的范圍名稱， C2 是您希望數字加起來的特定值。如果多個值組合的總和等于特定值，則僅列出一種組合。

在Excel中快速輕松地查找并列出等于給定總和的所有組合

Kutools for Excels 組成一個數字 實用程序可以幫助您快速輕松地查找和列出等于給定總數的所有組合和特定組合。 點擊下載 Kutools for Excel!

Kutools for Excel：超過300個方便的Excel插件，，沒有限制30 天。 立即下載并！

使用規劃求解加載項查找等于給定總和的單元格組合

如果您對上述方法感到困惑，則Excel包含 求解器加載項 功能，通過使用此加載項，您還可以標識總數等于給定值的數字。

1. 首先，您需要激活它求解添加請去文件 > 附加選項在 Excel選項 對話框，單擊 加載項 在左窗格中，然后單擊 求解器加載項 來自 不活動的應用程序加載項 部分，請參見屏幕截圖：

2。然后點擊 Go 按鈕進入 加載項 對話框，檢查 求解器加載項 選項，然后單擊 OK 成功安裝此加載項。

3。激活規劃求解加載項后，然后需要在單元格B9中輸入以下公式： = SUMPRODUCT（B2：B9，A2：A9）（B2：B9 是您的號碼列表旁邊的空白列單元格，并且 A2：A9 是您使用的號碼列表。），然后按輸入鍵，請參見屏幕截圖：

4. 然后點擊 Data > 求解去 求解器參數 對話框中，在對話框中，請執行以下操作：

（1.）點擊按鈕選擇單元格 B10 您的公式來自 設定目標 部分;

（2.）然后在至部分，選擇 的價值，然后輸入您的目標值 480 根據需要

（3.）在 通過更改可變單元格 部分，請點擊按鈕選擇單元格范圍 B2：B9 在哪里標記您的相應數字。

5. 然后點擊地址按鈕去 添加約束 對話框，單擊按鈕選擇單元格范圍 B2：B9，然后選擇箱子從下拉列表中，查看屏幕截圖：

6。點擊 OK 回去 求解器參數 對話框，然后單擊解決幾分鐘后， 求解結果 彈出對話框，您可以看到等于給定總和480的單元格組合被標記為1。 求解結果 對話框，請選擇 保持求解器解決方案 選項，然后單擊 OK 退出對話框。看截圖：

備注：如果存在多個組合的值之和等于特定值，則此方法也只能獲得一個組合單元格。

使用用戶定義函數查找等于給定總和的像元組合

對于大多數Excel用戶來說，前兩種方法都很復雜，在這里，我可以創建一個VBA代碼來快速輕松地解決此問題。

為了獲得正確的結果，您必須先按降序對數字列表進行排序。然后執行以下步驟：

1。按住 ALT + F11 鍵打開 Microsoft Visual Basic應用程序 窗口。

2。點擊插頁 > 模塊，然后將以下代碼粘貼到模塊窗口。

VBA代碼：查找等于給定總和的單元格組合：

Function GetCombination(CoinsRange As Range, SumCellId As Double) As Stringupdateby ExtendofficeDim xStr As StringDim xSum As DoubleDim xCell As RangexSum = SumCellIdFor Each xCell In CoinsRangeIf Not (xSum / xCell < 1) ThenxStr = xStr & Int(xSum / xCell) & " of " & xCell & " "xSum = xSum - (Int(xSum / xCell)) * xCellEnd IfNextGetCombination = xStrEnd FunctionCopy

3。然后保存并關閉此代碼窗口，然后返回工作表，并輸入此公式 = getcombination（A2：A9，C2） 放入空白單元格，然后按輸入鍵，您將得到以下結果，該結果顯示等于給定總和的組合號，請參見屏幕截圖：

筆記：在上式中 A2：A9 是數字范圍，并且 C2 包含您要等于的目標值。如果多個值組合的總和等于特定值，則僅列出一種組合。 查找等于給定總和且具有驚人功能的所有組合

也許上述所有方法對您來說都有些困難，在這里，我將介紹一個功能強大的工具， Kutools for Excel，其 組成一個數字 功能，您可以快速獲得等于給定總和的所有組合。

提示：申請這個 組成一個數字 功能，首先，您應該下載 Kutools for Excel，然后快速輕松地應用該功能。

安裝后 Kutools for Excel，請這樣做：

1。點擊 Kutools > 內容 > 組成一個數字，請參見屏幕截圖：

2。然后，在補數對話框，請單擊按鈕從中選擇要使用的號碼列表 數據源，然后將總數輸入到總和文本框，請參見屏幕截圖：

3。然后，單擊 OK 按鈕，將彈出提示框，提醒您選擇一個單元格以找到結果，請參見屏幕截圖：

4.然后，單擊 OK，現在，所有等于該給定數字的組合都已顯示，如下面的屏幕截圖所示：

點擊下載 Kutools for Excel 現在！

演示：在Excel中查找等于給定總和的單元格組合