Repérer automatiquement des données dans un tableau dense

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


Sujets que vous pourriez aussi aimer :


Repérer automatiquement les données dans les tableaux Excel

Dans cette formation Excel, nous proposons d'exploiter la mise en forme conditionnelle à des fins utiles, sur la base des travaux précédents. Nous avions réussi à distribuer automatiquement, tous les salariés d'une entreprise, sur les différentes lignes de production. La génération aléatoire de nombres nous avait permis de définir des affectations au hasard. Et ce sont des calculs d'extraction qui avaient affecté chacun des salariés. Les équipes sont reconstruites différemment à chaque demande. Le salarié est affecté sur un nouveau poste, pour un nouveau travail. En imaginant des données plus denses, il peut devenir intéressant de repérer automatiquement l'affectation de chacun sur demande, par le biais d'une liste déroulante. C'est ce qu'illustre la capture ci-dessous de l'application finalisée.



Au choix dans la liste, une mise en forme conditionnelle, bâtie sur des critères dynamiques, se déclenche et met en évidence instantanément les informations demandées. Il s'agit donc d'une amélioration intéressante et relativement simple pour gagner du temps sur des outils d'entreprise.

Source et présentation de la problématique
Dans un premier temps, nous proposons de récupérer les travaux Excel précédents. Cette formation nous avait permis de ventiler automatiquement les employés sur les différents postes de travail.

• Télécharger le classeur reperer-affectations-postes.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 constitué de deux feuilles. Les travaux doivent être réalisés sur la première des deux, la feuille Répartition_postes. La feuille salaries_tries sert seulement de source de données pour la liste déroulante située en cellule I14. Les données des salariés y ont été copiées pour pouvoir réaliser un tri croissant. Ce tri est restitué dans la liste déroulante ce qui simplifie le choix.

• Cliquer sur le bouton Répartir placé en haut de la feuille Répartition_postes,

Comme vous le remarquez, un traitement s'enclenche. Aucun code VBA n'a été écrit à la main. Seule une macro automatique réalise des actions. Elles consistent simplement à copier des cellules pour restituer les valeurs, détachées des formules. A la fin du traitement, les salariés sont répartis équitablement sur les 5 postes de l'entreprise. Les équipes et les affectations changent à chaque fois que l'ordre est donné.

Ces salariés sont tous énumérés dans le tableau situé entre les colonnes B et G. La répartition en équipe est matérialisée par le petit tableau situé entre les colonnes I et M.

Ce sont uniquement des calculs de repérage et d'extraction qui ont permis la ventilation automatique des données.

Une grande quantité de nombres aléatoires pour représenter les 5 postes, a été générée en colonne O :

=ALEA.ENTRE.BORNES(1;5)

Ces valeurs figées à l'instant T ont été prélevées et copiées en colonne P, grâce à une macro impulsée par le bouton Répartir. En colonne Q, un calcul a permis de repérer les numéros de poste, tant qu'ils n'avaient pas été générés plus de 6 fois, selon la formule suivante :

=SI(NB.SI($P$4:P4;P5)< 6; MAX($Q$4:Q4)+1;'')

Il y a en effet 6 salariés à répartir sur chaque poste. Il en résulte un repérage par un nombre entier incrémenté. Dans le tableau source en colonne F, une extraction a été réalisée sur la recherche de ces nombres incrémentés, selon la formule suivante :

=SIERREUR(INDEX(P:Q; EQUIV(LIGNE(Q1);Q:Q;0); 1);'')

Chaque salarié se voit affecter un numéro qui correspond à une ligne de production. Pour que nous puissions réaliser l'extraction finale destinée à la distribution sur les postes, un calcul d'incrémentation de ces résultats a été réalisé en colonne G :

=F5&NB.SI($F$4:F4;F5) + 1

Sur la base de cette dernière incrémentation, il ne restait plus qu'à réaliser la recherche pour fournir l'extraction des salariés afin de les répartir dans le tableau énumérant les postes de travail :

=SIERREUR(INDEX(E:G; EQUIV(1&LIGNE(I1);G:G;0); 1);'')

