Somme de différentes valeurs contenues dans une cellule

[BChaly]

Bonsoir,

Les cellules contenues dans la colonne "B2:B6" contiennent différentes valeurs.

En jonglant avec des formules contenues dans un tableau séparant les valeurs "D1:F7",
j'obtiens en "B7" la somme souhaitée.

Le problème, est que ceci ne fonctionne pas, si les valeurs sont supérieures à 9. (voir fichier joint)

Est-il possible de faire la somme de ces valeurs sans macro?

Merci pour votre aide.

Cordialement,

BChaly

 

[Staple1600]

Re : Somme de différentes valeurs contenues dans une cellule

Bonsoir à tous

BChaly
Si tu avais utilisé le moteur de recherche du forum, tu aurais trouvé réponse à ta question avant d'ici la poser dans les archives du forum
Voir EVALUER et macro XL4
D'ailleurs récemment R@chid à poster ce type de formule dans une discussion proche de la tienne

 

[BChaly]

Re : Somme de différentes valeurs contenues dans une cellule

Bonsoir JM,

Merci pour ta réponse. Cette fonction "EVALUER" que je découvre, est très intéressante,
et je pense que tu fais référence à la discussion du 06/05.

Mais comment l'adapter à ma demande?

Le code suivant dans le gestionnaire des noms:

=EVALUER("{"&SUBSTITUE(SUBSTITUE(Sheet1!$B2;"a";);" ";";")&"}")

Ainsi que la formule suivante en "D2" (pour la valeur "a"):

=somme(MesValeurs)

me renvoient une erreur.

Que dois-je modifier?

Cordialement,

BChaly

 

[job75]

Re : Somme de différentes valeurs contenues dans une cellule

Bonsoir BChaly, Jean-Marie,

Problème intéressant, voyez le fichier joint et cette fonction macro :

Function SommeTexte(r As Range, a) As String
'a doit être un vecteur colonne
Dim ub%, t, i&, s, j%
ub = UBound(a)
ReDim t(1 To r.Count, 1 To ub)
For i = 1 To r.Count
  For Each s In Split(r(i)) 'le séparateur est l'espace
    For j = 1 To ub
      If Right(s, Len(a(j, 1))) = a(j, 1) Then t(i, j) = Val(s)
    Next
  Next
Next
For j = 1 To ub
  SommeTexte = SommeTexte & " " & Application.Sum(Application.Index(t, , j)) & a(j, 1)
Next
SommeTexte = Mid(SommeTexte, 2)
End Function

Elle est utilisée en B7 avec cette formule :

=SommeTexte(B2:B6;{"a";"b";"c"})

Nota : les textes "a" "b" "c" peuvent être des textes quelconques.

A+

 

[BChaly]

Re : Somme de différentes valeurs contenues dans une cellule

Bonsoir job75,

Cette solution est Merveilleuse et me satisfait pleinement!!!

Très grand Merci pour votre aide.

Cordialement,

BChaly

 

[Staple1600]

Re : Somme de différentes valeurs contenues dans une cellule

Re, Bonsoir Job75

BChaly
Pour compléter mon précédent message
Voici le fil récent dont je parlais
https://www.excel-downloads.com/threads/extraire-des-nombres-dune-cellule-10-2-8-15.224415/

 

[job75]

Re : Somme de différentes valeurs contenues dans une cellule

Re,

Si comme sur l'exemple les textes "a" "b" "c" sont constitués d'une seule lettre et placés dans une cellule (B1) :

Function SommeTexte$(r As Range, t$)
Dim L%, tablo, i&, s, j%
L = Len(t)
ReDim tablo(1 To r.Count, 1 To L)
For i = 1 To r.Count
  For Each s In Split(r(i)) 'le séparateur est l'espace
    For j = 1 To L
      If Right(s, 1) = Mid(t, j, 1) Then tablo(i, j) = tablo(i, j) + Val(s)
    Next
  Next
Next
For j = 1 To L
  SommeTexte = SommeTexte & " " & Application.Sum(Application.Index(tablo, , j)) & Mid(t, j, 1)
Next
SommeTexte = Mid(SommeTexte, 2)
End Function

Fichier (2).

A+

 

[BChaly]

Re : Somme de différentes valeurs contenues dans une cellule

Bonsoir job75, Bonsoir JM,

Merci à tous les deux pour votre aide précieuse.

Cordialement,

BChaly

 

[R@chid]

Re : Somme de différentes valeurs contenues dans une cellule

Bonsoir @ tous,
Salut Staple1600, Salut Job75,

Voir PJ



@ + +

 

[job75]

Re : Somme de différentes valeurs contenues dans une cellule

Bonjour le fil, bonjour R@chid, heureux de te croiser,

Avec un tableau VBA à une seule dimension c'est plus simple et plus rapide :

Function SommeTexte$(r As Range, t$)
Dim L%, tablo, i&, s, j%
L = Len(t)
ReDim tablo(1 To L)
For i = 1 To r.Count
  For Each s In Split(r(i)) 'le séparateur est l'espace
    For j = 1 To L
      If Right(s, 1) = Mid(t, j, 1) Then tablo(j) = tablo(j) + Val(s)
    Next
  Next
Next
For j = 1 To L
  SommeTexte = SommeTexte & " " & tablo(j) & Mid(t, j, 1)
Next
SommeTexte = Mid(SommeTexte, 2)
End Function

Avec cette manière de faire on gagne 40% sur la durée d'exécution pour le fichier (2).

A+

 

[job75]

Re : Somme de différentes valeurs contenues dans une cellule

Re,

Une autre solution par formule matricielle, à valider par Ctrl+Maj+Entrée :

=SOMME(SI(ESTNUM(CHERCHE("a";" "&B3:B7));--STXT(" "&B3:B7;CHERCHE("a";" "&B3:B7)-2;2)))&"a "&SOMME(SI(ESTNUM(CHERCHE("b";" "&B3:B7));--STXT(" "&B3:B7;CHERCHE("b";" "&B3:B7)-2;2)))&"b "&SOMME(SI(ESTNUM(CHERCHE("c";" "&B3:B7));--STXT(" "&B3:B7;CHERCHE("c";" "&B3:B7)-2;2)))&"c"

Bémol : elle ne fonctionne qu'avec 1 ou 2 chiffres devant les lettres.

Pour aller jusqu'à 3 chiffres il faut d'abord remplacer chaque espace par 2 espaces :

=SOMME(SI(ESTNUM(CHERCHE("a";"  "&SUBSTITUE(D3:D7;" ";"  ")  ));--STXT("  "&SUBSTITUE(D3:D7;" ";"  ");CHERCHE("a";"  "&SUBSTITUE(D3:D7;" ";"  ")  )-3;3)))&"a "&SOMME(SI(ESTNUM(CHERCHE("b";"  "&SUBSTITUE(D3:D7;" ";"  ")  ));--STXT("  "&SUBSTITUE(D3:D7;" ";"  ");CHERCHE("b";"  "&SUBSTITUE(D3:D7;" ";"  ")  )-3;3)))&"b "&SOMME(SI(ESTNUM(CHERCHE("c";"  "&SUBSTITUE(D3:D7;" ";"  ")  ));--STXT("  "&SUBSTITUE(D3:D7;" ";"  ");CHERCHE("c";"  "&SUBSTITUE(D3:D7;" ";"  ")  )-3;3)))&"c"

Fichier joint.

A+

 

[job75]

Re : Somme de différentes valeurs contenues dans une cellule

Re,

Avec cette solution on peut utiliser SOMMEPROD et éviter la validation matricielle.

Avex un maximum de 10 chiffres :

=SOMMEPROD(--STXT(SUBSTITUE(" "&B3:B7&" 0a";" ";"         ");CHERCHE("a";SUBSTITUE(" "&B3:B7&" 0a";" ";"         "))-10;10))&"a "&SOMMEPROD(--STXT(SUBSTITUE(" "&B3:B7&" 0b";" ";"         ");CHERCHE("b";SUBSTITUE(" "&B3:B7&" 0b";" ";"         "))-10;10))&"b "&SOMMEPROD(--STXT(SUBSTITUE(" "&B3:B7&" 0c";" ";"         ");CHERCHE("c";SUBSTITUE(" "&B3:B7&" 0c";" ";"         "))-10;10))&"c"

Fichier (2).

A+

 

[job75]

Re : Somme de différentes valeurs contenues dans une cellule

Re,

En passant par une cascade de 3 noms définis on obtient des formules "simples".

Voyez ce fichier (3).

Bien noter que mat est un vecteur ligne.

A+

 

[BChaly]

Re : Somme de différentes valeurs contenues dans une cellule

Bonsoir R@chid, Bonsoir job75,

De retour, je vois que la discussion s'est prolongée.

Vous êtes vraiment plus que très fort, et certainement des extraterrestres

Bravo à tous les deux, et Mille Merci pour ces suggestions.

Cordialement,

BChaly