Comment comparer deux feuilles dans Google Sheets

Avez-vous déjà essayé de comparer le contenu de deux feuilles Google différentes pour trouver des divergences ? Si ce sont de petites feuilles de calcul avec quelques lignes et colonnes de données, cela ne devrait pas être trop difficile. Mais avec une grande quantité de données, c’est une toute autre histoire !

Heureusement, les logiciels de traitement de données comme Google Sheets sont mieux programmés que l’œil humain pour repérer de petites différences et similarités entre les feuilles.

Bien que Google Sheets ne propose pas d’outils directs pour comparer deux feuilles, il est possible de le faire en utilisant certains des outils qu’il fournit, ainsi qu’un peu de créativité.

Dans ce tutoriel, nous vous montrerons comment utiliser Google Sheets pour comparer deux feuilles.

Pour illustrer ces concepts, nous utiliserons les échantillons de données suivants, composés de 5 enregistrements (lignes) chacun :

Echantillon 1 :

Echantillon 2 :

Nous supposerons que l’échantillon 1 se trouve dans une feuille appelée « Feuille1 » et l’échantillon 2 dans une feuille appelée « Feuille2 ».

Comment accéder aux cellules d’une autre feuille dans Google Sheets

Si vous souhaitez accéder aux cellules d’une autre feuille, vous pouvez utiliser les références de cellules, précédées du nom de la feuille suivi d’un point d’exclamation (« ! »).

Ainsi, si vous voulez accéder à la cellule A2 de la feuille 1, vous pouvez utiliser la référence de cellule « Feuille1!A2 ».

De même, pour une référence de cellule absolue, vous pouvez utiliser la référence « Feuille1!$A$2 ».

Comment comparer deux feuilles dans Google Sheets pour trouver des différences dans les cellules

Si vous avez deux feuilles de données et que vous voulez savoir si les données ou le contenu des deux sont exactement les mêmes ou non, vous devrez utiliser une seule formule et la coller dans toute une troisième feuille.

La formule que vous pouvez utiliser est la suivante :

=SI(Feuille1!A1<>Feuille2!A1,Feuille1!A1&" | "&Feuille2!A1,"")

La fonction SI nécessite trois paramètres :

1. une condition
2. un texte ou une formule qui doit être renvoyé si la condition est VRAI
3. un texte ou une formule qui doit être renvoyé si la condition est FAUX

Ici, nous avons spécifié la condition comme : « Feuille1!A1<>Feuille2!A1 », ce qui signifie « la cellule A1 de la feuille1 n’est pas égale à la cellule A1 de la feuille2 ».

Si la condition est VRAIE, nous avons spécifié que la fonction doit renvoyer la valeur de la cellule A1 de la feuille1, suivie d’un symbole de pipe (« | »), suivi de la valeur de la cellule A1 de la feuille2, ou « Feuille1!A1& » | « &Feuille2!A1 ».

Si la condition est FAUSSE, la fonction doit simplement afficher une cellule vide («  »).

Lorsque vous collez la formule ci-dessus dans toutes les cellules de la troisième feuille (appelons-la « Feuille3 »), elle affichera exactement quelles cellules sont différentes (ou non correspondantes) ainsi que la manière dont elles sont différentes, comme indiqué ci-dessous :

Voyons maintenant les étapes à suivre pour appliquer ceci à nos données d’exemple :

