跳到主要內容

在Excel中輕鬆找到等於給定總和的所有組合

Excel的Kutools

提升 Excel 300+
強大的功能

假設有一個數字列表(整數,十進制數或負數),現在,您想從這些數字中找到所有等於給定總和的可能組合。 通常,您可以使用公式,求解器加載項或用戶定義的函數來實現它,但是這很耗時且不易處理。 這裡有用 組成一個數字 的效用 Excel的Kutools,找到等於給定總和的所有組合在Excel中從未如此簡單。

查找等於給定總和的所有組合


查找等於給定總和的所有組合

如下面的屏幕截圖所示,要查找範圍總計為3的B11:B520範圍內的所有可能數字組合,可以嘗試使用Make Up a Number實用程序,如下所示。

1。 點擊 庫工具 > 內容 > 組成一個數字.

拍補個03

2。 在裡面 補數 對話框,請進行以下設置。

  • 數據源 在方框中,選擇數字列表以查找哪些數字加起來為520;
  • 選項 部分,在520中輸入XNUMX 總和 文本框;
  • 點擊 OK 按鈕。 看截圖:

3.在接下來 補數 對話框中,選擇一個單元格以輸出組合,然後單擊 OK 按鈕。 然後一個 Excel的Kutools 彈出對話框,告訴您將創建多少個組合,單擊 OK.

您可以看到以下列出了總計為520的所有可能的選定數字組合。

筆記:

1.可以選擇指定組合數和每個組合中的元素數,而不是生成所有組合。 如下面的屏幕截圖所示,您可以將組合數指定為4,並在每個組合中從1到4指定元素數,然後,您將獲得以下結果:

結果:

2.此強大功能還可應用於十進制和負數,請參見屏幕截圖:

您將獲得如下所示的結果截圖:

3.您可以檢查 保存在新表中 框將組合輸出到新的工作表。


查找等於給定總和的所有組合

Excel的Kutools 包括300多個便捷的Excel工具。 30天免費試用,不受限制。 立即下載免費試用版!


生產力工具推薦
以下工具可以極大地節省您的時間和金錢,哪一種適合您?
Office Tab: 在辦公室中使用方便的選項卡,作為Chrome,Firefox和New Internet Explorer的方式。
Excel的Kutools: 超過300種Excel高級功能 2021、2019、2016、2013、2010、2007 和 Office 365。

Excel的Kutools

上述功能只是Kutools for Excel的300個強大功能之一。

專為 Excel(Office) 2021、2019、2016、2013、2010、2007 和 Office 365 設計。免費下載和使用 30 天。

Kutools for Excel的屏幕截圖

btn了解更多      btn下載     btn購買

Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I do not know about others, but I still have it.
This comment was minimized by the moderator on the site
There is no "Make up a number" under Content in my Kutools, has this feature been removed?
This comment was minimized by the moderator on the site
Hi Alex Ramaker,
I am sorry for the inconvenience. Can you tell me which Kutools version are you using? If you can't find it under the Content group, please press the Alt + S keys to open the Kutools search box and type in "Make up a number", and then the feature name will be listed under the search box. Just click on it to activate the feature.
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/make_up_a_number.png
This comment was minimized by the moderator on the site
I am also missing features in the Content section including Make Up a Number and Data Association and using Alt + S does not give me an option to search functions. Version is 19.0. Kutools was downloaded to my work machine through my organization's catalog, is it possible certain features are blocked?
This comment was minimized by the moderator on the site
Hi Sylvia,

The Make Up a Number feature was released in version 23.00 and the Data Association feature was released in version 25.00.
You are using an old version of Kutools and It’s the lifetime license with the 2-year free update support.
After the 2-year free update support, you cannot update to any new versions but you can still use the old versions of the software.
After the 2-year free update support, you will need to purchase a new version to use the features in the new version.
This comment was minimized by the moderator on the site
The bug has not been fixed yet. Even though I select one output, and wait forever for the answer, I still get an error message stating that the tool can't handle more than 1,600+ answers. I did not ask for that many, I asked for *one*.
This comment was minimized by the moderator on the site
Hi Cathy,
Sorry for the convenience and thanks for your support.
Our developers are working on upgrading this feature to have a better and fast way to deal with big data. And the new version will be released soon.
Please stay tuned for our updates!
This comment was minimized by the moderator on the site
Was the bug fixed? What was great? Thank you.
This comment was minimized by the moderator on the site
This is so great, Thanks
This comment was minimized by the moderator on the site
Same here. The function gets hung-up when searing more than 1000 numbers. I do see a progress bar, but it continues to get stuck at 6%. I have been using their products for close to a decade; it would be nice if they fixed/improved this. I was told to install latest version. I wouldn't know how to install anything other than the latest version.
This comment was minimized by the moderator on the site
Hi Tony,
Sorry for the convenience and thanks for your support for close to a decade.
Our developers are working on upgrading this feature to have a better and fast way to deal with big data, and it will be avaliable in the next release.
By the way, do the 1000+ numbers you used include positive numbers, negative numbers and decimal numbers? Do you mind send us the sample file for testing via ?
This comment was minimized by the moderator on the site
Anyone able to get this to work? The Processing please wait is taking forever.. Don't want to cancel

This comment was minimized by the moderator on the site
I'm having this problem! Did you get it to work?
This comment was minimized by the moderator on the site
instead of "processing, please wait" it needs to have a counter which displays either how much time is estimated to be remaining to calculate or an actual counter of the calculations done and remaining to be counted. ie i have waited over 4 hours for the program to work out my 44C8 query (177m combinations) but i don't know whether the program has crashed or is still calculating.
This comment was minimized by the moderator on the site
Still going after another 6 hours (10hrs all up). i assume it has crashed and will force close the program.
This comment was minimized by the moderator on the site
ok just tried a 35C8 and it took just over 90mins, so maybe 44C8 takes 11.5hrs.
This comment was minimized by the moderator on the site
È possibile escludere delle combinazioni da quelle trovate ?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations