XL 2010 Tirage au sort avec conditions

[jeyl35]

Bonjour à tout le forum,

Je souhaite créer un système de rotations horaires sur la base du volontariat.
Je n'ai que Excel pour le créer.
En fonction du nombre de rotations horaire données sur un mois, je tire au sort dans une liste de bénéficiaires établie un noms au hasard pour l'attribution de la rotation. J'arrive à créer le tirage au sort selon liste.
Seulement mon affaire se complique, car je ne sais pas comment intégrer 2 conditions dans le tirage:

La première : les bénéficiaires en repos ne peuvent pas participer au tirage pour les rotations données sur leur jours de repos.

la seconde: Sur une période d'1 mois, chaque bénéficiaire ne peut-être tiré au sort que 4 fois max (du coup même si des rotations données ne sont pas honorées à la fin ce n'est pas grave).

Je vous joins un fichier avec le tableau du haut qui correspond aux rotations données (cases en jaune) et la case en rouge qui correspond au tirage au sort simple.

le tableau du bas correspond aux bénéficiaires (soit la liste de tirage au sort) et leur planning sur les journées en question (le 1 sur cases grises correspond au jours de repos.)

Merci d'avance pour votre aide.

Jeyl35

 

[CISCO]

Bonjour

J'ai regardé les formules et la macro, et je ne vois pas d'oubli. J'ai lancé la macro et ai vu la valeur de R1 progresser de 250 à 270. Et j'avais bien toujours D1 <= R1. Je regarderai davantage ce soir pour voir s'il y a vraiment un problème.

@ plus

 

[CISCO]

Bonjour

J'ai relancé la macro avec 500 et 5000 essais, et j'obtiens 273 permanences d'assurées au max en R1. C'est long et cela m'étonne qu'on n'obtienne pas mieux...
Avec autant de bénévoles et de jours, je ne pense pas que faire autant de calculs avec des formules est la bonne méthode. Cela prend du temps à cause de l'affichage des résultats de ces nombreuses formules. Normalement, on peut empêcher cet affichage avec ScreenUpdating = false dans la macro, mais cela ne fonctionne pas avec ALEA. Il faudrait tout faire en VBA, mais c'est au dessus de mes compétences actuelles dans ce domaine. J'essayerai quand même dans les jours à venir.

@ plus

 

[jeyl35]

Bonjour,


Je pense savoir pourquoi je n'ai rien qui change en R1. Je ne réinitialise pas la macro quand j'ouvre le fichier la 1ère fois. En gros, je fais les modifs de date et de volume de bénévoles et je fais F9, du coup R1 ne change jamais et reste avec les anciennes valeurs. J'ai été dans le développeur et j'ai fait activer la macro et là tout a fonctionné et R1 s'est adapté au nouveau tableau.

Est-il possible de réinitialiser la macro? (sans faire défiler tous les tirages en auto). Ce n'est pas trop grave si ce n'est possible, on patientera et puis c'est tout.

Il est vrai que c'est étonnant que l'on ait pas un résultat de permanences plus élevées au vu des possibilités.

On a 73 bénévoles dans le fichier joint avec 4 max donc 292 permanences qui peuvent être données. Et nous avons 91 cellules rouges *4 soit 364 possibilités pour intégrer les 292 permanences. J'ai mis un compteur tout à droite pour voir ce que cela donnait en réalité par bénévoles

Pour te simplifier le pb peut-être, en priorité il me faut pouvoir assurer l’équité entres les bénévoles (qu’ils aient tous le même nombre de permanences, soit 4 ds le fichier). Ce qui veut dire que j’aurais forcément à chaque fois autant ou plus de possibilités de cases rouges que de possibilités max pour les bénévoles. Les quelques cases vides qui demeureront non prises je m’en débrouillerais.

Je ne sais pas si ça te simplifie le pb.


Merci

 

[CISCO]

Bonjour

Bonjour,
Je pense savoir pourquoi je n'ai rien qui change en R1. Je ne réinitialise pas la macro quand j'ouvre le fichier la 1ère fois. En gros, je fais les modifs de date et de volume de bénévoles et je fais F9, du coup R1 ne change jamais et reste avec les anciennes valeurs. J'ai été dans le développeur et j'ai fait activer la macro et là tout a fonctionné et R1 s'est adapté au nouveau tableau.

R1 ne change que si on utilise la macro, pas en appuyant sur F9 qui n'agit que sur le tableau de gauche et indirectement sur D1.