1. Créez une troisième feuille (en cliquant sur l’icône « + » en bas de la fenêtre. Vous devriez voir un nouvel onglet avec le nom « Feuille3 ».
2. Dans la première cellule de cette feuille (cellule A1), tapez la formule : =SI(Feuille1!A1<>Feuille2!A1,Feuille1!A1&" | "&Feuille2!A1,"")
3. Copiez la formule en appuyant sur CTRL+C sur le clavier.
4. Sélectionnez toutes les cellules de la Feuille3 en appuyant sur CTRL+A sur le clavier ou en cliquant sur le bouton « Sélectionner tout » en haut à gauche de votre feuille.
5. Appuyez sur CTRL+V sur le clavier pour coller la formule dans toutes les cellules sélectionnées.

Vous devriez maintenant trouver que toutes les cellules qui sont identiques dans la Feuille1 et la Feuille2 sont vides. Les cellules où le contenu est différent, en revanche, doivent afficher les valeurs de cette cellule à partir des deux feuilles, séparées par le caractère « | ».

Comment comparer deux fiches pour trouver des correspondances exactes de lignes

Une application très courante de Google Sheets consiste à comparer les feuilles ligne par ligne pour trouver quelles lignes (ou enregistrements) correspondent et lesquelles ne correspondent pas.

Dans notre exemple, essayons de comparer la Feuille1 et la Feuille2 ligne par ligne. Nous afficherons les résultats dans une troisième feuille, appelée Feuille3. Si une ligne correspond dans les deux feuilles, nous devrions voir le mot « Correspondance » affiché dans la ligne correspondante de la Feuille3. Sinon, nous devrions voir le mot « Non correspondant » dans la ligne correspondante.

Pour cela, nous devrons utiliser une formule qui inclut la fonction SI avec un AND imbriqué. La fonction AND prend deux ou plusieurs conditions comme paramètres. Elle renvoie une valeur VRAI si toutes les conditions sont VRAIES et FAUX sinon.

Si vos feuilles Google ont chacune 2 colonnes à comparer, voici les étapes à suivre :

1. Créez une troisième feuille (en cliquant sur l’icône « + » en bas de la fenêtre).
2. Dans la cellule A2 de cette feuille, tapez la formule : =SI(ET(Feuille1!A2=Feuille2!A2,Feuille1!B2=Feuille2!B2),"Correspondance","Non correspondant")
3. Copiez la formule vers le bas de la colonne en faisant glisser la poignée de remplissage de la cellule.

Vous devriez maintenant voir le mot « Correspondance » partout où les lignes correspondantes des deux feuilles se correspondent, et le mot « Non correspondant » sinon.

Remarquez que même si les noms de la dernière ligne de nos échantillons correspondent, puisque les éléments correspondants dans la même ligne ne correspondent pas, la fonction a renvoyé le résultat « Non correspondant ».

Pour que cette méthode fonctionne, vos deux feuilles doivent avoir le même nombre de colonnes. Si vos feuilles ont plus de deux colonnes, vous pouvez étendre la formule en ajoutant plus de paramètres pour comparer chaque cellule d’une seule ligne. Ainsi, si vous avez 3 colonnes, votre formule serait :

=SI(ET(Feuille1!A2=Feuille2!A2,Feuille1!B2=Feuille2!B2, Feuille1!C2=Feuille2!C2),"Correspondance","Non correspondant")

Et ainsi de suite.

Comment comparer deux feuilles et mettre en évidence les lignes correspondantes

Si vous préférez mettre en évidence les lignes correspondantes dans l’une des deux feuilles plutôt que d’afficher les résultats dans une feuille séparée, vous pouvez utiliser la fonctionnalité de mise en forme conditionnelle de Google Sheets.

La mise en forme conditionnelle est une technique qui vous permet de formater des cellules en fonction d’une condition. Nous pourrions utiliser la même formule que celle discutée dans la section précédente.

Cependant, la mise en forme conditionnelle dans Google Sheets ne permet pas de faire référence à des cellules d’une autre feuille.

Une solution consiste à utiliser la fonction INDIRECT pour accéder indirectement à l’autre feuille. Cela deviendra plus clair à mesure que nous démontrerons la méthode par étapes.

Voici comment vous pouvez utiliser la mise en forme conditionnelle pour comparer deux feuilles et mettre en évidence les lignes correspondantes dans l’une d’entre elles :

1. Cliquez sur le menu « Format » dans la barre de menu.
2. Sélectionnez « Mise en forme conditionnelle ».
3. Cela ouvrira la barre latérale « Règles de mise en forme conditionnelle » sur la droite de la fenêtre.
4. Dans la zone de texte sous « Appliquer à la plage », saisissez la plage de cellules à laquelle vous souhaitez appliquer le formatage. Dans notre exemple, si vous souhaitez appliquer le formatage à la Feuille2, tapez Feuille2!A2:A10.
5. Dans la section « Appliquer le format à », cliquez sur la flèche déroulante.
6. Sélectionnez « La formule personnalisée est » dans le menu déroulant.
7. Vous verrez une zone de texte en dessous de la liste déroulante. Tapez votre formule personnalisée : =ET(A2=INDIRECT("Feuille1!A2:A"),B2=INDIRECT("Feuille1!B2:B")).
8. Sous « Style de mise en forme », cliquez sur le bouton « Couleur de remplissage ».
9. Sélectionnez la couleur que vous souhaitez utiliser pour mettre en évidence les lignes correspondantes. Nous avons sélectionné « jaune clair 2 ».
10. Cliquez sur le bouton « Terminé » pour laisser la mise en forme conditionnelle faire son travail.

Vous devriez maintenant voir les lignes correspondantes mises en évidence dans votre couleur choisie.

La formule que nous avons utilisée ici compare deux colonnes de chaque ligne de la Feuille1 et de la Feuille2. Elle utilise la fonction INDIRECT pour extraire une référence aux cellules des colonnes A et B de la Feuille1. La formule vérifie ensuite si les cellules correspondantes des deux colonnes de chaque ligne se correspondent. Si c’est le cas, elle met en évidence la ligne.

Remarque : Si vos feuilles ont plus de deux colonnes, vous pouvez étendre la formule en ajoutant plus de paramètres pour comparer chaque colonne. Par exemple, si vous avez 3 colonnes, votre formule serait :

=ET(A2=INDIRECT("Feuille1!A2:A"),B2=INDIRECT("Feuille1!B2:B"), C2=INDIRECT("Feuille1!C2:C"))

Et ainsi de suite.

Remarque : Pour mettre en évidence uniquement les lignes qui ne correspondent pas, vous pouvez remplacer les symboles « = » de la formule par « <> ».

Comment comparer deux feuilles pour trouver des données en double

Les trois premières méthodes ont principalement trouvé les lignes dans les deux feuilles qui correspondaient exactement et dans le même ordre. Cependant, dans les situations pratiques, les lignes ne sont pas toujours triées de la même manière dans les deux feuilles. Regardons donc une situation où nous voulons comparer deux feuilles et trouver les lignes de la Feuille1 qui se répètent dans la Feuille2, indépendamment de l’ordre.

En d’autres termes, utilisons la mise en forme conditionnelle pour trouver et mettre en évidence les données en double dans la Feuille2. Voici les étapes à suivre :

1. Sélectionnez une colonne vide juste après la colonne la plus à droite de la Feuille2. Disons que nous avons utilisé la colonne C dans notre exemple.
2. Dans la deuxième ligne de cette colonne, insérez la formule : =SIERREUR(CELLULE("adresse",INDEX(Feuille1!$A$1:$A$9,EQUIV(A2,Feuille1!$A$1:$A$9,0),1)),FAUX). Cette formule renverra l’adresse d’une cellule de la colonne A de la Feuille1, qui correspond au contenu de la cellule A2 de la feuille en cours. Si une cellule correspondante n’existe pas dans la Feuille1, la formule renverra FAUX.
3. Copiez cette formule dans le reste des lignes de la colonne en faisant glisser la poignée de remplissage de la cellule. Voici ce que vous devriez voir :

4. Maintenant, nous pouvons commencer à appliquer la mise en forme conditionnelle. Répétez les étapes pour la mise en forme conditionnelle discutées dans la section précédente (étapes 1 à 10), mais remplacez la formule de l’étape 7 par : =SI(PAS(C2=FAUX),SI(DECALER(INDIRECT(C2),0,1)=B2,VRAI,FAUX),FAUX)

Vous devriez maintenant voir toutes les lignes dupliquées de la Feuille2 mises en évidence dans la couleur choisie.

Note : Si vous souhaitez masquer la colonne contenant les adresses correspondantes, vous pouvez simplement cliquer avec le bouton droit de la souris sur la colonne et sélectionner « Masquer les colonnes » dans le menu contextuel qui apparaît.

Explication de la formule :

La formule de l’étape 4 semble un peu complexe, mais vous pourrez peut-être la comprendre si nous la décomposons pour vous. Analysons la formule couche par couche, en commençant par la couche interne et en nous déplaçant vers l’extérieur :

OFFSET(INDIRECT(C2),0,1) : cette fonction renvoie la valeur de la colonne B de la Feuille1, correspondant à l’adresse que nous obtenons dans la cellule C2. Rappelez-vous, C2 contient l’adresse de cellule du nom dans la Feuille1 qui correspond au nom dans la cellule A2 de la feuille en cours. Cette formule renverra simplement la valeur de l’élément correspondant au nom.

IF(OFFSET(INDIRECT(C2),0,1)=B2,VRAI,FAUX) : la fonction SI vérifie maintenant si cette valeur de Feuille1, colonne B correspond à la valeur correspondante de la Feuille2, colonne B. Si c’est le cas, alors la formule renvoie VRAI, sinon elle renvoie FAUX.

IF(PAS(C2=FAUX),SI(DECALER(INDIRECT(C2),0,1)=B2,VRAI,FAUX),FAUX) : cette formule vérifie maintenant si C2 contient une adresse de cellule ou une valeur FAUX. Si elle contient une adresse de cellule, cela signifie qu’un nom correspondant a été trouvé dans la Feuille1. Si cela se produit, la formule renvoie une valeur VRAIE ou FAUX en fonction du résultat de la fonction SI imbriquée. Si C2 contient une valeur FAUX, alors la formule renvoie également FAUX.

En d’autres termes, la formule entière vérifie d’abord s’il y a un nom en double dans la Feuille1. Si ce n’est pas le cas, elle renvoie FAUX. Si un nom en double existe, elle vérifie si la valeur correspondante de l’élément correspond également. Si c’est le cas, elle renvoie VRAI, sinon elle renvoie FAUX.

Note : Si vos feuilles ont plus de deux colonnes, vous pouvez étendre la formule en incorporant une fonction ET dans votre fonction SI imbriquée. Par exemple, si vous avez 3 colonnes dans chaque feuille, vous pouvez changer votre formule en :

=SI(PAS(D2=FAUX),SI(DECALER(ET(INDIRECT(D2),0,1)=B2, DECALER(INDIRECT(D2),0,2)=C2),VRAI,FAUX),FAUX)

Ici, nous supposons que vos adresses de cellules correspondantes sont dans la colonne D de la Feuille2 au lieu de la colonne C.

Comment comparer deux feuilles pour trouver des données manquantes

Enfin, que faire si vous souhaitez mettre en évidence les lignes manquantes au lieu des lignes en double ?

Dans ce cas, vous avez simplement besoin des lignes opposées mises en évidence. Vous devez donc simplement ajouter une fonction NOT à la formule de la mise en forme conditionnelle discutée dans la section précédente.

En d’autres termes, votre formule devrait maintenant être :

=PAS(SI(PAS(C2=FAUX),SI(DECALER(INDIRECT(C2),0,1)=B2,VRAI,FAUX),FAUX))

Voici le résultat que vous obtiendriez :

En conclusion, dans ce tutoriel, nous avons discuté des différentes façons de comparer deux feuilles pour trouver des différences. Bien que les formules que nous avons utilisées puissent sembler un peu complexes, nous vous encourageons à les essayer vous-même pour mieux comprendre leur fonctionnement.