跳到主要内容

如何在Excel中查找等于给定总和的所有组合?

无论是出于预算、规划还是数据分析的目的,许多 Excel 用户可能会遇到一个挑战,即找出列表中所有可能的数字组合,使其总和达到特定总和。

在此示例中,我们有一个数字列表,目标是确定该列表中哪些组合的总和为 480。提供的屏幕截图显示有五组可能的组合可以达到此总和,包括 300+120 等组合+60、250+120+60+50 等等。在本文中,我们将探索各种方法来查明列表中总计为 Excel 中指定值的特定数字组合。

使用 Solver 函数查找等于给定总和的数字组合

获取等于给定总和的所有数字组合

使用VBA代码获取总和在一定范围内的所有数字组合


使用 Solver 函数查找等于给定总和的单元格组合

深入 Excel 中查找加起来等于特定数字的单元格组合可能看起来令人畏惧,但 Solver 插件使这一切变得轻而易举。我们将引导您完成设置求解器的简单步骤并找到正确的单元组合,使看似复杂的任务变得简单可行。

第 1 步:启用求解器插件

  1. 请进入 文件 > 附加选项 Excel选项 对话框,单击 加载项 从左窗格中,然后单击 Go 按钮。 看截图:
  2. 然后, 加载项 出现对话框,检查 求解器加载项 选项,然后单击 OK 成功安装此加载项。

步骤2:输入公式

激活 Solver 插件后,您需要在单元格 B11 中输入以下公式:

=SUMPRODUCT(B2:B10,A2:A10)
备注:在此公式中: B2:B10 是号码列表旁边的一列空白单元格,并且 A2:A10 是您使用的号码列表。

步骤 3:配置并运行 Solver 以获得结果

  1. 点击 时间 > 求解求解器参数 对话框中,在对话框中,请执行以下操作:
    • (1.)点击 按钮选择单元格 B11 你的公式所在的位置 设定目标 部分;
    • (2.)然后在 部分,选择 的价值,然后输入您的目标值 480 根据需要
    • (3.)在 通过更改可变单元格 部分,请点击 按钮选择单元格范围 B2:B10 在哪里标记您的相应数字。
    • (4.)然后,单击 地址 按钮。
  2. 然后, 添加约束 弹出对话框,点击 按钮选择单元格范围 B2:B10,然后选择 箱子 从下拉列表中。最后,点击 OK 按钮。 看截图:
  3. 求解器参数 对话框中,单击 解决 几分钟后, 求解结果 弹出对话框,可以看到B列中等于给定和480的单元格组合被标记为1。 求解结果 对话框,请选择 保持求解器解决方案 选项,然后单击 OK 退出对话框。 看截图:
备注:但是,此方法有一个限制:即使存在多个有效组合,它也只能识别加起来达到指定总和的单元格组合。

获取等于给定总和的所有数字组合

探索 Excel 的更深入功能可让您找到与特定总和匹配的每个数字组合,而且比您想象的要容易。本节将向您展示两种查找等于给定总和的所有数字组合的方法。

使用用户定义函数获取等于给定总和的所有数字组合

为了从特定集合中找出所有可能的数字组合,使其共同达到给定值,下面概述的自定义函数可以作为有效的工具。

第 1 步:打开 VBA 模块编辑器并复制代码

  1. 按住 ALT + F11 键,然后打开 Microsoft Visual Basic应用程序 窗口。
  2. 点击 插页 > 模块,然后将以下代码粘贴到“模块窗口”中。
    VBA代码:获取等于给定总和的所有数字组合
    Public Function MakeupANumber(xNumbers As Range, xCount As Long)
    'updateby Extendoffice
        Dim arrNumbers() As Long
        Dim arrRes() As String
        Dim ArrTemp() As Long
        Dim xIndex As Long
        Dim rg As Range
    
        MakeupANumber = ""
        
        If xNumbers.CountLarge = 0 Then Exit Function
        ReDim arrNumbers(xNumbers.CountLarge - 1)
        
        xIndex = 0
        For Each rg In xNumbers
            If IsNumeric(rg.Value) Then
                arrNumbers(xIndex) = CLng(rg.Value)
                xIndex = xIndex + 1
            End If
        Next rg
        If xIndex = 0 Then Exit Function
        
        ReDim Preserve arrNumbers(0 To xIndex - 1)
        ReDim arrRes(0)
        
        Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
        ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
        MakeupANumber = arrRes
    End Function
    
    Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
    
        Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
        Dim remainingNumbers() As Long, newCombination() As Long
        
        currentSum = 0
        If (Not Not ArrTemp) <> 0 Then
            For i = LBound(ArrTemp) To UBound(ArrTemp)
                currentSum = currentSum + ArrTemp(i)
            Next i
        End If
     
        If currentSum = Count Then
            indRes = UBound(arrRes)
            ReDim Preserve arrRes(0 To indRes + 1)
            
            arrRes(indRes) = ArrTemp(0)
            For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
                arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
            Next i
        End If
        
        If currentSum > Count Then Exit Sub
        If (Not Not Numbers) = 0 Then Exit Sub
        
        For i = 0 To UBound(Numbers)
            Erase remainingNumbers()
            num = Numbers(i)
            For j = i + 1 To UBound(Numbers)
                If (Not Not remainingNumbers) <> 0 Then
                    ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
                Else
                    ReDim Preserve remainingNumbers(0 To 0)
                End If
                remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
                
            Next j
            Erase newCombination()
    
            If (Not Not ArrTemp) <> 0 Then
                For k = 0 To UBound(ArrTemp)
                    If (Not Not newCombination) <> 0 Then
                        ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                    Else
                        ReDim Preserve newCombination(0 To 0)
                    End If
                    newCombination(UBound(newCombination)) = ArrTemp(k)
    
                Next k
            End If
            
            If (Not Not newCombination) <> 0 Then
                ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
            Else
                ReDim Preserve newCombination(0 To 0)
            End If
            
            newCombination(UBound(newCombination)) = num
    
            Combinations remainingNumbers, Count, newCombination, arrRes
        Next i
    
    End Sub
    

第二步:输入自定义公式得到结果

粘贴代码后,关闭代码窗口以返回到工作表。在空白单元格中输入以下公式输出结果,然后按 输入 获得所有组合的关键。看截图:

=MakeupANumber(A2:A10,B2)
备注:在此公式中: A2:A10 是号码列表,并且 B2 是您想要获得的总金额。

Tips::如果要在列中垂直列出组合结果,请应用以下公式:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
该方法的局限性:
  • 此自定义函数仅适用于 Excel 365 和 2021。
  • 该方法仅对正数有效;小数值会自动四舍五入到最接近的整数,负数将导致错误。

使用强大的功能获取等于给定总和的所有数字组合

鉴于上述功能的局限性,我们推荐一个快速且全面的解决方案:Kutools for Excel's 组成数字功能,它与任何版本的 Excel 兼容。这种替代方案可以有效地处理正数、小数和负数。通过此功能,您可以快速获得等于给定总和的所有组合。

Tips: 申请这个 组成一个数字 功能,首先,您应该下载 Kutools for Excel,然后快速轻松地应用该功能。
  1. 点击 库工具 > 内容 > 组成一个数字,请参见屏幕截图:
  2. 那么,在 补数 对话框,请单击 按钮从中选择要使用的号码列表 数据源,然后将总数输入到 总和 文本框。 最后,点击 OK 按钮,请参见屏幕截图:
  3. 然后会弹出提示框,提醒您选择一个单元格来定位结果,然后单击 OK,请参见屏幕截图:
  4. 现在,所有等于给定数字的组合都已显示,如下图所示:
备注: 要应用此功能,请 下载并安装 Kutools for Excel 第一。

使用VBA代码获取总和在一定范围内的所有数字组合

有时,您可能会发现自己需要识别所有可能的数字组合,这些数字加起来等于特定范围内的总和。例如,您可能想要找到总数在 470 到 480 之间的所有可能的数字分组。

在 Excel 中,发现总和为特定范围内的值的所有可能的数字组合是一项令人着迷且高度实用的挑战。本节将介绍用于解决此任务的 VBA 代码。

