Power Query : Comparer deux tables dans Excel

Comparer deux tables dans Excel est une exigence fréquente lorsqu'on travaille avec des données provenant de différentes sources, des ensembles de données mis à jour, ou pour effectuer des audits de données. Les objectifs courants incluent l'identification des enregistrements présents dans les deux tables, la détection des différences ou la spécification des entrées manquantes dans l'une ou l'autre liste. Par exemple, vous pouvez recevoir de nouvelles données d'un collègue ou d'un système externe et devoir les comparer avec votre liste principale pour vérifier les mises à jour ou les écarts. Dans les captures d'écran ci-dessous, deux tables ayant une structure de colonnes identique contiennent certains enregistrements différents et d'autres correspondants. La capacité à mettre efficacement en évidence ces différences ou similitudes est cruciale pour la validation des données et la prise de décision commerciale.

Ce tutoriel fournit une explication détaillée de la comparaison de deux tables en utilisant la fonctionnalité Power Query. De plus, si vous recherchez des méthodes alternatives et pratiques – y compris par formule, code VBA ou mise en forme conditionnelle – veuillez consulter les solutions ajoutées dans la table des matières ci-dessous.

Comparer deux tables dans Power Query

Solutions alternatives

• Formule Excel - Comparer deux tables avec une formule
• Code VBA - Comparer deux tables avec du code VBA
• Mise en forme conditionnelle - Comparer visuellement des tables

Comparer deux tables dans Power Query

Power Query est un outil efficace dans Excel pour restructurer et analyser des données provenant de plusieurs sources. Lorsque vous devez comparer deux tables ligne par ligne, Power Query automatise une grande partie du processus, réduisant ainsi les erreurs de comparaison manuelle et gagnant du temps. Veuillez suivre le guide étape par étape ci-dessous pour comparer deux tables, avec des notes et des conseils pratiques inclus pour assurer un flux de travail fluide.

Création de requêtes à partir des deux tables

1. Sélectionnez la première table que vous souhaitez comparer. Dans Excel 2019 et Excel 365, allez dans l'onglet Données et cliquez sur À partir du tableau/plage. Voir la capture d'écran ci-dessous.
Astuce : Assurez-vous que votre table est formatée comme un véritable Tableau Excel (Ctrl+T) avant de commencer. Cela aide Power Query à reconnaître précisément les limites des données.

Note: Dans Excel 2016 et Excel 2021, le menu indique Données > À partir du tableau. Ils sont équivalents en termes de fonction.
Si votre sélection n'est pas formatée comme un tableau, Excel peut vous inviter à en créer un.

2. La fenêtre Éditeur Power Query s'ouvre. Ici, vous pouvez examiner ou nettoyer vos données si nécessaire, mais pour la comparaison, vous pouvez procéder directement. Cliquez sur Fermer et charger > Fermer et charger vers pour définir les options de connexion.

3. Dans la boîte de dialogue Importer des données, choisissez Créer uniquement une connexion, puis cliquez sur OK. Cette option vous permet d'utiliser les données uniquement dans Power Query sans les charger immédiatement dans la feuille de calcul. Voir la capture d'écran suivante.

4. Répétez les étapes précédentes (1–3) pour créer une connexion pour votre deuxième table. Maintenant, les deux tables apparaissent comme des connexions distinctes dans le Requêtes et connexions volet. Cela prépare vos données pour l'étape de comparaison.
Astuce : Vérifiez deux fois que les deux tables ont des noms de colonnes et une structure identiques pour garantir une comparaison précise à l'étape suivante.

Joindre des requêtes pour comparer deux tables

Avec les deux requêtes créées, vous pouvez maintenant les fusionner pour comparer et révéler les différences ou correspondances ligne par ligne.

5. Dans Excel 2019 et Excel 365, sélectionnez l'onglet Données, puis cliquez sur Obtenir des données > Combiner des requêtes > Fusionner. Cela lance le processus de fusion. Voir la capture d'écran.

Note : Pour Excel 2016 et Excel 2021, accédez-y via Données > Nouvelle requête > Combiner des requêtes > Fusionner - le processus reste le même.

6. Dans la boîte de dialogue Fusionner :

• Sélectionnez les requêtes de la première et de la deuxième table dans les deux menus déroulants.
• Sélectionnez les colonnes que vous souhaitez comparer dans chaque table - appuyez sur Ctrl pour choisir plusieurs colonnes. En général, toutes les colonnes doivent être sélectionnées pour une comparaison ligne par ligne authentique.
• Choisissez Full Outer (toutes les lignes des deux côtés) comme type de jointure. Cette option met en correspondance toutes les lignes et met en évidence les enregistrements manquants, supplémentaires ou différents.
• Cliquez sur OK pour continuer.

7. Une nouvelle colonne apparaît pour les données correspondantes de la deuxième table :

• Cliquez sur le petit bouton Développer (deux flèches) à côté de l'en-tête de la nouvelle colonne.
• Sélectionnez Développer, et choisissez les colonnes à inclure dans les résultats (généralement, toutes les colonnes).
• Appuyez sur OK pour les insérer.

8. Les données de la deuxième table sont maintenant affichées à côté de celles de la première table, ce qui facilite la comparaison des enregistrements. Pour retourner ces données fusionnées dans Excel, allez dans Accueil > Fermer et charger > Fermer et charger. Cela ajoutera la comparaison côte à côte dans une nouvelle feuille de calcul.

