Comparer et confronter deux bases de données 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 deux bases de données Excel

Dans une précédente formation Excel, nous avons fabriqué une solution permettant d'identifier les différences entre deux tableaux de données. Pour cela, nous avons construit des calculs intermédiaires assemblant les données des champs et les purgeant des accents. Et sur ces chaînes, nous avons bâti des règles de mise en forme conditionnelle les comparant.



Ici, nous proposons d'aborder le problème sous un angle différent. Nous souhaitons toujours apporter la solution permettant de déceler les différences entre deux bases de données. Mais pour cela, nous souhaitons exploiter les calculs matriciels et notamment la fonction SommeProd. Il va certes en résulter une syntaxe plus complexe mais aussi une mise en oeuvre plus simple et plus directe.

Source et présentation de la problématique
Pour établir ces travaux, nous proposons tout d'abord de réceptionner un classeur offrant des données à analyser et à comparer.

• Télécharger le classeur comparer-deux-bases-de-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é,

Nous réceptionnons un classeur muni d'une seule feuille nommée Clients-matriciel. Et en guise de bases de données, nous retrouvons les deux tableaux que nous avons exploités pour les démonstrations de la formation précédente. Mais ils feront parfaitement l'affaire pour la mise en place de la solution à déployer.

Dans l'un et l'autre tableau, des clients sont référencés sur quatre champs. Des différences peuvent être observées entre les deux sources lorsque des enregistrements complets sont présents dans l'une et pas dans l'autre. Mais ce n'est pas tout, les données peuvent avoir évolué. Un client peut très bien être référencé dans les deux bases de données mais avoir déménagé entre temps. De fait, son code postal est susceptible d'avoir évolué. Donc, sa ligne doit être identifiée en anomalie à corriger.

Et ces anomalies doivent être repérées par des jeux de couleur qui se déclenchent dynamiquement.

Croisement matriciel des critères
Nous avons déjà exploité la fonction Excel SommeProd sous une forme inhabituelle. Par défaut, elle permet de multiplier les valeurs des lignes respectives des matrices spécifiées en arguments. Par matrice, vous pouvez traduire tableau. A l'issue, toutes les valeurs résultant des multiplications sont ajoutées.

=SommeProd(Matrice1; Matrice2; MatriceN)

On l'utilise souvent pour calculer la moyenne générale des étudiants en tenant compte des coefficients associés à chaque matière. C'est ainsi, à l'aide d'une seule formule, que toutes les notes sont multipliées par leur coefficient puis ajoutées entre elles.

Ici, le contexte est différent. Nous souhaitons recouper les critères entre eux sur chaque matrice respective. Pour savoir si un enregistrement d'une source est trouvé de façon strictement identique dans l'autre, trois conditions sont à observées. Pour une même ligne, le nom en colonne C recoupé au prénom en colonne D, recoupé au code postal en colonne E, doivent être identifiés dans les colonnes respectives de l'autre table de données.

En conséquence, en paramètre, nous allons lui passer les tests à recouper sur chaque colonne. Ils consistent en une égalité à observer sur chaque rangée, chacune recoupée avec les autres. Si le résultat obtenu vaut 0, cela signifie que la correspondance n'est pas établie.

Pour la bonne compréhension du mécanisme, l'assistant fonction est d'une aide précieuse. Or, ce dernier n'est pas disponible au moment de la création d'une règle de mise en forme conditionnelle. C'est pourquoi, nous proposons premièrement et temporairement, de bâtir ce calcul de repérage en colonne A. L'idée est d'identifier tous les enregistrements du premier tableau absents ou modifiés dans le second. Nous récolterons ensuite la syntaxe pour bâtir la règle de format dynamique.

• Sélectionner la cellule A4 et taper le symbole égal (=) pour débuter la formule,
• Saisir le nom de la fonction suivi d'une parenthèse, soit : SommeProd(,
• A gauche de la barre de formule, cliquer sur le bouton Insérer une fonction,

Nous affichons ainsi l'assistant Excel pour la fonction SommeProd. Une boîte de dialogue apparaît. Elle propose de renseigner les différentes matrices à énumérer. Mais dans notre cas, il ne s'agit pas d'une énumération mais de critères à recouper sur des colonnes. Nous allons bâtir l'intégralité de l'expression dans la zone Matrice1.

• Dans la zone Matrice1, ouvrir une parenthèse pour formuler la première condition,
• Sélectionner les noms du deuxième tableau, soit la plage de cellules H4:H21,
• Enfoncer la touche F4 du clavier pour la figer, ce qui donne : $H$4:$H$21,

En effet, les bornes de la plage de recherche ne doivent pas bouger en même temps que le calcul sera répliqué.

• Taper le symbole égal (=) pour énoncer le premier critère à vérifier,
• Sélectionner alors le premier nom à trouver depuis le premier tableau, soit la cellule C4,
• Enfoncer trois fois de suite la touche F4 du clavier, ce qui donne : $C4,

Ainsi, nous libérons la ligne et figeons la colonne. Chaque nom de client doit être cherché dans la colonne du second tableau. Il faut laisser la ligne libre de les passer tous en revue. Mais depuis le premier tableau, chaque nom est nécessairement placé dans sa colonne C. Pour trouver une correspondance, nous ne devons donc pas lui permettre de se déplacer à l'horizontale.

• Fermer la parenthèse de la première condition matricielle,

Aussitôt, des indications surgissent sur la droite de la boîte de dialogue, en regard de la zone de saisie. Il s'agit de résultats booléens livrés sous forme de tableau. Le premier indique Vrai. Il signifie donc que le nom cherché depuis le premier tableau est trouvé sur la première ligne de la colonne désignée dans le second tableau. Si ce client figurait sur la troisième ligne du second tableau, la mention Vrai apparaîtrait seulement en troisième position.

Nous devons maintenant recouper cette condition sur le nom avec le critère sur le prénom. Dans un calcul matriciel, c'est le symbole de l'étoile (*) qui permet de croiser des conditions sur les tableaux spécifiés.

• A la suite de la syntaxe, taper le symbole de l'étoile (*) et ouvrir une parenthèse,
• Dans le second tableau, désigner les prénoms, soit la plage de cellules I4:I21,
• Pour les mêmes raisons que précédemment, enfoncer la touche F4 pour la figer,
• Taper le symbole égal (=) pour exprimer la condition croisée à satisfaire,
• Sélectionner le premier prénom du premier tableau, soit la cellule D4,
• Enfoncer trois fois la touche F4 du clavier, ce qui donne : $D4,

Ainsi et pour les mêmes raisons que la condition précédente, nous libérons la cellule en ligne et la figeons en colonne.

• Fermer la parenthèse de cette seconde condition matricielle,

Les indications sont toujours présentes sur la droite de la boîte de dialogue, mais sous forme numérique cette fois. Le chiffre 1 signifie Vrai tandis que le chiffre 0 signifie Faux. Et ces chiffres confirment toujours que le premier client, issu du premier tableau, est toujours bien trouvé sur la première ligne du second tableau, malgré les conditions recoupées.

• A la suite de la syntaxe, taper de nouveau le symbole de l'étoile (*),
• Ouvrir une nouvelle parenthèse pour la dernière condition à recouper,
• Sélectionner tous les codes postaux du second tableau, soit la plage de cellules J4:J21,
• Enfoncer la touche F4 du clavier pour la figer complètement, ce qui donne : $J$4:$J$21,
• Taper le symbole égal (=) pour énoncer la condition à valider,
• Sélectionner le premier code postal du premier tableau, soit la cellule E4,
• Enfoncer trois fois la touche F4 du clavier, ce qui donne : $E4,
• Fermer la parenthèse de la dernière condition matricielle,
• Puis, valider la formule en cliquant sur le bouton Ok de la boîte de dialogue,
• Double cliquer sur la poignée du résultat pour reproduire la logique sur tout le tableau,

Comme vous le remarquez, la colonne des résultats affiche une succession de chiffres 1 et de chiffres 0. Et si vous consultez attentivement les correspondances, vous remarquez précisément que chaque ligne repérée par le chiffre 0 identifie un client absent dans la seconde source de données.

• En cellule E4, modifier le CP 06500 du premier client par le CP 06100,

Instantanément après validation, le chiffre 1 le repérant comme connu se transforme et l'identifie comme absent ou modifié. La formule que nous avons construite est la suivante :

=SOMMEPROD(($H$4:$H$21 = $C4)*($I$4:$I$21 = $D4)*($J$4:$J$21 = $E4))

Identifier dynamiquement les différences
C'est donc la syntaxe du calcul que nous venons de bâtir que nous devons exploiter comme règle de mise en forme conditionnelle. Pour repérer chaque enregistrement non concordant d'une source à une autre, nous devons vérifier qu'il renvoie bien la valeur 0. Il s'agit donc du critère à ajouter à la suite de la syntaxe qu'il convient de prélever.

• Sélectionne le premier calcul, soit la cellule A4,
• Dans sa barre de formule, sélectionner l'intégralité de la syntaxe,
• La copier par le raccourci clavier CTRL + C,
• Puis, sortir de la barre de formule par la touche Entrée du clavier,
• Sélectionner toutes les données du premier tableau, soit la plage de cellules B4:E21,
• 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 ...,
• Dans la zone de saisie du dessous, coller la syntaxe prélevée par le raccourci CTRL + V,
• Ajouter l'égalité du critère à satisfaire en bout de syntaxe, soit : =0,
• Cliquer alors 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 rouge foncé,
• Activer ensuite 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 en cliquant sur le bouton Ok de la boîte de dialogue,

Ainsi, nous sommes de retour sur la première boîte de dialogue. Elle indique explicitement, lorsqu'un enregistrement du premier tableau n'est pas strictement décelé dans le second, que la ligne du client doit être repérée dynamiquement sur fond rouge avec un texte blanc.

• Cliquer de nouveau sur le bouton Ok pour valider la règle de repérage dynamique,

De retour sur la feuille, vous constatez que tous les enregistrements ne concordant pas strictement, sont clairement et dynamiquement identifiés dans le premier tableau.



Nous devons produire une règle similaire sur la seconde source de données. Elle peut en effet très bien porter des enregistrements ayant été supprimés dans la première base. Ce sont donc désormais les informations recoupées de la deuxième base de données que nous devons rechercher dans la première.

• Sélectionner toutes les données du deuxième tableau, soit la plage de cellules G4:J21,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• 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 ...,
• Dans la zone de saisie juste en dessous, construire la règle suivante :

=SommeProd(($C$4:$C$21 = $H4)*($D$4:$D$21 = $I4)*($E$4:$E$21 = $J4))=0

• Comme précédemment, lui associer un fond rouge et un texte gris très clair,
• Enfin, valider la règle,

Désormais, nous repérons dynamiquement les enregistrements différents d'une source de données à l'autre, dans les deux sens.