Excel

Excel : comparer deux listes avec RECHERCHEX efficacement

Apprenez à comparer deux listes dans Excel pour repérer ajouts, suppressions et doublons avec RECHERCHEX, NB.SI et mise en forme conditionnelle.

Excel : comparer deux listes avec RECHERCHEX efficacement

Comparer deux listes est une tâche fréquente dans Excel : contrôle d’un export avant/après mise à jour, vérification d’un inventaire, suivi d’une base clients ou validation d’une liste de références. L’objectif n’est pas seulement de savoir si deux colonnes sont différentes, mais d’identifier précisément ce qui a été ajouté, supprimé et dupliqué.

Dans ce tutoriel, vous allez construire une méthode simple, lisible et réutilisable à partir de deux outils très pratiques : RECHERCHEX pour vérifier la présence d’une valeur dans une autre liste, et la mise en forme conditionnelle pour faire ressortir visuellement les écarts. Pour la détection des doublons, nous utiliserons aussi NB.SI, particulièrement adapté à ce besoin.

Le principe sera le suivant : placer l’ancienne liste et la nouvelle liste côte à côte, créer des colonnes d’état, puis faire calculer à Excel si chaque valeur est conservée, ajoutée, supprimée ou présente plusieurs fois. Vous obtiendrez ainsi un contrôle clair, exploitable et facile à adapter à vos propres fichiers.

Prérequis

Ce tutoriel suppose que vous savez saisir une formule dans Excel, recopier une formule vers le bas et sélectionner une plage de cellules. Il est également utile de connaître le principe des références de cellules, notamment la différence entre une référence qui se déplace et une référence figée avec le signe $.

La fonction RECHERCHEX doit être disponible dans votre version d’Excel. Si elle n’est pas présente dans votre environnement, le principe général du tutoriel reste valable, mais les formules montrées ici sont rédigées avec RECHERCHEX.

Matériel nécessaire

  • Un fichier Excel contenant une ancienne liste et une nouvelle liste à comparer
  • Excel avec la fonction RECHERCHEX disponible
  • Des listes comparables contenant un identifiant stable par ligne, comme un code, un e-mail ou une référence

