Formule Excel : Vérifier si une cellule contient tous les éléments d’une liste

Supposons qu’Excel contienne une liste de valeurs dans la colonne E, et que vous souhaitiez vérifier si les cellules de la colonne B incluent bien toutes ces valeurs, en renvoyant VRAI ou FAUX comme le montre la capture d’écran ci-dessous. Ce tutoriel vous propose une formule efficace pour accomplir cette tâche.

Formule générique :

Arguments

Valeur de retour :

Cette formule renvoie une valeur logique : FAUX signifie que la cellule ne contient pas tous les éléments, tandis que VRAI indique qu’elle les contient tous.

Fonctionnement de cette formule

Par exemple, pour vérifier si la colonne B contient toutes les valeurs de la plage E3:E5, utilisez la formule suivante.

Appuyez sur la touche Entrée, puis faites glisser la poignée de recopie sur les cellules à vérifier. La valeur FAUX indique que la cellule ne contient pas toutes les valeurs de la plage E3:E5, tandis que VRAI signifie qu’elle les inclut toutes.

Explication

Fonction RECHERCHE : cette fonction renvoie la position du premier caractère d’une chaîne de texte à l’intérieur d’une autre. Si le texte recherché est trouvé, elle renvoie sa position ; sinon, elle génère une erreur #VALEUR!. Par exemple, la formule SEARCH($E$3:$E$5,B4) recherche chaque valeur de la plage E3:E5 dans la cellule B4 et renvoie la position de chaque occurrence trouvée. Le résultat est un tableau tel que : {1;7;12}

Fonction ESTNUM : cette fonction vérifie si une valeur est un nombre et renvoie VRAI ou FAUX. Ici, ISNUMBER(SEARCH($E$3:$E$5,B4)) renvoie un tableau tel que {true;true;true}, car la fonction RECHERCHE a trouvé trois nombres.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) convertit les valeurs VRAI en 1 et les valeurs FAUX en 0, transformant ainsi le tableau en {1;1;1}.

Fonction SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) : elle permet de multiplier entre elles des plages ou des tableaux, puis d’additionner les produits obtenus. Ainsi, la formule SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) renvoie 1 + 1 + 1 = 3.

La fonction NBVAL renvoie le nombre de cellules non vides. Ainsi, COUNTA($E$3:$E$5) renvoie 3, ce qui signifie que le résultat de SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) est égal à 3 ; la formule complète renvoie alors VRAI, sinon FAUX.

Remarques :

La formule =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) ne permet pas de réaliser une vérification exacte. Consultez la capture d’écran ci-dessous :

Fichier exemple

Cliquez pour télécharger le fichier exemple

Formules associées

• Compter les cellules égales à
  Grâce à la fonction NB.SI, vous pouvez compter les cellules égales à une valeur donnée ou celles qui ne la contiennent pas.
• Compter les cellules égales à x ou y
  Vous souhaitez parfois compter le nombre de cellules qui répondent à l’un ou l’autre de deux critères. Dans ce cas, utilisez la fonction NB.SI.
• Compter les cellules égales à x et y
  Cet article présente la formule permettant de compter les cellules qui remplissent simultanément deux critères.
• Compter les cellules différentes de
  Découvrez comment utiliser la fonction NB.SI pour compter facilement le nombre de cellules qui diffèrent d’une valeur spécifique !