XL 2016 Additionner des heures - multi-critères

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion halecs93
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
halecs93

halecs93

XLDnaute Impliqué
Bonjour,

Je cherche à additionner des heures selon plusieurs critères :

Le contenu de L2 et une période définie par un formulaire.

Une âme charitable, ou plusieurs même 😉, aurait une solution ?

Un grand grand merci.

1744203954234.png
 

Pièces jointes

Solution
job75
Bonjour halecs93, le forum,

Pour renseigner les cellules J3, J34... et K3 il suffit d'utiliser la fonction SOMME.SI.

J'ai aussi revu en plus simple la macro Worksheet_Change :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, s
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next 'si les heures ne sont pas correctes
    For Each c In [C2:G152]
        s = Split(c.Text, "-")
        If UBound(s) = 1 Then c(2) = "": c(2) = CDate(s(1)) - CDate(s(0)): If c(2) < 0 Then c(2) = CDate(s(1)) + 1 - CDate(s(0))
    Next c
    Application.EnableEvents = True
    If Not Intersect([L2], Target) Is Nothing And [L2] <> "" Then UserForm1.Show
End Sub
A+
Trier par date Trier par votes
TooFatBoy à dit:
Hi,


Euh... et qu'y sont lesdits critères ??? 🤔

"contenu de L2"... soit, mais y a-t-il une fourchette de valeurs qui implique quelque chose dans l'addition à effectuer, etc.
Cliquez pour agrandir...
Bonjour

Simplement, si je retrouve le même contenu que L2 dans le tableau.... et entre les deux dates définies par le formulaire, alors je prends la valeur au dessus, qui apparait entre parenthèses), et ainsi de suite jusqu'à la dernière date définie .

Par exemple, L2 apparait plusieurs fois entre le 3/3/2025 et le 31/3/2025.... je peux facilement additionner les heures qui y sont associées.... mais je veux aussi pouvoir définir la même chose entre le 7/3/2025 et le 15/03205....
 
Vote positif 0 Vote négatif
Bonjour halecs93, TooFatBoy,

Le code de l'UserForm :
VB: 
Private Sub CommandButton1_Click()
    Dim ref$, date1 As Date, date2 As Date, i&, j%, k&, resu#
    ref = [L2]
    If Not IsDate(TextBox1) Then TextBox1 = "": TextBox1.SetFocus: Exit Sub
    If Not IsDate(TextBox2) Then TextBox2 = "": TextBox2.SetFocus: Exit Sub
    date1 = CLng(Application.Min(CDate(TextBox1), CDate(TextBox2)))
    date2 = CLng(Application.Max(CDate(TextBox1), CDate(TextBox2)))
    For i = 1 To 125 Step 31
        For j = 3 To 7
            If Cells(i, j) >= date1 And Cells(i, j) <= date2 Then
                For k = i + 3 To i + 29
                    If Cells(k, j) = ref Then
                        If IsNumeric(Cells(k - 1, j)) Then resu = resu + CDbl(Cells(k - 1, j))
                    End If
                Next k
            End If
    Next j, i
    [L3] = resu
End Sub
   
Private Sub UserForm_Initialize()
If [L2] = "" Then [L3] = "": End 'l'UserForm ne s'ouvre pas
End Sub
Il ne faut pas modifier la position des lignes et colonnes du tableau.

A+
 

Pièces jointes

Vote positif 0 Vote négatif
job75 à dit:
Bonjour halecs93, TooFatBoy,

Le code de l'UserForm :
VB: 
Private Sub CommandButton1_Click()
    Dim ref$, date1 As Date, date2 As Date, i&, j%, k&, resu#
    ref = [L2]
    If Not IsDate(TextBox1) Then TextBox1 = "": TextBox1.SetFocus: Exit Sub
    If Not IsDate(TextBox2) Then TextBox2 = "": TextBox2.SetFocus: Exit Sub
    date1 = CLng(Application.Min(CDate(TextBox1), CDate(TextBox2)))
    date2 = CLng(Application.Max(CDate(TextBox1), CDate(TextBox2)))
    For i = 1 To 125 Step 31
        For j = 3 To 7
            If Cells(i, j) >= date1 And Cells(i, j) <= date2 Then
                For k = i + 3 To i + 29
                    If Cells(k, j) = ref Then
                        If IsNumeric(Cells(k - 1, j)) Then resu = resu + CDbl(Cells(k - 1, j))
                    End If
                Next k
            End If
    Next j, i
    [L3] = resu
End Sub
  
