Liste des présences au clic sur une date

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


Sujets que vous pourriez aussi aimer :


Lister les présences au clic de la souris

Dans un calendrier énumérant les jours du mois et listant l'ensemble des salariés, nous souhaitons connaître la liste des personnes disponibles au clic sur l'une des dates.



Dans l'exemple illustré par la capture ci-dessus, l'utilisateur clique sur l'un des jours du mois, plus précisément ici, il s'agit du 15. Instantanément, l'extraction de toutes les personnes disponibles à cette date s'effectue dans une liste, sur la droite du calendrier. Et ce sont des formules, plus précisément matricielles, qui réalisent ce travail.

Source et présentation du concept
L'idée n'est pas de construire un calendrier mais de l'exploiter pour parvenir à nos fins. C'est pourquoi, nous suggérons de réceptionner une trame existante.

• Télécharger le classeur identifier-presences-salaries.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é,

Nous débouchons sur un calendrier déjà construit. Il représente les 30 jours du mois de Juin 2020. Vous pouvez le constater en cliquant sur l'un des jours en ligne 4.



En consultant la barre de formule, vous notez que la date complète est considérée. C'est un format personnalisé qui permet de l'abréger à l'affichage.

Les personnes sont énumérées en colonne B à partir de la ligne 5 jusqu'en ligne 60. Elles sont donc 56 au total. Dans le calendrier, les présences respectives sont identifiées par une croix (la lettre x) sur fond vert. Toutes les absences sont repérées par des cases vides sur fond jaune clair. Les jours de Week-End sont quant à eux légèrement hachurés.

• Cliquer sur l'une des cases à l'intérieur du calendrier, par exemple D6,
• 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,

La boîte de dialogue du gestionnaire de règles apparaît.



Et comme vous pouvez le voir, ce sont trois mises en forme conditionnelles dédiées qui régissent ces apparences. Ces règles sont les suivantes :

Pour les absences :
=ET(C5=''; JOURSEM(C$4)<>1; JOURSEM(C$4)<>7)

Nous vérifions trois conditions. La case doit être vide et il ne doit pas s'agir d'un jour de Week-End, soit ni d'un samedi ni d'un Dimanche. Pour ce test, nous exploitons la fonction Excel Joursem.

Pour les présences :
=C5='X'

Nous vérifions simplement que la case porte une croix.

Pour les Week-End :
=OU(JOURSEM(C$4)=1; JOURSEM(C$4)=7)

Nous cherchons à savoir si la case de la colonne correspond soit à un Samedi, soit à un Dimanche. C'est la raison pour laquelle nous exploitons de nouveau la fonction Joursem dans la fonction Ou, pour permettre l'énumération non exclusive.

• Cliquer sur le bouton Fermer de la boîte de dialogue pour revenir sur la feuille Excel,

Deux remarques sont encore nécessaires pour terminer la présentation. Les données de la colonne B possèdent l'intitulé nom. Nous l'exploiterons dans la formule matricielle d'extraction afin de désigner facilement la matrice des personnes énumérées. Cette extraction doit intervenir en colonne AH à partir de la ligne 6. Vous notez la présence d'une case juste au-dessus, plus précisément en cellule AH4. Elle est destinée à recevoir l'information sur la date choisie, au clic de l'utilisateur dans le calendrier. Cette information est cruciale pour procéder à l'extraction en pointant sur la bonne rangée du calendrier à analyser.

Déclencher le calcul au clic
Par défaut, Excel enclenche le recalcul de toutes les formules de la feuille au changement de valeur dans l'une de ses cellules. Mais cette action n'intervient pas au clic de la souris qui se traduit par un changement de sélection. Pour cela, nous avons besoin d'un déclencheur. Et c'est un tout petit bout de code VBA qui permet l'initialisation de l'événement. Il s'agira de son unique intervention. Et vous allez le voir, son intégration est triviale.

• Réaliser le raccourci clavier ALT + F11,

Nous basculons ainsi dans l'éditeur de code Visual Basic Excel.

• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1 (Presences),
• Déployer ensuite la première liste déroulante au-dessus de la feuille de code,
• Dans la liste, choisir l'objet Worksheet,

Cet objet VBA désigne une feuille et plus précisément celle sur laquelle nous sommes en train de travailler. Et cette action a eu pour effet de créer la procédure événementielle Worksheet_SelectionChange. Son code se déclenchera à chaque détection de changement de sélection.