Étapes

  1. Préparer les deux listes dans une structure claire

    Écran d’ordinateur affichant une feuille de calcul avec une ancienne liste et une colonne de résultat où certaines valeurs sont visuellement marquées comme supprimées.
    Feuille de calcul montrant deux listes côte à côte avec des cellules colorées par mise en forme conditionnelle pour visualiser les écarts.
    Section de synthèse dans une feuille de calcul regroupant les résultats de comparaison entre deux listes.

    Commencez par ouvrir une feuille dédiée à la comparaison. Placez l’ancienne liste dans une première colonne et la nouvelle liste dans une seconde zone, par exemple :

    • colonne A : Ancienne liste
    • colonne D : Nouvelle liste

    Laissez quelques colonnes libres entre les deux si vous souhaitez ajouter des colonnes de contrôle. Par exemple :

    • A : Ancienne liste
    • B : État dans la nouvelle liste
    • C : Doublons ancienne liste
    • D : Nouvelle liste
    • E : État dans l’ancienne liste
    • F : Doublons nouvelle liste

    Cette séparation a un avantage important : vous pouvez analyser les suppressions depuis l’ancienne liste, puis les ajouts depuis la nouvelle liste, sans mélanger les résultats.

    Avant d’aller plus loin, nettoyez autant que possible vos données :

    • supprimez les espaces inutiles en début ou fin de valeur si nécessaire ;
    • vérifiez que vous comparez bien le même type d’information dans les deux colonnes ;
    • assurez-vous que les formats sont cohérents, notamment pour les codes ou identifiants.

    Par exemple, si une liste contient un identifiant saisi comme texte et l’autre comme nombre, les résultats peuvent être difficiles à interpréter. Dans un contexte de comparaison, l’idéal est d’utiliser un identifiant unique et stable pour chaque ligne.

  2. Identifier les valeurs supprimées depuis l’ancienne liste

    L’idée est simple : pour chaque valeur de l’ancienne liste, on demande à Excel si cette valeur existe encore dans la nouvelle liste. Si elle n’est pas retrouvée, on peut la considérer comme supprimée.

    Dans la cellule B2, saisissez une formule de ce type :

    =SI(RECHERCHEX(A2;$D:$D;$D:$D;"ABSENT")="ABSENT";"Supprimé";"Présent")

    Cette formule fonctionne ainsi :

    • RECHERCHEX(A2;$D:$D;$D:$D;"ABSENT") cherche la valeur de A2 dans la colonne D ;
    • si la valeur est trouvée, Excel renvoie la valeur correspondante de la colonne D ;
    • si elle n’est pas trouvée, Excel renvoie le texte ABSENT ;
    • la fonction SI transforme ensuite ce résultat en état lisible : Supprimé ou Présent.

    Recopiez ensuite la formule vers le bas pour couvrir toutes les lignes de l’ancienne liste.

    Vous obtenez ainsi une première lecture :

    • Présent : la valeur existe encore dans la nouvelle liste ;
    • Supprimé : la valeur n’existe plus dans la nouvelle liste.

    Cette approche est particulièrement utile pour contrôler ce qui a disparu après une mise à jour, un export ou une fusion de données.

    Si vous préférez un libellé plus explicite, vous pouvez remplacer les textes renvoyés par des formulations comme Conservé et Supprimé. L’important est d’adopter des termes cohérents dans toute la feuille.

  3. Identifier les valeurs ajoutées dans la nouvelle liste

    La logique est symétrique. Cette fois, pour chaque valeur de la nouvelle liste, vous allez vérifier si elle existait déjà dans l’ancienne liste. Si ce n’est pas le cas, elle doit être considérée comme un ajout.

    Dans la cellule E2, saisissez :

    =SI(RECHERCHEX(D2;$A:$A;$A:$A;"ABSENT")="ABSENT";"Ajouté";"Présent")

    Puis recopiez la formule vers le bas.

    Lecture des résultats :

    • Présent : la valeur existait déjà dans l’ancienne liste ;
    • Ajouté : la valeur n’était pas présente auparavant.

    Avec les colonnes B et E, vous disposez désormais d’une vue bilatérale :

    • depuis l’ancienne liste, vous voyez ce qui a été supprimé ;
    • depuis la nouvelle liste, vous voyez ce qui a été ajouté.

    Cette double lecture est souvent plus fiable qu’une simple comparaison visuelle de deux colonnes, surtout lorsque les listes sont longues ou que leur ordre a changé.

    Un point important : cette méthode ne dépend pas de la position des valeurs dans la colonne. Même si les éléments ont été triés différemment entre les deux listes, RECHERCHEX teste la présence de chaque valeur dans toute la plage ciblée.

  4. Détecter les doublons dans chaque liste avec NB.SI

    La présence d’ajouts et de suppressions n’est pas le seul problème possible. Une liste peut aussi contenir des doublons, ce qui fausse l’analyse. Par exemple, une valeur peut sembler présente dans les deux listes, mais être répétée plusieurs fois dans l’une d’elles.

    Pour compter le nombre d’occurrences d’une valeur dans une liste, utilisez NB.SI.

    Dans la cellule C2, saisissez :

    =SI(NB.SI($A:$A;A2)>1;"Doublon";"Unique")

    Recopiez vers le bas. Cette formule indique si la valeur de l’ancienne liste apparaît plus d’une fois dans la colonne A.

    Faites la même chose pour la nouvelle liste dans F2 :

    =SI(NB.SI($D:$D;D2)>1;"Doublon";"Unique")

    Recopiez ensuite la formule vers le bas.

    Vous avez maintenant une indication simple :

    • Unique : la valeur n’apparaît qu’une seule fois dans la liste ;
    • Doublon : la valeur apparaît plusieurs fois.

    Cette étape est importante pour la qualité du contrôle. Si vous comparez des listes de références, de clients ou de produits, les doublons doivent souvent être traités séparément avant d’interpréter les ajouts et suppressions.

    Vous pouvez aller un peu plus loin en affichant le nombre exact d’occurrences. Par exemple, dans une colonne supplémentaire, utilisez :

    =NB.SI($A:$A;A2)

    ou

    =NB.SI($D:$D;D2)

    Cette information est utile si vous devez distinguer un doublon isolé d’une répétition plus importante.

  5. Appliquer une mise en forme conditionnelle pour visualiser les écarts

    Les formules donnent le bon résultat, mais l’intérêt d’Excel est aussi de rendre les écarts immédiatement visibles. La mise en forme conditionnelle permet de colorer les cellules selon leur statut.

    Commencez par la colonne B, qui contient l’état de l’ancienne liste :

    1. Sélectionnez la plage de résultats, par exemple B2:B1000.
    2. Ouvrez le menu de mise en forme conditionnelle.
    3. Créez une règle basée sur le texte contenant Supprimé ou sur une formule.
    4. Choisissez un remplissage visible, par exemple rouge clair.

    Créez ensuite une autre règle pour le texte Présent avec une couleur plus neutre, par exemple vert clair si vous souhaitez marquer les valeurs conservées.

    Faites la même chose dans la colonne E :

    • Ajouté en couleur distincte, par exemple bleu ou jaune selon vos habitudes ;
    • Présent dans une couleur discrète.

    Pour les doublons :

    • sélectionnez C2:C1000 puis créez une règle pour le texte Doublon ;
    • répétez l’opération sur F2:F1000.

    Vous pouvez également choisir de colorer directement les valeurs sources au lieu des colonnes d’état. Par exemple, si vous souhaitez surligner les cellules de la colonne A lorsque B indique Supprimé, créez une règle avec formule appliquée à la plage A2:A1000 :

    =$B2="Supprimé"

    Et pour la colonne D lorsque E indique Ajouté :

    =$E2="Ajouté"

    Ainsi, les listes elles-mêmes deviennent plus faciles à lire. Dans de grands tableaux, cette présentation est souvent plus pratique qu’un simple libellé textuel dans une colonne voisine.

  6. Créer une lecture de synthèse exploitable

    À ce stade, vous savez repérer les suppressions, les ajouts et les doublons. Pour rendre le fichier encore plus utile, vous pouvez construire une petite zone de synthèse avec quelques comptages.

    Par exemple :

    • nombre de suppressions ;
    • nombre d’ajouts ;
    • nombre de cellules marquées comme doublons dans l’ancienne liste ;
    • nombre de cellules marquées comme doublons dans la nouvelle liste.

    Pour compter les suppressions dans la colonne B :

    =NB.SI(B:B;"Supprimé")

    Pour compter les ajouts dans la colonne E :

    =NB.SI(E:E;"Ajouté")

    Pour compter les lignes marquées comme doublons dans C :

    =NB.SI(C:C;"Doublon")

    Et dans F :

    =NB.SI(F:F;"Doublon")

    Attention toutefois à l’interprétation : compter les cellules marquées Doublon ne revient pas toujours à compter le nombre de valeurs distinctes dupliquées. Si une même référence apparaît trois fois, trois cellules seront marquées comme doublon. Ce comportement peut être utile pour repérer toutes les lignes concernées, mais il faut le garder en tête lorsque vous présentez les résultats.

    Une synthèse simple rend le fichier plus opérationnel, surtout si vous devez le transmettre à un collègue ou l’utiliser comme support de contrôle avant validation.

  7. Fiabiliser la comparaison avec de bonnes pratiques

    La qualité du résultat dépend fortement de la qualité des données comparées. Voici plusieurs bonnes pratiques à appliquer avant de conclure qu’un élément a été ajouté ou supprimé.

    1. Comparer un identifiant stable
    Évitez de comparer un libellé libre si vous avez à disposition un code unique, un matricule, une référence produit ou une adresse e-mail. Un simple changement d’orthographe dans un nom peut faire croire à une suppression suivie d’un ajout.

    2. Harmoniser les formats
    Si une liste contient 00125 et l’autre 125, le résultat dépendra de la manière dont les données sont stockées et affichées. Avant la comparaison, vérifiez les formats numériques et textuels.

    3. Traiter les espaces parasites
    Deux valeurs visuellement identiques peuvent être différentes si l’une contient un espace supplémentaire. Lorsque vous suspectez ce problème, créez une colonne de nettoyage avant comparaison.

    4. Éviter les cellules vides mélangées aux données
    Des blancs au milieu des listes ne bloquent pas forcément les formules, mais peuvent compliquer la lecture des résultats, surtout avec les recopiages et les mises en forme.

    5. Vérifier les doublons avant d’interpréter les écarts
    Si une valeur est dupliquée dans une liste, l’analyse des ajouts et suppressions peut sembler cohérente tout en masquant une anomalie métier. Les colonnes C et F doivent donc faire partie du contrôle standard.

    En pratique, une comparaison fiable n’est pas seulement une question de formule. C’est aussi une question de préparation et d’interprétation rigoureuse.

  8. Adapter la méthode à un cas réel de suivi avant/après

    Imaginons un usage très courant : vous disposez d’une liste d’articles avant mise à jour et d’une nouvelle liste après import. Vous voulez savoir :

    • quels articles ont disparu ;
    • quels articles ont été ajoutés ;
    • si certaines références apparaissent plusieurs fois par erreur.

    Avec la structure proposée :

    • la colonne A contient les anciennes références ;
    • la colonne D contient les nouvelles références ;
    • la colonne B indique Supprimé ou Présent ;
    • la colonne E indique Ajouté ou Présent ;
    • les colonnes C et F signalent les doublons.

    Une fois les filtres activés sur les en-têtes, vous pouvez afficher uniquement :

    • les lignes Supprimé pour lister les suppressions ;
    • les lignes Ajouté pour lister les nouveautés ;
    • les lignes Doublon pour corriger les répétitions.

    Cette manière de travailler est simple, très visuelle et compatible avec de nombreux contextes de bureautique : inventaires, bases de contacts, catalogues, listes d’employés, références techniques ou exports applicatifs.

    Elle a aussi l’avantage d’être facile à relire plusieurs jours plus tard : les colonnes d’état expliquent clairement la logique utilisée, sans obliger à relancer une comparaison manuelle.

