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, une pincée de fonction ESTNA et un soupçon de 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 blanc 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 capables 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 nous 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;
Puis, il faut mettre le troisième paramètre à 1 (car nous n'avons sélectionné qu'une seule colonne en deuxième paramètre).
=RECHERCHEV(A1;$D$1:$D$4;1;
Enfin 0 (pour faire une recherche exacte).
=RECHERCHEV(A1;$D$1:$D$4;1;0)
En 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))
Etape 3 : Terminer avec la fonction SI
Laisser VRAI ou FAUX comme résultat n'est toujours pas très lisible, 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";"")
Changer la couleur des valeurs manquantes
Pour améliorer le rendu, nous allons reprendre le test créé pour trouver les écarts entre les colonnes et l'intégrer dans une mise en forme conditionnelle.
Dans notre exemple
- Commencez par sélectionner votre colonne de comparaison (ici la colonne A)
- Ensuite, ouvrez le menu Accueil>Mise en forme conditionnelle>Nouvelle règle..
- Puis, choisissez l'option Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
- Dans la zone de texte, collez la formule qui a permis de retourner VRAI ou FAUX
- Terminez par la mise en forme personnalisée en cliquant sur le bouton Format ....
Finalement, 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. 😊😉
Vidéo explicatives - Plusieurs techniques
Vous trouverez des informations complémentaires sur la fonction isNA sur le site de Microsoft.
26/10/2021 @ 15:37
excellente formule, mais il me semble qu'avec les dernières versions d'excel on peut obtenir le même résultat en utilisant :
mise en forme conditionnelle/règle de mise en forme des cellules/valeurs en double
26/10/2021 @ 17:19
Bonjour, alors j'ai vérifié mais le résultat est l'inverse du résultat attendu. Avec cette option, on trouve les valeurs communes et donc par opposition, les cellules non coloriées sont unique. Moi, dans mon exemple, j'explique comme colorier les cellules en écart. Mais merci pour avoir partager cette méthode que je ne connaissais pas.
29/10/2021 @ 09:52
En effet je n'ai pas précisé, mais si vous sélectionnez "uniques" au lieu de "en double" dans la dernière fenêtre, vous aurez le bon résultat.
29/10/2021 @ 11:55
Ha oui, sympa comme astuce !
15/10/2021 @ 11:05
Bonjour,
En 2021 la formule refuse le ;0 dans la formule et donne des résultats aléatoires une valeur présente dans les deux colonnes va tantôt donner un résultat manquant tantôt une case vide. Je ne sais pas si c'est un problème lié à des majs de excel mais c'est dommage la fonction semblait intéressante
19/10/2021 @ 12:46
Avec RECHERCHEV ? C'est impossible. La fonction n'a pas évoluée depuis l'origine d'Excel
23/08/2021 @ 10:32
Merci c'est parfait !
10/07/2021 @ 18:11
Bonjour Frédéric,
SOS,
J'ai des valeurs numériques en colonnes A et B,
Je souhaite pouvoir trouver dans la colonne B les valeurs de la colonne A mais au lieu d'avoir un résultat vrai/faux ou manquant je veux afficher en regard de chaque cellule de A les coordonnées de la cellule B où se trouve la correspondance et indiquer ABSENT s'il n'y a pas de correspondance.
Merci d'avancer pour votre aide
27/06/2021 @ 21:44
Bonjour Très intéressante la formule MERCI.
J'ai 2 tableaux de 2 colonnes représentant les note obtenus par des élèves durant les années différente dont j'aimerais comparer et trouver la note max en fonction de la position de chaque noms.
TAB ANNEE 1 (colonne A "Prénoms1" et B "Note1" ) TAB ANNEE 2 (colonne C "Prénoms2" et D "Note2" ). J'aimerais comparer les colonnes (Prénoms 1 & 2) et donner la valeurs de la note MAXIMAL dans une autre colonne.
Merci de votre générosité.
A B C D
ALEX 12 MAT 8
FRANK 14 FRANK 12
BEN 11 HENRI 11
HENRI 10 ALEX 14
MAT 9 BEN 13
A B C D
ALEX RO
22/03/2021 @ 16:04
bonjour j'aurais besoin d'une infos je suis tombe sur votre tuto mais je n'arrive pas a faire ce que je veux :
je herche a compare deux listes(uniquement des chiffres) colonne A et colonne B ,si les deux sont identiques marquer ok et si les deux on une differente marquer partiel . des idees ?
22/03/2021 @ 17:44
Tout simplement avec un test logique =A1=B1
22/03/2021 @ 19:13
j'ai essayer mais ca ne marche pas avec une liste ou je m'y prend mal
22/03/2021 @ 20:01
Si c'est une liste, alors c'est RECHERCHEV qu'il faut utiliser, comme c'est indiqué dans l'article
23/03/2021 @ 11:10
=SI(ESTNA(RECHERCHEV(C4:C6;J4:J6;1;0));"ok";"partiel") j'ai bien mes deux listes mais je n'ai que le retour "faux" que jai modifié en partiel
30/03/2021 @ 16:35
enfait ca marche si je cherche une valeur dans une liste . mais moi je veux comparer deux liste pour savoir si elles sont identique
30/03/2021 @ 16:48
=SI(ESTNA(RECHERCHEV(F52:F54;G52:G54;1;0));"ok";"partiel")
comme ceci .
15/03/2021 @ 17:35
J'utilisais = "" dans mes formules pour spécifier si une case etait vide ou non et je ne comprenais pas pourquoi ca ne marchait pas, maintenant j'utilise estNA et ca marche parfaitement, ce tuto m'a été d'une grande aide, encore merci !
03/07/2019 @ 09:53
Excellent tutoriel, très clair et très bien illustré.
Merci beaucoup !
03/07/2019 @ 10:01
Merci bcp
25/06/2019 @ 08:15
Super, merci. Depuis le temps que je galère pour comparer les changements
30/05/2019 @ 11:35
bravo et merci beaucoup. très pratique votre cours encore merci
18/02/2021 @ 14:48
Bravo pour ce tutoriel très clair qui m'a permis de répondre facilement à ma problématique !
16/05/2019 @ 10:25
TROP PROPRE, MAGNIFIQUE COURS, VOUS M'AVEZ SAUVEZ LA VIE, MERCI MERCI MERCI ET ENCORE MERCI
12/02/2019 @ 12:23
Bonjour,
La formule fonctionne à merveille, merci beaucoup.
Une question, voici la structure de mon fichier :
Noms cellule A
Donnée cellule B
"manquant" cellule C
Noms cellule D (Liste de comparaison)
J'aimerais ajouter une cellule dans la colonne D à chaque fois qu'il y a "manquant" en cellule C. Est-ce possible svp?
Car à l’instanté si je masque par un filtre mes données manquantes en C pour avoir celle présente, il me masque également les données en D de la même ligne.
Exemple :
A B C D
Ligne 1 : Mr Y Mr Y
Ligne 2 : Mme X Manquant Mr Z
Ligne 3 : Mr Z Mme K
Ligne 4 : Mme K
Merci de votre aide !
Aurore
23/01/2019 @ 14:19
Bonjour,
J'ai utilisé la formule RechercheV dans le cadre d'une analyse de base de donnée. Lorsque j'exécute la fonction, j'ai des N/A dans la colonne de réponse.
A noté que dans la plage de cellule dans laquelle, la formule doit aller chercher la valeur, il existe un même nom pour certaines valeurs:
Exemple : 90000006 AT
60000009 AT
Comment résoudre cette problème?
Je vous remercie pour votre aide.
29/11/2018 @ 20:52
bonjour,
je viens de construire une base de donnée avec Excel en VBA.
je dispose de plusieurs formulaires qui interroge la base, cependant je souhaite qu'ils apparaissent tous en sembles, afin exploiter la base sur tous les formulaires en même temps.
cordialement
07/12/2018 @ 09:59
Bonjour,
Désolé pour la réponse tardive mais je n'avais pas vu le message avant.
Comme ça, il est impossible de vous répondre. Il faudrait faire une étude de votre existant pour faire une évaluation du travail à fournir
Contactez-moi via le formulaire accessible depuis le menu pour me contacter en privé si vous voulez que je traite votre problème
Cordialement
24/10/2018 @ 12:48
bonjour , veillez m'expliquer le cas ou la taille de deux colonnes sont pas egaux et une partie l'une des colonne vide, ....pourrons nous appliquer cette formule de comparaison et que donne le test sur les cases vides?
16/09/2018 @ 12:20
mercii
01/08/2018 @ 18:01
Bonjour,
Merci pour toutes ces informations.
J'ai un besoin un peu différent :
Je dois vérifier que 2 cellules dans 2 lignes se suivant, comportent ou non les mêmes mots, mais dans le désordre et à ce moment là ce sont des doubles et je dois supprimer 1 des 2 lignes.
Par exemple :
Ligne 1 : cellule 1 : "VINTAGE POSTER REPRINT PARIS-ALMANACH"
Ligne 2 : cellule 1 : "PARIS-ALMANACH REPRINT VINTAGE POSTER "
Tous les mots sont communs aux 2 lignes ce sont donc des doubles.
Si quelqu'un a une solution, je le remercie par avance.
Cordialement
Roland
08/06/2018 @ 10:37
Bonjour,
Je n'arrive pas à faire apparaître le texte d'origine après avoir appliqué une mise en forme conditionnelle à la fonction ESTNA. J'ai VRAI ou FAUX avec VRAI en vert mais j'aimerais qu'apparaisse le texte (l'adresse email) à la place.
Merci d'avance!
17/04/2018 @ 19:00
bonjour moi j'ai une liste de recouvrement ou certain client on régler leur crédit d'autre non, je veux comparer de sorte a n'obtenir que ceux qui n'ont pas encore régler leur crédit
exemple:
client prestation montant régler reste à payer
cli1 rep 1500 0 1500
cli2 ent 2000 0 2000
cli1 rep 1500 1500 0
cli2 rep 500 0 2500
20/03/2018 @ 14:03
Bonjour,
Explications très claires qui m'ont beaucoup aidée.
C'était la solution que je cherchais.
Merci beaucoup
06/10/2017 @ 16:15
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.
08/08/2017 @ 16:20
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 !
11/08/2017 @ 08:17
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.
19/07/2017 @ 11:09
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
20/07/2017 @ 06:58
Bonjour,
Avez-vous fait un test d'égalité pour savoir si le problème vient de votre formule ou de vos données ?