Comparer deux feuilles Excel pour repérer les différences
Comparez deux feuilles Excel à partir d’un identifiant unique pour repérer les valeurs modifiées et les lignes ajoutées ou absentes. Exemple détaillé, gestion des doublons et consigne IA précise.
Deux exports de commandes peuvent contenir le même nombre de lignes tout en décrivant des commandes différentes. Comparer A2 à A2 ne suffit pas si quelqu’un a trié une feuille ou inséré un enregistrement.
Pour des données métier, commencez par rapprocher les enregistrements au moyen d’un identifiant stable, puis comparez les champs associés. Distinguez trois résultats : les enregistrements modifiés, ceux présents uniquement dans l’ancienne feuille et ceux présents uniquement dans la nouvelle. Conservez les deux sources et placez le rapport sur une feuille séparée.
Définir ce que vous souhaitez comparer
| Question | Approche adaptée |
|---|---|
| Le montant d’une commande a-t-il changé ? | Retrouver l’identifiant de la commande, puis comparer le montant |
| Quelles commandes ont été ajoutées ou supprimées ? | Vérifier la présence des identifiants dans les deux sens |
| Une formule ou un format de cellule a-t-il changé ? | Comparer les versions du classeur, pas seulement les valeurs obtenues |
| Deux petites feuilles semblent-elles différentes ? | Les afficher côte à côte, puis examiner les différences |
L’affichage des feuilles côte à côte proposé par Microsoft facilite l’inspection visuelle. Il n’aligne pas les enregistrements selon leurs identifiants métier. L’exemple ci-dessous compare des valeurs, et non la mise en forme ou le texte des formules.
Commencer par une clé unique
Utilisez un numéro de commande, un matricule salarié ou un autre identifiant qui ne devrait apparaître qu’une seule fois dans chaque source. Les noms seuls conviennent rarement. Si une commande comporte plusieurs lignes, son numéro ne suffit pas : utilisez une combinaison documentée, par exemple le numéro de commande et le numéro de ligne.
Avant le rapprochement, examinez les identifiants vides, les doublons, les zéros initiaux et les différences entre texte et nombres. Gardez l’identifiant d’origine à côté de toute version nettoyée. N’effacez pas des espaces significatifs ou des zéros initiaux simplement pour forcer une correspondance. Notre guide de rapprochement des colonnes traite du cas voisin où l’on ajoute des champs à un tableau existant.
Les formules suivantes utilisent les noms de fonctions anglais et des virgules. Une version localisée d’Excel peut nécessiter des noms traduits ou des points-virgules. L’exemple repose sur des identifiants simples, sans caractères génériques, et des montants numériques non vides.
Réaliser une petite comparaison
Dans une copie du classeur, créez deux feuilles nommées Old et New. Saisissez ces données fictives dans les colonnes A et B, avec les en-têtes en ligne 1. L’ordre des lignes est volontairement différent.
| ID dans Old | Montant dans Old | ID dans New | Montant dans New |
|---|---|---|---|
| A101 | 120 | A103 | 75 |
| A102 | 80 | A101 | 120 |
| A103 | 75 | A105 | 60 |
| A104 | 50 | A102 | 95 |
Les deux premières colonnes vont dans Old et les deux dernières dans New. Il s’agit de données d’illustration, pas d’un résultat client ni d’un test de rapidité.
Sur chaque feuille, commencez par compter, dans une colonne auxiliaire, les occurrences de l’identifiant de la ligne dans sa propre source :
=COUNTIF($A$2:$A$5,A2)
Chaque identifiant non vide de cet exemple doit renvoyer 1. Examinez tout résultat supérieur à 1 avant d’effectuer une recherche. COUNTIF ne distingue pas les majuscules des minuscules et reconnaît les caractères génériques dans les critères. Si la casse distingue vos identifiants, ou s’ils contiennent *, ? ou ~, ces formules simples nécessitent une autre règle de rapprochement. Ne les utilisez pas telles quelles.
Ensuite, dans Old!D2, comptez les correspondances dans la nouvelle source, puis recopiez la formule vers le bas :
=COUNTIF(New!$A$2:$A$5,A2)
0 signifie que l’identifiant apparaît uniquement dans Old ; 1 indique un candidat unique ; plus de 1 signale une correspondance ambiguë. Dans Old!E2, récupérez le nouveau montant :
=XLOOKUP(A2,New!$A$2:$A$5,New!$B$2:$B$5,"Not found",0)
Comparez E à B uniquement si D vaut 1 et si les deux montants sont des nombres valides. Ne confondez pas un enregistrement absent, un montant vide et un véritable zéro. Pour des montants calculés, définissez une règle d’arrondi ou de tolérance adaptée avant de classer les écarts.
XLOOKUP renvoie le premier élément correspondant ; la fonction ne résout pas les doublons. Elle n’est pas non plus disponible dans Excel 2016 et 2019. Dans ces versions, après les mêmes contrôles d’unicité, utilisez cette solution de recherche exacte en E2 :
=IFNA(INDEX(New!$B$2:$B$5,MATCH(A2,New!$A$2:$A$5,0)),"Not found")
Consultez la documentation de XLOOKUP et les explications sur INDEX et MATCH de Microsoft pour le comportement et la compatibilité des fonctions.
Enfin, sur New, comptez chaque identifiant dans Old pour repérer les ajouts :
=COUNTIF(Old!$A$2:$A$5,A2)
En filtrant cette colonne sur 0, vous trouverez A105. Un contrôle effectué uniquement de Old vers New ne le détecterait pas.
Créer un rapport qui explique chaque différence
Pour cet exemple, un rapport séparé doit contenir :
| ID | Montant dans Old | Montant dans New | Statut |
|---|---|---|---|
| A101 | 120 | 120 | Inchangé |
| A102 | 80 | 95 | Modifié |
| A103 | 75 | 75 | Inchangé |
| A104 | 50 | — | Uniquement dans Old |
| A105 | — | 60 | Uniquement dans New |
Il y a cinq identifiants distincts : deux inchangés, un modifié, un uniquement dans Old et un uniquement dans New. Les deux sources contiennent quatre lignes. Un rapprochement selon la position des lignes donnerait une image trompeuse.
Si vous comparez plusieurs champs, consignez pour chaque changement le nom du champ, l’ancienne valeur et la nouvelle. Gardez aussi les références des lignes sources, en plus des identifiants. Placez les clés vides ou dupliquées dans une liste de vérification distincte au lieu de les supprimer silencieusement. Consultez la méthode pour supprimer les doublons sans risque avant de modifier l’une des sources.
Demander à GetSheetAI une comparaison bien délimitée
Dans la barre latérale GetSheetAI pour Excel, précisez la clé, les champs, l’emplacement du résultat et les règles plutôt que de demander simplement « compare ces feuilles ». Remplacez les noms de champs de cette consigne par vos véritables en-têtes et omettez Statut si votre source ne comporte qu’une colonne de montant :
Compare Old et New selon l’identifiant de commande, en utilisant leurs plages réellement remplies. Signale d’abord les identifiants vides et dupliqués dans chaque source. Si les identifiants sont ambigus, arrête-toi et demande-moi comment les rapprocher. Compare uniquement Montant et Statut. Distingue les enregistrements absents, les valeurs vides et les zéros. Crée une nouvelle feuille Comparison avec l’identifiant, le champ modifié, l’ancienne valeur, la nouvelle valeur, la catégorie du résultat et les références des lignes sources. Inclus les enregistrements présents d’un seul côté. Ne modifie aucune feuille source. Résume les décomptes et montre les lignes non résolues.
Examinez la règle de rapprochement proposée avant d’accepter des modifications. L’IA peut aider à organiser la comparaison, mais elle ne peut pas déterminer si deux identifiants différents représentent la même commande sans une règle de votre part.
Vérifier le rapport avant de l’utiliser
- Contrôlez un enregistrement inchangé, un enregistrement modifié et un enregistrement propre à chaque source.
- Vérifiez que la somme des catégories correspond au nombre d’identifiants distincts ; comptez séparément les identifiants non résolus.
- Confirmez que le tri de l’une ou l’autre source ne modifie pas le classement des résultats.
- Assurez-vous que les plages remplies complètes ont été prises en compte, et pas seulement les lignes visibles ou filtrées.
- Conservez les exports d’origine pour qu’une autre personne puisse reproduire la comparaison.
Si l’étape suivante consiste à rapprocher des factures et des paiements plutôt qu’à comparer des versions, utilisez le processus de rapprochement des factures. Plusieurs paiements par facture nécessitent d’autres règles que cet exemple à un enregistrement par identifiant.
