You are currently viewing Comment comparer deux tableaux Excel ?

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 :

IdLibellé
12Clémentine
16Kiwi
17Orange
20Pomme
IdLibellé
12Clémentine
16Kiwi
17Pêche
18Poire
20Pomme

Si l’on compare les tableaux uniquement sur la colonne Id (identifiant), alors il n’y a qu’une seule différence : l’élément d’id 18 présent dans le second tableau et pas dans le premier.

Comparer 2 tableaux selon une seule colonne consiste en fait à trouver les valeurs de cette colonne qui sont présentes dans un tableau et pas dans l’autre (dans les 2 sens).

Mais si l’on compare les 2 tableaux selon les 2 colonnes, alors il y a une différence de plus : l’item d’id 17 n’a pas le même libellé.

Comparer 2 tableaux sur plusieurs colonnes nécessite donc 2 choses de plus :

  • Déterminer la colonne qui sert d’identifiant
  • Trouver les éléments qui ont le même identifiant mais une ou plusieurs valeurs différentes dans les autres colonnes

La suite de cet article ne traite que la comparaison selon une seule colonne.

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 un 3ème tableau contenant uniquement les éléments communs ou différents
Cet article devrait aussi vous intéresser :  Maîtrisez la classe Collection en VBA

Nous allons voir comment traiter les différents cas de figure au travers de 3 exemples concrets.

NB/ Je vous rappelle que nous ne traitons dans cet article que la comparaison de 2 tableaux selon une seule colonne.

Exemple 1 : mettre en forme les différences

Prenons comme exemple les 2 tableaux de noms suivants :

2 tableaux Excel à comparer

Dans le premier, tous les noms présents aussi dans le second 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 :

Règle de mise en forme conditionnelle dans Excel

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.

Remarque : sélectionnez bien le tableau 1 en entier avant d’ajouter la mise en forme conditionnelle.

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 via le formulaire qui figure à la fin de cet article. Il vous aidera à maîtriser ces notions comme un pro.
  • Enfin, cette page de doc Microsoft vous en apprendra plus sur les différentes fonctions EST…

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 :

comparaison de 2 listes Excel

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.
Cet article devrait aussi vous intéresser :  Rechercher dans un tableau avec la fonction EQUIV d’EXCEL

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.
Filtrer les 2 tableaux Excel à comparer

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

Extraire les différences de 2 tableaux Excel avec une macro

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 qu’on va 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

Extraire les éléments communs de 2 tableaux Excel avec une macro

Cette fois, la macro extrait les éléments communs entre les 2 tableaux (ceux sur fond blanc).

Code 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/ Vous pouvez éventuellement le télécharger en cliquant sur l’icône en forme de flèche descendante dans le coin supérieur droit de la page qui s’ouvre, mais ce n’est pas nécessaire.

Pour tester le code, suivez les étapes suivantes :

  1. Faites un copier/coller du code dans un module VBA de votre classeur Excel au format xlsm
  2. Dans la fonction ComparerTableaux, modifiez les éléments suivants :
    • Les noms des 2 tableaux à comparer (« tabNoms1 » et « tabNoms2 » dans mon exemple)
    • Éventuellement les numéros des colonnes à comparer (par défaut 1)
    • Le mode de comparaison (xDifferences ou xMatches), selon que vous souhaitez extraire les éléments différents ou les éléments communs.
  3. Dans la feuille Excel, cliquez à l’endroit où vous souhaitez générer le tableau de résultat
  4. Appuyer sur ALT + F8 pour ouvrir la boîte de dialogue des macros
  5. 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.
  • Si vos tableaux comportent plusieurs colonnes, pensez à spécifier les numéros des colonnes que vous souhaitez comparer dans le code VBA (par défaut, ce sont les colonnes N°1).

En synthèse

Vous avez vu comment comparer deux listes ou tableaux Excel pour en faire ressortir les éléments communs, ou au contraire 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.

5 4 votes
Notez cet article
S’abonner
Notifier de
guest

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.

46 Commentaires
plus récent
plus ancien Le plus populaire
Commentaires en ligne
Voir tous les commentaires