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

Une possibilité en pièce jointe, ou, du moins, le début d'une possibilité.

j'ai, provisoirement, supprimé certaines colonnes pour "simplifier" les formules du tableau. Elles sont toutes matricielles. Il faut donc les valider avec Ctrl+maj+entrer. En pratique, la formule dans C3 peut être tirée vers la droite et vers le bas. Comme les résultats sont trouvés par tirage au sort (F9 pour obtenir d'autres résultats), on n'est pas certain d'obtenir un optimum dès les premiers tirages. J'ai donc mis une macro faisant 500 tirages et ne conservant qu'un de ceux employant le plus de bénévoles ce mois là (cf. la valeur de F1).

Il y a des améliorations à faire (remettre les colonnes jaunes que j'ai supprimées). A toi de me dire si cela te semble aller dans la bonne direction avant de continuer.

@ plus

 

[CISCO]

Bonjour

Idem avec les colonnes jaunes. Dans la macro faisant au max 500 tirages, j'ai mis un test pour arrêter les calculs dès que tous les bénévoles ont 4 permanences.

@ plus

 

[jeyl35]

Merci pour ta réponse.

je regarde ça lundi, je suis en séminaire. Un grand merci à toi.

 

[jeyl35]

Bjr Cisco,

Merci pour ton travail. Je me pose juste quelque questions:

A quoi sert le tableau résultats conservés?
Si je comprends bien, on appuie sur F9 jusqu'à ce que l'on arrive à 60 en haut à gauche?

Merci d'avance

 

[CISCO]

Bonjour

Les formules comportent une partie utilisant la fonction ALEA. On n'obtient donc pas forcément la "meilleure" répartition possible dès le début (la "meilleure" répartition est obtenue lorsque chaque bénévole effectue 4 permanences, et, comme dans ton exemple, il y a 15 bénévoles, cela donne effectivement 15 * 4 = 60 dans D1). En appuyant sur F9, tu lances un nouveau tirage et Excel recalcule toutes les formules de la plage D3: D33. Cela change aussi la valeur dans D1, inférieure ou égale à 60. Tu peux appuyer autant de fois que tu le veux sur F9, pour obtenir un nouveau tirage, et une valeur plus proche de 60, ou 60 dans D1. Il ni y a pas une seule répartition donnant D1 = 60, mais plusieurs, mais elles ne sont pas forcément obtenues rapidement.

Lorsque tu cliques sur le bouton "Tirage au sort", tu lances la macro qui automatise le recalcul (Tu n'as plus à appuyer sur F9, c'est la macro qui fait le travail pour toi). Elle s'arrêtera automatiquement, soit parce que D1 = 60, soit parce que 500 tirages ont été faits (c-à-d comme si tu avais appuyé 500 fois sur la touche F9). Elle écrit les résultats les plus intéressants (c-à-d ceux avec D1 le plus proche possibles de 60, ou avec D1=60) dans le tableau à droite, au fur et à mesure de son déroulement. Regarde les valeurs dans R1 : Elles vont en croissant, et la valeur 60 n'est pas forcément obtenue rapidement. Dans ce tableau, il ni y a plus de formules, mais des valeurs. Si la répartition te convient, c'est fini. Si non, soit tu fais de nouveau avec F9, soit tu relances la macro avec le bouton "Tirage au sort".

@ plus

P.S : Si tu veux travailler avec plus de bénévoles, il faut changer dans la macro la partie "A37:A51". Par exemple, si tu as deux bénévoles en plus, cela donnera "A37:A53". Il ni a rien d'autre à faire. La macro cherchera à obtenir la valeur la plus proche de 17 * 4 = 68 dans R1.

 

[jeyl35]

Salut,

Merci pour ta réponse. C'est bcp plus clair. Ton système est très bien car hyper adaptable.

J'ai 2 pb qui résident encore dans le fichier.

Le 1er: Lorsque je modifie la macro A37:A51 et met A37:A104 par exemple car j'ai plus de bénévoles. Les calculs se font toujours jusqu'à A51 en n'intègrent pas le reste des personnes qui suivent dans le planning du dessous. (D'ailleurs la valeur ds R1 reste toujours à 60)

Le second: Plus aucun calcul ne se fait lorsque je modifie les dates. C'est-à-dire quand je passe sur le mois de novembre par exemple.
Un grand merci à toi.

Cdt,

jeyl35

 

[CISCO]

Bonjour

J'essaye de regarder ça ce soir ou un peu plus tard.

@ plus

 

[CISCO]

Bonsoir

Salut,
.....
Le 1er: Lorsque je modifie la macro A37:A51 et met A37:A104 par exemple car j'ai plus de bénévoles. Les calculs se font toujours jusqu'à A51 en n'intègrent pas le reste des personnes qui suivent dans le planning du dessous. (D'ailleurs la valeur ds R1 reste toujours à 60)

