Comment comparer deux listes dans Excel : un guide pratique

Préparez vos listes pour la comparaison

Avant d'appliquer une formule de comparaison, assurez-vous que les deux listes sont propres et formatées de manière cohérente. Les espaces supplémentaires, les apostrophes en début ou fin de cellule et les caractères non imprimables peuvent entraîner de fausses discordances. Les fonctions TRIM et CLEAN d'Excel suppriment la plupart de ces problèmes.

Si vos listes contiennent des doublons au sein de la même colonne, décidez si vous souhaitez comparer chaque instance ou uniquement les valeurs uniques. La suppression préalable des doublons internes évite souvent des résultats trompeurs, en particulier lors du comptage des correspondances. Utilisez la fonctionnalité Supprimer les doublons ou une colonne d'aide avec COUNTIF pour signaler les doublons avant la comparaison croisée des listes.

1. Copiez chaque liste dans sa propre colonne (par exemple, Liste1 dans la colonne A, Liste2 dans la colonne B) à partir de la ligne 1.
2. Sélectionnez chaque colonne et exécutez la commande Supprimer les doublons dans l'onglet Données si vous voulez comparer uniquement les entrées uniques.
3. Appliquez =TRIM(A1) dans une nouvelle colonne et collez les valeurs pour supprimer les espaces indésirables, puis remplacez les données d'origine par la version nettoyée.

• Conservez toujours une copie de sauvegarde de vos données brutes avant d'appliquer des transformations.
• Utilisez =CLEAN(A1) pour supprimer les caractères non imprimables souvent importés depuis d'autres systèmes.

=TRIM(A1)

=CLEAN(A1)

Utilisez COUNTIF pour identifier les éléments manquants ou supplémentaires

COUNTIF est l'un des moyens les plus simples de vérifier si chaque élément d'une liste apparaît dans l'autre. La formule compte les occurrences d'une valeur dans une plage spécifiée. Un résultat de zéro signifie que l'élément est absent ; tout nombre positif signifie qu'il existe au moins une fois.

Vous pouvez créer une colonne d'aide qui renvoie « Correspondance » ou « Uniquement dans Liste1 » pour chaque ligne, vous donnant une image claire des chevauchements et des écarts.

1. Assurez-vous que les deux listes sont dans les colonnes A et B. Insérez une nouvelle colonne C à côté de Liste1.
2. Dans la cellule C1, entrez : =IF(COUNTIF(B:B,A1)=0, "Uniquement dans Liste1", "Correspondance"). Faites glisser la formule vers le bas pour couvrir toute la Liste1.
3. Répétez le processus pour Liste2 dans la colonne D pour voir quels éléments sont uniquement dans Liste2.

• COUNTIF n'est pas sensible à la casse. Si la casse est importante, utilisez l'approche SUMPRODUCT avec EXACT (voir Section 5).
• Pour les grandes plages, COUNTIF peut ralentir votre classeur ; envisagez de limiter la plage aux données réellement utilisées.

=IF(COUNTIF(B:B, A1)>0, "Match", "Only in List1")

Utilisez XLOOKUP pour connecter des données associées

Lorsque vous devez renvoyer un champ associé plutôt qu'un simple résultat oui/non, XLOOKUP peut rechercher une plage et renvoyer la valeur correspondante d'une autre. Il est disponible dans Microsoft 365 et les versions perpétuelles actuelles d'Excel telles qu'Excel 2021 et ultérieures ; les installations plus anciennes peuvent nécessiter INDEX-MATCH ou RECHERCHEV.

XLOOKUP renvoie un résultat pour chaque formule de recherche. Si la recherche échoue, son argument facultatif non trouvé peut afficher une étiquette claire telle que « Non trouvé ». Utilisez FILTER, Power Query ou une jointure structurée lorsqu'une clé doit renvoyer plusieurs enregistrements.

1. Supposons que vos valeurs de recherche se trouvent dans la colonne A (Liste1) et que les données à récupérer se trouvent dans les colonnes B et C (colonne de recherche B, colonne de retour C).
2. Dans la colonne D, entrez : =XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100, "Non trouvé"). Ajustez les plages pour qu'elles correspondent à vos données.
3. Copiez la formule vers le bas. Les entrées qui indiquent « Non trouvé » sont absentes de Liste2, ou présentes mais sans enregistrement correspondant.

• XLOOKUP utilise par défaut une correspondance exacte, ce qui est idéal pour la comparaison de listes.
• Pour les anciennes versions d'Excel, utilisez INDEX-MATCH ou RECHERCHEV(FAUX) comme alternatives.

=XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100, "Not found")

=INDEX($C$2:$C$100, MATCH(A2, $B$2:$B$100, 0))

Utilisez la mise en forme conditionnelle pour mettre en évidence les différences

La mise en forme conditionnelle permet d'appliquer une couleur aux cellules qui répondent à une règle, rendant les discordances instantanément visibles sans ajouter de colonnes supplémentaires. Vous pouvez mettre en évidence les cellules de Liste1 qui sont absentes de Liste2, ou vice versa.

