Affecter les salariés par roulements en gérant les absences

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


Sujets que vous pourriez aussi aimer :


Equipes de travail et gestion des absences

Dans une formation Excel précédente, nous avons montré comment affecter les équipes de travail par roulements, sur différents postes de production, les salariés les uns après les autres. Une mise en valeur dynamique avait permis de repérer les employés indisponibles dans le tableau des affectations. Cette fonctionnalité précieuse permettait au responsable d'anticiper pour combler l'absence et ne pas casser le rythme de production.



Dans cette formation VBA Excel, nous proposons de recréer le processus d'affectation par roulement, à l'aide du code VBA. Nous constaterons dans un premier temps, la mise en valeur dynamique des salariés indisponibles. Puis nous améliorerons le code pour trouver des solutions de remplacement automatisées, afin de simplifier l'organisation et l'élaboration des plannings pour le responsable.

Source et présentation de la problématique
Des données à manipuler sont nécessaires. Nous proposons de débuter les travaux depuis une source offrant la structure des tableaux et les informations à recouper. Ainsi, nous nous concentrerons sur l'objectif à atteindre.

• Télécharger le classeur roulement-equipes-et-indisponibilites.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 composé de deux feuilles. La feuille active par défaut est la feuille Gestion-ind. Il s'agit de la console d'administration du responsable. Un clic sur le bouton Générer doit lui permettre de réaliser l'affectation des salariés par roulements, sur le poste de travail. Un clic sur le bouton Indisp. doit lui permettre de remplacer automatiquement les salariés indisponibles, sur les dates mises en valeur par alertes dynamiques.

• Cliquer sur l'onglet Indisponibilités en bas de la fenêtre Excel pour activer sa feuille,

Les absences des salariés sont référencées dans cette feuille. C'est une table à deux entrées située sentre les colonnes G et L qui repère les indisponibilités, au croisement d'une semaine et d'un salarié.

Le tableau placé entre les colonnes B et E énumère quant à lui les salariés. Ils sont accompagnés de drôles de chiffres. En effet, nous souhaitons certes réaliser une répartition par roulement, mais en débutant l'affectation depuis un salarié choisi aléatoirement. C'est pourquoi une macro automatique a été préconçue. Sur la base de valeurs aléatoires générées grâce à la fonction Excel Alea, elle organise un tri des employés au hasard.

• Basculer dans l'éditeur de code VBA Excel à l'aide du raccourci ALT + F11,

Le bouton Visual Basic du ruban Développeur permet d'atteindre le même résultat. La formation pour débuter la programmation en VBA Excel rappelle comment l'afficher.

• Dans l'explorateur de projet, déployer l'affichage du dossier Modules,
• Puis, double cliquer sur l'élément Module1 pour afficher sa feuille de code,

Comme vous le constatez, cette macro existe déjà. Elle se nomme trier_aleat. Et elle est appelée par la procédure Générer. Cette procédure est elle-même liée au bouton Générer. De fait, un clic sur ce dernier doit ordonner la réorganisation aléatoire des salariés dans leur tableau.

• Revenir sur le classeur (ALT + F11)
• Cliquer sur l'onglet Gestion-ind en bas de la fenêtre Excel pour afficher sa feuille,
• Puis, cliquer sur le bouton Générer,
• Afficher ensuite la feuille Indisponibilités,

Comme vous le remarquez, les salariés sont en effet réorganisés. Sur cette base de départ, nous devons procéder à l'affectation par roulement, sur le poste de travail de la feuille Gestion-ind, en commençant par la première semaine. Il s'agit donc de poursuivre le développement de la procédure generer.

Affecter les salariés par roulements
En colonne 3 (C) de la feuille Gestion-ind, pour les lignes 5 à 56, l'objectif consiste à affecter les salariés sur le poste de travail, les uns à la suite des autres, en partant du premier d'entre eux, généré aléatoirement. Nous devons donc développer un code parcourant l'ensemble de ces lignes et dans le même temps, parcourant les lignes du tableau des salariés. Dès que le dernier salarié est atteint, l'énumération doit reprendre depuis le premier.

Comme toujours, nous devons commencer par déclarer les variables nécessaires au traitement, notamment pour parcourir les cellules.

