Disponibilités des salariés sur calendrier perpétuel

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


Sujets que vous pourriez aussi aimer :


Disponibilités des salariés

Dans une précédente formation, nous avons conçu un outil permettant au responsable d'archiver les congés des salariés, par simples sélections des dates dans un calendrier annuel. Pour plus d'efficacité, nous souhaitons désormais et de la même façon, dresser la liste du personnel disponible, sur une période définie.



L'objectif est de lui permettre d'organiser au mieux ses équipes, notamment au cours de périodes particulièrement délicates, comme celles des vacances annuelles.

Source et présentation de la problématique
Ces travaux s'inscrivent dans la continuité des précédents. Nous devons donc commencer par réceptionner le classeur offrant la construction automatique des calendriers annuels et permettant d'archiver les congés des salariés.

• Télécharger le classeur presence-salaries-periodes.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

A l'ouverture, la feuille Calendrier est active par défaut. En actionnant la première liste déroulante en BD8, vous pouvez choisir une date. Cette action a pour effet de reconstruire les jours et les semaines dans la représentation de chacun des mois de l'année. En actionnant la seconde liste déroulante en BD11, vous pouvez désigner un salarié. Ce choix a pour effet de surligner toutes les dates de ses absences pour congés ou RTT par exemple. Sachez néanmoins que ces absences sont pour l'instant stockées pour l'année 2020 seulement. Et ces archives sont énumérées dans le tableau de la feuille Conges.

De façon très ergonomique, il est possible d'ajouter de nouvelles périodes d'indisponibilités pour le salarié désigné. Pour cela, il suffit de sélectionner des dates à la souris dans le calendrier annuel. Ensuite, il convient de cliquer sur le bouton Gérer. Il est placé sous les listes déroulantes. Puis, dans le formulaire qui apparaît, il s'agit de définir la nature de l'absence et de cliquer sur le bouton Ajouter. Dès lors, les nouvelles périodes sont archivées et formatées dynamiquement dans le calendrier annuel.

Désormais, pour simplifier l'organisation du chef d'équipe, nous devons dresser un tableau de synthèse. Celui-ci doit rendre compte sur la disponibilité de chacun des salariés pour les dates désignées sur le calendrier annuel. Il saura ainsi sur qui il peut compter pour construire ses équipes, lorsque les périodes sont propices aux départs en vacances par exemple.

• A l'aide de la première liste déroulante en BD8, choisir l'année 2020,
• A l'aide de la seconde liste déroulante en BD11, choisir le salarié Fortune Alain par exemple,

Une mise en forme dynamique se déclenche aussitôt. Très vite, nous comprenons que ce salarié est en congés payés à partir du 15 Juillet jusqu'à la fin du mois. La semaine du 14 Juillet est délicate. Elle est propice aux congés et RTT. Le chef d'équipe aimerait connaître les personnes disponibles du 15 au 17 inclus.

• Sélectionner ces trois dates en cliquant et glissant sur les trois cases,
• Puis, cliquer sur le bouton Gérer à droite de la feuille,

Nous connaissons l'utilité de la liste déroulante ainsi que des boutons Ajouter et Supprimer. Ils permettent de gérer les périodes d'absence. Ils sont l'oeuvre du développement entrepris lors de la précédente formation VBA Excel.

Le bouton Dispo est nouveau quant à lui. Au clic, il doit scruter les dates d'absences des salariés dans la feuille Conges, pour dresser le bilan des présences dans la feuille Disponibilités.



Bien sûr, il apparaîtra judicieux d'établir un tri sur cette synthèse afin de classer les employés par ordre de disponibilités.

• Cliquer sur la croix du formulaire pour fermer le UserForm,
• Réaliser ensuite le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,

Le module3 doit être actif par défaut.

• Si ce n'est pas le cas, dans l'explorateur de projet sur la gauche de l'écran, déployer l'arborescence du dossier Modules,

Les deux premiers hébergent des codes que nous avons exploités lors de la formation VBA précédente.

• Double cliquer alors sur l'élément Module3 pour afficher sa feuille de code,

