Comparer les données de deux tableaux Excel

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


Sujets que vous pourriez aussi aimer :


Comparer les données de deux tableaux

Les entreprises peuvent être confrontées à des anomalies de données qu'il faut savoir appréhender au plus juste pour être en mesure de les corriger. Ces problèmes peuvent surgir lorsque les informations émanent de plusieurs sources mal consolidées. Il peut aussi s'agir de mises à jour manquées.



Mais ces défauts ne sont pas seulement dus à des enregistrements manquants. Les différences peuvent aussi s'observer sur la casse ou l'orthographe.

Source et présentation de la problématique
Pour la mise en place de la solution, nous proposons de réceptionner un classeur Excel offrant des données à comparer.

• Télécharger le classeur comparer-textes-cellules.xlsx 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 l'unique feuille de ce classeur. Elle est nommée : Clients. Elle présente deux tableaux censés restituer les résultats d'une même source de données. Mais comme le stipule leurs titres, l'une est gérée en interne, l'autre est une source importée, depuis un serveur distant par exemple.



Ces deux tableaux ont la même longueur. L'un des deux pourrait très bien proposer plus d'enregistrements. Cette différence n'altèrerait en rien les démonstrations et solutions que nous allons apporter.

Leur structure est en revanche et fort logiquement identique. Des clients y sont référencés sur quatre champs. Si vous consultez attentivement leurs contenus, vous remarquez que la source importée est totalement vidée de ses caractères latins. Tous les accents ont disparu. Et cette remarque est loin d'être anodine. Deux enregistrements équivalents seront considérés comme étrangers du fait de cette différence.

Assembler les données à comparer
Pour débusquer les différences entre les deux tableaux, nous suggérons de progresser par étapes. Ainsi la compréhension des techniques s'avèrera plus simple. Si des informations ont changé, il peut s'agir pour chaque enregistrement, d'une donnée sur l'un ou plusieurs de ses champs. Une personne peut avoir déménagé par exemple. De fait, son code postal n'est plus le même. Nous devons donc effectuer une analyse sur ces données regroupées. Pour chacun des deux tableaux, nous suggérons donc de concaténer les informations par ligne. Et pour ce faire, nous allons utiliser les colonnes aux extrémités.

• Sélectionner tout d'abord la cellule A7,
• Taper le symbole égal (=) pour démarrer la formule,
• Sélectionner la première donnée à assembler, soit le nom en cellule C7,
• Taper le caractère de concaténation (&) avec la touche 1 en haut à gauche du clavier,
• Sélectionner la deuxième information à assembler, soit le prénom en cellule D7,
• Taper de nouveau le caractère de concaténation (&),
• Puis, sélectionner la dernière donnée à concaténer, soit le code postal en cellule E7,
• Valider le calcul par le raccourci clavier CTRL + Entrée,

Comme vous le savez, cette technique permet de conserver active la cellule du résultat. Nous pouvons donc l'exploiter dans la foulée, sans devoir la resélectionner.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Pour chaque enregistrement, nous obtenons les données assemblées sur les trois champs. La civilité n'a pas été incluse. Il paraît compliqué qu'un client change de sexe entre deux périodes. La chaîne obtenue est une sorte de carte d'identité de l'enregistrement. C'est grâce à elle que nous pourrons débusquer le moindre changement opéré. Mais pour cela, nous devons reproduire strictement le même assemblage sur le deuxième tableau.

• Sélectionner la première cellule à droite du deuxième tableau,

Certes, sa largeur est limitée et le résultat débordera. Mais dans tous les cas, ces colonnes intermédiaires sont destinées à être masquées à l'issue. Elles doivent uniquement servir de relai pour la solution finale.

• Construire la formule suivante : =H7&I7&J7,
• Puis, la répliquer sur tout le tableau,

Comme précédemment, nous obtenons une chaîne regroupant les trois informations descriptives du client.

Comparer les données assemblées
La comparaison à la recherche des différences ne peut être effectuée ligne à ligne. Rien n'indique en effet qu'un enregistrement n'ait pas été déplacé ou qu'un autre ait été inséré dans l'intervalle. L'idée consiste donc à rechercher la présence de chaque chaîne de la première colonne, dans la seconde colonne. Et la méthode la plus simple suggère d'exploiter la fonction Excel de dénombrement Nb.Si :

=Nb.Si(Plage_où_chercher; Critère_à_chercher)

En premier argument, nous devons lui indiquer la plage de recherche, soit les cellules de la seconde colonne. En deuxième argument, nous devons lui indiquer l'élément cherché, soit la chaîne concaténée de la première colonne. Si cette fonction retourne une valeur positive, cela signifie qu'elle a compté la présence de l'élément cherché.

Pour cette étude comparative, nous allons exploiter la colonne M.

