Comparaison de colonnes Excel : repérez les correspondances et les différences !

Comparez deux colonnes ligne par ligne

Ci-dessous se trouve un jeu de données (plage B2:C8) dans lequel je dois vérifier si les noms figurant dans la colonne B correspondent à ceux de la colonne C sur la même ligne.

Cette partie présente deux exemples expliquant comment comparer deux colonnes ligne par ligne

• Exemple 1 : Comparer les cellules sur la même ligne
• Exemple 2 : Comparer les cellules sur la même ligne dans Sensible à la casse

Exemple 1 : Comparer les cellules sur la même ligne

Généralement, pour comparer deux colonnes ligne par ligne afin de détecter des correspondances exactes, vous pouvez utiliser la formule suivante :

=B2=C2

Appuyez sur la touche Enter, puis faites glisser la poignée de recopie jusqu’à la cellule D8. Si la formule renvoie VRAI, les valeurs des deux colonnes sur la même ligne sont identiques ; si elle renvoie FAUX, elles sont différentes.

Vous pouvez également afficher des textes spécifiques pour indiquer les correspondances ou les écarts en utilisant la fonction SI, comme suit :

=IF(B2=C2,"Match","No match")

Le résultat peut ressembler à ceci :

Exemple 2 : Comparer les cellules sur la même ligne dans Sensible à la casse

Pour comparer deux colonnes ligne par ligne en tenant compte de la casse, utilisez une formule combinant les fonctions SI et EXACT.

IF(EXACT(B2,C2), "Match", "Mismatch")

Appuyez sur la touche Enter pour obtenir le premier résultat, puis faites glisser la poignée de recopie jusqu’à la cellule D8.

Dans la formule ci-dessus, remplacez librement les termes « Match » et « Mismatch » par vos propres descriptions.

Comparer plusieurs colonnes sur la même ligne

Parfois, vous pouvez avoir besoin de comparer plus de deux colonnes sur une même ligne, comme le jeu de données (plage B2:D7) illustré dans la capture d’écran ci-dessous. Cette section présente plusieurs méthodes pour comparer plusieurs colonnes.

Ici, le contenu est divisé en deux parties pour vous guider pas à pas dans la comparaison de plusieurs colonnes sur une même ligne.

• Exemple 1 : Comparez plusieurs colonnes et identifiez les correspondances dans toutes les cellules de la même ligne
• Exemple 2 : Comparez plusieurs colonnes et trouvez des correspondances entre deux cellules quelconques de la même ligne

Exemple 1 : Comparer plusieurs colonnes et trouver les correspondances dans toutes les cellules de la même ligne

Pour identifier des correspondances exactes entre les colonnes d’une même ligne, la formule ci-dessous vous sera utile.

=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")

Appuyez sur la touche Enter pour obtenir le premier résultat de comparaison, puis faites glisser la poignée de recopie jusqu’à la cellule E7.

Exemple 2 : Comparer plusieurs colonnes et trouver des correspondances entre deux cellules quelconques sur la même ligne

Parfois, vous souhaitez vérifier si deux colonnes quelconques sur la même ligne correspondent. Utilisez pour cela la formule SI ci-dessous.

=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")

Appuyez sur la touche Enter, puis faites glisser la poignée de recopie jusqu’à la cellule E7.

Comparez deux ou plusieurs colonnes ligne par ligne et mettez en évidence les correspondances ou les différences

Si vous souhaitez comparer deux colonnes ou plus et mettre en évidence leurs correspondances ou leurs différences, cette section vous présente deux méthodes pour réaliser ces opérations.

Il existe deux exemples pour comparer et mettre en évidence les correspondances et les différences

• Exemple 1 : Comparez deux colonnes et mettez en évidence les correspondances exactes dans toutes les cellules de la même ligne ou entre deux cellules quelconques de la même ligne
• Exemple 2 : Comparez deux colonnes et mettez en évidence les différences sur la même ligne

Exemple 1 : Comparer deux colonnes et mettre en évidence les correspondances complètes dans toutes les cellules de la même ligne ou entre deux cellules quelconques sur la même ligne

Pour mettre en évidence les correspondances dans toutes les cellules ou entre deux cellules quelconques d’une même ligne, utilisez la mise en forme conditionnelle.

1. Sélectionnez la plage que vous utilisez, puis cliquez sur Accueil > Utiliser la mise en forme conditionnelle > Nouvelle règle.

2. Dans la boîte de dialogue Nouvelle règle de mise en forme