• Entre les bornes de cette procédure, ajouter l'instruction VBA suivante :

Application.Calculate



La méthode Calculate de l'objet Application ordonne le recalcul des formules de la feuille. Ainsi encapsulée dans cette procédure, ce recalcul interviendra bien au clic de la souris notamment.

• Enregistrer les modifications (CTRL + S) et revenir sur la feuille (ALT + F11),

Coordonnées de la cellule cliquée
L'idée est de pouvoir récupérer la date de la colonne cliquée par l'utilisateur en cellule AH4. Nous annonçons ainsi la synthèse souhaitée pour l'extraction. La fonction Excel Cellule permet de renseigner sur de nombreuses propriétés de la cellule ciblée. En lui passant l'argument 'colonne', nous obtenons l'indice de la rangée souhaitée puisque désormais le recalcul est opéré au clic. La ligne est connue. Il s'agit de la quatrième.

• En cellule AH4, inscrire et valider la formule suivante :

=INDIRECT(ADRESSE(4; CELLULE('colonne')))

La fonction Excel Adresse permet de récupérer les coordonnées d'une cellule en fonction de sa ligne en premier paramètre et de sa colonne en second argument. Mais nous ne souhaitons pas exploiter les coordonnées de cette cellule. Nous souhaitons afficher son contenu. C'est pourquoi nous l'imbriquons dans la fonction d'interprétation Indirect.

A validation de la formule, le résultat obtenu n'est pas cohérent. Mais cela est tout à fait logique. Le calcul s'est regénéré sur lui-même. Et cette cellule ne porte aucune date.

• Cliquer sur une cellule à l'intérieur du calendrier,

Aussitôt la date est interprétée et affichée en cellule AH4. Rappelez-vous, c'est bien la date complète qui est affichée en ligne 4 pour chaque colonne du planning. Mais son format d'affichage l'abrège.

Détecter la plage d'extraction
L'extraction des personnes présentes doit se faire en fonction des croix repérées dans la colonne cliquée. Donc la formule matricielle doit être en mesure de déplacer son analyse dynamiquement. Pour cela, nous devons exploiter la fonction Excel Decaler. Et une fois n'est pas coutume, nous allons renseigner son troisième argument. Celui-ci permet de réaliser un décalage par rapport à la cellule de départ. Ce décalage doit être fait en fonction de la date réceptionnée au clic pour pointer sur la bonne colonne à analyser. Nous devons créer une plage nommée dynamique.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue qui suit, cliquer sur le bouton Nouveau,
• Dans la zone Nom, saisir l'intitulé Cible,
• Dans la zone Fait référence à, construire la formule suivante :

=DECALER(Presences!$C$5; ; Presences!$AH$4-Presences!$C$4; NBVAL(nom))

En premier argument de la fonction Decaler, nous renseignons le point de départ. Il s'agit de la case des absences ou présences pour le premier jour du mois. Nous ignorons le second paramètre. Nous n'avons en effet pas de décalage en ligne à opérer par rapport à ce point de référence. En revanche, nous renseignons le troisième paramètre pour réajuster le pointage en colonne : Presences!$AH$4 - Presences!$C$4. En réalisant la différence entre la date reconstruite au clic et la date du premier jour du mois, nous définissons le décalage à opérer en colonne pour pointer sur la bonne rangée à analyser. Celle-ci est constituée d'un certain nombre de lignes. Ce nombre dépend de la quantité de personnes référencées. C'est pourquoi nous exploitons la fonction NbVal sur la colonne des noms en quatrième argument de la fonction Decaler : NBVAL(nom). Nous définissons ainsi la hauteur dynamique de la plage à analyser au clic de la souris.

• Cliquer sur le bouton Ok pour valider la syntaxe de cette plage dynamique,
• Puis, cliquer sur le bouton Fermer du gestionnaire de noms pour revenir sur la feuille Excel,

Cette plage obéit désormais au clic de la souris pour désigner la colonne sur laquelle le critère doit être posé.

Dresser la liste des présences au clic
Pour extraire et regrouper les données correspondant à un critère, nous connaissons bien la technique désormais. Nous devons exploiter les fonctions Index, Equiv, Petite.Valeur et Si. Equiv doit se charger de repérer les positions des croix. Index pourra alors réaliser l'extraction des noms sur ces lignes. La présence de ces croix peut être testée par la fonction conditionnelle Si. Enfin, comme toutes les lignes ne seront pas considérées, c'est la fonction Petite.Valeur qui permettra la réunion, sans trous, des noms concordants.

• Sélectionner les cellules d'extraction pour les noms, soit la plage de cellules AH6:AH60,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

En cas de recherche infructueuse, nous souhaitons en effet neutraliser les erreurs retournées par les fonctions d'extraction.

• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la colonne des personnes par son nom de plage, soit : nom,
• Taper un point-virgule (;) pour passer dans l'argument des indices de ligne à repérer,
• Saisir la fonction des petites valeurs suivie d'une parenthèse, soit : Petite.Valeur(,

C'est elle en effet qui doit permettre de regrouper les concordances repérées. Ces positions doivent être décelées par la fonction Equiv sous condition. Cette condition consiste à déceler la présence des croix, synonymes de présence.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner la plage dynamique de la colonne cliquée par son nom, soit : Cible,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Saisir la lettre x entre guillemets, soit : 'x',
• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction trouvant les positions en ligne, suivie d'une parenthèse, soit : Equiv(,

En effet, ce repérage ne doit être entrepris que dans la mesure où la présence de la croix a été observée.

• En guise de valeur cherchée, désigner la colonne des noms, soit : nom,
• Taper un point-virgule (;) pour passer dans l'argument suivant de la fonction Equiv,
• En guise de tableau de recherche, désigner de nouveau la colonne des noms, soit : nom,

Dans ce raisonnement matriciel, chaque nom sera trouvé dans sa propre colonne. Mais la position ne sera conservée que dans la mesure où le critère précédent sur la présence de la croix est satisfait.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Si,

Effectivement, il n'est pas utile de renseigner la branche Sinon de la fonction Si. Lorsque la condition n'est pas validée, nous ne souhaitons pas repérer les positions. De plus, la fonction SiErreur veille au grain pour neutraliser tous les retours d'erreur.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Petite.Valeur,

Nous ne pouvons nous contenter de spécifier un unique rang. Nous devons désigner tous ceux qui pourraient concorder avec les positions repérées. Nous devons donc construire une matrice débutant de la première ligne pour le rang 1 et s'étendant jusqu'à la dernière ligne de la base de données pour le dernier rang. Ce contexte correspondrait au cas de figure où toutes les personnes seraient présentes.

• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,

En guise de cellule, c'est elle qui doit recevoir la matrice pour retourner tous les indices correspondants. Cette matrice est virtuelle. Elle doit donc être interprétée.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le point de départ de la matrice entre guillemets, comme suit : '1:',

Nous démarrons bien de la première ligne pour le premier rang. Les deux points annoncent la borne inférieure à suivre. Elle est dynamique. Elle dépend de la hauteur du tableau. Elle doit donc être calculée et concaténée.

• Ajouter le caractère de concaténation, soit : &,
• Inscrire la fonction comptant les lignes d'un tableau, suivie d'une parenthèse, soit : Lignes(,

Attention, contrairement à son homologue, cette fonction s'orthographie au pluriel.

• Désigner la colonne des personnes par son intitulé, soit : nom,
• Fermer la parenthèse de la fonction Lignes,
• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la fonction Petite.Valeur,
• Fermer la parenthèse de la fonction Index,

Nous l'évoquons à chaque occasion, il n'est en effet pas nécessaire de renseigner le troisième argument de cette fonction Index. Il concerne l'indice de colonne pour réaliser l'extraction au croisement de la ligne. Mais comme nous avons spécifié une matrice d'une seule rangée en premier paramètre, la fonction Index pointera directement dessus.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour ignorer les anomalies en cas de non correspondance,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

La logique se réplique sur la hauteur de la plage présélectionnée. Mais à ce stade les résultats retournés sont tous nuls. La raison est simple. Notre formule faire référence à la colonne de la cellule cliquée ou modifiée. En l'état, la rangée désignée est celle du calcul. Aucune date n'étant spécifiée, elle ne peut opérer.

• Cliquer dans l'une des colonnes du calendrier, par exemple pour le jour 15,

Aussitôt, l'extraction s'actualise et dresse la liste des personnes présentes pour le jour cliqué. Et si vous consultez la colonne en question, vous confirmez que les résultats sont parfaitement cohérents. Nous avons donc conçu une petite application tout à fait ergonomique en termes d'organisation des plannings.