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:

• Phạm vi là phạm vi của các số cần kiểm tra.
• Tổng là tổng mục tiê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:

• Phạm vi có số nguồn (A4:A13)
• Tổng mục tiêu (50)
• Ô phía trên bên trái của phạm vi đích (C4).

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.