Repérer une donnée dans un tableau Excel
Le plus dur a donc déjà été conçu. Au choix d'un salarié dans la liste déroulante, nous souhaitons le mettre en valeur instantanément dans les deux tableaux. Chacun pourra ainsi repérer plus facilement son affectation et son équipe.

Nous proposons de débuter par le cas le plus simple. Il s'agit de faire ressortir le salarié sélectionné dans le tableau des postes. La mise en forme conditionnelle Excel propose des règles préconçues permettant de formater dynamiquement des cellules, selon une égalité à respecter. Concrètement, si une cellule porte le même texte que celle de la liste déroulante en I14, elle doit changer de couleur.

• Choisir un salarié à l'aide de la liste déroulante en I14,
• Sélectionner toutes les cellules concernées par ce formatage dynamique, soit la plage I5:M10,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste qui apparaît, cliquer sur Règles de mise en surbrillance des cellules,
• Dans le sous menu qui se dévoile, cliquer sur la rubrique Egal à,

Une boîte de dialogue s'affiche. Elle consiste à définir le format dynamique à appliquer lorsque l'égalité avec une valeur ou une cellule est vérifiée.

• Cliquer directement sur la cellule I14 de la liste déroulante,
• Dérouler la liste sur la droite de la boîte de dialogue et choisir Format personnalisé,
• Dans la boîte de dialogue qui suit, cliquer sur l'onglet Remplissage,
• Choisir une couleur de fond, un vert pâle par exemple en cliquant dans la palette,
• Cliquer alors sur l'onglet Police de la boîte de dialogue,
• Dans la zone Style, sélectionner le Gras,
• Dérouler ensuite la liste Couleur et choisir un violet par exemple,
• Cliquer sur le bouton Ok pour valider ces réglages,

Comme l'illustre la capture ci-dessus, bien que la règle ne soit pas encore validée, un aperçu temps réel est proposé. La seule cellule du tableau vérifiant l'égalité est mise en évidence par rapport aux autres, selon les attributs de mise en forme que nous venons de définir.

• Cliquer sur le bouton Ok pour confirmer la création de la règle,
• Dans la liste déroulante en I14, choisir un autre salarié,

Instantanément, les réglages de mises en valeur du précédent salarié s'effacent. Dans le même temps, une autre cellule sort du lot. Il s'agit bien sûr du salarié correspondant au choix de la liste déroulante. Cette technique est donc efficace pour repérer facilement une donnée dans un tableau, à plus forte raison quand ce dernier est volumineux.

Mise en valeur des correspondances
Lorsqu'un salarié est repéré sur son poste, nous souhaitons en profiter pour faire ressortir depuis le tableau source, toutes les personnes affectées à la même équipe. La donnée permettant de relier les personnes est le numéro de poste. En J14, nous devons donc bâtir un calcul d'extraction intermédiaire permettant de restituer le numéro de poste du salarié sélectionné. Il suffit d'effectuer une recherche de ce salarié en colonne E pour restituer l'information attachée, située en colonne F.

La fonction de recherche la plus adaptée pour ce type d'extraction triviale est la fonction RechercheV. Sa syntaxe est la suivante :

=RechercheV(Valeur_cherchée ; Tableau_de_recherche ; Colonne_de_retour ; Faux)

La valeur cherchée est le nom du salarié sélectionné en I14. C'est lui qui permet d'établir la correspondance avec le numéro de poste. Concernant le tableau de recherche, cette fonction impose une limitation. La valeur cherchée doit nécessairement se trouver en première colonne. Nous désignerons donc les colonnes E et F. La colonne E énumère les noms des salariés. La colonne F est essentielle puisqu'elle fournit les numéros de postes correspondants. La colonne de retour est un numéro indiquant la colonne dans laquelle se situe l'information à renvoyer. Nous taperons le chiffre 2 pour indiquer d'importer l'élément situé en deuxième colonne de la sélection, soit le numéro de poste. Enfin, le dernier argument est un booléen que nous règlerons à Faux. Nous réaliserons ainsi une recherche selon une correspondance exacte. La formation Excel sur le calcul des primes d'ancienneté avait montré l'intérêt de régler ce paramètre booléen sur Vrai.

