Comment comparer deux colonnes dans Excel (pour les correspondances et les différences)

Regardez la vidéo – Comparez deux colonnes dans Excel pour les correspondances et les différences

La seule requête que je reçois souvent est : « comment comparer deux colonnes dans Excel ? ».

Cela peut être fait de différentes manières, et la méthode à utiliser dépendra de la structure des données et de ce que l’utilisateur en attend.

Par exemple, vous pouvez comparer deux colonnes et rechercher ou mettre en évidence tous les points de données correspondants (qui se trouvent dans les deux colonnes), ou uniquement les différences (où un point de données se trouve dans une colonne et pas dans l’autre), etc.

Comme on me pose tellement de questions à ce sujet, j’ai décidé d’écrire ce didacticiel massif avec l’intention de couvrir la plupart (sinon tous) des scénarios possibles.

Si vous trouvez cela utile, transmettez-le à d’autres utilisateurs d’Excel.

Notez que les techniques de comparaison de colonnes présentées dans ce tutoriel ne sont pas les seules.

En fonction de votre ensemble de données, vous devrez peut-être modifier ou ajuster la méthode. Cependant, les principes de base resteraient les mêmes.

Si vous pensez qu’il y a quelque chose qui peut être ajouté à ce tutoriel, faites-le moi savoir dans la section commentaires

Comparer deux colonnes pour une correspondance exacte des lignes

Celui-ci est la forme la plus simple de comparaison. Dans ce cas, vous devez effectuer une comparaison ligne par ligne et identifier les lignes qui contiennent les mêmes données et celles qui n’en contiennent pas.

Exemple : comparer les cellules de la même ligne

Vous trouverez ci-dessous un ensemble de données dans lequel je dois vérifier si le nom de la colonne A est le même que celui de la colonne B.

Comparer les colonnes - ligne par ligne - ensemble de données

S’il y a une correspondance, j’ai besoin du résultat comme “VRAI”, et s’il ne correspond pas, alors j’ai besoin du résultat comme “FAUX”.

La formule ci-dessous ferait ceci:

=A2=B2

Comparer les listes dans Excel - les correspondances sont affichées comme VRAI

Exemple : comparer les cellules de la même ligne (à l’aide de la formule SI)

Si vous souhaitez obtenir un résultat plus descriptif, vous pouvez utiliser une simple formule SI pour renvoyer « Correspondance » lorsque les noms sont les mêmes et « Incompatibilité » lorsque les noms sont différents.

=IF(A2=B2,"Match","Mismatch")

Si formule pour comparer les colonnes dans Excel

Remarque : si vous souhaitez rendre la comparaison sensible à la casse, utilisez la formule SI suivante :

=IF(EXACT(A2,B2),"Match","Mismatch")

Avec la formule ci-dessus, ‘IBM’ et ‘ibm’ seraient considérés comme deux noms différents et la formule ci-dessus renverrait ‘Mismatch’.

Exemple : mettre en surbrillance les lignes avec les données correspondantes

Si vous souhaitez mettre en évidence les lignes qui ont des données correspondantes (au lieu d’obtenir le résultat dans une colonne séparée), vous pouvez le faire en utilisant la mise en forme conditionnelle.

Voici les étapes à suivre :

  1. Sélectionnez l’ensemble de données.
  2. Cliquez sur l’onglet « Accueil ».Cliquez sur l'onglet Accueil dans le ruban Excel
  3. Dans le groupe Styles, cliquez sur l’option “Mise en forme conditionnelle”.Cliquez sur Formatage conditionnel
  4. Dans la liste déroulante, cliquez sur « Nouvelle règle ».Cliquez sur l'option Nouvelle règle
  5. Dans la boîte de dialogue “Nouvelle règle de formatage”, cliquez sur “Utiliser une formule pour déterminer les cellules à formater”.Cliquez sur l'option Utiliser la formule
  6. Dans le champ formule, saisissez la formule : =$A1=$B1Formule pour comparer les colonnes dans la mise en forme conditionnelle
  7. Cliquez sur le bouton Format et spécifiez le format que vous souhaitez appliquer aux cellules correspondantes.Définir la mise en forme dans la mise en forme conditionnelle
  8. Cliquez sur OK.

