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

Lily78

XLDnaute Nouveau
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..

:):):)
 

Pièces jointes

  • test congés.xlsx
    16 KB · Affichages: 14

soan

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

Pièces jointes

  • test congés.xlsm
    24.1 KB · Affichages: 7

Lily78

XLDnaute Nouveau
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.
 

soan

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

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

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

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

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


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

Pièces jointes

  • test congés.xlsm
    25 KB · Affichages: 6

Lily78

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

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

soan

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

Pièces jointes

  • test congés.xlsm
    25.1 KB · Affichages: 3
Dernière édition:
Haut Bas