• Sélectionner la cellule M7 et taper le symbole égal (=) pour initier le calcul,
• Saisir le nom de la fonction suivi d'une parenthèse, soit : Nb.Si(,
• Sélectionner les données de la colonne K, soit la plage de cellules K7:K24,
• Enfoncer la touche F4 du clavier pour la figer,

En effet, chaque enregistrement de la première colonne doit être cherché. Donc, nous allons répliquer la logique de ce calcul sur les lignes du dessous. Et bien entendu, les coordonnées de cette plage ne doivent pas suivre le déplacement. La recherche doit toujours être effectuée dans cette section bornée.

• Taper un point-virgule (;) pour passer dans l'argument du critère,
• Sélectionner la première chaîne assemblée de la première colonne, soit la cellule A7,
• Fermer la parenthèse de la fonction Nb.Si et valider la formule par le raccourci CTRL + Entrée,
• Cliquer et glisser la poignée du résultat jusqu'en ligne 24,

La colonne de comparaison livre une alternance de 0 et de 1. Le chiffre 1 indique que l'enregistrement de la ligne en cours et de la première colonne est trouvé dans la seconde. Le chiffre 0 stipule qu'aucune correspondance n'est établie.

Mais à y regarder de plus près, ces dénombrements ne semblent pas du tout cohérents. Par exemple, les deuxième et troisième enregistrements sont bien présents dans les deux bases de données. Excel réalise en effet une recherche stricte. La simple présence ou absence d'un accent suffit à biaiser le résultat. L'écart d'interprétation aurait encore grandi si nous avions exploité la fonction Excel Exact. Celle-ci est en effet sensible à la casse dans les comparaisons.



Nous ne pouvons donc pas exploiter ces données à ce stade.

Supprimer les accents dans les chaînes de texte
Pour trouver les réelles différences entre les données des deux tableaux, nous devons donc réaliser un traitement intermédiaire. Ce traitement consiste à neutraliser les accents. Pour cela, nous devons exploiter la fonction Excel Substitue :

=Substitue(Texte; Ancien_texte; Nouveau_texte)

En premier argument, nous devons lui spécifier la chaîne assemblée et recherchée dans l'autre colonne. En second argument, nous devons désigner le caractère à remplacer, par exemple la lettre é. Enfin, en dernier argument, nous devons mentionner le caractère de remplacement, soit la lettre e dans cet exemple.

Mais, vous l'avez compris, la fonction Substitue ne permet de remplacer qu'une occurrence par une autre. Et il n'est pas possible d'exploiter les expressions régulières avec Excel comme nous avons déjà pu le faire avec le code VBA.

Pour remplacer tous les accents, nous allons donc devoir imbriquer autant de fonctions Substitue les unes dans les autres, que nécessaire :

=Substitue(Substitue(Substitue(Texte; 'é'; 'e'); 'è'; 'e')...; 'ù'; 'u')

Certes, la syntaxe va considérablement s'allonger mais elle ne propose aucune difficulté. De plus et vous le verrez, le résultat est redoutable d'efficacité.

• Réaliser ces imbrications en cellule A7 en adaptant la formule comme suit :

=SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE(C7&D7&E7; 'é'; 'e'); 'è'; 'e'); 'ê'; 'e'); 'ë'; 'e'); 'à'; 'a'); 'â'; 'a'); 'ù'; 'u'); 'ô'; 'o')

Une fonction Substitue est utilisée pour chaque accent à remplacer.

• Après validation du calcul, le répliquer sur la hauteur du tableau,

En consultant la colonne Comparaison, vous constatez que la densité de correspondances, matérialisées par le chiffre 1, a aussitôt considérablement augmenté. Il s'agit donc de la bonne méthode pour comparer avec le maximum de succès, des informations de bases de données.

Bien que le second tableau ne propose pas d'accents, il paraît tout de même opportun d'adapter la formule en K7, avec les mêmes imbrications de la fonction Substitue, puis de la répliquer sur la hauteur. Rien n'indique en effet, que des caractères latins ne seront pas insérés à l'avenir.

Repérer les non correspondances
Grâce à ces calculs intermédiaires assemblant et nettoyant les chaînes, nous pouvons désormais apporter la solution pour repérer dynamiquement et visuellement les différences entre deux tableaux. Il s'agit de bâtir une règle de mise en forme conditionnelle. Cette dernière doit bien sûr exploiter la fonction Nb.Si à son tour. Dès qu'un assemblage d'une colonne n'est pas comptabilisé dans l'autre, la ligne entière du tableau doit être surlignée. Ainsi, chaque enregistrement différent sera automatiquement identifié.

• Sélectionner toutes les données du premier tableau, soit la plage de cellules B7:E24,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour...,
• Cliquer ensuite dans la zone de saisie juste en dessous pour l'activer,
• Saisir la syntaxe de la règle comme suit :

=Nb.Si($K$7:$K$24; $A7)=0

