Comment comparer deux colonnes et trouver les valeurs manquantes dans les tableurs

Préparer vos données pour une comparaison propre

Avant de comparer, assurez-vous que les deux colonnes sont propres et cohérentes. Supprimez les espaces supplémentaires à l'aide de la fonction TRIM, vérifiez les zéros non significatifs ou les caractères cachés, et décidez si la comparaison doit être sensible à la casse. Trier chaque colonne par ordre alphabétique n'est pas strictement nécessaire mais peut aider à vérifier visuellement les résultats plus tard.

Les cellules vides peuvent entraîner des résultats trompeurs. Décidez à l'avance comment les traiter : comme des valeurs manquantes, des données légitimes, ou à exclure. De plus, s'il existe des doublons dans une colonne, ils peuvent devoir être traités séparément, car un même élément manquant peut apparaître plusieurs fois et fausser le décompte.

1. Copiez les deux colonnes dans des colonnes adjacentes (par exemple, colonne A et colonne B) dans une seule feuille.
2. Utilisez la fonction TRIM dans une colonne auxiliaire pour supprimer les espaces accidentels : =TRIM(A1).
3. Convertissez les types de données si nécessaire (par exemple, les nombres stockés sous forme de texte). Utilisez les fonctions VALUE ou TEXT.
4. Étiquetez clairement les colonnes de comparaison et sauvegardez une copie des données d'origine.

Utiliser VLOOKUP ou XLOOKUP pour trouver les valeurs manquantes

VLOOKUP et XLOOKUP peuvent tester si une valeur de la première colonne existe dans la seconde. XLOOKUP est disponible dans Google Sheets et dans les versions actuelles d'Excel ; les installations Excel plus anciennes peuvent utiliser VLOOKUP, MATCH ou INDEX-MATCH.

Pour une vérification d'appartenance, renvoyez la valeur de recherche elle-même et utilisez le comportement de non-trouvé de la fonction ou IFERROR pour afficher « Missing ». Ces formules renvoient un résultat par ligne d'entrée et ne prouvent pas une relation biunivoque lorsque des clés en double existent.

• VLOOKUP : =IFERROR(VLOOKUP(A2, B$2:B$100, 1, FALSE), "Missing")
• XLOOKUP : =IFERROR(XLOOKUP(A2, B$2:B$100, B$2:B$100), "Missing")
• Une erreur #N/A signifie que la valeur n'est pas trouvée ; IFERROR la convertit en une étiquette lisible.

=IFERROR(VLOOKUP(A2, B$2:B$100, 1, FALSE), "Missing")

=IFERROR(XLOOKUP(A2, B$2:B$100, B$2:B$100), "Missing")

Utiliser INDEX-MATCH pour plus de flexibilité

INDEX-MATCH est une alternative flexible à VLOOKUP car la plage de retour n'a pas besoin de se trouver à droite de la plage de recherche. Elle est également utile dans les classeurs qui ne disposent pas de XLOOKUP.

Pour un indicateur de valeur manquante, MATCH seul suffit ; enveloppez-le avec ISNA ou IFERROR. Les performances dépendent du classeur, de la taille de la plage, de la conception de la formule et de la version d'Excel, donc ne supposez pas qu'un modèle de recherche est toujours plus rapide.

• Formule : =IFERROR(INDEX(B$2:B$100, MATCH(A2, B$2:B$100, 0)), "Missing")
• La fonction MATCH renvoie la position relative d'une valeur dans une plage.
• INDEX renvoie la valeur réelle de la deuxième colonne à cette position. Si elle n'est pas trouvée, IFERROR renvoie "Missing".

=IFERROR(INDEX(B$2:B$100, MATCH(A2, B$2:B$100, 0)), "Missing")

Mise en forme conditionnelle pour mettre en évidence les différences

Pour un aperçu visuel, la mise en forme conditionnelle peut mettre en surbrillance les cellules d'une colonne qui n'apparaissent pas dans l'autre. Cette méthode est utile lorsque vous souhaitez repérer les valeurs manquantes en un coup d'œil sans ajouter de colonnes auxiliaires. Excel et Google Sheets prennent tous deux en charge cela avec des formules personnalisées.

Appliquez une règle basée sur une formule. Par exemple, pour mettre en évidence les valeurs de la colonne A qui ne se trouvent pas dans la colonne B, sélectionnez A2:A100 et entrez une formule COUNTIF ou MATCH qui renvoie TRUE lorsque la valeur est manquante.