Si aucune valeur n'est choisie dans la liste déroulante, la fonction de recherche retournera une erreur. Pour éviter ces inscriptions disgracieuses, nous proposons d'imbriquer le calcul dans la fonction Excel SiErreur :

=SiErreur(Calcul_à_essayer ; Action_pour_gérer_l_erreur)

En premier paramètre, nous lui passerons donc la formule d'extraction. Si un résultat est retourné, il s'affichera. Si une erreur est interceptée, c'est le deuxième paramètre qui est déclenché. Nous inscrirons deux guillemets pour conserver la cellule vide.

• Sélectionner la cellule du calcul, soit J14,
• Taper le symbole = pour débuter la formule,
• Saisir la fonction de gestion d'erreur suivie d'une parenthèse, soit SiErreur(,
• Saisir la fonction de recherche suivie d'une parenthèse, soit RechercheV(,
• Cliquer sur la cellule I14 pour désigner la valeur à chercher,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner l'intégralité des colonnes E et F en tapant leurs références, comme suit : E:F,

A cause de la cellule de titre fusionnée en ligne 2, la sélection des deux colonnes par leur étiquette se serait étendue sur la largeur du tableau, soit B:G. Dans ce contexte, il est préférable de les saisir directement.

• Taper un point-virgule (;) pour passer dans l'argument du numéro de colonne,
• Saisir le chiffre 2 pour désigner la colonne listant les numéros de postes,
• Taper un point-virgule (;) suivi de la valeur Faux pour une correspondance exacte,
• Fermer la parenthèse de la fonction RechercheV,
• Taper un point-virgule (;) pour passer dans l'argument de la gestion d'erreur de la fonction SiErreur,
• Saisir deux guillemets, soit : '', pour garder la cellule vide en cas de souci,
• Fermer la parenthèse de la fonction SiErreur,
• Valider la formule en enfonçant la touche Entrée du clavier,

Le numéro de poste correspondant au salarié s'affiche instantanément. Si vous choisissez un autre salarié dans la liste déroulante, son numéro de poste se met automatiquement à jour en cellule J14. Dans le même temps, grâce aux réglages de mise en forme conditionnelle précédents, la mise en valeur s'adapte sur le tableau des équipes.

La formule que nous avons construite est la suivante :

=SIERREUR(RECHERCHEV(I14; E:F; 2; FAUX); '')



Repérer les dépendances dans un tableau Excel
Nous devons exploiter ce résultat pour faire ressortir tous les salariés appartenant à la même équipe, depuis le tableau source. Le critère est légèrement plus complexe que celui de la mise en forme conditionnelle précédente. L'objectif en effet consiste à mettre en valeur toute la ligne du salarié. La condition à vérifier est celle de l'égalité entre les numéros de poste.

• Sélectionner toutes les cellules du tableau des employés, soit la plage B5:G34,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• En bas de la liste, choisir Nouvelle Règle,
• Dans la boîte de dialogue qui suit, choisir le type de règle : 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 de mise en forme,
• Directement sur la feuille Excel, cliquer sur la cellule J14 pour la désigner,
• Taper de nouveau le symbole = pour établir la comparaison à valider,
• Sélectionner le premier numéro de poste dans le tableau, soit la cellule F5,
• Enfoncer deux fois la touche F4 du clavier pour ne la figer qu'en colonne, soit : $F5,

Les références d'une cellule complètement figée sont encadrées de deux dollars : $F$5. Dans ces conditions, elle ne peut plus bouger. Or, un critère de mise en forme conditionnelle raisonne de la même façon qu'un calcul répliqué. Il étudie les cellules du tableau dans l'ordre où elles apparaissent. Pour chaque ligne en effet, seule la cellule de la colonne F doit être comparée au numéro de poste inscrit en J14. Nous la figeons donc en colonne ($F5) pour que la condition ne soit pas vérifiée avec les autres colonnes de la même ligne. Mais pour chaque salarié, donc pour chaque ligne suivante, le critère doit bien être établi avec le numéro de poste qui lui correspond. Donc la condition doit se déplacer sur les lignes du dessous. C'est la raison pour laquelle nous avons défigé la cellule en ligne ($F5).

La formule du critère est terminée. Elle n'est pas longue mais peu paraître complexe à comprendre. Nous devons désormais définir la mise en valeur qui doit se déclencher sur chacune des lignes, lorsque la condition est vérifiée.

• Cliquer sur le bouton Format en dessous de la zone de critère,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Choisir un bleu assez clair dans la palette des couleurs,
• Activer ensuite sur l'onglet Police pour définir les attributs de texte,
• Choisir un style Gras et une couleur bleu foncé,
• Valider ces réglages par ok pour revenir sur la première boîte de dialogue,
• Cliquer de nouveau sur le bouton Ok pour valider la règle de mise en forme conditionnelle,

Toutes les lignes des employés appartenant à la même équipe (Même numéro de poste) apparaissent intégralement surlignées. C'est précisément l'astuce des références absolues qui a permis ce résultat. Le critère s'est bien déplacé en ligne. Figé en colonne, il a permis le rendu dynamique pour toutes les cellules de la même ligne.

Superposer les critères de mise en forme conditionnelle
Dans la mise en valeur de l'équipe depuis le tableau source, nous souhaitons faire ressortir explicitement le salarié sélectionné depuis la liste déroulante. Ce point de contrôle permet de faciliter le repérage et la correspondance entre les deux tableaux. Le principe est le même mais la procédure n'est pas anodine. L'employé en question est déjà mis en valeur par une règle de mise en forme conditionnelle. Si bien que cette nouvelle règle doit intervenir chronologiquement après. Elle sera prioritaire puisqu'elle se déclenchera après la première et remplacera les attributs de mise en valeur par les nouveaux. C'est donc une méthodologie chronologique qui permet de superposer les attributs de formats dynamiques régis par des règles.

La condition à bâtir est quasiment identique dans sa construction et dans la mise en oeuvre des références absolues. Mais comme seul le salarié sélectionné est concerné, la comparaison doit être effectuée entre la valeur contenue dans la colonne E et celle contenue en cellule I14.

• Sélectionner toutes les cellules du tableau source, soit la plage B5:G34,
• Cliquer sur le bouton Mise en forme conditionnelle du ruban Accueil,
• En bas de 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 du critère juste en-dessous, pour l'activer,
• Taper le symbole = pour débuter la syntaxe du critère,
• Sélectionner la première cellule de la colonne E, soit la cellule E5,
• Enfoncer deux fois la touche F4 pour ne la figer qu'en colonne, soit $E5,
• Taper le symbole = pour l'égalité de la condition à respecter,
• Sélectionner la cellule de la liste déroulante, soit I14, ce qui donne : $I$14,
• Cliquer juste en dessous sur le bouton Format pour définir les attributs personnalisés,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un vert pâle, identique à celui choisi pour la mise en forme conditionnelle dans le tableau des postes,
• Activer l'onglet Police pour définir les attributs personnalisés de texte,
• Cliquer sur Ok pour valider ces paramétrages,
• Cliquer de nouveau sur Ok pour confirmer la création de la règle de format dynamique,

Comme l'illustre la capture ci-dessus, la nouvelle règle prend le pas sur la précédente. La mise en valeur bleue est remplacée par une mise en valeur verte. La correspondance entre les deux tableaux est ainsi plus pertinente.

Si vous changez de salarié grâce à la liste déroulante, il est automatiquement repéré dans son poste. Dans le même temps, toute son équipe est parfaitement mise en valeur dans le tableau source, avec le chef de file qui ressort explicitement.

Nous avons donc réussi à effectuer un repérage efficace et instantané, grâce à des formats dynamiques qui réagissent aux choix de l'utilisateur. Dans le même temps, nous sommes parvenus à cumuler plusieurs critères de mise en forme conditionnelle sur une même plage de cellules.



Comme l'illustre la capture ci-dessus (Mise en forme conditionnelle / Gérer les règles), c'est l'ordre d'empilement des conditions qui permet ce cumule selon la chronologie à respecter.