Sommer les caractères numériques d'une chaîne alphanumérique Fermé

Question

Bonjour à toutes et à tous,
Je viens à vous pour faire appel à votre expertise excel.

J'ai un tableau qui se présente sous la forme suivante:

HSA 2	CP 3	A 2,6	HSD 5	HSA 5		7	Total HSA ligne 1
CP 5,7	HSA 3,6	HSD 5	A 31	B4		3,6	Total HSA ligne 2
							
						10,6	Total HSA lignes 1 + 2


Dans la 1ère ligne, à chaque fois que je trouve HSA, j'extraie le nombre qui suit et j'en fais la somme. La formule que j'utilise est la suivante:

=SI(NB.SI(A1;"*HSA*");SUBSTITUE(STXT(A1;MAX((STXT(A1;LIGNE($1:$30);1)>"9")*LIGNE($1:$30))+1;30);".";",")*1;0)+SI(NB.SI(B1;"*HSA*");SUBSTITUE(STXT(B1;MAX((STXT(B1;LIGNE($1:$30);1)>"9")*LIGNE($1:$30))+1;30);".";",")*1;0)+SI(NB.SI(C1;"*HSA*");SUBSTITUE(STXT(C1;MAX((STXT(C1;LIGNE($1:$30);1)>"9")*LIGNE($1:$30))+1;30);".";",")*1;0)+SI(NB.SI(D1;"*HSA*");SUBSTITUE(STXT(D1;MAX((STXT(D1;LIGNE($1:$30);1)>"9")*LIGNE($1:$30))+1;30);".";",")*1;0)+SI(NB.SI(E1;"*HSA*");SUBSTITUE(STXT(E1;MAX((STXT(E1;LIGNE($1:$30);1)>"9")*LIGNE($1:$30))+1;30);".";",")*1;0)

N'étant pas expert, je réplique la même formule par cellule.

Ma 1ère question est donc: sauriez-vous me dire comment je peux faire de manière plus courte et plus élégante pour extraire le nombre à chaque fois que je trouve "HSA" et sommer sur une ligne entière s'il vous plait ?

Aussi, j'aimerais faire la même chose sur 2 lignes. Existe-t-il une manière de faire sur 2 lignes (extraire le nombre à chaque fois que je trouve "HSA" et sommer sur 2 lignes) s'il vous plait ?

Merci beaucoup pour votre aide !

Bien à vous,

kkurt · Answer

fait un tableau croisée dynamique, ça fera tout seul se genre de calcul

Babou193 · Answer

Merci kkurt. En fait, les nombres sont inclus dans la cellule (on ne voit pas bien dans mon copier coller.

Une cellule contient par exemple "HSA 2"

kkurt · Answer

y a pas bcp de solution alors dans ce cas. le mieux est de séparer le titre du numérique.

mais comme HSA apparait pas dans les même colonnes entre chaque ligne c est la merde.

tontong · Answer

Bonjour,
Si la séquence Texte, espace, nombre, espace se répète en étant toujours respectée il est simple de découper les cellules avec Données Convertir Séparateur "espace".
Il sera plus simple ensuite de travailler sur un tableau.

eriiic · Answer

Bonjour,

proposition avec une fonction personnalisée. A mettre dans un module standard :
Function sommeCh(plage As Range, chaine As String) As Double
    ' fait la somme des nombres suivant une chaine
    Dim c As Range, ch1 As String, ch2 As String, decoupe As Variant, i As Long
    For Each c In plage
        ch1 = UCase(Replace(c, ",", "."))
        If InStr(ch1, chaine) Then
            decoupe = Split(ch1, UCase(chaine))
            For i = 0 To UBound(decoupe)
                ch1 = Trim(decoupe(i))
                If InStr(ch1, " ") Then
                    ch1 = Split(ch1, " ")(0)
                End If
                sommeCh = sommeCh + Val(ch1)
            Next i
        End If
    Next c
End Function
Ex syntaxe : =sommeCh(A2:A3;"HSA")
https://www.cjoint.com/c/DJrqlI8RpaF
Alt+F11 pour voir le code dans module 1

eric

Babou193 · Answer

Bonjour,
Etant  nouveau dans ce genre de forum, je n'avais pas pensé qu'il fallait respecter un certain délai avant de poser la question ailleurs. J'ai donc eu des réponses sur un autre site que je poste ici pour l'information de toutes et tous. Il existe 3 solutions à la question posée sous forme de formules matricielles:

=SOMMEPROD(SI(GAUCHE(A1:E2;3)="HSA";--STXT(A1:E2;4;15);0))

=SOMMEPROD(ESTNUM(CHERCHE("hsa";A1:E1)*(CHERCHE({1;2;3;4;5;6;7;8;9;0};A1:E1)))*({1;2;3;4;5;6;7;8;9;0}))

=SOMME(SIERREUR(CNUM(SUBSTITUE(A1:E1;"HSA";));))

Les 3 fonctionnent. Merci respectivement à Eric, tulipe_4 et R@chid pour leur précieuse aide.

Bien à vous,

Sommer les caractères numériques d'une chaîne alphanumérique

6 réponses

Discussions similaires