Répartition entre agents

Bonsoir à tous,

Je viens vers vous puisque mes compétences en Excel/VBA ont été dépassées.

Dans l'absolue, j'aimerais répartir les nonbres de la colonne B entre les 9 différents agents de façon équilibré. la somme de la colonne B étant 3 074 532,47 chaque agents devraient avoir une somme proche de 341 614,7. bien entendu chaque nombre ne peuevnt être utilisé qu'une seule fois.

j'espère que mon explication est assez claire

merci pour votre aide et pour votre temps

41example.xlsm (53.25 Ko)

Bonsoir Jofk, bonsoir le forum,

Il y a quelques années j'avais bossé pour une copine qui avait un besoin similaire. Je lui avait crée un fichier que j'ai utilisé avec tes données. Beaucoup plus nombreuses que celles de ma copine, il a mis quelques minutes avant de terminer toutes les tâches. Mais j'ai été surpris du résultat obtenu.

• Un agent avait 3 centimes de plus que la somme totale divisée par 9.

• trois agents avaient 1 centime de moins que la somme totale divisée par 9

• les cinq autres avaient la somme exacte du total divisé par 9

Ce fichier était vraiment adapté à ces besoins à elle mais pourrait de convenir tel qu'il est. Dis-moi si seule la partie Calcul t'intéresse ou si le fichier entier te conviendrait j'essaierai de l'optimiser.

J'avais même crée un manuel sur Word mais il est trop lourd et ne passe pas même zippé. Je pense qu'il est suffisamment explicite pour qu'on l'utilise sans manuel.

