Microsoft 365 Addition des nombres dans une cellule avec du texte

[Dreyz]

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.

 

[mapomme]

@R@chid,

Sinon pourrais-tu expliquer en détail le fonctionnement de ta superbe formule avec FILTRE.XML ?
Merci, car j'ai beau me creuser les méninges.
Je ne comprends pas comment elle fonctionne.

Même demande que @Staple1600...
(mais je crains de devoir me plonger dans le domaine inconnu de Xpath 😱)

 

[Dreyz]

Re,


On peut faire plus courte, en B1 :

=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]

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 😱)

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

=SUM(SPLIT(A1;" "))

Cordialement

 

[soan]

Bonjour @Staple1600, le fil,

j'ai une explication très simple et très facile :
c'est comme tes [aparté] ... [/aparté] 😂🤣

à part que c'est avec <x> et </x> au lieu de [x] et [/x]

(tu devrais pas être trop dépaysé ! 😜)

soan

 

[Staple1600]

Bonsoir le fil

R@chid
mapomme et ma pomme, je pense qu'on a juste besoin d'explications sur cette partie de ta formule

=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.

 

[soan]

=> 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

 

[Staple1600]

Re

soan
Merci mais ma question s'adressait à R@chid
(puisque c'est lui qui a proposé cette superbe formule)

NB: J'avais écrit de "mémoire"
Donc merci de l'avoir rafraichie.

 

[R@chid]

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

 

[Staple1600]

Bonsoir R@chid

Je n'ai pas vu ton fichier
J'avais copier/coller ta formule depuis ton post dans mon Excel.
Je vais de ce pas voir ton fichier.
Merci pour tes explications.

 

[R@chid]

Le fichier du Post #48
https://www.excel-downloads.com/attachments/filtrexml-xlsx.1088996/

Cordialement

 

[Staple1600]

Re

Merci pour ce fichier trés complet
Et qui me permet de voir que mon Excel 2013 n'est plus à la page 😉

=TRANSPOSE(_xlfn.ANCHORARRAY(C18))

Donc pas d'ANCHORARRAY pour bibi 😉

 

[R@chid]

Re,
J'étais pas devant mon PC pour te répondre,

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.

J'espère avoir été clair.


Cordialement

 

[Staple1600]

Bonjour le fil

=>R@chid
Merci pour ce complément d'information.
Ton fichier m'a également permis de me rendre compte que FILTRE.XLM utilise les fonctions WEBSERVICES.
Ce qui implique donc d'être connecté en permanence.
Mais alors est-ce là, la nouvelle aube?
Entre deux maux, lequel choisir?
Vite, mon doudou
😉