Comparer deux colonnes Excel et trouver les valeurs manquantes

Trouvez les valeurs d'une colonne Excel absentes d'une autre avec NB.SI, FILTRE, RECHERCHEV ou Power Query, et corrigez les clés qui ne correspondent jamais.

Fondateur, Glow Reports. , 4 min de lecture
  • Excel
  • Qualité des données
  • Reporting
Deux colonnes de fiches reliées quand elles correspondent, les fiches sans correspondance mises à part dans un bac orange près d’une loupe
Sur cette page
  1. Comment comparer deux colonnes Excel pour trouver les valeurs manquantes ?
  2. Comment repérer les valeurs manquantes avec NB.SI ?
  3. Comment obtenir la liste des valeurs manquantes en une seule formule ?
  4. Comment comparer deux exports chaque mois sans refaire les formules ?
  5. Pourquoi Excel indique une valeur manquante alors qu'elle est dans les deux colonnes ?
  6. Que faire de la liste une fois obtenue ?
  7. Où intervient Glow Reports ?
  8. Questions fréquentes

Vous avez deux exports, la liste clients du mois dernier et celle de ce mois-ci, collées côte à côte. Vous cherchez les identifiants présents dans une colonne et absents de l'autre. Excel n'a pas de bouton « comparer deux colonnes », mais trois méthodes intégrées font le travail. Une colonne d'aide avec NB.SI repère chaque valeur manquante. Une formule FILTRE renvoie directement la liste. Une fusion Power Query sert pour les fichiers qui reviennent chaque mois. Les trois échouent de la même façon quand les clés ne sont pas identiques au caractère près. La première étape consiste donc à nettoyer la colonne de comparaison.

Comment comparer deux colonnes Excel pour trouver les valeurs manquantes ?

Comptez combien de fois chaque valeur de la première colonne apparaît dans la seconde. Un résultat de 0 signifie que la valeur manque dans la seconde colonne. NB.SI fait ce calcul ligne par ligne, et FILTRE transforme le même test en liste.

Les exemples suivants utilisent une seule feuille : les identifiants clients du mois dernier en colonne A (), ceux de ce mois-ci en colonne C (). Les clients présents en A et absents de C sont ceux qui sont partis. Les clients présents en C et absents de A sont les nouveaux. Inversez les plages pour obtenir l'autre sens.

Collez les deux listes sur la même feuille, même si elles viennent de deux fichiers. Une formule qui pointe vers un autre classeur casse dès que quelqu'un renomme ou déplace le fichier.

Comment repérer les valeurs manquantes avec NB.SI ?

Saisissez en B2 et recopiez vers le bas. VRAI signale un identifiant de la colonne A qui n'apparaît nulle part en colonne C. Filtrez ensuite la colonne B sur VRAI pour ne garder que les lignes manquantes.

NB.SI compte les cellules d'une plage qui remplissent une condition Support Microsoft. Les dollars figent la plage pendant la recopie. Sans eux, la plage descend d'une ligne à chaque copie et les dernières lignes se comparent à un bloc vide.

Trois points de la page de Microsoft comptent pour une comparaison de listes :

  • Les critères ne respectent pas la casse : « acme » et « ACME » sont considérés comme identiques.
  • Un point d'interrogation ou un astérisque dans la valeur sert de caractère générique. Un identifiant comme trouve plus que lui-même. Placez un tilde devant le caractère pour chercher le caractère lui-même.
  • Les espaces en début ou en fin de cellule et les guillemets typographiques faussent le résultat. Nettoyez-les avant.

Pour colorer les lignes plutôt qu'ajouter une colonne, sélectionnez , puis AccueilMise en forme conditionnelleNouvelle règle, choisissez « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué » et saisissez . Google Sheets propose la même fonction NB.SI, avec les mêmes caractères génériques et sans distinction de casse Aide Éditeurs Google Docs.

Comment obtenir la liste des valeurs manquantes en une seule formule ?

Dans Excel pour Microsoft 365, 2021 ou 2024, FILTRE renvoie les identifiants manquants sous forme de liste, mise à jour dès qu'une des deux colonnes change. Saisissez la formule une fois dans une colonne vide et les résultats se déversent vers le bas.

