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

Comparer 2 listes Excel : Techniques et méthodes éprouvées

Comparer deux listes dans Excel est une tâche courante qui peut s'avérer fastidieuse si elle est effectuée manuellement. Heureusement, Excel propose plusieurs méthodes pour automatiser ce processus. Nous allons explorer différentes techniques, allant des plus simples aux plus avancées, afin de répondre à tous vos besoins.

1. Comparaison simple avec la mise en forme conditionnelle

La mise en forme conditionnelle est une méthode rapide et visuelle pour identifier les éléments présents dans une liste et absents dans l'autre. Cette technique est particulièrement utile lorsque vous avez des listes relativement courtes.

Étapes :

1. Sélectionnez la première liste (par exemple, la liste A).
2. Allez dans l'onglet Accueil, puis cliquez sur Mise en forme conditionnelle.
3. Choisissez Nouvelle règle...
4. Sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué.
5. Entrez la formule suivante : =NB.SI(B:B;A1)=0 (remplacez B:B par la plage de cellules correspondant à la deuxième liste et A1 par la première cellule de la première liste).
6. Cliquez sur Format... et choisissez un format de remplissage (par exemple, rouge) pour mettre en évidence les éléments présents dans la liste A mais absents dans la liste B.
7. Cliquez sur OK à deux reprises.
8. Répétez les étapes en inversant les listes pour identifier les éléments présents dans la liste B mais absents dans la liste A. La formule deviendra alors: =NB.SI(A:A;B1)=0

Explication de la formule:

• NB.SI(B:B;A1) : Cette partie de la formule compte le nombre de fois que la valeur de la cellule A1 apparaît dans la plage B:B. Si le résultat est 0, cela signifie que la valeur n'est pas présente dans la liste B.
• =0 : Cette condition vérifie si le résultat de NB.SI est égal à 0. Si c'est le cas, la mise en forme conditionnelle est appliquée à la cellule A1.

Avantages:

• Simple et rapide à mettre en œuvre.
• Visuelle : permet d'identifier rapidement les différences.

Inconvénients:

• Moins efficace pour les grandes listes.
• Ne permet pas d'identifier les doublons.

2. Utilisation de la fonction RECHERCHEV

La fonction RECHERCHEV est une autre méthode efficace pour comparer deux listes Excel. Elle permet de rechercher une valeur dans une plage de cellules et de renvoyer une valeur correspondante.

Étapes :

1. Dans une colonne adjacente à la première liste (par exemple, la colonne C), entrez la formule suivante : =RECHERCHEV(A1;B:B;1;FAUX) (remplacez A1 par la première cellule de la première liste et B:B par la plage de cellules correspondant à la deuxième liste).
2. Tirez la formule vers le bas pour l'appliquer à toutes les cellules de la première liste.
3. Si la fonction RECHERCHEV renvoie #N/A, cela signifie que la valeur n'est pas présente dans la deuxième liste. Sinon, elle renvoie la valeur correspondante.
4. Vous pouvez utiliser la fonction ESTERREUR pour identifier plus facilement les erreurs #N/A. Dans une autre colonne (par exemple, la colonne D), entrez la formule suivante : =ESTERREUR(C1) (remplacez C1 par la première cellule de la colonne où vous avez utilisé RECHERCHEV).
5. Si la fonction ESTERREUR renvoie VRAI, cela signifie que la valeur n'est pas présente dans la deuxième liste.

Explication de la formule:

• RECHERCHEV(A1;B:B;1;FAUX) : Cette fonction recherche la valeur de la cellule A1 dans la plage B:B. Le 1 indique que l'on souhaite renvoyer la valeur de la première colonne de la plage B:B (dans ce cas, la valeur elle-même). FAUX indique que l'on souhaite une correspondance exacte.
• ESTERREUR(C1) : Cette fonction vérifie si la cellule C1 contient une erreur. Si c'est le cas, elle renvoie VRAI.

Avantages:

• Permet d'identifier les éléments manquants dans une liste.
• Peut être utilisée pour rechercher des informations associées à chaque élément.

Inconvénients:

• Peut être plus complexe à mettre en œuvre que la mise en forme conditionnelle.
• Nécessite une compréhension de la fonction RECHERCHEV.

3. Comparaison avancée avec les fonctions INDEX et EQUIV

Les fonctions INDEX et EQUIV combinées offrent une alternative puissante à RECHERCHEV. Elles peuvent être plus performantes et plus flexibles dans certaines situations.

Étapes :

