Comment comparer 2 colonnes dans Excel et identifier les différences ?

Comparer 2 colonnes dans Excel : Techniques et Astuces

Comparer deux colonnes dans Excel est une compétence essentielle pour de nombreux utilisateurs, des analystes de données aux gestionnaires de projets. Que vous ayez besoin d'identifier des doublons, de vérifier des mises à jour, ou simplement de comparer des listes, Excel offre plusieurs outils pour vous aider. Nous allons explorer différentes méthodes, allant des formules simples à la mise en forme conditionnelle, pour vous permettre de comparer vos données efficacement.

1. Comparaison simple avec la fonction SI

La fonction SI est l'une des fonctions les plus fondamentales d'Excel, et elle peut être utilisée pour comparer deux colonnes ligne par ligne. Voici comment:

1. Ouvrez votre feuille Excel contenant les deux colonnes que vous souhaitez comparer.
2. Insérez une nouvelle colonne à côté de vos colonnes à comparer. Par exemple, si vous comparez les colonnes A et B, insérez une colonne C.
3. Dans la première cellule de la nouvelle colonne (C2 par exemple), entrez la formule suivante : =SI(A2=B2;"Identique";"Différent")
4. Appuyez sur Entrée. La cellule affichera "Identique" si les valeurs dans A2 et B2 sont les mêmes, et "Différent" sinon.
5. Faites glisser la poignée de recopie (le petit carré en bas à droite de la cellule) vers le bas pour appliquer la formule à toutes les lignes.

Cette méthode est simple et rapide pour une comparaison visuelle. Cependant, elle ne vous indique pas quelles sont les différences, seulement qu'il y en a.

2. Identifier les différences spécifiques

Si vous souhaitez connaître les valeurs spécifiques qui diffèrent, vous pouvez modifier la formule SI pour afficher les valeurs elles-mêmes.

1. Suivez les étapes 1 et 2 de la méthode précédente.
2. Dans la première cellule de la nouvelle colonne (C2 par exemple), entrez la formule suivante : =SI(A2=B2;"Identique";A2&" est différent de "&B2)
3. Appuyez sur Entrée.
4. Faites glisser la poignée de recopie pour appliquer la formule à toutes les lignes.

Cette formule affichera "Identique" si les valeurs sont les mêmes, et sinon, elle affichera un message indiquant les deux valeurs différentes.

3. Utiliser la mise en forme conditionnelle pour mettre en évidence les différences

La mise en forme conditionnelle est un outil puissant pour visualiser rapidement les différences. Voici comment l'utiliser :

1. Sélectionnez les deux colonnes que vous souhaitez comparer (A et B par exemple).
2. Allez dans l'onglet "Accueil" du ruban Excel.
3. Cliquez sur "Mise en forme conditionnelle", puis sur "Nouvelle règle...".
4. Choisissez "Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué".
5. Dans le champ "Appliquer une mise en forme aux valeurs où cette formule est vraie:", entrez la formule suivante : =A1<>B1
6. Cliquez sur le bouton "Format..." pour choisir le format d'affichage des cellules différentes (par exemple, remplir la cellule en rouge).
7. Cliquez sur "OK" pour valider la règle.

Excel mettra automatiquement en évidence toutes les cellules où les valeurs dans les deux colonnes sont différentes. Il est important de noter que A1 et B1 dans la formule représentent la première cellule de la sélection que vous avez faite. Excel ajustera automatiquement la formule pour les autres lignes de votre sélection.

Capture d'écran : (Description textuelle : Une capture d'écran montrant la fenêtre de dialogue "Nouvelle règle de mise en forme" dans Excel. L'option "Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué" est sélectionnée, et la formule '=A1<>B1' est entrée dans le champ correspondant. Le bouton "Format..." est mis en évidence.)

4. Identifier les doublons entre deux colonnes

Parfois, vous voulez savoir si des valeurs d'une colonne apparaissent également dans l'autre. Vous pouvez utiliser la fonction NB.SI pour cela.

1. Insérez une nouvelle colonne à côté de la première colonne que vous voulez vérifier (par exemple, à côté de la colonne A).
2. Dans la première cellule de la nouvelle colonne (C2 par exemple), entrez la formule suivante : =NB.SI(B:B;A2)
3. Appuyez sur Entrée.
4. Faites glisser la poignée de recopie pour appliquer la formule à toutes les lignes.

Cette formule compte le nombre de fois que la valeur de A2 apparaît dans toute la colonne B. Si le résultat est supérieur à 0, cela signifie que la valeur de A2 est présente dans la colonne B. Si le résultat est 0, elle n'y est pas.

Vous pouvez combiner cette formule avec la fonction SI pour obtenir un résultat plus clair :

=SI(NB.SI(B:B;A2)>0;"Doublon";"Unique")