Erreurs fréquentes & dépannage

Comparer des valeurs non homogènes
Si vous comparez des codes, des noms et des références sans format cohérent, vous risquez d’obtenir de faux écarts.

Utiliser RECHERCHEX sans valeur de remplacement en cas d’absence
Prévoir un texte comme ABSENT rend la formule plus facile à lire et à tester dans SI.

Oublier de figer les colonnes de recherche
Dans les formules avec $A:$A ou $D:$D, le figement évite que la plage ne se décale lors de la recopie.

Interpréter trop vite les doublons
Une cellule marquée comme doublon signale une répétition dans la liste, mais ne suffit pas à expliquer son origine. Il faut ensuite analyser la valeur concernée.

Se fier uniquement à la couleur
La mise en forme conditionnelle améliore la lecture, mais les colonnes d’état et les formules doivent rester présentes pour conserver une logique vérifiable.

Astuces & pour aller plus loin

Transformez les plages en tableau Excel
Même sans changer les formules du principe, travailler dans un tableau facilite les filtres, la lisibilité et l’extension des données.

Filtrez immédiatement les colonnes d’état
Après calcul, appliquez un filtre sur Supprimé, Ajouté ou Doublon pour obtenir des listes d’action prêtes à exploiter.

Conservez une feuille modèle
Si vous effectuez souvent ce type de contrôle, gardez une feuille préconfigurée avec les colonnes, les formules et les règles de mise en forme conditionnelle.