• Basculer dans l'éditeur de code grâce au raccourci ALT + F11,
• Dans les bornes de la procédure generer, ajouter les instructions suivantes :

Dim ligne As Byte: Dim compteur As Byte

trier_aleat
compteur = 1

Vous devez veiller à conserver l'appel à la procédure trier_aleat. La variable ligne doit être utilisée pour parcourir l'ensemble des lignes du tableau de la feuille Gestion-ind. La variable compteur doit permettre de pointer sur les lignes du tableau des salariés, en fonction de la progression des lignes dans le premier tableau. Toutes deux sont déclarées comme des entiers courts (Byte) dont la capacité maximale est de 255. Aucun des deux tableaux ne propose 255 lignes. Après l'appel de la procédure de tri, nous initialisons la variable compteur à 1. Cette valeur permettra de pointer sur le premier salarié à affecter.

Comme nous connaissons le point de départ et le point d'arrivée, la boucle For est dédiée pour entreprendre le traitement récursif.

• A la suite du code, ajouter les instructions suivantes :

For ligne = 5 To 56

Cells(ligne, 3).Value = Sheets('Indisponibilités').Cells(compteur + 2, 4).Value

compteur = compteur + 1
If (compteur > 5) Then compteur = 1
Next ligne

Nous parcourons l'ensemble des lignes du tableau d'affectation, de la 5^ème à la 56^ème. Pour chacune d'entre elles, l'affectation par roulement doit être effectuée. Il s'agit d'inscrire les salariés les uns à la suite des autres, en partant du premier généré aléatoirement. Lorsque le dernier est atteint, l'énumération doit reprendre à partir du premier. Et c'est là qu'entre en jeu la variable compteur.

Nous affectons chaque ligne du tableau (Cells(ligne, 3).Value), du salarié correspondant à la progression, en pointant bien sur la colonne D (indice 4) de la feuille Indisponibilités (Sheets('Indisponibilités').Cells(compteur + 2, 4).Value). C'est l'objet VBA Sheets qui permet de désigner une feuille par son nom. Nous ajoutons deux unités à la variable compteur puisque le tableau des salariés débute à la ligne 3 (1 + 2).

Comme il s'agit d'une boucle, toutes les lignes du tableau des affectations sont passées en revue (For ligne = 5 To 56). Comme à chaque passage, la variable compteur est incrémentée (compteur = compteur + 1), le salarié suivant est affecté. Comme il n'y a que 5 salariés, à chaque fois que la valeur seuille est atteinte, elle est réinitialisée à sa valeur de départ (If(compteur > 5) Then compteur = 1). Ainsi l'énumération reprend bien à partir du premier salarié.

Le code est très simple. Pourtant, comme vous allez le constater, il fait son oeuvre.

• Enregistrer les modifications (CTRL + S),
• Basculer sur le classeur (ALT + F11),
• Afficher la feuille Gestion-ind,
• Puis, cliquer sur le bouton Générer,

Comme vous le constatez, et comme l'illustre la capture ci-dessus, en partant d'un salarié choisi aléatoirement, l'affectation par roulement est parfaitement réalisée. Chaque employé intervient une fois sur cinq, le temps que le reste de l'équipe ait fini de tourner. De plus, ces affectations sont complètement automatisées grâce au code Visual Basic. Il s'agit donc d'un outil précieux pour le responsable, afin d'organiser ses équipes de travail.

Le code complet de la procédure generer est le suivant :

Sub generer()
Dim ligne As Byte: Dim compteur As Byte

trier_aleat
compteur = 1

For ligne = 5 To 56

Cells(ligne, 3).Value = Sheets('Indisponibilités').Cells(compteur + 2, 4).Value

compteur = compteur + 1
If (compteur > 5) Then compteur = 1
Next ligne

End Sub

Identifier les absences
Il est indispensable de tenir compte des indisponibilités des uns et des autres, ce que ne fait pas notre code jusqu'à présent. Mais en le décomposant, la compréhension s'en trouvera simplifiée. Nous proposons de monter une mise en forme conditionnelle capable d'alerter le responsable sur les affectations qui ne peuvent avoir lieu, à cause des absences repérées dans le tableau de la feuille Indisponibilités.