Private Sub UserForm_Initialize()
If [L2] = "" Then [L3] = "": End 'l'UserForm ne s'ouvre pas
End Sub
Il ne faut pas modifier la position des lignes et colonnes du tableau.

A+
Cliquez pour agrandir...
Que répondre....? Waou....? Je le fais. Merci !!!!
 
Vote positif 0 Vote négatif
job75 à dit:
Bonjour halecs93, TooFatBoy,

Le code de l'UserForm :
VB: 
Private Sub CommandButton1_Click()
    Dim ref$, date1 As Date, date2 As Date, i&, j%, k&, resu#
    ref = [L2]
    If Not IsDate(TextBox1) Then TextBox1 = "": TextBox1.SetFocus: Exit Sub
    If Not IsDate(TextBox2) Then TextBox2 = "": TextBox2.SetFocus: Exit Sub
    date1 = CLng(Application.Min(CDate(TextBox1), CDate(TextBox2)))
    date2 = CLng(Application.Max(CDate(TextBox1), CDate(TextBox2)))
    For i = 1 To 125 Step 31
        For j = 3 To 7
            If Cells(i, j) >= date1 And Cells(i, j) <= date2 Then
                For k = i + 3 To i + 29
                    If Cells(k, j) = ref Then
                        If IsNumeric(Cells(k - 1, j)) Then resu = resu + CDbl(Cells(k - 1, j))
                    End If
                Next k
            End If
    Next j, i
    [L3] = resu
End Sub
  
Private Sub UserForm_Initialize()
If [L2] = "" Then [L3] = "": End 'l'UserForm ne s'ouvre pas
End Sub
Il ne faut pas modifier la position des lignes et colonnes du tableau.

A+
Cliquez pour agrandir...
Encore un grand merci.... quelques lignes et ça semble tout simple.... peut-être serait-il possible de choisir le lieu directement dans le formulaire ?
 
Vote positif 0 Vote négatif
Bonjour halecs93, le forum,

Pour renseigner les cellules J3, J34... et K3 il suffit d'utiliser la fonction SOMME.SI.

J'ai aussi revu en plus simple la macro Worksheet_Change :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, s
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next 'si les heures ne sont pas correctes
    For Each c In [C2:G152]
        s = Split(c.Text, "-")
        If UBound(s) = 1 Then c(2) = "": c(2) = CDate(s(1)) - CDate(s(0)): If c(2) < 0 Then c(2) = CDate(s(1)) + 1 - CDate(s(0))
    Next c
    Application.EnableEvents = True
    If Not Intersect([L2], Target) Is Nothing And [L2] <> "" Then UserForm1.Show
End Sub
A+
 

Pièces jointes

Vote positif 0 Vote négatif
Solution
job75 à dit:
Bonjour halecs93, le forum,

Pour renseigner les cellules J3, J34... et K3 il suffit d'utiliser la fonction SOMME.SI.

J'ai aussi revu en plus simple la macro Worksheet_Change :
VB: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, s
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next 'si les heures ne sont pas correctes
    For Each c In [C2:G152]
        s = Split(c.Text, "-")
        If UBound(s) = 1 Then c(2) = "": c(2) = CDate(s(1)) - CDate(s(0)): If c(2) < 0 Then c(2) = CDate(s(1)) + 1 - CDate(s(0))
    Next c
    Application.EnableEvents = True
    If Not Intersect([L2], Target) Is Nothing And [L2] <> "" Then UserForm1.Show
End Sub
A+
Cliquez pour agrandir...
Hello.... et encore merci pour cette fulgurante amélioration.
 
Vote positif 0 Vote négatif
Bonjour halecs93, le forum,

Avez vous bien compris pourquoi j'utilise s = Split(c.Text, "-") et non pas s = Split(c.Value, "-") ?

Parce qu'en C3 par exemple c.Value a la valeur 7,29166666666667E-02 et que donc cette cellule serait traitée.

.Text renvoie ce qui est affiché.

A+
 
Vote positif 0 Vote négatif
En fait on peut se passer de VBA et utiliser uniquement des formules.

Formule en C3 à copier vers la droite et dans les lignes concernées :
Code: 
=SIERREUR(SUPPRESPACE(STXT(C2;TROUVE("-";C2)+1;9))-GAUCHE(C2;TROUVE("-";C2)-1);"")
Formule en M3, assez difficile à bien comprendre :
Code: 
=SOMMEPROD((Date1>=L2)*(Date1<=L3)*(C4:G30=M2);C3:G29)+SOMMEPROD((Date2>=L2)*(Date2<=L3)*(C35:G61=M2);C34:G60)+SOMMEPROD((Date3>=L2)*(Date3<=L3)*(C66:G92=M2);C65:G91)+SOMMEPROD((Date4>=L2)*(Date4<=L3)*(C97:G123=M2);C96:G122)+SOMMEPROD((Date5>=L2)*(Date5<=L3)*(C128:G154=M2);C127:G153)
Voyez les noms définis Date1 Date2 Date3 Date4 Date5.

