3 cách để tìm tất cả các kết hợp từ một tập hợp số có tổng thành một tổng cụ thể.
Tìm sự kết hợp của các giá trị cộng lại thành một tổng nhất định là một vấn đề phổ biến trong phân tích dữ liệu. Ví dụ: bạn có thể muốn biết tất cả các kết hợp có thể có của các mặt hàng có thể được mua với ngân sách nhất định hoặc tất cả các cách có thể để phân bổ nguồn lực nhằm đáp ứng các yêu cầu nhất định. Trong bài viết này, chúng ta sẽ khám phá cách sử dụng Excel Solver và VBA để hoàn thành nhiệm vụ.
Tìm tổ hợp số bằng tổng cho trước bằng Excel Solver
Đáng tiếc là không có hàm Excel sẵn có nào có thể giúp bạn xác định các số cộng lại thành một tổng nhất định. May mắn thay, Excel cung cấp một phần bổ trợ đặc biệt để giải các bài toán lập trình tuyến tính. Phần bổ trợ Bộ giải được bao gồm trong tất cả các phiên bản Excel nhưng không được bật theo mặc định. Nếu bạn chưa quen với công cụ này thì đây là bài viết hay về cách thêm và sử dụng Solver trong Excel.
Với phần bổ trợ Bộ giải được kích hoạt trong Excel của bạn, hãy tiến hành các bước sau:
1. Tạo mô hình.
Để làm điều này, hãy nhập tập hợp số của bạn vào một cột (A3:A12) và thêm một cột trống ở bên phải các số của bạn để có kết quả (B3:B12). Trong một ô riêng biệt (B13), nhập công thức SUMPRODVEL tương tự như sau:
=SUMPRODUCT(A3:A12, B3:B12)
2. Chạy phần bổ trợ Solver
Trên tab Data, trong nhóm Analysis, bấm vào nút Solver.
3. Xác định vấn đề cho Solver.
Trong hộp thoại Solver Parameters, hãy đặt cấu hình các ô mục tiêu và biến:
-
Trong hộpObjective, nhập địa chỉ của ô công thức (B13).
-
Trong phần To, chọn Value Of và nhập giá trị tổng mong muốn (50 trong ví dụ này).
-
Trong hộp By Changing Variable Cells, hãy chọn phạm vi cần điền kết quả (B3:B12).
4. Thêm các constraints.
Để chỉ định các ràng buộc, tức là các hạn chế hoặc điều kiện phải được đáp ứng, hãy nhấp vào nút Add. Trong cửa sổ hộp thoại Add Constraint, chọn phạm vi kết quả (B3:B12) và chọn thùng từ danh sách thả xuống. Ràng buộc sẽ được tự động đặt thành nhị phân. Khi hoàn tất, hãy nhấp vào OK.
5. Giải quyết vấn đề.
Khi được đưa trở lại cửa sổ hộp thoại Solver Parameter, hãy xem lại cài đặt của bạn và nhấp vào nút Solve.
Vài giây (hoặc vài phút) sau, hộp thoại Solver Results sẽ xuất hiện. Nếu thành công, hãy chọn tùy chọn Keep Solver Solution và bấm OK để thoát khỏi hộp thoại.
Kết quả là bạn sẽ có 1 được chèn bên cạnh các số có tổng bằng tổng đã chỉ định. Đây không phải là giải pháp thân thiện với người dùng nhưng là giải pháp tốt nhất mà Excel có thể làm được.
Để trực quan hóa, đã đánh dấu các ô có tổng mong muốn bằng màu xanh lục nhạt:
Giới hạn: Bộ giải Excel chỉ có thể tìm thấy nhiều nhất một tổ hợp số bằng một tổng cụ thể.
Tìm tất cả các kết hợp bằng một tổng nhất định với hàm tùy chỉnh
Để có được tất cả các kết hợp có thể có từ một tập hợp số nhất định có tổng giá trị nhất định, bạn có thể sử dụng hàm tùy chỉnh bên dưới. Nếu bạn chưa quen với UDF, bạn sẽ tìm thấy nhiều thông tin hữu ích trong hướng dẫn này: Cách tạo các hàm tùy chỉnh do người dùng xác định trong Excel.
Hàm tùy chỉnh để tìm tất cả các kết hợp bằng một tổng nhất định
Option Explicit
Public Function FindSumCombinations(rngNumbers As Range, lTargetSum As Long)
Dim arNumbers() As Long, part() As Long
Dim arRes() As String
Dim indI As Long
Dim cellCurr As Range
ReDim arRes(0)
If rngNumbers.Count > 1 Then
ReDim arNumbers(rngNumbers.Count – 1)
indI = 0
For Each cellCurr In rngNumbers
arNumbers(indI) = CLng(cellCurr.Value)
indI = indI + 1
Next cellCurr
Call SumUpRecursiveCombinations(arNumbers, lTargetSum, part(), arRes())
End If
ReDim Preserve arRes(0 To UBound(arRes) – 1)
FindSumCombinations = arRes
End Function
Private Sub SumUpRecursiveCombinations(Numbers() As Long, target As Long, part() As Long, ByRef arRes() As String)
Dim s As Long, i As Long, j As Long, num As Long, indRes As Long
Dim remaining() As Long, partRec() As Long
s = SumArray(part)
If s = target Then
indRes = UBound(arRes)
ReDim Preserve arRes(0 To indRes + 1)
arRes(indRes) = ArrayToString(part)
End If
If s > target Then Exit Sub
If (Not Not Numbers) <> 0 Then
For i = 0 To UBound(Numbers)
Erase remaining()
num = Numbers(i)
For j = i + 1 To UBound(Numbers)
AddToArray remaining, Numbers(j)
Next j
Erase partRec()
CopyArray partRec, part
AddToArray partRec, num
SumUpRecursiveCombinations remaining, target, partRec, arRes
Next i
End If
End Sub
Private Function ArrayToString(x() As Long) As String
Dim n As Long, result As String
result = x(n)
For n = LBound(x) + 1 To UBound(x)
result = result & “,” & x(n)
Next n
ArrayToString = result
End Function
Private Function SumArray(x() As Long) As Long
Dim n As Long
SumArray = 0
If (Not Not x) <> 0 Then
For n = LBound(x) To UBound(x)
SumArray = SumArray + x(n)
Next n
End If
End Function
Private Sub AddToArray(arr() As Long, x As Long)
If (Not Not arr) <> 0 Then
ReDim Preserve arr(0 To UBound(arr) + 1)
Else
ReDim Preserve arr(0 To 0)
End If
arr(UBound(arr)) = x
End Sub
Private Sub CopyArray(destination() As Long, source() As Long)
Dim n As Long
If (Not Not source) <> 0 Then
For n = 0 To UBound(source)
AddToArray destination, source(n)
Next n
End If
End Sub
Chức năng này hoạt động như thế nào
Hàm chính, FindSumCombinations, gọi một số hàm phụ thực hiện các tác vụ phụ nhỏ hơn. Hàm có tên SumUpRecursiveCombinations thực thi thuật toán cốt lõi để tìm tất cả các tổng có thể có trong phạm vi được chỉ định và lọc các tổng đạt được mục tiêu. Hàm ArrayToString kiểm soát dạng chuỗi đầu ra. Ba hàm nữa ( SumArray , AddToArray và CopyArray ) chịu trách nhiệm xử lý các mảng trung gian: mỗi lần chúng ta tạo một mảng tạm thời, hãy thêm một phần tử từ mảng nguồn vào mảng đó và kiểm tra xem liệu có đạt được tổng mục tiêu hay không.
Cú pháp
Từ góc nhìn của người dùng, cú pháp của hàm tùy chỉnh đơn giản như sau:
FindSumCombinations(phạm vi, tổng)
Ở đâu:
Cách sử dụng hàm FindSumCombinations:
1. Chèn mã ở trên vào mô-đun Mã của sổ làm việc của bạn và lưu nó dưới dạng sổ làm việc hỗ trợ macro (.xlsm).
2. Trong bất kỳ ô trống nào, hãy nhập công thức FindSumCombinations và nhấn nút Enter. Đảm bảo có đủ ô trống ở bên phải để xuất tất cả các kết hợp, nếu không công thức sẽ trả về lỗi #SPILL.
Ví dụ: để tìm tất cả các kết hợp số có thể có trong phạm vi A6:A15 bằng tổng trong A3, công thức là:
=FindSumCombinations(A6:A15, A3)
Giống như bất kỳ hàm mảng động nào khác, bạn nhập công thức chỉ vào một ô (C6 trong hình ảnh bên dưới) và nó sẽ đưa kết quả vào nhiều ô nếu cần. Theo mặc định, các chuỗi được phân tách bằng dấu phẩy sẽ được xuất thành một hàng:
Để trả về kết quả trong một cột, hãy gói hàm tùy chỉnh vào TRANSPOSE như thế này:
=TRANSPOSE(FindSumCombinations(A6:A15, A3))
Để xuất các chuỗi ở dạng một mảng được đặt trong dấu ngoặc nhọn, hãy sửa đổi hàm ArrayToString như sau:
Private Function ArrayToString(x() As Long) As String
Dim n As Long, result As String
result = “{” & x(n)
For n = LBound(x) + 1 To UBound(x)
result = result & “,” & x(n)
Next n
result = result & “}”
ArrayToString = result
End Function
Kết quả sẽ trông giống như thế này:
Nhận tất cả các kết hợp bằng một tổng nhất định với macro VBA
Hàm tùy chỉnh được mô tả ở trên trả về sự kết hợp của các số dưới dạng chuỗi. Nếu bạn muốn đặt mỗi số trong một ô riêng biệt thì macro này sẽ hữu ích. Mã này được viết bởi một chuyên gia Excel khác Alexander Trifuntov, người đã tích cực giúp đỡ người dùng giải quyết các vấn đề Excel khác nhau trên blog này.
Macro để tìm tất cả các kết hợp có giá trị nhất định
Public RefArray1 As String
Public DS As Variant
Public TargetSum As Long
Public TargetCol As Integer
Public TargetRow As Integer
Sub Combination()
UserForm1.Show
End Sub
Function GrayCode(Items As Variant) As String
Dim CodeVector() As Integer
Dim i, kk, rr, col1, row1, n1, e As Integer
Dim lower As Integer, upper As Integer
Dim SubList As String
Dim NewSub As String
Dim done As Boolean
Dim OddStep As Boolean
Dim SSS
Dim TargetArray() As String
kk = TargetCol
rr = TargetRow
col1 = TargetCol + 3
row1 = TargetRow
OddStep = True
lower = LBound(Items)
upper = UBound(Items)
Cells(rr – 1, kk) = “Result”
Cells(rr – 1, kk + 1) = “Sum”
Cells(rr, kk + 1) = TargetSum
Cells(rr – 1, kk).Font.Bold = True
Cells(rr – 1, kk + 1).Font.Bold = True
ReDim CodeVector(lower To upper) ‘it starts all 0
Do Until done
NewSub = “”
For i = lower To upper
If CodeVector(i) = 1 Then
If NewSub = “” Then
NewSub = “,” & Items(i)
SSS = SSS + Items(i)
Else
NewSub = NewSub & “,” & Items(i)
SSS = SSS + Items(i)
End If
End If
Next i
If NewSub = “” Then NewSub = “{}” ’empty set
SubList = SubList & vbCrLf & NewSub
If SSS = TargetSum Then
Cells(rr, kk).NumberFormat = “@”
Cells(rr, kk) = “{ ” & Mid(NewSub, 2) & ” }”
TargetArray() = Split(Mid(NewSub, 2), “,”)
n1 = UBound(TargetArray)
For e = 0 To n1
Cells(row1, col1) = TargetArray(e)
row1 = row1 + 1
Next e
col1 = col1 + 1
row1 = TargetRow
rr = rr + 1
End If
SSS = 0
‘now update code vector
If OddStep Then
‘just flip first bit
CodeVector(lower) = 1 – CodeVector(lower)
Else
‘first locate first 1
i = lower
Do While CodeVector(i) <> 1
i = i + 1
Loop
‘done if i = upper:
If i = upper Then
done = True
Else
‘if not done then flip the *next* bit:
i = i + 1
CodeVector(i) = 1 – CodeVector(i)
End If
End If
OddStep = Not OddStep ‘toggles between even and odd steps
Loop
GrayCode = SubList
End Function
Tiếp theo, tạo UserForm với thiết kế và thuộc tính sau:
Sau khi hoàn thành việc thiết kế biểu mẫu, hãy thêm mã cho biểu mẫu. Để thực hiện việc này, hãy nhấp chuột phải vào biểu mẫu trong Project Explorer và chọn View Code :
Mã cho UserForm
Private Sub CommandButton1_Click()
Dim B
Dim c As Integer
Dim d As Integer
Dim A() As Variant
Dim i As Integer
Dim e As Integer
DS = Range(RefEdit1)
TargetSum = TextBox1.Value
Range(RefEdit2).Select
TargetCol = Selection.Column
TargetRow = Selection.Row
c = LBound(DS)
d = UBound(DS)
ReDim B(d – 1)
For i = 1 To d
e = i – 1
B(e) = DS(i, 1)
Next i
Call GrayCode(B)
Unload Me
End Sub
Private Sub Label1_Click()
End Sub
Private Sub Label3_Click()
End Sub
Với mã và biểu mẫu đã có, hãy nhấn Alt + F8 và chạy macro FindAllCombinations:
Trên biểu mẫu bật lên, hãy xác định những điều sau:
Khi chỉ định ô phía trên bên trái của phạm vi đầu ra, hãy đảm bảo có ít nhất một hàng trống phía trên (dành cho tiêu đề) và đủ ô trống ở phía dưới và bên phải . Nếu không đủ ô trống, dữ liệu hiện có của bạn sẽ bị ghi đè.
Nhấp vào OK sẽ cho ra kết quả như trong ảnh chụp màn hình bên dưới:
-
Trong C4:C6, bạn có sự kết hợp của các số dưới dạng các giá trị được phân tách bằng dấu phẩy.
-
Các cột F, G và H chứa các tổ hợp số giống nhau, mỗi số nằm trong một ô riêng biệt.
-
Trong D4, bạn có tổng mục tiêu.
Dạng đầu ra này giúp kiểm tra kết quả dễ dàng hơn – chỉ cần nhập công thức SUM vào ô F13, kéo nó sang phải qua hai ô nữa và bạn sẽ thấy rằng mỗi tổ hợp số cộng lại bằng giá trị đã chỉ định (50).
Hạn chế: Đối với một tập hợp số lớn, macro có thể mất chút thời gian để tạo ra tất cả các kết hợp có thể có.
Ưu điểm: Hoạt động trên mọi phiên bản Excel 2010 – 365; cung cấp hai dạng đầu ra – chuỗi giá trị và số được phân tách bằng dấu phẩy trong các ô riêng biệt.
Tóm lại, việc tìm tất cả các kết hợp giá trị bằng một giá trị nhất định là một công cụ mạnh mẽ để giải quyết nhiều vấn đề phân tích dữ liệu. Hy vọng bài viết này đã cung cấp cho bạn một điểm khởi đầu hữu ích để khám phá sâu hơn về chủ đề này và xử lý các vấn đề tương tự trong công việc của bạn.
