Comparer deux tableaux Excel : Méthodes et astuces pour une analyse efficace

Pourquoi et comment comparer deux tableaux Excel ?

L'importance de la comparaison de tableaux

Dans le monde professionnel, la gestion et l'analyse de données sont cruciales. Souvent, vous vous retrouverez avec deux tableaux Excel contenant des informations similaires mais potentiellement différentes. La capacité à comparer deux tableaux Excel efficacement vous permet de :

• Identifier les doublons et les erreurs.
• Suivre les modifications apportées aux données au fil du temps.
• Valider la cohérence des informations entre différentes sources.
• Automatiser des processus de contrôle qualité.

Les différentes méthodes de comparaison

Il existe plusieurs approches pour comparer deux tableaux Excel, chacune ayant ses avantages et ses inconvénients. Le choix de la méthode dépendra de la taille des tableaux, de la complexité des données et du type d'informations que vous recherchez. Nous allons explorer les méthodes suivantes :

• Comparaison visuelle (manuelle).
• Utilisation des formules Excel (RECHERCHEV, SI, NB.SI, etc.).
• Mise en forme conditionnelle.
• Outil de comparaison de feuilles de calcul (Excel 365).
• Power Query.

Comparaison manuelle : Simple mais limitée

La méthode la plus simple est la comparaison visuelle. Il s'agit de parcourir les deux tableaux côte à côte et de repérer les différences à l'œil nu. Cette méthode est rapide pour les petits tableaux, mais elle devient fastidieuse et sujette aux erreurs pour les grands ensembles de données.

Avantages

• Simple et rapide pour les petits tableaux.
• Ne nécessite aucune connaissance technique particulière.

Inconvénients

• Très chronophage pour les grands tableaux.
• Risque élevé d'erreurs humaines.
• Ne permet pas de détecter facilement les doublons ou les données manquantes.

Utilisation des formules Excel pour la comparaison

Les formules Excel offrent une solution plus puissante et automatisée pour comparer deux tableaux Excel. Voici quelques formules couramment utilisées :

RECHERCHEV (VLOOKUP)

La fonction RECHERCHEV est idéale pour vérifier si une valeur d'un tableau se trouve également dans un autre tableau. Elle recherche une valeur dans la première colonne d'une plage et renvoie une valeur correspondante d'une autre colonne de cette même plage.

Exemple : Supposons que vous ayez deux tableaux de clients, un avec la liste complète et un autre avec les clients ayant effectué un achat. Vous pouvez utiliser RECHERCHEV pour identifier les clients de la liste complète qui ont effectué un achat.

Formule : =RECHERCHEV(A2;Tableau2!A:A;1;FAUX)

• A2 : La valeur à rechercher (le nom du client dans le premier tableau).
• Tableau2!A:A : La plage de recherche (la colonne des noms de clients dans le deuxième tableau).
• 1 : L'indice de la colonne à renvoyer (ici, la première colonne).
• FAUX : Pour une correspondance exacte.

Si la fonction RECHERCHEV renvoie une valeur (le nom du client), cela signifie que le client est présent dans les deux tableaux. Si elle renvoie #N/A, cela signifie que le client n'est pas présent dans le deuxième tableau.

SI (IF)

Combinée avec d'autres fonctions, la fonction SI permet de créer des tests conditionnels pour identifier les différences entre deux tableaux.

Exemple : Vous pouvez utiliser SI avec ESTNA (ISNA) pour vérifier si une valeur n'est pas trouvée par RECHERCHEV et afficher un message personnalisé.

Formule : =SI(ESTNA(RECHERCHEV(A2;Tableau2!A:A;1;FAUX));"Non trouvé";"Trouvé")

Cette formule affichera "Non trouvé" si le client n'est pas présent dans le deuxième tableau, et "Trouvé" sinon.

NB.SI (COUNTIF)

La fonction NB.SI permet de compter le nombre de fois qu'une valeur apparaît dans une plage. Elle est utile pour identifier les doublons dans un tableau ou pour vérifier si une valeur d'un tableau apparaît plusieurs fois dans un autre tableau.

Exemple : Vous pouvez utiliser NB.SI pour compter le nombre de fois qu'un produit apparaît dans une liste de commandes.

Formule : =NB.SI(Tableau1!A:A;A2)

• Tableau1!A:A : La plage où compter (la colonne des noms de produits dans le premier tableau).
• A2 : La valeur à compter (le nom du produit dans le deuxième tableau).

Si la fonction NB.SI renvoie une valeur supérieure à 1, cela signifie que le produit apparaît plusieurs fois dans la liste de commandes.

CONCATENER (CONCATENATE)

La fonction CONCATENER permet de combiner le contenu de plusieurs cellules en une seule. Elle peut être utile pour créer une clé unique à partir de plusieurs colonnes et comparer cette clé entre deux tableaux.

Exemple : Si vous avez deux colonnes (Nom et Prénom) dans chaque tableau, vous pouvez créer une clé unique en concaténant les deux colonnes.

Formule : =CONCATENER(A2;B2)

Vous pouvez ensuite utiliser RECHERCHEV ou NB.SI pour comparer les clés uniques entre les deux tableaux.

Utilisation de la mise en forme conditionnelle

La mise en forme conditionnelle permet de mettre en évidence les différences entre deux tableaux en appliquant des formats spécifiques (couleurs, icônes, etc.) aux cellules qui répondent à certains critères.

Mise en évidence des doublons

Vous pouvez utiliser la mise en forme conditionnelle pour mettre en évidence les doublons dans un tableau ou entre deux tableaux.