Comme vous pouvez le voir, ce dernier héberge deux modules. Ils sont nommés respectivement trier_conges et trier_dispo. Leur code a été construit par une macro Excel enregistrée automatiquement. Le premier module permet de trier le tableau des congés sur le nom des salariés. Ainsi regroupés, le traitement par le code VBA à la recherche des dates, pourra être optimisé. Le second est utile pour trier le tableau de synthèse de la feuille Disponibilités. Le tri est organisé croissant sur la fréquence des présences pour la période déterminée. Ce code devra être appelé lorsque nous aurons réussi à consolider toutes les disponibilités des salariés pour la période désignée.

Tous deux attendent un paramètre. Il s'agit de l'indice de la dernière ligne pour chaque tableau. Cet indice est utile pour savoir quelles lignes doivent être considérées dans le tri. C'est l'adaptation que nous avons apportée à ces modules. Les variables respectives sont répétées à trois occasions dans chacun des codes. Nous devrons donc être en mesure d'appeler ces procédures en leur passant l'information sur ces indices à trouver.

Initialiser les variables
Le traitement à entreprendre n'est pas anodin. Il va s'avérer précieux pour les gestionnaires. De nombreuses données sont à manipuler, à scruter et à dépouiller. De fait, un nombre relativement important de variables est à prévoir.

• Dans l'explorateur de projet, déployer l'arborescence du dossier Feuilles,
• Puis, double cliquer sur le formulaire Outils pour l'afficher en conception,
• Sur le formulaire, double cliquer sur le bouton Dispo,

Nous basculons ainsi dans la feuille de code associée au formulaire, plus précisément entre les bornes de la procédure événementielle Disponibilites_Click. Son code se déclenchera donc au clic sur le bouton Dispo.

• Dans les bornes de la procédure, ajouter les déclarations et affectations suivantes :

Dim nom As String: Dim nom_temp As String
Dim chaine_date As String
Dim nb_dates As Byte: Dim nb_abs As Byte
Dim ligne_ext As Integer: Dim ligne_fin As Integer
Dim cellule As Range: Dim ligne_disp As Integer
Dim feuille As Worksheet: Dim feuille_disp As Worksheet
Dim trouve As Boolean

Set feuille = Sheets('conges')
Set feuille_disp = Sheets('Disponibilités')
ligne_fin = reperer - 1
trier_conges ligne_fin
nb_dates = Selection.Count
nom = '': nom_temp = '': nb_abs = 0
ligne_disp = 5 : trouve = False

Les variables nom et nom_temp sont déclarées comme des textes (String). Dans la progression de l'analyse, elles serviront à vérifier que le nom du salarié étudié est bien toujours le même. Le cas échéant, les résultats du précédent devront être consolidés.

La variable chaine_date, typée elle aussi comme un String sera utilisée pour stocker toutes les dates d'absence sur la période désignée, pour chaque salarié. Cette information sera restituée en colonne D de la feuille Disponibilités.

Les variables nb_dates et nb_abs sont typées comme des entiers courts (Byte). La première doit stocker l'information sur le nombre de dates choisies dans la période sélectionnée. La seconde doit compter le nombre de jours d'absence du salarié sur cette période. Une division des deux conduira au taux de présence du salarié. Cette information devra être affichée en colonne C de la feuille Disponibilités.

Les variables ligne_ext et ligne_fin seront utilisées pour exploiter les lignes de départ et d'arrivée du tableau de la base de données des congés. C'est la raison pour laquelle elles sont typées comme des entiers. La variable ligne_disp doit servir à parcourir les lignes de la feuille Disponibilités pour ajouter les nouveaux enregistrements de synthèse, les uns à la suite des autres.

La variable cellule est déclarée comme un objet de type Range. Comme lors du développement précédent, nous l'utiliserons pour parcourir toutes les cellules de la sélection, soit toutes les dates choisies.

Ensuite, nous déclarons les variables feuille et feuille_disp comme des objets feuille. Nous les exploiterons pour désigner les feuilles respectives (Conges et Disponibilités) et ainsi simplifier la syntaxe. Enfin, la variable booléenne Trouve permettra de savoir à partir de quel moment les données peuvent être consolidées et inscrites.

