Comment comparer deux feuilles Excel (pour les différences)

Regarder la vidéo - Comment comparer deux feuilles Excel pour les différences

Comparer deux fichiers Excel (ou comparer deux feuilles dans le même fichier) peut être délicat car un classeur Excel n'affiche qu'une feuille à la fois.

Cela devient plus difficile et sujet aux erreurs lorsque vous avez beaucoup de données à comparer.

Heureusement, il existe des fonctionnalités intéressantes dans Excel qui vous permettent d'ouvrir et de comparer facilement deux fichiers Excel.

Dans ce didacticiel Excel, je vais vous montrer plusieurs façons de comparer deux fichiers (ou feuilles) Excel différents et vérifier les différences. La méthode que vous choisirez dépendra de la façon dont vos données sont structurées et du type de comparaison que vous recherchez.

Commençons!

Comparer deux feuilles Excel dans des fichiers Excel séparés (côte à côte)

Si vous souhaitez comparer deux fichiers Excel séparés côte à côte (ou deux feuilles dans le même classeur), il existe une fonctionnalité intégrée dans Excel pour le faire.

C'est le Voir côte à côte option.

Ceci n'est recommandé que lorsque vous disposez d'un petit ensemble de données et que la comparaison manuelle de ces fichiers est susceptible de prendre moins de temps et d'être moins sujette aux erreurs. Si vous avez un jeu de données volumineux, je vous recommande d'utiliser la méthode conditionnelle ou la méthode de formule abordée plus loin dans ce didacticiel.

Voyons comment l'utiliser lorsque vous devez comparer deux fichiers séparés ou deux feuilles dans le même fichier.

Supposons que vous ayez deux fichiers pour deux mois différents et que vous vouliez vérifier quelles valeurs sont différentes dans ces deux fichiers.

Par défaut, lorsque vous ouvrez un fichier, il est susceptible d'occuper tout votre écran. Même si vous réduisez la taille, vous voyez toujours un fichier Excel en haut.

Avec l'option d'affichage côte à côte, vous pouvez ouvrir deux fichiers, puis les disposer horizontalement ou verticalement. Cela vous permet de comparer facilement les valeurs sans basculer.

Vous trouverez ci-dessous les étapes pour aligner deux fichiers côte à côte et les comparer :

1. Ouvrez les fichiers que vous souhaitez comparer.
2. Dans chaque fichier, sélectionnez la feuille que vous souhaitez comparer.
3. Cliquez sur l'onglet Affichage
4. Dans le groupe Windows, cliquez sur l'option « Afficher côte à côte ». Cela devient disponible uniquement lorsque vous avez ouvert deux fichiers Excel ou plus.

Dès que vous cliquez sur l'option Afficher côte à côte, Excel organise le classeur horizontalement. Les deux fichiers seront visibles et vous êtes libre de modifier/comparer ces fichiers lorsqu'ils sont disposés côte à côte.

Si vous souhaitez organiser les fichiers verticalement, cliquez sur l'option Tout organiser (dans l'onglet Affichage).

Cela ouvrira la boîte de dialogue « Organiser les fenêtres » dans laquelle vous pourrez sélectionner « Vertical ».

À ce stade, si vous faites défiler l'une des feuilles de calcul vers le bas, l'autre restera telle quelle. Vous pouvez modifier cela de sorte que lorsque vous faites défiler une feuille, l'autre défile également en même temps. Cela facilite la comparaison ligne par ligne et la détection des différences.

Mais pour ce faire, vous devez activer Défilement synchrone.

Pour activer le défilement synchrone, cliquez sur l'onglet Affichage (dans l'un des classeurs), puis cliquez sur l'option Défilement synchrone. Il s'agit d'un bouton à bascule (donc si vous voulez le désactiver, cliquez simplement dessus à nouveau).

Comparaison de plusieurs feuilles dans des fichiers Excel séparés (côte à côte)

Avec l'option "Afficher côte à côte", vous ne pouvez comparer que deux fichiers Excel à la fois.

Si vous avez plusieurs fichiers Excel ouverts, lorsque vous cliquez sur l'option Afficher côte à côte, une boîte de dialogue "Comparer côte à côte" s'affiche, dans laquelle vous pouvez choisir le fichier que vous souhaitez comparer avec le classeur actif.

