فرض کنید یک کاربرگ با سه ستون داده دارید. ستون اول به ترتیب هر حرف الفبا، از A تا Z را دارد. ستون دوم شامل تعدادی از موارد است که با حروف الفبا مرتبط است. ستون سوم شامل تعدادی ساعت است که با حروف الفبا مرتبط است.
اگر بخواهید تا حد امکان به طور مساوی ترکیبی از حروف الفبا را در چهار گروه بر اساس ستون سوم (ساعت) توزیع کنید؟ برای مثال، اگر مجموع تمام ساعت های هر حرف الفبا 4000 ساعت باشد، می خواهید ترکیبی ایجاد کنید که الفبا را از هم جدا کند تا هر یک از چهار گروه حدود 1000 ساعت در هر گروه داشته باشند.
این در واقع یک مسئله شناخته شده در زمینه ریاضیات گسسته است. الگوریتم های مختلفی برای ارائه راه حل ها توسعه داده شده اند، و زبان های برنامه نویسی خاصی (مانند LISP) وجود دارند که ایجاد ساختارهای درختی را که می توانند برای راه حل های بهینه «جستجو» کنند، بسیار تسهیل می کنند.
با این حال، در این مورد، یک رویکرد ساده بهترین است، و این شامل استفاده از یک ماکرو است. فرض کنید که داده های خود را در ستون های A تا C دارید. ماکرو زیر محدوده ای را که مشخص کرده اید تجزیه و تحلیل می کند و ترکیبی از مقادیری را برمی گرداند که نیازهای شما را برآورده می کند.
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)
Notice that this function is passed five parameters. The first is the range that you want evaluated, the second is the offset of the column within that range that should be totaled, the third is the number of "buckets" you want to use in the evaluation, the fourth is the number of the bucket that you want to return, and the fifth is the offset of the column (in the specified range) that contains the values you want returned.
What the macro does is to grab all the values in the column you want totaled, and then sort them in descending order. These values, from largest to smallest, are then distributed among however many "buckets" you specified that there should be. The number is always added to the bucket that contains the smallest total. The string that is returned by the function represents the return values (whatever is in each cell of the column specified by the fifth parameter) and the total of the bucket.
For instance, if you wanted to evaluate the range A1:C:26, you wanted the distribution to be based on the values in the third column of the range (column C), you wanted there to be four buckets in the analysis, you wanted the third bucket returned, and you wanted to have the function return whatever is in column A of the range, then you would use the following to call the function:
=DoDist(A1:C26,3,4,3,1)