Est-il possible de réinitialiser la macro? (sans faire défiler tous les tirages en auto).

Et non, à cause du ALEA.

On a 73 bénévoles dans le fichier joint avec 4 max donc 292 permanences qui peuvent être données. Et nous avons 91 cellules rouges *4 soit 364 possibilités pour intégrer les 292 permanences.

Et oui, en faisant tourner la macro avec 5000 essais, j'espérai trouver un résultat plus proche de 292.

Pour te simplifier le pb peut-être, en priorité il me faut pouvoir assurer l’équité entres les bénévoles (qu’ils aient tous le même nombre de permanences, soit 4 ds le fichier).

J'essaye de faire un code faisant tous les calculs en VBA, sans formule directement dans les cellules, sans rien afficher, si ce n'est la dernière répartition intéressante dans le tableau de droite. Cela devrait tourner beaucoup plus vite, et on devrait ainsi pouvoir demander plus d'essais, et donc trouver un résultat plus proche du max possible, 292 dans l'exemple actuel. Ce n'est pas évident, mais bon, j'y apprendrai peut être quelque chose en VBA au passage.

@ plus

 

[jeyl35]

Re,
Merci pour ton travail.
En fait, je me dis que c'est peut-être plus simple pour toi de modifier la formule en indiquant que l'on souhaite avoir 4 dates par bénévoles dispatchés au hasard dans les cellules rouges, comme ça l'équité entres eux est respecté et le surplus de cellules rouges vides disponibles je peux le gérer.
Du coup, en fonction du nombre de bénévoles et de cellules rouges dispo, j'ai juste à calculer le volume de permanences max par personnes que je peux affecter à tous les bénévoles.
Je ne sais pas si c'est plus simple à faire pour toi par contre

 

[jeyl35]

Salut à toi Cisco,

Est-ce que ce serait plus simple si j'avais pour chaque journée, la liste des présents. c'est-à-dire que sur le planning du bas, sur chaque date, j'ai une liste de noms (exit les 1 qui symbolise les jours de repos). Du coup, adapter la formule pour qu'il fasse le tirage sur chaque journée dans la limite de 4 (ou x nombre) sur l'ensemble des tirages?

 

[CISCO]

Bonsoir

Il y a effectivement beaucoup de possibilités, mais je ne sais exactement quelle est la plus facile à programmer, et la plus rapide...

Depuis mon dernier post, hier, j'ai un peu "travaillé". Cf. le résultat en pièce jointe.

On a toujours dans H1, le nombre max de permanences à effectuer par chaque bénévole, 4 par exemple (valeur que tu peux changer bien sûr).

Le principe de la macro :
* Cellule D3.
Excel tire au hasard un nom dans la liste des noms.
Si le bénévole choisi est de permanence ce jour là, donc déjà marqué sur la ligne en cours, dans une des autres colonnes rouges,
ou s'il n'est pas disponible,
ou s'il a déjà 4 permanences à effectuer
Excel tire de nouveau au hasard un nom dans la liste des noms

Sinon, Excel écrit dans D3 ce nom

* Cellule D4
Idem

* Cellule D5
Idem
* etc

Une fois la colonne rouge D remplie, on passe à la colonne F, puis à la H et ainsi de suite.

Donc on remplie le tableau colonne rouge par colonne rouge. Plus la macro avance, plus elle doit effectuer des tirages pour trouver un bénévole non déjà nommé sur la ligne en cours, disponible, et n'ayant pas déjà 4 permanences à effectuer. J'ai donc mis un nombre d'essais max de 1000. Si Excel n'a pas trouvé de nom adéquate au bout de ces 1000 essais, la cellule reste vide et Excel passe à la cellule rouge en dessous. Par conséquent, on peut avoir parfois des cellules vides.

Conditions à respecter :
* Rien d'écrit dans la colonne A en dessous du dernier nom
* Première date dans B3
* une ligne blanche, et une seule, au dessus de la ligne lun, mar, mer, jeu... (95 dans le fichier ci-joint).
Si tu veux plus de jours, il faut insérer ces lignes à partir de la ligne 95.

Pour tester, j'ai mis tous les bénévoles indisponibles le 18/09, sauf les bénévoles J, K et L. Ca fonctionne : On n'a que ces trois noms sur la ligne 18 correspondant à cette date (mais pas forcément les trois).

Il ni a plus de formule dans cette feuille, sauf dans D1.