Comme vous le savez, une mise en forme conditionnelle consiste à déclencher des réglages de formats particuliers, lorsqu'un critère est satisfait. Ce critère consiste à vérifier, pour la semaine désignée et le salarié impliqué, s'il est marqué comme indisponible dans le tableau de référence.

Pour établir cette correspondance, nous devons exploiter les fonctions Index et Equiv. Elles sont capables d'extraire une donnée d'un tableau, située au croisement d'une ligne et d'une colonne. 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 trouver la 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 trouver la colonne, la fonction Equiv doit chercher le nom du salarié dans la ligne de titre du même tableau. Si au croisement de ces deux indices, la fonction Index récupère une information, cela signifie que l'employé est absent. De fait, nous devons marquer sa cellule dans le tableau des affectations, pour en alerter le responsable.

• Sélectionner toutes les cellules du tableau des affectations, soit la plage C5:C56,
• 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 G3:L55,
• Taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne à trouver,
• Saisir la fonction pour la ligne de la semaine cherchée suivie d'une parenthèse, soit Equiv(,
• Cliquer sur l'onglet Gestion-ind 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 chaque ligne du tableau des affectations, le repérage doit s'effectuer uniquement sur le numéro de semaine situé en colonne B qui ne doit donc pas se déplacer en colonne C. Mais pour chaque ligne, 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 G3:G55,
• 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 a retourné le numéro dynamique de ligne, dépendant de la semaine en cours d'analyse. Nous devons croiser cette information 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 pour la fonction Index,
• Saisir la fonction pour la colonne du salarié cherché, suivie d'une parenthèse, soit Equiv(,
• Cliquer sur l'onglet Gestion-ind 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 pouvoir se déplacer au fur et à mesure de l'analyse.

• 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 G3:L3,
• 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 ont retourné les indices de ligne et de colonne. La fonction Index peut opérer le croisement de ces données pour extraire l'information qui s'y trouve. Mais il s'agit d'un critère à vérifier. Il consiste simplement à savoir si l'information à cet emplacement 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 vous le constatez, des salariés sont instantanément repérés. Le responsable en est automatiquement alerté.

Si vous consultez le tableau des absences, vous constatez que les salariés sont effectivement mis en valeur lorsqu'ils sont affectés une semaine d'indisponibilité. La formule que nous avons bâtie pour ce critère de mise en forme conditionnelle, est la suivante :

=Index(Indisponibilités!$G$3:$L$55; Equiv($B5; Indisponibilités!$G$3:$G$55; 0); Equiv(C5; Indisponibilités!$G$3:$L$3; 0))<>''

Nous l'avions exploitée quasiment à l'identique dans la formation Excel précédente pour identifier les absents dans une affectation sur 5 postes de travail.



Remplacer automatiquement les employés absents
Il s'agit de faire tourner les équipes en fonction des contraintes identifiées. Et pour réorganiser les affectations par roulements en tenant compte des indisponibilités, nous devons adapter le code de la procédure précédente, en exploitant une fonction à créer. Cette dernière doit être questionnée à chaque passage dans la boucle. Elle doit informer si le salarié est absent, selon la semaine en cours d'étude.

• Basculer dans l'éditeur de code Visual Basic Excel (ALT + F11),
• Sous la procédure generer, créer la fonction cherche_colonne, comme suit :

Function cherche_colonne(semaine As String,nom As String) As Boolean

Dim colonne As Byte: Dim ligne As Byte

cherche_colonne = False

End Function

Pour effectuer son analyse, cette fonction a besoin de connaître la semaine et le salarié. C'est pourquoi nous déclarons ces deux variables à lui passer en argument (semaine As String, nom As String). C'est au croisement de ces deux données, dans la table à deux entrées, que l'information doit être testée. La mission d'une fonction est de retourner une valeur. Nous la déclarons comme un booléen (As Boolean). Ainsi, en conclusion de son analyse, pour la semaine demandée, elle indiquera si le salarié est absent (True) ou disponible (False).

Nous déclarons ensuite les deux variables nécessaires pour parcourir les lignes et les colonnes dans la table à deux entrées. Puis, nous initialisons la variable cherche_colonne à False. Nous partons du principe qu'un employé est disponible, jusqu'à preuve du contraire. Si la preuve est apportée, sa valeur doit être basculée à True. Souvenez-vous, en VBA, une fonction retourne la donnée d'un traitement par son propre nom. C'est la raison pour laquelle nous l'affectons elle.