Identifiants de la colonne A absents de la colonne C
=FILTRE(A2:A500;(A2:A500<>"")*(NB.SI(C2:C500;A2:A500)=0);"Aucun manquant")

NB.SI reçoit toute la plage comme critère et renvoie donc un résultat par ligne. La partie écarte les cellules vides, qui apparaîtraient sinon comme manquantes. Le dernier argument est le texte affiché quand rien ne manque. Sans lui, FILTRE renvoie l'erreur #CALC! quand aucune ligne ne correspond Support Microsoft.

Google Sheets accepte la même logique. Sa fonction FILTER prend la plage puis une ou plusieurs conditions, séparées par des points-virgules, et renvoie #N/A quand rien ne correspond Aide Éditeurs Google Docs :

Même liste dans Google Sheets
=FILTER(A2:A500;A2:A500<>"";NB.SI(C2:C500;A2:A500)=0)

Si vous avez aussi besoin d'une valeur de la seconde liste, comme l'offre ou la date de dernière commande, passez par une recherche. Le quatrième argument de RECHERCHEX fixe ce qui s'affiche sans correspondance : renvoie l'offre en colonne D ou le mot Manquant Support Microsoft. RECHERCHEX n'existe pas dans Excel 2016 ni 2019. Là, RECHERCHEV fait la même chose en correspondance exacte : renvoie VRAI pour un identifiant manquant, car RECHERCHEV renvoie #N/A quand il ne trouve pas de correspondance exacte. L'identifiant doit se trouver dans la première colonne de la plage de recherche Support Microsoft.

Comment comparer deux exports chaque mois sans refaire les formules ?

Chargez les deux fichiers dans Power Query et fusionnez-les avec une jointure anti gauche. Elle ne garde que les lignes de la première table sans correspondance dans la seconde. Le mois suivant, vous remplacez les fichiers et vous actualisez.

Microsoft définit la jointure anti gauche comme celle qui n'amène que les lignes de la table de gauche sans lignes correspondantes dans la table de droite Microsoft Learn. Les étapes dans Excel :

  1. Chargez chaque fichier avec DonnéesObtenir des donnéesÀ partir d'un fichier, puis choisissez ChargerCharger dansCréer uniquement la connexion.
  2. Ouvrez la requête du mois dernier et sélectionnez AccueilFusionner des requêtes.
  3. Sous « Table de droite à fusionner », choisissez la requête de ce mois-ci, puis sélectionnez la colonne d'identifiant dans les deux tables.
  4. Dans « Type de jointure », choisissez , puis OK.

Le message en bas de la boîte de dialogue Fusionner indique combien de lignes de la première table sont retenues. C'est votre nombre de clients manquants, avant même de charger le résultat. Pour les nouveaux clients, refaites la fusion en inversant les deux tables.

La fusion garde aussi toutes les autres colonnes de la première table : le résultat montre les lignes complètes des clients partis, avec leur offre, leur pays et leur dernière commande. Si vos fichiers mensuels arrivent dans un même dossier, combinez-les d'abord avec l'import de dossier de Power Query, puis lancez la comparaison sur la table combinée.

Pourquoi Excel indique une valeur manquante alors qu'elle est dans les deux colonnes ?

Parce que les deux cellules ne contiennent pas la même valeur. Les causes habituelles : un espace en fin de cellule, un espace insécable copié depuis une page web, un identifiant stocké en nombre dans un export et en texte dans l'autre, ou une autre orthographe du même client.

Faites ces vérifications avant de croire une liste de valeurs manquantes :

SymptômeCause probableCorrection
RECHERCHEX ou Power Query ne trouve aucune ligneIdentifiants en nombre d'un côté, en texte de l'autreConvertir les deux colonnes dans le même type
Quelques noms semblent manquer mais sont présentsEspaces en trop ou insécables
NB.SI trouve, Power Query ne trouve pasMajuscules et minuscules différentesPasser les deux clés en minuscules dans Power Query
Même client sous deux nomsPas d'identifiant communComparer sur l'e-mail ou le numéro client, pas sur le nom