第 1 步:打开 VBA 模块编辑器并复制代码

  1. 按住 ALT + F11 键,然后打开 Microsoft Visual Basic应用程序 窗口。
  2. 点击 插页 > 模块,然后将以下代码粘贴到“模块窗口”中。
    VBA代码:获取总和达到特定范围的所有数字组合
    Sub Getall_combinations()
    'Updateby Extendoffice
        Dim xNumbers As Variant
        Dim Output As Collection
        Dim rngSelection As Range
        Dim OutputCell As Range
        Dim LowLimit As Long, HiLimit As Long
        Dim i As Long, j As Long
        Dim TotalCombinations As Long
        Dim CombTotal As Double
        Set Output = New Collection
        On Error Resume Next
        Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
        If rngSelection Is Nothing Then
            MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
            Exit Sub
        End If
        On Error GoTo 0
        xNumbers = rngSelection.Value
        LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
        HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
        On Error Resume Next
        Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
        If OutputCell Is Nothing Then
            MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
            Exit Sub
        End If
        On Error GoTo 0
        TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
        For i = 1 To TotalCombinations - 1
            Dim tempArr() As Double
            ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
            CombTotal = 0
            Dim k As Long: k = 0
            
            For j = 1 To UBound(xNumbers, 1)
                If i And (2 ^ (j - 1)) Then
                    k = k + 1
                    tempArr(k) = xNumbers(j, 1)
                    CombTotal = CombTotal + xNumbers(j, 1)
                End If
            Next j
            If CombTotal >= LowLimit And CombTotal <= HiLimit Then
                ReDim Preserve tempArr(1 To k)
                Output.Add tempArr
            End If
        Next i
        Dim rowOffset As Long
        rowOffset = 0
        Dim item As Variant
        For Each item In Output
            For j = 1 To UBound(item)
                OutputCell.Offset(rowOffset, j - 1).Value = item(j)
            Next j
            rowOffset = rowOffset + 1
        Next item
    End Sub
    
    
    

第2步:执行代码

  1. 粘贴代码后,按 F5 键运行此代码,在第一个弹出的对话框中,选择要使用的数字范围,然后单击 OK。 看截图:
  2. 在第二个提示框中,选择或输入下限数字,然后单击 OK。 看截图:
  3. 在第三个提示框中,选择或输入上限数,然后单击 OK。 看截图:
  4. 在最后一个提示框中,选择一个输出单元格,即开始输出结果的位置。然后点击 OK。 看截图:

结果

现在,每个合格组合都将从您选择的输出单元格开始列在工作表中的连续行中。

Excel 为您提供了多种方法来查找加起来达到特定总数的一组数字,每种方法的工作原理都不同,因此您可以根据您对 Excel 的熟悉程度以及您的项目需求来选择一种方法。如果您有兴趣探索更多 Excel 提示和技巧,我们的网站提供了数千个教程,请 单击此处访问它们。 感谢您的阅读,我们期待在未来为您提供更多有用的信息!


相关文章:

  • 列出或生成所有可能的组合
  • 假设我有以下两列数据,现在,我想基于两个值列表生成所有可能组合的列表,如左图所示。 也许,如果值很少,则可以一一列出所有组合,但是,如果有几列需要列出多个值,可能的组合就可以通过下面的一些快速技巧来解决。 。
  • 列出单列中所有可能的组合
  • 如果您想从单列数据中返回所有可能的组合以获得如下屏幕截图所示的结果,您是否有任何快速的方法可以在 Excel 中处理此任务?
  • 生成 3 列或多列的所有组合
  • 假设我有3列数据,现在,我想在这3列中生成或列出数据的所有组合,如下图所示。 您有什么好的方法可以在Excel中解决此任务?
  • 生成所有可能的 4 位数字组合的列表
  • 在某些情况下,我们可能需要生成一个由4到0的所有可能的9位数字组成的列表,这意味着要生成一个0000、0001、0002…9999的列表。 为了快速解决Excel中的列表任务,我为您介绍了一些技巧。
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