1. Choisissez Utiliser une formule pour déterminer les cellules à mettre en formedans la section Sélectionner un type de règle
2. Saisissez la formule ci-dessous dans le champ de texte Mettre en forme les valeurs pour lesquelles cette formule est vraie.
   

   =COUNTIF($B2:$D2, $B2)=3

3. Cliquez sur Format.

3. Dans la boîte de dialogue Définir le format des cellules, choisissez une couleur de remplissage ou un autre format de cellule pour faire ressortir les lignes, puis cliquez sur OKOK pour fermer les boîtes de dialogue.

Désormais, seules les lignes dont toutes les cellules correspondent seront mises en évidence.

Exemple 2 : Comparer deux colonnes et mettre en évidence les différences sur la même ligne

Si vous souhaitez mettre en évidence les différences au sein d’une même ligne — c’est-à-dire comparer les cellules colonne par colonne et identifier celles qui diffèrent de la première colonne — vous pouvez utiliser la fonctionnalité intégrée d’Excel « Atteindre spécial ».

1. Sélectionnez la plage dans laquelle vous souhaitez mettre en évidence les différences ligne par ligne, puis cliquez sur Accueil > Rechercher et sélectionner > Atteindre spécial.

2. Dans la boîte de dialogue Atteindre spécial qui s’ouvre, choisissez l’option Différences ligne par ligne. Cliquez sur OK.

Les différences ligne par ligne ont maintenant été sélectionnées.

3. Maintenant, gardez les cellules sélectionnées, puis cliquez sur Accueil > Couleur de remplissage pour choisir une couleur dans le menu déroulant.

Comparez deux colonnes dans les cellules pour identifier les données uniques et les doublons

Dans cette section, l’ensemble de données (plage B2:C8) est présenté ci-dessous. Vous souhaitez identifier soit les valeurs communes aux colonnes B et C, soit celles qui n’apparaissent que dans la colonne B.

Cette section présente quatre méthodes distinctes pour comparer deux colonnes cellule par cellule : choisissez celle qui correspond le mieux à vos besoins.

• Exemple 1 : Comparez deux colonnes dans les cellules et affichez le résultat de la comparaison dans une autre colonne
• Exemple 2 : Comparez deux colonnes dans les cellules et sélectionnez ou mettez en évidence les données en double ou uniques à l’aide d’un outil pratique
• Exemple 3 : Comparez deux colonnes dans les cellules et mettez en évidence les données en double ou uniques
• Exemple 4 : Comparez deux colonnes dans les cellules et listez les doublons exacts dans une autre colonne

Exemple 1 : Comparer deux colonnes cellule par cellule et afficher le résultat de la comparaison dans une autre colonne

Vous pouvez ici utiliser une formule combinant les fonctions SI et NB.SI pour comparer deux colonnes et repérer les valeurs présentes dans la colonne B mais absentes de la colonne C.

=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")

Appuyez sur la touche Enter, puis faites glisser la poignée de recopie jusqu’à la cellule D8.

Exemple 2 : Comparer deux colonnes cellule par cellule et sélectionner ou mettre en évidence les données en double ou uniques à l’aide d’un outil pratique

Parfois, après avoir comparé deux colonnes, vous souhaitez effectuer d’autres actions sur les correspondances ou les différences, comme les sélectionner, les supprimer, les copier, etc. Dans ce cas, un outil pratique – Sélectionner les cellules identiques/différentes de Kutools pour Excel – vous permet de sélectionner instantanément les correspondances ou les différences pour simplifier les opérations suivantes, et peut même mettre directement en évidence ces valeurs.

Après avoir installé gratuitement Kutools pour Excel, cliquez sur Kutools > Sélectionner > Sélectionner les cellules identiques/différentes. Puis, dans la boîte de dialogue « Sélectionner les cellules identiques/différentes », procédez comme suit :

1. Dans les sections Rechercher les valeurs dans et Selon, sélectionnez séparément les deux colonnes à comparer.
2. Choisissez l’option Chaque ligne.
3. Choisissez Valeurs identiques ou Valeur différente selon vos besoins.
4. Indiquez si vous souhaitez colorer les valeurs sélectionnées, puis cliquez sur OK.

Une boîte de dialogue s’ouvre pour vous indiquer le nombre de valeurs trouvées ; cliquez sur OK pour la fermer. Les valeurs sont alors sélectionnées, et vous pouvez désormais les supprimer, les copier ou effectuer d’autres opérations.

Si vous cochez les cases Couleur d'arrière-plan de remplissage et Couleur de police de remplissage, le résultat s’affiche ainsi :

Exemple 3 : Comparer deux colonnes cellule par cellule et mettre en évidence les données en double ou uniques

La fonctionnalité « Utiliser la mise en forme conditionnelle » d’Excel est puissante : elle vous permet de comparer deux colonnes cellule par cellule et de mettre en évidence, selon vos besoins, les différences ou les correspondances.

1. Sélectionnez les deux colonnes que vous souhaitez comparer, puis cliquez sur Accueil > Utiliser la mise en forme conditionnelle > Règles de mise en évidence des cellules > Valeurs en double.

2. Dans la boîte de dialogue Valeurs en double qui s’ouvre, choisissez le format de mise en évidence souhaité dans la liste déroulante.

3. Cliquez sur OK. Les doublons présents dans les deux colonnes sont alors mis en évidence.

Exemple 4 : Comparer deux colonnes cellule par cellule et lister les doublons exacts dans une autre colonne

Si vous souhaitez lister les valeurs correspondantes dans une autre colonne après avoir comparé deux colonnes cellule par cellule dans Sensible à la casse, la macro ci-dessous peut vous aider.

1. Activez la feuille dans laquelle vous souhaitez comparer deux colonnes, puis appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Cliquez sur Insertion > Module dans la fenêtre Microsoft Visual Basic pour Applications.

3. Copiez ensuite le code ci-dessous et collez-le dans le nouveau module vierge.

VBA : Lister les doublons dans la colonne adjacente après comparaison de deux colonnes

Sub ExtendOffice_FindMatches()
'UpdatebyKutools
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
    MsgBox "Please select single column"
    GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
    MsgBox "Please select single column"
    GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
    For Each xRgF2 In xRgC2
        If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
    Next xRgF2
Next xRgF1
End Sub

4. Appuyez sur la touche F5 pour exécuter le code. Deux boîtes de dialogue s’ouvrent successivement afin que vous sélectionniez séparément les deux colonnes. Cliquez ensuite sur OKOK.

Les correspondances ont été automatiquement répertoriées dans la colonne située à droite des deux colonnes comparées.

Comparez deux listes et extrayez les données correspondantes

Nous vous présentons ici deux scénarios distincts pour comparer deux listes et extraire des données.

• Exemple 1 : Comparez deux colonnes et extrayez les données correspondant exactement
• Exemple 2 : Comparez deux colonnes et extrayez les données correspondant partiellement

Exemple 1 : Comparer deux colonnes et extraire les données correspondant exactement

Par exemple, vous disposez de deux tableaux et souhaitez comparer les colonnes B et E, puis rechercher les prix correspondants dans la colonne C pour les afficher dans la colonne F.

Voici quelques formules pratiques pour mener à bien cette tâche.

Dans la cellule F2 (la cellule où vous souhaitez insérer le Valeur de retour), utilisez l’une des formules ci-dessous :

=VLOOKUP(E2,$B$2:$C$8,2,0)

Ou

=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)

Appuyez sur la touche Enter : la première valeur apparaît aussitôt. Ensuite, faites glisser la poignée de recopie jusqu’à la cellule F6 pour extraire automatiquement toutes les valeurs.

Exemple 2 : Comparer deux colonnes et extraire les données correspondant partiellement

S’il existe de légères différences entre les deux colonnes comparées, comme le montre la capture d’écran ci-dessous, les méthodes précédentes ne seront pas efficaces.

Dans la cellule F2 (la cellule où vous souhaitez placer le Valeur de retour), utilisez l’une des formules suivantes :

=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)

Ou

=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)

Appuyez sur la touche Enter, et la première valeur a été trouvée. Ensuite, faites glisser la poignée de recopie vers le bas jusqu’à la cellule F6 : toutes les valeurs ont été extraites.

Comparez deux colonnes et identifiez les données manquantes

Supposons deux colonnes, la colonne B étant plus longue que la colonne C, comme le montre la capture d’écran ci-dessous. Comment identifier, par rapport à la colonne B, les données manquantes dans la colonne C ?

• Exemple 1 : Comparez deux colonnes et identifiez les données manquantes
• Exemple 2 : Identifiez les données manquantes et listez-les dans une autre colonne (à l’aide d’un outil pratique)
• Exemple 3 : Comparez deux colonnes et listez les données manquantes ci-dessous

Exemple 1 : Comparer deux colonnes et identifier les données manquantes

Si vous souhaitez uniquement identifier les données manquantes après avoir comparé deux colonnes, vous pouvez utiliser l’une des formules suivantes :

=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))

Ou

=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))

