Tirage au sort aléatoire sur plusieurs tour sans doublons

Bonjour à tous,

Au vu des miracles qui se sont produit dans d'autres sujets, dû au génie des intervenants de ce forum, je me permet de vous soumettre mon problème sur excel. Problème qui malgré 4h de réflexion et de recherche me tient toujours en échec.

Mes collègues et moi devons nous répartir des jours de gardes tous les trimestres. Actuellement au début de chaque trimestre nous organisons une répartition sur de simples calendriers, de façon sommaire, brouillonne, et trop souvent confuse. De plus la répartition se fait généralement selon un ordre alphabétique, parfois anti-alphabétique, qui privilégie globalement toujours les mêmes personnes. Donc autant vous dire que ça se termine souvent de façon bordélique et que la loi de la plus grande gueule prime.

Afin de changer cette situation mon premier problème était de pouvoir nous répartir de façon claire, intuitive et efficace. Je suis finalement arrivé à un résultat qui me satisfait. (4 tableaux de garde modifiable façon calendrier, une liste de nom facilement modifiable, avec le nombre de garde prises associé)

Mon second problème concerne le tirage aléatoire parmi les liste de noms, que je n'ai que partiellement résolu:

-Je veux pouvoir tirer au sort un nom dans une liste déterminée (il y a 3 listes indépendantes ("M6;M5;M4") donc il doit y avoir 3 tirages au sort différents)

  • Je ne veux pas de doublons tant que tous les noms de la liste tirés au sort n'ont pas été tiré précédemment (pas de nom tiré une 2eme fois tant que tous les noms n'ont pas été tirés au moins une fois, pas de noms tiré pour une 3eme fois tant que tous les noms n'ont pas été tirés au moins deux fois, etc...). Il y a normalement un maximum de 3 tour dans la répartition, mais un 4ème pourrait être envisageable.
  • Je ne veux pas tirer au sort des noms ayant déjà choisi leur quota minimal de garde.

Je m'étais personnellement dirigé vers plusieurs fonctions de type

=INDEX(A:A;ALEA.ENTRE.BORNES(1;NBVAL(A:A)-1)+1;1)

pour chaque tour de tirage mais je me retrouve toujours coincé par les exceptions à retirer de la liste.

D'avance merci de votre aide, et désolé si mes propos manque de précisions.

Choppah

Les règles complètes de la répartition

  • *Il y a 3 groupes de personnes M6,M5,M4, de taille variables mais +/- équivalents entre eux (entre 15 et 30)
    *La répartition concerne un trimestre, environ 90 jours (dans mon exemple trimestre fictif du 7/12 au 6/03)
    *Il doit y avoir une personne de garde minimum pour chaque jour avant qu'il puisse y avoir un doublont, et un doublont pour chaque jour avant qu'il puisse y avoir des triplont. (le tableau 1 doit être complètement rempli avant de commencer à remplir de le tableau 2, et ainsi de suite jusqu'au tableau 4)
    *Le groupe M6 doit faire au minimum 2 gardes, les deux autres doivent en faire minimum 3.
    *Les gardes sont pondérées en fonction du jour de la semaine: Samedi x1,5 ; Dimanche x2 ; autre jour x1
    *La répartition se déroule comme suit:
    1)Le groupe M6 choisit toutes ses gardes afin de remplir le nombre de gardes obligatoires minimum (2). (NB: priorité aux plus anciens)
    2)Lorsque tous les M6 ont choisis, on fait un premier tour de choix parmi les M5 puis les M4.
    3)Lorsque tous les M5 et M4 ont fait un premier choix, on fait un un deuxième tour pour ceux qui n'ont pas leur nombre minimal de jours de garde, toujours en commençant par le groupe M5 puis le groupe M4.
    3bis) Régle supplémentaire, on finit d'abord de répartir les samedis qui valent 1,5 avant de répartir les autres jours. Ce qui explique pourquoi je ne peux pas utiliser la technique de tirage au sort avec la fonction RANG
    4)Lorsque tous les M5 et M4 ont fait un premier choix, on fait un un troisième tour pour ceux qui n'ont pas leur nombre minimal de jours de garde, toujours en commençant par le groupe M5 puis le groupe M4.
    5)A ce stade là généralement on a tous rempli nos quotat minimum personnels, et remplit au moins un tableau.

