Microsoft 365 Addition des nombres dans une cellule avec du texte

Dreyz · 16 Décembre 2020

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.

R@chid · 17 Décembre 2020

Re,

Staple1600 à dit:
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...

mapomme · 17 Décembre 2020

@R@chid,

Staple1600 à dit:
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 · 17 Décembre 2020

R@chid à dit:
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 · 17 Décembre 2020

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.

Staple1600 à dit:
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

mapomme à dit:
(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)

Dreyz à dit:
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

soan · 17 Décembre 2020

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 · 17 Décembre 2020

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, 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 · 17 Décembre 2020

=> Staple

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

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 · 17 Décembre 2020

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 · 17 Décembre 2020

Bonsoir @ tous,

Staple1600 à dit:
=SOMME(FILTRE.XML("<a>"&SUBSTITUE(A1;" ";"")&"</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>, 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 · 17 Décembre 2020

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 · 17 Décembre 2020

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

Cordialement

Staple1600 · 17 Décembre 2020

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 · 18 Décembre 2020

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 · 18 Décembre 2020

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

Microsoft 365 Addition des nombres dans une cellule avec du texte

Dreyz

XLDnaute Nouveau

R@chid

mapomme

XLDnaute Barbatruc

Dreyz

XLDnaute Nouveau

R@chid

XLDnaute Barbatruc

Pièces jointes

soan

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

soan

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

R@chid

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

R@chid

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

R@chid

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Discussions similaires