Comment faire correspondre les données dans Excel?
Si vous souhaitez mettre en surbrillance des enregistrements dans les deux listes, vous devrez mettre en évidence des enregistrements qui correspondent les uns aux autres.
L'une des nombreuses fonctionnalités de Microsoft Excel est la possibilité de comparer deux listes de données, d'identifier les correspondances entre les listes et d'identifier les éléments trouvés dans une seule liste. Ceci est utile lors de la comparaison d'enregistrements financiers ou de la vérification pour voir si un nom particulier se trouve dans une base de données. Vous pouvez utiliser la fonction MATCH pour identifier et marquer les enregistrements correspondants ou non correspondants, ou vous pouvez utiliser le formatage de conditionnement avec la fonction COUNTIF. Les étapes suivantes vous indiquent comment utiliser chacune pour faire correspondre vos données.
Méthode 1 sur 2: identification des enregistrements avec la fonction MATCH
- 1Copiez les listes de données sur une seule feuille de calcul. Excel peut fonctionner avec plusieurs feuilles de calcul dans un seul classeur ou avec plusieurs classeurs, mais vous trouverez plus facile de comparer les listes si vous copiez leurs informations sur une seule feuille de calcul.
- 2Donnez à chaque élément de la liste un identifiant unique. Si vos deux listes ne partagent pas un moyen commun de les identifier, vous devrez peut-être ajouter une colonne supplémentaire à chaque liste de données qui identifie cet élément dans Excel afin qu'il puisse voir si un élément d'une liste donnée est lié à un élément dans l'autre liste. La nature de cet identifiant dépendra du type de données que vous essayez de faire correspondre. Vous aurez besoin d'un identifiant pour chaque liste de colonnes.
- Pour les données financières associées à une période donnée, comme les dossiers fiscaux, il peut s'agir de la description d'un actif, de la date d'acquisition de l'actif ou des deux. Dans certains cas, une entrée peut être identifiée par un numéro de code; cependant, si le même système n'est pas utilisé pour les deux listes, cet identifiant peut créer des correspondances là où il n'y en a pas ou ignorer les correspondances qui devraient être faites.
- Dans certains cas, vous pouvez prendre des éléments d'une liste et les combiner avec des éléments d'une autre liste pour créer un identifiant, tel qu'une description de l'actif physique et l'année de son achat. Pour créer un tel identifiant, vous concaténez (ajoutez, combinez) les données de deux ou plusieurs cellules à l'aide de l'esperluette (&). Pour combiner une description d'élément dans la cellule F3 avec une date dans la cellule G3, séparée par un espace, vous devez saisir la formule '=F3&" "&G3' dans une autre cellule de cette ligne, telle que E3. Si vous vouliez inclure uniquement l'année dans l'identifiant (parce qu'une liste utilise des dates complètes et l'autre n'utilise que des années), vous incluriez la fonction ANNÉE en saisissant à la place '=F3&" "&ANNÉE(G3)' dans la cellule E3. (N'incluez pas les guillemets simples; ils ne sont là que pour indiquer l'exemple.)
- Une fois que vous avez créé la formule, vous pouvez la copier dans toutes les autres cellules de la colonne d'identifiant en sélectionnant la cellule avec la formule et en faisant glisser la poignée de remplissage sur les autres cellules de la colonne où vous souhaitez copier la formule. Lorsque vous relâchez le bouton de la souris, chaque cellule sur laquelle vous avez fait glisser sera remplie avec la formule, avec les références de cellule ajustées aux cellules appropriées de la même ligne.
- 3Standardisez les données dans la mesure du possible. Alors que l'esprit reconnaît que "Inc." et "Incorporé" signifient la même chose, Excel ne le fait pas à moins que vous ne l'ayez reformaté d'un mot ou d'un autre. De même, vous pouvez considérer des valeurs telles que 8920€ et 8960€ comme suffisamment proches pour correspondre, mais Excel ne le fera pas à moins que vous ne le lui demandiez.
- Vous pouvez gérer certaines abréviations, telles que «Co» pour «Société» et «Inc» pour « Incorporated» en utilisant la fonction de chaîne GAUCHE pour tronquer les caractères supplémentaires. D’autres abréviations, telles que «Assn» pour «Association», peuvent être mieux adaptées traité en établissant un guide de style de saisie de données, puis en écrivant un programme pour rechercher et corriger les formats incorrects.
- Pour les chaînes de chiffres, telles que les codes postaux où certaines entrées incluent le suffixe ZIP+4 et d'autres non, vous pouvez à nouveau utiliser la fonction de chaîne GAUCHE pour reconnaître et faire correspondre uniquement les codes postaux principaux. Pour qu'Excel reconnaisse les valeurs numériques proches mais pas identiques, vous pouvez utiliser la fonction ROUND pour arrondir les valeurs proches au même nombre et les faire correspondre.
- Les espaces supplémentaires, tels que la saisie de deux espaces entre les mots au lieu d'un, peuvent être supprimés à l'aide de la fonction TRIM.
Les étapes suivantes vous indiquent comment utiliser chacune pour faire correspondre vos données. - 4Créez des colonnes pour la formule de comparaison. Tout comme vous deviez créer des colonnes pour les identifiants de liste, vous devrez créer des colonnes pour la formule qui effectue la comparaison pour vous. Vous aurez besoin d'une colonne pour chaque liste.
- Vous voudrez étiqueter ces colonnes avec quelque chose comme «Manquant?»
- 5Entrez la formule de comparaison dans chaque cellule. Pour la formule de comparaison, vous utiliserez la fonction MATCH imbriquée dans une autre fonction Excel, ISNA.
- La formule prend la forme de "=ISNA(MATCH(G3,$L2,20€:$L10€, FALSE))", où une cellule de la colonne identifiant de la première liste est comparée à chacun des identifiants de la seconde liste pour voir si elle correspond à l'un d'entre eux. S'il ne correspond pas, un enregistrement est manquant et le mot «TRUE» sera affiché dans cette cellule. S'il correspond, l'enregistrement est présent et le mot "FALSE" sera affiché. (Lors de la saisie de la formule, n'incluez pas les guillemets.)
- Vous pouvez copier la formule dans les cellules restantes de la colonne de la même manière que vous avez copié la formule d'identifiant de cellule. Dans ce cas, seule la référence de cellule pour l'identifiant de cellule change, car le fait de placer les signes dollar devant les références de ligne et de colonne pour la première et la dernière cellule de la liste des deuxièmes identifiants de cellule en fait des références absolues.
- Vous pouvez copier la formule de comparaison de la première liste dans la première cellule de la colonne de la deuxième liste. Vous devrez alors éditer les références des cellules pour que "G3" soit remplacé par la référence de la première cellule identifiant de la deuxième liste et "$L2,20€:$L10€" soit remplacé par la première et dernière cellule identifiant de la deuxième liste. (Laissez les signes dollar et les deux-points seuls.) Vous pouvez ensuite copier cette formule modifiée dans les cellules restantes de la ligne de comparaison de la deuxième liste.
- 6Triez les listes pour voir plus facilement les valeurs qui ne correspondent pas, si nécessaire. Si vos listes sont volumineuses, vous devrez peut-être les trier pour regrouper toutes les valeurs qui ne correspondent pas. Les instructions des sous-étapes ci-dessous convertiront les formules en valeurs pour éviter les erreurs de recalcul et, si vos listes sont volumineuses, éviteront un long temps de recalcul.
- Faites glisser votre souris sur toutes les cellules d'une liste pour la sélectionner.
- Sélectionnez Copier dans le menu Edition dans Excel 2003 ou dans le groupe Presse-papiers du ruban Accueil dans Excel 2007 ou 2010.
- Sélectionnez Collage spécial dans le menu Edition d'Excel 2003 ou à partir du bouton déroulant Coller dans le groupe Presse-papiers du ruban Accueil d'Excel 2007 ou 2010.
- Sélectionnez "Valeurs" dans la liste Coller sous de la boîte de dialogue Collage spécial. Cliquez sur OK pour fermer la boîte de dialogue.
- Sélectionnez Trier dans le menu Données dans Excel 2003 ou le groupe Trier et filtrer du ruban Données dans Excel 2007 ou 2010.
- Sélectionnez « Ligne d'en-tête» dans la liste «Ma plage de données a» dans la boîte de dialogue Trier par, sélectionnez «Manquant?» (ou le nom que vous avez réellement donné à l'en-tête de la colonne de comparaison) et cliquez sur OK.
- Répétez ces étapes pour l'autre liste.
- 7Comparez visuellement les éléments qui ne correspondent pas pour voir pourquoi ils ne correspondent pas. Comme indiqué précédemment, Excel est conçu pour rechercher des correspondances de données exactes, sauf si vous le configurez pour rechercher des correspondances approximatives. Votre non-correspondance peut être aussi simple qu'une transposition accidentelle de lettres ou de chiffres. Cela pourrait également être quelque chose qui nécessite une vérification indépendante, comme vérifier si les actifs répertoriés devaient être déclarés en premier lieu.
La nature de cet identifiant dépendra du type de données que vous essayez de faire correspondre.
Méthode 2 sur 2: mise en forme conditionnelle avec countif
- 1Copiez les listes de données sur une seule feuille de calcul.
- 2Décidez dans quelle liste vous souhaitez mettre en évidence les enregistrements correspondants ou non correspondants. Si vous souhaitez mettre en évidence des enregistrements dans une seule liste, vous souhaiterez probablement mettre en évidence les enregistrements uniques à cette liste; c'est-à-dire les enregistrements qui ne correspondent pas aux enregistrements de l'autre liste. Si vous souhaitez mettre en surbrillance des enregistrements dans les deux listes, vous devrez mettre en évidence des enregistrements qui correspondent les uns aux autres. Pour les besoins de cet exemple, nous supposerons que la première liste occupe les cellules G3 à G14 et que la deuxième liste occupe les cellules L3 à L14.
- 3Sélectionnez les éléments de la liste dans lesquels vous souhaitez mettre en évidence des éléments uniques ou correspondants. Si vous souhaitez mettre en évidence des éléments correspondants dans les deux listes, vous devrez sélectionner les listes une par une et appliquer la formule de comparaison (décrite à l'étape suivante) à chaque liste.
- 4Appliquez la formule de comparaison appropriée. Pour ce faire, vous devrez accéder à la boîte de dialogue Mise en forme conditionnelle dans votre version d'Excel. Dans Excel 2003, vous le faites en sélectionnant Mise en forme conditionnelle dans le menu Format, tandis que dans Excel 2007 et 2010, vous cliquez sur le bouton Mise en forme conditionnelle dans le groupe Styles du ruban Accueil. Sélectionnez le type de règle comme "Formule" et entrez votre formule dans le champ Modifier la description de la règle.
- Si vous souhaitez mettre en évidence des enregistrements uniques à la première liste, la formule serait "=COUNTIF($L2,20€:$L10€,G3=0)", avec la plage de cellules de la deuxième liste rendue en valeurs absolues et la référence à la première cellule de la première liste en tant que valeur relative. (N'entrez pas les guillemets de fermeture.)
- Si vous souhaitez mettre en évidence des enregistrements uniques à la deuxième liste, la formule serait "=COUNTIF($G2,20€:$G10€,L3=0)", avec la plage de cellules de la première liste rendue en valeurs absolues et la référence à la première cellule de la deuxième liste en tant que valeur relative. (N'entrez pas les guillemets de fermeture.)
- Si vous souhaitez mettre en évidence les enregistrements de chaque liste qui se trouvent dans l'autre liste, vous aurez besoin de deux formules, une pour la première liste et une pour la seconde. La formule pour la première liste est "=COUNTIF($L2,20€:$L10€,G3>0)", tandis que la formule pour la deuxième liste est COUNTIF($G2,20€:$G10€,L3>0)". Comme indiqué précédemment, vous sélectionnez la première liste pour appliquer sa formule, puis sélectionnez la deuxième liste pour appliquer sa formule.
- Appliquez le formatage que vous souhaitez pour mettre en évidence les enregistrements signalés. Cliquez sur OK pour fermer la boîte de dialogue.
Pour créer un tel identifiant, vous concaténez (ajoutez, combinez) les données de deux ou plusieurs cellules à l'aide de l'esperluette (&).
- Au lieu d'utiliser une référence de cellule avec la méthode de mise en forme conditionnelle COUNTIF, vous pouvez entrer une valeur à rechercher et marquer une ou plusieurs listes pour les instances de cette valeur.
- Pour simplifier les formulaires de comparaison, vous pouvez créer des noms pour votre liste, tels que "List1" et "List2". Ensuite, lors de l'écriture des formules, ces noms de liste peuvent remplacer les plages de cellules absolues utilisées dans les exemples ci-dessus.
Lisez aussi: Comment programmer un moteur 2D?
