Rechercher les valeurs manquantes
Il arrive que vous deviez comparer deux listes pour vérifier si une valeur de la liste A existe dans la liste B dans Excel. Par exemple, vous avez une liste de produits et vous souhaitez vérifier si les produits de votre liste figurent dans la liste de produits fournie par votre fournisseur. Pour accomplir cette tâche, nous avons répertorié ci-dessous trois méthodes, choisissez celle qui vous convient.
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 vérifier si tous les produits de votre liste existent dans la liste de votre fournisseur comme illustré dans la capture d'écran ci-dessus, vous pouvez d'abord utiliser la fonction EQUIV pour récupérer la position d'un produit de votre liste (valeur de la liste A) dans la liste du fournisseur (liste B). EQUIV renverra l'erreur #N/A lorsqu'un produit n'est pas trouvé. Ensuite, vous pouvez transmettre le résultat à ESTNA pour convertir les erreurs #N/A en VRAI, ce qui signifie que ces produits sont manquants. La fonction SI renverra ensuite le résultat attendu.
Syntaxe générique
=SI(ESTNA(EQUIV("valeur_recherche",plage_recherche,0)),"Manquant","Trouvé")
√ Remarque : Vous pouvez modifier "Manquant", "Trouvé" par toute autre valeur selon vos besoins.
- valeur_recherche : La valeur qu'EQUIV utilise pour récupérer sa position si elle existe dans plage_recherche ou erreur #N/A si non. Ici, cela fait référence aux produits dans votre liste.
- plage_recherche : La plage de cellules à comparer avec la valeur_recherche. Ici, cela fait référence à la liste des produits du fournisseur.
Pour vérifier 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ée pour 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éplacez ou copiez la formule vers d'autres cellules. Cependant, aucun signe dollar n'est ajouté à la valeur_recherche car vous souhaitez qu'elle soit dynamique. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour appliquer la formule aux cellules ci-dessous.
Explication de la formule
Ici, nous utilisons la formule ci-dessous comme exemple :
=SI(ESTNA(EQUIV(G8,$B$6:$B$10,0)),"Manquant","Trouvé")
- EQUIV(G8,$B$6:$B$10,0): Le type_correspondance 0 force la fonction EQUIV à retourner une valeur numérique qui indique la position de la première correspondance de 3004, la valeur dans la cellule G8, dans le tableau $B$6:$B$10. Cependant, dans ce cas, EQUIV ne peut pas trouver la valeur dans le tableau de recherche, donc il retournera l'erreur #N/A.
- ESTNA(EQUIV(G8,$B$6:$B$10,0)) = ESTNA(#N/A) : ESTNA sert à déterminer si une valeur est une erreur « #N/A » ou non. Si oui, la fonction retournera VRAI ; si la valeur est autre chose qu'une erreur « #N/A », elle retournera FAUX. Donc, cette formule ESTNA retournera VRAI.
- SI(ESTNA(EQUIV(G8,$B$6:$B$10,0)),"Manquant","Trouvé") = SI(VRAI,"Manquant","Trouvé") : La fonction SI retournera Manquant si la comparaison effectuée par ESTNA et EQUIV est VRAIE, sinon retournera Trouvé. Donc, la formule retournera Manquant.
Rechercher les valeurs manquantes avec RECHERCHEV, ESTNA et SI
Pour vérifier si tous les produits de votre liste existent dans la liste de votre fournisseur, vous pouvez remplacer la fonction EQUIV ci-dessus par RECHERCHEV, car elle fonctionne de la même manière qu'EQUIV, c'est-à-dire qu'elle retournera l'erreur #N/A si la valeur n'existe pas dans une autre liste, ou nous dirons qu'elle est manquante.
Syntaxe générique
=SI(ESTNA(RECHERCHEV("valeur_recherche",plage_recherche,1,FAUX)),"Manquant","Trouvé")
√ Remarque : Vous pouvez modifier "Manquant", "Trouvé" par toute autre valeur selon vos besoins.
- valeur_recherche : La valeur que RECHERCHEV utilise pour récupérer sa position si elle existe dans plage_recherche ou erreur #N/A si non. Ici, cela fait référence aux produits dans votre liste.
- plage_recherche : La plage de cellules à comparer avec la valeur_recherche. Ici, cela fait référence à la liste des produits du fournisseur.
Pour vérifier 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ée pour 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_recherche dans la formule ne changera pas lorsque vous déplacez ou copiez la formule vers d'autres cellules. Cependant, aucun signe dollar n'est ajouté à la valeur_recherche car vous souhaitez qu'elle soit dynamique. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour appliquer la formule aux cellules ci-dessous.
Explication de la formule
Ici, nous utilisons la formule ci-dessous comme exemple :
=SI(ESTNA(RECHERCHEV(G8,$B$6:$B$10,1,FAUX)),"Manquant","Trouvé")
- RECHERCHEV(G8,$B$6:$B$10,1,FAUX) : L'argument recherche_proche FAUX force la fonction RECHERCHEV à rechercher et retourner la valeur qui correspond exactement à 3004, la valeur dans la cellule G8. Si la valeur_recherche 3004 existe dans la 1ère colonne du tableau $B$6:$B$10, la fonction RECHERCHEV retournera cette valeur ; sinon, elle retournera la valeur d'erreur #N/A. Ici, 3004 n'existe pas dans le tableau, donc le résultat serait #N/A.
- ESTNA(RECHERCHEV(G8,$B$6:$B$10,1,FAUX)) = ESTNA(#N/A) : ESTNA sert à déterminer si une valeur est une erreur « #N/A » ou non. Si oui, la fonction retournera VRAI ; si la valeur est autre chose qu'une erreur « #N/A », elle retournera FAUX. Donc, cette formule ESTNA retournera VRAI.
- SI(ESTNA(RECHERCHEV(G8,$B$6:$B$10,1,FAUX)),"Manquant","Trouvé") = SI(VRAI,"Manquant","Trouvé") : La fonction SI retournera Manquant si la comparaison effectuée par ESTNA et RECHERCHEV est VRAIE, sinon retournera Trouvé. Donc, la formule retournera Manquant.
Rechercher les valeurs manquantes avec NB.SI et SI
Pour vérifier si tous les produits de votre liste existent dans la liste de votre fournisseur, vous pouvez utiliser une formule plus simple avec les fonctions NB.SI et SI. La formule tire parti du fait qu'Excel évalue tout nombre sauf zéro (0) comme VRAI. Donc, si une valeur existe dans une autre liste, la fonction NB.SI retournera le nombre de ses occurrences dans cette liste, puis SI prendra ce nombre comme un VRAI ; si la valeur n'existe pas dans la liste, la fonction NB.SI retournera 0, et SI le prendra comme un FAUX.
Syntaxe générique
=SI(NB.SI("plage_recherche",valeur_recherche),"Trouvé","Manquant")
√ Remarque : Vous pouvez modifier "Trouvé", "Manquant" par toute autre valeur selon vos besoins.
- plage_recherche : La plage de cellules à comparer avec la valeur_recherche. Ici, cela fait référence à la liste des produits du fournisseur.
- valeur_recherche : La valeur que NB.SI utilise pour retourner le nombre de ses occurrences dans plage_recherche. Ici, cela fait référence aux produits dans votre liste.
Pour vérifier 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ée pour 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_recherche dans la formule ne changera pas lorsque vous déplacez ou copiez la formule vers d'autres cellules. Cependant, aucun signe dollar n'est ajouté à la valeur_recherche car vous souhaitez qu'elle soit dynamique. Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour appliquer la formule aux cellules ci-dessous.
Explication de la formule
Ici, nous utilisons la formule ci-dessous comme exemple :
=SI(NB.SI($B$6:$B$10,G8),"Trouvé","Manquant")
- NB.SI($B$6:$B$10,G8) : La fonction NB.SI compte combien de fois 3004, la valeur dans la cellule G8, apparaît dans le tableau $B$6:$B$10. Apparemment, 3004 n'existe pas dans le tableau, donc le résultat serait 0.
- SI(NB.SI($B$6:$B$10,G8),"Trouvé","Manquant") = SI(0,"Trouvé","Manquant") : La fonction SI évaluera 0 comme FAUX. Donc, la formule retournera Manquant, la valeur à retourner lorsque le premier argument est évalué comme FAUX.
Fonctions connexes
La fonction SI est l'une des fonctions les plus simples et les plus utiles dans un classeur Excel. Elle effectue un test logique simple qui dépend du résultat de la comparaison, et elle retourne une valeur si le résultat est VRAI, ou une autre valeur si le résultat est FAUX.
La fonction EQUIV d'Excel recherche une valeur spécifique dans une plage de cellules et retourne la position relative de la valeur.
La fonction RECHERCHEV d'Excel recherche une valeur en faisant correspondre sur la première colonne d'une table et retourne la valeur correspondante d'une certaine colonne dans la même ligne.
La fonction NB.SI est une fonction statistique dans Excel qui est utilisée pour compter le nombre de cellules qui répondent à un critère. Elle prend en charge les opérateurs logiques (<>, =, >, et <), ainsi que les caractères génériques (? et *) pour la correspondance partielle.
Formules connexes
Rechercher une valeur contenant un texte spécifique avec des caractères génériques
Pour trouver la première correspondance contenant une chaîne de texte spécifique dans une plage dans Excel, vous pouvez utiliser une formule INDEX et EQUIV avec des caractères génériques - l'astérisque (*) et le point d'interrogation (?).
Correspondance partielle avec RECHERCHEV
Il arrive que vous ayez besoin qu'Excel récupère des données basées sur des informations partielles. Pour résoudre le problème, vous pouvez utiliser une formule RECHERCHEV avec des caractères génériques - l'astérisque (*) et le point d'interrogation (?).
Correspondance approximative avec INDEX et EQUIV
Il arrive que nous devions trouver des correspondances approximatives dans Excel pour évaluer la performance des employés, noter les scores des étudiants, calculer les frais de port en fonction du poids, etc. Dans ce tutoriel, nous parlerons de la façon d'utiliser les fonctions INDEX et EQUIV pour récupérer les résultats dont nous avons besoin.
Rechercher la valeur correspondante la plus proche avec plusieurs critères
Dans certains cas, vous devrez peut-être rechercher la valeur correspondante la plus proche ou approximative basée sur plus d'un critère. Avec la combinaison des fonctions INDEX, EQUIV et SI, vous pouvez rapidement y arriver dans Excel.
Les meilleurs outils de productivité pour Office
Kutools pour Excel - Vous aide à vous démarquer de la foule
Kutools pour Excel compte plus de 300 fonctionnalités, garantissant que ce dont vous avez besoin n'est qu'à un clic...
Office Tab - Active la lecture et l'édition par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts !
- Réduit des centaines de clics de souris pour vous chaque jour, dites adieu à la main de la souris.
- Augmente votre productivité de 50 % lors de la consultation et de l'édition de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Edge et Firefox.