XL 2010 Fonction TROUVE en matriciel avec cellules vides...

[jcproperty]

Bonjour,

Je cherche à faire un truc un peu complexe, mais que je croyais possible jusqu'à aujourd'hui, peut-être que je me trompe !

Col A || Col B
1 John || #VALEUR
2
3 John || toto (0,5)
4 Mike ||
5 John || tututu (0,2)
6 Mike || tata (0,4)

Je souhaiterais sommer la partie numérique entre parenthèses dans les cellules de la colonne B, lignes 3 à 6, en fonction du prénom de la colonne A, et mettre le résultat en B1.

La récupération de la valeur entre parenthèses, j'ai réussi à l'obtenir avec :
CNUM(STXT(B3: D6;TROUVE("(";B3:B6)+1;TROUVE(")";B3:B6)-TROUVE("(";B3:B6)-1))

Mais par contre je bloque sur la somme conditionnelle. J'ai tenté du SOMMEPROD(A3:A6="John";CNUM...) ou bien du {SOMME(SI(A3:A6;"John";CNUM...)}, je tombe toujours sur une erreur #VALEUR.
J'ai compris que le problème venait de la valeur vide en B4 qui ne plait pas du tout à TROUVE() (ni à CHERCHE() d'ailleurs).
J'ai donc rajouté des conditions ESTNUM(TROUVE("(";B3:B6)) un peu partout, j'ai essayé avec du NON(ESTERR()), rien n'y fait, j'ai désespérément le retour #VALEUR dans ma cellule B1...

"Tu n'as qu'à créer des étapes de calcul intermédiaire dans la colonne C", me direz-vous. Oui mais voilà, je ne vous ai montré qu'un extrait, en réalité la plage B3:B6 est beaucoup plus grosse (50 lignes et plus de 150 colonnes). C'est donc un peu compliqué de dédoubler ce tableau pour y mettre des valeurs intermédiaires. Et je souhaiterais ne pas avoir recours aux macros, même si ça me démange.

Pouvez-vous m'aider sur ce coup-là ?
Merci d'avance.

 

[JHA]

Bonjour à tous,

Ci joint un essai avec ce que j'ai compris.

JHA

 

[jcproperty]

Trop fort !! Ah vraiment merci beaucoup, c'est génial !
Je n'avais pas été assez loin, il me manquait un test ESTERREUR sur toute l'expression CNUM(STXT...)
Excellent, merci encore !

 

[JHA]

Bonjour à tous,
Bonjour Jocelyn,

Ci joint la proposition de Jocelyn qui est bien plus claire et avec des formules beaucoup plus courtes.
pour le total

=SOMME(SI(ESTNUM(CHERCHE("(";B1:B6));CNUM(SUBSTITUE(STXT(B1:B6;CHERCHE("(";B1:B6)+1;99);")";""))))

Par nom

=SOMME(SI((ESTNUM(CHERCHE("(";$B$1:$B$6)))*($A$1:$A$6=D1);CNUM(SUBSTITUE(STXT($B$1:$B$6;CHERCHE("(";$B$1:$B$6)+1;99);")";""))))

Formules matricielles
Merci à Jocelyn pour cette simplification.
Je pense que "jcproperty" appréciera.

JHA

 

[jcproperty]

En effet ça fonctionne aussi ! Même si je ne comprends pas comment...
Le fait d'utiliser SUBSTITUE() enlève le besoin de tester que l'expression CNUM(STXT(... tombe en erreur, alors que sans SUBSTITUE() on est obligés de le faire... très étrange, mais en effet, j'apprécie et je prends !
Merci beaucoup !!

 

[Jocelyn]

Bonjour Jcproperty,
Re JHA,

un essai d'explication,

pour le total en fait la formule ne teste pas le CNUM(STXT )) elle teste si dans la plage B1:b6 il y a la présence de la paranthèse ouvrante"('" est pour toutes les cellules contenant cette paranthèse on extrait les valeur chiffrées en se servant de Cnum(substitue(stxt))))substitue et on en fait la somme

pour le "par prénom" elle teste si dans la plage B1:b6 et en plus si le prénom est présent dans la plage A1:A6 ensuite même fonction de Cnum(substitue(stxt))))

voila j'espere t'avoir éclairé un peu

Cordialement

 

[jcproperty]

Merci Jocelyn, j'avais bien compris la formule en fait. Ce que je ne comprenais pas, c'est pourquoi tous mes essais ramenaient #VALEUR, alors que je faisais à peu près pareil que la formule de JHA. Alors je me suis dit que c'était dû au SUBSTITUE() qui changeait peut-être une façon de calculer dans Excel, mais en fait non. Je viens de refaire un de mes 1ers essais... et ça fonctionne... donc je ne comprends plus du tout ! J'ai dû faire une faute ailleurs, je ne sais pas laquelle. Bref, ça fonctionne et c'est l'essentiel !
Merci à tous.

 

[mapomme]

Bonjour à tous,

Pour le fun, avec deux noms dynamiques, la formule devient plus lisible.

Pour le total:

=SOMME(SIERREUR(CNUM(SUBSTITUE(STXT(Valeur;TROUVE("(";Valeur)+1;99);")";""));0))

Pour un prénom:

=SOMME(SIERREUR(CNUM(SUBSTITUE(STXT(Valeur;TROUVE("(";Valeur)+1;99);")";""));0)*(Nom=E1))

nota: les formules sont des formules matricielles.