Bỏ qua nội dung chính

Làm thế nào để tìm tất cả các kết hợp bằng một tổng đã cho trong Excel?

Việc khám phá tất cả các tổ hợp số có thể có trong một danh sách có tổng thành một số tiền cụ thể là một thách thức mà nhiều người dùng Excel có thể gặp phải, cho dù vì mục đích lập ngân sách, lập kế hoạch hay phân tích dữ liệu.

Trong ví dụ này, chúng tôi có một danh sách các số và mục tiêu là xác định các kết hợp nào từ danh sách này có tổng bằng 480. Ảnh chụp màn hình được cung cấp chứng minh rằng có năm nhóm kết hợp có thể đạt được tổng này, bao gồm các kết hợp như 300+120 +60, 250+120+60+50, trong số những thứ khác. Trong bài viết này, chúng ta sẽ khám phá các phương pháp khác nhau để xác định các tổ hợp số cụ thể trong danh sách có tổng giá trị được chỉ định trong Excel.

Tìm tổ hợp số bằng tổng cho trước bằng hàm Solver

Nhận tất cả các kết hợp số bằng một tổng nhất định

Nhận tất cả các kết hợp số có tổng trong một phạm vi bằng mã VBA


Tìm tổ hợp ô bằng một tổng nhất định bằng hàm Solver

Đi sâu vào Excel để tìm các tổ hợp ô cộng với một số cụ thể có vẻ khó khăn, nhưng Bổ trợ Bộ giải khiến việc này trở nên dễ dàng. Chúng tôi sẽ hướng dẫn bạn các bước đơn giản để thiết lập Bộ giải và tìm tổ hợp ô phù hợp, khiến những việc tưởng chừng như phức tạp trở nên đơn giản và có thể thực hiện được.

Bước 1: Kích hoạt bổ trợ bộ giải

  1. Xin vui lòng đến Tập tin > Các lựa chọn, Trong Tùy chọn Excel hộp thoại, nhấn Add-Ins từ khung bên trái, sau đó nhấp vào Go cái nút. Xem ảnh chụp màn hình:
  2. Sau đó, Add-Ins hộp thoại xuất hiện, hãy kiểm tra Phần bổ trợ Solver và nhấp vào OK để cài đặt thành công bổ trợ này.

Bước 2: Nhập công thức

Sau khi kích hoạt bổ trợ Solver, bạn cần nhập công thức này vào ô B11:

=SUMPRODUCT(B2:B10,A2:A10)
Chú thích: Trong công thức này: B2: B10 là một cột gồm các ô trống bên cạnh danh sách số của bạn và A2: A10 là danh sách số mà bạn sử dụng.

Bước 3: Cấu hình và chạy Solver để nhận kết quả

  1. Nhấp chuột Ngày > Người giải quyết đi đến Tham số bộ giải , trong hộp thoại, vui lòng thực hiện các thao tác sau:
    • (1.) Nhấp vào nút để chọn ô B11 công thức của bạn nằm ở đâu từ Đặt mục tiêu phần;
    • (2.) Sau đó, trong Đến phần, chọn Giá trị củavà nhập giá trị mục tiêu của bạn 480 như bạn cần;
    • (3.) Dưới Bằng cách thay đổi các ô biến phần, vui lòng nhấp vào nút để chọn phạm vi ô B2: B10 nơi sẽ đánh dấu các số tương ứng của bạn.
    • (4.) Sau đó, nhấp vào Thêm .
  2. Sau đó, một Thêm ràng buộc hộp thoại hiển thị, bấm vào nút để chọn phạm vi ô B2: B10, Và chọn bin từ danh sách thả xuống. Cuối cùng, nhấp vào OK cái nút. Xem ảnh chụp màn hình:
  3. Trong tạp chí Tham số bộ giải hộp thoại, nhấp vào Giải quyết , vài phút sau, Giải kết quả hộp thoại xuất hiện và bạn có thể thấy sự kết hợp của các ô bằng tổng 480 đã cho được đánh dấu là 1 trong cột B. Trong hộp thoại Giải kết quả hộp thoại, vui lòng chọn Giải pháp Keep Solver và nhấp vào OK để thoát khỏi hộp thoại. Xem ảnh chụp màn hình:
Chú thích: Tuy nhiên, phương pháp này có một hạn chế: nó chỉ có thể xác định một tổ hợp ô cộng lại với tổng đã chỉ định, ngay cả khi tồn tại nhiều tổ hợp hợp lệ.

Nhận tất cả các kết hợp số bằng một tổng nhất định

Khám phá các chức năng sâu hơn của Excel cho phép bạn tìm thấy mọi tổ hợp số khớp với một tổng cụ thể và việc này dễ dàng hơn bạn nghĩ. Phần này sẽ chỉ cho bạn hai phương pháp để tìm tất cả các tổ hợp số bằng một tổng cho trước.

Nhận tất cả các tổ hợp số bằng một tổng nhất định với Hàm do người dùng xác định

Để khám phá mọi tổ hợp số có thể có từ một tập hợp cụ thể đạt đến một giá trị nhất định, hàm tùy chỉnh được nêu bên dưới đóng vai trò là một công cụ hiệu quả.

Bước 1: Mở trình chỉnh sửa mô-đun VBA và sao chép mã

  1. Giữ phím tắt ALT + F11 các phím trong Excel và nó sẽ mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
  2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.
    Mã VBA: Nhận tất cả các kết hợp số bằng một tổng nhất định
    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
    

Bước 2: Nhập công thức tùy chỉnh để được kết quả

Sau khi dán mã, hãy đóng cửa sổ mã để quay lại bảng tính. Nhập công thức sau vào ô trống để xuất kết quả, sau đó nhấn đăng ký hạng mục thi chìa khóa để có được tất cả các kết hợp. Xem ảnh chụp màn hình:

=MakeupANumber(A2:A10,B2)
Chú thích: Trong công thức này: A2: A10 là danh sách số và B2 là tổng số tiền bạn muốn nhận được.

Mẹo: Nếu bạn muốn liệt kê các kết quả kết hợp theo chiều dọc trong một cột thì hãy áp dụng công thức sau:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
Hạn chế của phương pháp này:
  • Chức năng tùy chỉnh này chỉ hoạt động trong Excel 365 và 2021.
  • Phương pháp này chỉ có hiệu quả đối với số dương; các giá trị thập phân được tự động làm tròn đến số nguyên gần nhất và số âm sẽ dẫn đến lỗi.

Nhận tất cả các tổ hợp số bằng một tổng nhất định với tính năng mạnh mẽ

Do những hạn chế của chức năng nói trên, chúng tôi đề xuất một giải pháp nhanh chóng và toàn diện: tính năng Tạo số của Kutools cho Excel, tương thích với mọi phiên bản Excel. Giải pháp thay thế này có thể xử lý hiệu quả các số dương, số thập phân và số âm. Với tính năng này, bạn có thể nhanh chóng nhận được tất cả các kết hợp bằng một tổng nhất định.

Lời khuyên: Để áp dụng điều này Tạo thành một con số tính năng, trước tiên, bạn nên tải xuống Kutools cho Excel, và sau đó áp dụng tính năng một cách nhanh chóng và dễ dàng.
  1. Nhấp chuột Kutools > Nội dung > Tạo thành một con số, xem ảnh chụp màn hình:
  2. Sau đó, trong Tạo một số hộp thoại, vui lòng nhấp vào để chọn danh sách số mà bạn muốn sử dụng từ Nguồn dữ liệu, rồi nhập tổng số vào Tổng hộp văn bản. Cuối cùng, bấm vào OK nút, xem ảnh chụp màn hình:
  3. Sau đó, một hộp nhắc sẽ bật ra để nhắc bạn chọn một ô để tìm kết quả, sau đó nhấp vào OK, xem ảnh chụp màn hình:
  4. Và bây giờ, tất cả các kết hợp bằng với số đã cho đó đã được hiển thị như ảnh chụp màn hình bên dưới:
Chú thích: Để áp dụng tính năng này, vui lòng tải xuống và cài đặt Kutools cho Excel đầu tiên.

Nhận tất cả các kết hợp số có tổng trong một phạm vi bằng mã VBA

