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 lorsque vous comparez des enregistrements financiers ou vérifiez 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, ou utiliser la mise en forme avec la fonction COUNTIF. Les étapes suivantes vous indiquent comment utiliser chacune d’elles pour correspondre à vos données.

Méthode One of Two:
Identification des enregistrements avec la fonction MATCH

  1. 1 Copiez 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 la comparaison des listes est plus simple si vous copiez leurs informations sur une seule feuille de calcul.
  2. 2 Attribuez un identifiant unique à chaque élément de la liste. 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 identifiant cet élément dans Excel afin de voir si un élément d'une liste donnée est associé à 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, telles que les enregistrements fiscaux, cela peut être la description d'un actif, la date d'acquisition de l'actif ou les 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 à effectuer.
    • Dans certains cas, vous pouvez extraire 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 ressource physique et l'année d'achat. Pour créer un tel identifiant, vous concaténez (ajoutez, combinez) des données provenant de plusieurs cellules à l'aide de l'esperluette (&). Pour combiner une description d'article dans la cellule F3 avec une date dans la cellule G3, séparée par un espace, entrez la formule '= F3 & "" & G3 "dans une autre cellule de cette ligne, telle que E3. Si vous souhaitez inclure uniquement l'année dans l'identificateur (car une liste utilise des dates complètes et l'autre uniquement des années), vous devez inclure la fonction ANNEE en entrant "= F3 &" "& YEAR (G3)" dans la cellule E3. (N'incluez pas les guillemets simples; ils ne sont là que pour indiquer l'exemple.)
    • Une fois la formule créée, vous pouvez la copier dans toutes les autres cellules de la colonne d'identifiant en sélectionnant la cellule contenant 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 déplacée sera remplie avec la formule, avec les références de cellule ajustées aux cellules appropriées dans la même ligne.
  3. 3 Standardiser les données dans la mesure du possible. Alors que l'esprit reconnaît que "Inc." et "Incorporated" signifie la même chose, Excel ne le fait pas sauf si vous le reformatez un mot ou l'autre. De même, vous pouvez considérer que des valeurs telles que 11 950 $ et 11 999,95 $ sont suffisamment proches pour correspondre, mais Excel ne le fera pas, sauf si vous le lui dites.
    • Vous pouvez traiter certaines abréviations, telles que "Co" pour "Company" et "Inc" pour "Incorporated en utilisant la fonction de chaîne LEFT pour tronquer les caractères supplémentaires. D'autres abréviations, telles que" Assn "pour" Association ", peuvent être traité en établissant un guide de saisie des données, puis en écrivant un programme permettant de rechercher et de corriger les formats incorrects.
    • Pour les chaînes de nombres, 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 LEFT pour reconnaître et correspondre uniquement les codes postaux principaux. Pour que Excel reconnaisse les valeurs numériques proches mais pas identiques, vous pouvez utiliser la fonction ROUND pour arrondir les valeurs proches au même numéro 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.
  4. 4 Créez des colonnes pour la formule de comparaison. Tout comme vous avez dû créer des colonnes pour les identificateurs de liste, vous devez 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?"
  5. 5 Entrez 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 "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", où une cellule de la colonne d'identifiant de la première liste est comparée à chacun des identificateurs de la deuxième liste. voir si elle correspond à l'un d'eux. S'il ne correspond pas, un enregistrement est manquant et le mot "TRUE" sera affiché dans cette cellule. Si elle correspond, l'enregistrement est présent et le mot "FALSE" sera affiché. (Lors de la saisie de la formule, n'incluez pas les guillemets fermants.)
    • Vous pouvez copier la formule dans les cellules restantes de la colonne de la même manière que vous avez copié la formule de l'identificateur de cellule. Dans ce cas, seule la référence de cellule pour la cellule d'identifiant change, car placer les signes dollar devant les références de ligne et de colonne pour les première et dernière cellules de la liste des identificateurs de deuxième 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 pour la deuxième liste. Vous devrez ensuite éditer les références de cellule pour que "G3" soit remplacé par la référence de la première cellule d'identifiant de la deuxième liste et que "$ L $ 3: $ L $ 14" soit remplacé par le premier et le dernier identifiant de la cellule. 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.
  6. 6 Triez les listes pour voir plus facilement les valeurs non correspondantes, si nécessaire. Si vos listes sont volumineuses, vous devrez peut-être les trier pour regrouper toutes les valeurs non correspondantes. Les instructions des sous-étapes ci-dessous convertissent les formules en valeurs pour éviter les erreurs de recalcul, et si vos listes sont volumineuses, vous éviterez 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 d'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 dans le bouton déroulant Coller du groupe Presse-papiers du ruban Accueil Excel 2007 ou 2010s.
    • Sélectionnez "Valeurs" dans la liste Coller sous dans 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 dans le groupe Trier et filtrer du ruban Données dans Excel 2007 ou 2010.
    • Sélectionnez "Rangée 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 donné à l'en-tête de la colonne de comparaison) et cliquez sur OK.
    • Répétez ces étapes pour l'autre liste.
  7. 7 Comparez les éléments non correspondants visuellement pour voir pourquoi ils ne correspondent pas. Comme indiqué précédemment, Excel est conçu pour rechercher des correspondances de données exactes à moins que vous ne les configuriez pour rechercher des approximations. Votre non-correspondance pourrait ê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 par exemple vérifier si les actifs listés doivent être déclarés en premier lieu.

Méthode deux sur deux:
Formatage conditionnel avec COUNTIF

  1. 1 Copiez les listes de données sur une seule feuille de calcul.
  2. 2 Décidez dans quelle liste vous souhaitez mettre en évidence les enregistrements correspondants ou non. 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 évidence des enregistrements dans les deux listes, vous devez mettre en surbrillance les enregistrements correspondants. Pour les besoins de cet exemple, nous supposerons que la première liste reprend les cellules G3 à G14 et la seconde liste les cellules L3 à L14.
  3. 3 Sélectionnez les éléments de la liste sur lesquels vous souhaitez mettre en évidence des éléments uniques ou correspondants. Si vous souhaitez mettre en évidence les éléments correspondants dans les deux listes, vous devez sélectionner les listes une par une et appliquer la formule de comparaison (décrite à l'étape suivante) à chaque liste.
  4. 4 Appliquez la formule de comparaison appropriée. Pour ce faire, vous devez 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 voulez mettre en évidence des enregistrements uniques à la première liste, la formule serait "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", avec la plage de cellules de la deuxième liste rendue comme valeurs absolues et la référence à la première cellule de la première liste en tant que valeur relative. (Ne pas entrer les guillemets fermés.)
    • Si vous voulez mettre en évidence des enregistrements uniques à la deuxième liste, la formule serait "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", avec la plage de cellules de la première liste rendue comme valeurs absolues et la référence à la première cellule de la deuxième liste en tant que valeur relative. (Ne pas entrer les guillemets fermés.)
    • Si vous souhaitez mettre en évidence les enregistrements de chaque liste figurant dans l'autre liste, vous aurez besoin de deux formules, une pour la première liste et une pour la seconde. La formule de la première liste est "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", tandis que la formule de la deuxième liste est COUNTIF ($ G $ 3: $ G $ 14, L3> 0). notée 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 souhaité pour mettre en évidence les enregistrements marqués. Cliquez sur OK pour fermer la boîte de dialogue.