Cette approche fonctionne mieux pour une inspection visuelle lorsque vous n'avez pas besoin de conserver un marqueur permanent ou lorsque vous partagez un classeur avec des collègues qui préfèrent ne pas voir de colonnes de formules supplémentaires.

1. Sélectionnez la plage dans Liste1 (par exemple, A2:A100). Sous l'onglet Accueil, cliquez sur Mise en forme conditionnelle > Nouvelle règle.
2. Choisissez « Utiliser une formule pour déterminer les cellules à mettre en forme ». Entrez : =COUNTIF($B$2:$B$100, $A2)=0
3. Cliquez sur Format et choisissez une couleur de remplissage (par exemple, rouge) pour les cellules uniques à Liste1, puis cliquez sur OK. Répétez pour Liste2, en référençant Liste1 comme plage.

• Utilisez des références mixtes ($A2) pour que la formule s'ajuste correctement pour chaque ligne.
• Vous pouvez également mettre en évidence les doublons entre deux listes en modifiant la règle en =COUNTIF($B$2:$B$100, $A2)>0 pour les mêmes plages.

=COUNTIF($B$2:$B$100, $A2)=0

Gérer la sensibilité à la casse et les espaces supplémentaires

Les formules de comparaison standard dans Excel (COUNTIF, XLOOKUP, RECHERCHEV) ne sont pas sensibles à la casse. Si vos listes contiennent « Apple » et « apple » et que vous devez les traiter comme différentes, vous devez utiliser la fonction EXACT avec SUMPRODUCT ou la mise en forme conditionnelle.

Même avec des comparaisons insensibles à la casse, les espaces de fin peuvent produire de fausses non-correspondances. Appliquez toujours TRIM aux deux listes avant de comparer, ou imbriquez TRIM dans votre formule.

1. Pour effectuer une correspondance sensible à la casse de Liste1 par rapport à Liste2, entrez dans C1 : =IF(SUMPRODUCT((EXACT(A2, $B$2:$B$100))*1)>0, "Correspondance", "Différent") et faites glisser vers le bas.
2. Alternativement, utilisez la mise en forme conditionnelle avec une règle basée sur EXACT : =SUMPRODUCT((EXACT($A2, $B$2:$B$100))*1)=0
3. Pour gérer les espaces, encapsulez chaque référence de plage dans TRIM : =IF(COUNTIF($B$2:$B$100, TRIM(A2))=0, ...)

• EXACT est sensible à la casse et prend également en compte les espaces, donc nettoyez les données au préalable.
• Pour les grandes listes, SUMPRODUCT avec EXACT peut être lent ; envisagez d'utiliser une colonne d'aide avec un indicateur de correspondance exacte.

=IF(SUMPRODUCT((EXACT(A2, $B$2:$B$100))*1)>0, "Match", "No match")

Comprendre les limites d'Excel pour les comparaisons volumineuses ou complexes

Une feuille de calcul Excel a une limite de lignes fixe, mais la limite pratique pour une comparaison est souvent inférieure. La taille du classeur, les plages de formules, le recalcul, la mise en forme conditionnelle, la mémoire disponible et la vitesse de l'appareil affectent tous la réactivité.

Les vérifications d'appartenance exactes sont simples. La correspondance approximative, les jointures multi-colonnes et les pipelines de données reproductibles nécessitent Power Query, des compléments spécialisés, des scripts ou une base de données. Un outil de liste dans le navigateur est utile pour une comparaison exacte ciblée, mais ce n'est pas un moteur de correspondance approximative et doit également être testé avec un échantillon représentatif.

• Évitez les références de colonne entière lorsqu'une plage limitée ou un tableau suffit.
• Évaluez le classeur réel au lieu de vous fier à un seuil de lignes générique.
• Utilisez Power Query, SQL ou des scripts lorsque la tâche nécessite des jointures structurées ou une automatisation reproductible.

Quand passer à un outil de comparaison de listes basé sur un navigateur

Pour une comparaison exacte ponctuelle, copier les deux colonnes de valeurs dans un outil dédié dans le navigateur peut être plus rapide que de maintenir des formules dans le classeur. CompareTwoLists peut afficher les valeurs partagées, les valeurs uniquement dans la première liste, les valeurs uniquement dans la deuxième liste, ou l'ensemble combiné.

Le traitement a lieu dans la page, donc les valeurs de liste collées ne sont pas téléchargées sur le serveur du site. La comparaison est basée sur les valeurs : elle ne fait pas de correspondance approximative des noms, ne joint pas des enregistrements complets de feuille de calcul, ni ne déduit les en-têtes correspondants. Revenez à Excel, Power Query, SQL ou un dataframe lorsque ces capacités sont nécessaires.

1. Copiez uniquement les deux colonnes de valeurs à comparer.
2. Collez-les dans Compare Two Lists ou Compare Columns.
3. Choisissez le résultat partagé, première uniquement, deuxième uniquement ou combiné.
4. Validez les comptes de lignes et vérifiez plusieurs valeurs avant d'utiliser le résultat.

• Traitement local dans la page pour les valeurs collées
• Appartenance exacte à la liste et opérations ensemblistes
• Pas de correspondance approximative ni de jointures d'enregistrements complets
• Les performances dépendent du navigateur, de l'appareil et des données d'entrée