XL 2013 Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

[escouger]

Bonjour,
Je vous transmet un petit fichier illustrant mon souci.
Je veux réaliser une formule matricielle un peu complexe qui devrait additionner des cellules contenant au sein d'un texte, des chiffres contigus et toujours au même emplacement dans le texte.
Il semble que les instructions STXT et Somme.si soient incompatibles.

Voyez dans la partie haute du tableau un exemple avec des cellules à additionner classiques. (Fonctionne)
Dans la partie basse je voudrais les mêmes résultats sachant que les valeurs à additionner sont en position 4 et pour une longueur de 2 au sein d'un texte. (STXT(cellule;4;2)

Merci d'avance de votre aide.

GE

 

[Calvus]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Bonjour,

J'ai remplacé ta formule par celle-ci.

=SOMMEPROD((STXT($C$24:$E$26;4;2)*1)*(($B$24:$B$26)=B24))*((($C$23:$E$23)=B24))

A copier vers le bas.

Bonne journée

 

[escouger]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Merci c'est une bonne idée, mais malheureusement le résultat donne 45 en F24 ce qui représente la somme totale des 3 lignes 24 25 et 26.(alors que je voudrais F24=10).
F25 = 0 et F26 = 0 alors que j'attends respectivement 15 et 20)

Je trouve pourtant logique ce qui est proposé.
Cette formule est matricielle. D'ailleurs en "non matricielle" elle donne une erreur.


Merci

GE

 

[escouger]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Erreur de ma part: j'attends 0 en F26 car "titi" ne figure pas en ligne 23!

 

[R@chid]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Bonjour,
pourrais-tu nous dire pourquoi ne pas prendre en compte tata alors qu'il figure en B9:B11 et D20 commence par R ?

Edit : Ok j'ai bien compris je m'excuse

@ + +

 

[R@chid]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Re,
en F9 :

=SOMMEPROD(NB.SI(B9;C$8:E$8)*(GAUCHE(C$20:E$20)="R");C9:E9)

en F24 :

=SOMMEPROD(NB.SI(B24;C$23:E$23)*(GAUCHE(C$35:E$35)="R");CNUM(STXT(C$24:E$24;TROUVE("-";C$24:E$24)+1;TROUVE("|";SUBSTITUE(C$24:E$24;"-";"|";2))-TROUVE("-";C$24:E$24)-1)))

@ + +

 

[escouger]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

C'est super; çà marche comme je le souhaite.

J'ai toutefois envie de simplifier la recherche du tiret et l'identification des caractères à additionner qui est trop puissante pour le besoin réel et trop compliquée aussi pour que je la modofoe sans me tromper(!). Je ne suis un expert comme vous.
En fait dans la réalité les données additionnables sont toujours en position 8 et comportent 6 chiffres.

Pourriez-vous modifier F24 en fonction de cela ?

Merci encore de votre efficacité et rapidité.

GE

 

[R@chid]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Re,
le chiffre commence en position 8 :

=SOMMEPROD(NB.SI(B24;C$23:E$23)*(GAUCHE(C$35:E$35)="R");CNUM(STXT(C$24:E$24;8;6)))

@ + +

 

[escouger]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Merci R@Chid, trop fort.

GE!

 

[escouger]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Encore un dernier coup de pouce, s'il vous plait sur cette formule.

J'ai omis de vous dire que la comparaison sur la ligne 23 avec B24 (NB.SI(B24;C$23:E$23)
doit limiter sa longueur à celle de B24.
J'ai tenté de mettre (NB.SI (B24;gauche(nbcar(B24);C$23:E$23)) mais çà ne fonctionne pas.

Sur le tableau que je vous ai transmis essayer par exemple de remplacer dans la cellule C23 toto par toto-xyz.
Dans la formule actuelle il n'est pas additionné, alors qu'il devrait l'être

Merci pour ce tout dernier coup de pouce.
GE

 

[Amilo]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Bonjour le forum, Rachid, Calvus, escouger,
Peut-être une autre formule matricielle en F24 (pour info SIERREUR fonctionne à partir de la version 2007) :

=SIERREUR(CNUM(STXT(DECALER($B24;0;EQUIV(VRAI;B24=GAUCHE($C$23:$E$23;NBCAR(B24));0););4;2));0)

Pour votre fichier perso, modifiez les valeurs 4 et 2 dans cette partie NBCAR(B24));0);4;2)) par 8 et 6 par rapport à votre post 7

En fait dans la réalité les données additionnables sont toujours en position 8 et comportent 6 chiffres.

Bonne journée

Cordialement

 

[escouger]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Bonjour Amilo,
Cette formule , sauf si je l'ai mal implémentée, ne résout pas le seul point restant à solutionner, à savoir limiter la comparaison entre le contenu de la ligne 23 et celui de la colonne B sur une longueur égale au contenu de cette colonne B.
Exemple:
en B24 "toto" a une longueur de 4
en C23 figure "toto" La valeur 10 est à additionner
en D23 figure "tata" La valeur 15 n'est pas à additionner
en E23 figure "toto-xyz La valeur 20 est à additionner les 4 caractères de gauche de E23 = B24

Formule de R@chid
=SOMMEPROD(NB.SI(B24;C$23:E$23)*(GAUCHE(C$35:E$35)="R");CNUM(STXT(C$24:E$24;4;2)))

Formule de Amilo
=SIERREUR(CNUM(STXT(DECALER($B24;0;EQUIV(VRAI;B24=GAUCHE($C$23:$E$23;NBCAR(B24));0);4;2));0)

Je vous renvoie le tableau ou j'ai repris ces 2 formules des lignes 45 à 14 (R@chid) ou 49 à 51 (Amilo)

Merci encore de votre assistance.

 

[Amilo]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Re,

D'après vos nouvelles données, à essayer avec cette formule matricielle :

=SOMME(CNUM(STXT(C24:E24;4;2))*(B24=GAUCHE($C$23:$E$23;NBCAR(B24))))

Bonne journée

Cordialement

 

[escouger]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Re-bonjour,

Cette fois c'est tout bon, aussi bien dans le fichier de test que dans mon fichier réel.

Merci mille fois.

Ce message peut être cloturé.

GE

 

[R@chid]

Re : Formule matricielle récalcitrante utilisant Somme.si et des extraits de texte

Bonjour @ tous,

=SOMMEPROD(ESTNUM(CHERCHE(B24;C$23:E$23))*(GAUCHE(C$35:E$35)="R");CNUM(STXT(C$24:E$24;4;2)))

@ + +