Planning tirage au sort et conditions

Bonjour à tous,

Je suis face à une problématique de taille !

J'aimerai automatiser le remplissage d'un planning de permanences à réaliser.

L'idée est la suivante :

Quand chaque collaborateur a renseigné ses jours de présence et disponibilité,

j'aimerai qu'Excel me propose automatiquement une répartition.

Plus de détails :

PUIS ma mission est de renseigner équitablement qui ouvre et qui ferme. Avec certaines conditions à respecter dans les colonnes w et x.

1ER CONDITION : Le nombre de permanences est calculé en fonction du nombre de jours de présences :

Exemple : Sébastien est présent 24 JOURS il doit donc réaliser 8 permanences (E45 et E46)

2ème CONDITION :

Celui qui ferme ne peut pas être d’ouverture le lendemain matin (afin de respecter une amplitude de repos) sauf si pas d’autres possibilités,

Celui qui ouvre ne peut pas fermer le même jour

3ème CONDITION : Tenter de ne pas positionner plus de 4 permanences hebdomadaires pour la même personne

4ème CONDITION : Tenter d’équilibrer le nombre d’ouvertures et de fermetures

Et Dernière condition : En fonction des demandes de chacun tenter de respecter s’ils ne peuvent pas fermer ou ouvrir certains jours en respectent le code couleur indiqué.

J’ai tenté la fonction tirage sans doublons mais je n’y arrive pas …. Pas assez expérimentée !!

Si avez une fonction ou une petite formule magique …. Je suis preneuse !!

Plannings Permanences Décembre (003).xlsx

Merci à celles ou ceux qui seront trouver "la solution" !!!

Béatrice

Salut Béatrice,

j'attaque ta demande!

Questions:

• quid de Lucille, Jérémy, Frédéric et Germain : punis ?
• cellules noires, cellules vides, même combat : absent ou jour férié ?
• ton tableau-horaire va toujours de la ligne 2 à 43 ?
• si le nombre d'employés venait à évoluer, je peux postuler que les en-têtes de colonnes de la permanence auront toujours le même libellé (Ouverture - Fermeture) ?
• ton horaire est une période fixe par feuille, qui ne "glisse" pas, je suppose ?
• j'imagine donc que tu as plusieurs feuilles et qu'elles ont STRICTEMENT la même structure ?

Je commence en me basant sur des réponses potentiellement positives !

A+

Bonsoir Curulis57

pour répondre à tes questions :

- quid de Lucille, Jérémy, Frédéric et Germain : punis ?

**Punis presque .... Non je plaisante, ils ne font plus partis de la société, en revanche potentiellement remplacé(s) par de nouveaux arrivants....

- ton tableau-horaire va toujours de la ligne 2 à 43 ?

**On va considérer que oui, cela me laisse toujours 6 semaines d'avances...(une feuille tous les mois)

- si le nombre d'employés venait à évoluer, je peux postuler que les en-têtes de colonnes de la permanence auront toujours le même libellé (Ouverture - Fermeture) ?

**oui Toujours ouverture et fermeture et le prénom que change éventuellement,...

• ton horaire est une période fixe par feuille, qui ne "glisse" pas, je suppose ? ** Oui
• j'imagine donc que tu as plusieurs feuilles et qu'elles ont STRICTEMENT la même structure ? **Oui

Merci à toi !!!!

Béatrice

Salut Béatrice,

Autres questions (je vois que tu es en congé : tu as un peu le temps, donc ! )

• à la lecture des conditions et pour respecter certaines d'entre elles, il me faudrait lors du calcul d'une feuille, connaître l'horaire du premier jour suivant ! Tu vois pourquoi, j'imagine !?
• est-il envisageable de colorer ces "anciens" d'une couleur autre que le noir ? Cela faciliterait le travail de la macro en les éliminant d'office du calcul !

A+

Oui tout a fait !! en congés

La permanence d'ouverture est 6h 14h

La permanence de Fermeture est 14h 21h

Oui pour une autre couleur !!

Béatrice

Salut Béatrice,

première étape cosmétique.

Le calcul que tu attends n'est pas encore fait. Je l'entame dans la foulée.

Bon, comme je suis un emm..., j'aime bien arranger certaines choses à ma sauce!

J'espère que ce chamboulement te plaira!

Explications

- tes jolies petites images contenant les significations des symboles et couleurs ont été supprimées et installées à demeure en ligne 1 à droite et servent de palette d'outils pour régler ton horaire. L'outil sélectionné est reproduit en [A1].

Emm... jusqu'au bout, peux-tu valider les ajustements des libellés de ces options (histoire de tenir dans l'étroitesse des cellules) :

FORM -> FOR

