Affectation des postes de travail par roulements

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Affectation des équipes de travail par roulements

L'enjeu de cette formation Excel est de parvenir à automatiser l'affectation des salariés sur différents postes de travail. Mais contrairement à une formation précédente, il ne s'agit pas de réaliser une distribution aléatoire mais une affectation par roulement.

Dans l'exemple de l'application finalisée, illustrée ci-dessous, 5 salariés sont à affecter sur les 5 postesde travail pour les 52 semaines qui constituent une année. Le point de départ pour le poste 1 en semaine 1 doit être aléatoire. A partir du salarié ainsi positionné, un roulement naturel doit s'opérer sur les 52 semaines. En fonction de l'employé ainsi désigné, les affectations se produisent naturellement sur les autres postes. L'enjeu est qu'un salarié ne soit pas affecté sur deux postes différents pendant la même semaine.



En cliquant sur le bouton Regénérer, l'affectation par roulement prend effet, à partir du premier employé généré aléatoirement. Comme vous le constatez, en haut à gauche du tableau, il est possible de sélectionner l'un des employés à mettre en valeur, par le biais d'une liste déroulante. C'est une mise en forme conditionnelle qui permet de repérer ses affectations de façon évidente. Tout d'abord, nous constatons que le roulement s'effectue parfaitement puisque l'employé est missionné une fois sur cinq pour un même poste. De même, la distribution s'opère dans les règles. Le salarié n'est pas affecté sur deux postes à la fois pour une même semaine.

Une mise en valeur dynamique orange, active sur certains salariés, attire l'attention. Il s'agit d'une alerte visuelle prévenant le responsable que ces personnes sont indisponibles aux dates prévues. C'est un tableau des indisponibilités situé dans une feuille annexe qui en réfère. Le responsable a donc tout le loisir de prendre les mesures afin de prévoir des solutions de remplacement.

Source et présentation de la problématique
Pour ne pas avoir à tout construire et pour disposer de données à manipuler, nous proposons de débuter les travaux depuis un classeur source.

• Télécharger le classeur roulement-equipes-et-affectations.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification du bandeau de sécurité,

Ce classeur est donc constitué de deux feuilles. La première est nommée Affectations. Elle propose la structure du tableau dans lequel les salariés doivent être ventilés, au clic sur le bouton Regénérer. Pour réaliser ces affectations d'équipes par roulement, nous avons besoin de calculs intermédiaires. Ils serviront de base d'extraction pour remplir le tableau.

Ces calculs, pour la plupart, existent déjà dans la feuille Indisponibilités. Vous notez la présence d'un petit tableau référençant les salariés en colonne D. Il doit servir de base au tri aléatoire pour définir le point de départ de l'affectation par roulement.



Juste en-dessous, toutes les absences sont listées dans une table à deux entrées, selon la semaine et le salarié. Elles pourront être mises à jour au fil du temps. Nos règles dynamiques de mise en valeur les intègreront à la volée.



Enfin, les calculs intermédiaires interviennent dans le tableau qui commence à partir de la colonne I. Ils produisent déjà les opérations permettant de simuler le roulement, mais sur des chiffres variant de 1 à 5 pour l'instant. Chacun de ces chiffres doit être associé à un salarié.

La formule est simple, en ligne comme en colonne. Vous pouvez la consulter en sélectionnant par exemple la cellule J7 :

=SI(J6+1<=5;J6+1;$J$2)

Si le chiffre précédent incrémenté d'une unité ne dépasse pas 5 (J6+1<=5), nous l'incrémentons (J6+1). Donc nous désignons numériquement le prochain salarié pour l'affectation au poste. Dans le cas contraire, nous repartons du chiffre 1, soit du premier salarié ($J$2). C'est ainsi que nous réalisons le roulement.

Bien sûr, il convient de transposer ce raisonnement sur les salariés. C'est pourquoi, à partir de la colonne O, nous effectuons l'extraction des noms des salariés. Cette extraction est produite par la fonction Excel RechercheV. En cherchant le numéro précédemment généré dans le petit tableau situé entre les colonnes C et D, nous restituons l'employé en toutes lettres :

=SIERREUR(RECHERCHEV(J2; $C$2:$D$6; 2; FAUX); '')



