Note: The other languages of the website are Google-translated. Back to English

如何在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,現在,已顯示等於該給定數字的所有組合,如下所示的屏幕截圖:

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


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


最佳辦公效率工具

Kutools for Excel 解決了你的大部分問題,並將你的生產力提高了 80%

  • 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 超過 300 項強大的功能. 支持 Office / Excel 2007-2021 和 365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能 30 天免費試用。 60 天退款保證。
kte選項卡201905

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

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
按評論排序
留言 (47)
還沒有評分。 成為第一位評論!
網站主持人對此評論進行了最小化
有沒有辦法擴大範圍,使其包含超過 8 個數字? 另外,我不確定這個函數是如何工作的:“=ROW(INDIRECT("1:"&2^ROWS(Range1)))”。 如果我嘗試將“Range1”擴展到 15 行以上,我會收到 #Ref 錯誤。 它只適用於 8 個數字,但如果你想包含 50 個數字甚至 100 個數字怎麼辦。
網站主持人對此評論進行了最小化
同一期
網站主持人對此評論進行了最小化
好男人!!! 好男人!!!
網站主持人對此評論進行了最小化
有沒有辦法像 Thom 所說的那樣擴大範圍,最多可以說 50 個數字,但也只能在總和為指定總數的範圍之外總計六個數字? 目前它將提供總計到指定總數的所有組合。 謝謝
網站主持人對此評論進行了最小化
驚人的。 無法使大型公式起作用,但求解器插件運行良好。 為我節省了很多工作。
網站主持人對此評論進行了最小化
但這需要太多時間
網站主持人對此評論進行了最小化
我充其量是 Excel 的高級初學者。 我嘗試了一切,但沒有奏效。 我可能做錯了什麼?
網站主持人對此評論進行了最小化
如果我需要多個組合怎麼辦? 謝謝你
網站主持人對此評論進行了最小化
你好。 公式版本對我也不起作用。 感覺好像少了一步。 我看不到單元格 C2 中指定的數字進入公式的位置。

謝謝
網站主持人對此評論進行了最小化
你好,多莉,


C2 中沒有公式,它只是您想要將數字相加的特定值。
網站主持人對此評論進行了最小化
嗨,我下載了 Kutools 但無法找到所有小於指定總數的組合。
網站主持人對此評論進行了最小化
我能夠讓 Range1 的示例在 12 行中使用我的範圍,但是當我將範圍更改為 42 行時它不起作用。 我什至用 42 行版本重新啟動了整個過程,但也沒有用。 有任何想法嗎?
網站主持人對此評論進行了最小化
輝煌!!!
網站主持人對此評論進行了最小化
可以上傳excel嗎?
網站主持人對此評論進行了最小化
如果有多個解決方案,則宏不起作用。
另外,如果我找到“0”,我就沒有工作
網站主持人對此評論進行了最小化
你好,洛麗娜,
在應用上述 VBA 代碼之前,您必須先按降序對數字列表進行排序。
其次,代碼無法正常工作以獲取總數 0。
希望對你有幫助,謝謝!
網站主持人對此評論進行了最小化
我正在嘗試確定產品的最佳組合,但不確定這是否是最好的方法。 我最多混合使用三種產品,每種產品有 5 種規格。 所有規格都是線性的,可以在混合時取平均值。 一種混合物通常為 45,000 磅,每批為 30,000 磅。 大多數時候我們的混合是 15k+30k,但我希望能夠使用一直到 2000lbs 的增量來計算不尋常的混合。
網站主持人對此評論進行了最小化
嗨,

我對這個公式的問題是它給了我一個值足夠多次來獲得目標值..
在不同值的列表中,有一些值彼此相等。

例如,我有 0,16 次 3(列表中的第一個值),公式給出的答案是我的目標值是 593 中的 0,16。

為什麼它不結合不同的值來獲得我的目標值? 它只選擇一個值並給出它是目標值的次數。

有什麼幫助或想法嗎?


謝謝!
網站主持人對此評論進行了最小化
有誰知道這是否適用於谷歌表格
網站主持人對此評論進行了最小化
是的,有一個類似於 excel 求解器的擴展名為“求解器”
網站主持人對此評論進行了最小化
有人知道如何調整 VBA Getcombination 函數以使不允許重複嗎?

例如,對於數字 1,2,3,4,5,13,如果要達到 14,則 1,13 是一個解決方案,而不是 14 中的 1。
網站主持人對此評論進行了最小化
函數 GetCombination(CoinsRange As Range, SumCellId As Double) As String
'更新 Extendoffice 20160506
將 xStr 調暗為字符串
Dim xSum 為 Double
將 xCell 調暗為範圍
xSum = SumCellId
對於 CoinsRange 中的每個 xCell
如果不是 (xSum / xCell < 1) 那麼
xStr = xStr & "1 of " & xCell & " "
xSum = xSum - xCell
如果結束
下一頁
獲取組合 = xStr
函數結束
網站主持人對此評論進行了最小化
嗨,它給了我 vba 代碼的模棱兩可的名稱錯誤
任何幫助,因為我對 VBA 一無所知
網站主持人對此評論進行了最小化
嗨,拉姆,這很好用,但沒有給出實際的總和。
例如:如果我有 23,34,25,28,10,17&12 並且我有 80 的總和(這是 23,28,17&12 的總和),我需要一個可以找到這個組合的 vba 代碼(總和 23,28,17 ,12&XNUMX) 你能幫我解決這個問題嗎?
網站主持人對此評論進行了最小化
你好,
非常感謝您的信息;
如果沒有確切的值,如何找到最近似的組合。
非常感謝,
網站主持人對此評論進行了最小化
你好,
謝謝這個非常好
如果沒有確切的值,如何找到最近似的組合。
非常感謝
網站主持人對此評論進行了最小化
如果列表中有負數或 number 的值為 0,求解器加載項會不起作用嗎? 我試圖在列表中找到等於零的數字總和,其中一些數字是負數和正數,但求解器不起作用。 我更改了列表中的幾個數字以進行測試,以確保我正確地遵循了這些步驟並且它確實適用於測試。 請告知是否有辦法用負數和正數解決以找到0值。
L     a
網站主持人對此評論進行了最小化
你有沒有得到答案或者你有沒有找到辦法做到這一點?
網站主持人對此評論進行了最小化
你好,
如果列中同時存在正數和負數,我建議您應用 Kutools for Excel 的補數功能,它可以快速輕鬆地解決您的問題。

您可以下載 Kutools for Excel 並免費試用 60 天。 請試試!
網站主持人對此評論進行了最小化
我有 1162 個單元格可以找到數字 x。 Excel 告訴我變量單元格太多。 非常小的數據集! 有什麼建議麼? 謝謝!
這裡還沒有評論
載入更多
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點

關注我們

版權所有©2009 - 萬維網。extendoffice.com。 | 版權所有。 供電 ExtendOffice。 |
Microsoft和Office徽標是Microsoft Corporation在美國和/或其他國家的商標或註冊商標。
受Sectigo SSL保護