Comparer des listes de clients ou d’e-mails : repérer ajouts, départs et modifications

Chaque mois, vous exportez la liste des clients de votre logiciel de facturation ou celle des abonnés de votre newsletter, et vous devez savoir ce qui a changé depuis le mois précédent : qui est nouveau, qui n’y est plus et quelle adresse e-mail a été modifiée. Avec deux fichiers de plusieurs centaines de lignes, comparer à la main n’est pas envisageable.

Voici une méthode structurée pour repérer ajouts, départs et modifications avec des formules Excel, avec des données d’exemple et les erreurs qui provoquent le plus de faux résultats.

Avant de commencer : choisissez une clé unique

Pour comparer deux listes, il vous faut un champ qui identifie chaque client sans ambiguïté et qui ne change pas dans le temps. C’est la clé. Les options habituelles, de la meilleure à la moins bonne :

• Un code client (C-001, C-002…). C’est l’idéal : il ne se répète pas et ne change pas même si le client met à jour son e-mail.
• L’adresse e-mail. Elle convient aux listes d’abonnés, mais si quelqu’un change d’adresse, il apparaîtra comme un départ et un ajout.
• Le nom. C’est la pire option : les homonymes existent et une même personne peut être écrite de plusieurs façons.

Copiez les deux versions de la liste dans le même classeur, sur des feuilles nommées Precedent et Actuel. Travailler dans un seul fichier simplifie les formules et évite les références à des classeurs fermés.

Les données d’exemple

Feuille Precedent (données en A2:C5) :

Feuille Actuel (données en A2:C5) :

D’un coup d’œil : C-005 est un ajout, C-003 un départ et C-002 a changé d’e-mail. Voyons comment Excel le détecte.

Repérer ajouts et départs avec NB.SI

Ajouts : présents dans Actuel mais pas dans Precedent

Sur la feuille Actuel, saisissez l’en-tête « Statut » en D1 et cette formule en D2. Recopiez-la ensuite vers le bas :

=SI(NB.SI(Precedent!$A$2:$A$5;A2)=0;"Nouveau";"Existant")

NB.SI compte combien de fois l’ID apparaît sur la feuille Precedent. Si le résultat est zéro, le client est nouveau. Seul C-005 s’affiche comme « Nouveau ».

Départs : présents dans Precedent et absents désormais

Faites la même chose sur la feuille Precedent, en cherchant dans l’autre sens :

=SI(NB.SI(Actuel!$A$2:$A$5;A2)=0;"Parti";"Toujours là")

C-003 est marqué « Parti ». Avec vos vraies données, étendez les plages jusqu’à la dernière ligne ou, mieux, transformez chaque liste en tableau avec Ctrl+L pour que les plages s’agrandissent toutes seules.

Repérer les modifications de données

Qu’un client figure toujours dans la liste ne veut pas dire que ses données sont identiques. Pour vérifier l’e-mail, récupérez l’ancienne valeur avec RECHERCHEV et comparez-la à l’actuelle. Cette formule, en colonne D de la feuille Actuel, remplace celle des ajouts et règle tout d’un coup :

=SI(NB.SI(Precedent!$A$2:$A$5;A2)=0;"Nouveau";SI(RECHERCHEV(A2;Precedent!$A$2:$C$5;3;FAUX)=C2;"Aucun changement";"E-mail modifié"))

Elle vérifie d’abord si l’ID existe dans la liste précédente. Si c’est le cas, elle récupère l’e-mail d’origine (colonne 3 de la plage) et le compare à celui de la ligne actuelle. Résultat : C-001 et C-004 « Aucun changement », C-002 « E-mail modifié » et C-005 « Nouveau ».

Contrôler plusieurs champs

Pour surveiller aussi le nom, ajoutez une autre colonne sur le même principe, en changeant le numéro de colonne et la cellule comparée :

=SI(NB.SI(Precedent!$A$2:$A$5;A2)=0;"";SI(RECHERCHEV(A2;Precedent!$A$2:$C$5;2;FAUX)=B2;"";"Nom modifié"))