Nous initialisons ensuite les variables objets sur leur feuille respective. Nous réceptionnons le dernier indice de ligne de la feuille Conges, grâce à la fonction reperer de la formation précédente. Nous appelons la procédure de tri sur cette base de données en lui passant cet indice. Les bornes sont ainsi connues. Grâce à la propriété Count de l'objet VBA Selection, nous stockons le nombre de cellules sélectionnées. Puis, nous initialisons les dernières variables dont la variable ligne_disp que nous fixons sur la première ligne utile de la feuille Disponibilités.

Avant d'enclencher l'analyse des dates sur la base de données, nous devons commencer par purger le tableau de la feuille Disponibilités. Les synthèses des précédentes demandes y sont nécessairement stockées au fil de l'utilisation.

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

...
Do While feuille_disp.Cells(ligne_disp, 2).Value <> ''
feuille_disp.Cells(ligne_disp, 2).EntireRow.Delete
Loop
...

Il s'agit d'une boucle désormais classique dans nos développements. Sur la colonne B (2), nous parcourons l'ensemble des lignes de la feuille Disponibilités. Tant qu'une valeur est trouvée (Cells(ligne_disp,2).Value <> ''), nous supprimons la ligne entière. La suppression place automatiquement le pointeur sur la ligne suivante. C'est la raison pour laquelle nous n'avons pas besoin d'incrémenter la variable de boucle. A l'issue, nous recalons la variable ligne_disp.

Parcourir et comparer les dates
Une fois la feuille de réception purgée, nous devons enclencher un traitement récursif destiné à parcourir l'intégralité des dates d'absence archivées. Il s'agit même d'une double boucle puisque chaque date de la feuille Conges doit être comparée avec chacune des dates de la sélection.

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

...
For ligne_ext = 3 To ligne_fin

nom = feuille.Cells(ligne_ext, 2).Value

For Each cellule In Selection


Next cellule

nom_temp = nom

Next ligne_ext
...

La boucle For Each est donc encapsulée dans la boucle For Next. Nous analysons la base de données des absences de la ligne 3 à la dernière recensée (ligne_fin). A chaque passage, nous prélevons le nom du salarié en colonne B (2). Et pour chacune de ces lignes, nous enclenchons une boucle For Each destinée à parcourir toutes les dates de la sélection. Après le For Each et avant de passer à l'enregistrement suivant des congés, nous stockons le nom dans la variable nom_temp. Ainsi, au prochain indice, si les deux variables nom et nom_temp diffèrent, nous saurons que le salarié a changé. Cette information est capitale pour ordonner la consolidation de ses présences dans la feuille Disponibilités.

Le raisonnement que nous sommes en train de poser est inverse. Avant même d'enclencher la boucle For Each sur les dates, nous devons coder les instructions permettant de consolider ces données recueillies. En effet, au moment où nous écrivons ces lignes, nous considérons que nous sommes au premier passage. Mais cette boucle recommence son traitement tant que le dernier enregistrement n'est pas atteint. Donc, pour les passages suivants, si les noms diffèrent, nous devons inscrire les informations recueillies pour le précédent salarié. Ainsi, nous pourrons poursuivre l'analyse des dates pour le suivant.

• Dans la boucle For, juste avant la boucle For Each, ajouter les lignes VBA suivantes :

...
For ligne_ext = 3 To ligne_fin

nom = feuille.Cells(ligne_ext, 2).Value

If (nom <> nom_temp And nom_temp <> '' And chaine_date <> '' And trouve = True) Then
feuille_disp.Cells(ligne_disp, 2).Value = nom_temp
feuille_disp.Cells(ligne_disp, 3).Value = 1 - (nb_abs / nb_dates)
feuille_disp.Cells(ligne_disp, 4).Value = chaine_date
ligne_disp = ligne_disp + 1
chaine_date = ''
nb_abs = 0
nom_temp = nom
trouve = False
End If

For Each cellule In Selection
...

Le testest quadruple. Il consiste à vérifier dans le même temps que les noms diffèrent, que la variable nom_temp est bien chargée d'une valeur, que la chaîne de date a été construite et que la valeur du booléen a été basculée. Ce booléen devra ainsi être affecté à chaque fois qu'une correspondance de date est trouvée.

