«

»

Comparer 2 colonnes

Pour comparer le contenu de 2 colonnes et  afficher les données qui sont communes ou celles qui sont absentes, nous allons tout simplement utiliser la fonction RECHERCHEV associée à la fonction ESTNA et également la fonction SI


Démo live dans un classeur 😃😍😎



Dans le classeur ci-dessous, amusez-vous à changer les valeurs de la première colonne pour voir en rouge les valeurs absentes et en blancs les valeurs présentes



Comparer 2 colonnes

Comparer 2 colonnes ça se fait très facilement avec Excel.

Tout repose sur le fait que la fonction RECHERCHEV trouve, ou pas, une valeur dans une colonne.

Si vous ne connaissez pas bien la fonction RECHERCHEV, vous trouverez toutes les explications utiles dans cet article.

Pour beaucoup d’utilisateurs d’Excel,quand la fonction RECHERCHEV retourne #N/A, c’est une erreur.

Non pas du tout !  #N/A signifie “je n’ai pas trouvé l’élément recherché“.

Dans le cas d’une comparaison entre 2 colonnes c’est la valeur #N/A qui va nous intéresser 😃. Ainsi, nous serons capable d’identifier les éléments manquants entre les deux colonnes 😉😉.



Etape 1 : La fonction RECHERCHEV

Commençons par écrire notre fonction RECHERCHEV et le premier élément que vous voulons rechercher. Ici, nous voulons rechercher le contenu de la cellule A1.

=RECHERCHEV(A1;

Ensuite, Nous allons vérifier que cette valeur est présente dans la colonne $D$1:$D$4 (n’oubliez pas les $ pour verrouiller les références 😉)

=RECHERCHEV(A1;$D$1:$D$4;

Enfin, il suffit de mettre le troisième paramètre à 1 (car nous n’avons sélectionner qu’une seule colonne en deuxième paramètre) et enfin 0 (pour faire une recherche exacte).

=RECHERCHEV(A1;$D$1:$D$4;1;0)

comparer_2_colonnes_2

comparer_2_colonnes_3En recopiant la formule, nous obtenons le résultat suivant (la valeur manquante c’est la lettre c)

 

 

 

La valeur manquante est immédiatement identifiée par l’indication #N/A mais ce n’est vraiment pas joli de conserver cette indication dans votre classeur😱😱 💔



Etape 2 : La fonction ESTNA pour faire un test

Pour personnaliser le résultat d’une comparaison, nous allons utiliser la fonction ESTNA. La fonction va nous retourner 2 valeurs

  • VRAI : la fonction RECHERCHEV retourne #N/A
  • FAUX : la recherche a abouti à un résultat

Donc, en écrivant notre précédente formule RECHERCHEV à l’intérieur d’une fonction ESTNA, nous affichons maintenant dans la colonne les résultats VRAI ou FAUX selon que la recherche a abouti ou non.

=ESTNA(RECHERCHEV(A1;$D$1:$D$4;1;0))

comparer_2_colonnes_4

Etape 3 : Terminer avec la fonction SI



Laisser VRAI ou FAUX comme résultat n’est toujours pas très visible, surtout si vous devez présenter ce document à d’autres collaborateurs.

Voilà pourquoi, nous allons intégrer ce test dans une fonction SI pour personnaliser le résultat afin de ne pas laisser VRAI/FAUX dans les cellules.

Nous pouvons écrire la fonction SI suivante :

=SI(ESTNA(RECHERCHEV(A1;$D$1:$D$4;1;0));”Manquant”;””)

comparer_2_colonnes_5

Changer la couleur des valeurs manquantes

Beaucoup d’utilisateurs n’ont pas fait la relation entre un test logique et les mises en forme conditionnelles. Pourtant dès que vous avez créé un test dans une cellule, il vous suffit de le recopier en tant que critère d’une mise en forme conditionnelle pour que celle-ci fonctionne.

Par exemple, nous allons reprendre exactement le test VRAI/FAUX réalisé avec la fonction ESTNA pour le copier en tant que règle.

comparer_2_colonnes_6Dans notre exemple

  1. Commencez par sélectionnez votre colonne de comparaison (ici la colonne A)
  2. Ensuite, ouvrez le menu Accueil>Mise en forme conditionnelle>Nouvelle règle.
  3. Puis, choisissez l’option Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
  4. Dans la zone de texte, coller la formule qui nous a permis de retourner VRAI ou FAUX
  5. Terminer par votre mise en forme personnalisée en cliquant sur le bouton Format …

 

comparer_2_colonnes_7Finalement, le résultat dans votre feuille de calcul ce sont des cellules en fond rouge quand la valeur n’est pas trouvée dans la seconde colonne 😍😍😍

 

C’est tout de même plus compréhensif de montrer les écarts par une couleur plutôt que de laisser une formule 😊😉

Articles complémentaires

Voici une liste d’articles qui pourraient également vous intéresser dans la recherche des doublons.



Lien Permanent pour cet article : https://www.excel-exercice.com/comparer-2-colonnes/

(5 commentaires)

Passer au formulaire de commentaire

  1. NSENGUMUREMYI Emmanuel

    pour moi j’ai un autre pblm, si je doit comparer deux colonnes, excel compare avec la liste que j’ai donné-1, càd:

    =SI(ESTNA(RECHERCHEV(G2:G45;B1:B70713;;0));”manquant”;”present”) pour le premier;
    =SI(ESTNA(RECHERCHEV(G3:G46;B2:B70714;;0));”manquant”;”present”) pour le deuxieme.

    remarquer que G2 est devenu G3, ce qui fait que si l’elément recherché se trouve dans G2, la réponse est que l’elément n’est pas présent alors qu’en réalité il est.

    merci de vos commentaires.

  2. Vanessa

    Bonjour,
    J’ai le même souci dès qu’1 élément est manquant dans une colonne, toutes les valeurs suivantes sont en #N/A. Je pense que ma formule tient compte de l’ordre alphabétique, mais puisque la valeur manque cela décale les suivantes… Je ne suis pas claire mais avez-vous comprit et surtout que dois-je modifier ?
    MERCI !

    1. Frédéric LE GUEN

      L’ordre alphabétique n’a rien à voir avec la formule. Ce qui est probablement la cause ce sont des caractères invisibles (genre espace) qui font que les chaînes de caractères ne soient pas identiques.

  3. Juliette DL

    Bonjour Frédéric,
    j’ai appliqué la formule sauf qu’en résultat je n’ai que des N/A ..;
    où est l’erreur ?

    merci d ‘avance pour votre réponse

    1. Frédéric LE GUEN

      Bonjour,
      Avez-vous fait un test d’égalité pour savoir si le problème vient de votre formule ou de vos données ?

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *