Comment confronter deux listes de données dans Excel

Informations concernant l'auteur.e

Avec Microsoft Excel et ses formules, il est possible de suivre, d'analyser, de comparer diverses données. Si vous avez, par exemple, un document qui représente la référence dans votre domaine d'activité, il vous sera possible de comparer d'autres documents à ce dernier en utilisant la formule EQUIV. Autre aspect intéressant d'Excel, c'est de pouvoir croiser les informations de deux fichiers et ainsi d'identifier les différences pour permettre une analyse de ces dernières. Pour réaliser cette opération, il vous faudra utiliser la formule NB.SI.

Méthode 1

Méthode 1 sur 2:
Comparer des données avec la fonction EQUIV

1
Ayez vos données sur une seule feuille de calcul. Vous pouvez avoir deux listes de données que vous voulez comparer, mais ces dernières se trouvent sur des feuilles différentes où même dans des fichiers Excel différents. Pour faciliter la comparaison des données, copiez ces dernières et collez-les dans une même feuille de calcul. Cela facilitera la comparaison et vous évitera de faire des erreurs.
2
Trouvez un identifiant commun. Pour pouvoir effectuer une comparaison entre deux listes de données, vous devez avoir un élément commun dans chacune des deux listes. Par contre, si vous n'avez pas une référence commune pour chacune des listes, il vous faudra en créer une. Il sera donc nécessaire de rajouter une nouvelle colonne dans laquelle vous devrez y faire apparaitre une donnée qui sera unique pour une ligne dans une liste, mais commune à une ligne dans l'autre liste.
- Par exemple, vous avez un fichier mensuel sur les commandes de vos produits dans tous les magasins en France où ils sont vendus. Pour comparer les données entre chaque mois et connaitre les mois les plus intéressants de votre entreprise, il vous faudra une donnée commune qui correspondra à une même ligne dans chacune des listes. L'élément qui créera la correspondance pourra être le libellé du produit, son code de référencement, etc. Notez que pour que cela fonctionne, il vous faudra des tableaux au formatage identique, c'est-à-dire le même nombre de colonnes, d'entêtes, d'intitulés, etc.
- Il arrive que deux listes aient bien une donnée commune qui permettrait de les comparer, mais cela ne répondrait pas à votre besoin. Par exemple, vous pouvez comparer la vente d'un article entre deux mois ou deux années, mais le résultat sera pour toute la France et vous voulez un département bien précis. Il est possible de pallier cet inconvénient en utilisant la formule CONCATENER. Vous combinez, par exemple, le code de référence de l'article qui se trouve dans la cellule A2 avec la date en B2 et le numéro de département qui est noté en cellule C2. Cela vous donne la formule =CONCATENER(A2;B2;C2) que vous pourrez placer en bout de ligne, par exemple en J2. Si pour la même combinaison d'informations, vous souhaitez avoir seulement l'année ou le mois, il vous faudra juste inclure la formule adéquate avec B2. Avec seulement l'année, la formule devient =CONCATENER(A2;ANNEE(B2);C2) et avec seulement le mois, la formule s'écrit =CONCATENER(A2;MOIS(B2);C2).
- Quand vous avez choisi de quelle manière vous allez créer votre identifiant et que vous avez écrit la formule pour la première ligne de votre tableau, il ne vous reste plus qu'à la recopier pour toutes les autres lignes. Sélectionnez la cellule qui contient la formule, puis cliquez sur la poignée de recopie qui se trouve en bas à droite de votre cellule, étirez jusqu'à la dernière ligne de votre tableau. Répétez l'opération avec votre deuxième tableau. Vous avez, maintenant, deux tableaux avec un identifiant spécifique qui va vous permettre de comparer vos données.
3
Uniformisez votre style d'écriture. C'est un point important, si vous voulez bien comparer toutes vos données. Il vous faut de préférence utiliser les mêmes dénominations pour vos données, par exemple, « assoc. » et « association » sont pour vous la même chose, mais pour Excel il s'agit de deux données différentes.
- Si vous voulez, la formule GAUCHE peut vous aider. Elle permet selon une cellule sélectionnée de donner comme résultat dans une autre cellule le nombre de caractères à partir de la gauche que vous aurez défini. Cependant, cela complexifie votre tableau et vous n'êtes pas à l'abri de faire des erreurs. Du coup, cherchez plutôt à uniformiser la manière d'inscrire des données dans vos tableaux. Ne compliquez pas votre tableau, soyez juste rigoureux. Faites au plus simple et vous assurerez un suivi rapide et efficace.
- Dans votre tableau, si vous avez une colonne avec les codes postaux, vous pouvez avec la formule GAUCHE identifier les départements et ainsi effectuer une comparaison ciblée géographiquement. Pour une colonne avec des sommes, vous pouvez utiliser la formule ARRONDI pour arrondir les valeurs et ainsi les comparer plus facilement, car Excel ne le fera pas, si vous ne le lui dites pas de faire.
- Si vous avez plusieurs espaces entre des données, vous pouvez corriger en employant la formule SUPPRESPACE qui supprimera les espaces superflues.
4
Insérez une nouvelle colonne. Selon votre besoin, vous avez rajouté une colonne à chacun de vos tableaux pour y insérer un identifiant spécifique. Maintenant, il va vous falloir rajouter une nouvelle colonne, à vos deux tableaux, pour y inscrire la formule qui permettra de connaitre le résultat de la comparaison de vos deux listes de données.
- Pour l'entête de la colonne, vous pouvez inscrire « Absent », par exemple.
5
Tapez la formule pour la comparaison des données. Employez la formule EQUIV pour comparer les informations. Toutefois, il peut arriver que la formule ne trouve pas de réponse et donne le résultat « #N/A » au lieu de « FAUX » ou « VRAI » et donc vous risquez de ne pas tenir compte de ces lignes. Pour pallier ce point, vous allez inclure le résultat de votre comparaison à la formule ESTNA qui inscrira « VRAI » quand le résultat de la formule EQUIV sera « #N/A » et cela vous amènera à ne pas oublier ces lignes.
- Dans votre feuille de calcul, vous avez deux tableaux. Le premier tableau, par exemple, se trouve sur la plage A1 : E21 et votre deuxième tableau sur la plage G1 : K21. La première ligne est utilisée pour les entêtes et les vingt autres lignes sont pour les données. Dans le premier tableau, la colonne « D » est celle qui est intitulée « Identifiant » et la colonne « E » se nomme « Absent », c'est dans cette dernière colonne que vous allez inscrire la formule suivante =ESTNA(EQUIV(D2;$J$2:$J$21;FAUX)). Cette formule recherche dans la plage de données $J$2:$J$21 (données de la colonne « Identifiant » du deuxième tableau) s'il existe une donnée correspondante à celle de la cellule D2. Si la recherche est fructueuse, le résultat sera « FAUX » et si la recherche ne trouve pas une équivalence ou que des cellules sont vides, le résultat sera « VRAI » dans la cellule E2.
- Maintenant que vous avez écrit la formule dans la première cellule prévue de la colonne « E », vous pouvez copier la formule dans les autres cellules qui suivent. Sélectionnez votre cellule E2, puis cliquez sur la poignée de recopie qui se trouve dans le coin inférieur droit de votre cellule. Étirez votre sélection jusqu'à la dernière cellule de la colonne de votre tableau. Vous pourrez voir dans les différentes formules que ce qui est entouré par le signe « $ » n'a pas changé, seule la première donnée change.
- Copiez la formule dans la cellule K2 qui est la première cellule pour comparer les données du deuxième tableau avec celles du premier tableau. Ensuite, changez les données de la formule. Remplacez D2 par J2 la première valeur d'identification du deuxième tableau et $J$2:$J$21 par $D$2:$D$21 qui représente la plage de données d'identification du premier tableau. Votre formule devient donc =ESTNA(EQUIV(J2;$D$2:$D$21;FAUX)). Ensuite, copiez la formule en dessous dans les cellules de la colonne.
6
Organisez vos données. Si vous avez des listes très longues, cela deviendra rapidement compliqué de visualiser les lignes que vous voulez voir. L'idéal est de trier vos données suivant un critère à partir de la colonne « Absent » qui compare les données des deux listes. Pour commencer, il sera intéressant de changer les formules en données pour ne pas avoir de problèmes avec les différents calculs, puis de trier.
- Sélectionnez toutes les cellules d'une plage de données d'un de vos tableaux.
- Dans la version 2003 d'Excel, cliquez dans la barre de menu sur Édition, puis sur Copier. Pour les versions 2007 et 2010 d'Excel, sélectionnez l'onglet Accueil dans le ruban, puis dans la rubrique Presse-papiers, appuyez sur l'icône Copier.
- Pour le collage, il vous faudra cliquer sur Collage spécial après avoir ouvert le menu déroulant d'Édition dans la version 2003 d'Excel. Pour les versions 2007 et 2010, appuyez sur ▼ de l'icône Coller de l'onglet Accueil, puis sur Collage spécial.
- Dans la boite de dialogue de Collage spécial, sélectionnez Valeurs, puis sur le bouton OK pour valider et fermer la boite de dialogue.
- Pour effectuer un tri, allez dans la barre de menu d'Excel 2003 et appuyez sur Données, puis sur Trier. Pour Excel 2007 et 2010, cliquez sur l'onglet Données du ruban et ensuite, sur l'icône Trier.
- Assurez-vous que Oui pour l'option Ma page de données à une ligne de titres est sélectionnée dans la version 2003 d'Excel. Pour les versions 2007 et 2010, l'option Mes données ont des entêtes doit être cochée. Pour l'option Trier par (les 3 versions d'Excel), choisissez Absent (entête de la colonne qui compare les données). Ensuite, cliquez sur OK.
- Maintenant que vous avez configuré votre premier tableau, faites la même chose pour le deuxième.
7
Faites une vérification visuelle. Vérifiez les différentes lignes, vous-même, pour vous assurer que vous n'avez pas une petite exception qui serait présente dans l'une de vos listes. Sachez qu'Excel cherchera une concordance exacte entre les données, sauf bien sûr, si vous lui demandez de rechercher une valeur approximative. Il est important de faire une vérification visuelle, car vous pouvez avoir fait une erreur de frappe.

Publicité

Méthode 2

Méthode 2 sur 2:
Utiliser la formule NB.SI pour voir les différences

1
Importez vos données. Faites une copie de vos listes et collez-les dans une seule feuille de calcul.
2
Définissez votre approche. Vous pouvez faire ressortir les données qui sont identiques aux deux listes ou identifier les données d'une liste qui sont différentes à l'autre liste. Vous pourrez, par exemple, distinguer les données de la première liste qui n'ont pas d'équivalent dans la deuxième liste. Autre approche, faites apparaitre les données qui sont identiques dans les deux listes. Pour présenter une approche correctement, nous partirons sur le fait que votre première liste débute à la cellule B2 et se termine à la cellule B21. La deuxième liste quant à elle se trouvera entre les cellules E2 et E21.
3
Sélectionnez la liste de données. Cliquez dans la première cellule de la liste pour laquelle vous voulez faire apparaitre les éléments uniques ou ceux qui sont communs à l'autre liste. Pour faire ressortir des données similaires dans les deux listes, il sera intéressant que vous appliquiez la formule à chacune des deux listes.
4
Utilisez la formule et un format conditionnel. Dans Excel 2003, allez dans la barre de menu et cliquez sur Format, puis sur 'Mise en forme conditionnelle. Pour les versions 2007 et 2010, appuyez sur l'onglet Accueil dans le ruban et sélectionnez l'icône Mise en forme conditionnelle, puis Nouvelles règles. Une boite de dialogue s'ouvre. Pour la version 2003, changez La valeur de la cellule est par La formule est. Pour les deux autres versions d'Excel, sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué. Enfin, inscrivez la formule dans l'espace dédié.
- Pour pouvoir faire apparaitre les éléments qui sont uniques de la première liste, tapez la formule =NB.SI($E$2:$E$21;B2). Cette formule contrôle dans la plage $E$2:$E$21 qui est la seconde liste, s'il y a une valeur qui est égale à celle qui se trouve en B2 qui est une donnée de la première liste. Notez le signe « $ » est là pour que lors de la recopie sur les autres cellules, cette plage de données reste inchangée. À l'inverse, B2 évoluera et passera B3 en faisant glisser la cellule B2 sélectionnée vers la cellule en dessous. N'oubliez pas de corriger une fois la recopie terminée en cochant Ne recopier que la mise en forme dans la petite icône qui apparaitra.
- Pour effectuer la même vérification des données de la deuxième liste suivant les éléments contenus dans la première liste. Il vous faudra appliquer la même formule, mais avec les éléments appropriés. La formule sera =NB.SI($B$2:$B$21;E2). La formule cherchera dans $B$2:$B$21 qui est la plage de données de la première liste, s'il y a une donnée qui est égale à celle qui se trouve en E2 qui fait partie de la seconde liste.
- Pour connaitre les données communes entre les deux listes, c'est très simple, puisque si une donnée n'est pas unique à une liste cela veut dire qu'elle est commune aux deux listes. Vous n'avez donc pas besoin de rajouter ou modifier quoi que ce soit, vous devez juste utiliser la fonction Filtrer. Sélectionnez vos entêtes de colonnes qui contiennent des données, puis cliquez sur Filtrer dans le menu déroulant de 'Données qui se trouve dans la barre de menu d'Excel 2003 et dans l'onglet Données du ruban pour les versions 2007 et 2010. Ensuite, vous pourrez filtrer sur les données qui sont uniques ou communes.
- Selon la formule utilisée, vous devrez choisir le type de format et ici de couleur qui devra apparaitre dans la cellule, si la donnée est commune aux deux listes. Notez qu'utiliser la formule SI, dans une autre colonne, serait tout aussi simple et surtout utile dans la version 2003, car vous pourriez faire apparaitre une valeur et ce serait plus facile pour filtrer (Excel 2003 ne filtre pas sur les couleurs).
Publicité

Conseils

Suivant le type de listes que vous souhaitez comparer, vous pouvez dans la formule NB.SI inscrite dans la fenêtre de Mise en forme conditionnelle la recherche d'une valeur et non le contenu d'une cellule, puis filtrer sur cette valeur trouvée dans certaines lignes.
Si vous voulez, vous pouvez nommer vos listes, par exemple « Liste1 » et « Liste2 » pour plus de confort et ainsi inscrire les noms au lieu des plages de données quand vous aurez à écrire vos formules pour comparer les données de vos deux listes.