Angenommen, Sie haben ein Arbeitsblatt mit drei Datenspalten. Die erste Spalte enthält nacheinander jeden Buchstaben des Alphabets A bis Z.

Die zweite Spalte enthält eine Reihe von Vorkommen, die mit dem Buchstaben im Alphabet korrelieren. Die dritte Spalte enthält eine Anzahl von Stunden, die mit dem Buchstaben im Alphabet korreliert.

Was ist, wenn Sie eine Kombination der Buchstaben in vier Gruppen basierend auf der dritten Spalte (Stunden) so gleichmäßig wie möglich verteilen möchten?

Wenn beispielsweise die Summe aller Stunden für jeden Buchstaben des Alphabets 4.000 Stunden beträgt, möchten Sie eine Kombination finden, die das Alphabet so trennt, dass jede der vier Gruppen ungefähr 1.000 Stunden pro Gruppe hat.

Dies ist tatsächlich ein bekanntes Problem auf dem Gebiet der diskreten Mathematik. Eine Vielzahl von Algorithmen wurde entwickelt, um Lösungen bereitzustellen, und es gibt bestimmte Programmiersprachen (wie LISP)

Dies erleichtert die Erstellung von Baumstrukturen, die nach optimalen Lösungen „suchen“ können, erheblich.

In diesem Fall ist jedoch ein einfacher Ansatz am besten, bei dem ein Makro verwendet wird. Angenommen, Sie haben Ihre Daten in den Spalten A bis C. Das folgende Makro analysiert den von Ihnen angegebenen Bereich und gibt eine Kombination von Werten zurück, die Ihren Anforderungen entsprechen.

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

Beachten Sie, dass dieser Funktion fünf Parameter übergeben werden. Der erste ist der Bereich, den Sie auswerten möchten, der zweite ist der Versatz der Spalte innerhalb dieses Bereichs, der summiert werden soll, der dritte ist die Anzahl der „Buckets“, die Sie für die Auswertung verwenden möchten, der vierte ist die Anzahl der Bucket, den Sie zurückgeben möchten, und der fünfte ist der Versatz der Spalte (im angegebenen Bereich), die die Werte enthält, die Sie zurückgeben möchten.

Das Makro erfasst alle Werte in der Spalte, die summiert werden sollen, und sortiert sie dann in absteigender Reihenfolge. Diese Werte, vom größten zum kleinsten, werden dann auf beliebig viele „Buckets“

verteilt Sie haben angegeben, dass es geben sollte. Die Zahl wird immer dem Bucket hinzugefügt, der die kleinste Summe enthält. Die von der Funktion zurückgegebene Zeichenfolge repräsentiert die Rückgabewerte (unabhängig davon, was sich in jeder Zelle der durch den fünften Parameter angegebenen Spalte befindet) und die Summe des Buckets.

Wenn Sie beispielsweise den Bereich A1: C: 26 bewerten möchten, möchten Sie, dass die Verteilung auf den Werten in der dritten Spalte des Bereichs (Spalte C) basiert. Sie möchten, dass die Analyse vier Buckets enthält Wenn der dritte Bucket zurückgegeben werden soll und die Funktion alles in Spalte A des Bereichs zurückgeben soll, können Sie die Funktion wie folgt aufrufen:

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

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (2408) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier:

Bestimmen von Kombinationen, um eine Summe zu bilden.