Comment comparer 2 listes sur Excel et identifier les différences ?

Comparer 2 listes sur Excel : Méthodes et Astuces

Excel est un outil puissant pour la gestion de données, et la comparaison de listes est une tâche courante. Que vous ayez besoin d'identifier les doublons, les éléments manquants ou les différences entre deux ensembles de données, Excel propose plusieurs approches pour répondre à vos besoins. Explorons ensemble les méthodes les plus efficaces.

1. Utiliser la mise en forme conditionnelle pour identifier les doublons

La mise en forme conditionnelle est un moyen simple et rapide de mettre en évidence les doublons dans une liste ou entre deux listes. Voici comment procéder :

1. Sélectionnez les deux listes que vous souhaitez comparer. Si les listes sont dans des colonnes différentes, sélectionnez les deux colonnes.
2. Dans l'onglet Accueil, cliquez sur Mise en forme conditionnelle.
3. Sélectionnez Règles de mise en surbrillance des cellules puis Valeurs en double…
4. Dans la boîte de dialogue, choisissez le format que vous souhaitez appliquer aux doublons (par exemple, remplissage rouge clair avec texte rouge foncé) et cliquez sur OK.

Excel mettra automatiquement en surbrillance toutes les valeurs qui apparaissent à la fois dans la première liste et dans la deuxième liste. Vous pouvez ainsi identifier visuellement les éléments communs.

2. Utiliser la fonction SI et la fonction NB.SI pour identifier les éléments présents dans une liste mais pas dans l'autre

Cette méthode permet de créer une colonne supplémentaire qui indique si un élément de la première liste est présent ou non dans la deuxième liste. Nous utiliserons la fonction NB.SI pour compter le nombre d'occurrences d'un élément de la première liste dans la deuxième liste, puis la fonction SI pour afficher un message en fonction du résultat.

1. Supposons que votre première liste se trouve dans la colonne A (A1:A10) et votre deuxième liste dans la colonne B (B1:B15).
2. Dans la colonne C, à partir de la cellule C1, entrez la formule suivante : =SI(NB.SI(B$1:B$15;A1)>0;"Présent";"Absent")
3. Tirez la formule vers le bas pour l'appliquer à toutes les cellules de la colonne A.

Explication de la formule :

• NB.SI(B$1:B$15;A1) compte le nombre de fois que la valeur de la cellule A1 apparaît dans la plage B1:B15. Le symbole $ fige les références de ligne de la plage B1:B15 pour que la plage de recherche reste fixe lorsque vous tirez la formule vers le bas.
• SI(NB.SI(B$1:B$15;A1)>0;"Présent";"Absent") vérifie si le résultat de NB.SI est supérieur à 0. Si c'est le cas, cela signifie que la valeur de A1 est présente dans la liste B, et la formule affiche "Présent". Sinon, elle affiche "Absent".

Variante : Pour identifier les éléments présents dans la deuxième liste mais pas dans la première, vous pouvez inverser les colonnes A et B dans la formule.

3. Utiliser la fonction EQUIV pour identifier la position d'un élément dans une autre liste

La fonction EQUIV recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de cette valeur dans la plage. Si la valeur n'est pas trouvée, la fonction renvoie une erreur #N/A. On peut utiliser cette propriété pour identifier les éléments présents dans une liste et pas dans l'autre.

1. Supposons que votre première liste se trouve dans la colonne A (A1:A10) et votre deuxième liste dans la colonne B (B1:B15).
2. Dans la colonne C, à partir de la cellule C1, entrez la formule suivante : =SI(ESTNA(EQUIV(A1;B$1:B$15;0));"Absent";"Présent")
3. Tirez la formule vers le bas pour l'appliquer à toutes les cellules de la colonne A.

Explication de la formule :

• EQUIV(A1;B$1:B$15;0) recherche la valeur de la cellule A1 dans la plage B1:B15. Le 0 indique que la recherche doit être exacte.
• ESTNA(EQUIV(A1;B$1:B$15;0)) vérifie si la fonction EQUIV renvoie une erreur #N/A. Si c'est le cas, cela signifie que la valeur de A1 n'est pas présente dans la liste B, et la fonction ESTNA renvoie VRAI.
• SI(ESTNA(EQUIV(A1;B$1:B$15;0));"Absent";"Présent") vérifie si le résultat de ESTNA est VRAI. Si c'est le cas, la formule affiche "Absent". Sinon, elle affiche "Présent".

4. Utiliser Power Query pour comparer deux listes et trouver les différences

Power Query (également appelé "Obtenir et transformer des données") est un outil puissant intégré à Excel qui permet d'importer, de transformer et de combiner des données provenant de différentes sources. Il peut également être utilisé pour comparer deux listes et trouver les différences.

1. Importer les deux listes dans Power Query : Sélectionnez la première liste et allez dans l'onglet Données > Du tableau/plage. La liste sera importée dans l'éditeur Power Query. Répétez l'opération pour la deuxième liste. Nommez les requêtes de manière descriptive (par exemple, "Liste1" et "Liste2").
2. Combiner les requêtes : Dans l'éditeur Power Query, sélectionnez la requête "Liste1". Allez dans l'onglet Accueil > Combiner > Fusionner les requêtes. Dans la boîte de dialogue, sélectionnez "Liste2" et choisissez la colonne à comparer. Sélectionnez le type de jointure approprié. Pour trouver les éléments présents dans "Liste1" mais pas dans "Liste2", choisissez "Jointure gauche (Liste1)". Pour trouver les éléments présents dans "Liste2" mais pas dans "Liste1", choisissez "Jointure droite (Liste2)". Pour trouver les éléments communs, choisissez "Jointure interne". Pour trouver tous les éléments sauf les communs, choisissez "Jointure externe complète".
3. Développer la colonne combinée : Après la fusion, une nouvelle colonne contenant les données de "Liste2" sera ajoutée à "Liste1". Cliquez sur l'icône à double flèche dans l'en-tête de cette colonne et désélectionnez toutes les colonnes sauf celle qui contient la valeur à comparer. Cochez la case "Utiliser le nom de la colonne d'origine comme préfixe" si vous le souhaitez.
4. Filtrer les lignes : Selon le type de jointure que vous avez choisi, vous devrez peut-être filtrer les lignes pour identifier les différences. Par exemple, si vous avez choisi une jointure gauche et que vous souhaitez trouver les éléments présents dans "Liste1" mais pas dans "Liste2", vous pouvez filtrer la colonne combinée pour afficher uniquement les lignes où la valeur est null (vide).
5. Charger les résultats dans Excel : Une fois que vous avez transformé les données comme vous le souhaitez, cliquez sur Fermer et charger pour charger les résultats dans une nouvelle feuille de calcul Excel.

5. Utiliser une macro VBA pour comparer deux listes (méthode avancée)

Pour les utilisateurs plus avancés, une macro VBA (Visual Basic for Applications) peut offrir une solution personnalisée pour comparer deux listes. Cette méthode nécessite des connaissances en programmation VBA.

Exemple de code VBA :

Sub ComparerListes()
    Dim Liste1 As Range, Liste2 As Range, Cell As Range
    Dim Dico As Object
    Dim DerniereLigne1 As Long, DerniereLigne2 As Long

    ' Définir les plages de données
    DerniereLigne1 = ThisWorkbook.Sheets("Feuil1").Cells(Rows.Count, 1).End(xlUp).Row 'Colonne A
    DerniereLigne2 = ThisWorkbook.Sheets("Feuil1").Cells(Rows.Count, 2).End(xlUp).Row 'Colonne B
    Set Liste1 = ThisWorkbook.Sheets("Feuil1").Range("A1:A" & DerniereLigne1)
    Set Liste2 = ThisWorkbook.Sheets("Feuil1").Range("B1:B" & DerniereLigne2)

    ' Créer un dictionnaire pour stocker les éléments de la deuxième liste
    Set Dico = CreateObject("Scripting.Dictionary")
    For Each Cell In Liste2
        If Not Dico.Exists(Cell.Value) Then
            Dico.Add Cell.Value, 1
        End If
    Next Cell

    ' Parcourir la première liste et vérifier si les éléments existent dans la deuxième liste
    For Each Cell In Liste1
        If Not Dico.Exists(Cell.Value) Then
            ' L'élément n'existe pas dans la deuxième liste
            ThisWorkbook.Sheets("Feuil1").Cells(Cell.Row, 3).Value = "Absent"
        Else
            ' L'élément existe dans la deuxième liste
            ThisWorkbook.Sheets("Feuil1").Cells(Cell.Row, 3).Value = "Présent"
        End If
    Next Cell

    MsgBox "Comparaison terminée ! Les résultats sont dans la colonne C."
End Sub

Comment utiliser la macro :

1. Ouvrez l'éditeur VBA (Alt + F11).
2. Insérez un nouveau module (Insertion > Module).
3. Collez le code VBA dans le module.
4. Modifiez les noms des feuilles et les plages de données si nécessaire.
5. Exécutez la macro (F5).

Cette macro parcourt la première liste et vérifie si chaque élément existe dans la deuxième liste. Le résultat (Présent/Absent) est affiché dans la colonne C.

Conseils et bonnes pratiques pour comparer efficacement deux listes sur Excel

• Nettoyez vos données avant de comparer : Supprimez les espaces inutiles, corrigez les fautes d'orthographe et assurez-vous que les formats de données sont cohérents.
• Utilisez des noms de colonnes clairs et descriptifs : Cela facilitera la compréhension des formules et des résultats.
• Vérifiez les résultats : Après avoir utilisé une méthode de comparaison, prenez le temps de vérifier les résultats pour vous assurer qu'ils sont exacts.
• Choisissez la méthode la plus adaptée à vos besoins : La mise en forme conditionnelle est idéale pour une identification rapide des doublons, tandis que Power Query est plus adapté aux comparaisons complexes impliquant plusieurs colonnes ou sources de données.

Erreurs courantes à éviter lors de la comparaison de listes sur Excel

• Ignorer les différences de casse : Par défaut, Excel est insensible à la casse. Si vous devez tenir compte de la casse, utilisez les fonctions EXACT ou TROUVE.
• Oublier de figer les références de cellules : Lorsque vous utilisez des formules, assurez-vous de figer les références de cellules (avec le symbole $) pour éviter que les plages de données ne se décalent lorsque vous tirez la formule vers le bas.
• Ne pas tenir compte des espaces : Les espaces avant ou après une valeur peuvent empêcher Excel de les identifier comme des doublons. Utilisez la fonction SUPPRESPACE pour supprimer les espaces inutiles.
• Utiliser la mauvaise jointure dans Power Query : Choisir le mauvais type de jointure dans Power Query peut entraîner des résultats incorrects. Assurez-vous de comprendre les différents types de jointure et de choisir celui qui correspond à vos besoins.