Cela mettra en évidence toutes les cellules où les noms sont les mêmes dans chaque ligne.

Comparez deux colonnes et mettez en surbrillance les lignes correspondantes

Comparez deux colonnes et surlignez les correspondances

Si vous souhaitez comparer deux colonnes et mettre en évidence les données correspondantes, vous pouvez utiliser la fonctionnalité de duplication dans la mise en forme conditionnelle.

Notez que cela est différent de ce que nous avons vu en comparant chaque ligne. Dans ce cas, nous ne ferons pas de comparaison ligne par ligne.

Exemple : comparer deux colonnes et mettre en surbrillance les données correspondantes

Souvent, vous obtiendrez des ensembles de données où il y a des correspondances, mais ceux-ci peuvent ne pas être dans la même ligne.

Quelque chose comme indiqué ci-dessous :

Comparez deux colonnes et mettez en surbrillance macthes - ensemble de données

Notez que la liste de la colonne A est plus grande que celle de B. De plus, certains noms figurent dans les deux listes, mais pas dans la même ligne (comme IBM, Adobe, Walmart).

Si vous souhaitez mettre en évidence tous les noms d’entreprise correspondants, vous pouvez le faire en utilisant une mise en forme conditionnelle.

Voici les étapes à suivre :

  1. Sélectionnez l’ensemble des données.
  2. Cliquez sur l’onglet Accueil.
  3. Dans le groupe Styles, cliquez sur l’option “Mise en forme conditionnelle”.Cliquez sur Formatage conditionnel
  4. Passez le curseur sur l’option Mettre en évidence les règles de cellule.
  5. Cliquez sur Dupliquer les valeurs.Sélectionnez les valeurs en double dans la mise en forme conditionnelle
  6. Dans la boîte de dialogue Valeurs en double, assurez-vous que « Dupliquer » est sélectionné.Dupliquer dans la mise en forme conditionnelle
  7. Spécifiez le formatage.Spécifier la mise en forme dans la mise en forme conditionnelle
  8. Cliquez sur OK.

Les étapes ci-dessus vous donneraient le résultat indiqué ci-dessous.

Données correspondantes en surbrillance lors de la comparaison de listes dans Excel

Remarque : La règle de duplication de mise en forme conditionnelle n’est pas sensible à la casse. Ainsi, « Apple » et « pomme » sont considérés comme identiques et seraient mis en évidence en tant que doublons.

Exemple : comparer deux colonnes et mettre en évidence les données non concordantes

Si vous souhaitez mettre en évidence les noms présents dans une liste et pas dans l’autre, vous pouvez également utiliser la mise en forme conditionnelle pour cela.

  1. Sélectionnez l’ensemble des données.
  2. Cliquez sur l’onglet Accueil.
  3. Dans le groupe Styles, cliquez sur l’option “Mise en forme conditionnelle”.Cliquez sur Formatage conditionnel
  4. Passez le curseur sur l’option Mettre en évidence les règles de cellule.
  5. Cliquez sur Dupliquer les valeurs.Sélectionnez les valeurs en double dans la mise en forme conditionnelle
  6. Dans la boîte de dialogue Valeurs en double, assurez-vous que « Unique » est sélectionné.Sélectionnez Unique pour mettre en évidence les différences
  7. Spécifiez le formatage.Spécifiez la mise en forme pour mettre en évidence les différences dans deux colonnes
  8. Cliquez sur OK.

Cela vous donnera le résultat comme indiqué ci-dessous. Il met en évidence toutes les cellules qui ont un nom qui n’est pas présent sur l’autre liste.

Comparer deux colonnes et mettre en évidence les différences

Comparer deux colonnes et rechercher les points de données manquants

Si vous souhaitez identifier si un point de données d’une liste est présent dans l’autre liste, vous devez utiliser les formules de recherche.

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez identifier les entreprises présentes dans la colonne A mais pas dans la colonne B,

