Pour télécharger votre livre offert "7 techniques indispensables dans Excel", cliquez ici
Comment comparer deux tableaux Excel ?

Comparer 2 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 pouvoir 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 valeur 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 2 tableaux grâce à une macro Excel

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 2 exemples de mise en œuvre cette macro :

Extraction des différences

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

Dans les tableaux à comparer, j’ai ajouté des mises en forme conditionnelles pour mettre 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 généré automatiquement 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 ces 2 exemples :

Option Explicit
Option Base 1
Public Enum ComparisonType
    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 les différences entre les 2 tableaux à comparer ' Ce tableau est placé à partir de la cellule active ' Paramètres : les objets tableaux et les N° de colonnes à comparer, ' 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 ar1() ar1 = CompareListObjects(list1, 1, list2, 1, compMode) ' On affiche les éléments identiques ou différents rgInit.Value2 = "Valeur" ' en-tête de colonne rgInit.Offset(1, 0).Resize(UBound(ar1), 1).Value2 = _ Application.WorksheetFunction.Transpose(ar1) ' Si on cherche les différences, il faut comparer aussi dans l'autre sens ' c'est à dire en prenant le second tableau comme référence If compMode = xDifferences Then Dim ar2() ar2 = CompareListObjects(list2, 1, list1, 1, compMode) rgInit.Offset(0, 1).Value2 = "Tableau" ' en-tête de la seconde colonne rgInit.Offset(1, 1).Resize(UBound(ar1), 1).Value2 = 1 ' On affiche les éléments différents à la suite des précédents rgInit.Offset(UBound(ar1) + 1).Resize(UBound(ar2), 1).Value2 = _ Application.WorksheetFunction.Transpose(ar2) rgInit.Offset(UBound(ar1) + 1, 1).Resize(UBound(ar2), 1).Value2 = 2 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 .Name = "tabDifferences" .TableStyle = "TableStyleLight14" .ShowTotals = True .ListColumns(1).TotalsCalculation = xlTotalsCalculationCount If .ListColumns.Count > 1 Then .ListColumns(2).TotalsCalculation = xlTotalsCalculationNone End If .Sort.SortFields.Add2 Key:=Range("tabDifferences[[#Headers],[#Data],[Valeur]]") .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'objet Array 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) ' Déclaration des tableaux VBA (array) Dim ar1() ' Pour stocker les elts du premier tableau à comparer Dim ar2() ' Pour stocker les elts du second tableau à comparer Dim arMatch() ' Pour stocker les elts qui correspondent Dim arDif() ' Pour stocker les elts sans correpondance ' Initialisation des tableaux ar1 = list1.ListColumns(colIndex1).DataBodyRange.Value2 ar2 = list2.ListColumns(colIndex2).DataBodyRange.Value2 arMatch = Array() arDif = Array() 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 ReDim Preserve arMatch(UBound(arMatch) + 1) arMatch(UBound(arMatch)) = ar1(i, 1) match = True Exit For ' On s'arrête dès que l'élément a été trouvé End If Next j If Not match Then ReDim Preserve arDif(UBound(arDif) + 1) arDif(UBound(arDif)) = ar1(i, 1) End If Next i If compMode = xDifferences Then CompareListObjects = arDif Else CompareListObjects = arMatch 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 :
    1. Les noms des 2 tableaux à comparer
    1. Eventuellement les numéros des colonnes à comparer (par défaut 1)
    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.

Si vous avez aimé cet article, un like ou un partage me ferait très plaisir ;)
  •  
  •  
  •  
  •  
  •  

1
Poster un Commentaire

avatar
1500

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

  S’abonner  
plus récent plus ancien Le plus populaire
Notifier de
Valentine
Invité

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

Fermer le menu
sed tristique accumsan Donec ut Nullam Aenean libero ut ultricies lectus adipiscing