Ajoutez une colonne de commentaire
Dans un contexte de validation, une colonne libre peut servir à noter la raison d’une suppression, d’un ajout ou d’un doublon détecté.

Questions fréquentes

Pourquoi utiliser RECHERCHEX plutôt qu’une simple comparaison ligne à ligne ?

Parce que les deux listes peuvent ne pas être dans le même ordre. RECHERCHEX vérifie si une valeur existe dans toute l’autre liste, ce qui évite qu’un tri différent fasse croire à des écarts inexistants.

RECHERCHEX permet-il aussi de détecter les doublons ?

Pas directement pour ce besoin. Dans ce tutoriel, RECHERCHEX sert à tester la présence d’une valeur dans l’autre liste, tandis que NB.SI est utilisé pour compter les occurrences dans une même liste.

Dois-je comparer les libellés ou un identifiant ?

Il vaut mieux comparer un identifiant stable comme une référence, un code ou un e-mail. Cela limite les faux écarts causés par des variations de nommage ou de saisie.

Puis-je colorer les cellules de la liste plutôt que les colonnes de résultat ?

Oui. Le tutoriel montre comment utiliser une règle de mise en forme conditionnelle basée sur la colonne d’état, par exemple pour surligner directement les valeurs supprimées ou ajoutées.

Que signifie un grand nombre de cellules marquées comme doublon ?

Cela signifie que plusieurs lignes contiennent des valeurs répétées dans la même liste. Il faut ensuite vérifier si ces répétitions sont normales dans votre contexte ou si elles doivent être corrigées avant l’analyse finale.

Conclusion

Comparer deux listes dans Excel peut rester très simple dès lors que la méthode est bien structurée. En associant RECHERCHEX pour vérifier la présence d’une valeur dans l’autre liste, NB.SI pour repérer les doublons et la mise en forme conditionnelle pour visualiser les écarts, vous obtenez un contrôle clair et immédiatement exploitable.

Cette approche permet de distinguer proprement les ajouts, les suppressions et les doublons, même lorsque les listes ne sont pas triées de la même manière. Une fois mise en place dans un modèle, elle devient un excellent réflexe pour sécuriser les imports, exports et mises à jour de données dans Excel.

Commentaires· Aucun commentaire pour l'instant

Soyez le premier à réagir.

Laisser un commentaire