Les corrections pas à pas des espaces, des nombres stockés en texte et des dates sont dans nettoyer ses données dans Excel. Power Query respecte la casse Microsoft Learn, donc TransformerFormatminuscule sur les deux colonnes d'identifiant évite les faux manquants.

Un test rapide attrape la plupart des problèmes. Prenez un identifiant que vous savez présent dans les deux listes, cherchez-le dans chaque colonne avec Ctrl+F et vérifiez que NB.SI le trouve. Sinon, le nettoyage n'est pas fini.

Que faire de la liste une fois obtenue ?

Comptez-la, puis expliquez-la. Le nombre d'identifiants absents de la liste de ce mois, divisé par le total du mois dernier, donne le taux de churn client du mois. La liste elle-même vous dit qui appeler.

La méthode de calcul du taux de churn et les segments à comparer sont dans analyser le churn client. La même comparaison sert à confronter deux systèmes : des clients du CRM sans facture, ou des factures sans fiche CRM. Ce cas et ses pièges sont traités dans relier les données CRM et de facturation.

Gardez le résultat à côté des deux fichiers source, avec la date de chaque export. Quand on vous demandera au trimestre suivant pourquoi un client a été compté comme perdu, vous pourrez montrer les deux listes qui le disaient.

Où intervient Glow Reports ?

Pour une vérification ponctuelle, les formules ci-dessus suffisent. Glow Reports aide quand la comparaison alimente un rapport. Vous importez les deux fichiers, CSV ou Excel, et vous demandez en langage courant quels clients figurent dans le fichier du mois dernier et pas dans celui de ce mois-ci, et ce qu'ils représentaient.

L'IA écrit le SQL, construit les graphiques et rédige le texte. Le SQL apparaît sous chaque chiffre : vous voyez comment les deux fichiers ont été rapprochés et vous modifiez la requête si elle s'est appuyée sur la mauvaise colonne. Les rapports s'exportent en PDF et restent dans l'espace de travail. Le plan gratuit donne 300 crédits une fois, et un rapport coûte 100 crédits.

Questions fréquentes

Quelle est la méthode la plus simple pour comparer deux colonnes Excel ?

Une colonne d'aide avec NB.SI : à côté de la première colonne, recopiée vers le bas. VRAI signale les valeurs absentes de la seconde colonne. Elle marche dans toutes les versions d'Excel et dans Google Sheets.

Comment comparer deux fichiers Excel plutôt que deux colonnes ?

Copiez les deux listes sur une même feuille et utilisez les formules ci-dessus, ou chargez chaque fichier dans Power Query et fusionnez-les avec une jointure anti gauche. Power Query est le meilleur choix quand les fichiers reviennent chaque mois.

Peut-on comparer deux colonnes avec RECHERCHEV ?

Oui. renvoie VRAI quand la valeur de A2 n'est pas en colonne C. Mettez toujours FAUX en dernier argument, sinon RECHERCHEV renvoie une valeur approchée au lieu de signaler l'absence.

NB.SI distingue-t-il les majuscules des minuscules ?

Non. NB.SI considère « acme » et « ACME » comme la même valeur. Les fusions Power Query respectent la casse : passez les deux clés en minuscules si la casse ne compte pas pour vos données.

Pourquoi FILTRE renvoie #CALC! dans ma comparaison ?

Rien dans la première liste ne manque dans la seconde, et FILTRE n'a aucune valeur à afficher. Ajoutez un dernier argument comme « Aucun manquant » pour afficher un texte à la place de l'erreur.

El Mehdi Benane
Fondateur, Glow Reports

Je suis ingénieur produit et fondateur de Glow Reports. J'ai passé plus de 6 ans à construire des produits dans la tech, les données et la cybersécurité, pour des grands groupes, des startups et des PME. Dans la plupart des organisations, j'ai vu les données traitées comme un sujet technique, alors qu'elles guident les décisions. J'ai construit l'outil de reporting que je cherchais, pour que les dirigeants des petites et moyennes structures lisent leurs chiffres sans équipe data.

Importez les deux fichiers dans Glow Reports et demandez qui figure dans l'un et pas dans l'autre, avec le SQL derrière chaque chiffre.

À lire aussi