XL 2013 Somme des nombres uniquement avec cellule incluant texte et chiffre

[Lily78]

Bonjour,

Je me permets de poster ce message car je suis en train de peaufiner un tableau de suivi des congés.
Je vous joins pour plus de visualisation le modèle en pièce jointe.

Mon souci réside pour calculer la somme des colonnes AL, AM et AR. En effet, ces dernières comporte des données systématiquement numériques et textuelles.
Je sais extraire le texte de la cellule et ne conserver que le chiffre qui m'intéresse (fonction STXT ou GAUCHE) mais comment ensuite faire la somme de toute la ligne ?
Est-ce possible d'avoir une unique formule qui ne conserve que le chiffre si la lettre apparait, puis calculer la somme de tous les chiffres sur la ligne entière ?


Je vous remercie par avance car nous sommes plusieurs à utiliser ce tableau et cela permettrait de limiter les erreurs de saisie..

 

[soan]

Bonjour Lily,

ton fichier en retour ; j'y ai fait plein d'modifs :

A) j'ai figé les volets, donc :

* si tu fais défiler loin vers la droite, tu verras toujours la colonne A
* si tu fais défiler loin vers le bas, tu verras toujours la ligne 1

B) en B2:AF13, les couleurs adéquates sont mises automatiquement
pour tes saisies de : C ; M ; AT ; CE ; R ; F ; AI ; PT ; ou avec HDS ; JM

C) colonne AR "JM" : le calcul correct s'y fait automatiquement.

---------------------------------------------------------------------------

je te laisse faire tous les tests nécessaires.

si besoin, tu peux demander une adaptation.
merci de me donner ton avis.

soan

 

[Lily78]

Bonsoir Soan,

Tout d'abord un grand merci pour ta contribution
J'utilise parfois pour d'autres tableau la mise en forme conditionnelle mais ici je n'y ai pas pensé !!!
Donc bonne idée effectivement.
Ensuite, impeccable la formule utilisée pour la colonne AR se calcule automatiquement : c'est bien se que je recherchais, pour les entiers et les décimales.

Toutefois, je me demande comment tu as fait pour masquer les formules car parmi toutes les fonctionnalités que je connais, je ne suis pas parvenue à afficher la formule utilisée.
Ors, je dois recopier cette dernière pour les 100 agents gérés.

Merci pour cette précision.

 

[Lily78]

Et aussi, cela me permettrait de faire à l'identique pour les colonnes AL et AM.

Thanks

 

[soan]

Bonsoir Lily,

je travaillais sur un autre exo, et j'viens d'voir ton post ; ne cherche pas la formule,
car il n'y en n'a pas ! c'est fait automatiquement par VBA : fais Alt F11 pour voir
le code VBA de Feuil1 ; c'est celui-ci :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .CountLarge > 1 Then Exit Sub
    If Intersect(Target, [B2:AF13]) Is Nothing Then Exit Sub
    Dim T#, chn$, lig&, col As Byte: lig = .Row
    For col = 2 To 32
      With Cells(lig, col)
        chn = .Value
        If Right$(chn, 2) = "JM" Then
          T = T + Val(Replace$(chn, ",", "."))
        End If
      End With
    Next col
    Cells(lig, 44) = T
  End With
End Sub

soan

 

[soan]

ajout : pour les colonnes AL et AM, je pensais bien que tu en aurais besoin,
mais d'une part je voulais que tu me confirmes que c'est ok pour AR,
et d'autre part je voulais être sûr que tu avais besoin de la même chose
pour AL et AM ; maintenant que tout est confirmé, je vais l'ajouter au
code VBA, mais ça va tarder (car j'ai d'autres occupations à côté).

soan

 

[soan]

Lis d'abord mes 2 posts précédents, puis celui-ci.

tu as écrit : « Or je dois recopier cette dernière pour les 100 agents gérés. »