Point de départ aléatoire
L'affectation par roulement doit donc débuter à partir d'un salarié choisi au hasard. Nous proposons de trier aléatoirement le petit tableau des salariés, placé entre les colonnes D et E.

• Sur la feuille Indisponibilités, sélectionner la plage de cellules B2:B6,
• Taper la formule suivante : =Alea(),
• Puis, valider le calcul par le raccourci clavier CTRL + Entrée,

Il s'agit d'une technique enseignée notamment par le livre pour débuter les calculs avec Excel. Elle permet de répliquer la logique d'un calcul sur une plage de cellules présélectionnées.



Contrairement à la fonction Excel Alea.Entre.Bornes, la fonction Alea génère des valeurs nécessairement uniques. Elles ne présentent donc aucune redondance. Mais comme vous le constatez, il s'agit de nombres réels, donc avec décimales. Qu'à cela ne tienne, nous souhaitons seulement les exploiter pour organiser un tri aléatoire des salariés. Ainsi nous laisserons le hasard définir le point de départ pour l'affectation par roulement.

Mais ce tri doit intervenir au clic sur le bouton de la feuille Affectations. Nous devons donc créer une macro. Cette dernière doit coller les valeurs détachées de leurs formules en colonne E puis réaliser le tri sur cette base. Comme pour toute fonction Excel, Alea est dynamique et le recalcul s'opère à chaque événement sur la feuille. Il est donc nécessaire de détacher ces nombres de leurs formules pour enclencher le tri sur ordre seulement.

Il est important de débuter la simulation des actions à enregistrer depuis la feuille Affectations.

• Cliquer sur l'onglet Affectations en bas de la fenêtre Excel pour activer sa feuille,
• Dans la section Code du ruban Développeur, cliquer sur le bouton Enregistrer une macro,

S'il n'est pas présent sur votre interface, la formation pour débuter la programmation en VBA Excel rappelle comment afficher ce ruban.

• Dans la boîte de dialogue qui suit, la nommer : tri_alea (Sans espace),
• Puis, cliquer sur le bouton Ok pour démarrer l'enregistrement,
• Cliquer sur l'onglet Indisponibilités en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner les nombres aléatoires, soit la plage de cellules B2:B6,
• Les copier à l'aide du raccourci clavier CTRL + C par exemple,
• Sélectionner la cellule de départ pour la destination, soit E2,
• Cliquer sur l'onglet Accueil en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Presse-papiers, Cliquer sur la flèche du bouton Coller,
• Dans la section Coller des valeurs de la liste, cliquer sur le bouton Valeurs,

Nous devons exploiter ces nombres désormais détachés de leurs formules pour trier aléatoirement les salariés.

• Sélectionner le tableau à trier, soit la plage de cellules D2:E6,
• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Trier et filtrer, cliquer sur le bouton Trier,
• Dans la boîte de dialogue qui suit, choisir un Tri sur la colonne E grâce à la première liste déroulante,
• Conserver l'ordre proposé par défaut,
• Puis valider en cliquant sur le bouton Ok,

Le tri est désormais défini sur la colonne des valeurs aléatoires. A chaque clic sur le bouton, elles seront regénérées. Donc les salariés seront complètement réorganisés.

• Cliquer sur l'onglet Affectations en bas de la fenêtre Excel pour afficher sa feuille,
• Puis, sélectionner la cellule A1,

Ainsi, au clic sur le bouton depuis la feuille Affectations, le traitement s'effectue premièrement sur la feuille Indisponibilités. Une fois les actions terminées, le focus est rendu sur la première cellule de la feuille de départ. En conséquence, le changement de feuille est transparent pour l'utilisateur.

• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Code du ruban, cliquer sur le bouton Arrêter l'enregistrement,

La macro existe, mais à l'état virtuel pour l'instant, c'est-à-dire sous forme de code VBA. Nous devons l'attacher au bouton pour qu'elle s'exécute au clic sur ce dernier.

• Sur la feuille Affectations, cliquer avec le bouton droit de la souris sur le bouton Regénérer,
• Dans le menu contextuel, choisir Affecter une macro,
• Dans la liste de la boîte de dialogue qui suit, sélectionner la macro tri_alea,