Đôi khi, bạn có thể rơi vào tình huống cần xác định tất cả các tổ hợp số có thể có để cộng lại thành một tổng trong một phạm vi cụ thể. Ví dụ: bạn có thể đang tìm mọi nhóm số có thể có trong đó tổng số nằm trong khoảng từ 470 đến 480.

Việc khám phá tất cả các tổ hợp số có thể có tổng thành một giá trị trong một phạm vi cụ thể là một thách thức hấp dẫn và mang tính thực tế cao trong Excel. Phần này sẽ giới thiệu mã VBA để giải quyết nhiệm vụ này.

Bước 1: Mở trình chỉnh sửa mô-đun VBA và sao chép mã

  1. Giữ phím tắt ALT + F11 các phím trong Excel và nó sẽ mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
  2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.
    Mã VBA: Nhận tất cả các kết hợp số có tổng bằng một phạm vi cụ thể
    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
    
    
    

Bước 2: Thực thi mã

  1. Sau khi dán mã, nhấn F5 để chạy mã này, trong hộp thoại xuất hiện đầu tiên, hãy chọn dãy số bạn muốn sử dụng và nhấp vào OK. Xem ảnh chụp màn hình:
  2. Trong hộp nhắc thứ hai, hãy chọn hoặc nhập số giới hạn thấp rồi bấm vào OK. Xem ảnh chụp màn hình:
  3. Trong hộp nhắc thứ ba, hãy chọn hoặc nhập số giới hạn cao rồi bấm vào OK. Xem ảnh chụp màn hình:
  4. Trong hộp nhắc cuối cùng, hãy chọn ô đầu ra, đây là nơi kết quả sẽ bắt đầu được xuất ra. Sau đó nhấn vào OK. Xem ảnh chụp màn hình:

Kết quả

Bây giờ, mỗi kết hợp đủ điều kiện sẽ được liệt kê theo các hàng liên tiếp trong bảng tính, bắt đầu từ ô đầu ra bạn đã chọn.

Excel cung cấp cho bạn một số cách để tìm các nhóm số có tổng bằng một tổng nhất định, mỗi phương pháp hoạt động khác nhau nên bạn có thể chọn một phương pháp dựa trên mức độ quen thuộc của bạn với Excel và những gì bạn cần cho dự án của mình. Nếu bạn muốn khám phá thêm các mẹo và thủ thuật Excel, trang web của chúng tôi cung cấp hàng nghìn bài hướng dẫn, vui lòng bấm vào đây để truy cập chúng. Cảm ơn bạn đã đọc và chúng tôi mong muốn cung cấp cho bạn nhiều thông tin hữu ích hơn trong tương lai!


Bài viết liên quan:

  • Liệt kê hoặc tạo ra tất cả các kết hợp có thể
  • Giả sử, tôi có hai cột dữ liệu sau và bây giờ, tôi muốn tạo danh sách tất cả các kết hợp có thể có dựa trên hai danh sách giá trị như hình minh họa bên trái. Có thể, bạn có thể liệt kê tất cả các kết hợp lần lượt nếu có ít giá trị, nhưng nếu có một số cột có nhiều giá trị cần được liệt kê các kết hợp có thể có, thì đây là một số thủ thuật nhanh có thể giúp bạn giải quyết vấn đề này trong Excel .
  • Tạo tất cả các kết hợp của 3 hoặc nhiều cột
  • Giả sử, tôi có 3 cột dữ liệu, bây giờ, tôi muốn tạo hoặc liệt kê tất cả các kết hợp dữ liệu trong 3 cột này như hình minh họa bên dưới. Bạn có phương pháp nào hay để giải quyết công việc này trong Excel không?
  • Tạo danh sách tất cả các kết hợp 4 chữ số có thể
  • Trong một số trường hợp, chúng tôi có thể cần tạo danh sách tất cả các tổ hợp 4 chữ số có thể có của số 0 đến 9, nghĩa là tạo danh sách 0000, 0001, 0002… 9999. Để giải quyết nhanh nhiệm vụ danh sách trong Excel, tôi giới thiệu một số thủ thuật cho bạn.
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