Si vous souhaitez comparer plus de deux fichiers à la fois, ouvrez tous ces fichiers, puis cliquez sur l'option Réorganiser tout (c'est dans l'onglet Affichage).

Dans la boîte de dialogue Réorganiser les fenêtres, sélectionnez Vertical/Horizontal, puis cliquez sur OK.

Cela organisera tous les fichiers Excel ouverts dans l'ordre sélectionné (vertical ou horizontal).

Comparer deux feuilles (côte à côte) dans le même classeur Excel

Si vous souhaitez comparer deux feuilles distinctes dans le même classeur, vous ne pouvez pas utiliser la fonction Afficher côte à côte (car elle ne fonctionne que pour des fichiers Excel séparés).

Mais vous pouvez toujours faire la même comparaison côte à côte.

Ceci est rendu possible par le «Nouvelle fonctionnalité de Windows dans Excel, cela vous permet d'ouvrir deux instances sur le même classeur. Une fois que vous avez ouvert deux instances, vous pouvez les disposer côte à côte, puis les comparer.

Supposons que vous ayez un classeur Excel contenant deux feuilles pour deux mois différents (janvier et février) et que vous souhaitiez les comparer côte à côte pour voir comment les ventes par magasin ont changé :

Voici les étapes pour comparer deux feuilles dans Excel :

1. Ouvrez le classeur contenant les feuilles que vous souhaitez comparer.
2. Cliquez sur l'onglet Affichage
3. Dans le groupe Fenêtre, cliquez sur l'option « Nouvelle fenêtre ». Cela ouvre la deuxième instance du même classeur.
4. Dans l'onglet « Affichage », cliquez sur « Tout organiser ». Cela ouvrira la boîte de dialogue Réorganiser les fenêtres
5. Sélectionnez « Vertical » pour comparer les données en colonnes (ou sélectionnez Horizontal si vous souhaitez comparer les données en lignes).
6. Cliquez sur OK.

Les étapes ci-dessus organiseraient les deux instances du classeur verticalement.

À ce stade, les deux classeurs auraient la même feuille de calcul sélectionnée. Dans l'un des classeurs, sélectionnez l'autre feuille que vous souhaitez comparer avec la feuille active.

Comment cela marche-t-il?

Lorsque vous cliquez sur Nouvelle fenêtre, le même classeur s'ouvre à nouveau avec un nom légèrement différent. Par exemple, si le nom de votre classeur est « Test » et que vous cliquez sur Nouvelle fenêtre, il nommera le classeur déjà ouvert « Test - 1 » et la deuxième instance comme « Test - 2 ».

Notez qu'il s'agit toujours du même classeur. Si vous apportez des modifications à l'un de ces classeurs, cela sera reflété dans les deux.

Et lorsque vous fermez une instance du fichier ouvert, le nom revient à l'original.

Vous pouvez également activer le défilement synchrone si vous le souhaitez (en cliquant sur l'option "Défilement synchrone" dans l'onglet "Affichage")