• Puis, cliquer sur le bouton Ok pour établir le lien,
• Cliquer ensuite sur une cellule vide de la feuille pour désactiver la sélection du bouton,
• Cliquer enfin sur le bouton Regénérer pour exécuter le traitement,

Les actions s'enchainent à la vitesse du processeur. A l'issue du processus, le focus est bien rendu à la cellule A1 de la feuille Affectations. Si vous consultez le tableau des salariés sur la feuille Indisponibilités, vous constatez qu'il a en effet été réorganisé.

Affectations des salariés par formules d'extraction
Il est temps de reproduire le raisonnement des affectations par roulement, pour les cinq postes du tableau de la feuille Affectations. Nous devons importer les noms des salariés inscrits entre les colonnes O et S de la feuille Indisponibilités. Cette importation doit être effectuée sur la correspondance des numéros de semaine. Il s'agit donc de l'élément à chercher. Nous choisissons d'exploiter une fois encore la précieuse fonction RechercheV.

• Sélectionner la première cellule du calcul sur la feuille Affectations, soit C5,
• Taper le symbole = pour débuter la formule,
• Saisir le nom de la fonction d'extraction suivi d'une parenthèse, soit RechercheV(,
• Désigner le numéro de semaine à chercher en cliquant sur la cellule B5,
• Enfoncer trois fois de suite la touche F4 du clavier pour la figer seulement en colonne, ce qui donne : $B5,

Nous souhaitons répliquer ce calcul sur les colonnes des cinq postes. Mais pour chacun d'entre eux, la recherche doit continuer de se faire sur le numéro de semaine situé en colonne B. La colonne ne doit donc pas bouger. Par contre, pour les lignes du dessous, l'affectation par roulement doit considérer les numéros de semaine qui progressent. L'indice de ligne doit donc suivre le déplacement de la formule.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Cliquer sur l'onglet Indisponibilités en bas de la fenêtre Excel pour afficher sa feuille,
• Sélectionner l'intégralité du tableau par ses étiquettes de colonne (De I à S), ou bien saisir ses références, ce qui donne : Indisponibilités!I:S,
• Enfoncer la touche F4 du clavier pour figer ses bornes, soit : Indisponibilités!$I:$S,
• Taper un point-virgule (;) pour passer dans l'argument du numéro de colonne de retour,
• Saisir le chiffre 7,

En effet, les premiers salariés à extraire sont placés dans la colonne O. Cette dernière est la septième colonne de la sélection qui débute en colonne I.

• Taper un point-virgule (;) suivi du texte Faux pour une correspondance exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Valider le calcul par le raccourci clavier CTRL + Entrée pour garder la cellule active,

Le premier salarié est fidèlement restitué. La formule que nous avons bâtie est la suivante :

=RECHERCHEV($B5; Indisponibilités!$I:$S; 7; FAUX)

• Tirer la poignée de la cellule C5 sur la droite jusqu'en G5,

Le calcul est répliqué mais restitue toujours le même salarié. C'est tout à fait logique. Même si nous avons judicieusement figé les cellules dans la formule, l'un des paramètres est une constante à adapter. Il s'agit du numéro de colonne qui ne suit pas le déplacement imposé.

• Dans la formule en D5, remplacer le chiffre 7 par le 8, puis valider,
• Dans la formule en E5, remplacer le chiffre 7 par le 9, puis valider,
• Dans la formule en F5, remplacer le chiffre 7 par le 10, puis valider,
• Dans la formule en G5, remplacer le chiffre 7 par le 11, puis valider,

Cette fois, chaque salarié est parfaitement réparti sur un poste indépendant.

• Sélectionner l'ensemble de ces résultats, soit la plage de cellules C5:G5,
• Puis, double cliquer sur la poignée située en bas à droite de la sélection,

De cette manière, nous répliquons le calcul sur la hauteur du tableau pour toutes les colonnes.

Comme vous le constatez, le roulement est parfaitement orchestré. De plus, aucune incohérence n'est détectée. Aucun salarié n'est affecté sur plusieurs postes à la fois pour une même semaine. C'est encore une fois la précieuse fonction d'extraction RechercheV qui a permis d'importer les données reproduisant la logique de ce fameux roulement.

Rappelons néanmoins sa contrainte fondamentale. Pour produire l'extraction, l'élément cherché doit nécessairement se trouver en première colonne du tableau de recherche. C'est bien le cas ici. La semaine cherchée est située en colonne I, soit la première colonne de la sélection.



Repérer les affectations d'un salarié
Pour simplifier la lecture et l'exploitation de ce tableau, nous devons faire ressortir dynamiquement un salarié, au choix de son nom par le biais de la liste déroulante située en B4. Il s'agit de répliquer la technique que nous avions mise en oeuvre dans la formation pour repérer une donnée dans un tableau. Une règle de mise en forme conditionnelle doit être bâtie sur l'ensemble des cellules du tableau. Cette règle consiste à vérifier l'égalité entre le nom du salarié et celui choisi dans la liste.

• Sélectionner l'ensemble des cellules du tableau, soit la plage C5:G56,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie située juste en-dessous pour l'activer,
• Taper le symbole = pour débuter la syntaxe de la règle,
• Cliquer sur la cellule de la liste déroulante, soit B4,
• Taper le symbole = pour l'égalité à vérifier,
• Sélectionner le premier salarié du tableau, soit la cellule C5,
• Enfoncer trois fois de suite la touche F4 du clavier pour la libérer dans son déplacement,

Le critère que nous venons de construire est le suivant :

=$B$4=C5

Chaque salarié doit être comparé au choix de l'utilisateur en B4. B4 est donc une cellule de référence que nous conservons figée ($B$4). Les salariés quant à eux doivent tous être passés en revue, c'est pourquoi nous défigeons C5. Si l'égalité est vérifiée, une mise en forme dynamique doit se déclencher.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert pâle,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante des couleurs, choisir un Violet,
• Puis cliquer sur le bouton Ok pour valider ces réglages de format dynamique,

Nous sommes de retour sur la première boîte de dialogue. Elle donne un aperçu des réglages de mise en forme qui seront déclenchés à chaque fois que le critère sera vérifié.

• Valider de nouveau par le bouton Ok,

Vous remarquez que l'employé choisi est parfaitement repéré dans chacune de ses affectations. Au passage, nous validons ainsi le bon fonctionnement de l'affectation par roulement. Une même couleur n'est jamais présente deux fois sur une même ligne. De plus, l'employé est bien affecté sur un poste chaque semaine.

Si vous choisissez un autre salarié en B4, les couleurs se déplacent pour le repérer instantanément dans l'intégralité du tableau.

Alertes dynamiques sur les absences
Nous devons parfaire l'application pour alerter le responsable sur les incohérences des affections. Elles se produisent par roulement. Mais elles ne tiennent pas compte des indisponibilités des uns et des autres. Il s'agit donc de bâtir une nouvelle règle de mise en forme conditionnelle. Son critère doit recouper les informations du tableau des affectations avec celles du tableau des indisponibilités. Si pour une semaine donnée, le salarié est marqué comme indisponible, il doit ressortir dans une couleur attirant l'oeil immédiatement. Le responsable aura ainsi tout le loisir de prendre les dispositions qui s'imposent pour combler l'absence.

Pour établir cette correspondance dans le critère, nous devons exploiter les fonctions Index et Equiv. Comme vous le savez, elles permettent d'extraire de l'information d'un tableau, au croisement d'une ligne et d'une colonne. Ces variables de ligne et colonne seront évaluées par la fonction Equiv. Leurs syntaxes sont les suivantes :

=Index(Tableau_de_recherche ; Indice_de_ligne ; Indice_de_colonne)
=Equiv(Valeur_cherchée ; Rangée_de_recherche ; 0)

Pour l'indice de ligne, la fonction Equiv doit chercher le numéro de semaine en cours d'analyse par la mise en forme conditionnelle, dans la colonne de titre du tableau des indisponibilités. Pour l'indice de colonne, la fonction Equiv doit chercher le nom du salarié dans la ligne de titre du tableau des indisponibilités. Il s'agit donc d'une recherche dans une table à deux entrées. Si au croisement de ces deux indices, la fonction Index décèle la présence d'une information, nous en déduisons que l'employé est absent. En conséquence, nous devons déclencher des réglages de mise en forme.

• Sélectionner de nouveau toutes les cellules du tableau des affectations, soit la plage C5:G56,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Cliquer dans la zone de saisie située juste en dessous pour l'activer,
• Taper le symbole = pour débuter la syntaxe de la règle de mise en forme conditionnelle,
• Saisir le nom de la fonction d'extraction suivi d'une parenthèse, soit Index(,
• Cliquer sur l'onglet Indisponibilités en bas de la fenêtre Excel pour afficher sa feuille,
• Sélectionner toutes les cellules du tableau des absences, soit la plage B9:G61,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne à trouver,
• Saisir la fonction donnant la position d'un élément cherché suivie d'une parenthèse, soit Equiv(,
• Cliquer sur l'onglet Affectations en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la première semaine à chercher, soit la cellule B5,
• Enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, ce qui donne : $B5,

Pour une même ligne du tableau des affectations, le repérage doit être réalisé sur le même numéro de semaine situé dans sa colonne qui ne bouge pas. Mais pour chaque ligne suivante, le numéro de semaine à considérer est bien le suivant. Il doit donc suivre le déplacement en ligne.

• Taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,
• Cliquer sur l'onglet Indisponibilités pour revenir sur sa feuille,
• Sélectionner la colonne des semaines, soit la plage de cellules B9:B61,
• Taper un point-virgule suivi du chiffre 0, soit : ;0 pour une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,

A ce stade, la fonction Equiv est susceptible d'avoir retourné le numéro dynamique de ligne, dépendant de la semaine en cours d'analyse. Nous devons croiser cette recherche avec le numéro de colonne dynamique, dépendant du salarié en cours d'analyse.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Saisir de nouveau la fonction donnant la position d'un élément, suivie d'une parenthèse, soit Equiv(,
• Cliquer sur l'onglet Affectations pour revenir sur sa feuille,
• Sélectionner le tout premier salarié, soit la cellule C5,
• Enfoncer trois fois la touche F4 du clavier pour la libérer totalement,

En effet, chaque salarié doit être passé en revue dans la recherche. La cellule doit donc se déplacer en ligne comme en colonne.

• Taper un point-virgule (;) pour passer dans l'argument de la rangée de recherche,
• Cliquer sur l'onglet Indisponibilités pour afficher sa feuille,
• Sélectionner la ligne des salariés, soit la plage de cellules B9:G9,
• Taper un point-virgule suivi du chiffre 0, soit : ;0 pour une correspondance exacte,
• Fermer la parenthèse de la fonction Equiv,

A ce stade, les deux fonctions Equiv sont censées avoir retourné les variables sur l'indice de ligne et l'indice de colonne. La fonction Index peut donc effectuer le croisement des informations et extraire la donnée qui s'y trouve. Mais il s'agit d'un critère à vérifier. Il consiste à savoir si l'information trouvée est vide ou non.

• Fermer la parenthèse de la fonction Index,
• Taper le symbole inférieur suivi du symbole supérieur et de deux guillemets, soit <>'',

Cette inégalité consiste à vérifier que l'extraction n'est pas vide et donc, que le salarié est absent. Dans ce cas, nous devons lui appliquer une mise en valeur sans équivoque.

• Cliquer sur le bouton Format situé en bas de la boîte de dialogue,
• Dans l'onglet Remplissage, choisir un jaune-orangé,
• Dans l'onglet Police, choisir un rouge rubis,
• Valider ces réglages en cliquant sur le bouton Ok,

Comme précédemment, nous sommes de retour sur la boîte de dialogue pour la mise en forme conditionnelle. L'aperçu confirme la mise en valeur qui doit se déclencher dynamiquement si un salarié est repéré comme absent, la semaine de son affectation.

• Valider de nouveau par Ok,

Comme vous le constatez, les couleurs de remplissage se déclenchent instantanément. Vous pouvez vérifier la cohérence de ces alertes avec les absences référencées dans le tableau des indisponibilités. Il n'y a aucune faille. Le responsable en est averti dynamiquement et instantanément. Il peut prendre les dispositions qui s'imposent. Il bénéficie désormais d'un outil lui permettant de répartir les salariés par roulement, tout en identifiant les remplacements à envisager. Si vous cliquez sur le bouton Générer, vous réorganisez les affectations. De fait, les couleurs dynamiques changent de position.