Giả sử bạn có một trang tính với ba cột dữ liệu. Cột đầu tiên có thứ tự tuần tự từng chữ cái trong bảng chữ cái, từ A đến Z.

Cột thứ hai chứa một số lần xuất hiện tương quan với chữ cái trong bảng chữ cái. Cột thứ ba chứa một số giờ tương quan với chữ cái trong bảng chữ cái.

Điều gì xảy ra nếu bạn muốn phân phối sự kết hợp của các chữ cái trong bảng chữ cái thành bốn nhóm dựa trên cột thứ ba (giờ)?

Ví dụ: nếu tổng số giờ cho mỗi chữ cái trong bảng chữ cái là 4.000 giờ, bạn muốn đưa ra một tổ hợp có thể tách riêng bảng chữ cái để mỗi một trong bốn nhóm sẽ có khoảng 1.000 giờ cho mỗi nhóm.

Đây thực sự là một vấn đề nổi tiếng trong lĩnh vực toán học rời rạc. Một loạt các thuật toán đã được phát triển để cung cấp các giải pháp và có một số ngôn ngữ lập trình nhất định (chẳng hạn như LISP)

điều đó tạo điều kiện rất nhiều cho việc tạo ra các cấu trúc cây có thể “tìm kiếm” các giải pháp tối ưu.

Tuy nhiên, trong trường hợp này, cách tiếp cận đơn giản là tốt nhất và liên quan đến việc sử dụng macro. Giả sử rằng bạn có dữ liệu của mình trong các cột từ A đến C. Macro sau sẽ phân tích phạm vi bạn chỉ định và trả về một tổ hợp các giá trị đáp ứng yêu cầu của bạn.

Function DoDist(sRaw As Range, _   iTCol As Integer, _   iBuckets As Integer, _   iWanted As Integer, _   iRetCol As Integer) As String

Dim lGTotal As Long     Dim lPerBucket As Long     Dim lCells() As Long     Dim sRet() As String     Dim lBk() As Long     Dim sBk() As String     Dim lTemp As Long     Dim sTemp As String     Dim J As Integer     Dim K As Integer     Dim L As Integer

Application.Volatile     ReDim lCells(sRaw.Rows.Count)

ReDim sRet(sRaw.Rows.Count)

ReDim lBk(iBuckets)

ReDim sBk(iBuckets)



lGTotal = 0     For J = 1 To sRaw.Rows.Count         lCells(J) = sRaw(J, iTCol)

lGTotal = lGTotal + lCells(J)

sRet(J) = sRaw(J, iRetCol)

Next J

For J = 1 To sRaw.Rows.Count - 1         For K = J + 1 To sRaw.Rows.Count             If lCells(J) < lCells(K) Then                 lTemp = lCells(J)

lCells(J) = lCells(K)

lCells(K) = lTemp                 sTemp = sRet(J)

sRet(J) = sRet(K)

sRet(K) = sTemp             End If         Next K     Next J

lPerBucket = lGTotal / iBuckets     For J = 1 To sRaw.Rows.Count         L = iBuckets         For K = iBuckets To 1 Step -1             If lBk(K) <= lBk(L) Then L = K         Next K         lBk(L) = lBk(L) + lCells(J)

sBk(L) = sBk(L) & sRet(J) & ", "

Next J

For J = 1 To iBuckets         If Right(sBk(J), 2) = ", " Then             sBk(J) = Left(sBk(J), Len(sBk(J)) - 2)

End If         sBk(J) = sBk(J) & " (" & lBk(J) & ")"

Next J

DoDist = sBk(iWanted)

End Function

Lưu ý rằng hàm này được truyền năm tham số. Đầu tiên là phạm vi mà bạn muốn đánh giá, thứ hai là bù đắp của cột trong phạm vi đó sẽ được tính tổng, thứ ba là số “nhóm” bạn muốn sử dụng trong đánh giá, thứ tư là số nhóm mà bạn muốn trả về và nhóm thứ năm là phần bù của cột (trong phạm vi đã chỉ định) chứa các giá trị bạn muốn trả về.

Những gì macro làm là lấy tất cả các giá trị trong cột mà bạn muốn tính tổng, sau đó sắp xếp chúng theo thứ tự giảm dần. Các giá trị này, từ lớn nhất đến nhỏ nhất, sau đó được phân phối giữa nhiều “nhóm”

bạn đã chỉ định rằng phải có. Số luôn được thêm vào nhóm chứa tổng nhỏ nhất. Chuỗi được trả về bởi hàm đại diện cho các giá trị trả về (bất kỳ giá trị nào nằm trong mỗi ô của cột được chỉ định bởi tham số thứ năm) và tổng của nhóm.

Ví dụ: nếu bạn muốn đánh giá phạm vi A1: C: 26, bạn muốn phân phối dựa trên các giá trị trong cột thứ ba của phạm vi (cột C), bạn muốn có bốn nhóm trong phân tích, bạn muốn nhóm thứ ba được trả về và bạn muốn hàm trả về bất cứ thứ gì trong cột A của dải ô, thì bạn sẽ sử dụng cách sau để gọi hàm:

=DoDist(A1:C26,3,4,3,1)

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (2408) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: