Regarder la vidéo - Comparez deux colonnes dans Excel pour les correspondances et les différences
La seule requête que je reçois beaucoup 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 des 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 où je dois vérifier si le nom de la colonne A est le même que celui de la colonne B ou non.
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
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 « Discordance » lorsque les noms sont différents.
=SI(A2=B2,"Correspondance","Discordance")
Remarque : Si vous souhaitez rendre la comparaison sensible à la casse, utilisez la formule SI suivante :
=SI(EXACT(A2,B2),"Correspondance","Discordance")
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 :
- Sélectionnez l'ensemble de données complet.
- Cliquez sur l'onglet « Accueil ».
- Dans le groupe Styles, cliquez sur l'option "Mise en forme conditionnelle".
- Dans la liste déroulante, cliquez sur « Nouvelle règle ».
- Dans la boîte de dialogue "Nouvelle règle de formatage", cliquez sur "Utiliser une formule pour déterminer les cellules à formater".
- Dans le champ formule, saisissez la formule : =$A1=$B1
- Cliquez sur le bouton Format et spécifiez le format que vous souhaitez 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 surbrillance 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 :
Notez que la liste de la colonne A est plus grande que celle de 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 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 :
- 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 Dupliquer les valeurs.
- Dans la boîte de dialogue Valeurs en double, assurez-vous que « Dupliquer » est sélectionné.
- Spécifiez le formatage.
- Cliquez sur OK.
Les étapes ci-dessus vous donneraient le résultat comme indiqué ci-dessous.
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.
- 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 Dupliquer les valeurs.
- Dans la boîte de dialogue Valeurs en double, assurez-vous que « Unique » est sélectionné.
- Spécifiez le formatage.
- 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 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,
Pour ce faire, je peux utiliser la formule RECHERCHEV suivante.
=ERREUR(RECHERCHEV(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.
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;
=PAS(ESTNUMÉRO(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 VLOOKUP. 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 avez 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.
Vous trouverez ci-dessous la formule qui le fera :
=RECHERCHEV(D2,$A$2:$B$14,2,0)
ou alors
=INDICE($A$2:$B$14,MATCH(D2,$A$2:$A$14,0),2)
Exemple : extraire les données correspondantes (partiellement)
Si vous obtenez un ensemble de données où il existe 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).
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 :
=RECHERCHEV("*"&D2&"*",$A$2:$B$14,2,0)
ou alors
=INDICE($A$2:$B$14,MATCH("*"&D2&"*",$A$2:$A$14,0),2)
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 aussi les conseils et didacticiels Excel suivants :
- Comment comparer deux feuilles Excel (pour les différences)
- Comment mettre en surbrillance les cellules vides dans Excel
- Mettez en surbrillance CHAQUE autre LIGNE dans Excel.
- Filtre avancé Excel : un guide complet avec des exemples.
- Mettez en surbrillance les lignes en fonction d'une valeur de cellule dans Excel.
