Trouver les différences de données 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 :


Identifier toutes les différences de données

La solution que nous proposons de développer consiste à repérer très précisément les informations qui diffèrent entre deux sources de données. Il ne s'agit pas d'identifier l'enregistrement complet pour lequel des informations auraient évoluer. Il s'agit bien d'alerter sur la donnée spécifiquement et potentiellement modifiée au sein de chaque enregistrement.



Les différences de casse ne sont pas considérées quant à elles.

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 differences-sources-donnees.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é,

Le classeur réceptionné est muni de deux feuilles nommées respectivement Base1 et Base2. Elles proposent chacune une petite source de données. A priori, les deux tableaux sont identiques. Mais ils regorgent de petites différences dues à des changements de codes postaux ou à des fautes dans la saisie des prénoms par exemple. Certains clients sont aussi présents dans une source et absents dans l'autre.

Pour simplifier la mise en oeuvre de la formule alambiquée, les deux tableaux possèdent un nom.

• 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,

La boîte de dialogue du gestionnaire de noms apparaît. Et comme vous pouvez le voir, les deux tableaux sont reconnus sous les noms respectifs BdTab1 et BdTab2.

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

Ici, les bornes des tableaux sont considérées comme strictes. Mais pour des plages mouvantes et des comparaisons évolutives, il suffirait d'exploiter la fonction Excel Decaler comme nous l'avions appris à l'occasion d'une formation.

Identifier chaque différence
Pour mettre en valeur chacune des disparités trouvées d'un tableau à un autre, nous devons bâtir une règle de mise en forme conditionnelle. La première colonne est considérée comme le champ de référence. En effet, dans une base de données digne de ce nom, chaque enregistrement est référencé par un identifiant unique. Pour l'étude, nous considérons donc qu'aucune différence ne peut être décelée dans cette rangée.

A partir de chacune des données de cette colonne, l'idée consiste à trouver sa position en ligne dans l'autre base de données. Et pour cette ligne, chaque élément de ses champs doit être comparé. Si des différences sont observées, elles doivent être relevées par format dynamique.

Pour bâtir cette règle de mise en forme conditionnelle, nous avons besoin d'utiliser les deux fonctions de recherche Index et Equiv :

=Index(BD_de_recherche; ligne; colonne)
=Equiv(Valeur_cherchée; Colonne_de_recherche; Mode_de_recherche)

La fonction Index permet d'extraire d'une base de données (BdTab2) à passer en premier paramètre, l'information située au croisement d'une ligne, à passer en deuxième paramètre, et d'une colonne à passer en troisième paramètre.

L'indice de ligne dépend du nom cherché dans l'autre source de données. Nous récupèrerons cet indice grâce à la fonction Equiv en lui passant la cellule du nom cherché en premier paramètre.

• Sélectionner les données du tableau de la feuille Base1, soit la plage de cellules B3:D20,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie juste en dessous pour l'activer,
• Taper alors le symbole égal (=) pour initier la syntaxe de la règle,
• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Saisir le nom du tableau de recherche, soit : BdTab2,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne,
• Saisir la fonction cherchant cet indice, suivie d'une parenthèse, soit : Equiv(,
• Désigner la première valeur cherchée en cliquant sur la cellule B3 de la feuille Base1,
• Enfoncer deux fois la touche F4 du clavier, ce qui donne : $B3,

Ainsi, nous la libérons en ligne et la conservons figée en colonne. Nous le disions, la première colonne est considérée comme celles des références. Nous cherchons donc à trouver sa position dans la seconde base de données. En libérant cette cellule en ligne, nous les passerons toutes en revue pour les rechercher tour à tour.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• En bas de la fenêtre Excel, cliquer sur l'onglet Base2 pour activer sa feuille,
• Désigner alors l'ensemble des références pour la recherche, soit : Base2!$B$3:$B$20,
• Taper un point-virgule suivi du chiffre 0, soit : ;0, pour une recherche exacte,
• Fermer alors la parenthèse de la fonction Equiv,

A ce stade, nous sommes censés avoir retourné à la fonction Index, l'indice variable de ligne de l'élément cherché, afin de comparer chacune de ses valeurs de colonne avec celles de l'autre base de données. Il nous reste à lui fournir l'information sur l'indice de colonne pour effectuer le croisement de la donnée à extraire et à comparer. Et c'est précisément là que réside l'astuce. Pour un même nom, toutes ses colonnes (Ici il y en a deux : Le prénom et le CP), doivent être passées en revue dans la première base à la recherche de correspondances dans la seconde.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de la fonction Index,
• Taper la fonction donnant l'indice d'un champ, suivie de deux parenthèses, soit : Colonne(),