Les cellules contenant des formules sont verrouillées et la feuille est protégée (sans mot de passe).

Bonne nuit.
 

Pièces jointes

Vote positif 0 Vote négatif
Bonjour halecs93, JM, le forum,

Je découvre que Date1... Date5 ne sont pas du tout nécessaires, formule en M3 :
Code: 
=SOMMEPROD((C1:G1>=L2)*(C1:G1<=L3)*(C4:G30=M2);C3:G29)+SOMMEPROD((C32:G32>=L2)*(C32:G32<=L3)*(C35:G61=M2);C34:G60)+SOMMEPROD((C63:G63>=L2)*(C63:G63<=L3)*(C66:G92=M2);C65:G91)+SOMMEPROD((C94:G94>=L2)*(C94:G94<=L3)*(C97:G123=M2);C96:G122)+SOMMEPROD((C125:G125>=L2)*(C125:G125<=L3)*(C128:G154=M2);C127:G153)
Je croyais que dans SOMMEPROD les plages devaient toujours avoir mêmes dimensions, on voit ici que ce n'est pas vrai..

A+
 

Pièces jointes

Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

3xceln4ute
  • Résolu(e)
Microsoft 365 Calculer une date projetée évolutive selon des heures cumulées (période d’essai)
Réponses
4
Affichages
75
3xceln4ute
3xceln4ute
J
  • Résolu(e)
XL 2016 Verrouillage cellule automatique
Réponses
6
Affichages
53
jcoillot
J
V
  • Question Question
XL 2019 Compter heures suivant date et nom
Réponses
7
Affichages
492
Valky68
Valky68
J
  • Résolu(e)
Microsoft 365 Impression groupée de plusieurs feuilles dans plusieurs onglets
2
Réponses
15
Affichages
343
Phil69970
Phil69970
D
  • Question Question
Microsoft 365 Cumul des heures supplémentaires sur plusieurs onglets
Réponses
6
Affichages
323
alexga78
A
A
  • Question Question
XL 2016 Obtenir un NB SI ENS mais avec un filtre
Réponses
12
Affichages
192
AdrienExcel13
A
D
  • Résolu(e)
Microsoft 365 Filtre avancé VBA multi-critères
Réponses
14
Affichages
539
DELHOMME
D
A
  • Résolu(e)
Microsoft 365 VBA_Copier coller selon des critères dans deux menus déroulants
Réponses
6
Affichages
113
Altfkat
A
G
  • Question Question
XL 2019 additionner des cellules en fonction
Réponses
7
Affichages
248
JHA
J
P
  • Question Question
Microsoft 365 Problème avec une listview
Réponses
3
Affichages
92
Pat54
P
D
  • Question Question
Microsoft 365 Différence entre la valeur trouvée et 7
Réponses
10
Affichages
219
Lu76Fer
Lu76Fer
F
  • Résolu(e)
XL 2021 VBA et Power Query : ecrire des requetes avec variables
2
Réponses
28
Affichages
2 K
Florent74
F
H
  • Résolu(e)
XL 2016 Trouver le poste en fonction des heures
Réponses
3
Affichages
66
hutch57
H
Chubby
  • Question Question
Microsoft 365 Formules de recherche deux critères
Réponses
6
Affichages
126
Chubby
Chubby
S
  • Question Question
Microsoft 365 Ventiler des données sur une année (sans TCD)
2
Réponses
25
Affichages
399
cathodique
cathodique
M
  • Résolu(e)
Autres calcul de présence
Réponses
3
Affichages
124
micke32
M
Escargot04
  • Question Question
Microsoft 365 Mise en frome conditionnelle de lignes par dates différentes
2
Réponses
19
Affichages
237
TooFatBoy
TooFatBoy
C
Répartition paramétrable en Groupes Homogènes Multi-Critères
Réponses
0
Affichages
247
crocrocro
C
jeff1494
  • Question Question
XL 2021 Aide pour recherche intuitive
2
Réponses
16
Affichages
833
jeff1494
jeff1494
P
  • Question Question
Microsoft 365 Calculer heures supplémentaires automatiquement selon pointage
Réponses
2
Affichages
260
Po-p0/59
P
Retour