如何在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，請參見屏幕截圖：

如果您對上述方法感到困惑，則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代碼：查找等于給定總和的單元格組合：

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

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

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

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

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

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

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

點擊下載 Kutools for Excel 現在免費試用！