1. Sélectionnez la plage de cellules à analyser.
2. Cliquez sur "Mise en forme conditionnelle" dans l'onglet "Accueil".
3. Choisissez "Règles de mise en surbrillance des cellules" et sélectionnez "Valeurs en double".
4. Choisissez le format à appliquer aux doublons (par exemple, remplissage rouge).

Comparaison de deux colonnes

Vous pouvez utiliser une formule dans la mise en forme conditionnelle pour comparer deux colonnes et mettre en évidence les différences.

1. Sélectionnez la plage de cellules à analyser.
2. Cliquez sur "Mise en forme conditionnelle" dans l'onglet "Accueil".
3. Choisissez "Nouvelle règle...".
4. Sélectionnez "Utiliser une formule pour déterminer pour quelles cellules le format doit être appliqué".
5. Entrez une formule comme =A2<>B2 (pour comparer la cellule A2 avec la cellule B2) dans la zone de texte.
6. Cliquez sur "Format..." pour choisir le format à appliquer aux cellules qui ne sont pas égales.

L'outil de comparaison de feuilles de calcul (Excel 365)

Excel 365 propose un outil intégré de comparaison de feuilles de calcul qui permet d'identifier les différences entre deux versions d'un même classeur. Cet outil est particulièrement utile pour suivre les modifications apportées à un fichier Excel au fil du temps.

Activation de l'outil

L'outil de comparaison de feuilles de calcul n'est pas activé par défaut. Pour l'activer, suivez les étapes suivantes :

1. Cliquez sur "Fichier" > "Options".
2. Sélectionnez "Compléments".
3. Dans la liste "Gérer", sélectionnez "Compléments COM" et cliquez sur "Atteindre...".
4. Cochez la case "Inquire" et cliquez sur "OK".

Un nouvel onglet "Inquire" apparaîtra dans le ruban Excel.

Utilisation de l'outil

1. Ouvrez les deux versions du classeur Excel que vous souhaitez comparer.
2. Cliquez sur l'onglet "Inquire" et sélectionnez "Compare Files".
3. Sélectionnez les deux fichiers à comparer et cliquez sur "OK".

L'outil affichera un rapport détaillé des différences entre les deux fichiers, incluant les modifications de cellules, les changements de formules, les insertions/suppressions de lignes/colonnes, etc.

Power Query : Une solution puissante pour les données complexes

Power Query est un outil d'extraction, de transformation et de chargement de données (ETL) intégré à Excel. Il permet de se connecter à différentes sources de données, de les nettoyer, de les transformer et de les charger dans Excel. Power Query peut également être utilisé pour comparer deux tableaux Excel de manière sophistiquée.

Comparaison de deux tableaux avec Power Query

1. Chargez les deux tableaux dans Power Query en utilisant "Données" > "Obtenir des données externes" > "À partir d'un tableau/d'une plage".
2. Renommez les requêtes pour les identifier facilement (par exemple, "Tableau1" et "Tableau2").
3. Fusionnez les deux requêtes en utilisant "Accueil" > "Combiner" > "Fusionner les requêtes".
4. Choisissez les colonnes à utiliser pour la fusion (par exemple, les colonnes contenant les identifiants uniques).
5. Choisissez le type de jointure (par exemple, "Jointure externe gauche" pour conserver toutes les lignes du premier tableau et les lignes correspondantes du deuxième tableau).
6. Développez la colonne contenant les données du deuxième tableau pour afficher les colonnes correspondantes.
7. Créez une colonne personnalisée pour comparer les valeurs des colonnes correspondantes et identifier les différences.

Power Query offre une grande flexibilité pour la comparaison de tableaux complexes, avec la possibilité de gérer les types de données, de filtrer les données et de créer des transformations personnalisées.

Bonnes pratiques pour comparer deux tableaux Excel

• Nettoyer les données : Avant de comparer deux tableaux, assurez-vous qu'ils sont propres et cohérents. Supprimez les espaces inutiles, corrigez les erreurs de frappe et uniformisez les formats de données.
• Utiliser des identifiants uniques : Si possible, utilisez des identifiants uniques (par exemple, des numéros de référence) pour faciliter la comparaison des tableaux.
• Sauvegarder les données : Avant d'effectuer des modifications importantes, sauvegardez vos fichiers Excel pour éviter toute perte de données.
• Documenter les étapes : Documentez les étapes de votre processus de comparaison pour faciliter la reproduction et la compréhension de votre travail.
• Tester les formules : Vérifiez que vos formules fonctionnent correctement en les testant sur des exemples de données connus.

Erreurs courantes à éviter

• Comparaison de tableaux avec des structures différentes : Assurez-vous que les deux tableaux ont des structures similaires (colonnes, types de données) avant de les comparer.
• Oubli de la sensibilité à la casse : Certaines formules Excel sont sensibles à la casse. Utilisez la fonction MAJUSCULE (UPPER) ou MINUSCULE (LOWER) pour uniformiser la casse avant la comparaison.
• Ignorer les espaces : Les espaces peuvent fausser les résultats de la comparaison. Utilisez la fonction SUPPRESPACE (TRIM) pour supprimer les espaces inutiles.
• Mauvaise utilisation des références de cellules : Faites attention aux références de cellules (relatives, absolues, mixtes) lorsque vous copiez des formules.

En conclusion, comparer deux tableaux Excel efficacement est une compétence essentielle pour l'analyse de données. En maîtrisant les différentes méthodes présentées dans cet article, vous serez en mesure d'identifier rapidement les différences, les doublons et les données manquantes, et d'optimiser votre travail avec Excel.