Comparer deux colonnes dans Excel

Table des matières

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.

Vidéo tutorielle

Vous trouverez des informations complémentaires sur la fonction ESTNA sur le site de Microsoft.

DOCUMENT