Comment comparer deux tableaux Excel ?

Comparer deux tableaux Excel consiste à faire ressortir soit leurs éléments communs, soit leurs différences.

Le résultat peut être mise en évidence par :

  • Des couleurs
  • Une colonne supplémentaire de type VRAI / FAUX
  • La création d’un 3ème tableau contenant uniquement les éléments communs ou différents

Nous allons voir ces 3 techniques dans les exemples qui suivent.

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.

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

Voici le code VBA de la macro utilisée dans les exemples précédents.
NB/ Ce code a été mis à jour le 19/06/20 pour optimiser les performances et mieux gérer les cas où il n’y a aucun résultat à afficher.

Option Explicit
Option Base 1
Public Enum ComparisonMode
    xDifferences = 1
    xMatches = 2
End Enum

Public Sub ComparerTableaux()
BuildComparisonResult ActiveSheet.ListObjects("tabNoms1"), 1, _ ActiveSheet.ListObjects("tabNoms2"), 1, _ xDifferences, ActiveCell
End Sub ' Crée un 3ème tableau contenant le résultat de la comparaison des 2 tableaux ' La première cellule de ce tableau est la cellule active de la feuille ' Paramètres : ' - les objets tableaux et les N° de colonnes à comparer, ' - le mode de comparaison (différences ou similitudes) ' - la cellules à partir de laquelle créer le tableau de résultat Public Sub BuildComparisonResult(list1 As ListObject, colIndex1 As Integer, _ list2 As ListObject, colIndex2 As Integer, _ compMode As ComparisonMode, _ rgInit As Range) ' On compare les tableaux en prenant le premier comme référence Dim coll1 As Collection Set coll1 = CompareListObjects(list1, 1, list2, 1, compMode) ' Si on cherche les différences, il faut aussi comparer dans l'autre sens ' c'est à dire en prenant le second tableau comme référence If compMode = xDifferences Then Dim coll2 As Collection Set coll2 = CompareListObjects(list2, 1, list1, 1, compMode) End If ' S'il n'y a aucun résultat à afficher, on affiche un message et on ne va pas plus loin If compMode = xMatches Then If coll1.Count = Then MsgBox "Il n'y a aucune valeur commune aux 2 tableaux", vbInformation Exit Sub End If Else If coll1.Count = And coll2.Count = Then MsgBox "Les 2 tableaux sont identiques", vbInformation Exit Sub End If End If ' en-tête de la première colonne du tableau de résultat If compMode = xMatches Then rgInit.Value2 = "Valeurs communes" Else rgInit.Value2 = "Différences" End If ' On affiche les résultats de la première comparaison Dim i As Long For i = 1 To coll1.Count rgInit.Offset(i, ).Value2 = coll1(i) Next i ' Si on cherche les différences, on affiche les résultats de la seconde comparaison If compMode = xDifferences Then rgInit.Offset(, 1).Value2 = "Tableau" ' en-tête de la seconde colonne If coll1.Count > Then rgInit.Offset(1, 1).Resize(coll1.Count, 1).Value2 = 1 For i = 1 To coll2.Count rgInit.Offset(coll1.Count + i, ).Value2 = coll2(i) Next i If coll2.Count > Then rgInit.Offset(coll1.Count + 1, 1).Resize(coll2.Count, 1).Value2 = 2 End If End If ' On crée un objet tableau à partir des résultats générés précédemment ' et on le trie selon la première colonne Dim lstObj As ListObject Set lstObj = ActiveSheet.ListObjects.Add(xlSrcRange, rgInit.CurrentRegion, , xlYes) With lstObj .TableStyle = "TableStyleLight14" .ShowTotals = True .ListColumns(1).TotalsCalculation = xlTotalsCalculationCount If .ListColumns.Count > 1 Then .ListColumns(2).TotalsCalculation = xlTotalsCalculationNone End If .Sort.SortFields.Add2 Key:=.ListColumns(1).DataBodyRange .Sort.Apply End With End Sub ' Compare 2 tableaux selon les colonnes choisies, en prenant le 1er comme référence ' Renvoie le résultat sous forme d'une collection contenant : ' - Si compType = xDifferences : les éléments présents dans le premier tableau ' et pas dans le second ' - Si compType = xMatches : les éléments présents dans les 2 tableaux Private Function CompareListObjects(list1 As ListObject, colIndex1 As Integer, _ list2 As ListObject, colIndex2 As Integer, _ compMode As ComparisonMode) Dim ar1() ' Pour stocker les elts du premier tableau à comparer Dim ar2() ' Pour stocker les elts du second tableau à comparer ' On récupère les valeurs des tableaux à comparer ar1 = list1.ListColumns(colIndex1).DataBodyRange.Value2 ar2 = list2.ListColumns(colIndex2).DataBodyRange.Value2 Dim collMatch As New Collection ' Pour stocker les elts qui correspondent Dim collDif As New Collection ' Pour stocker les elts sans correpondance Dim i As Long Dim j As Long Dim match As Boolean For i = 1 To UBound(ar1) match = False For j = 1 To UBound(ar2) If ar1(i, 1) = ar2(j, 1) Then collMatch.Add ar1(i, 1) match = True Exit For ' On arrête dès que l'élément a été trouvé End If Next j If Not match Then collDif.Add ar1(i, 1) End If Next i If compMode = xDifferences Then Set CompareListObjects = collDif Else Set CompareListObjects = collMatch End If End Function

Pour l’essayer, suivez les étapes suivantes :

  1. Faites un copier/coller de ce code dans un module VBA de votre classeur Excel au format xlsm
  2. Dans la ligne surlignée en jaune, modifiez :
    • Les noms des 2 tableaux à comparer
    • É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 2 votes
Notez cet article
Si vous avez aimé cet article, un like et un partage me feraient très plaisir ;)
  •   
  •   
  •  
  •   
  •   
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.

16 Commentaires
plus récent
plus ancien Le plus populaire
Commentaires en ligne
Voir tous les commentaires
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.

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

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