Cette fonction doit analyser toutes les cellules du tableau des indisponibilités, à la recherche de l'information située au croisement de la semaine et du salarié. En d'autres termes, elle doit parcourir toutes les lignes (4 à 55). Et pour chacune d'entre elles, elle doit parcourir toutes les colonnes (H à L, soit 8 à 12). Nous devons donc réaliser une imbrication de boucles For.

• A la suite du code de la fonction, réaliser l'imbrication suivante :

For ligne = 4 To 55
For colonne = 8 To 12

Next colonne
Next ligne

Pour chaque cellule ainsi passée en revue, nous devons vérifier si la semaine et le salarié correspondent. Si c'est le cas et que le contenu au croisement n'est pas vide, nous devons basculer la valeur de retour à True. Nous informerons donc que le salarié est absent. En conséquence, l'affectation devra être changée.

• A l'intérieur de la double boucle, ajouter les instructions suivantes :

If (Sheets('Indisponibilités').Cells(ligne, colonne).Value <> '') Then
If (Sheets('Indisponibilités').Cells(ligne, 7).Value = semaine And Sheets('Indisponibilités').Cells(3, colonne).Value = nom) Then
cherche_colonne = True
Exit For
End If
End If

Nous cherchons tout d'abord à savoir si la cellule en cours d'analyse n'est pas vide (Sheets('Indisponibilités').Cells(ligne, colonne).Value <> '')). Dans le cas contraire en effet, le salarié étant disponible, le traitement n'a pas lieu d'être. Si cette cellule propose une information, nous cherchons à vérifier la correspondance de semaine et de salarié. Nous recoupons donc les deux critères (Sheets('Indisponibilités').Cells(ligne, 7).Value = semaine And Sheets('Indisponibilités').Cells(3,colonne).Value = nom). La semaine passée en argument est comparée à la valeur située en première colonne du tableau (7), pour la ligne en cours (ligne). Le nom du salarié passé en argument est comparé à la valeur située en première ligne du tableau (3), pour la colonne en cours (colonne). Si les deux égalités sont vérifiées, sachant que la cellule au croisement n'est pas vide, nous basculons la valeur de retour à True (cherche_colonne = True). De fait, nous pouvons stopper le traitement en sortant de la deuxième boucle (Exit For). Pour un code optimisé, il s'agit aussi de sortir de la première boucle.

• Pour ce faire, entre les deux bornes Next, ajouter l'instruction suivante :

...
Next colonne
If (cherche_colonne = True) Then Exit For
Next ligne
...

Cette fonction doit donc être questionnée dans une boucle de traitement passant en revue toutes les lignes du tableau des affectations. Donc nous pouvons récupérer le code de la procédure generer. Mais il s'agira bien sûr de l'adapter.

• Au-dessus de la fonction cherche_colonne, créer la procédure indisp, comme suit :

Sub indisp()

End Sub

• Dans les bornes de cette routine, coller le code de la procédure generer, sans l'appel de la procédure trier_aleat, ce qui donne :

Dim ligne As Byte: Dim compteur As Byte

compteur = 1

For ligne = 5 To 56

Cells(ligne, 3).Value = Sheets('Indisponibilités').Cells(compteur + 2, 4).Value

compteur = compteur + 1
If (compteur > 5) Then compteur = 1
Next ligne

Ainsi, nous pourrons visualiser sur l'affectation par roulement, la disparition des personnes indisponibles, remplacées automatiquement par le code VBA Excel. C'est précisément la partie que nous devons ajouter dans la boucle. Elle doit questionner la fonction cherche_colonne avant l'affectation de la cellule en cours. Si la fonction renvoie False, l'affectation peut avoir lieu directement. Dans le cas contraire, la disponibilité du salarié suivant doit être vérifiée, toujours grâce à la fonction cherche_colonne. Tant qu'un employé disponible n'est pas trouvé, le test doit se poursuivre. Nous devons donc inclure cet appel dans un traitement récursif capable de vérifier une condition à la volée. Il s'agit en l'occurrence d'une boucle Do While.