Notez que la comparaison avec = ne distingue pas les majuscules : « Client b » et « Client B » sont considérés comme égaux. Pour un e-mail, c’est exactement ce que vous voulez.

Listes automatiques des ajouts, départs et modifications dans Excel 365

Si vous avez Excel 365 ou Excel 2021, FILTRE renvoie chaque groupe complet, avec toutes ses colonnes, sur une feuille de synthèse. Ajouts :

=FILTRE(Actuel!A2:C5;NB.SI(Precedent!A2:A5;Actuel!A2:A5)=0;"Aucun ajout")

Départs :

=FILTRE(Precedent!A2:C5;NB.SI(Actuel!A2:A5;Precedent!A2:A5)=0;"Aucun départ")

Modifications d’e-mail, avec RECHERCHEX :

=FILTRE(Actuel!A2:C5;SIERREUR(RECHERCHEX(Actuel!A2:A5;Precedent!A2:A5;Precedent!C2:C5)<>Actuel!C2:C5;FAUX);"Aucun changement")

SIERREUR empêche les ajouts, absents de la liste précédente et qui renverraient #N/A, de casser le résultat. Laissez de la place libre sous chaque formule : si les cellules de destination sont occupées, vous verrez l’erreur #PROPAGATION!.

Listes d’e-mails sans code client

Dans une liste d’abonnés, la seule clé est souvent l’adresse e-mail. Avant de comparer, normalisez-la, car « Ana@Exemple.com » suivi d’un espace et « ana@exemple.com » sont la même adresse pour vous, mais pas toujours pour Excel :

1. Insérez une colonne auxiliaire à côté de l’e-mail sur les deux feuilles.
2. Saisissez =MINUSCULE(SUPPRESPACE(B2)), en supposant que l’e-mail est en colonne B, et recopiez vers le bas.
3. Utilisez cette colonne comme clé dans les formules NB.SI des sections précédentes.
4. Vérifiez les ajouts et départs qui portent le même nom : il peut s’agir de la même personne avec une nouvelle adresse.

SUPPRESPACE supprime les espaces superflus et MINUSCULE met tout en minuscules : la comparaison devient bien plus fiable.

Erreurs fréquentes et comment les éviter

• Doublons dans l’une des listes. Si un client apparaît deux fois, les comptages deviennent flous et RECHERCHEV ne regarde que la première correspondance. Nettoyez-les d’abord avec notre outil de suppression des doublons.
• Des ID dans des formats différents. « C-001 » et « C001 », ou 1 et « 0001 », ne correspondent pas. Uniformisez le format avant de comparer.
• Des plages trop courtes. Si vous ajoutez des lignes sans étendre la plage, les nouveaux clients apparaîtront comme de faux ajouts ou départs.
• Oublier FAUX dans RECHERCHEV. Sans ce dernier argument, la recherche est approximative et peut renvoyer les données d’un autre client.
• Travailler sur le fichier d’origine. Faites une copie. Ce sont des données personnelles : conservez-les avec soin et ne les partagez pas plus que nécessaire.

Quand les listes comportent beaucoup de colonnes (téléphone, adresse, catégorie, commercial attitré…), écrire une formule par champ devient fastidieux. Dans ces cas, vous pouvez utiliser notre outil gratuit pour comparer des fichiers Excel : vous envoyez la version précédente comme fichier d’origine et la nouvelle comme fichier modifié, et vous obtenez un fichier avec les changements entre les deux. Vous pourrez ensuite utiliser les formules de cet article pour classer ce qui vous intéresse.

Conclusion

Comparer deux listes de clients revient à répondre à trois questions : qui est nouveau, qui est parti et qu’est-ce qui a changé. Avec une clé unique, NB.SI répond aux deux premières et RECHERCHEV ou RECHERCHEX à la troisième. Si vous normalisez les e-mails, supprimez les doublons et fixez bien les plages, vous disposerez d’un contrôle mensuel fiable, à refaire en quelques minutes.

Questions fréquentes