Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Somme de différentes valeurs contenues dans une cellule

B

BChaly

XLDnaute Occasionnel
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
 

Pièces jointes

  • SommeMultiValeurs.xlsx
    9.9 KB · Affichages: 35
Trier par date Trier par votes

Staple1600

XLDnaute Barbatruc
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
 
Vote positif 0 Vote négatif
B

BChaly

XLDnaute Occasionnel
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:
Code: 
=EVALUER("{"&SUBSTITUE(SUBSTITUE(Sheet1!$B2;"a";);" ";";")&"}")

Ainsi que la formule suivante en "D2" (pour la valeur "a"):
=somme(MesValeurs)
Cliquez pour agrandir...

me renvoient une erreur.

Que dois-je modifier?

Cordialement,

BChaly
 
Dernière édition:
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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 :

Code: 
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 :

Code: 
=SommeTexte(B2:B6;{"a";"b";"c"})
Nota : les textes "a" "b" "c" peuvent être des textes quelconques.

A+
 

Pièces jointes

  • SommeMultiValeurs(1).xls
    49 KB · Affichages: 24
Dernière édition:
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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) :

Code: 
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+
 

Pièces jointes

  • SommeMultiValeurs(2).xls
    48.5 KB · Affichages: 23
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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 :

Code: 
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+
 
Vote positif 0 Vote négatif

job75

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

Re,

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

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

Code: 
=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+
 

Pièces jointes

  • SommeMultiValeurs par formule(1).xls
    48 KB · Affichages: 21
Dernière édition:
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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 :

Code: 
=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+
 

Pièces jointes

  • SommeMultiValeurs par formule(2).xls
    37 KB · Affichages: 19
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
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+
 

Pièces jointes

  • SommeMultiValeurs par formule(3).xls
    37.5 KB · Affichages: 21
Vote positif 0 Vote négatif
B

BChaly

XLDnaute Occasionnel
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
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.

Discussions similaires

Y
  • Question
XL 2013 Recherche d'une valeur de la feuil1 dans une base de la feuil2
Réponses
6
Affichages
224
job75
F
  • Question
XL 2019 Tri dans une même colonnes de valeurs contenues dans plusieurs colonnes
Réponses
14
Affichages
677
fabienb
F
J
  • Question
Microsoft 365 tracer un triangle en fonction de trois valeurs de cellules
Réponses
12
Affichages
626
sylvanu
D
  • Question
XL 2010 Anomalie d'utilisation avec plage de cellule dans une formule
Réponses
4
Affichages
205
djidji59430
  • Question
XL 2019 sommer les valeurs d'un tableau dans un autre selon critere
Réponses
2
Affichages
185
JBOBO
  • Résolu(e)
Microsoft 365 Masquer des colonnes aux cellules avec valeurs données selon sélection d'une liste déroulante
Réponses
5
Affichages
606
PatFr38
  • Résolu(e)
Microsoft 365 Reporter automatiquement le nombre de valeurs uniques d'un tableau structuré filtré par plusieurs segments.
Réponses
3
Affichages
387
chris
J
  • Question
XL 2010 Création liste ? une cellule alimentée par une autre cellule ou non
Réponses
3
Affichages
385
Gégé-45550
B
  • Question
XL 2019 Calcul Nb de valeurs entre 2 dates
Réponses
4
Affichages
428
sylvanu
  • Question
Power BI MFC de colonne suivant valeurs de 2 tables différentes
Réponses
0
Affichages
357
luke3300
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…