Cette formule affichera "Doublon" si la valeur de A2 se trouve dans la colonne B, et "Unique" sinon.

5. Comparer des colonnes avec des données textuelles

Les méthodes précédentes fonctionnent bien pour les nombres, mais peuvent nécessiter quelques ajustements pour les données textuelles, surtout si la casse (majuscules/minuscules) est importante.

5.1. Comparaison sensible à la casse

Si vous voulez que la comparaison tienne compte de la casse, utilisez la fonction EXACT.

1. Insérez une nouvelle colonne à côté de vos colonnes à comparer.
2. Dans la première cellule de la nouvelle colonne (C2 par exemple), entrez la formule suivante : =SI(EXACT(A2;B2);"Identique";"Différent")
3. Appuyez sur Entrée.
4. Faites glisser la poignée de recopie pour appliquer la formule à toutes les lignes.

La fonction EXACT renvoie VRAI si les chaînes de texte sont exactement les mêmes (y compris la casse), et FAUX sinon.

5.2. Comparaison insensible à la casse

Si vous voulez ignorer la casse, vous pouvez convertir les deux chaînes de texte en majuscules (ou en minuscules) avant de les comparer.

1. Insérez une nouvelle colonne à côté de vos colonnes à comparer.
2. Dans la première cellule de la nouvelle colonne (C2 par exemple), entrez la formule suivante : =SI(MAJUSCULE(A2)=MAJUSCULE(B2);"Identique";"Différent")
3. Appuyez sur Entrée.
4. Faites glisser la poignée de recopie pour appliquer la formule à toutes les lignes.

Ou, pour convertir en minuscules :

=SI(MINUSCULE(A2)=MINUSCULE(B2);"Identique";"Différent")

6. Comparer des colonnes avec des espaces superflus

Les espaces superflus (au début, à la fin, ou entre les mots) peuvent rendre la comparaison difficile. Utilisez la fonction SUPPRESPACE pour les supprimer.

1. Insérez une nouvelle colonne à côté de vos colonnes à comparer.
2. Dans la première cellule de la nouvelle colonne (C2 par exemple), entrez la formule suivante : =SI(SUPPRESPACE(A2)=SUPPRESPACE(B2);"Identique";"Différent")
3. Appuyez sur Entrée.
4. Faites glisser la poignée de recopie pour appliquer la formule à toutes les lignes.

La fonction SUPPRESPACE supprime tous les espaces superflus dans une chaîne de texte.

7. Comparer des colonnes avec des dates

Comparer des dates peut être délicat car Excel stocke les dates comme des nombres. Assurez-vous que les deux colonnes sont formatées comme des dates. Ensuite, vous pouvez utiliser les mêmes méthodes que pour les nombres.

1. Sélectionnez les deux colonnes contenant les dates.
2. Allez dans l'onglet "Accueil" et dans le groupe "Nombre", choisissez le format "Date".
3. Utilisez les méthodes décrites précédemment (fonction SI, mise en forme conditionnelle) pour comparer les colonnes.

8. Erreurs courantes à éviter

• Oublier de verrouiller les références de cellules : Lorsque vous utilisez des formules comme NB.SI, assurez-vous de verrouiller la référence de la colonne (par exemple, B:B) avec le signe $, pour éviter que la référence ne change lorsque vous faites glisser la poignée de recopie. B:B est une référence absolue à la colonne B, tandis que B2 est une référence relative qui change en fonction de la ligne.
• Ne pas tenir compte de la casse : Si la casse est importante, utilisez la fonction EXACT. Sinon, utilisez MAJUSCULE ou MINUSCULE.
• Ignorer les espaces superflus : Utilisez la fonction SUPPRESPACE pour nettoyer les données avant de les comparer.
• Comparer des données de types différents : Assurez-vous que les deux colonnes contiennent des données du même type (nombres, texte, dates) avant de les comparer.

9. Astuces supplémentaires

• Utiliser les filtres : Après avoir comparé les colonnes avec une formule, vous pouvez utiliser les filtres pour afficher uniquement les lignes où les valeurs sont différentes ou identiques.
• Créer un tableau croisé dynamique : Les tableaux croisés dynamiques peuvent être utilisés pour comparer des données de manière plus avancée, en regroupant et en synthétisant les informations.
• Utiliser Power Query : Pour les comparaisons de données plus complexes, Power Query offre des fonctionnalités puissantes pour nettoyer, transformer et comparer les données de différentes sources.

En conclusion, comparer deux colonnes dans Excel peut se faire de plusieurs manières, chacune ayant ses avantages et ses inconvénients. Le choix de la méthode dépendra de vos besoins spécifiques et de la complexité de vos données. En maîtrisant les techniques décrites dans cet article, vous serez en mesure de comparer efficacement vos données et d'en extraire des informations précieuses.