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
- 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:
- 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)
Bước 3: Cấu hình và chạy Solver để nhận kết quả
- 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 .
- 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:
- 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:
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ã
- 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ổ.
- 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 địnhPublic 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)
=TRANSPOSE(MakeupANumber(A2:A10,B2))
- 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.
- Nhấp chuột Kutools > Nội dung > Tạo thành một con số, xem ảnh chụp màn hình:
- 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:
- 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:
- 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:
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ã
- 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ổ.
- 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ã
- 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:
- 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:
- 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:
- 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 .
- Liệt kê tất cả các kết hợp có thể có từ một cột
- Nếu bạn muốn trả lại tất cả các kết hợp có thể có từ dữ liệu cột đơn để nhận được kết quả như hình ảnh chụp màn hình bên dưới, bạn có cách nào nhanh chóng để giải quyết tác vụ này trong Excel không?
- 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.
Công cụ năng suất văn phòng tốt nhất
Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian. Bấm vào đây để có được tính năng bạn cần nhất...
Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!