KHIZHNYAKOVComparer deux listes sur Excel : correspondances, éléments manquants et écarts
Rapprochement de deux listes dans Excel — modèle gratuit — Télécharger gratuitement →
Deux listes qui devraient être identiques ne le sont jamais tout à fait : le fichier du service comptable et celui des ventes, l'export de la banque et le journal des encaissements, l'état des effectifs et la liste des badges. Les repérer à l'œil sur quelques centaines de lignes est fatigant et peu fiable.
Excel sait le faire en quelques formules. Voici la méthode, de la préparation des données à la lecture des écarts.
Préparer les listes
Chaque liste doit avoir une clé : une colonne qui identifie la ligne de manière unique (numéro de contrat, matricule, référence, e-mail). Ensuite, une ou deux colonnes de valeurs à comparer, par exemple le montant ou la date.
Avant toute comparaison, nettoyez la clé. Deux fautes courantes : les espaces en trop et les différences de casse.
=SUPPRESPACE(MAJUSCULE(A2))
Mettez cette formule dans une colonne auxiliaire et comparez cette colonne plutôt que l'original. Précision utile : NB.SI et RECHERCHEV ne tiennent déjà pas compte de la casse, mais pas des espaces superflus.
Trouver ce qui manque d'un côté
Pour chaque clé de la liste A, comptez ses occurrences dans la liste B :
=NB.SI(ListeB;A2)
- résultat 0 : l'élément n'existe que dans A ;
- résultat 1 : il est présent dans les deux ;
- résultat supérieur à 1 : doublon dans B.
Un statut lisible :
=SI(NB.SI(ListeB;A2)=0;"Uniquement dans A";"Présent dans B")
Faites la même chose dans l'autre sens, de B vers A, pour trouver les éléments uniquement dans B.
Repérer les valeurs qui diffèrent
Quand la clé existe des deux côtés, comparez les valeurs avec RECHERCHEV :
=SIERREUR(SI(RECHERCHEV(A2;ListeB;2;FAUX)=B2;"OK";"Écart");"Absent")
Ici, ListeB contient la clé en première colonne et la valeur en deuxième. Pour chiffrer l'écart d'un montant : =B2-RECHERCHEV(A2;ListeB;2;FAUX).
Exemple chiffré
| N° facture (A) | Montant A | Montant B |
|---|---|---|
| F-101 | 1 200,00 € | 1 200,00 € |
| F-102 | 845,50 € | 854,50 € |
| F-103 | 300,00 € | absent |
F-101 correspond. F-102 présente un écart de 9 € qui est probablement une inversion de chiffres. F-103 n'existe que dans la liste A. En trois lignes, vous avez les trois cas de figure d'un rapprochement.
Les doublons de clés
Une clé répétée fausse le rapprochement : RECHERCHEV s'arrête à la première occurrence. Détectez-les d'abord avec =NB.SI(A:A;A2)>1, puis décidez : doublon à supprimer, ou deux lignes légitimes (par exemple deux paiements pour une même facture) à regrouper avant la comparaison.
Les erreurs fréquentes
- Comparer des nombres stockés comme du texte : « 1200 » et 1200 ne sont pas égaux. Convertissez avec
CNUM. - Oublier les espaces insécables venus d'un export.
- Comparer sans clé fiable : le nom d'une personne n'est pas un identifiant ; préférez un matricule.
- Écraser les listes d'origine : travaillez toujours sur une copie.
Modèle gratuit de rapprochement de deux listes
Le modèle Rapprochement de deux listes est gratuit. Collez vos deux listes, et il fait le reste. Il fonctionne sans macros, sous Excel 2007 et versions ultérieures et sous LibreOffice, et il est en français.
- Liste A et Liste B : jusqu'à 5 000 lignes chacune, avec une clé et deux valeurs à comparer.
- Paramètres : choix des valeurs à comparer en plus de la clé.
- Synthèse : correspondances, éléments uniquement dans A ou dans B, écarts et doublons de clés.
- Uniquement dans A, Uniquement dans B et Écarts : listes prêtes à copier, avec les numéros de ligne d'origine.
La clé est nettoyée avant la comparaison : espaces superflus supprimés, casse ignorée. Une clé répétée est comparée à la première correspondance trouvée et marquée comme doublon. Il convient pour rapprocher des effectifs, des paiements, des stocks ou des fichiers clients.
FAQ
Peut-on comparer sur plusieurs colonnes à la fois ?
Oui, en construisant une clé composée, par exemple =A2&"-"&B2. Le modèle gratuit, lui, compare une clé et deux valeurs.
La mise en forme conditionnelle suffit-elle ?
Pour un contrôle rapide, oui : Mise en forme conditionnelle, puis Valeurs en double. Mais elle ne produit pas de liste exploitable ni les écarts chiffrés.
Et avec plus de 5 000 lignes ?
Au-delà, découpez par période ou par catégorie, ou utilisez Power Query si votre version d'Excel le propose.
Comment comparer deux versions d'un même fichier ?
Collez l'ancienne version dans la liste A et la nouvelle dans la liste B, avec la même clé. Les écarts montrent ce qui a changé.
