Formule Excel aléa répartition égale + sauf

Bonjour,

Je cherche une formule afin de faire une sorte de répartition avec ces éléments à prendre en compte :

• J'ai 8 listes de contrats appartenant à 8 gestionnaires
• Chaque liste de gestionnaire doit être réparti sur les 7 autres, de manières assez égale ( à 1 ou 2 près), de manière aléatoire
• j'ai trouvé sur ce forum comme faire la 1ère partie afin d'avoir en 2ndaire le nom de quelqu'un qui n'est pas celui mis en principal, mais je ne vois pas comment faire pour éviter les doublons en 2ndaire, 3re et 4aire

Sous un autre point de vue (afin de comprendre):

une personne gère 80 contrats, je dois avoir un tableau avec un gestionnaire 2ndaire, tertiaire et quaternaire divisé de manière assez équitable et aléatoirement, sans doublon (sans avoir en 2ndaire celui qui est principal, ou en secondaire le même qu'en tertiaire)

A dispo pour tout éclaircissement, en espérant ne pas avoir oublié de détails

Merci par avance pour votre aide

Cdt

Kenryo

Bonjour,

Je ne sais pas comment faire ça avec une fonction Excel classique... Du coup j'ai crée une fonction qui pourra le faire, elle s'appelle AleaSansDoublons, prend deux paramètres : la liste source, la liste des valeurs déjà sorties (optionnelle).

Le fichier est le suivant:

Et la macro qui la fait fonctionner:

Function AleaSansDoublons(Liste As Range, Optional ValeurSorties As Range)
Application.Volatile

'Enregistrement de la liste dans un objet dictionnary
Dim Valeurs As New Dictionary
For Each valeur In Liste.Cells
    Valeurs.Add CStr(valeur), valeur
Next valeur

'Supprime les valeurs déjà sorties de dictionnary, si ValeurSorties est précisé
If Not ValeurSorties Is Nothing Then
    For Each valeur In ValeurSorties
        If Valeurs.Exists(CStr(valeur)) Then
            Valeurs.Remove (CStr(valeur))
        End If
    Next valeur
End If

'Sort une valeur aléatoire sur ce qu'il reste dans le dictionnaire
If Valeurs.Count > 0 Then
    Index = Int(((Valeurs.Count - 1) * Rnd) + 1)
    AleaSansDoublons = Valeurs(Valeurs.Keys(Index))
End If
End Function

L'idée du code est la suivante:

J'enregistre dans un objet les différentes valeurs que tu peux sortir aléatoirement, j'enlève celles déjà sorties, et je choisis aléatoirement une valeur parmis celles qui restent dans mon objet. L'avantage d'un dictionnaire par rapport à un tableau, c'est que tu peux supprimer des éléments sans avoir à savoir où ils se trouvent, tu lui donnes juste une "clé" et ça te supprimera l'élément lié à cette clé.

Bonjour,

Déjà merci pour votre retour et votre tableau

Dans le principe ça répondait à ma demande, mais un "nouveau souci" se pose:

En effet en secondaire je me retrouve avec 2, 3 ou 4 fois le même nom (j'ai "tiré" la formule vers le bas pour avoir le visuel sur les 7 gestionnaires)

C'est, je crois le seul souci qu'il me reste

Cdt

Kenryo

ps: je suis assez novice sur excel, à part les bases..du coup j'ai pas tout compris dans le bas de ton message, mais la macro dans l'ensemble à l'air de faire l'affaire

Re!

Là on aborde un autre souci, c'est le hasard qui décide qui tu as pour les colonnes, du coup parfois une personne peut toujours être en secondaire, ce n'est pas ce que tu souhaites?

Hum j'ai essayé quelque chose, c'est du bricolage mais bon... Je regarde les 3 cases au dessus, et les cases à gauche de celle où je fais la formule, ça me permet de déterminer qui je vais mettre, comme ça tu es sûr qu'une valeur ne peut pas revenir sur la même ligne, et pas sur les 4 lignes qui l'entourent, ainsi tu devrais avoir une meilleure répartition je l'espère...

Voici le test, j'ai modifié un peu le code pour pouvoir lui donner plusieurs plages de cellules!

Je t'ai mis un graphique pour que tu puisses contempler la répartition

je vais tester avec mes données réelles et je te tiens au courant! ( et je clôturerais si besoin est)

En tout cas merci beaucoup pour ton travail!

Kenryo

Edit : autre souci, puis-je te PM ?

merci

Re

Tu ne peux absolument pas essayer de joindre une version non confidentielle de ton fichier de base? je n'aime pas trop le mp, c'est lent, ça déconne, et pas tous le monde peut profiter de la réponse

Honnêtement je sais même pas si c'est faisable

Voici le tableau que cela doit donner

Pour résumer : Chaque gestionnaire gère 80 contrats, il faut que ce soit réparti assez équitablement en 2ndaire. Derrière un 2nde nommé X, ce sera le même Y et Z qui doivent suivrent (en recopiant et tirant la cellule du dessus, ça devrait faire l'affaire)

Donc Jean, 80 contrats, 7 autres gestionnaires

puis par la suite, c'est Jacques, 80 contrats, 7 gestionnaires (sur le même principe).

C'est vraiment pas évident ><

Cdt

Kenryo

Edit : Il s'agit de prénom (ça peut passer) mais je ne peux pas mettre les noms des contrats. Il me faudrait "simplement" les formules à placer, je reprendrais mon tableau avec

Re,

J'ai juste un souci avec ta logique...

Si tu dis :

Autre prénom (aléa, sauf D2, E22, F22, G2, G4, G10, G17), ça fait 7 noms maximum qui ne peuvent pas apparaitre, sachant que tu en as 7, ça provoquera forcément une erreur, c'est pour ça que je me limitais à une vision de 2 cellules au dessus maximum...

Il manque un prénom

Nous sommes 8 gestionnaires

mea culpa

Je te retourne le fichier avec une visibilité maxi de 3 cases au dessus, 3 cases à gauche, soit maximum 6 valeurs retirées sur 7

EDIT: nouveau fichier:

ça se rapproche mais c'est pas encore ça

je pensais vraiment pas que ce serait si compliqué ahah ^^'

Là le petit souci que je vois, c'est que derrière le secondaire, le nom varie

Si j'ai en secondaire Luc, il faudrait que j'ai (un prénom aléatoire) en Tertiaire, et un autre (aléatoire) en quaternaire. ça ok, c'est bon

mais si plus bas j'ai encore Luc en 2ndaire, il faudrait les mêmes noms que au dessus

Tout en respectant le fait que si par exemple Jean a 80 contrats, Luc doit apparaitre (80/7 = 1180) 11 ou 12 fois en 2ndaire

compliqué hein..

(en tout cas tu es super rapide ^^)

Kenryo

Ah d'accord, dans ce cas ce n'est pas compliqué!

On avait peut-être même pas besoin de fonction d'aléa

Je te propose cette solution quand même

Je fais de l'aléa pour la 2nde personne, et pour les personnes qui suivent, les colonnes 3 et 4 recherchent simplement qui doit être après la personne tirée "aléatoirement".

Bah pour toi peut-être, mais quand moi je vois la taille de la formule

Et du coup si dans mon exemple bien concret j'ai 82 contrats, je change ça à quel niveau dans la formule? Afin d'avoir la répartition équivalente entre les gestionnaires? ^^

en tout cas t'es au top

Ken

Si tu as plus de contrat, tu as juste à étaler la formule, le seul truc qui peut changer dans la formule c'est le +6 et le -6 que j'ai mis dedans, car ça varie en fonction du nombre de personnes que tu peux bloquer, je pense qu'on pourrait même se passer d'une formule d'aléa vu qu'en fait c'est juste de la répétition, mais bon

je vais essayer ça sera plus simple :p

Après il me fallait une formule d'aléa afin que la décision ne m'appartienne pas

Je teste et je reviens (très sûrement lol) vers toi

En tout cas merci encore pour ton travail énorme

D'ici là, bon après-midi

Ken

De rien et bon après-midi

J'essaie sur du concret demain et si c'est bon je clôture

J'ai vu que je pouvais "fixer" l'aléa une fois fait, ça peut être pratique aussi (F2 F9 j'ai cru voir ^^)

Çà sera un fichier partagé, si l'aléa change les noms à chaque ouverture je vais être obligé de l'utiliser je pense

Le mieux serait de garder le texte de la formule quelque part et de passer le résultat du calcul en valeur fixe, ou bien de passer la méthode de calcul de ton classeur en manuel

En langage simplifié? 0:)

Re,

en langage simplifié ça donnerait...

Le mieux serait de garder le texte de la formule quelque part, en clair, sélectionner une des cellules qui contient la formule, copier celle qui s'affiche dans la barre de formule, la coller quelque part pour s'en rappeler si besoin de refaire tourner le calcul, et de passer le résultat du calcul en valeur fixe, c'est à dire copier toutes les cellules qui contiennent la formule, et faire un collage spécial au même endroit (collage spécial valeurs). Ce collage permettra de remplacer la formule par la valeur de la cellule, donc plus de recalcul à chaque fois, une valeur constante dans le temps. Ou bien de passer la méthode de calcul de ton classeur en manuel, tu as des options de calcul dans ton classeur, tu peux y accéder via l'onglet "Formules", puis dans "Option de calcul", tu peux choisir Automatique ou Manuel, Automatique c'est ce qui te pose problème actuellement, à chaque fois que tu entres quelque chose dans une cellule, ça recalcule tout ton classeur, donc ça refait aussi fonctionner la fonction que tu as mis, et te donne de nouvelles valeurs. En passant ton classeur en manuel, il ne calculera que si tu lui demandes manuellement, toujours via l'onglet "Formules" avec le bouton calculer maintenant (raccourci : touche F9).

J'espère être déjà plus compréhensible