Comment comparer les données dans Excel
La comparaison des colonnes dans Excel est quelque chose que nous faisons tous de temps en temps. Microsoft Excel offre un certain nombre d'options pour comparer et faire correspondre les données, mais la plupart d'entre elles se concentrent sur la recherche dans une colonne. Dans ce tutoriel, nous allons explorer plusieurs techniques pour comparer deux colonnes dans Excel et trouver des correspondances et des différences entre elles, vous apprendrez également diverses méthodes pour comparer des fichiers Excel et identifier leurs différences. Découvrez comment ouvrir deux fenêtres Excel côte à côte, comment utiliser des formules Excel pour créer un rapport de différence, mettre en évidence des différences avec la mise en forme conditionnelle, etc.
Lorsque vous avez deux classeurs Excel similaires, ou plutôt deux versions du même classeur, quelle est la première chose que vous voulez généralement en faire ? Bien, comparez ces fichiers pour les différences, puis probablement les fusionner en un seul fichier. En outre, la comparaison de classeurs peut vous aider à identifier les problèmes potentiels tels que les liens brisés, les enregistrements en double, les formules incohérentes ou le formatage incorrect.
Examinons donc de plus près différentes méthodes pour comparer deux colonnes, feuilles de calcul Excel ou des classeurs entiers et identifier les différences entre elles.
Table des matières
Comment comparer 2 colonnes dans Excel, ligne par ligne
Exemple 1. Comparer deux colonnes pour des correspondances ou des différences dans la même ligne
Exemple 2. Comparez deux listes pour des correspondances sensibles à la casse dans la même ligne
Comment comparer deux colonnes dans Excel pour les correspondances et les différences
Comment comparer deux fichiers Excel en les visualisant côte à côte
Comparer deux feuilles dans le même classeur
Comment comparer deux feuilles Excel pour les différences de valeurs
Souligner les différences entre 2 feuilles avec la mise en forme conditionnelle
Comment comparer 2 colonnes dans Excel, ligne par ligne
Lorsque vous effectuez une analyse de données dans Excel, l’une des tâches les plus fréquentes consiste à comparer les données de chaque ligne. Cette tâche peut être effectuée à l'aide de la fonction SI comme illustré dans les exemples suivants.
Exemple 1. Comparer deux colonnes pour des correspondances ou des différences dans la même ligne
Pour comparer deux colonnes d'Excel, ligne par ligne, écrivez une formule SI habituelle comparant les deux premières cellules. Entrez la formule dans une autre colonne de la même ligne, puis copiez-la dans d'autres cellules en faisant glisser la poignée de recopie (un petit carré dans le coin inférieur droit de la cellule sélectionnée). Ce faisant, le curseur devient le signe plus :
Formule pour les correspondances
Pour rechercher dans la même ligne des cellules de même contenu, A2 et B2 dans cet exemple, la formule est la suivante :
=SI(A2=B2 ; "Correspond" ; "")
Formule pour les différences
Pour rechercher des cellules dans la même ligne avec un contenu différent, remplacez simplement "=" par le signe de non-égalité :
=SI(A2 B2 ; "Aucune correspondance" ; "")
Correspondances et différences
Et bien sûr, rien ne vous empêche de trouver à la fois les correspondances et les différences avec une seule formule :
=SI(A2 = B2 ; "Correspondance" ; "Aucune correspondance")
Ou
=SI(A2 B2 ; "Aucune correspondance" ; "Correspondance")
Le résultat peut ressembler à ceci :
Comme vous le voyez, la formule gère également les chiffres, les dates, les heures et les chaînes de texte.
Exemple 2. Comparez deux listes pour des correspondances sensibles à la casse dans la même ligne
Comme vous l'avez probablement remarqué, les formules de l'exemple précédent ignorent la casse lors de la comparaison de valeurs de texte, comme dans la ligne 8 de la capture d'écran ci-dessus. Si vous souhaitez rechercher des correspondances sensibles à la casse entre 2 colonnes de chaque ligne, utilisez la fonction EXACT :
=SI(EXACT(A2; B2); "Correspond"; "")
Pour rechercher des différences sensibles à la casse dans la même ligne, entrez le texte correspondant ("Unique" dans cet exemple) dans le troisième argument de la fonction SI, par exemple :
=SI(EXACT(A2; B2); "Correspond"; "Unique")
Comment comparer deux colonnes dans Excel pour les correspondances et les différences
Supposons que vous avez 2 listes de données dans Excel et que vous souhaitez rechercher toutes les valeurs (nombres, dates ou chaînes de texte) qui se trouvent dans la colonne A mais pas dans la colonne B.
Pour cela, vous pouvez incorporer la fonction NB.SI($B:$B ;$A2) = 0 dans le test logique du SI et vérifier si elle renvoie zéro (aucune correspondance n'est trouvée) ou tout autre nombre (au moins une correspondance est trouvée).
Par exemple, la formule SI / NB.SI suivante recherche dans la colonne B entière la valeur de la cellule A2. Si aucune correspondance n'est trouvée, la formule renvoie "Aucune correspondance dans B", une chaîne vide sinon :
=SI(NB.SI($B:$B ; $A2) = 0 ; "Aucune correspondance dans B" ; "")
Remarque : Si votre table comporte un nombre fixe de lignes, vous pouvez spécifier une certaine plage (par exemple, $B2: $B9) plutôt que la colonne entière ($B:$B) pour que la formule fonctionne plus rapidement sur des ensembles de données volumineux.
Le même résultat peut être obtenu en utilisant une formule SI avec les fonctions intégrées SIERREUR et EQUIV :
=SI(ESTERREUR(EQUIV(A2;$B$2:$B$9;0)); "Aucune correspondance dans B"; "")
Ou, en utilisant la formule matricielle suivante (n'oubliez pas d'appuyer sur Ctrl + Shift + Entrée pour le saisir correctement) :
=SI(SOMME(- -($B$2:$B$10 = $A2)) = 0 ; "Aucune correspondance dans B" ; "")
Si vous souhaitez qu'une seule formule identifie à la fois les correspondances (doublons) et les différences (valeurs uniques), insérez du texte pour les correspondances dans les guillemets vides ("") de l'une des formules ci-dessus. Par exemple :
=SI(NB.SI($B:$B ; $A2) = 0 ; "Aucune correspondance dans B" ; " Correspondance dans B ")
Comment comparer deux fichiers Excel en les visualisant côte à côte
Si vous avez des classeurs relativement petits et un sens aigu du détail, ce moyen rapide et facile de comparer des fichiers Excel peut vous convenir. Je parle du mode Afficher côte à côte qui vous permet d’organiser deux fenêtres Excel côte à côte. Vous pouvez utiliser cette méthode pour comparer visuellement deux classeurs ou deux feuilles dans le même classeur.
Comparer 2 classeurs Excel
Supposons que vous ayez des rapports de ventes pour deux mois et que vous souhaitiez les visualiser simultanément pour savoir quels produits ont eu les meilleurs résultats ce mois-ci et ceux du mois dernier.
Pour ouvrir deux fichiers Excel côte à côte, procédez comme suit :
- Ouvrez les classeurs que vous souhaitez comparer.
- Accédez à l'onglet Affichage, groupe de fenêtres, puis cliquez sur le bouton Afficher côte à côte. C'est tout !
Par défaut, deux fenêtres Excel distinctes sont affichées horizontalement.
Pour fractionner les fenêtres Excel verticalement, cliquez sur le bouton Tout organiser et sélectionnez Vertical :
En conséquence, deux fenêtres Excel distinctes seront disposées côte à côte, comme dans la capture d'écran ci-dessous.
Si vous souhaitez faire défiler simultanément les deux feuilles de calcul pour comparer les données ligne par ligne, assurez-vous que l'option Défilement synchrone est activée. Cette option réside dans l'onglet Affichage, dans le groupe Fenêtre, juste sous le bouton Afficher côte à côte. Elle est généralement activée automatiquement dès que vous activez le mode Afficher côte à côte.
Comparer deux feuilles dans le même classeur
Parfois, deux feuilles que vous souhaitez comparer résident dans le même classeur. Pour les afficher côte à côte, procédez comme suit.
- Ouvrez votre fichier Excel, accédez à l'onglet Affichage > Groupe de fenêtres, puis cliquez sur le bouton Nouvelle fenêtre.
- Cela ouvrira le même fichier Excel dans une fenêtre différente.
- Activez le mode Afficher côte à côte en cliquant sur le bouton correspondant du ruban.
- Sélectionnez la feuille 1 dans la première fenêtre et la feuille 2 dans la deuxième fenêtre.
Cela ouvrira le même fichier Excel dans deux fenêtres. Si vous regardez en haut du cahier, vous remarquerez que les deux portent le même nom, mais avec des numéros différents.
Par défaut, la même feuille de calcul est sélectionnée pour les deux. Si vous modifiez quelque chose dans une fenêtre, il sera automatiquement affiché dans l'autre. Maintenant, vous pouvez basculer vers la feuille de calcul que vous souhaitez comparer dans un autre classeur pour afficher côte à côte avec défilement synchrone.
Comment comparer deux feuilles Excel pour les différences de valeurs
C’est le moyen le plus simple de comparer des données dans Excel qui vous permet d’identifier des cellules avec des valeurs différentes. En conséquence, vous aurez un rapport de différence dans une nouvelle feuille de calcul.
Pour comparer deux feuilles de calcul Excel aux différences, ouvrez simplement une nouvelle feuille vide, entrez la formule suivante dans la cellule A1, puis copiez-la ensuite vers la droite en faisant glisser la poignée de recopie :
=SI(Feuil1!A1 Feuil!A; "Feuil1:" &Feuil1!A1 &" vs Feuil2:" &Feuil2!A1; "")
En raison de l'utilisation de références de cellules relatives, la formule changera en fonction de la position relative de la colonne et de la ligne. En conséquence, la formule dans A1 comparera la cellule A1 dans Feuil1 et Feuil2, la formule dans B1 comparera la cellule B1 dans les deux feuilles, et ainsi de suite. Le résultat ressemblera à ceci :
Comme vous pouvez le voir dans la capture d'écran ci-dessus, la formule compare 2 feuilles, identifie les cellules avec des valeurs différentes et affiche les différences dans les cellules correspondantes.
Sachez que dans le rapport de différence, les dates sont présentées sous forme de numéros de série car elles sont stockées dans le système Excel interne, ce qui n’est pas très pratique pour analyser les différences entre elles.
Souligner les différences entre 2 feuilles avec la mise en forme conditionnelle
Pour mettre en surbrillance les cellules ayant des valeurs différentes dans deux feuilles avec la couleur de votre choix, utilisez la fonctionnalité de mise en forme conditionnelle Excel :
- Dans la feuille de calcul où vous souhaitez mettre en évidence les différences, sélectionnez toutes les cellules utilisées. Pour cela, cliquez sur la cellule supérieure gauche de la plage utilisée, généralement A1, puis appuyez sur Ctrl + Shift + End pour étendre la sélection à la dernière cellule utilisée.
- Sous l'onglet « Accueil », dans le groupe « Styles »", cliquez sur le bouton « Mise en forme conditionnelle » puis choisissez « Nouvelle règle » et créez une règle avec l’option « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué » :
=A1Feuil2!A1
Où Feuil2 est le nom de l'autre feuille que vous comparez.
En conséquence, les cellules avec des valeurs différentes seront mises en surbrillance avec la couleur sélectionnée :
Comme vous le voyez, il est très facile de comparer deux feuilles Excel en utilisant des formules ou des formats conditionnels. Cependant, ces méthodes ne conviennent pas à une comparaison complète en raison des limitations suivantes :
- Ils trouvent des différences uniquement dans les valeurs, mais ne peuvent pas comparer les formules ou la mise en forme des cellules.
- Ils ne peuvent pas identifier les lignes et les colonnes ajoutées ou supprimées. Dès que vous ajoutez ou supprimez une ligne / colonne dans une feuille, toutes les lignes / colonnes suivantes sont marquées en tant que différences.
- Ils fonctionnent au niveau de la feuille, mais ne peuvent pas détecter les différences structurelles au niveau du classeur, telles que les ajouts et les suppressions de feuille.