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

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.

Cet article devrait aussi vous intéresser :  Power BI Desktop vs Excel + Power Pivot : comment choisir ?

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.

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.

Cet article devrait aussi vous intéresser :  15 raccourcis clavier et souris hyper efficaces pour Excel et Calc

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.

44 Commentaires
plus récent
plus ancien Le plus populaire
Commentaires en ligne
Voir tous les commentaires
Kazaar
Kazaar
01/06/2021 18:39

Bonjour,

Merci pour cette macro !

Selon vous, est-il possible de pouvoir comparer les deux tableaux, et faire apparaître dans le 3ème tableau les lignes entières qui ne sont pas des doublons ? Je n’ai pas réussis à trouver la solution…

Merci beaucoup pour votre aide

Tiger
Tiger
18/05/2021 07:58

Bonjour,

Les erreurs de syntaxes à la ligne 39 sont toujours présentes. J’ai vu qu’il s’agissait de la disparition des zéros dû à la MaJ du thème. Il y aurait moyen d’avoir le code corrigé ?

Merci d’avance

Tiger

Quentin
Quentin
21/04/2021 12:22

Bonjour, merci pour cette macro très utile. Sauriez-vous pourquoi ma macro ne s’exécute pas entièrement?

Quentin
Quentin
21/04/2021 12:24
Répondre à  Quentin

désolé, voici ma capture d’écran

Capture.PNG
Kalunna
Kalunna
15/04/2021 13:00

Bonjour, merci pour cette macro très utile, très bien expliquée. Sauriez-vous s’il est possible de comparer 2 tableaux possédant chacun 2 colonnes ; la comparaison ne pourrait s’effectuer que sur les colonnes 1, mais dans le résultat, je voudrais récupérer les valeurs de la colonne 2 (colonne 1 = identifiant numérique // colonne 2 = une url)
Merci d’avance

Safouane
Safouane
31/03/2021 10:07

je n’arrive pas exécuter la macro, le message suivant s’affiche :

Kamale
Kamale
30/03/2021 11:58

Bonjour,
Merci pour toutes ces infos. J’ai tenté de reproduire l’utilisation de la macro avec le même type de jeux de données (2 tableaux simples avec les mêmes noms de tableau que dans le script). Au lancement de la macro, j’ai l’erreur suivante en pj.

Pourriez-vous m’aider svp?

script vba.PNG
Larroque
Larroque
12/03/2021 20:58

Bonsoir,
J’ai moi aussi un problème dès le début de la macro avec le “BuildComparisonResult ActiveSheet.ListObjects” qui me donne une Run-time error 9: Subscript out of range. j’ai ajusté le nom des tableau mais rien n’y fait….

Mike
Mike
07/01/2021 14:51

Bonjour,

Les deux tableaux que je désire comparer se trouvent dans deux feuilles différentes et j’aimerai que le tableau de comparaison s’affiche sur une autre page mais je ne parviens pas à adapter cela au code, Pouvez vous m’aider ?

Dernière modification 6 mois il y a by Mike
Clément
Clément
14/12/2020 17:26

Bonjour,

Merci pour ce support article et surtout cette macro !

Cependant , j’aurais aimé l’adapter pour contrôler l’entièrreté de mes deux tableaux, pour ensuite créer un troisième tableau ou est indiqué les lignes qui ont été modifiées.

Est-ce que cela est réalisable ?

trackback

[…] L’exemple ci-dessus montre une interface visuelle que j’ai créée pour la comparaison de tableaux de données à l’intérieur d’une feuille, selon la technique décrite dans cet article. […]

Gauthier
Gauthier
03/12/2020 09:27

Bonjour,

Je reviens vers votre macro car elle m’intéresse pour un projet.
Malheureusement, je bloque sur le point suivant, une erreur de synthaxe à la ligne 39… Une idée ?
cf capture d’écran

Merc d’avance !

Gauthier
Gauthier
03/12/2020 12:23
Répondre à  Cyril

ça fonctionne ?

Capture.PNG
Gauthier
Gauthier
04/12/2020 07:14
Répondre à  Cyril

Incroyable ! Merci beaucoup pour votre réactivité et votre investissement !

salma
salma
06/05/2021 07:55
Répondre à  Cyril

j ai le même prob comment je peux le corriger

Gian
Gian
23/10/2020 15:42

Bonjour,

J’aimerais savoir si votre code peut s’adapter pour comparer plus de 2 Tableaux ?
En effet, j’extrait des données et une colonne correspond à un tableau. Je peux avoir plus de 2 colonnes à comparer.

Odrey
Odrey
25/06/2020 14:23

Bonjour, j’aimerai savoir quelle formule vous utilisez pour l’exemple 2 (les vrai / faux).
Votre tutoriel est très clair dans son ensemble. Merci pour le partage de connaissances.

Odrey
Odrey
25/08/2020 15:29
Répondre à  Cyril

Bonjour, je reprends mon travail sur un tableau de comparaison, et je n’ai pas saisi la formule à intégrer dans la colonne “Présent nomtab2” (dans le 1er tableau)

Watters Véronique
Watters Véronique
22/06/2020 17:07

Bon quelque chose que je pige pas! Je mets la formule du premier tableau dans une nouvelle r`gle de =ESTNUM(EQUIV($A2;INDIRECT(“tabNoms2[Nom]”);0)). je formate couleur bleue . Mais la couleur disparaît en sélectionnant le 2eme tableau, normal docteur ? Je persiste et signe la même procédure pour le deuxième tableau, avec une couleur différente, mais cette fois avec :=ESTNUM(EQUIV($C2;INDIRECT(“tabNoms1[Nom]”);0)) Quand je sélectionne le deuxième tableau et même résultat, pas de couleur et pas comaparaison…. qu’est-ce que je n’ai pas compris ? Merci Cyril!

Watters Véronique
Watters Véronique
21/06/2020 14:11

Bonjour
Très intéressant! Est-ce possible de comparer des chiffres au lieu de noms?
Je compare des numéros de réservations.
Merci pour votre aide!

Vince
Vince
12/06/2020 07:58

Bonjour,
 
C’est vraiment très intéressant et très bien expliqué !
Cependant impossible de faire fonctionner cette macro malheureusement.
VBA m’indique : type défini par l’utilisateur non défini

lea
lea
19/06/2020 07:49
Répondre à  Cyril

bonjour,
je n’arrivais pas à créer de le 3eme tableau
il y a un souci avec le nom des tableau

BuildComparisonResult ActiveSheet.ListObjects(” TK”), 1, _
                   ActiveSheet.ListObjects(” CCH”), 1, _
                   xDifferences, ActiveCell

Léa
Léa
19/06/2020 15:41
Répondre à  Cyril

J’ai bien mis les noms de mes deux TableauX « TK » et « CCH » mais ça ne fonctionne pas . La macro n’arrive pas S’exécuter.
j’ai mis le nom des tableaux en haut de chaque tableau .
et sur la macro entre les parenthèses et les guillemets.
merci pour votre aide

Léa
Léa
19/06/2020 19:40
Répondre à  Cyril

Super merci encore

Corentin
Corentin
19/01/2021 16:29
Répondre à  Cyril

Bonjour et merci pour votre travail. Je ne sais pas si c’est mon navigateur qui déconne, mais je ne vois pas de ligne surligné en jaune, pouvez-vous m’indiquer peut être un numéro de ligne ou un autre moyen de la trouver ?

Valentine
24/09/2019 15:07

Merci pour cet article clé en main ; ça aide bien. Les captures d’écran, pas à pas, sont bien utiles. Merci