Dans ces conditions, nous inscrivons les valeurs correspondantes dans les trois colonnes de la feuille Disponibilités. Nous nous positionnons sur la première ligne disponible (ligne_disp), que nous n'oublions pas d'incrémenter ensuite. C'est ainsi que les nouvelles inscriptions se cumuleront les unes sous les autres. La colonne des fréquences de présence (Cells(ligne_disp, 3)) est formatée en pourcentage. La division du nombre d'absences par le nombre de dates sélectionnées donne le taux d'absence. En retranchant ce résultat à 100% (1), nous obtenons le taux de présence.

Nous réinitialisons ensuite les variables chaine_date, nb_abs, nom_temp et trouve. C'est ainsi que nous pouvons poursuivre l'analyse pour le salarié suivant.

Dans la boucle For Each, il convient de comparer chaque date sélectionnée avec la date en cours d'analyse. Si une correspondance est trouvée, la variable booléenne doit premièrement être basculée à True. Elle indiquera ainsi que des absences sont recensées pour le salarié en cours.

• Dans la boucle For Each, ajouter les instructions VBA suivantes :

...
If (feuille.Cells(ligne_ext, 3).Value = cellule.Value) Then
trouve = True
If (nom = nom_temp Or nom_temp = '') Then
chaine_date = chaine_date & CStr(cellule.Value) & ' '
nb_abs = nb_abs + 1
Exit For
Else
chaine_date = CStr(cellule.Value) & ' '
nb_abs = 1
Exit For
End If
End If
...

Lorsque la date concorde et que le salarié est toujours le même (nom = nom_temp), nous concaténons sa date d'absence aux autres précédemment trouvées (chaine_date = chaine_date & CStr(cellule.Value) & ' '). Notez l'emploi de la fonction VBA CStr pour forcer la conversion de l'information de date en texte. Nous incrémentons de même la variable nb_abs pour comptabiliser les absences et pour honorer le futur calcul sur le taux de présence. Aucune autre date de la sélection ne peut être identique, donc nous sortons de la boucle For (Exit For). Ainsi, nous reprenons l'analyse dans la première boucle sur la suite des salariés de la feuille Conges.

Dans le cas où les dates concordent mais que le nom du salarié a changé (Else), nous stockons ces nouvelles informations dans les variables que nous écrasons. Là aussi, nous sortons de la boucle pour des raisons d'optimisation de traitement.

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

Private Sub Disponibilites_Click()
Dim nom As String: Dim nom_temp As String
Dim chaine_date As String
Dim nb_dates As Byte: Dim nb_abs As Byte
Dim ligne_ext As Integer: Dim ligne_fin As Integer
Dim cellule As Range: Dim ligne_disp As Integer
Dim feuille As Worksheet: Dim feuille_disp As Worksheet
Dim trouve As Boolean

Set feuille = Sheets('conges')
Set feuille_disp = Sheets('Disponibilités')
ligne_fin = reperer - 1
trier_conges ligne_fin
nb_dates = Selection.Count
nom = '': nom_temp = '': nb_abs = 0
ligne_disp = 5: trouve = False

Do While feuille_disp.Cells(ligne_disp, 2).Value <> ''
feuille_disp.Cells(ligne_disp, 2).EntireRow.Delete
Loop

For ligne_ext = 3 To ligne_fin

nom = feuille.Cells(ligne_ext, 2).Value

If (nom <> nom_temp And nom_temp <> '' And chaine_date <> '' And trouve = True) Then
feuille_disp.Cells(ligne_disp, 2).Value = nom_temp
feuille_disp.Cells(ligne_disp, 3).Value = 1 - (nb_abs / nb_dates)
feuille_disp.Cells(ligne_disp, 4).Value = chaine_date
ligne_disp = ligne_disp + 1
chaine_date = ''
nb_abs = 0
nom_temp = nom
trouve = False
End If

For Each cellule In Selection

If (feuille.Cells(ligne_ext, 3).Value = cellule.Value) Then
trouve = True
If (nom = nom_temp Or nom_temp = '') Then
chaine_date = chaine_date & CStr(cellule.Value) & ' '
nb_abs = nb_abs + 1
Exit For
Else
chaine_date = CStr(cellule.Value) & ' '
nb_abs = 1
Exit For
End If
End If