vu ce qui précède, il n'y a donc pas de formule à recopier ! et si tu as un fichier
par agent, donc 100 fichiers, il faudra y recopier dans tous le code VBA ; mais
surtout, ne commence pas à le faire ! attends la prochaine version qui sera à
la fois pour AL, AM, et AR.

soan

 

[soan]

Bonjour Lily,

Lis d'abord mes 3 posts précédents #5 à #7.

voilà, c'est fait pour AL, AM, et AR ; donc pour "HDS", "R", et "JM".

fais des tests, et si tout est ok, tu peux copier le code VBA dans
les fichiers de chaque agent.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .CountLarge > 1 Then Exit Sub
    If Intersect(Target, [B2:AF13]) Is Nothing Then Exit Sub
    Dim T#(1 To 3), chn$, s$, lig&, col As Byte, k As Byte
    lig = .Row: Application.ScreenUpdating = 0
    For col = 2 To 32
      With Cells(lig, col)
        chn = .Value: s = Right$(chn, 3)
        k = -(s = "HDS") - 2 * (Right$(s, 2) = "JM") - 3 * (Right$(s, 1) = "R")
        If k > 0 Then T(k) = T(k) + Val(Replace$(chn, ",", "."))
      End With
    Next col
    If T(1) > 0 Then Cells(lig, 38) = T(1) 'HDS
    If T(3) > 0 Then Cells(lig, 39) = T(3) 'R
    If T(2) > 0 Then Cells(lig, 44) = T(2) 'JM
  End With
End Sub

soan

 

[Lily78]

Bonjour,

Alors, je m'en doutais….. mais je ne sais pas utiliser pas de VBA
Bon, je veux bien essayer mais en ouvrant les deux fichiers Excel, aucun de fonctionne sur mon poste de travail alors que hier soir en essayant de chez moi, le 1er fichier fonctionnait.
Je n'y comprends rien.

 

[Lily78]

PS : La mise en forme se fait mais pas le calcul

 

[soan]

alors c'est que les macros doivent être désactivées !

il faut obligatoirement activer les macros ; y'a pas
d'autre moyen !!!

et j'ai pas d'solution sans VBA !!!!!!

(tu veux une boîte entière de kleenex ?)

soan

 

[soan]

ajout : pour activer les macros, suit la procédure indiquée
dans mon post #28 (clique sur le lien bleu pour y aller).

soan

 

[Lily78]

Bonjour,

C'est ok pour moi, macros activées
Le code fonctionne !
Serait-il possible du coup d'avoir une vba pour calculer directement les autres sommes, à partir de la colonne AG, cela éviterait de combiner le fichier avec des formules ? (oui, je sais j'abuse un peu )

Merci encore

 

[Jocelyn]

Bonjour le Forum,
Bonjour Lily78, soan,

Pour le fun une solution par formule matricielle

Cordialement

 

[soan]

Bonjour @Lily78, Jocelyn,

j'suis très content qu't'aies réussi à activer tes macros !

(surtout qu'c'est un passage obligé pour faire du VBA sans la contrainte
pénible du message récurrent « Voulez-vous activer les macros ? »)

--------------------------------------------------------------------------------------

ton fichier en retour. pour ta demande du post #13, j'ai rien modifié dans
le code VBA ; tout est fait sur la feuille de calcul (+ améliorations de présentation).

la méthode utilisée fonctionne uniquement pour des cellules non vides ! donc
pour "CA 19", tu dois obligatoirement entrer quelque chose ; vu que t'utilises
un remplissage avec motif de points, le texte "CA 19" par exemple donne un
mauvais effet visuel ; même si tu l'abrèges en "K" (ceci juste pour la consonnance),
c'est idem ; donc seule solution valable : saisir un point « . » : caractère qui
est à la fois discret et facile à d'accès sur le clavier ; j'dis ça car y'a un autre
point plus discret, mais il n'est pas sur une des touches du clavier : c'est par
une combinaison de touches Alt + code numérique (moins pratique).

à te lire pour avoir ton avis.

soan