Comment comparer deux colonnes dans Excel (pour les correspondances et les différences)
Voir la vidéo – Comparer deux colonnes dans Excel pour les correspondances et les différences
La question que je reçois souvent est – ‘comment comparer deux colonnes dans Excel ?’.
Cela peut être fait de plusieurs façons différentes, et la méthode à utiliser dépendra de la structure de données et de ce que l’utilisateur en attend.
Par exemple, vous pouvez vouloir comparer deux colonnes et trouver ou mettre en évidence tous les points de données correspondants (qui sont dans les deux colonnes), ou seulement les différences (où un point de données est dans une colonne et pas dans l’autre), etc.
Puisque l’on me pose souvent cette question, j’ai décidé d’écrire ce tutoriel massif avec l’intention de couvrir la plupart (sinon tous) les scénarios possibles.
Si vous trouvez cela utile, faites-le passer à d’autres utilisateurs d’Excel.
Ce tutoriel couvre :
Notez que les techniques de comparaison de colonnes présentées dans ce tutoriel ne sont pas les seules.
Selon votre ensemble de données, vous devrez peut-être changer 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 des commentaires
Comparer deux colonnes pour une correspondance exacte des lignes
Celle-ci est la forme la plus simple de comparaison. Dans ce cas, vous devez faire une comparaison ligne par ligne et identifier les lignes qui ont les mêmes données et celles qui ne le sont pas.
Exemple : Comparer les cellules dans la même rangée
Vous trouverez ci-dessous un ensemble de données où je dois vérifier si le nom dans la colonne A est le même dans la colonne B ou non.
S’il y a une correspondance, j’ai besoin du résultat comme « VRAI », et si ne correspond pas, alors j’ai besoin du résultat comme « FAUX ».
La formule ci-dessous ferait cela:
=A2=B2
Exemple : Comparer les cellules de la même rangée (à l’aide de la formule IF)
Si vous voulez obtenir un résultat plus descriptif, vous pouvez utiliser une simple formule IF pour retourner « Match » lorsque les noms sont identiques et « Mismatch » lorsque les noms sont différents.
=IF(A2=B2,"Match","Mismatch")
Note : Dans le cas où vous voulez rendre la comparaison sensible à la casse, utilisez la formule IF 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 évidence les lignes avec des données correspondantes
Si vous voulez 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 pour le faire :
- Sélectionnez l’ensemble de données entier.
- Cliquez sur l’onglet ‘Accueil’.
- Dans le groupe Styles, cliquez sur l’option ‘Formatage conditionnel’.
- Dans la liste déroulante, cliquez sur ‘Nouvelle règle’.
- Dans la boîte de dialogue ‘Nouvelle règle de formatage’, cliquez sur l’option ‘Utiliser une formule pour déterminer les cellules à formater’.
- Dans le champ de formule, entrez la formule : =$A1=$B1
- Cliquez sur le bouton Format et spécifiez le format que vous voulez appliquer aux cellules correspondantes.
- Cliquez sur OK.
Cela mettra en évidence toutes les cellules où les noms sont les mêmes dans chaque ligne.
Comparer deux colonnes et mettre en évidence les correspondances
Si vous voulez comparer deux colonnes et mettre en évidence les données correspondantes, vous pouvez utiliser la fonctionnalité de duplication dans le formatage conditionnel.
Notez que cela est différent de ce que nous avons vu en comparant chaque ligne. Dans ce cas, nous ne ferons pas une comparaison ligne par ligne.
Exemple : Comparer deux colonnes et mettre en évidence les données correspondantes
Souvent, vous aurez des ensembles de données où il y a des correspondances, mais celles-ci peuvent ne pas être dans la même ligne.
Quelque chose comme indiqué ci-dessous :
Notez que la liste dans la colonne A est plus grande que celle dans B. De plus, certains noms sont présents dans les deux listes, mais pas dans la même ligne (comme IBM, Adobe, Walmart).
Si vous voulez mettre en évidence tous les noms de société correspondants, vous pouvez le faire en utilisant le formatage conditionnel.
Voici les étapes pour le faire:
- Sélectionnez l’ensemble des données.
- Cliquez sur l’onglet Accueil.
- Dans le groupe Styles, cliquez sur l’option ‘Mise en forme conditionnelle’.
- Passez le curseur sur l’option Mettre en évidence les règles de cellule.
- Cliquez sur Valeurs dupliquées.
- Dans la boîte de dialogue Valeurs dupliquées, assurez-vous que ‘Dupliquer’ est sélectionné.
- Spécifiez le formatage.
- Cliquez sur OK.
Les étapes ci-dessus vous donneraient le résultat indiqué ci-dessous.
Note : la règle de duplication du formatage conditionnel n’est pas sensible à la casse. Ainsi, ‘Apple’ et ‘apple’ sont considérés comme identiques et seraient mis en évidence comme des doublons.
Exemple : Comparer deux colonnes et mettre en évidence les données non concordantes
Dans le cas où vous voulez mettre en évidence les noms qui sont présents dans une liste et pas dans l’autre, vous pouvez utiliser le formatage conditionnel pour cela aussi.
- Sélectionnez l’ensemble des données.
- Cliquez sur l’onglet Accueil.
- Dans le groupe Styles, cliquez sur l’option ‘Formatage conditionnel’.
- Passez le curseur sur l’option ‘Règles de cellules en surbrillance’.
- Cliquez sur ‘Valeurs dupliquées’.
- Dans la boîte de dialogue ‘Valeurs dupliquées’, assurez-vous que ‘Unique’ est sélectionné.
- Spécifiez le formatage.
- Cliquez sur OK.
Ceci vous donnera le résultat 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 trouver les points de données manquants
Si vous voulez 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 avez un ensemble de données comme indiqué ci-dessous et que vous voulez identifier les sociétés qui sont présentes dans la colonne A mais pas dans la colonne B,
Pour ce faire, je peux utiliser la formule VLOOKUP suivante.
=ISERROR(VLOOKUP(A2,$B:$B,1,0))
Cette formule utilise la fonction VLOOKUP 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, elle renverra ce nom de la colonne B, sinon elle renverra une erreur #N/A.
Ces noms qui renvoient l’erreur #N/A sont ceux qui manquent dans la colonne B.
La fonctionISERROR renverrait VRAI s’il y a le résultat du VLOOKUP est une erreur et FAUX si ce n’est pas une erreur.
Si vous voulez obtenir une liste de tous les noms où il n’y a pas de correspondance, vous pouvez filtrer la colonne de résultat pour obtenir toutes les cellules avec TRUE.
Vous pouvez également utiliser la fonction MATCH pour faire la même chose;
=NOT(ISNUMBER(MATCH(A2,$B:$B,0)))
Note : Personnellement, je préfère utiliser la fonction Match (ou la combinaison INDEX/MATCH) plutôt que VLOOKUP. Je la trouve plus flexible et plus puissante. Vous pouvez lire la différence entre Vlookup et Index/Match ici.
Comparer deux colonnes et extraire les données correspondantes
Si vous avez deux ensembles de données et que vous voulez comparer les éléments d’une liste à l’autre et extraire le point de données correspondant, vous devez utiliser les formules de lookup.
Exemple : Extraire les données correspondantes (exactes)
Par exemple, dans la liste ci-dessous, je veux extraire la valeur d’évaluation du marché pour la colonne 2. Pour ce faire, je dois rechercher cette valeur dans la colonne 1, puis récupérer la valeur de valorisation du marché correspondante.
Voici la formule qui permettra de faire cela :
=VLOOKUP(D2,$A:$B,2,0)
ou
=INDEX($A:$B,MATCH(D2,$A:$A,0),2)
Exemple : Tirer les données correspondantes (partielles)
Dans le cas où vous obtenez un ensemble de données où il y a une différence mineure dans les noms des deux colonnes, l’utilisation des formules de recherche présentées ci-dessus ne va pas fonctionner.
Ces formules de recherche nécessitent une correspondance exacte pour donner le bon résultat. Il existe une option de correspondance approximative dans la fonction VLOOKUP 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 qu’il y a des noms qui ne sont pas complets dans la colonne 2 (comme JPMorgan au lieu de JPMorgan Chase et Exxon au lieu d’ExxonMobil).
Dans un tel cas, vous pouvez utiliser une recherche partielle en utilisant des caractères génériques.
La formule suivante donnera est le bon résultat dans ce cas:
=VLOOKUP("*"&D2&"*",$A:$B,2,0)
ou
=INDEX($A:$B,MATCH("*"&D2&"*",$A:$A,0),2)
Dans l’exemple ci-dessus, l’astérisque (*) est un caractère générique qui peut représenter un nombre quelconque de caractères. Lorsque la valeur de recherche est flanquée de ce caractère des deux côtés, toute valeur de la colonne 1 qui contient la valeur de recherche de la colonne 2 serait considérée comme une correspondance.
Par exemple, *Exxon* serait une correspondance pour ExxonMobil (car * peut représenter un nombre quelconque de caractères).
Vous aimerez peut-être aussi les astuces Excel &Tutoriels suivants :
- Comment comparer deux feuilles Excel (pour les différences)
- Comment mettre en évidence les cellules vides dans Excel.
- Mettre en évidence TOUT autre rangée dans Excel.
- Filtre avancé Excel : Un guide complet avec des exemples.
- Mettre en évidence les lignes basées sur la valeur d’une cellule en Excel.
.