En paramètre, nous aurions très bien pu lui passer la cellule B3 complètement défigée, de la feuille Base1. Mais, en l'absence de paramètre, elle considère la cellule active. Elle suit donc le raisonnement chronologique de la mise en forme conditionnelle qui passe en revue toutes les cellules de la sélection. C'est ainsi pour un enregistrement dont la ligne a été trouvée, que ses colonnes vont être analysées.

En revanche, la fonction Colonne retourne une information absolue. Pour elle, la colonne B est placée à l'indice 2. Or, il s'agit de la première colonne de notre tableau. Nous devons corriger cet écart.

• A la suite de la syntaxe, retrancher une unité, soit : -1,
• Puis, fermer la parenthèse de la fonction Index,

Désormais, pour chaque champ analysé de chaque enregistrement, nous devons vérifier si une correspondance est trouvée avec l'extraction effectuée depuis le tableau de la feuille Base2.

• Terminer la syntaxe par l'inégalité suivante : <>B3,

Nous prenons soin et c'est fondamental, de complètement défiger la première cellule pour la comparaison. Cette cellule désigne le premier nom du tableau de la feuille Base1. De cette manière, au fil des extractions d'informations pour chaque enregistrement repéré, les données sont comparées colonne à colonne. Si l'inégalité est vérifiée, cela signifie qu'une différence est isolée. Nous devons la faire ressortir.

• Cliquer sur le bouton Format situé 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 rouge foncé,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un gris très clair pour la couleur du texte,
• Valider ces attributs de format avec le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue qui illustre parfaitement le contexte. Dès lors qu'une différence est décelée entre les deux bases de données, les cellules correspondantes du premier tableau doivent être mises en valeur.

• Cliquer sur le bouton Ok pour valider la création de la règle de mise en forme conditionnelle,

De retour sur la feuille, des cellules surgissent instantanément. Prenons l'exemple du dernier enregistrement repéré en ligne 15. Il s'agit de Madame Sarbaque. La règle alerte sur une incohérence décelée au niveau du code postal.

• En bas de la fenêtre Excel, cliquer sur l'onglet Base2 pour atteindre sa feuille,

L'enregistrement de cette personne se situe cette fois en ligne 12. Le prénom est fort logiquement identique mais le code postal a changé. Sa valeur 13500 est passée à 06500. Notre solution est donc parfaitement fonctionnelle pour isoler précisément les données ayant été modifiées pour des enregistrements a priori identiques. Et nous allons le prouver une fois encore.

• Sur la feuille Base2, sélectionner la cellule C11,

Il s'agit du client Douche Raël. Il existe bien dans le tableau de la feuille Base1. Nous proposons de modifier le prénom en supprimant les accents.

• Donc, en C11, saisir le prénom Rael,
• Après validation, revenir sur la feuille Base1,

Comme vous pouvez le voir, la donnée de cet enregistrement y est désormais repérée comme erronée ou plutôt non concordante.



La formule de la règle de mise en forme conditionnelle que nous avons bâtie est donc la suivante :

=INDEX(BdTab2; EQUIV($B3; Base2!$B$3:$B$20; 0); COLONNE()-1)<>B3

Pour repérer des enregistrements totalement existants, souvenez-vous, il s'agit d'exploiter la fonction SommeProd. Grâce à son raisonnement matriciel, elle permet de recouper des critères sur des colonnes respectives :

=SOMMEPROD((Base2!$B$3:$B$20=$B3) * (Base2!$C$3:$C$20=$C3))=0

Nous avions démontré son efficacité à l'occasion d'une formation destinée à comparer deux bases de données.

Bien sûr et pour terminer, afin d'identifier toutes les données du second tableau qui diffèrent dans le premier, il convient d'adapter la syntaxe de la règle de mise en forme conditionnelle.

=INDEX(BdTab1; EQUIV($B3; Base1!$B$3:$B$20;0); COLONNE()-1)<>B3

Les recherches ne sont plus effectuées dans le tableau BdTab2 ni dans la première colonne de la feuille Base2 pour la fonction Equiv. Il s'agit donc de les remplacer respectivement par BdTab1 et Base1.