Il faut aussi changer les plages utilisées dans les formules du tableau D3: D33. C'est fait dans la pièce jointe. J'y ai même mis des plages dynamiques, qui s'adaptent automatiquement au nombre de bénévoles que tu as. Donc, normalement, tu ne devrais plus avoir besoin de changer ces formules.

Le second: Plus aucun calcul ne se fait lorsque je modifie les dates. C'est-à-dire quand je passe sur le mois de novembre par exemple.

J'ai changé les formules dans les plages B3:B33 et B36:AG36 (Regarde le contenu de ces cellules, tu comprendras mieux. Par ex, on a B36 = B3). Pour changer de mois, il suffit de changer la date dans B3. Si tu ne dispose pas les jours de repos dans une seule plage en dessous de B36:AG36, mais dans plusieurs plages, une par mois, il faudra procéder autrement.

Maintenant, la macro fait systématiquement les 500 tirages. Tu peux mettre une valeur plus petite dans la macro à la place de ce 500 dans la ligne de code For i=1 to 500.

@ plus

 

[jeyl35]

Salut Cisco,

Merci bcp pour ton travail et ce fichier. Afin que je puisse faire vivre le ficher dans le temps (et que je t'embête moins aussi ), j'essaie de comprendre le fonctionnement des formules et les modifs que je peux y apporter lorsque j'ai des données qui vont changer. Je te soumets mes questions pour voir si je suis dans le vrai.

1) Si je souhaite dupliquer le modèle sur 3 mois par ex: j'ajouter des ligne sous la dernière date en jaune (2/10/2016) et en ajoute le même nombre à la suite de la colonne AG? Du coup il faut que je modifie le contenu de 'plage repos' je suppose? et dans la macro je modifie toutes les plages de cellules correspondantes?

2) Si je souhaite modifier la règle du 4 max par une autre valeur: Je modifie dans les cellules la partie <4 par l'autre valeur? je pense qu'il y a une valeur à modifier dans la macro?

3) Enfin si je souhaite intégrer plus de possibilités sur une journée je copie la formule des cellules rouges dans les cellules grises des colonnes L, N et P dans le tableau de gauche?

J'ai tenté toutes ces choses sur le fichier et je n'arrive pas à le faire fonctionner. En réalité, dès que j'ai ajouté une ligne, tout le tirage s'est effacé. J'ai tenté ensuite de le faire réapparaitre en bricolant tout ce que j'ai dit en haut. En vain.

Enfin, je n'ai pas bien saisi la fonction du tableau de droite. Tu as rempli à la main le contenu des cellules rouges de façons à retrouver en R1 le résultat nombre de bénévoles X rotations max autorisées soient 4 dans le fichier ? Car rien ne change dans ce tableau au fur et à mesure des tirages.

Merci pour tes réponses et ton aide précieuse.

Bonne fin de journée à toi

 

[CISCO]

Salut Cisco,

Merci bcp pour ton travail et ce fichier...

1) Si je souhaite dupliquer le modèle sur 3 mois par ex: j'ajouter des ligne sous la dernière date en jaune (2/10/2016) et en ajoute le même nombre à la suite de la colonne AG? Du coup il faut que je modifie le contenu de 'plage repos' je suppose? et dans la macro je modifie toutes les plages de cellules correspondantes?

Si tu veux faire le tirage sur plusieurs mois en une seule fois, il faut insérer des lignes à partir de la ligne 34. Le nom plagereposjour s'adaptera automatiquement. Par contre il faudra modifier dans la macro tout les 33, en remplaçant ce nombre par le n° de la dernière ligne de ton nouveau tableau.

2) Si je souhaite modifier la règle du 4 max par une autre valeur: Je modifie dans les cellules la partie <4 par l'autre valeur? je pense qu'il y a une valeur à modifier dans la macro?

* Dans les formules : Oui, il faut y mettre la nouvelle valeur à la place du 4. Si tu veux, je peux mettre ce 4 dans une cellule, et la formule ira lire cette valeur. Ainsi, si tu veux changer celle-ci, tu n'auras plus à modifier les formules. Il te suffira de modifier la valeur dans cette cellule.
* Dans la macro : Il ni y a rien à changer dans la dernière version car celle-ci fait systématiquement les 500 essais (la ligne 'If max = Range("A37:A109").Rows.Count * 4 Then i = 500 ne "sert à rien" (donc le 4 dedans aussi), ce que l'on voit avec l'apostrophe au début de cette ligne de code (symbole qui, normalement, introduit un commentaire, mais ici, m'a servi à mettre sur la "touche" cette ancienne ligne, au cas où j'aurai besoin de la réintroduire dans une version ultérieure), et aussi à sa couleur verte).

3) Enfin si je souhaite intégrer plus de possibilités sur une journée je copie la formule des cellules rouges dans les cellules grises des colonnes L, N et P dans le tableau de gauche?

