提示:其它语言是由 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加载项,可以在60天免费试用。 下载并免费试用现在!


查找与公式相等的给定总和的单元格组合

首先,您需要创建一些范围名称,然后应用数组公式来查找总和为目标值的单元格,请执行以下一步一步:

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。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
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 · 25 days 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 · 22 days 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 · 3 months ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 5 months ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 6 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 · 7 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 · 10 months 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 · 18 days 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 · 18 days 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 · 7 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 · 10 months 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 · 1 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 · 2 years ago
    Superb Man!!! Superb Man!!!
  • To post as a guest, your comment is unpublished.
    Thom · 2 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.