• Dans la boucle For, avant l'affectation de la cellule, ajouter la boucle suivante :

Do While (cherche_colonne(Cells(ligne, 2).Value, Sheets('Indisponibilités').Cells(compteur + 2, 4).Value) = True)
compteur = compteur + 1
If (compteur > 5) Then
compteur = 1
Exit Do
End If
Loop

La boucle Do While teste si le salarié correspondant dans la progression est disponible. Pour cela, elle vérifie la valeur retournée par la fonction cherche_colonne que nous avons créée. Les éléments nécessaires à l'analyse lui sont bien sûr passés en paramètres. Il s'agit de la semaine en cours (Cells(ligne, 2).Value) et du nom du salarié, normalement affecté par roulement (Cells(compteur + 2, 4).Value)). Tant que la valeur retournée vaut True, elle incrémente la variable compteur. Ainsi, le test est réalisé sur l'employé suivant, jusqu'à ce que le premier salarié disponible soit trouvé. Dans ce cas, la boucle Do While arrête son traitement. La variable compteur est mémorisée en l'état. Et dans l'affectation qui suit la boucle, elle est exploitée pour remplacer le salarié indisponible par celui sur lequel elle pointe.

Une sécurité est nécessaire. Dans certains cas particuliers, aucune solution ne peut être trouvée. C'est ainsi si tous les employés sont absents la même semaine. Pour ne pas risquer de tourner dans une boucle infinie, lorsque le dernier est atteint (If (compteur > 5) Then), nous réinitialisons la variable compteur. Et surtout, nous mettons fin à l'exécution de la boucle (Exit Do). Dans ce contexte particulier, c'est donc le premier salarié généréalé atoirement qui sera malgré tout affecté. Le responsable devra prendre les dispositions qui s'imposent.

• Enregistrer les modifications (CTRL + S),
• Basculer sur le classeur Excel (ALT + F11) et afficher la feuille Gestion-ind,
• Cliquer avec le bouton droit de la souris sur le bouton Indisp.,
• Dans le menu contextuel, choisir Affecter une macro,
• Dans la liste des macros, sélectionner la procédure indisp et valider par Ok,
• Puis, cliquer tout d'abord sur le bouton Générer pour produire l'affectation par roulements,

Sans surprise, l'affectation par roulements s'effectue naturellement. De fait, le format dynamique se déclenche pour repérer les absents et alerter le responsable.

• Cliquer alors sur le bouton Indisp.,

Souvenez-vous, la procédure déclenchée ne réalise pas le tri aléatoire sur le tableau des salariés. De fait, elle travaille strictement sur les mêmes enchaînements que ceux produits par la procédure generer. Et vous constatez que toutes les alertes disparaissent. Grâce aux tests de la fonction cherche_colonne, notre procédure VBA trouve des solutions de remplacements automatisés pour pallier les absences. En un seul clic désormais, le responsable est capable d'organiser ses équipes de travail par roulements, tout en gérant les indisponibilités des uns et des autres.

Le code complet attaché au bouton Indisp. est le suivant :

Sub indisp()
Dim ligne As Byte: Dim compteur As Byte

compteur = 1

For ligne = 5 To 56

Do While (cherche_colonne(Cells(ligne, 2).Value, Sheets('Indisponibilités').Cells(compteur + 2, 4).Value) = True)
compteur = compteur + 1
If (compteur > 5) Then
compteur = 1
Exit Do
End If
Loop

Cells(ligne, 3).Value = Sheets('Indisponibilités').Cells(compteur + 2, 4).Value

compteur = compteur + 1
If (compteur > 5) Then compteur = 1
Next ligne

End Sub

Function cherche_colonne(semaine As String, nom As String) As Boolean

Dim colonne As Byte: Dim ligne As Byte
cherche_colonne = False

For ligne = 4 To 55
For colonne = 8 To 12
If (Sheets('Indisponibilités').Cells(ligne, colonne).Value <> '') Then
If (Sheets('Indisponibilités').Cells(ligne, 7).Value = semaine And Sheets('Indisponibilités').Cells(3, colonne).Value = nom) Then
cherche_colonne = True
Exit For
End If
End If
Next colonne
If (cherche_colonne = True) Then Exit For
Next ligne

End Function