Annotations tableur provisoire:

  • Feuille "Nom"
    *29 Noms fictifs répartis en 3 groupes. (colonne A, G, M)
    *Total : total de garde coefficienté (colonne B, H, N)
    *Tour n° : Nombre de fois que le nom a été tiré au sort/ nombre de fois que la personne a fait un choix sur les tableaux 1 à 4 (Colonne C, I, O)
    *Semaine, Samedi, Dimanche : Détail des jours choisi par la personne sur les tableaux 1 à 4
    *M6/M5/M4 Aléa: Tirage au sort aléatoire d'un nom parmi la liste correspondante.
    Feuilles "Tableau 1", "Tableau 2", "Tableau 3", "Tableau 4" :
    Détails des jours gardes pris, sur calendrier.

Bonjour,

Après relecture, je crois que ce n'est pas très clair : on tire quoi et c'est destiné à quoi ! ?

Le processus fait état de choix avec priorité aux anciens. A quoi sert le tirage dans ce cas ?

Le tirage, soit définit directement les gardes, soit définit un ordre de choix.

Il serait utile de décrire pas à pas un processus de son démarrage jusqu'au résultat recherché, de façon extrêmement détaillée.

Ce qui permettrait de positionner très exactement chaque tirage, ses caractéristiques et son utilisation dans le processus complet.

Cordialement.

Bonjour,

Effectivement après m'être cassé les dents sur ce problème hier, je ne devais plus être très frais.

La répartition des gardes se déroule comme suit:

*A chaque trimestre nous sommes répartis sur plusieurs site de garde. Il y a trois groupes de personnes (M4 à M6). Le nombre total de personnes varie en fonction du site de garde mais les 3groupes sont toujours de taille +/- équivalente.

*Pour chaque site de garde, il faut au minimum une personne présente chaque jour.

*Les personnes du groupes M4 et M5 doivent faire au minimum 3 jours de garde par trimestre, les personnes du groupes M6 seulement 2. N'importe qui peut faire plus de jours de garde que le minimum s'il le souhaite.

*Les jours de semaine compte pour un jour de garde. Les samedis compte pour 1,5 jours, et les dimanches 2 jours.

*Dans certaines situations le nombre minimum de jours de gardes à poser par l'ensemble des personnes sur un site de garde est superieur aux nombre total de jours dans le trimestres

exemple:

Sur mon site de garde il y a 90 personnes (30 de chaque groupes), il y a au total 240 jours de gardes à poser pour que tout le monde valide son nombre minimum de jour de garde à effectuer.

Comme il y a 90 jours dans un trimestre, il y aura environ 2,67 personnes présentes pour chaque jour de garde.

*On veut que les effectifs de présence pour chaque jour du trimestre soit équivalent à +/- 1 personnes

exemple:

Sur mon site de garde, on ne veut pas avoir un jour 4 personnes présentes pour effectuer la garde puis le lendemain se retrouver avec une seule personne. Par conséquent on remplit le trimestre tableau par tableau. Une fois que toutes les dates dans le "tableau 1" sont choisie par une personne, on ouvre le "tableau 2" et ainsi de suite.

*Lors de la répartition de début de trimestre, les personnes du groupe M6 choisissent leurs jours de gardes en premiers sur le "tableau".

La plupart du temps ceux-ci choisissent d'assurer les gardes des dimanches, validant ainsi leur nombre minimum de jours de gardes à valider.

*Une fois que toutes les personnes du groupe M6 ont choisi et valider leur nombre minimum de jours de garde à effectuer, on passe aux deux groupes suivants.

On commence à faire un premier tour pour les personnes du groupe M5

Les personnes du groupe M5 choisissent un premiers jours de garde. Une fois que toutes les personnes du groupe M5 ont choisit un premier jour, on passe aux personnes du groupe M4. Lorsque toutes les personnes du groupe M4 ont choisit un premier jour, on revient aux personnes du groupe M5 pour un deuxième tour. Et ainsi de suite, jusqu'à ce que toutes les personnes présentent aient choisi suffisamment de jours pour valider leur nombre minimum de jour de garde à effectuer sur le trimestre.

On peut résumer ainsi:

M6 1er tour -> M6 2e tour -> M5 1er tour -> M4 1er tour -> M5 2e tour -> M4 2e tour -> M5 3e tour -> M4 3e tour

*A noter que si quelqu'un choisi un samedi lors du premier tour, on lui fait choisir immédiatement un 2ème samedi pour qu'il valide directement son nombre minimum de jour de garde à effectuer pendant le trimestre, et éviter de se retrouver avec trop de personne qui valide plus que le minimum (exemple un dimanche + un samedi = 2+1,5 = 3,5 jours de gardes sur le trimestre)

Par conséquent sur mon tableur excel il me faut: les noms des personnes à répartir (onglet "Nom"), les jours de garde du trimestre à pourvoir (onglet "Tableau1", "Tableau 2", "Tableau 3", "Tableau 4"), le nombre de garde choisies par chaque personnes ainsi que le nombre de fois qu'il a choisi (actuellement fait sur les colonnes "Total" et "Tour n°" dans l'onglet "Nom")

