Microsoft 365 Addition des nombres dans une cellule avec du texte

Dreyz

XLDnaute Nouveau
Bonjour à tous,

Je tente en vain d'additionner des chiffres de ma colonne A qui contient des nombres et des lettres, vers ma colonne B
Exemple :
A1 :
benoit x 1, eric x 1, nathalie x 1, marc x 15
J'aimerais avoir en B = 18

A2 :
jean-jacques x 1, eric x 1, marie x 1
En B = 3

A3 :
jean-jacques x 1, eric x 5, marie x 1, marie x 1, marc x 1, sylvie x 1, , flavien x 5
En B = 15

Je cherche donc à trouver une formule pour additionner les chiffres se trouvant après "x ".
Je précise que je suis essentiellement sous google sheet, ayant testé une formule type (je débute sur les tableurs, j'avais trouvé cela sur le net)

=SOMMEPROD(SIERREUR(1*STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0))

Mais qui ne prend pas en compte les valeurs supérieurs à 9. Exemple au lieu d'avoir d'avoir une somme de 18 en B1 , il me renvoie 9 (1+1+1+1+5).


Merci à vous,
Nicolas.
 
Dernière édition:
Solution
Re,
Puis je me suis souvenu que j'avais des archives alors j'ai mis un masque et soulevé la poussière et bim!
=SOMME(SIERREUR((STXT(SUBSTITUE(SUBSTITUE(A1;",";"");" ";REPT(" ";NBCAR(A1)));1+NBCAR(A1)*(LIGNE(A$1:INDEX(A:A;NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))+(A1<>"")))-1);NBCAR(A1)))+0;0))
Il faudrait un volontaire sous XL2K10 pour la tester ;)

On peut faire plus courte, en B1 :
VB:
=SOMME(SIERREUR(STXT(SUBSTITUE(A1;" ";REPT(" ";NBCAR(A1)));(LIGNE(INDIRECT("1:"&(NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";))+1)))-1)*NBCAR(A1);NBCAR(A1))*1;))
@ valider par Ctrl+Maj+Entrée
@ tirer vers le bas

Valide pour toutes les versions à partir de 2007.

Noter que sous Excel pour Microsoft 365 on n'a pas...

Dreyz

XLDnaute Nouveau
Re,


On peut faire plus courte, en B1 :
VB:
=SOMME(SIERREUR(STXT(SUBSTITUE(A1;" ";REPT(" ";NBCAR(A1)));(LIGNE(INDIRECT("1:"&(NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";))+1)))-1)*NBCAR(A1);NBCAR(A1))*1;))
@ valider par Ctrl+Maj+Entrée
@ tirer vers le bas

Valide pour toutes les versions à partir de 2007.

Noter que sous Excel pour Microsoft 365 on n'a pas besoin de validation matricielle.


Cordialement
Bonjour, cela fonctionne également sous google sheet,
Un grand merci à vous.
 

R@chid

XLDnaute Barbatruc
Bonjour @ tous,
@mapomme
@Staple1600
Je sais que vous savez que les explications ne sont pas mon fort, à plusieurs reprises c'était mapomme qui prend le relais pour donner des explications.

Test OK avec ta 3ième formule
Mais c'est logique puisque la mienne fonctionnait ;)
D'ailleurs tu le dis toi-même ;)
Valide pour toutes les versions à partir de 2007.
Toutes versions d'Excel possédant SOMME, SUBSTITUE, REPT etc verra nos formules fonctionner. C'est tout ce que je voulais dire ;)
Je parlais de la formule avec FILTRE.XML(), sinon la tienne et ma 3ème formule, vont fonctionner sur toutes les versions à partir de 2007 car SIERREUR() a vu le jour à partir de cette version.
FILTRE.XLM() est apparue avec la version 2013 et malgré tout ne fonctionne pas sur tous les Excel, quelques utilisateurs vont ouvrir le fichier et ils vont trouver une écriture de type ={xml.filter(formule...etc, je ne me rappelle pas puisque tout fonctionne correctement sous Excel 365 :)


(mais je crains de devoir me plonger dans le domaine inconnu de Xpath :eek:)
Malheureusement oui ;) :)
J'espère que le fichier ci-joint vous donne une idée sur l'utilisation des fonctions SERVICEWEB() et FILTRE.XML().
XML Tutorial (w3schools.com)


Bonjour, cela fonctionne également sous google sheet,
Si c'est sur GoogleSheet, préfère la formule que je t'ai donné en Post #12
VB:
=SUM(SPLIT(A1;" "))



Cordialement
 

Pièces jointes

  • FiltreXML.xlsx
    13.6 KB · Affichages: 18

Staple1600

XLDnaute Barbatruc
Bonsoir le fil

R@chid
mapomme et ma pomme, je pense qu'on a juste besoin d'explications sur cette partie de ta formule
Enrichi (BBcode):
=SOMME(FILTRE.XML("<a><b>"&SUBSTITUE(A1;" ";"</b><b>")&"</b></a>";"//b"))
Cela ressemble à des balises Html mais pourquoi leur emploi font que ne sont gardés que les chiffres?
De mémoire, en HTML, </b> c'est pour mettre en gras, non?

NB: j'étais été voir le lien que tu as mis, mais je n'ai pas trouvé d'explications.
 
Dernière édition:

soan

XLDnaute Barbatruc
Inactif
=> Staple

dans le cas présent, en HTML, </b> signifie simplement :
fin du « paragraphe » qui avait débuté par <b>.

c'est pareil pour <a> ... </a> : </a> est la fin du § qui
a commencé par <a>.

quand j'te disais qu'c'est pareil que pour tes apartés ! 😜 sauf que
ça utilise des chevrons
< et > au lieu de crochets [ et ]


soan
 
Dernière édition:

R@chid

XLDnaute Barbatruc
Bonsoir @ tous,
=SOMME(FILTRE.XML("<a><b>"&SUBSTITUE(A1;" ";"</b><b>")&"</b></a>";"//b"))[/CODE]
Cela ressemble à des balises Html mais pourquoi leur emploi font que ne sont gardés que les chiffres?
FILTRE.XML() ne garde pas que les chiffres, par contre elle renvoie une matrice contenant des chiffres et des textes, mais ces derniers n'affectent pas le calcul avec SOMME() puisque cette fonction peut faire la somme en ignorant les textes.
Sinon, pour <a><b>, tu peux utiliser ce que tu veux pour pouvoir séparer les mots, ou les chiffres, <x><y> ou <1><20>, sur le fichier exemple que j'ai joint j'ai fait <Texte><Mot>.

Tu ne m'as pas précisé c'est pour cela je suis parti vers l'explication de l'utilité de FILTRE.XML().

Cordialement
 

R@chid

XLDnaute Barbatruc
Re,
J'étais pas devant mon PC pour te répondre,
2020-12-18_01-52-14.jpg


Ce qui est bien en Excel 365, c'est que cette formule matricielle je vais juste la taper en C18 et la valider par simple Entrée, et après Excel va s'occuper d'adapter la taille de la matrice au fur et mesure selon le nombre de valeurs à renvoyer.

Après, j'ai utilisé TRANSPOSE() pour si l'on veut un jour utiliser ses valeurs comme noms de champs. Et pour transposer la matrice, on n'a pas besoin de créer un champ dynamique avec la fonction DECALER(), puisque le # après la première cellule de la matrice fait l'affaire.
2020-12-18_01-49-22.jpg


J'espère avoir été clair.


Cordialement
 

Discussions similaires

Statistiques des forums

Discussions
311 725
Messages
2 081 940
Membres
101 845
dernier inscrit
annesof