1. Sélectionnez la plage dans la colonne A que vous souhaitez formater (par exemple, A2:A100).
2. Accédez à Accueil > Mise en forme conditionnelle > Nouvelle règle (Excel) ou Format > Mise en forme conditionnelle (Sheets).
3. Choisissez « Utiliser une formule pour déterminer les cellules à mettre en forme ».
4. Entrez une formule comme =COUNTIF(B$2:B$100, A2)=0 et définissez une couleur de remplissage.
5. Confirmez et appliquez. Les cellules de la colonne A qui sont absentes de la colonne B seront mises en évidence.

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

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

Gérer les doublons dans une ou les deux colonnes

Les doublons peuvent obscurcir la comparaison. Si votre liste principale contient plusieurs occurrences de la même valeur, une formule naïve traitera chaque occurrence comme un élément distinct, signalant potentiellement plusieurs fois la même valeur manquante. De même, les doublons dans la liste de recherche ne provoquent pas d'erreurs mais peuvent produire des résultats inattendus si vous attendez une correspondance biunivoque.

Pour gérer les doublons, décidez d'abord s'ils sont significatifs. S'ils doivent être ignorés, utilisez un COUNTIF pour signaler les doublons avant de comparer. Ensuite, supprimez ou regroupez les lignes en double. Alternativement, pour trouver les valeurs uniques manquantes, utilisez une liste unique comme colonne principale.

1. Insérez une colonne auxiliaire à côté de votre liste principale avec =COUNTIF(A$2:A2, A2)>1 pour marquer les doublons.
2. Filtrez ou triez pour examiner et supprimer les entrées en double si approprié.
3. Utilisez la fonction UNIQUE (Excel 365/2021, Google Sheets) pour créer une liste dédoublonnée pour la comparaison.
4. Exécutez ensuite les formules de comparaison sur la liste unique.

=COUNTIF(A$2:A2, A2)>1

Gérer les cellules vides

Les cellules vides dans l'une ou l'autre colonne nécessitent une manipulation prudente. Dans de nombreuses formules, une cellule vide est traitée comme un zéro ou une chaîne vide, ce qui peut provoquer des correspondances fausses ou des indicateurs de manquant. Par exemple, si les deux colonnes ont des cellules vides dans la même ligne, VLOOKUP peut les faire correspondre comme équivalentes, même si une cellule vide peut ne pas représenter une valeur valide.

Pour gérer les cellules vides, prétraitez les données en remplaçant les cellules vides par un indicateur (par exemple, « BLANK ») ou en utilisant une condition IF pour exclure les cellules vides de la comparaison. Alternativement, utilisez des formules qui vérifient explicitement les cellules vides avec ISBLANK.

1. Décidez d'une stratégie : traiter les cellules vides comme une valeur valide à comparer ou les exclure.
2. Si vous traitez les cellules vides comme valides, remplacez-les par un indicateur cohérent : =IF(A1="", "BLANK", A1).
3. Si vous les excluez, filtrez les lignes où l'une ou l'autre colonne est vide avant d'effectuer la comparaison.
4. Utilisez une condition IF dans votre formule : =IF(OR(A2="", B2=""), "Exclude", primary_comparison).

=IF(A2="", "BLANK", A2)

Valider vos résultats et éviter les faux positifs

La validation est cruciale. Les faux positifs peuvent provenir d'espaces cachés, de différences de casse ou de différences de type de données. Après avoir appliqué votre comparaison, vérifiez un échantillon des éléments signalés en les vérifiant manuellement. Utilisez des filtres pour isoler les valeurs manquantes et confirmez qu'elles ne sont pas présentes en raison de problèmes de formatage.

Une étape de validation pratique consiste à utiliser un tableau croisé dynamique pour compter les occurrences de chaque valeur dans la colonne principale et à comparer avec les comptes de la colonne de recherche. Auditez également quelques entrées en recherchant le texte exact dans la colonne source avec Ctrl+F ou Rechercher. Cette vérification supplémentaire garantit que votre liste de manquants est précise avant d'agir.

1. Ajoutez une colonne temporaire qui supprime les espaces et convertit en casse cohérente à l'aide de UPPER et TRIM.
2. Comparez manuellement un échantillon des entrées signalées : mettez-en une en surbrillance, copiez la valeur et recherchez-la dans la colonne de recherche.
3. Utilisez un tableau croisé dynamique : comptez chaque valeur dans la colonne principale et également dans la colonne de recherche, puis comparez les comptes.
4. Si vous utilisez un outil comme CompareTwoLists, notez qu'il s'exécute entièrement dans votre navigateur et affiche clairement les ensembles correspondants et non correspondants.

=UPPER(TRIM(A2))

=UPPER(TRIM(B2))

=IF(COUNTIF($D$2:$D$100,C2)>0,"Match","Missing")