Actuellement mon tableur est adapté pour une répartition suivant un appel par ordre alphabétique ou pré-établi. (ce qui généralement défavorise ceux qui sont à la fin de l'alphabet)

Mais pour rendre la répartition plus égalitaire entre chaque personne du même groupe je voudrai introduire pour chaque groupe un tirage au sort d'un nom parmi les autres du même groupe. Tout en gardant, la main-mise pour le choix de l'attribution du jour de garde.

Pour ce la j'ai tenté d'utiliser la fonction

=INDEX(A:A;ALEA.ENTRE.BORNES(1;NBVAL(A:A)-1)+1;1) la colonne A étant ma liste de nom M6

=INDEX(G:G;ALEA.ENTRE.BORNES(1;NBVAL(G:G)-1)+1;1) la colonne G étant ma liste de nom M5

=INDEX(M:M;ALEA.ENTRE.BORNES(1;NBVAL(M:M)-1)+1;1) la colonne M étant ma liste de nom M4

Si j'arrive effectivement à tirer au sort aléatoirement des noms parmi chaque liste, je n'arrive pas à exclure du tirage les personnes qui ont déjà validé leur nombre minimum de jour de garde (colonne B/H/N "Total"). De même je n'arrive pas à exclure temporairement les personnes parmi un même groupe qui ont déjà choisi un premier jour quand d'autre ne l'ont pas encore fait (toute l'histoire du 1er, 2e et 3e tour de tirage au sort)

En espérant que j'ai pu éclaircir le problème.

Bien à vous.

Désolé, mais je ne parviens pas à entrer dans ton processus... Là où j'attends un mode opératoire immuable et d'une précision chirurgicale, c'est le brouillard.

Il me semblait que le rôle d'un tirage, au moment où il devait intervenir, était de définir un ordre de choix (1er à choisir, 2e à choisir...) mais cela reste très brumeux si l'on se retrouve ensuite avec des qui n'ont pas choisi...

Je peux toutefois te livrer une fonction qui te permettra d'améliorer ta procédure par rapport à ALEA...

Function TIRAGESELEC(n As Integer)
    Dim tabt(), tablo() As Integer, i%, m%, x%
    Application.Volatile False
    If n < 1 Then
        TIRAGESELEC = CVErr(xlErrNum)
        Exit Function
    End If
    m = Selection.Cells.Count
    ReDim tabt(1 To m)
    If m > n Then
        For i = n + 1 To m
            tabt(i) = CVErr(xlErrNA)
        Next i
        m = n
    End If
    ReDim tablo(n)
    For i = 1 To n
        tablo(i) = i
    Next i
    Randomize
    For i = 1 To n
        x = Int(n * Rnd + 1)
        tablo(0) = tablo(x)
        tablo(x) = tablo(i)
        tablo(i) = tablo(0)
    Next i
    tablo(0) = tablo(n)
    x = Int(n * Rnd + 1)
    For i = 1 To m
        tabt(i) = tablo((x - 1 + i) Mod n)
    Next i
    If Application.Caller.Rows.Count > 1 Then
        TIRAGESELEC = Application.Transpose(tabt)
    Else
        TIRAGESELEC = tabt
    End If
End Function

Fonction matricielle. En argument le nombre d'éléments parmi lesquels tirer ; le nombre d'éléments tirés étant défini par le nombre de cellules sélectionnées avant de taper la fonction.

Exemple : tu sélectionnes 9 cellules ; tu tapes : =TIRAGESELEC(9) ; tu valides (Ctrl+Maj+Entrée) ; les nombres 1 à 9 apparaissent dans tes 9 cellules, dans un ordre aléatoire (sans doublons). Et ne se recalcule pas lors du recalcul...

Utilisation adaptée à ton cas : tu as une liste de 9 noms en AC2:AC10, sur laquelle tu veux appliquer un tirage, soit la mettre dans un ordre aléatoire qui sera l'ordre de priorité de choix.

Tu sélectionnes 9 cellules [NB- si tu en sélectionnes moins, tu n'auras pas tous les noms, si tu en sélectionnes plus, tu auras des #N/A dans les cellules en surplus du nombre de noms]

Tu tapes cette formule :

=INDEX(AC2:AC10;TIRAGESELEC(NBVAL(AC2:AC10)))

La fonction de tirage y est utilisée pour fournir un index (sans doublon) à la fonction INDEX...

Tu valides (Ctrl+Maj+Entrée) et ta liste est réordonnée de façon aléatoire.

Cordialement.

Rechercher des sujets similaires à "tirage sort aleatoire tour doublons"