Appuyez sur la touche Enter, puis faites glisser la poignée de recopie jusqu’à la cellule D10. Désormais, si une donnée est présente à la fois dans la colonne B et dans la colonne C, la formule renvoie FAUX ; si elle n’est présente que dans la colonne B mais absente de la colonne C, la formule renvoie VRAI.

Exemple 2 : Identifier les données manquantes et les lister dans une autre colonne (à l’aide d’un outil pratique)

Si vous souhaitez effectuer une opération complémentaire sur les données manquantes après avoir comparé deux colonnes — par exemple, les lister dans une autre colonne ou les ajouter sous la colonne la plus courte —, découvrez un outil pratique : Sélectionner les cellules identiques/différentes de Kutools pour Excel.

Après avoir installé Kutools pour Excel, cliquez sur Kutools > Sélectionner > Sélectionner les cellules identiques/différentes. Puis, dans la boîte de dialogue Sélectionner les cellules identiques/différentes, procédez comme suit :

1. Dans la section Rechercher les valeurs, sélectionnez la colonne la plus longue contenant la liste complète.
2. Dans la section Selon, choisissez la colonne la plus courte, dont certaines données sont manquantes.
3. Choisissez l’option Chaque ligne.
4. Choisissez l’option Valeur différente. Cliquez sur OK.

Une boîte de dialogue s’ouvre pour vous indiquer le nombre de données manquantes. Cliquez sur OK pour la fermer. Les données manquantes sont alors sélectionnées.

Vous pouvez désormais appuyer sur les touches Ctrl + C pour copier les données manquantes sélectionnées, puis les coller en appuyant sur Ctrl + V, sous la colonne la plus courte ou dans une nouvelle colonne, selon vos besoins.

Exemple 3 : Comparer deux colonnes et lister les données manquantes en dessous

Si vous souhaitez lister les données manquantes sous la colonne la plus courte après avoir comparé deux colonnes, la formule matricielle INDEX peut vous aider.

Dans la cellule située sous la colonne la plus courte, par exemple la cellule C7, saisissez la formule suivante :

=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))

Appuyez sur les touches Shift + Ctrl + Enter pour obtenir la première donnée manquante, puis faites glisser la poignée de recopie vers le bas jusqu’à l’apparition de la valeur d’erreur #N/A.

Vous pouvez ensuite supprimer la valeur d’erreur, et toutes les données manquantes seront listées sous la colonne la plus courte.

Comparez deux colonnes avec des caractères génériques

Supposons qu’une liste de données se trouve dans la colonne B, et que vous souhaitiez compter les cellules contenant « Apple » ou « Candy » dans la colonne D, comme illustré dans la capture d’écran ci-dessous :

Pour compter les cellules contenant une ou plusieurs valeurs spécifiques, vous pouvez utiliser une formule avec des caractères génériques pour résoudre ce problème.

=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))

Appuyez sur les touches Shift + Ctrl + Enter pour obtenir le premier résultat, puis faites glisser la poignée de recopie jusqu’à la cellule F8.

Si vous souhaitez compter le nombre total de cellules contenant les valeurs indiquées dans la colonne D, utilisez la formule suivante sous la cellule F8 :

Comparez deux colonnes (dates) selon qu’elles sont supérieures ou inférieures

Si deux colonnes contiennent des dates, comme le montre la capture d’écran ci-dessous, vous voudrez peut-être comparer, ligne par ligne, laquelle est la plus récente.

• Exemple 1 : Comparez deux colonnes selon qu’elles sont supérieures ou inférieures
• Exemple 2 : Comparez deux colonnes selon qu’elles sont supérieures ou inférieures, puis appliquez une mise en forme

Exemple 1 : Comparer deux colonnes pour savoir si l’une est supérieure ou inférieure à l’autre

Vous pouvez utiliser une formule simple pour déterminer rapidement si la date 1 est postérieure à la date 2 sur chaque ligne.

=IF(B2>C2,"Yes","No")

Appuyez sur la touche Enter pour obtenir le premier résultat de comparaison, puis faites glisser la poignée de recopie jusqu’à la cellule C6 afin d’obtenir l’ensemble des résultats.

Exemple 2 : Comparer deux colonnes (supérieur/inférieur) puis appliquer une mise en forme

Si vous souhaitez mettre en évidence les cellules de la colonne Date 1 qui sont postérieures à la Date 2, vous pouvez utiliser la fonctionnalité Utiliser la mise en forme conditionnelle d’Excel.

1. Sélectionnez les dates de la colonne B (Date1), puis cliquez sur Accueil > Utiliser la mise en forme conditionnelle > Nouvelle règle.

