Repérer les homonymes en couleur avec Excel

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 les homonymes en couleur

Identifier la présence d'homonymes dans une base de données est souvent un enjeu. C'est normalement par une clé primaire qu'on les différencie. Mais lorsque ce n'est pas le cas, il s'agit de les débusquer. Et nous allons le voir, c'est une formule matricielle, encore une fois, qui permet de résoudre le cas lorsqu'elle est utilisée dans une règle de mise en forme conditionnelle.

Classeur Excel à télécharger
Pour démontrer la solution, nous suggérons d'appuyer les travaux sur un classeur offrant un tableau constitué de certaines de ces répétitions.

• Télécharger le classeur reperer-homonymes.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons effectivement un petit tableau constitué de trois colonnes. Les noms et prénoms sont respectivement inscrits en colonnes C et D. En y regardant de plus près, nous constatons que certains homonymes existent.

Les noms identiques
Pour bien comprendre le raisonnement que nous allons mettre en place, nous proposons de bâtir tout d'abord la syntaxe de la règle de mise en forme à construire, dans les cellules de la colonne F. Vous le savez, la fonction matricielle sommeprod est initialement prévue pour additionner les multiplications des lignes respectives des matrices confrontées. Dans un emploi dérivé comme nous l'avons déjà fait à maintes reprises, elle permet d'évaluer des matrices conditionnelles. Il est question de trouver la répétition du nom en colonne C et la répétition du prénom pour le même nom en colonne D. Chaque correspondance répondra par un indicateur booléen. En les multipliant et additionnant naturellement par la fonction SommeProd, nous connaîtrons la quantité exacte d'homonymes s'ils existent.

• Cliquer sur la cellule F5 pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Taper le nom de la fonction matricielle suivie d'une parenthèse, soit SommeProd(,
• A gauche de la barre de formule, cliquer sur le petit bouton de l'assistant fonction,

De cette manière, nous affichons l'assistant pour la fonction SommeProd. Il va livrer des résultats intermédiaires, au fil de la construction, qui vont nous aider à comprendre le mécanisme pour débusquer ces homonymes. La zone Matrice1 est active par défaut. Nous allons pouvoir l'implémenter.

• Ouvrir une parenthèse pour accueillir la première matrice conditionnelle,
• Désigner tous les noms en sélectionnant la plage de cellules C5:C19,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C$5:$C$19,

En effet, nous répliquerons cette formule sur les lignes du dessous. Et malgré ce déplacement, les bornes de la matrice pour la comparaison, ne doivent pas varier.

• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Puis, cliquer sur le premier nom, soit la cellule C5,

Cette fois, nous ne la figeons pas. Avec la réplication, ce sont tous les noms suivants qui devront être trouvés dans cette matrice aux bornes fixes.

• Fermer alors la parenthèse de la matrice conditionnelle,

Comme vous pouvez le voir, l'assistant fonction répond aussitôt par une matrice de valeurs booléennes, sur la droite de la zone Matrice1. Chaque indicateur Vrai repère la position d'une concordance dans la matrice des noms avec celui qui est en cours d'analyse. Mais nous l'avons dit, nous avons besoin de travailler sur des chiffres pour que la fonction SommeProd puisse les additionner à l'issue. Pour cela, nous pouvons forcer la conversion par une simple multiplication.

• Multiplier ce résultat par le chiffre 1, soit : *1,

Cette fois, c'est bien une matrice de chiffres que nous obtenons.

Les prénoms identiques
Nous devons maintenant établir la correspondance conjointe sur la colonne des prénoms. La technique est identique.

• Cliquer dans la zone Matrice2 pour l'activer,
• Ouvrir une parenthèse pour accueillir la seconde matrice conditionnelle,
• Désigner tous les prénoms en sélectionnant la plage de cellules D5:D19,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $D$5:$D$19,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Puis, désigner le premier prénom en cliquant sur sa cellule D5,
• Fermer la parenthèse de la matrice conditionnelle,
• Forcer alors la conversion en multipliant par le chiffre 1 : *1,

Comme vous pouvez le voir, l'assistant fonction réagit de nouveau instantanément. Il repère aussi les positions des prénoms trouvés. Lorsque ces emplacements correspondent à ceux des noms, la multiplication du chiffre 1 par 1, identifie bien un homonyme. Tous ces chiffres additionnés à l'issue livrent le décompte de tous les homonymes dans ce tableau. Et c'est bien ce qu'indique le résultat en bas à droite de la boîte de dialogue. Sur cette paire, deux homonymes sont trouvés. Vous l'avez compris, le même prénom peut être trouvé à de multiples reprises. Mais tant qu'il ne correspond pas à l'association cherchée avec le nom, la multiplication du chiffre 1 par 0 conduit à un résultat nul et la paire est ignorée.

Pour résumer, les correspondances des paires sont vérifiées ligne à ligne. Il est important de comprendre que les fonctions Nb.Si et Nb.Si.Ens auraient été inopérantes dans ce cas. Elles dénombrent toutes les informations trouvées indépendamment, sans respecter l'association des paires, d'où l'importance de cette technique matricielle.

• Cliquer sur le bouton Ok en bas de la boîte de dialogue,
• Puis, cliquer et glisser la poignée du résultat jusqu'en ligne 19,

Nous obtenons une série de chiffres. A chaque fois que la quantité d'une unité est dépassée, ce sont des homonymes qui sont repérés. Si vous remplacez un prénom par un autre existant déjà, sans établir la correspondance avec le nom, les indicateurs ne bronchent pas. En revanche, si vous ajoutez une nouvelle paire identique, elle est instantanément identifiée.

Les paires identiques
C'est donc cette syntaxe que nous allons maintenant exploiter dans une règle de mise en forme conditionnelle. Mais nous le verrons et nous le comprendrons, deux ajustements sont à opérer avec les références absolues ($).

• Cliquer sur la première cellule du calcul (F5) pour la sélectionner,
• Dans la barre de formule, sélectionner et copier (CTRL + C) toute la syntaxe,
• Sortir de la barre de formule en enfonçant la touche Echap,
• Sélectionner alors l'intégralité du tableau, soit la plage de cellules B5:D19,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans les propositions, choisir l'option 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 dessous pour l'activer,
• Puis, coller (CTRL + V) la précédente syntaxe,

Contrairement à précédemment, c'est l'intégralité du tableau qui est désignée pour l'analyse de la règle. Les correspondances doivent être cherchées sur toutes les lignes certes mais uniquement dans les colonnes respectives C et D. C'est la raison pour laquelle les cellules du nom et du prénom doivent être figées en colonne. Il est donc question d'ajouter un dollar devant le C de la cellule C5 pour la première comparaison et un autre devant le D de la cellule D5 pour la seconde comparaison. Il est aussi question d'ajouter un critère pour déclencher la mise en forme de la règle :

=SOMMEPROD(($C$5:$C$19=$C5)*1; ($D$5:$D$19=$D5)*1)>1

En effet, un homonyme est considéré comme tel dans la mesure où il est trouvé au moins deux fois.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans celle qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange clair pour le fond de la cellule de l'homonyme,
• Activer alors l'onglet Police,
• Avec la seconde liste déroulante, choisir un orange foncé pour le texte,
• Revenir sur la première boîte de dialogue en cliquant sur le bouton Ok,
• Puis, valider la création de la règle en cliquant de nouveau sur le bouton Ok,

Comme vous pouvez l'apprécier, toutes les lignes des homonymes ressortent instantanément en couleur pour un repérage visuel sans équivoque. Si vous créez de nouvelles paires identiques, les lignes s'illuminent aussitôt.