Next cellule

nom_temp = nom

Next ligne_ext
End Sub

Injecter les salariés présents
Le petit code que nous avons produit est tout à fait efficace et nous allons bientôt le constater. Mais il ne traite que des employés recensés dans la feuille Conges et pour lesquels des correspondances de dates sur les absences ont été trouvées. Tous les autres sont ignorés. Or, il s'agit précisément des personnes pleinement disponibles pour la période désignée par le responsable d'équipe. Nous proposons donc de créer une procédure parcourant l'ensemble des employés à leur recherche dans le tableau de la feuille Disponibilités, fraîchement construit. Dès que l'un d'eux n'est pas trouvé, il doit être ajouté avec un taux de présence défini à 100%.

• A la suite de la procédure Disponibilites_Click, créer la procédure consolider :

Private Sub consolider()
Dim feuille As Worksheet: Dim feuille_disp As Worksheet
Dim ligneS As Byte: Dim ligneD As Integer
Dim ligneN As Integer: Dim trouve As Boolean

Set feuille = Sheets('Sources')
Set feuille_disp = Sheets('Disponibilités')
ligneS = 3: ligneD = 5: ligneN = 5

Do While feuille_disp.Cells(ligneN, 2).Value <> ''
ligneN = ligneN + 1
Loop

End Sub

Pour l'instant, nous nous contentons d'initialiser les variables et de détecter la première ligne vide de la feuille Disponibilités. C'est à partir de cet indice que nous pourrons procéder à l'inscription des employés pleinement disponibles. Trois variables de ligne sont nécessaires : ligneS pour la feuille Source, ligneD pour la feuille Disponibilités et ligneN, toujours sur la même feuille mais pour repérer la dernière ligne. Sur la feuille Source, l'énumération de tous les salariés commence en effet à l'indice de ligne 3. Sur la feuille Disponibilités, l'inscription des salariés débute à partir de la ligne 5. La boucle While qui suit parcourt l'ensemble des enregistrements jusqu'à trouver la première ligne vide. Il en résulte l'indice (ligneN) à partir duquel nous pouvons procéder à l'inscription.

Ensuite, nous devons parcourir l'ensemble des salariés archivés sur la feuille Source en colonne D (4). Et pour chacun d'entre eux, nous devons vérifier leur présence dans la feuille Disponibilités. Si la correspondance n'est pas établie, nous en déduisons qu'il s'agit d'un salarié pleinement disponible à ajouter à la suite.

• A la suite du code VBA, créer la double boucle suivante :

...
Do While feuille.Cells(ligneS, 4).Value <> ''
trouve = False: ligneD = 5
Do While feuille_disp.Cells(ligneD, 2).Value <> ''
If (feuille.Cells(ligneS, 4).Value = feuille_disp.Cells(ligneD, 2).Value) Then
trouve = True
Exit Do
End If
ligneD = ligneD + 1
Loop

ligneS = ligneS + 1
Loop
...

Nous parcourons donc tous les salariés de la feuille Source, tant qu'un nom est bien trouvé (Do While feuille.Cells(ligneS, 4).Value <> ''). A chaque passage, nous n'oublions pas d'incrémenter la variable de boucle, pour placer le pointeur sur le salarié suivant (ligneS = ligneS + 1).

Pour chaque employé analysé, nous considérons tout d'abord qu'il n'est pas trouvé (trouve = False) et que l'analyse doit débuter à partir du premier enregistrement de la feuille Disponibilités (ligneD = 5). C'est ainsi que nous enclenchons une boucle parcourant toutes les lignes de cette feuille (Do While feuille_disp.Cells(ligneD, 2).Value <> ''), à la recherche de correspondances. A chaque passage non concluant, nous incrémentions bien évidemment la variable de boucle (ligneD). Ainsi, l'analyse se poursuit.

Si l'égalité est avérée, nous basculons l'état de la variable booléenne à True et nous stoppons l'analyse de la feuille Disponibilités (Exit Do). Mais, du fait de l'imbrication des boucles, cette analyse redémarre aussitôt avec le salarié suivant de la feuille Source.

