提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中找到所有等於給定總和的組合?

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


在Excel中快速輕鬆地查找並列出等於給定總和的所有組合

Kutools for Excel's 彌補一個數字 實用程序可以幫助您快速,輕鬆地查找和列出等於給定總數的所有組合和特定組合。 點擊下載Kutools for Excel!

Kutools for Excel:具有超過300個便捷的Excel加載項,可以在30天之內不受限制地免費試用。 下載並免費試用現在!


查找與公式相等的給定總和的單元格組合

首先,您需要創建一些範圍名稱,然後應用數組公式來查找總和為目標值的單元格,請執行以下一步一步:

1。 選擇號碼列表並定義這個列表的範圍名稱 - Range1名稱框,然後按 輸入 鍵完成定義的範圍名稱,參見截圖:

2。 定義號碼列表的範圍名稱後,您需要在中創建另外兩個範圍名稱 名稱經理 框,請點擊 公式 > 名稱經理名稱經理 對話框,單擊 按鈕,看截圖:

3。 在彈出 新名字 對話框中輸入一個名稱 List1名稱 字段,然後鍵入此公式 = ROW(INDIRECT(“1:”&ROWS(Range1))) (Range1 是您在step1中創建的範圍名稱) 字段,看截圖:

4。 點擊 OK 回到了 名稱經理 對話框,然後繼續點擊 按鈕來創建另一個範圍名稱 新名字 對話框中輸入一個名稱 List2名稱 字段,然後鍵入此公式 = ROW(INDIRECT(“1:”&2 ^ ROWS(Range1))) (Range1 是您在step1中創建的範圍名稱) 字段,看截圖:

5。 創建範圍名稱後,請將以下數組公式應用到B1單元格中:

=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 + Enter 然後將填充手柄拖到單元格B8(列表的最後一個數字),您可以在列B中看到總數為480的數字標記為X,請參見屏幕截圖:

  • 筆記:
  • 在上面的長公式中: List1, List2 - Range1 是您在前面的步驟中創建的範圍名稱, C2 是你想要的數字加起來的具體價值。
  • 如果多個值組合的總和等於特定值,則僅列出一個組合。

查找與求解器加載項相等的給定和的單元組合

如果您對上述方法感到困惑,Excel將包含一個 求解器加載項 功能,通過使用此加載項,您還可以識別總金額等於給定值的數字。

1. 首先,你需要激活這個 求解 添加請去 文件 > 選項Excel選項 對話框,單擊 加載項 從左側窗格中單擊,然後單擊 求解器加載項 來自 不活動的應用程序加載項 部分,看截圖:

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

3。 在激活Solver加載項之後,您需要將此公式輸入到單元格B9中: = SUMPRODUCT(B2:B9,A2:A9)B2:B9 是您的號碼列表旁邊的空白列單元格 A2:A9 是您使用的號碼列表。 ),然後按 輸入 鍵,看截圖:

4. 然後點擊 數據 > 求解求解器參數 對話框中,請在對話框中進行以下操作:

(1。)單擊 按鈕選擇單元格 B10 你的公式來自哪裡? 設定目標 部分;

(2。)然後在 部分,選擇 的價值,並輸入您的目標值 480 如你所需;

(3。)下 通過更改可變單​​元格 部分,請點擊 按鈕選擇單元格範圍 B2:B9 在哪裡會標記你的相應號碼。

5. 然後點擊 添加 按鈕去 添加約束 對話框,單擊 按鈕選擇單元格範圍 B2:B9,然後選擇 箱子 從下拉列表中,看截圖:

6。 點擊 OK 回去了 求解器參數 對話框,然後單擊 解決 按鈕,幾分鐘後,a 求解器結果 彈出對話框,您可以看到等於給定和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) 放入空白單元格中,然後按 輸入 鍵,您將得到以下結果,顯示等於給定總和的組合數字,請參見截圖:

  • 筆記:
  • 在上面的公式中, A2:A9 是數字範圍,和 C2 包含您想要的目標值。
  • 如果多個值組合的總和等於特定值,則僅列出一個組合。

找到與給定總和相等的所有組合,並具有驚人的功能

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

提示:申請這個 彌補一個數字 功能,首先,你應該下載 Kutools for Excel,然後快速輕鬆地應用該功能。

安裝後 Kutools for Excel,請這樣做:

1。 點擊 Kutools > 內容 > 彌補一個數字,看截圖:

2。 然後,在 彌補一個數字 對話框,請點擊 按鈕,從中選擇要使用的數字列表 數據源,然後輸入總數 總和 文本框,看截圖:

3。 然後點擊 OK 按鈕,會彈出一個提示框,提醒您選擇一個單元格來查找結果,見截圖:

4。 然後,點擊 OK,現在,所有與給定數字相等的組合都顯示在下面的屏幕截圖中:

點擊下載Kutools for Excel和免費試用版吧!


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


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

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

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jeremy · 3 months ago
    How come i can not use the Make up a Number in Kutools for numbers with decimals?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hello, Jeremy,
      So far, this Make up a number feature can not support the decimals, but, you can apply it with a workaround.
      First, you can enlarge all the decimal numbers as whole numbers, such as multiply 100 to all the decimal numbers, and then apply this Make up a number feature, after getting the result, you should divide 100 to these numbers for returning them back to decimal numbers.
      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Feroz · 6 months ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 7 months ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 9 months ago
    Thank you so much for the VBA coding, it has solved a major headache trying to find combinations to equal an exact amount.
  • To post as a guest, your comment is unpublished.
    Stephanie · 10 months ago
    I have 1162 cells to find number x. Excel tells me that is too many variable cells. Very small data set! Any suggestions? Thanks!
  • To post as a guest, your comment is unpublished.
    a · 1 years ago
    Will the solver add-in not work if there are negative numbers in the list or if the value of number is 0? I'm trying to find a sum of numbers in a list that equate to zero with some numbers being negative and positive, but the solver does not work. I changed a couple numbers on my list to test to make sure I followed the steps correctly and it did work for the test. Please advise if there is a way to solve with negative and positive numbers to find a 0 value.
    • To post as a guest, your comment is unpublished.
      L · 3 months ago
      did you ever get an answer or did you find a way to do this?
      • To post as a guest, your comment is unpublished.
        skyyang · 3 months ago
        Hello,
        If there are both positive and negative numbers in the column, I recommend you apply the Kutools for Excel's Make up a number feature, it can solve your problem quickly and easily.

        You can download Kutools for Excel and free trial 60 days. Please try!
  • To post as a guest, your comment is unpublished.
    Fattir · 1 years ago
    Hello,
    Thanks this is very good,
    How can find the most approximate combinations if there is no exact value.
    Many thanks
  • To post as a guest, your comment is unpublished.
    Fattir · 1 years ago
    Hello,
    Many thanks for information;
    How can find the most approximate combinations if there is no exact value.
    Many thanks,
  • To post as a guest, your comment is unpublished.
    Igor Wilk · 1 years ago
    Would somebody know how to adjust the VBA Getcombination function so that no repetition should be allowed?

    For example, for numbers 1,2,3,4,5,13 if 14 is to be achieved than 1,13 is a solution, and not 14 of 1.
    • To post as a guest, your comment is unpublished.
      Ram · 1 years ago
      Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
      'updateby Extendoffice 20160506
      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 & "1 of " & xCell & " "
      xSum = xSum - xCell
      End If
      Next
      GetCombination = xStr
      End Function
      • To post as a guest, your comment is unpublished.
        Shashanth · 10 months ago
        Hi Ram, this works fine but doesnot give the actual sum.
        EX: if i have 23,34,25,28,10,17&12 and i have a sum of 80(which is the sum of 23,28,17&12), I need a vba code which can find this combination (sum of 23,28,17&12) Can you please help me with this ?
      • To post as a guest, your comment is unpublished.
        ddddddd7 · 1 years ago
        hi it is giving me ambigious name error for the vba code
        any help cause i know nothing in VBA
  • To post as a guest, your comment is unpublished.
    alex · 1 years ago
    does anyone know if this works on google sheets
  • To post as a guest, your comment is unpublished.
    epp · 1 years ago
    Hi,

    My drouble with this formula is that it gives me one value for enough times to get the target value..
    In the list of different values there are some values which are equal to each other.

    E.g. I have 0,16 for 3 times(the first values in the list) and the formula gives me the answer that my target value is 593 of 0,16.

    Why does it not combine different values to get my target value? It only chooses one value and gives how many times it is to be the target value.

    Any help or idea?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Dana · 1 years ago
    I am trying to determine the best blend of product and am unsure if this is the best way to do it. At most I use three products in a blend with 5 specifications each. All of the specifications are linear and can be averaged when blended. One blend is usually 45,000lbs and each batch is 30,000lbs. Most of the time our blends are 15k+30k but I would like to be able to calculate for the unusual blends using the increments all the way down to 2000lbs.
  • To post as a guest, your comment is unpublished.
    Lorena · 1 years ago
    The macro didn't work if there are more than one solution.
    Also, I didn't work if I find "0"
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello,Lorena,
      Before applying the above VBA code, you must sort the number list in descending order first.
      Second, the code is not work correctly to get the total number 0.
      Hope it can help you, thank you!
  • To post as a guest, your comment is unpublished.
    laura · 1 years ago
    Could you upload the excel?
  • To post as a guest, your comment is unpublished.
    Ruchir · 1 years ago
    Brilliant!!!
  • To post as a guest, your comment is unpublished.
    LL · 2 years ago
    I was able to get the example with Range1 to work with my range in 12 rows, but when I changed the range to 42 rows it did not work. I even restarted the entire process with the 42 row version and that didn't work either. Any ideas?
  • To post as a guest, your comment is unpublished.
    WL · 2 years ago
    HI, I downloaded Kutools but cannot get it to find all the combos less than a specified total.
  • To post as a guest, your comment is unpublished.
    Dori · 2 years ago
    Hi. The formula version didn't work for me either. It feels like it is missing a step. I do not see where the number specified in cell C2 comes into the formula.

    Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Dori,


      There is no formula in C2, it is just the specific value that you want numbers added up to.
  • To post as a guest, your comment is unpublished.
    tarra · 2 years ago
    how if i need more than one combination? thank you
  • To post as a guest, your comment is unpublished.
    DJ · 2 years ago
    I'm at best a advanced beginner at Excel. I tried everything and it didn't work. What could I be doing wrong?
  • To post as a guest, your comment is unpublished.
    Alan · 2 years ago
    Awesome. Couldn't get the large formula to work but the solver add-in worked perfectly. Saved me so much work.
  • To post as a guest, your comment is unpublished.
    Rick · 2 years ago
    Is there a way to expand the range as Thom says, to say up to 50 numbers, but to also only total six of the numbers out of the range that sum to the specified total? Currently it will provide all combinations that total to the specified total.

    thanks
  • To post as a guest, your comment is unpublished.
    nitin · 3 years ago
    Superb Man!!! Superb Man!!!
  • To post as a guest, your comment is unpublished.
    Thom · 3 years ago
    Is there a way to expand the range so that it includes more than 8 numbers? Also, I'm not sure how this function is working: "=ROW(INDIRECT("1:"&2^ROWS(Range1)))". If I try to expand "Range1" beyond 15 rows, I get an #Ref error. It works great with just the 8 numbers, but what if you wanted to include, say, 50 numbers or even 100.