ARTT -> ART (arrêt de travail ? Accident de travail ? -> AT serait bien, alors!

* un simple clic sur une de ces options te permet de faire UN changement dans l'horaire avant désactivation ;

* un double-clic sur une de ces options te permet de faire autant de changements dans l'horaire que nécessaire. Pour désactiver, un clic dans [A1].

• les options-horaire de base (1 - R) sont directement accessibles via un clic- DROIT selon la séquence 1-R-Vide ;
• tu peux, via une sélection "glissée" de plusieurs cellules, verticales (si cette verticale n'est pas située après le tableau-horaire) ou horizontales, régler une période de congés ou fermeture (cellules noires). En repassant sur cette sélection noircie, elle revient à la normale mais l'horaire original est évidemment perdu!

• j'ai changé tes formules du tableau de droite : un changement de prénom à gauche sera pris en charge automatiquement ;
• j'ai changé "Ouverture-Fermeture" par "OPEN- CLOSE" ;
• entre ces deux mentions et la palette d'outils à droite se trouve un rappel du mois en cours ;
• un double-clic sur les prénoms à gauche (dés)activent ces employés (présents = NOIR, ou pas = ROUGE) ;

Questions

• les colonnes vides en [Y-Z-AQ] ?? Embêtant, ça...
• Bruno ne fait pas partie du tableau de droite ??
• les valeurs en colonne [V] peuvent-elles être calculées automatiquement ? Signification ? Me regarde pas, je sais...
• le rappel en rouge en bas à droite en dehors du tableau "Bruno CP" : utile ? Me regarde pas, je sais...

Idées

- je propose de créer les nouvelles feuilles (sauf si déjà fait) au fur et à mesure de deux façons :

* soit automatiquement à partir d'un certain jour du mois si elle ne l'a pas encore été manuellement ;

* manuellement en double-cliquant sur une cellule-date en colonne ['B].

Je la vois bien se créer en première position en faisant reculer les autres vers la droite.

- un clic-DROIT en colonne [A] cacherait ou dévoilerait les lignes supérieures du tableau.

Je pense à ça parce que mon écran n'affiche pas tout l'horaire. Tu as peut-être un écran XXL ?

- ce serait chouette de penser à garder de feuille en feuille, le récapitulatif du solde "Permanences" comme tu le calcules en ligne 47 car je réfléchis à un système de calcul des permanences qui tiendrait compte de cet équilibre, histoire d'éviter les frustrations de certains irascibles.

Ai-je oublié quelque chose ? Sans doute...

Teste et re-teste et renvoie-moi les défauts d'utilisation et tes idées.

A+

Salut Béatrice,

ta demande est sur le point d'être rencontrée!

Je passe tout ça en tableaux, derniers tests et j'envoie !

A+

Bonjour,

Merci pour ton travail, suis de retour dès lundi !

Je testerai tout , mais vraiment merci !!

Beatrice

Salut Béatrice,

ça n'a pas été sans mal (voir, optionnellement, le lien ci-dessous ), entièrement de ma faute, forcément, mais c'est fait !

https://forum.excel-pratique.com/viewtopic.php?f=2&t=135469

Merci à Eriiic et LouReed de m'avoir remis sur le bon chemin !

Le temps perdu à démêler les neurones ne m'a pas permis de développer les idées émises dans un post précédent.

Y as-tu réfléchi ?

Je me suis permis d'ajouter une condition supplémentaire : pas de fermeture avant un jour "R" ou "CP" !

Fonctionnement

• un double-clic en [W1:X1] pour démarrer le calcul ;
• [W:X] s'effacent le temps pour la macro de trouver une combinaison : de rien du tout à plusieurs dizaines de secondes selon la "chance" de la macro ;
• je laisse 10.000 boucles DO...LOOP pour trouver un résultat positif sinon, affichage d'un message t'invitant à relancer le calcul ;
• tu peux évidemment relancer le calcul si le "choix" de la macro ne te plaît pas.

Je me réserve la soirée et je reviens vers toi demain pour la suite du feuilleton XBDEO. Y'a encore du taf' !

A+

Bonjour,

Pour commencer je te remercie mille fois pour t être penché sur mon petit tableau tellement complexe..

Merci merci merci….

J'ai répondu à tes question ci-dessous :

Explications

- tes jolies petites images contenant les significations des symboles et couleurs ont été supprimées et installées à demeure en ligne 1 à droite et servent de palette d'outils pour régler ton horaire. L'outil sélectionné est reproduit en [A1]. *Ok c’est Parfait

Emm... jusqu'au bout, peux-tu valider les ajustements des libellés de ces options (histoire de tenir dans l'étroitesse des cellules) : ==Oui, en fait ce qui est important c’est d’avoir une visualisation d’un seul coup d’œil des colonnes A à X les autres peuvent être masquées

Les colonnes pouvant être masquées de AA à AP me permettaient de créer un menu déroulant car sans cela je me trompais souvent en y mettant des personnes non présentes en ouvertures ou fermetures….

FORM -> FOR ==OK

ARTT == Aménagement de réduction du temps de travail ou jours de repos -> ART (arrêt de travail ? Accident de travail ? -> AT serait bien, alors! ==Ok je valide

* un simple clic sur une de ces options te permet de faire UN changement dans l'horaire avant désactivation ;

* un double-clic sur une de ces options te permet de faire autant de changements dans l'horaire que nécessaire. Pour désactiver, un clic dans [A1].

• les options-horaire de base (1 - R) sont directement accessibles via un clic- DROIT selon la séquence 1-R-Vide ;
• tu peux, via une sélection "glissée" de plusieurs cellules, verticales (si cette verticale n'est pas située après le tableau-horaire) ou horizontales, régler une période de congés ou fermeture (cellules noires). En repassant sur cette sélection noircie, elle revient à la normale mais l'horaire original est évidemment perdu!

==Cela c’est génial !! Je valide

• j'ai changé tes formules du tableau de droite : un changement de prénom à gauche sera pris en charge automatiquement ; OK Parfait
• j'ai changé "Ouverture-Fermeture" par "OPEN- CLOSE" ; OK
• entre ces deux mentions et la palette d'outils à droite se trouve un rappel du mois en cours ;
• un double-clic sur les prénoms à gauche (dés)activent ces employés (présents = NOIR, ou pas = ROUGE) ;

Questions

• les colonnes vides en [Y-Z-AQ] ?? Embêtant, ça...==Si vides elles ne servent pas…
• Bruno ne fait pas partie du tableau de droite ??

- les valeurs en colonne [V] peuvent-elles être calculées automatiquement ? ==(OUI)

Signification ? Me regarde pas, je sais…

==Cette valeur correspond à 2 = une ouverture + une fermeture, et comme je variais le nombre de semaine par mois à compléter (parfois4 parfois5), cela me calcule le nombre de permanence à réaliser (somme en V46) pour recalculer ensuite le nombre de permanence par collaborateur(De C à U 46). Mais je vois que dans ta formule tout est bien rejoué en ligne 48/49/50 !!l

- le rappel en rouge en bas à droite en dehors du tableau "Bruno CP" : utile ? Me regarde pas, je sais...==A supprimer pas Utile

Idées

- je propose de créer les nouvelles feuilles (sauf si déjà fait) au fur et à mesure de deux façons :

* soit automatiquement à partir d'un certain jour du mois si elle ne l'a pas encore été manuellement ;

* manuellement en double-cliquant sur une cellule-date en colonne ['B]. Me semble Bien

Je la vois bien se créer en première position en faisant reculer les autres vers la droite.

==Une feuille par mois ces mieux ….

- un clic-DROIT en colonne [A] cacherait ou dévoilerait les lignes supérieures du tableau.

Je pense à ça parce que mon écran n'affiche pas tout l'horaire. Tu as peut-être un écran XXL ? non pas d’écran XXL pour moi …

- ce serait chouette de penser à garder de feuille en feuille, le récapitulatif du solde "Permanences" comme tu le calcules en ligne 47 car je réfléchis à un système de calcul des permanences qui tiendrait compte de cet équilibre, histoire d'éviter les frustrations de certains irascibles.

==Exactement !! Tu as tout compris 😊

Ai-je oublié quelque chose ? Sans doute...

Teste et re-teste et renvoie-moi les défauts d'utilisation et tes idées.

==MERCIIIIIIIII, tu es TOP !!!!! et PERFORMENT

Je viens de tester et message ci après

MVB :

Erreur d'execution '1004'

Impossible de lire la propriété randbetween dans la classe Worksheet fonction

iSpot = WorksheetFunction.RandBetween(CInt(43 / iPerm) * (y - 1) + IIf(y = 1, 2, 1), IIf(y = iPerm, 43, CInt(43 / iPerm) * y))

Salut XDBEO,

désolé pour le retard de réponse : j'avais la tête ailleurs !

Je n'ai jamais l'erreur que tu décris donc je ne vois pas quoi te conseiller ! As-tu trouvé, de ton côté ?

Par ailleurs, je prépare une version épurée de ton tableau de droite (apparemment inutile si j'ai bien compris ton commentaire) qui compterait 3 feuilles représentant 3 mois d'horaire successifs.

As-tu besoin de plus de mois "d'avance" et veux-tu garder tous tes horaires en archives (feuille supplémentaire, donc), histoire de garder une trace nostalgique du temps qui passe ?

A+