Désormais, si la recherche du salarié dans la feuille Disponibilités s'est soldée par un échec (Trouve valant toujours False), nous devons ajouter les informations de ce dernier, à la suite (ligneN) dans le tableau des disponibilités.

• Après la seconde boucle mais toujours à l'intérieur de la première, ajouter le code suivant :

...
ligneD = ligneD + 1
Loop

If (trouve = False) Then
feuille_disp.Cells(ligneN, 2).Value = feuille.Cells(ligneS, 4).Value
feuille_disp.Cells(ligneN, 3).Value = 1
feuille_disp.Cells(ligneN, 4).Value = 'Disponible'
ligneN = ligneN + 1
End If

ligneS = ligneS + 1
Loop
...

Comme nous le disions, si la variable booléenne n'a pas changé d'état à l'issue de l'analyse du nom du salarié, nous en concluons qu'il n'est pas répertorié dans la feuille Disponibilités. Nous procédons donc à son inscription, à la suite des autres lignes (ligneN). Bien sûr, nous n'oublions pas d'incrémenter cette variable pour que la prochaine insertion soit réalisée en dessous.

Avant d'être affiché, le tableau de synthèse de la feuille Disponibilités doit être trié, décroissant sur la disponibilité. Nous mettrons ainsi en lumière les salariés complètement libres sur la période choisie. Souvenez-vous, cette procédure existe déjà. Elle se nomme trier_dispo. Nous devons lui passer l'indice de la dernière ligne du tableau à trier, pour n'omettre aucune donnée dans la réorganisation. Cet indice est ligneN-1 puisque la dernière incrémentation, non suivie d'une inscription, désigne la ligne vide en dessous du tableau.

• A la fin du code, après la double boucle, ajouter les instructions VBA suivantes :

...
ligneS = ligneS + 1
Loop

trier_dispo (ligneN - 1)

feuille_disp.Select
Outils.Hide
Range('A1').Select

End Sub
...

Après le tri, nous affichons donc la feuille Disponibilités et masquons le formulaire.

Le code de la procédure consolider est terminé. Mais cette dernière doit être appelée à la fin du traitement de la procédure événementielle Disponibilites_Click.

• A la fin de la procédure événementielle, ajouter l'appel suivant :

...
Next cellule

nom_temp = nom

Next ligne_ext

consolider

End Sub

• Enregistrer les modifications et basculer sur la feuille Calendrier du classeur,
• Sélectionner les trois derniers Vendredis du mois de Juillet 2020, soit les 17, 24 et 31,
• Cliquer alors sur le bouton Gérer à droite de la feuille,
• Dans le formulaire qui suit, cliquer sur le bouton Dispo,

Comme vous le constatez, une fois le traitement achevé, l'affichage est donné à la feuille Disponibilités. Les salariés y sont tous énumérés par ordre de disponibilité. Ceux pour lesquels des absences ont été observées, livrent l'ensemble des dates concernées.



Si vous choisissez une autre période sur la feuille Calendrier, après validation du bouton Dispo, la synthèse s'adapte parfaitement. Il s'agit donc d'un outil précieux en termes d'efficacité et d'organisation pour le responsable.

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

Private Sub consolider()
Dim feuille As Worksheet: Dim feuille_disp As Worksheet
Dim ligneS As Byte: Dim ligneD As Integer
Dim ligneN As Integer: Dim trouve As Boolean

Set feuille = Sheets('Sources')
Set feuille_disp = Sheets('Disponibilités')
ligneS = 3: ligneD = 5: ligneN = 5

Do While feuille_disp.Cells(ligneN, 2).Value <> ''
ligneN = ligneN + 1
Loop

Do While feuille.Cells(ligneS, 4).Value <> ''
trouve = False: ligneD = 5
Do While feuille_disp.Cells(ligneD, 2).Value <> ''
If (feuille.Cells(ligneS, 4).Value = feuille_disp.Cells(ligneD, 2).Value) Then
trouve = True
Exit Do
End If
ligneD = ligneD + 1
Loop

If (trouve = False) Then
feuille_disp.Cells(ligneN, 2).Value = feuille.Cells(ligneS, 4).Value
feuille_disp.Cells(ligneN, 3).Value = 1
feuille_disp.Cells(ligneN, 4).Value = 'Disponible'
ligneN = ligneN + 1
End If