2. Dans la boîte de dialogue Nouvelle règle de mise en forme, sélectionnez Utiliser une formule pour déterminer les cellules à mettre en forme dans la section Sélectionner un type de règle, puis saisissez la formule

 =$B2>$C2

dans la zone de texte intitulée Mettre en forme les valeurs pour lesquelles cette formule est vraie.

=$B2<$C2.

3. Cliquez sur le bouton Format pour ouvrir la boîte de dialogue « Définir le format des cellules », puis choisissez le type de format souhaité. Cliquez sur OK.

4. Les cellules de la colonne Date1 postérieures à celles de la colonne Date2 sont désormais mises en évidence.

Comparez deux colonnes et comptez les correspondances ou les différences

L’ensemble de données ci-dessous illustre la comparaison et le comptage des correspondances ou des différences.

La formule SOMMEPROD permet de compter rapidement les correspondances entre deux colonnes.

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))

Appuyez sur la touche Enter pour obtenir votre résultat.

Pour découvrir d’autres méthodes permettant de compter les correspondances et les différences, consultez cette page :Compter toutes les correspondances / doublons entre deux colonnes dans Excel

Comparez deux plages

Vous savez désormais comment comparer deux colonnes grâce aux méthodes présentées ci-dessus. Toutefois, dans certains cas, vous souhaiterez comparer deux plages (c’est-à-dire deux séries comportant plusieurs colonnes). Vous pouvez certes appliquer les méthodes précédentes — formules ou mise en forme conditionnelle — colonne par colonne, mais voici un outil bien plus pratique : Kutools pour Excel, qui permet de résoudre ce type de tâche efficacement, sans aucune formule.

• Exemple 1 : Comparez deux plages cellule par cellule
• Exemple 2 : Comparez deux plages lorsque les données sont dans le même ordre

Exemple 1 : Comparer deux plages cellule par cellule

Voici deux plages à comparer cellule par cellule. Vous pouvez utiliser l’utilitaire Sélectionner les cellules identiques/différentes de Kutools pour Excel pour effectuer cette opération.

Après avoir installé gratuitement Kutools pour Excel, cliquez sur Kutools > Sélectionner > Sélectionner les cellules identiques/différentes. Dans la boîte de dialogue Sélectionner les cellules identiques/différentes qui s’ouvre, procédez comme suit :

1. Dans la section Rechercher les valeurs dans, sélectionnez la plage dans laquelle vous souhaitez identifier les correspondances ou les différences après avoir comparé les deux plages.
2. Dans la section Selon, choisissez l’autre plage utilisée comme référence pour la comparaison.
3. Dans la section Basé sur, sélectionnez Cellule unique.
4. Dans la section Rechercher, choisissez le type de cellules que vous souhaitez sélectionner ou mettre en évidence.
5. Dans la section Traitement des résultats sélectionnés, vous pouvez mettre en évidence les cellules en remplissant la couleur de fond ou en modifiant la couleur de la police ; si vous ne souhaitez pas appliquer de mise en évidence, laissez les cases décochées. Cliquez sur OK.

Une boîte de dialogue s’ouvre et indique le nombre de cellules ou de lignes sélectionnées. Cliquez sur OK pour la fermer.

• Sélection et mise en évidence des valeurs uniques
  
• Sélection et mise en évidence des Valeurs en double
  

Exemple 2 : Comparer deux plages pour vérifier si les données sont dans le même ordre

Supposons que la plage F2:H7 constitue un modèle. Vous souhaitez maintenant vérifier si les données de la plage B2:D7 respectent le bon ordre par rapport à la plage F2:H7.

Dans ce cas, la fonction Comparer les cellules de Kutools pour Excel peut vous aider.

Après avoir installé gratuitement Kutools pour Excel, cliquez sur Kutools > Comparer les cellules. Puis, dans la boîte de dialogue Comparer les cellules, configurez comme suit :

1. Saisissez les deux plages respectivement dans les zones Rechercher les valeurs dans et Selon.
2. Dans la section Rechercher, choisissez le type de cellules que vous souhaitez mettre en évidence.
3. Dans la section type de mise en évidence, sélectionnez Traitement des résultats sélectionnés, puis cliquez sur OK.

Une boîte de dialogue s’ouvre et affiche le nombre de cellules sélectionnées. Cliquez sur OK pour la fermer. Les cellules différentes de celles de l’autre plage sont désormais sélectionnées et mises en évidence.

Les informations ci-dessus expliquent en détail comment comparer des colonnes dans Excel. J’espère qu’elles vous seront utiles et vous feront gagner du temps ! Pour découvrir davantage d’astuces Excel inestimables capables de transformer votre traitement de données, plongez-vous ici.