Oui. Tu peux copier toute la plage J3:J33 et la coller dans L3, puis dans N3, puis dans P3.

Enfin, je n'ai pas bien saisi la fonction du tableau de droite. Tu as rempli à la main le contenu des cellules rouges de façons à retrouver en R1 le résultat nombre de bénévoles X rotations max autorisées soient 4 dans le fichier ? Car rien ne change dans ce tableau au fur et à mesure des tirages.

La macro ne trouve pas dès le début une possibilité avec toutes les cellules rouges occupées dans la plage C3: C33. Donc, elle stocke dans la plage R3:AE33 une possibilité comportant x cellules rouges occupées. Si lors d'un autre tirage, elle trouve une autre possibilité, avec y cellules rouges occupées, avec y > x, la macro l'inscrit dans R3:AE33. Elle fait au maximum 500 essais, et inscrit au fur et à mesure la dernière meilleure possibilité dans R3:AE33, en écrasant la précédente, moins bonne. Si la valeur dans R1 ne change pas, c'est qu'elle a trouvé dès le début une des meilleures possibilités, avec un max de cellules rouges occupées. Dans les essais que j'ai fait, j'avais par ex R1= 120, puis un peu après 122, puis 123, puis 124. Après effectivement, cela ne changeait plus, ce qui est logique puisqu'il y a 124 cellules rouges disponibles (31 * 4 = 124).

J'essaye de te faire ce soir un exemple avec deux mois. Ainsi tu verras la différence. Ne te restera plus qu'à tout comprendre pour le faire pour trois mois, si tu en as besoin.

@ plus

 

[CISCO]

Bonsoir

Cf. en pièce jointe une possibilité avec 2 mois.

Pour obtenir ce nouveau fichier, j'ai :
* inséré des lignes en dessous de la ligne 34
* tiré (=copier coller) les formules dans A33 et B33 vers le bas
* tiré les formules le plus à droite dans les lignes 66 et 67 vers la droite aussi longtemps que désiré, jusqu'en BK
* modifié les noms nom et plagereposjour dans le gestionnaire de noms
* inscrit dans H1 la valeur 4 et du texte dans G1
* modifié le <4 par un <$H$1 dans la formule dans D3
* validé cette formule en matriciel avec les 3 touches Ctrl+maj+entrer (d'où les {})
* tiré la formule de D3 vers le bas
* copié la plage D3: D64 pour la coller dans F3, H3 et J3
* remplacé les 33 par des 64 dans D1 et R1
* remplacé les 33 par des 64 dans la macro (j'ai aussi réorganisé l'ordre des lignes, mais pour d'autres raisons)

@ plus

 

[jeyl35]

Bonjour Cisco,

C est top ton fichier. j'ai enfin compris pour effectuer es modifications et l'ai d'ailleurs fait pour tester. et le tirage fonctionne nickel.
il me reste une dernière question concernant le tableau de droite. E ajoutant des dates et des participants, la valeur en D1 varie en fonction des tirages, ce qui est normal. Par contre, la valeur en R1 ne bouge jamais alors qu'elle est en dessous de celle en D1. Si j'ai bien compris, la tableau de droite prend la meilleure répartiton possible et ne la modifie que si une autre meilleure arrive sur le tableau de gauche. J'ai peut-être oublié de faire une manip. je t'ai mis le fichier en Pj.

Merci encore pour le temps que tu y consacres.

jéyl35

 

[CISCO]

Bonjour

Est-ce que tu ne te serais pas trompé de pièce jointe ? Dans les cellules rouges, il ni y a pas les formules matricielles en INDEX($C$1:$C$20;PETITE.VALEUR((... et la macro présente est faite pour un tableau beaucoup plus grand...

Dans mon dernier exemple, il y a 61 lignes * 4 colonnes = 244 cellules rouges. La valeur max dans D1 comme dans R1 est donc égale à 244. En pratique, dans R1, la valeur max que j'ai obtenue après 3 ou 4 tentatives (donc au max 4 * 500 =2000 essais) est de 225. Cela signifie que 2000 essais ne suffisent pas pour positionner à coup sûr plus de bénévoles en fonction des disponibilités de cet exemple. Cela m'étonne un peu que l'on n'approche pas davantage la valeur de 244, mais bon, comme le tirage est fait au hasard...

@ plus

 

[jeyl35]

Re,

Désolé, je t'envoie un mauvais fichier. Voici le bon en PJ. dans celui-ci j'ai fait tourner le tirage sur 3 mois. Le tirage fonctionne, par contre pas de mouvement sur le tableau de droite alors que D1 est bien supérieur à R1 à chaque tirage.
Du coup, je pense qu'il y a un oubli peut-être.

Merci

Jeyl35