Méthode pour comparer les colonnes dans Excel
Ici, nous allons expliquer une méthode qui est applicable à toutes les versions d’Excel en utilisant les fonctions RECHERCHEV, ESTNA et SI. Si vous utilisez Excel 365 ou Excel Online, une autre méthode utilisant les fonctions NB.SI et FILTRE peut être utilisée.
Démo en direct dans un classeur
Dans le classeur ci-dessous, amusez-vous à modifier les valeurs de la première colonne pour voir les valeurs absentes en rouge et les valeurs présentes en blanc.
Comparer 2 colonnes
Comparer deux colonnes est très facile avec Excel. Cela repose sur l’utilisation de la fonction RECHERCHEV pour trouver, ou non, une valeur dans une colonne.
Pour de nombreux utilisateurs d’Excel, quand la fonction RECHERCHEV retourne #N/A, cela est considéré comme une erreur. Cependant, ce n’est pas le cas ! #N/A signifie “je n’ai pas trouvé l’élément recherché”. Dans le cas d’une comparaison entre deux colonnes, c’est la valeur #N/A qui nous intéresse. Ainsi, nous serons capables d’identifier les éléments manquants entre les deux colonnes.
Étape 1 : Utiliser 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. Ensuite, nous allons vérifier si cette valeur est présente dans la colonne $D$1:$D$4 en utilisant les références absolues. Mettez le troisième paramètre à 1 (car nous avons sélectionné une seule colonne en deuxième paramètre) et enfin, mettez le quatrième paramètre à 0 (pour une recherche exacte).
=RECHERCHEV(A1;$D$1:$D$4;1;0)
En recopiant cette formule, nous obtenons le résultat suivant (la valeur manquante est la lettre C). La valeur manquante est immédiatement identifiée par l’indication #N/A, mais ce n’est pas esthétique de laisser cette indication dans votre classeur.
Étape 2 : Utiliser la fonction ESTNA pour faire un test
Pour personnaliser le résultat de la comparaison, nous allons utiliser la fonction ESTNA. Cette fonction nous retourne deux valeurs : VRAI si la fonction RECHERCHEV retourne #N/A et FAUX si la recherche a abouti à un résultat.
En écrivant notre formule RECHERCHEV à l’intérieur de la fonction ESTNA, nous affichons maintenant les résultats VRAI ou FAUX dans la colonne selon que la recherche a abouti ou non.
=ESTNA(RECHERCHEV(A1;$D$1:$D$4;1;0))
Étape 3 : Utiliser la fonction SI pour personnaliser le résultat
Laisser les résultats VRAI ou FAUX n’est pas très lisible, surtout si vous devez partager ce document avec d’autres collaborateurs. C’est pourquoi nous allons intégrer ce test dans une fonction SI pour personnaliser le résultat et 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";"")
Autre méthode avec la fonction NB.SI et FILTRE
Si vous utilisez Excel 365 ou Excel Online, vous pouvez utiliser une formule qui ne renvoie que les écarts.
Changer la couleur des valeurs manquantes
Pour améliorer la présentation, nous allons utiliser le test créé pour trouver les écarts entre les colonnes et l’appliquer à une mise en forme conditionnelle.
Dans notre exemple :
- Sélectionnez la colonne de comparaison (ici, la colonne A)
- Ouvrez le menu Accueil > Mise en forme conditionnelle > Nouvelle règle.
- Choisissez l’option “Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué”.
- Collez la formule qui a permis de retourner VRAI ou FAUX dans la zone de texte.
- Personnalisez la mise en forme en cliquant sur le bouton Format.
Finalement, vous obtiendrez des cellules avec un arrière-plan rouge lorsque la valeur n’est pas trouvée dans la deuxième colonne.
Articles liés
- Comprendre la fonction RECHERCHEV avec un quiz
- Pourquoi la fonction RECHERCHEV retourne #N/A ?
- Fonction FILTRE
- Recherche approchante, calcul entre tranches
- Exercice RECHERCHEV, choisir la bonne réponse
- Exercice RECHERCHEV, remplir les trous
Vidéo tutorielle
Vous trouverez des informations complémentaires sur la fonction ESTNA sur le site de Microsoft.