1. Dans une colonne adjacente à la première liste (par exemple, la colonne C), entrez la formule suivante : =INDEX(B:B;EQUIV(A1;B:B;0)) (remplacez A1 par la première cellule de la première liste et B:B par la plage de cellules correspondant à la deuxième liste).
2. Tirez la formule vers le bas pour l'appliquer à toutes les cellules de la première liste.
3. Comme avec RECHERCHEV, si la formule renvoie #N/A, cela signifie que la valeur n'est pas présente dans la deuxième liste. Utilisez ESTERREUR pour identifier facilement ces erreurs.

Explication de la formule:

• EQUIV(A1;B:B;0) : Cette fonction recherche la position de la valeur de la cellule A1 dans la plage B:B. Le 0 indique que l'on souhaite une correspondance exacte.
• INDEX(B:B;EQUIV(A1;B:B;0)) : Cette fonction renvoie la valeur de la cellule située à la position renvoyée par EQUIV dans la plage B:B.

Avantages:

• Plus flexible que RECHERCHEV : peut être utilisée pour rechercher des valeurs dans différentes colonnes.
• Peut être plus performante pour les grandes listes.

Inconvénients:

• Plus complexe à comprendre que RECHERCHEV.

4. Identifier les doublons dans une liste

Il est souvent nécessaire d'identifier les doublons dans une liste avant de la comparer à une autre. Excel propose plusieurs méthodes pour cela.

a) Mise en forme conditionnelle pour les doublons

1. Sélectionnez la liste dans laquelle vous souhaitez identifier les doublons.
2. Allez dans l'onglet Accueil, puis cliquez sur Mise en forme conditionnelle.
3. Choisissez Règles de mise en surbrillance des cellules, puis Valeurs en double...
4. Choisissez un format de remplissage pour mettre en évidence les doublons.
5. Cliquez sur OK.

b) Supprimer les doublons

1. Sélectionnez la liste dans laquelle vous souhaitez supprimer les doublons.
2. Allez dans l'onglet Données, puis cliquez sur Supprimer les doublons.
3. Sélectionnez les colonnes à prendre en compte pour identifier les doublons.
4. Cliquez sur OK.

Attention : La suppression des doublons est une action irréversible. Assurez-vous de sauvegarder votre feuille de calcul avant de procéder.

5. Utiliser Power Query pour des comparaisons complexes

Power Query, intégré à Excel, est un outil puissant pour l'importation, la transformation et l'analyse de données. Il peut être utilisé pour comparer deux listes de manière très flexible, notamment en cas de critères de correspondance complexes.

Étapes :

1. Importer les deux listes dans Power Query : sélectionnez chaque liste, puis allez dans l'onglet Données et cliquez sur Du tableau/plage.
2. Fusionner les requêtes : Dans l'éditeur Power Query, sélectionnez une des requêtes, puis cliquez sur Fusionner les requêtes (sous l'onglet Accueil ou Combiner).
3. Choisir la deuxième requête et les colonnes de correspondance pour la fusion.
4. Choisir le type de jointure : Par exemple, une jointure gauche externe pour conserver toutes les lignes de la première liste et les correspondances de la deuxième.
5. Développer les colonnes de la deuxième requête fusionnée pour afficher les informations correspondantes.
6. Filtrer les résultats pour identifier les lignes sans correspondance (valeurs nulles dans les colonnes développées).

Avantages:

• Extrêmement flexible : permet de gérer des critères de correspondance complexes.
• Peut gérer de très grandes listes.
• Permet d'automatiser le processus de comparaison.

Inconvénients:

• Nécessite une connaissance de Power Query.
• Plus complexe à mettre en œuvre que les méthodes précédentes.

6. Conseils et astuces pour une comparaison efficace

• Nettoyez vos données avant de commencer la comparaison. Supprimez les espaces inutiles, corrigez les erreurs de frappe et assurez-vous que les formats de données sont cohérents.
• Utilisez des noms de plages pour faciliter la lecture et la maintenance de vos formules.
• Sauvegardez votre feuille de calcul avant de procéder à des modifications importantes.
• Commentez vos formules pour vous rappeler de leur fonctionnement et faciliter la collaboration.
• Testez vos formules sur un petit échantillon de données avant de les appliquer à l'ensemble de vos listes.

Conclusion

Comparer deux listes Excel peut sembler une tâche ardue, mais avec les bonnes techniques et formules, vous pouvez automatiser ce processus et gagner un temps précieux. Que vous optiez pour la simplicité de la mise en forme conditionnelle, la puissance de RECHERCHEV ou la flexibilité de Power Query, Excel vous offre les outils nécessaires pour identifier les différences, les doublons et les éléments manquants dans vos données. N'hésitez pas à expérimenter avec les différentes méthodes présentées dans cet article pour trouver celle qui convient le mieux à vos besoins et à votre niveau de compétence. En suivant les conseils et astuces, vous serez en mesure de comparer efficacement vos listes Excel et d'optimiser votre gestion de données.