跳到主要內容

如何在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:擁有 300 多個方便的 Excel 插件,30 天內免費試用,無限制。 立即下載並免費試用!


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

如果您對上述方法感到困惑,則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,現在,已顯示等於該給定數字的所有組合,如下所示的屏幕截圖:

點擊下載Kutools for Excel並立即免費試用!


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

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (49)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi everyone,

I'm trying to find a way to to find all possible combinations of workershifts for a specific amount of workhours within a specific amount of working days.

Both, solver and Kutools basically work, but:

Solver only ever displays one combination not all of them (which is what I would need).

Kutools on the other hand only uses each value once, for example I know there are solutions with say 3 7-hour shifts but it won't pick the shift more than once.

Thanks in advance for any responses!

Hope I'm not threadnecroing too hard here.
This comment was minimized by the moderator on the site
Hello, simsok,
Sorry, I can't understand you clearly.
Could you give your problem more detailed, or can you insert a screenshot of your problem here?
Thank you!
This comment was minimized by the moderator on the site
Thank you so much!!! The solver add-in worked for me!
This comment was minimized by the moderator on the site
How to get list of cell names that add to a given no.
This comment was minimized by the moderator on the site
Hello, Ranka,
I'm sorry, at present, there is no good way for getting the cells that add to a given number.
Thank you!
This comment was minimized by the moderator on the site
ExtendOffice - How To Find All Combinations That Equal A Given Sum In Excel

In this example, I would like to run 100 rows instead of the 8 in your example. When I try to create more rows the formula stops working. I started over with a fresh sheet and I still can not get the formula to work.

Running windows 10
Excel 2207
Office 365
This comment was minimized by the moderator on the site
Hello, Shaw

Unfortunately, the formula in this article has a limit of 20 values, if there are more than 20 numbers, the result will not come out. In this case, I will recommend the Make Up A Number feature of Kutools for Excel, with it, you can find all combinations from the list of numbers. If you have a lot of numbers and many combination results, it will take much time, but you can set the number of combinations to make it faster. See screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-make-up-number-1.png

You can download this tool from here: https://www.extendoffice.com/download/kutools-for-excel.html
You can try it for free 30 days, please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hi I have tried doing this with the first method and it just isn't working. I'm unsure what I'm doing wrong as I seem to have followed the instructions exactly. Are there any common errors to watch out for? There is no error showing in the formula itself and all cells are all in the same position as yours are, but it's a list of 48 numbers rather than 8.
This comment was minimized by the moderator on the site
Hello, Angie
If you can't get the result by using the first method, you can view the video at the bottom of this article:
https://www.extendoffice.com/documents/excel/3557-excel-find-all-combinations-that-equal-given-sum.html#demo
Also, you can try our tool-Kutools for Excel's Make up a number feature, it will get all combinations quickly and easily.
Thank you!
This comment was minimized by the moderator on the site
So will any of these work when I have been paid by a client, but with no remit so don’t know which invoices have been paid.
This comment was minimized by the moderator on the site
For the solver add in on excel, can you make it solve for closest to 480 rather than value of 480 (example used above)? I really need some help on how this might work out, thanks in advance

This comment was minimized by the moderator on the site
Could this be adapted to find combinations that sum up to specific range i.e. sum between 450 and 500? Is there a way to set it so that each cell value can be used only in one combination not more?
This comment was minimized by the moderator on the site
kutools works only integer value. Not support double. Like (395,52) ! Best solution is excel solver extention.
This comment was minimized by the moderator on the site
is there a way to find combination for a target average instead of sum ?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations