Comparer deux tableaux Excel consiste à faire ressortir soit leurs éléments communs, soit leurs différences.
Les questions à se poser
Mais avant de commencer, il est important de bien déterminer ce que vous voulez faire. Pour cela, je vous invite à répondre à 3 questions :
Question 1 : voulez-vous comparer vos 2 tableaux Excel selon une ou plusieurs colonnes ?
Pour clarifier, considérons les 2 tableaux ci-dessous :
| Nom | Prénom |
|---|---|
| BAUDOIN | Clément |
| BAUDRY | Céline |
| BAZIN | Jérémy |
| BEAUMONT | Eliane |
| Nom | Prénom |
|---|---|
| BARTOLI | Chloé |
| BAUDOIN | Clément |
| BAUDRY | Céline |
| BAZIN | Eric |
| BEAUMONT | Eliane |
Si l’on compare les 2 tableaux uniquement sur la colonne « Nom », il n’y a qu’une seule différence : le nom « BARTOLI » est présent dans le second tableau, mais pas dans le premier.
Mais si l’on compare les 2 tableaux selon les 2 colonnes, il y a une différence de plus : le prénom de M. Bazin est différent dans chaque tableau.
De façon générale, comparer 2 tableaux selon X colonnes consiste à :
- Parcourir toutes les lignes du premier tableau et vérifier pour chaque ligne si le second tableau contient une ligne semblable, c’est-à-dire ayant exactement les mêmes valeurs dans les X colonnes (sans s’occuper des éventuelles colonnes supplémentaires).
- Refaire la même chose dans l’autre sens, c’est-à-dire en partant du second tableau.
Question 2 : cherchez-vous les similitudes ou bien les différences ?
Autrement dit, voulez-vous faire ressortir les éléments présents dans les 2 tableaux, ou au contraire les éléments présents dans un tableau et pas dans l’autre ?
Question 3 : Comment voulez-vous mettre en évidence les similitudes ou les différences ?
Il y a 3 façons de présenter le résultat de la comparaison :
- En utilisant des couleurs ou autres mises en forme
- En ajoutant une colonne de type VRAI / FAUX à chaque tableau, pour indiquer si chaque élément est également présent dans l’autre tableau. Vous pourrez ensuite filtrer les tableaux selon cette colonne pour plus de lisibilité.
- Générer des tableaux de résultats contenant uniquement les éléments communs ou différents
Nous allons voir maintenant des exemples concrets illustrant ces 3 cas de figure (avec 2 techniques différentes pour le dernier cas).
Exemple 1 : mettre en forme les différences
Prenons comme exemple les 2 tableaux de noms suivants :
Dans le premier tableau, tous les noms présents aussi dans le second tableau sont colorés en bleu.
Dans le second, tous les noms présents aussi dans le premier sont colorés en verts.
Les noms en blanc représentent donc les différences entre les 2 tableaux.
Pour faire cela, vous devez créer des mises en forme conditionnelles basées sur des formules.
Voici celle utilisée pour le premier tableau :
La formule est :
=ESTNUM(EQUIV($A2;INDIRECT("tabNoms2[Nom]");0))
Explications
INDIRECT permet de faire référence à la colonne Nom du second tableau, nommé tabNoms2.
EQUIV permet de rechercher dans tabNoms2 le numéro de la ligne qui contient la valeur de la cellule A2, c’est-à-dire la première cellule du tableau 1. La formule étant appliquée au tableau entier, Excel incrémentera automatiquement la référence $A2 en $A3, $A4… pour les autres lignes.
Sélectionnez bien le tableau 1 en entier avant d’ajouter la mise en forme conditionnelle pour qu’elle s’applique à toutes les lignes !
EQUIV renvoie une erreur #N/A lorsqu’aucune correspondance n’est trouvée. La fonction ESTNUM renvoie VRAI si son paramètre est une valeur numérique. Dans notre cas, elle renverra donc VRAI uniquement si une correspondance dans le second tableau a été trouvée.
La couleur de fond bleue choisie ici est alors appliquée à toutes les cellules pour lesquelles la formule renvoie VRAI.
Sur le même principe, voici la formule utilisée pour la mise en forme conditionnelle du second tableau :
=ESTNUM(EQUIV($C2;INDIRECT("tabNoms1[Nom]");0))
Si vous préférez inverser les couleurs, c’est-à-dire colorer les éléments différents, et laisser en blanc les éléments identiques, il vous suffit de remplacer ESTNUM par ESTNA. Cette fonction renvoie VRAI quand la valeur en paramètre est #N/A.
Remarques
- Pour en savoir plus sur la fonction EQUIV, n’hésitez pas jeter un œil ici.
- Si les notions de tableau nommé et de mise en forme conditionnelle ne vous sont pas familières, n’hésitez pas à demander mon ebook gratuit en cliquant sur le bouton dans la barre qui figure tout en haut de la page. Il vous aidera à maîtriser ces notions rapidement.
- Enfin, cette page de doc Microsoft vous en apprendra plus sur les différentes fonctions EST…
Comparaison selon plusieurs colonnes
Pour comparer deux tableaux Excel selon plusieurs colonnes avec cette technique :
- Créez une colonne supplémentaire dans chaque tableau, qui rassemble les valeurs des colonnes à comparer.
- Remplissez cette colonne supplémentaire à l’aide de la formule ci-dessous, où NomColonneX et NomColonneY sont les noms de la première et de la dernière colonne à comparer.
- Puis utilisez cette colonne calculée dans la formule de la mise en forme conditionnelle que nous venons de voir plus haut.
=CONCAT([@NomColonneX]:[@NomColonneY])
Exemple 2 : filtrer les éléments différents
Peut-être préférez-vous filtrer les tableaux sur les éléments communs ou différents. Dans ce cas, vous devez ajouter une colonne dans chaque tableau, qui indique VRAI ou FAUX selon que l’élément est présent ou non dans l’autre tableau, comme ceci :
Une fois cette colonne ajoutée, il vous suffit de filtrer le tableau sur la valeur VRAI ou FAUX grâce au filtre automatique dans l’en-tête de colonne.
Cette technique présente 2 avantages :
- Après filtrage, vous pouvez copier facilement les éléments différents en vue de constituer par exemple un 3ème tableau.
- Vous pouvez ajouter un compteur pour les éléments communs ou différents dans la ligne de total.
La formule utilisée pour compter le nombre de valeurs VRAI dans le premier tableau est :
=NB.SI([Présent tab2];VRAI)
Remarques :
- Si vous souhaitez filtrer simultanément les 2 tableaux, vous devez les placer l’un en dessous de l’autre, comme dans la figure ci-dessous. Sinon, vous ne pourrez pas voir tous les éléments.
- Rien ne vous empêche de garder les mises en forme conditionnelles pour bien faire ressortir les différences.
Exemple 3 : Comparer deux tableaux Excel grâce à une macro
Dans certains cas, il est intéressant d’avoir le résultat de la comparaison des 2 tableaux sous forme d’un troisième tableau contenant uniquement les éléments similaires ou différents.
On peut automatiser la construction de ce tableau grâce à une macro VBA.
Voici tout d’abord 2 exemples illustrant le résultat souhaité :
Extraction des différences
Dans les tableaux à comparer, j’ai mis sur fond gris les différences, c’est-à-dire les éléments qui ne sont pas présents dans l’autre tableau. C’est juste pour les faire ressortir, afin que vous constatiez que le tableau que nous allons générer avec la macro contient bien uniquement ces éléments. La colonne « Tableau » permet de savoir lequel des 2 tableaux contient l’élément orphelin.
Plutôt sympathique, non ?
Extraction des éléments communs
Cette fois, la macro extrait les éléments communs entre les 2 tableaux (ceux sur fond blanc).
Code et mise en œuvre de la macro
Pour voir le code VBA de la macro utilisée dans les exemples précédents, cliquez sur le bouton ci-dessous :
NB/ Cette macro permet de comparer deux tableaux Excel selon une ou plusieurs colonnes adjacentes.
Pour mettre en œuvre cette macro dans votre propre classeur, suivez les étapes suivantes :
- Faites un copier/coller du code dans un module VBA de votre classeur Excel au format xlsm
- Dans la fonction ComparerTableaux, modifiez les éléments suivants :
- Les noms des feuilles contenant les 2 tableaux à comparer
- Les noms des 2 tableaux, en reprenant ceux que vous avez spécifiés dans la zone « Nom du tableau » de l’onglet contextuel « Création de tableau » (cf. image ci-dessous)
- Les numéros de la première et dernière colonne de la plage de colonnes à comparer (mettez des valeurs identiques pour comparer selon une seule colonne)
- Le mode de comparaison (xDifferences ou xMatches), selon que vous souhaitez extraire les éléments différents ou les éléments communs.
- Dans la feuille Excel, cliquez à l’endroit où vous souhaitez générer le tableau de résultat
- Appuyer sur ALT + F8 pour ouvrir la boîte de dialogue des macros
- Sélectionnez la macro « ComparerTableaux » et cliquez sur Exécuter
Remarques
- Si vous modifiez l’un des 2 tableaux à comparer, il faut supprimer le tableau de résultat de comparaison et le recréer.
- Pour spécifier le nom d’un tableau, placez le curseur n’importe où dedans pour que l’onglet « Création de tableau » s’affiche, puis saisissez le nom dans la zone suivante et validez par Entrée :
Exemple 4 : Comparer deux tableaux Excel avec Power Query
Power Query et un complément d’Excel très puissant pour charger et transformer des données. Il est intégré par défaut à Excel depuis sa version 2016.
Avec Power Query, vous pourrez générer facilement 3 tableaux de résultats contenant respectivement les éléments communs et les éléments spécifiques à l’un ou l’autre des tableaux à comparer. C’est sans doute la technique la plus puissante de toutes !
Utiliser Power Query présente en effets de gros avantages :
- Aucune formule Excel à écrire. Donc si vous n’êtes pas à l’aise avec les formules, ce n’est pas un problème.
- Pas besoin de macros, ce qui est un gage de sécurité. D’ailleurs depuis une mise à jour d’Office d’Octobre 2022, toutes les macros sont désormais désactivées par défaut. Ceci afin de lutter contre les problèmes de sécurité qu’elles génèrent, surtout en entreprise.
- Power Query vous permet de transformer au préalable les données de vos tableaux si besoin (ex : séparer ou regrouper des colonnes, transformer des colonnes en dates, prix…etc.). Ainsi, si vous recevez des données brutes, vous pourrez facilement les mettre en forme avant de les comparer. Et ce, avec une interface conviviale, sans avoir à écrire de formule.
- Après génération des tableaux de résultats de comparaison, si vos données d’origine changent, vous pourrez mettre à jour les résultats en un clic.
Cette technique pourrait faire l’objet d’un article à part entière. Mais j’ai préféré vous la présenter en vidéo pour plus de simplicité. La vidéo ci-dessous vous fera donc découvrir Power Query, et vous expliquera en détails comment comparer 2 tableaux Excel.
En synthèse
Vous avez vu comment comparer deux tableaux Excel pour en faire ressortir les éléments communs ou les différences, puis afficher le résultat de différentes manières.
Ces techniques fonctionnent même si les tableaux à comparer ne sont pas dans la même feuille, et vous pouvez également déplacer les tableaux sans problème.
Avez-vous essayé ces techniques ? Qu’en pensez-vous ? Partagez en commentaire.
Laisser un commentaire