ligneS = ligneS + 1
Loop

trier_dispo (ligneN - 1)

feuille_disp.Select
Outils.Hide
Range('A1').Select

End Sub

Indicateurs visuels
Pour renforcer la pertinence de la synthèse livrée et simplifier l'interprétation des disponibilités, nous proposons d'engager des règles de mise en forme conditionnelle. Dans des jeux de couleurs adaptés, elles doivent mettre en évidence les salariés les plus disponibles sur la période demandée.

Mais avant cela, quelques formatages sont nécessaires.

• Sélectionner la colonne C de la feuille Disponibilités par son étiquette,
• Dans la section Nombre du ruban Accueil, cliquer sur le bouton Style de pourcentage,
• Puis, dans la section Alignement, cliquer sur le bouton Aligner à droite,
• Sélectionner alors la colonne D par son étiquette,
• Puis, cliquer de même sur le bouton Aligner à droite,

Les résultats de synthèse sont déjà plus simples à interpréter.

• Sélectionner les quatre colonnes du tableau par les étiquettes, soit de B à D,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Dans la zone de saisie juste en dessous, bâtir la règle suivante :

=ET($C1<>'';$C1=1)

L'analyse porte donc sur les cellules de la colonne C, soit la colonne des disponibilités. Deux conditions sont à satisfaire ensemble. Le contenu de la cellule en cours d'analyse ne doit pas être vide et sa valeur doit être 1, soit 100%. Comme la ligne est libérée (Dollar non présent devant le 1), toutes les disponibilités de la colonne seront passées en revue.

Lorsque la disponibilité est totale donc, nous souhaitons en alerter visuellement le responsable par un remplissage vert.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert assez soutenu,
• Activer l'onglet Police de la boîte de dialogue,
• Choisir un style gras et une couleur gris très clair pour le texte,
• Valider ces réglages de mise en forme en cliquant sur le bouton Ok,

De fait, nous sommes de retour sur la première boîte de dialogue qui rend compte de la situation. Dès qu'un taux de disponibilité à 100% est détecté, la ligne complète du salarié doit se parer de vert.

• Cliquer de nouveau sur le bouton Ok pour revenir sur la feuille Excel,

Instantanément, tous les employés pleinement disponibles pour la période désignée ressortent du lot. Ces indicateurs visuels sont d'une aide précieuse pour le gestionnaire.

• De la même façon, pour les colonnes B à D, créer la règle suivante sur fond bleu :

=ET($C1<> ''; ESTNUM($C1); $C1>=0,7)

Elle doit donc mettre en valeur, tous les taux de disponibilités supérieurs à 70%. A validation, vous remarquez qu'elle prend le pas sur la première règle. Tout est une question de hiérarchie. Nous y reviendrons lorsque toutes les règles seront construites.

• Créer la règle suivante sur fond jaune :

=ET($C1<>''; ESTNUM($C1); $C1>=0,4)

Elle doit repérer toutes les disponibilités supérieures à 40%. Son indicateur de couleur révèle une symbolique plutôt négative, informant instantanément le responsable sur la nature et la proportion.

• Enfin, créer la règle suivante sur fond orange :

=ET($C1<>''; ESTNUM($C1); $C1<0,4)

Une disponibilité de 100% vérifie les trois premiers critères à la fois. C'est pourquoi, dans l'ordre de leur empilement, sa propre règle est neutralisée par les suivantes. Dans l'ordre logique, elle doit intervenir en dernier. Elle doit donc être remontée tout en haut de la hiérarchie. Les colonnes B à D doivent toujours être sélectionnées.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir l'option Gérer les règles,

Nous affichons ainsi le gestionnaire des règles de mise en forme conditionnelle.

• Sélectionner la règle verte puis la remonter en haut de la liste avec le bouton de flèche (Monter),
• Ensuite, placer dans l'ordre la règle bleue, puis jaune et orange,
• Enfin, valider cette organisation hiérarchique des règles en cliquant sur le bouton Ok,

De retour sur la feuille, la mise en valeur dynamique est conforme à nos attentes. Les personnes pleinement disponibles sont prioritairement repérées grâce à leur indicateur positif associé.