Rechercher les valeurs manquantes

Il vous arrive parfois de devoir comparer deux listes sous Excel pour vérifier si une valeur de la liste A figure également dans la liste B. Par exemple, vous avez une liste de produits et souhaitez savoir si ces produits sont présents dans la liste fournie par votre fournisseur. Pour accomplir cette tâche, découvrez ci-dessous trois méthodes efficaces : choisissez celle qui vous convient le mieux.

Rechercher les valeurs manquantes avec EQUIV, ESTNA et SI
Rechercher les valeurs manquantes avec RECHERCHEV, ESTNA et SI
Rechercher les valeurs manquantes avec NB.SI et SI

Rechercher les valeurs manquantes avec EQUIV, ESTNA et SI

Pour déterminer si tous les produits de votre liste existent dans la liste de votre fournisseur, comme illustré dans la capture d’écran ci-dessus, commencez par utiliser la fonction EQUIV pour trouver la position d’un produit de votre liste (valeur de la liste A) dans la liste du fournisseur (liste B). Lorsqu’un produit est introuvable, la fonction EQUIV renvoie l’erreur #N/A. Transmettez ensuite ce résultat à la fonction ESTNA afin de transformer ces erreurs #N/A en VRAI — ce qui indique précisément que ces produits sont manquants. Enfin, la fonction SI vous permet d’obtenir le résultat souhaité.

Syntaxe générique

=IF(ISNA(MATCH()))«lookup_value»,lookup_range,0)),«Missing»,«Found»)

√ Remarque : vous pouvez remplacer « Manquant » et « Trouvé » par les valeurs de votre choix.

Pour déterminer si tous les produits de votre liste existent dans la liste de votre fournisseur, veuillez copier ou saisir la formule ci-dessous dans la cellule H6, puis appuyer sur Entréepour obtenir le résultat :

=SI(ESTNA(EQUIV()))30002;$B$6:$B$10;0));«Manquant»;«Trouvé»)

Ou utilisez Une référence de cellule pour rendre la formule dynamique :

=SI(ESTNA(EQUIV()))G6;$B$6:$B$10;0));«Manquant»;«Trouvé»)

√ Remarque : Les signes dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que la plage_recherche dans la formule ne changera pas lorsque vous déplacerez ou copierez celle-ci vers d’autres cellules. En revanche, aucun signe dollar n’est ajouté à la valeur_recherchée, car vous souhaitez qu’elle reste dynamique. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour l’appliquer aux cellules situées en dessous.

Explication de la formule

Nous utilisons ici la formule ci-dessous comme exemple :

=IF()ISNA()MATCH(G8,$B$6:$B$10,0)),«Missing»,«Found»)

• EQUIV(G8;$B$6:$B$10;0): Le type_correspondance 0 oblige la fonction EQUIV à renvoyer une valeur numérique indiquant la position de la première correspondance exacte de 3004 — la valeur située dans la cellule G8 — dans le tableau $B$6:$B$10. Cependant, comme la fonction EQUIV n’a pas trouvé cette valeur dans le tableau de recherche, elle renvoie l’erreur #N/A.
• ESTNA()EQUIV(G8;$B$6:$B$10,0))=ESTNA()#N/A):La fonction ESTNA permet de vérifier si une valeur correspond à l’erreur « #N/A ». Elle renvoie VRAI dans ce cas, et FAUX pour toute autre valeur. Ainsi, cette formule ESTNA renverra VRAI.
• SI()ESTNA()EQUIV(G8;$B$6:$B$10;0));«Manquant»;«Trouvé») = SI(VRAI;«Manquant»;«Trouvé») :La fonction SI renvoie « Manquant » lorsque la comparaison effectuée par ESTNA et EQUIV donne VRAI ; dans le cas contraire, elle renvoie « Trouvé ». Ainsi, la formule affichera Manquant.

Rechercher les valeurs manquantes avec RECHERCHEV, ESTNA et SI

Pour vérifier si tous les produits de votre liste figurent bien dans celle de votre fournisseur, remplacez simplement la fonction EQUIV ci-dessus par RECHERCHEV : cette dernière fonctionne de manière similaire en renvoyant une erreur #N/A dès qu’une valeur est absente de l’autre liste — c’est-à-dire lorsqu’elle manque.

Syntaxe générique

=IF(ISNA(VLOOKUP()))«lookup_value»,lookup_range,1,FALSE)),«Missing»,«Found»)

√ Remarque : vous pouvez remplacer « Manquant » et « Trouvé » par les valeurs de votre choix.

Pour vérifier si tous les produits de votre liste figurent dans la liste de votre fournisseur, veuillez copier ou saisir la formule ci-dessous dans la cellule H6, puis appuyez sur Entréepour obtenir le résultat :

