Identifier dans une plage les cellules contenant une chaîne

Question

J'ai besoin de détecter dans une plage la présence d'une chaîne particulière (voir fichier joint). Je m'explique : 1/ la plage contient une liste de noms de personnes. 2/ je cherche si un nom de famille donné est présent dans la plage 3/ si oui, j'ai besoin de récupérer la position dans la plage de la première cellule qui contient ce nom Et : je ne sais pas quoi faire en cas de doublon... J'ai essayé les fonctions CHERCHE, INDEX et RECHERCHEV, mais sans succès... Help ! Merci...

MFerrand · Answer

Une solution : =INDEX($B:$B;PETITE.VALEUR(SI(ESTERREUR(CHERCHE($D$3;$B$3:$B$10));60000;LIGNE($B$3:$B$10));LIGNE(1:1))) La formule est matricielle (valider par Ctrl+Maj+Entrée). Tirer sur quelques lignes pour afficher toutes les occurrences du nom cherché. Lorsque plus de nom, affiche 0 (à inhiber par format de cellule). &#91;Ou utiliser SIERREUR, sous 2010 (ne fonctionnnerait pas sous 2003)&#93; Cordialement...

MFerrand · Answer

Bon ! Tu bénéficies du fait que j'étais devant un film qu je trouvais quelque peu soporifique. Je me suis donc livré pour éviter de m'endormir dessus à une analyse détaillé de formule dont j'espère qu'elle te permettra d'y voir plus clair.

Exercice d'analyse de formule :

Elle utilise la fonction INDEX dont la syntaxe habituelle est :

INDEX(PlageRecherche;IndexLigne;IndexColonne)

Cependant si PlageRecherche est réduite à une seule colonne ou une seule ligne, on omet l'IndexColonne ou l'IndexLigne pour ne conserver que le seul Index utile. C'est le cas ici, la formule a donc la forme générale :

=INDEX(PlageRecherche;Index)

PlageRecherche est constituée par la colonne B, entière.Le résultat que l'on veut faire renvoyer par la formule est bien situé en B, mais dans une plage plus limitée. On verra par la suite pourquoi on élargit la recherche à la colonne entière, notons pour l'instant qu'on recherche dans la colonne qui contient éventuellement une ou plusieurs des valeurs cherchées.

Index est représentée par une expression qui doit donc renvoyer une valeur d'index pour que la recherche aboutisse. Comme on l'applique à une colonne entière, on peut déjà déduire que cet index se confondra avec le numéro de ligne où une valeur cherchée se trouve.

L'expression utilisée pour renvoyer cet index est construite sur la fonction PETITE.VALEUR dont la syntaxe est :

PETITE.VALEUR(MatriceValeurs;Rang)

MatriceValeurs est constituée par une expression aussi, qu'on va devoir décortiquer. Rang, de même mais plus simple : LIGNE(1:1). Expression qui renvoie tout bêtement 1 pour définir le rang recherché avec la fonction PETITE.VALEUR, mais la ligne étant définie en références relatives, lors de la recopie de la formule sur les lignes suivantes, l'expression s'incrémentera pour renvoyer successivement 2, 3, etc.

MatriceValeurs dans laquelle on cherche la plus petite valeur numérique est constituée au moyen d'une expression conditionnelle, qui devra nécessairement renvoyer un ensemble de valeurs pour constituer une matrice. Elle utilise SI, dont la syntaxe est :

SI(Condition;ValeurSiVrai;ValeurSiFaux)

On peut voir que dans cette expression, dans la formule, ValeurSiVrai est représentée par le nombre 60000. Donc dans les cas où la condition est vérifiée, le nombre 60000 prendra place dans la matrice de valeurs dans laquelle on va chercher la plus petite.

ValeurSiFaux, dans la formule est représentée par l'expression LIGNE($B$3:$B$10), renvoyant donc un numéro de ligne de la plage de valeur qui nous intéresse si la condition n'est pas vérifiée. On peut donc d'ores et déjà prévoir que la condition va concerner la même plage, pour renvoyer une matrice de valeurs numériques en nombre égal aux valeurs de cette plage : le numéro de ligne lorsque la condition n'est pas vérifiée, 60000 (soit une valeur nettement plus grande) lorsque la condition est vérifiée.

On peut noter ici que les fonctions utilisées dans l'expression destinée à renvoyer une matrice de valeurs, renvoient dans leur utilisation classique une seule valeur, mais supportent un traitement matriciel. C'est ainsi qu'on peut faire renvoyer plusieurs valeurs à l'expression en validant la formule comme matricielle.

Reste à voir la Condition. Elle est basée sur la fonction CHERCHE dont la syntaxe est :

CHERCHE(ChaîneCherchée;ChaîneOùOnRecherche)

Si ChaîneCherchée est trouvée, la fonction renvoie sa position dans ChaîneOùOnRecherche (rang du premier caractère de ChaîneCherchée), sinon elle renvoie une valeur d'erreur (qu'il convient de récupérer si on veut obtenir un résultat...).

Ce qui nous intéresse c'est de savoir si le nom consigné en D3 est ou n'est pas dans l'une ou l'autre (ou plusieurs) des valeurs de la plage B3:B10.

On va donc tester si l'expression CHERCHE($D$3;$B$3:$B$10) renvoie ou non une erreur avec l'expression :

ESTERREUR(CHERCHE($D$3;$B$3:$B$10))

constituant la Condition de la fonction SI.

Le résultat global renvoyé par cette fonction sera, pour chaque valeur de la plage B3:B10, si le nom cherché n'y est pas (condition vérifiée) : la valeur 60000, et si le nom cherché s'y trouve (condition non vérifiée) : le numéro de la ligne concernée de la plage.

Une fois la formule validée matriciellement (en F3), si le nom cherché (en D3) figure dans la plage, elle renverra le nom entier par INDEX(ColonneB;NuméroLigne), NuméroLigne correspondant à un numéro de ligne de la plage listant les noms entiers.

Lorsqu'on tire la formule pour la recopier en F4, si le nom existe une 2e fois, la 2e petite valeur de la matrice qu'on a constitué correspondra à un numéro de ligne de la plage et un 2e nom entier sera renvoyé.

Ainsi de suite lorsqu'on tire la formule en F5... Si le nom ne figure pas ou plus, on tombera sur 60000 comme valeur d'Index, ce qui renverra la valeur de la cellule B60000, laquelle étant (en principe) vide provoquera l'affichage de la valeur 0.

NB- On a retenu 60000 pour rester dans la limite du nombre de lignes d'Excel 2003 (et antérieurs) et pointer sur une cellule existante.

Sur les versions postérieures, on peut remplacer 60000 par 9^9 (valeur d'usage fréquent en pareil cas, qui dépasse largement la limite du nombre de lignes d'Excel 2007 et postérieurs et provoque donc le renvoie de l'erreur #REF!, la cellule n'existant pas). On récupère alors cette erreur par la foncton SIERREUR qui n'allonge que peu la formule.

Mais cette fonction n'existant pas sous 2003 (et antérieurs), il est plus économique de renvoyer 0 et d'inhiber l'affichage des 0 par le format personnalisé de cellule : Standard;;

Voilà. Cordialement.