Comparez deux colonnes et mettez en surbrillance macthes - ensemble de données

Pour ce faire, je peux utiliser la formule RECHERCHEV suivante.

=ISERROR(VLOOKUP(A2,$B$2:$B$10,1,0))

Cette formule utilise la fonction RECHERCHEV pour vérifier si un nom de société dans A est présent dans la colonne B ou non. S’il est présent, il renverra ce nom de la colonne B, sinon il renverra une erreur #N/A.

Ces noms qui renvoient l’erreur #N/A sont ceux qui manquent dans la colonne B.

La fonction ISERROR renverrait TRUE si le résultat RECHERCHEV est une erreur et FALSE s’il ne s’agit pas d’une erreur.

comparer les listes et trouver les données manquantes

Si vous souhaitez obtenir une liste de tous les noms pour lesquels il n’y a pas de correspondance, vous pouvez filtrer la colonne de résultats pour obtenir toutes les cellules avec VRAI.

Vous pouvez également utiliser la fonction MATCH pour faire de même ;

=NOT(ISNUMBER(MATCH(A2,$B$2:$B$10,0)))

Remarque : Personnellement, je préfère utiliser la fonction Match (ou la combinaison INDEX/MATCH) au lieu de RECHERCHEV. Je le trouve plus souple et puissant. Vous pouvez lire la différence entre Vlookup et Index/Match ici.

Comparez deux colonnes et extrayez les données correspondantes

Si vous disposez de deux ensembles de données et que vous souhaitez comparer les éléments d’une liste à l’autre et récupérer le point de données correspondant, vous devez utiliser les formules de recherche.

Exemple : extraire les données correspondantes (exactes)

Par exemple, dans la liste ci-dessous, je souhaite récupérer la valeur d’évaluation de marché pour la colonne 2. Pour ce faire, je dois rechercher cette valeur dans la colonne 1, puis récupérer la valeur d’évaluation de marché correspondante.

Comparez deux listes dans Excel et récupérez les données correspondantes

Vous trouverez ci-dessous la formule qui fera cela :

=VLOOKUP(D2,$A$2:$B$14,2,0)

ou

=INDEX($A$2:$B$14,MATCH(D2,$A$2:$A$14,0),2)

Données d'appariement de recherche et d'extraction - évaluation boursière Excel

Exemple : extraire les données correspondantes (partiellement)

Si vous obtenez un ensemble de données où il y a une différence mineure dans les noms dans les deux colonnes, l’utilisation des formules de recherche ci-dessus ne fonctionnera pas.

Ces formules de recherche ont besoin d’une correspondance exacte pour donner le bon résultat. Il existe une option de correspondance approximative dans la fonction RECHERCHEV ou MATCH, mais elle ne peut pas être utilisée ici.

Supposons que vous ayez l’ensemble de données comme indiqué ci-dessous. Notez que certains noms ne sont pas complets dans la colonne 2 (tels que JPMorgan au lieu de JPMorgan Chase et Exxon au lieu d’ExxonMobil).

Tirer les données de correspondance - correspondance partielle

Dans un tel cas, vous pouvez utiliser une recherche partielle en utilisant des caractères génériques.

La formule suivante donnera le bon résultat dans ce cas :

=VLOOKUP("*"&D2&"*",$A$2:$B$14,2,0)

ou

=INDEX($A$2:$B$14,MATCH("*"&D2&"*",$A$2:$A$14,0),2)

Comparaison partielle dans les colonnes avec des caractères génériques

Dans l’exemple ci-dessus, l’astérisque

est un caractère générique qui peut représenter n’importe quel nombre de caractères. Lorsque la valeur de recherche est flanquée d’elle des deux côtés, toute valeur de la colonne 1 qui contient la valeur de recherche de la colonne 2 sera considérée comme une correspondance.

Par exemple, *Exxon* correspondrait à ExxonMobil (car * peut représenter n’importe quel nombre de caractères).

Vous aimerez peut-être également les conseils et didacticiels Excel suivants :