9. Dans la feuille de calcul résultante, vous pouvez facilement repérer correspondances et discordances : les lignes identiques apparaissent côte à côte, tandis que les différences se distinguent par des cellules vides ou différentes. Cette disposition vous permet de localiser efficacement les enregistrements uniques, manquants ou modifiés entre les deux tables.
Conseil de dépannage : Si certains enregistrements ne correspondent pas comme prévu, revérifiez que les colonnes de jointure ont des formats cohérents et qu'il n'y ait ni espaces supplémentaires ni fautes de frappe dans vos données sources. Power Query est sensible même aux plus petites différences.

En résumé, Power Query simplifie le processus complexe de comparaison de deux tables dans Excel. Une fois que vous vous êtes familiarisé avec ces étapes, vous pouvez étendre le processus à des scénarios de données plus élaborés, tels que la comparaison de tables provenant de fichiers différents ou la réalisation de correspondances multi-champs. Lorsque vous travaillez avec des mises à jour fréquentes des données, enregistrer les étapes de Power Query sous forme de modèle peut accélérer les comparaisons futures avec des structures similaires. Si vous rencontrez des difficultés avec la sélection de colonnes ou la fusion, revérifiez les types de données des colonnes et envisagez de supprimer à l'avance toute ligne en double pour éviter des problèmes de correspondance.

Si Power Query semble trop avancé pour des tâches occasionnelles, ou si vous avez besoin de mettre en évidence directement les différences dans la feuille de calcul ou d'automatiser des comparaisons répétitives, vous pouvez également utiliser les solutions alternatives décrites ci-dessous.

Formule Excel - Comparer deux tables avec une formule

Comparer deux tables ligne par ligne pour détecter des différences ; une méthode puissante pour effectuer cette vérification consiste à utiliser la fonction TEXTJOIN d'Excel en combinaison avec une formule SI.

Supposons que vous ayez Table1 dans les cellules A2:C10 et Table2 dans les cellules F1:H10, et que vous souhaitiez vérifier quels éléments de Table1 n'apparaissent pas dans Table2.

1. Entrez la formule suivante dans la cellule I2 :

=IF(TEXTJOIN("|",,A2:C2)=TEXTJOIN("|",,F2:H2), "Match", "Mismatch")

2. Ensuite, faites glisser la formule vers les autres cellules pour obtenir le résultat. Si les lignes de chaque table sont exactement identiques, la formule renvoie "Correspond" ; sinon, elle renvoie "Non correspondant".

Code VBA - Comparer deux tables avec automatisation macro

L'approche VBA est idéale lorsque vous souhaitez comparer rapidement et visuellement deux tables ligne par ligne sans encombrer votre feuille de calcul avec des formules. Elle est particulièrement utile pour auditer ou vérifier des données importées de manière propre et conviviale.

1. Cliquez sur Outils de développement > Visual Basic pour ouvrir l'éditeur VBA.

2. Dans l'éditeur, cliquez sur Insérer > Module et collez le code suivant dans la fenêtre du module :

Sub CompareSelectedTablesRowByRow() Dim rng1 As Range, rng2 As Range Dim rowCount As Long, colCount As Long Dim r As Long, c As Long Dim xTitle As String xTitle = "Compare Tables - KutoolsforExcel" On Error Resume Next Set rng1 = Application.InputBox("Select the first table range:", xTitle, Type:=8) If rng1 Is Nothing Then Exit Sub Set rng2 = Application.InputBox("Select the second table range:", xTitle, Type:=8) If rng2 Is Nothing Then Exit Sub On Error GoTo 0 If rng1.Rows.Count <> rng2.Rows.Count Or rng1.Columns.Count <> rng2.Columns.Count Then MsgBox "Selected ranges do not have the same size.", vbExclamation, xTitle Exit Sub End If rng1.Interior.ColorIndex = xlNone rng2.Interior.ColorIndex = xlNone For r = 1 To rng1.Rows.Count For c = 1 To rng1.Columns.Count If rng1.Cells(r, c).Value <> rng2.Cells(r, c).Value Then rng1.Cells(r, c).Interior.Color = vbYellow rng2.Cells(r, c).Interior.Color = vbYellow End If Next c Next r MsgBox "Comparison complete. Differences are highlighted in yellow.", vbInformation, xTitle End Sub 

3. Pour exécuter le code, sélectionnez le bouton Exécuter dans la fenêtre VBA, ou appuyez sur F5. Sélectionnez la plage de la première table, puis celle de la deuxième table lorsque vous y êtes invité. La macro vérifiera chaque cellule ligne par ligne des deux tables, si les valeurs diffèrent, les cellules des deux tables sont mises en surbrillance en jaune.

Mise en forme conditionnelle - Comparer visuellement des tables

La mise en forme conditionnelle offre une méthode visuellement intuitive pour mettre en évidence les différences ou les correspondances directement dans votre feuille de calcul. Cette méthode est appropriée lorsque vous devez identifier des valeurs dupliquées ou distinctes sans altérer les données sous-jacentes, et elle fonctionne particulièrement bien avec des tables plus petites affichées côte à côte.

1. Sélectionnez la plage de votre première table (par exemple, A1:C10).
2. Allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle.
3. Cliquez surUtiliser une formule pour déterminer quelles cellules mettre en forme et entrez cette formule :=A2<>F2
4. Cliquez sur Format, sélectionnez une couleur de remplissage, et cliquez sur OK > OK pour appliquer la règle.

Résultat : Les cellules mises en surbrillance représentent les valeurs dans Table1 qui n'existent pas dans Table2. Vous pouvez répéter le processus pour Table2 vs Table1 si nécessaire.

Découvrez la magie d'Excel avec Kutools AI

• Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
• Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
• Codage VBA : Écrivez et implémentez du code VBA sans effort.
• Interprétation des formules : Comprenez facilement des formules complexes.
• Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.

Améliorez vos capacités Excel avec des outils alimentés par l'IA. Téléchargez maintenant et découvrez une efficacité sans précédent !