Comparer deux feuilles et mettre en évidence les différences (à l'aide de la mise en forme conditionnelle)

Bien que vous puissiez utiliser la méthode ci-dessus pour aligner les classeurs ensemble et parcourir manuellement les données ligne par ligne, ce n'est pas un bon moyen si vous avez beaucoup de données.

En outre, effectuer ce niveau de comparaison manuellement peut entraîner de nombreuses erreurs.

Ainsi, au lieu de le faire manuellement, vous pouvez utiliser la puissance de la mise en forme conditionnelle pour mettre rapidement en évidence les différences entre les deux feuilles Excel.

Cette méthode est vraiment utile si vous avez deux versions dans deux feuilles différentes et que vous souhaitez vérifier rapidement ce qui a changé.

Notez que vous NE PEUX PAS comparer deux feuilles dans des cahiers différents.

Étant donné que la mise en forme conditionnelle ne peut pas faire référence à un fichier Excel externe, les feuilles que vous devez comparer doivent se trouver dans le même classeur Excel. Dans le cas contraire, vous pouvez copier une feuille de l'autre fichier dans le classeur actif, puis effectuer cette comparaison.

Pour cet exemple, supposons que vous ayez un ensemble de données comme indiqué ci-dessous pour deux mois (janvier et février) dans deux feuilles différentes et que vous souhaitiez comparer rapidement les données de ces deux feuilles et vérifier si les prix de ces articles ont changé ou non.

Vous trouverez ci-dessous les étapes à suivre :

1. Sélectionnez les données dans la feuille où vous souhaitez mettre en évidence les modifications. Puisque je veux vérifier comment les prix ont changé de janvier à février, j'ai sélectionné les données dans la feuille de février.
2. Cliquez sur l'onglet Accueil
3. Dans le groupe Styles, cliquez sur « Mise en forme conditionnelle »
4. Dans les options qui s'affichent, cliquez sur « Nouvelle règle »
5. Dans la boîte de dialogue « Nouvelle règle de formatage », cliquez sur « Utiliser une formule pour déterminer les cellules à formater »
6. Dans le champ de formule, saisissez la formule suivante : =B2Jan!B2
7. Cliquez sur le bouton Formater
8. Dans la boîte de dialogue Format de cellule qui s'affiche, cliquez sur l'onglet « Remplir » et sélectionnez la couleur dans laquelle vous souhaitez mettre en évidence les données incompatibles.
9. Cliquez sur OK
10. Cliquez sur OK

Les étapes ci-dessus mettraient instantanément en évidence tout changement dans l'ensemble de données dans les deux feuilles.

Comment cela marche-t-il?

La mise en forme conditionnelle met en évidence une cellule lorsque la formule donnée pour cette cellule renvoie un VRAI. Dans cet exemple, nous comparons chaque cellule d'une feuille avec la cellule correspondante de l'autre feuille (en utilisant l'opérateur différent de dans la formule).

Lorsque la mise en forme conditionnelle trouve une différence dans les données, elle la met en évidence dans la feuille de janvier (celle dans laquelle nous avons appliqué la mise en forme conditionnelle.

Notez que j'ai utilisé une référence relative dans cet exemple (A1 et non $A$1 ou $A1 ou A$1).

Lorsque vous utilisez cette méthode pour comparer deux feuilles dans Excel, n'oubliez pas ce qui suit :

• Cette méthode est bonne pour identifier rapidement les différences, mais vous ne pouvez pas l'utiliser de manière continue. Par exemple, si j'entre une nouvelle ligne dans l'un des ensembles de données (ou supprime une ligne), cela me donnera des résultats incorrects. Dès que j'insère/supprime la ligne, toutes les lignes suivantes sont considérées comme différentes et mises en évidence en conséquence.
• Vous ne pouvez comparer que deux feuilles dans le même fichier Excel
• Vous pouvez uniquement comparer la valeur (pas la différence de formule ou de formatage).

Comparez deux fichiers/feuilles Excel et obtenez les différences à l'aide d'une formule

Si vous souhaitez uniquement comparer et identifier rapidement les différences entre deux feuilles, vous pouvez utiliser une formule pour récupérer uniquement les valeurs différentes.

Pour cette méthode, vous aurez besoin d'une feuille de calcul distincte où vous pourrez récupérer les différences.

Cette méthode fonctionnerait si vous souhaitez comparer deux classeurs ou feuilles de calcul Excel distincts dans le même classeur.

Laissez-moi vous montrer un exemple où je compare deux ensembles de données dans deux feuilles (dans le même classeur).

Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous dans une feuille appelée Jan (et des données similaires dans une feuille appelée Feb), et que vous vouliez savoir quelles valeurs sont différentes.

Pour comparer les deux feuilles, insérez d'abord une nouvelle feuille de calcul (appelons cette feuille « Différence »).

Dans la cellule A1, entrez la formule suivante :

=IF(Jan!A1Fév!A1,"Valeur Jan :"&Jan!A1&CHAR(10)&"Valeur Fév :"&Fév!A1"")

Copiez et collez cette formule pour une plage afin qu'elle couvre l'intégralité du jeu de données dans les deux feuilles. Étant donné que j'ai un petit ensemble de données, je ne copierai et collerai que cette formule dans la plage A1: B10.

La formule ci-dessus utilise une condition IF pour vérifier les différences. S'il n'y a pas de différence dans les valeurs, il retournera vide, et s'il y a une différence, il renverra les valeurs des deux feuilles dans des lignes séparées dans la même cellule.

La bonne chose avec cette méthode est qu'elle ne vous donne que les différences et vous montre exactement quelle est la différence. Dans cet exemple, je peux facilement voir que les prix dans les cellules B4 et B8 sont différents (ainsi que les valeurs exactes dans ces cellules).

Comparez deux fichiers/feuilles Excel et obtenez les différences à l'aide de VBA

Si vous devez comparer des fichiers ou des feuilles Excel assez souvent, il est judicieux d'avoir un code VBA de macro Excel prêt et de l'utiliser chaque fois que vous avez besoin de faire la comparaison.

Vous pouvez également ajouter la macro à la barre d'outils d'accès rapide afin de pouvoir y accéder avec un seul bouton et de savoir instantanément quelles cellules sont différentes dans différents fichiers/feuilles.

Supposons que vous ayez deux feuilles Jan et Feb et que vous souhaitiez comparer et mettre en évidence les différences dans la feuille Jan, vous pouvez utiliser le code VBA ci-dessous :

Sub CompareSheets() Dim rngCell As Range pour chaque rngCell In Worksheets ("Jan").UsedRange If Not rngCell = Worksheets ("Feb").Cells(rngCell.Row, rngCell.Column) Then rngCell.Interior.Color = vbYellow End Si suivant rngCell End Sub

Le code ci-dessus utilise la boucle For Next pour parcourir chaque cellule de la feuille de janvier (toute la plage utilisée) et la compare à la cellule correspondante de la feuille de février. S'il trouve une différence (qui est vérifiée à l'aide de l'instruction If-Then), il met ces cellules en surbrillance en jaune.

Vous pouvez utiliser ce code dans un module standard de l'éditeur VB.

Et si vous devez le faire souvent, il est préférable d'enregistrer ce code dans le classeur de macros personnelles, puis de l'ajouter à la barre d'outils d'accès rapide. De cette manière, vous pourrez faire cette comparaison en cliquant sur un bouton.

Voici les étapes pour obtenir le classeur de macros personnelles dans Excel (il n'est pas disponible par défaut, vous devez donc l'activer).

Voici les étapes pour enregistrer ce code dans le classeur de macros personnelles.

Et ici, vous trouverez les étapes pour ajouter ce code de macro au QAT.

Utilisation d'un outil tiers - Comparateur XL

Un autre moyen rapide de comparer deux fichiers Excel et de vérifier les correspondances et les différences consiste à utiliser un outil tiers gratuit tel que XL Comparator.

Il s'agit d'un outil Web dans lequel vous pouvez télécharger deux fichiers Excel et créer un fichier de comparaison qui contiendra les données communes (ou des données différentes en fonction de l'option que vous avez sélectionnée.

Supposons que vous ayez deux fichiers contenant des ensembles de données client (tels que le nom et l'adresse e-mail) et que vous souhaitiez vérifier rapidement quels clients se trouvent dans le fichier 1 et non dans le fichier 2.

Voici comment comparer deux fichiers Excel et créer un rapport de comparaison :

1. Ouvrez https://www.xlcomparator.net/
2. Utilisez l'option Choisir un fichier pour télécharger deux fichiers (la taille maximale de chaque fichier peut être de 5 Mo)
3. Cliquez sur le bouton Suivant.
4. Sélectionnez la colonne commune dans ces deux fichiers. L'outil utilisera cette colonne commune pour rechercher des correspondances et des différences
5. Sélectionnez l'une des quatre options, que vous souhaitiez obtenir des données correspondantes ou des données différentes (basées sur le fichier 1 ou le fichier 2)
6. Cliquez sur Suivant
7. Téléchargez le fichier de comparaison qui contiendra les données (en fonction de l'option que vous avez sélectionnée à l'étape 5)

Vous trouverez ci-dessous une vidéo qui montre comment fonctionne l'outil XL Comparator.

Une préoccupation que vous pouvez avoir lorsque vous utilisez un outil tiers pour comparer des fichiers Excel concerne la confidentialité. Si vous avez des données confidentielles et que la confidentialité est vraiment importante pour cela, il est préférable d'utiliser les autres méthodes indiquées ci-dessus. Notez que le site Web XL Comparator mentionne qu'ils suppriment tous les fichiers après 1 heure de comparaison.

Voici quelques-unes des méthodes que vous pouvez utiliser pour comparer deux fichiers Excel différents (ou feuilles de calcul dans le même fichier Excel). J'espère que vous avez trouvé ce tutoriel Excel utile.