Dans la colonne assemblée du second tableau, nous cherchons chaque chaîne assemblée du premier. C'est la raison pour laquelle la colonne de recherche est totalement figée ($K$7:$K$24). Ainsi, elle ne subira pas le déplacement de l'analyse de ligne en ligne. L'élément cherché quant à lui n'est figé qu'en colonne ($A7), et c'est fondamental. La règle comprend donc que pour chaque ligne, l'élément cherché est forcément dans la colonne A. Et pour chaque enregistrement, c'est bien l'assemblage de la ligne qui est considéré, puisque cette dernière n'est pas figée.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange clair,
• Activer ensuite l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir une couleur de texte rouge foncé,
• Puis, valider ces attributs de format en cliquant sur le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle indique explicitement que tout enregistrement non concordant sera surligné selon les attributs de format définis.

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

Comme vous pouvez le voir, seuls les enregistrements présents dans le premier tableau et absents dans le second sont clairement identifiés. Cette règle fonctionne à merveille grâce au nettoyage des accents effectué en amont. Il est important de le souligner. Elle permet aussi de pointer les enregistrements ayant subi des modifications partielles.

• En cellule J7, modifier le code postal 06500 par le suivant : 06600, puis valider,

Instantanément, le premier client est désormais identifié comme une ligne d'information à corriger ou à ajuster.

Bien entendu, pour un repérage croisé, il convient de bâtir une règle similaire sur le second tableau, après avoir sélectionné l'intégralité de ses cellules, soit la plage G7:J24 : =NB.SI($A$7:$A$24; $K7)=0.

Surligner un client cherché
Grâce à ces travaux réalisés en amont, notamment sur les accents, nous allons pouvoir décliner le mécanisme pour identifier dynamiquement la ligne d'un client cherché par son nom et son prénom. Ces éléments de recherche sont attendus en cellules E2 et G2. Avant d'engager de nouvelles règles de mise en forme conditionnelle, nous devons préalablement assembler ces éléments dans une chaîne purgée de ses accents. Ainsi, nous pourrons réaliser des recherches fructueuses dans les deux sources de données. Et pour cela, nous choisissons la cellule K2.

Selon le même principe, la formule à y bâtir est donc la suivante :

=SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE( SUBSTITUE(E2&G2; 'é'; 'e'); 'è'; 'e'); 'ê'; 'e'); 'ë'; 'e'); 'à'; 'a'); 'â'; 'a'); 'ù'; 'u'); 'ô'; 'o')

La règle que nous devons construire doit trouver l'information cherchée concaténée et purgée dans chaque assemblage de chaîne, pour les deux tableaux. Nous pouvons donc exploiter la fonction Excel Cherche : =Cherche(Texte_cherché; Chaîne_de_recherche).

Si l'élément cherché à passer en premier paramètre est trouvé dans la chaîne de recherche, à passer en second paramètre, la fonction Cherche retourne une valeur numérique. Cette valeur correspond à la position de l'occurrence dans la chaîne. Dans le cas contraire, la fonction Cherche renvoie une erreur. La règle de mise en forme conditionnelle a donc simplement besoin de savoir si la recherche est fructueuse. Aucun critère d'égalité n'est nécessaire.

• Sélectionner toutes les données du premier tableau, soit la plage de cellules B7:E24,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le type : Utiliser une formule pour ...,
• Dans la zone de saisie du dessous, taper la syntaxe suivante :

=CHERCHE($K$2;$A7)

Nous cherchons simplement les éléments demandés et concaténés en cellule K2 dans chaque chaîne assemblée du tableau, soit en cellule A7. La cellule K2 doit nécessairement rester complètement figée ($K$2). Dans chaque chaîne assemblée à passer en revue, donc ligne à ligne, c'est toujours cette même donnée placée en K2 qui est cherchée. La recherche doit donc être réalisée pour chaque enregistrement. C'est pourquoi, nous libérons la ligne de la chaîne de recherche ($A7). Sa colonne reste bien figée quant à elle. Pour chaque enregistrement en effet, la correspondance doit être trouvée en colonne A.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, attribuer un fond bleu clair et un texte bleu foncé,
• Valider ces attributs en cliquant sur le bouton Ok,

C'est donc la ligne complète de l'enregistrement concordant qui doit être mise en valeur dynamiquement.

• Valider la création de la règle en cliquant sur le bouton Ok,

De retour sur la feuille, en tapant les informations d'un client, en respectant ou nom les accents, vous constatez que l'enregistrement est automatiquement repéré. Et il en est naturellement de même, en adaptant la syntaxe de la règle pour le deuxième tableau: =CHERCHE($K$2;$K7).

Enfin, il convient d'appliquer le blanc en couleur de texte pour les colonnes A et K. Les calculs intermédiaires demeurent mais ne sont plus visibles, sans pour autant masquer les colonnes.