Problème référence formule SI(NB.SI

Bonjour à tous,

Je suis un novice sur excel et ses nombreuses formules.

Cela fait plusieurs jours que je travail sur un document.

L'objectif est d'attribuer à un objet possédant certaines caractéristiques (H314, H217 ou 598 par exemple) une classe (0, 1, 2, 3, 4 ou 5) dépendant directement des caractéristiques.

Sur une première feuille, je dispose de la liste de mes produits auxquels sont attribués les caractéristiques et sur une seconde feuille un tableau (de référence) donne les classes en fonction de ces mêmes caractéristiques.

Afin de déterminer la classe d'un produit, 6 colonnes ont été ajoutées à la liste de mes produits (1 pour la classe 0, 1 pour la classe 1 etc). Dans ses colonnes, j'ai créer une fonction =SI(NB.SI qui devrait remplir la case correspondant à la classe du produit.

Exemple, si le produit A possède la caractéristique H314 (assimilée à la classe 4 dans le tableau de référence), la case "classe 4" doit afficher "4". Au final pour un produit, certaines cases de classes doivent être remplies. L'objectif, à l'aide d'une fonction "MAX" est de ne garder que la valeur maximale, si plusieurs classes ont été attribuées.

Les formules utilisées semblent cohérentes. Cependant aucune "case de classe" dans mon tableau n'est remplie...

Par exemple dans la case D3, la formule est la suivante : =SI(NB.SI(B2;"*Feuil5!C8:C32*");"4";"")

D'après le tableau de référence, le résultat devrait m'indiquer "4" puisque la cellule B3 contient "H314"

Lorsque je modifie la formule par =SI(NB.SI(B2;"*H314*");"4";"") j'obtient bien "4".

Je pensais enfin avoir réussi à faire ce que je voulais... en fait non. Je suis perdu !

Est-il possible d'utiliser la formule avec une sélection comme je souhaite le faire (il me semblais pourtant bien que oui... je croyais déjà avoir fait le test) ?

Je m'excuse d'avance pour mon explication qui je pense n'est pas si claire... Cependant le fichier joint devrait éclaircir la situation.

Je vous remercie par avance de votre indispensable et précieuse aide !

28aide-forum.xlsx (11.50 Ko)

et avec "rechercheV"

Bonsoir,

Pour réussir ce que tu souhaite, je pense que tu doit n' avoir qu'une valeur par cellule, la tu a 3 references dans une seule cellule!!!

sy_prob a écrit :

Par exemple dans la case D3, la formule est la suivante : =SI(NB.SI(B2;"*Feuil5!C8:C32*");"4";"")

D'après le tableau de référence, le résultat devrait m'indiquer "4" puisque la cellule B3 contient "H314"

Lorsque je modifie la formule par =SI(NB.SI(B2;"*H314*");"4";"") j'obtient bien "4".

donc ta formule =SI(NB.SI(B2;"*Feuil5!C8:C32*");"4";"") n'est pas logique !

remplace par :

=SI(NB.SI(B2;"*" & Feuil5!C8:C32 & "*");"4";"")

en validant en matriciel par Ctrl + Maj + Entrée

Tu ne peux pas associer un caractère comme * avec un référence de cellule, ce n'est pas une syntaxe correcte ! Et comme tes références de cellule sont multiples, il faut passer en matriciel !

Merci à tous pour vos réponses !

Je pense ne pense pas que la fonction rechercheV soit appropriée car en effet il en faudrait pas que j'ai plusieurs carcatéristiques dans une seule et même cases (avec du texte en plus...). Je dis ça, je ne dis rien, je viens juste de découvrir cette fonction qui est aussi nouvelle pour moi (oui oui je suis vraiment un débutant !)

L'idée des matrices semble être ce que je recherche !

Cependant ça ne semble pas marcher... Je m'excuse, pour la formule que j'avais indiqué dans mon premier message : la formule n'était pas le bonne (j'avais mis B2 au lieu de B3...). Idem pour la plage de données (il faut mettre B8 au lieu de C8)

Toujours dans la cellule D3, j'ai donc essayé les matrices : =SI(NB.SI(B3;"*" & Feuil5!B8:B32 & "*");"4";"")

Je précise que j'ai appuyé sur CTRL + SHIFT + ENTRE (des { } se sont mises de chaque côté de la formule) pour la matrice. Cependant toujours rien.

Je vous remet le fichier en question où j'ai tenté de modifier la cellule D3.

Une nouvelle fois merci par avance

Je précise que j'ai appuyé sur CTRL + SHIFT + ENTRE (des { } se sont mises de chaque côté de la formule) pour la matrice. Cependant toujours rien.

Je n'ai pas cherché à comprendre ce que tu voulais faire, j'ai juste corriger l'incohérence de ta formule =SI(NB.SI(B2;"*Feuil5!C8:C32*");"4";"") , correction que j'ai appliquée et qui a bien donné le résultat escompté sur le fichier posté initialement.

En D4, mets ceci :

=SI(SOMMEPROD(N(ESTERREUR(CHERCHE(Feuil5!$B$8:$B$32;B3))))-NBVAL(Feuil5!$B$8:$B$32)<0;"4";"")

en validant normalement, SOMMEPROD étant par définition déjà matricielle.

Bonjour @ tous,

en C3 :

=SIERREUR(INDEX(Feuil5!C$2:C$64;EQUIV(VRAI;ESTNUM(CHERCHE(SI(Feuil5!B$2:B$64<>"";Feuil5!B$2:B$64);B3));0));"")

@ valider par Ctrl+Shift+Enter

@ tirer vers le bas

@ + +

Bonjour,

NB.SI inadéquat pour ce que tu veux faire... (n'hésite pas à consulter la syntaxe des fonctions pour vérifier.

Evite de taper des nombres entre guillemets, les où c'est nécessaire sont d'une rareté vraiment extrême... mais les ennuis provoqués par les nombres entre guillemets sont beaucoup plus fréquents.

Les espaces dans les recherches sont mortelles !!!!!

Il y aura intérêt à nettoyer tes tables de références des espaces qui les entourent, sans quoi il est à prédire que tu ne pourras te fier à aucun résultat.

Et apprends à monter des tables pour pouvoir les utiliser facilement dans les formules, et à les nommer le cas échéant.

On est obligé d'individualiser la formule pour chaque classe alors qu'une bonne organisation doit permettre de taper une seule formule dans la cellule supérieure gauche d'un tableau et de la recopier sur tout le tableau.

Une formule qui fonctionnera dans ton cas :

=SI(SOMME(SI(ESTERREUR(CHERCHE(Feuil5!$B$2:$B$6;$B3));0;1));5;"")

La formule est matricielle, donc à valiser par Ctrl+Maj+Entrée.

Cordialement.

edit : Oh mais ça s'est étoffé pendant que je rédigeais mes conseils... Salut à tous !

Re,

j'ai pas bien vu le fichier.

voir PJ

15sy-prob.xlsx (12.45 Ko)

@ + +

@Rachid ... si la cellule contient 2 termes qui se trouvent dans la liste, la réponse devient 8.

J'ai donc évité cela en comparant le nombre de résultats trouvés et le nombre de termes.

Salut Steelson, il suffit de savoir qu'il y a au moins un résultat... Bon weekend pascal et bons zeufs !

Tout d'abord merci à tous pour vos messages et votre aide ! ça fait vraiment plaisir

C'est la première fois que j'écris sur un forum, et je dois avouer que c'est un très bel outil : donc merci !

Je suis désolé, mais je ne comprend pas grand chose... je ne connais absolument pas les formules que vous utilisez

Steelson

En D4, mets ceci :

CODE: TOUT SÉLECTIONNER

=SI(SOMMEPROD(N(ESTERREUR(CHERCHE(Feuil5!$B$8:$B$32;B3))))-NBVAL(Feuil5!$B$8:$B$32)<0;"4";"")

en validant normalement, SOMMEPROD étant par définition déjà matricielle.

En D3 et pas D4 (je pense que tu t'es trompé), en effet ça marche bien (j'obtiens bien 4) ! Cependant, lorsque je fais glisser cette formule jusqu'en D5, la case D5 ne m'indique rien (or je devrais avoir "4" puisqu'en B5 j'ai la phrase H314 (associée à la classe "4" d'après la table de référence).

Rachid, j'ai ouvert ton fichier. Ta méthode (même si, une nouvelle fois, je n'y comprend rien, à l'air de fonctionner) pour la première ligne. Cependant lorsque je sélectionne cette ligne (de C3 à H3) et que je la fait glisser jusqu'à la ligne 5 : pas de résultat pour les autres lignes..

De plus, j'aimerai éviter (si possible bien sûr), d'utiliser les pgl_chaîne (je suppose que c'est utiliser pour sélectionner des plages de données mais je n'y comprend rien...

Pouvoir glisser les formule de le première ligne sur les suivantes est impératif pour moi (le fichier principal contient plus de 400 lignes, donc impossible de tout taper à la main...)

Mr Fernand, votre formule semble fonctionner (testé des cellules C3 à E3). Cependant, comme faire pour étendre la sélection aux autres lignes en matriciel ? (comme je l'ai dit le tableur original contient plus de 400 lignes)

Si vous pouviez tous m'expliquez les formule que vous utilisez, ça m'aiderai énormément à comprendre (inutile de recopier sans comprendre si vous voyez ce que je veux dire)

Je remet les fichiers en copie : un pour la formule de steelson et un second pour Mr Fernand.

Merci et joyeuses pâques à tous !!

4sy-prob.xlsx (12.63 Ko)
5sy-prob-1.xlsx (12.78 Ko)
sy_prob a écrit :

je ne connais absolument pas les formules que vous utilisez

Steelson

En D4, mets ceci :

CODE: TOUT SÉLECTIONNER

=SI(SOMMEPROD(N(ESTERREUR(CHERCHE(Feuil5!$B$8:$B$32;B3))))-NBVAL(Feuil5!$B$8:$B$32)<0;"4";"")

En D3 et pas D4 (je pense que tu t'es trompé), en effet ça marche bien (j'obtiens bien 4) ! Cependant, lorsque je fais glisser cette formule jusqu'en D5, la case D5 ne m'indique rien (or je devrais avoir "4" puisqu'en B5 j'ai la phrase H314 (associée à la classe "4" d'après la table de référence).

Je n'ai pas été plus loin en effet, là il faut puiser dans les propositions des collègues !

Je t'expliquerai la formule ... il y a sans doute plus simple.

A ce soir ...

Bonjour,

Le contenu de ta cellule en colonne B est une chaîne. Tu ne peux appliquer ce contenu à NB.SI qui attend une plage de cellule et qui serait donc de même inopérante si tu parvenait à transformer ce contenu en matrice (mais dans ce cas tu pourrais utiliser EQUIV qui peut fonctionner sur toute matrice).

Il reste donc comme utilisable les recherches de texte dans un autre texte. On ne va pas chercher le contenu de B3 dans ta table de référence, mais l'inverse, si un élément de ta table de réf. est dans la chaîne, comme tu cherches plusieurs texte dans un, la formule devra être validée matriciellement. Pour pouvoir utiliser le résultat il faut additionner les résultats individuels pour chaque élément de la table pour n'en avoir qu'un. Mais quand la recherche n'aboutit pas elle renvoie une erreur, qu'il faut récupérer pour faire renvoyer des 0 à la place. Si l'addition des recherches individuelles donne 0, c'est qu'aucun élément de la table n'est dans la chaîne, si elle donne un autre résultat, c'est qu'il y en a au moins un.

Pour un formule recopiable, on va devoir un peu bricoler : nommer chaque table (Classe_5, Classe_4.... [Classe 5 serait refusé]) ; ajuster la ligne d'en-tête aux noms de tables (on y fera référence dans la formule par la réf. cellule avec colonne relative qui la rend recopiable sur les autres colonnes et ligne absolue ; comme on appelle le nom par le contenu d'une cellule l'utilisation de INDIRECT est requise) ; le texte testé, comme auparavant colonne absolue et ligne relative ; enfin, la classe par extraction du caractère à droite dans la ligne d'en-tête et sa conversion en nombre.

Ce qui donne :

=SI(SOMME(SI(ESTERREUR(CHERCHE(INDIRECT(C$2);$B3));0;1));CNUM(DROITE(C$2;1));"")

NB- tu devrais avoir des résultats sur la dernière ligne, que tu n'as pas en raison des espaces. A toi de traiter ce problème une bonne fois. On ne va pas le faire à ta place et c'est très désagréable de nous faire travailler dans ces conditions !

5sy-prob-1.xlsx (12.91 Ko)

Bonjour,

Maréchal...

en fait après avoir testé, cela marche bien mais il peut y avoir des erreurs car dans les matrices il existe des espaces en fin de valeurs.

Alors si vous "tapez" seulement H314 et bien chez mois il ne le trouve pas car dans la matice_4 c'es "H314 " avec un espace...

@ bientôt

LouReeD

Salut LouReed ! Bon weekend !

C'est bien ce que j'ai dit qu'il fallait qu'il épure...

Oups !!! ce doit être le chocolat !!!!

Je n'avais pas lu le post jusqu'à la fin !

Bon weekend à vous également et Joyeuses Pâques !!! D'ailleurs Pâques c'est 49 jours après quoi déjà ?

On le calcul souvent sous excel mais en fait je ne fais que des copier coller de formules sans savoir ce que j'écris !!!

Pauvre de moi

@ bientôt donc

LouReeD

C'est la Pentecôte qui est 49 jours après Pâques. Ne compressons pas.

Tiens c'est d'ailleurs la dérive de la date de Pâques qui est une des raisons de la réforme du calendrier grégorien. Mais ne soyons pas mauvaise langue, les problèmes plus concrets au niveau agricole où les dates des almanachs traditionnels ne collaient plus...

A+

Merci Mferrand (désolé pour l'erreur dans mon précédant post...)

NB- tu devrais avoir des résultats sur la dernière ligne, que tu n'as pas en raison des espaces. A toi de traiter ce problème une bonne fois. On ne va pas le faire à ta place et c'est très désagréable de nous faire travailler dans ces conditions !

Je suis désolé, en fait je ne voyais pas trop de quoi vous parliez au début. Je ne savais pas qu'il y avait des espaces et je ne savais pas encore moins, que cela gêné.

J'ai donc supprimé les espaces comme conseillé, et en effet ça marche !

J'essaie d'adapter cette solution sur mon tableur initiale et je reviens vers vous ce soir (afin de clôturer le topic).

Si certains sont intéressés, je remet le fichier en question en pièce jointe.

Une nouvelle fois : un grand merci

Re,

Steelson a écrit :

@Rachid ... si la cellule contient 2 termes qui se trouvent dans la liste, la réponse devient 8.

J'ai donc évité cela en comparant le nombre de résultats trouvés et le nombre de termes.

Oui Steelson j'y ai pas pensé.

=MIN(SOMMEPROD(ESTNUM(CHERCHE(Plg_Chaines;$B3))*DROITE(C$2));DROITE(C$2)*1)

j'essaie d’éviter le maximum possible la fonction SI().

@ + +

Rechercher des sujets similaires à "probleme reference formule"