Supposons que vous ayez une feuille de calcul avec trois colonnes de données. La première colonne a dans l’ordre séquentiel chaque lettre de l’alphabet, de A à Z.

La deuxième colonne contient un certain nombre d’occurrences qui correspondent à la lettre de l’alphabet. La troisième colonne contient un nombre d’heures qui correspond à la lettre de l’alphabet.

Que faire si vous souhaitez répartir, aussi uniformément que possible, une combinaison des lettres de l’alphabet en quatre groupes basés sur la troisième colonne (heures)?

Par exemple, si la somme de toutes les heures pour chaque lettre de l’alphabet est de 4 000 heures, vous voulez proposer une combinaison qui séparerait l’alphabet de sorte que chacun des quatre groupes dispose d’environ 1 000 heures par groupe.

Il s’agit en fait d’un problème bien connu dans le domaine des mathématiques discrètes. Divers algorithmes ont été développés pour apporter des solutions, et il existe certains langages de programmation (comme LISP)

qui facilitent grandement la création d’arborescences capables de «rechercher» des solutions optimales.

Dans ce cas, cependant, une approche simple est préférable, et cela implique l’utilisation d’une macro. Supposons que vous ayez vos données dans les colonnes A à C. La macro suivante analysera la plage que vous spécifiez et retournera une combinaison de valeurs qui répondent à vos besoins.

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

Notez que cette fonction reçoit cinq paramètres. Le premier est la plage que vous souhaitez évaluer, le second est le décalage de la colonne dans cette plage qui doit être totalisé, le troisième est le nombre de « compartiments » que vous souhaitez utiliser dans l’évaluation, le quatrième est le nombre de bucket que vous souhaitez renvoyer, et le cinquième est le décalage de la colonne (dans la plage spécifiée) qui contient les valeurs que vous souhaitez renvoyer.

Ce que fait la macro est de récupérer toutes les valeurs de la colonne que vous voulez totaliser, puis de les trier par ordre décroissant. Ces valeurs, de la plus grande à la plus petite, sont ensuite réparties entre le nombre de « compartiments »

vous avez spécifié qu’il devrait y en avoir. Le nombre est toujours ajouté au compartiment contenant le plus petit total. La chaîne retournée par la fonction représente les valeurs de retour (ce qui se trouve dans chaque cellule de la colonne spécifiée par le cinquième paramètre) et le total du compartiment.

Par exemple, si vous vouliez évaluer la plage A1: C: 26, vous vouliez que la distribution soit basée sur les valeurs de la troisième colonne de la plage (colonne C), vous vouliez qu’il y ait quatre compartiments dans l’analyse, vous voulait que le troisième compartiment soit renvoyé, et que vous vouliez que la fonction renvoie tout ce qui se trouve dans la colonne A de la plage, vous utiliseriez alors ce qui suit pour appeler la fonction:

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

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2408) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Determining_Combinations_to_Make_a_Total [Déterminer les combinaisons pour faire un total].