Teste, par ex avec H1 = 5, tu verras, ça tourne beaucoup plus vite qu'avec des formules un peu partout, comme dans les versions précédentes, et on obtient le nombre max de permanences assurables systématiquement. Si la répartition ne te convient pas, il te suffit de relancer la macro, et 2 secondes après, tu as une nouvelle possibilité.

@ plus

P.S : Si cela t'intéresse, je peux très facilement modifier la macro pour avoir un nombre max de permanences variant en fonction de chaque bénévole, 3 pour l'un, 4 pour l'autre...

 

[jeyl35]

Salut,
Merci pour le fichier, ça fonctionne très bien, tu es un génie.
Si je souhaite diminuer ou augmenter le nombre de bénévoles (A169 dans le fichier). Quelle ligne dois-je modifier dans la macro car tu mets dans les règles à respecter de ne rien écrire sous le dernier nom en colonne A?

L'idée est top pour le tirage cellules par cellules, par contre, ça me génère un autre pb.
En effet, dans la réalité, les cellules rouges ne seront jamais à suivre: j'aurais 3 possibilités le 1er septembre, 4 le 2/09, 5 le 03/09 et 0 le 04/09 par exemple. (Les cellules jaunes me permettent de remplir les personnes qui donnent leurs horaires pour les bénévoles). Je n'avais pas ce pb avec les formules dans l'autre fichier car je supprimais la formule dans la cellule rouge que je ne souhaitais pas faire participer au tirage (car il n'y avait pas d'horaires données en face).

Du coup, est-il possible de faire fonctionner le système que pour les cellules rouges où il y a une cellule jaune en face de renseignée?

J'ai pensé à refaire le tableau en ne mettant à suivre que les dates et horaires à prendre mais c'est ingérable car il y a les jours de repos.
Redis mois si c'est faisable pour toi.

Encore un grand merci pour ton aide

 

[CISCO]

Bonjour

Salut,
Merci pour le fichier, ça fonctionne très bien, tu es un génie.
Si je souhaite diminuer ou augmenter le nombre de bénévoles (A169 dans le fichier). Quelle ligne dois-je modifier dans la macro car tu mets dans les règles à respecter de ne rien écrire sous le dernier nom en colonne A?

Dans la nouvelle pièce jointe ci-dessous, on peut écrire de nouveaux noms en dessous de la ligne 169 (ou en supprimer) dans la colonne A. Normalement, la macro les prendra automatiquement en compte (sauf si j'ai oublié un détail ). Mais il ne faut pas qu'il y ait autre chose qu'un nom, car ce que tu vas rajouter sera considéré comme un nom et pourra donc être mis dans les cellules rouges. Il ne faut pas non plus des vides dans cette liste de noms.

En effet, dans la réalité, les cellules rouges ne seront jamais à suivre: j'aurais 3 possibilités le 1er septembre, 4 le 2/09, 5 le 03/09 et 0 le 04/09 par exemple...
Du coup, est-il possible de faire fonctionner le système que pour les cellules rouges où il y a une cellule jaune en face de renseignée?

J'avais pensé à ce "détail", mais comme tu n'en parlais pas... Cela n'est pas très difficile de modifier la macro en conséquence pour n'écrire un nom que dans les cellules rouges correspondant à une cellule jaune remplie. C'est fait en pièce jointe.

Petit détail au sujet des cellules jaunes : il faut absolument qu'il ni ait pas un des noms de la liste dedans. Si par hasard, tu as un Pierre dans la liste, et que tu dois écrire ce même nom dans une cellule jaune, il faudra le modifier, par ex en rajoutant un espace après, ou une étoile, ou...

@ plus

P.S : Il y avait une petite erreur dans la macro du fichier précédent Classeur1tirageausort3moisbis, un +1 à la place d'un -1 dans la ligne de code Or Cells(ChiffreTire, Ligne + 1) = 1 Then. Pour preuve, le test que j'avais positionné le 18/09 agissait sur la ligne du 16/09 !!! Je l'ai corrigé dans mon précédent post.

 

[CISCO]

Bonsoir

Petite amélioration de la macro en remplaçant les 94 par des Positionnom - 2.

@ plus

P.S : 1/8/2016 : Encore une petite modif du fichier.

 

[jeyl35]

Re Cisco

Je reviens de congés et en profite pour te remercier pour tout le travail effectué.
Je fais le premier test "grandeur nature" demain" avec ta superbe matrice.

Jeyl35

 

[CISCO]

Bonjour

Alors, qu'est-ce que cela donne ?

@ plus