J'ai rentré tes données au début pour les agents puis je n'ai rentré qu'une seule valeur. Ensuite, j'ai fait un copier/coller de tes valeurs (l'onglet est protégé mais sans mot de passe)

Le fichier :

44equittable.zip (70.88 Ko)

Bonsoir Denis,

Re bonsoir à tous et à toi ThauThème

Tout d'abord merci de m'avoir répondu aussi vite.

En somme la partie calcul est celle qui m'intéresse le plus car le l'intégrerai dans mon fichier final. Cependant ayant toujours soif d'apprendre de nouvelles choses je suis (personnellement) intéressé par le fichier en entier seulement si cela ne te prend pas un temps monstre.

Je me suis (très rapidement) penché sur le fichier que tu as transféré et je n’ai malheureusement pas vraiment compris son fonctionnement. Pourrais-tu m’expliquer son fonctionnement ?

Je te remercie d’avance

Bien à toi

Bonsoir Jofk, bonsoir le forum,

• tu ouvres le fichier (sinon c'est plus compliqué...)

• tu cliques sur le bouton Données

• la boîte de dialogue Date des Données s'ouvre

• tu renseignes le mois et les derniers digits de l'année (par exemple 11 & 14)

• un nouvel onglet est créé avec Novembre_2014 comme nom (toujours le même exemple...)

• une nouvelle boîte de dialogue Données Participant(e)s s'ouvre

• tu renseignes le nombre de participants (dans ton cas 9)

• la boîte de dialogue te propose alors des champs pour ces 9 paricipant(e)s

• tu renseignes le nom dans le premier champ de chaque participant (par exemple : Agent 1, Agent 2,..., Agent 9)

• Ignore les champs Acompte et Restant Précédent

• une nouvelle boîte de dialogue Données Valeurs s'ouvre

• clique sur le bouton Terminer

• à la question : Êtes-vous sûr(e) de vouloir terminer ?... Cliquer Ok

Pour ma copine, elle rentrait les valeurs une par une mais on va pas s'amuser à retaper toutes tes valeurs...

À ce stade,

• tu déverrouilles l'onglet Données

• tu copies tes valeurs (sans le total) pour les tests t'es pas obligé de tout prendre...

• tu les colles dans la cellule J11

• dans F11 tu écris 1 et tu tires vers le bas pour obtenir un numéro incrémenté jusqu'à la dernière valeur (en appuyant sur [Ctrl])

• tu mets de la bordure si tu en as envie (c'est juste pour l'esthétique)

Voilà la première partie est faite, c'est comme si tu avais rentré les valeurs manuellement

• Clique alors dans le bouton Envoyer

• les données sont envoyées dans l'onglet Calculs

• l'onglet Calculs est actif

• Cliques sur le bouton Calculer maintenant visible

• c'est un peu long (à l'époque je ne savais pas encore comment optimiser) mais ça dispatche les valeurs entre les 9 agents

• une fois le calcul fini tu as les indications de ce qui manque quand un agent n'as pas assez reçu et ce que doit l'agent quand il a trop perçu

• si tu cliques sur le bouton Ranger tu auras, dans l'onglet Novembre_2014, un tableau par agent que tu pourras trier de différentes manières...

Voilà ! Sinon j'ai commencé à optimiser le fichier mais pas encore à l'adapter à ton exemple...

Bonsoir Jkof, bonsoir le forum

J'ai un peu modifié ton fichier exemple avec le code ci-dessous appliqué au bouton Envoyer :

Private Sub CommandButton2_Click() 'bouton "Envoyer"
Dim O1 As Object 'déclare la variable O1 (Onglet1)
Dim O2 As Object 'déclare la variable O2 (Onglet2)
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Dim NEUV As Single 'déclare la variable NEUV (NEUVième du total)
Dim TC As Variant 'déclare la variable TC (Tableau de Cellules)
Dim I As Integer 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (incrément)
Dim DEST As Range 'déclare la variable DEST (cellule de DESTination)
Dim LTA As Range 'déclare la variable LTA (Ligne des Totaux des Agents)
Dim MSG As String 'déclare la variable MSG (MeSsaGe)
Dim NV As Integer 'déclare la variable NV (Nombre des Valeurs)

ActiveCell.Select 'enlève le focus au bouton
Application.ScreenUpdating = False 'masque les rafraîchiseements d'écran
Set O1 = Sheets("Sheet1") 'définit l'onglet O1
Set O2 = Sheets("Sheet2") 'définit l'onglet O2
DL = O1.Cells(Application.Rows.Count, 2).End(xlUp).Row 'définit la dernière ligne éditée DL de la colonne 2 (=B) de l'onglet O1
NEUV = CSng(O1.Range("G1").Value) 'définit le neuvième NEUV du total
TC = O1.Range("B5:B" & DL) 'définit le tableau de cellules TC
Set LTA = O1.Range("E3:M3") 'définit la plage LTA de la ligne des totaux des agents
O2.Range("A1").CurrentRegion.Clear 'efface d'enventuelles anciennes données de l'onglet O2
O2.Range("A1").Resize(UBound(TC, 1)) = TC 'copie le tableau TC dans la cellule A1 de l'onglet O2
O2.Sort.SortFields.Clear 'tri descendant des données
O2.Sort.SortFields.Add Key:=O2.Range("A1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
    .SetRange O2.Range("A1").CurrentRegion
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
'j'aurais pu trier directement dans l'onglet O1 mais je ne savais pas si ça allait te poser problème...
TC = O2.Range("A1").CurrentRegion 'récupère les données triées dans le tableau TC
For I = 1 To UBound(TC, 1) 'boucle 1 sur toutes les lignes du tableau TC
    For J = 5 To 13 'boucle 5 sur les les colonne 5 à 13
        'condition 1 : si la somme du tableau TA(J) plus la valeur de TC(I,1) est inféieure ou égale au neuvième NEUV
        If O1.Cells(3, J).Value + TC(I, 1) <= NEUV Then
            'condition 2 : si le total de l'agent en colonne J es égale à la valeur minimale de la ligne LTA
            If O1.Cells(3, J) = Application.WorksheetFunction.Min(LTA) Then
                'définit la cellule de destination DEST
                Set DEST = O1.Cells(Application.Rows.Count, J).End(xlUp).Offset(1, 0)
                DEST.Value = TC(I, 1) 'place la valeur de TC(I,1) dans DEST
                Exit For 'sort de la boucle 2
            End If 'fin de la condition 2
        End If 'fin de la condition 1
    Next J 'prochaine ceolonne de la boucle 2
Next I 'prochaijne ligne de la boucle 1
Application.ScreenUpdating = True 'affiche les rafraîchiseements d'écran
'définit le nombre NV de valeurs dispatchées
NV = Application.WorksheetFunction.CountA(O1.Range("D3").CurrentRegion) - 19
If NV < UBound(TC, 1) Then 'condition : si NV est inférieur au nombre total de valeurs
    'définit le message MSG (en prenant compte le pluriel et le singulier)
    MSG = IIf(UBound(TC, 1) - (NV + 1) > 1, "Restent " & UBound(TC, 1) - NV & " valeurs non dispatchées :" & Chr(13), _
       "Reste une valeur non dispatchée :" & Chr(13))
    For I = NV + 1 To UBound(TC, 1) 'boucle de NV+1 au nombre total de valeurs
        MSG = MSG & TC(I, 1) & Chr(13) 'redéfinit le message MSG
    Next I 'prochaine valeur de la boucle
    MsgBox MSG 'affiche le message MSG
End If 'fin de la condition
End Sub

Le fichier :

27example.xlsm (136.38 Ko)

Bonjour Thauthème, bonjour le forum

Désolé pour cette réponse tardive.

Je n'ai pas grand chose à dire à part MERCI, ton travail/aide correspond exactement à ce dont j'avais besoin. c'est tout simplement parfait/excellent.

Encore une fois merci

Bonjour le Forum et bonjour Thauthème,

Je suis malheureusement de retour. Ayant eu de nouvelles directives j'ai besoin à nouveau de notre aide.

Ma problématique est de garder le nom du client à coté de son montant lors du split encore agent. (Voir fichier exemple).

Encore merci pour votre aide précieuse.

15example.xlsm (158.02 Ko)

Bonsoir JofK, bonsoir le forum,

Je n'ai pas compris ce que tu veux dans ton nouvel exemple. Pourrais-tu me remplir au moins de lignes pour voir si je comprends mieux... En m'indiquant clairement la différence entre le premier et le second fichier.

Bonjour le forum et bonjour Thauthème

désolé pour mon post précédent qui n'était pas très clair.

dans le fichier excel tu trouveras deux colonnes: une montant et une client. Mon besoin est de garder le nom du client auquel appartient le montant lors du calcul de la macro afin de spliter en nombre égal. ( voir fichier)

Merci pour votre aide si précieuse

Bonjour JofK, bonjour le forum,

Tu aurais pu au moins faire l'effort de fournir un tableau fini ! La prochaine fois je ne retravaillerai pas le code sur un fichier incomplet...

30jofk-v02.zip (207.35 Ko)
Rechercher des sujets similaires à "repartition entre agents"