=SI(ESTNA(RECHERCHEV()))30002;$B$6:$B$10;1;FAUX));«Manquant»;«Trouvé»)

Ou utilisez Une référence de cellule pour rendre la formule dynamique :

=SI(ESTNA(RECHERCHEV()))G6;$B$6:$B$10;1;FAUX));«Manquant»;«Trouvé»)

√ Remarque : Les signes dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que la plage_recherchedans la formule ne changera pas lorsque vous déplacerez ou copierez la formule vers d’autres cellules. En revanche, aucun signe dollar n’est ajouté à la valeur_recherchéecar vous souhaitez qu’elle soit dynamique. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour l’appliquer aux cellules situées en dessous.

Explication de la formule

Nous utilisons ici la formule ci-dessous comme exemple :

=IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),«Missing»,«Found»)

• RECHERCHEV(G8;$B$6:$B$10;1;FAUX): L’argument plage_recherche avec la valeur FAUX oblige la fonction RECHERCHEV à rechercher et à renvoyer uniquement la valeur correspondant exactement à 3004, soit la valeur située dans la cellule G8. Si cette valeur 3004 est présente dans la 1re colonne du tableau $B$6:$B$10, la fonction RECHERCHEV la renvoie ; sinon, elle affiche l’erreur #N/A. Ici, comme 3004 n’existe pas dans le tableau, le résultat est donc #N/A.
• ESTNA()RECHERCHEV(G8;$B$6:$B$10,1;FAUX))=ESTNA()#N/A):La fonction ESTNA permet de vérifier si une valeur correspond à l’erreur « #N/A ». Si c’est le cas, elle renvoie VRAI ; sinon, elle renvoie FAUX. Ainsi, cette formule ESTNA renverra VRAI.
• SI()ESTNA()RECHERCHEV(G8;$B$6:$B$10;1;FAUX));«Manquant»;«Trouvé») = SI(VRAI;«Manquant»;«Trouvé») :La fonction SI renvoie « Manquant » si le test effectué par ESTNA et RECHERCHEV donne VRAI, sinon elle renvoie « Trouvé ». Ainsi, la formule affichera Manquant.

Rechercher les valeurs manquantes avec NB.SI et SI

Pour vérifier si tous les produits de votre liste figurent dans celle de votre fournisseur, utilisez une formule simple combinant NB.SI et SI. Elle repose sur le fait qu’Excel interprète tout nombre différent de zéro comme VRAI : si une valeur est présente dans l’autre liste, NB.SI renvoie son nombre d’occurrences, que SI considère alors comme VRAI ; si elle est absente, NB.SI renvoie 0, interprété par SI comme FAUX.

Syntaxe générique

=IF(COUNTIF())«lookup_range»,lookup_value),«Found»,«Missing»)

√ Remarque : Vous pouvez remplacer « Trouvé » et « Manquant » par les valeurs de votre choix.

Pour vérifier si tous les produits de votre liste figurent dans la liste de votre fournisseur, veuillez copier ou saisir la formule ci-dessous dans la cellule H6, puis appuyez sur Entréepour obtenir le résultat :

=SI(NB.SI())$B$6:$B$10;30002);«Trouvé»;«Manquant»)

Ou utilisez Une référence de cellule pour rendre la formule dynamique :

=SI(NB.SI())$B$6:$B$10;G6);«Trouvé»;«Manquant»)

√ Remarque : Les signes dollar ($) ci-dessus indiquent des références absolues, ce qui signifie que la plage_recherchedans la formule ne changera pas lorsque vous déplacerez ou copierez la formule vers d’autres cellules. En revanche, aucun signe dollar n’est ajouté à la valeur_recherchéecar vous souhaitez qu’elle soit dynamique. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour l’appliquer aux cellules situées en dessous.

Explication de la formule

Nous utilisons ici la formule ci-dessous comme exemple :

=IF()COUNTIF($B$6:$B$10,G8),«Found»,«Missing»)

• NB.SI($B$6:$B$10;G8):La fonction NB.SI dénombre combien de fois la valeur 3004, située dans la cellule G8, apparaît dans la plage $B$6:$B$10. Comme 3004 n’est manifestement pas présent dans cette plage, le résultat sera donc 0.
• SI()NB.SI($B$6:$B$10;G8);«Trouvé»;«Manquant») = SI(0;«Trouvé»;«Manquant») :La fonction SI considère la valeur 0 comme FAUX. La formule renvoie donc Manquant, c’est-à-dire la valeur associée au cas où le premier argument est évalué à FAUX.

Fonctions associées

Formules associées