XL 2016 Somme de plages sur différentes feuilles avec critères de dates

[mthlcrx]

Bonjour à tous,

j'ai un fichier pour gèrer le nombre d'heures réalisées mensuellement par des personnes. Il y a différentes feuilles : une feuille TBD pour avoir toutes les infos, une feuille pour le 1er mois, une feuille pour le 2ème mois, .... Les feuilles des mois sont générées par du code VBA.

Différentes périodes marquent le parcours de ces personnes : une période P1 et une période P2 qui ne sont généralement pas des mois entiers.

Je cherche donc à calculer le nombre d'heures par période pour chaque personne en fonction des dates des périodes.
Sur chaque feuille de mois, je calcule le total des heures mensuelles que je rapporte sur la feuille TBD.

Je vous donne un exemple sur deux mois mais la plupart des parcours sont sur 12 mois minimum.

Merci et bonne journée.

 

[Robert]

Bonjour consonnes, bonjour le forum,

En pièce jointe ton fichier modifié avec le code ci-dessous :

Private Sub CommandButton1_Click() 'bouton "Calcul P1 et P2
Dim OT As Worksheet 'déclare la variable OT (Onglet des Totaux)
Dim DP1 As Date 'déclare la variable DP1 (Début Période 1)
Dim FP1 As Date 'déclare la variable FP1 (Fin Période 1)
Dim DP2 As Date 'déclare la variable DP2 (Début Période 2)
Dim FP2 As Date 'déclare la variable FP2 (Fin Période 2)
Dim O As Worksheet 'déclare la variable O (Onglets)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim CP1(1 To 4) As Double 'déclare le tableau de 4 variables CP1 (Compteur Période 1)
Dim CP2(1 To 4) As Double 'déclare le tableau de 4 variables CP2 (Compteur Période 2)
Dim I As Byte 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (incrément)

ActiveCell.Select 'enlèce le focus au bouton
Set OT = Worksheets("TBD") 'définit l'onglet des totaux OT
DP1 = DateSerial(Year(OT.Range("C2").Value), Month(OT.Range("C2").Value), Day(OT.Range("C2").Value)) 'définit la date DP1
FP1 = DateSerial(Year(OT.Range("C3").Value), Month(OT.Range("C3").Value), Day(OT.Range("C3").Value)) 'définit la date DF1
DP2 = DateSerial(Year(OT.Range("C4").Value), Month(OT.Range("C4").Value), Day(OT.Range("C4").Value)) 'définit la date DP2
FP2 = DateSerial(Year(OT.Range("C5").Value), Month(OT.Range("C5").Value), Day(OT.Range("C5").Value)) 'définit la date DF2
For Each O In Worksheets 'boucle 1 : sur tous les onglet O du classeur
    If Not O.Name = "TBD" Then 'condition : si le nom de l'onglet n'est pas "TBD"
        TV = O.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
        For I = 4 To UBound(TV, 1) 'boucle 2 : sur toutes les lignes I de tableau des valeurs TV (en partant de la quatrième)
            For J = 2 To UBound(TV, 2) 'boucle 3 : sur toutes les colonnes J de tableau des valeurs TV (en partant de la seconde)
                'si la date ligne 3 colonne J de TV est comprise entre (ou égale) entre les date DP1 et DF1
                'incrémente le compteur CP1 de la ligne I - 3 (E1, E2 E3 ou E4)avec la valeur ligne I colonne J de TV
                If DP1 <= TV(3, J) And FP1 >= TV(3, J) Then CP1(I - 3) = CP1(I - 3) + TV(I, J)
                'si la date ligne 3 colonne J de TV est comprise entre (ou égale) entre les date DP2 et DF2
                'incrémente le compteur CP2 de la ligne I - 3 (E1, E2 E3 ou E4)avec la valeur ligne I colonne J de TV
                If DP2 <= TV(3, J) And FP2 >= TV(3, J) Then CP2(I - 3) = CP2(I - 3) + TV(I, J)
            Next J 'prochaine colonne de la boucle 3
        Next I 'prochaine ligne de la boucle 2
    End If 'fin de la condition
Next O 'prochain onglet de la boucle 1
OT.Range("C8").Resize(4, 1).Value = Application.Transpose(CP1) 'renvoie dans C8 redimensionnée le tableau CP1 transposé
OT.Range("D8").Resize(4, 1).Value = Application.Transpose(CP2) 'renvoie dans D8 redimensionnée le tableau CP2 transposé
End Sub

 

[Hasco]

Bonjour,

Voici une solution par power query. Vos Grilles mensuelles ont été nommées 'Janvier', 'Février' si vous nommer ainsi vos grilles la requête fonctionnera pour les mois futurs.

La période P2 va du 31/1 au 25/2, le 31/1 est donc pris en compte dans la requête alors que vos résultats n'en tiennent pas compte. Que faut-il faire ?



Cordialement

 

[mthlcrx]

Robert, merci pour ton code VBA. Je vais l'étudier et l'appliquer à mon modèle et reviens vers toi

Hasco, c'est effectivement une erreur de ma part dans le calcul "manuel" de ce que je voulais obtenir Pour Power Query, mes feuilles seront toujours nommés par le mois mais comme il peut y avoir des périodes sur 2 ans je précise l'année. Le format du nom de feuille est donc "mmmm aa". J'ai ouvert le fichier et regarder les requêtes mais je suis un newbie de PowerQuery.

Je remets le fichier avec les corrections, merci Hasco et Robert.

 

[Hasco]

Re,

Merci, mais je ne vois pas ce que vous voulez que je fasse

La seule chose, c'est que lorsque je parlais de nommer vos grilles, c'est plus les plages de cellules que les feuilles. (dans l'exemple, A2:AF7)
Si vos feuilles sont parfois sur 2 années alors, vous pouvez nommer les grilles : 'Mois_1" à "Mois_12" ou "Grille_1" à "Grille_12". Où Grille_1 pourrait débuter sur Novembre 2022 et Grille_12 finir sur Octobre 2023.

L'important pour power query c'est de les sélectionner dans l'ordre où vous voulez qu'elles soient.
Si vous voulez ultérieurement qu'apparaissent les noms des mois, on pourra les déduire facilement en fonction du numéro terminal du nom de la plage ou des dates quelle contient.

Cordialement

 

[mthlcrx]

Re,

Merci, mais je ne vois pas ce que vous voulez que je fasse

La seule chose, c'est que lorsque je parlais de nommer vos grilles, c'est plus les plages de cellules que les feuilles. (dans l'exemple, A2:AF7)
Si vos feuilles sont parfois sur 2 années alors, vous pouvez nommer les grilles : 'Mois_1" à "Mois_12" ou "Grille_1" à "Grille_12". Où Grille_1 pourrait débuter sur Novembre 2022 et Grille_12 finir sur Octobre 2023.

L'important pour power query c'est de les sélectionner dans l'ordre où vous voulez qu'elles soient.
Si vous voulez ultérieurement qu'apparaissent les noms des mois, on pourra les déduire facilement en fonction du numéro terminal du nom de la plage ou des dates quelle contient.

Cordialement

Rien faire de particulier, tu en as déjà fait beaucoup. Il faut que je me forme à PowerQuery car ça a l’air très puissant !
Merci pour l’explication des noms de plages.

 

[mthlcrx]

Robert : je travaille sur la macro VBA que tu m'as proposé. Seul hic, si le nombre d'heures est au format hh:mm cela ne fonctionne pas. J'ai testé avec une fonction format( ,"hh:mm") mais sans succès.
Une idée stp ?

 

[Robert]

Bonjour le fil, bonjour le forum,

Heu oui et une bonne ! Propose un un fichier avec l'exemple qui correspond. Ça nous évite de perdre du temps ! J'attends ton nouveau fichier pour regarder ça...

 

[mthlcrx]

Merci Robert.

J'ai apporté quelques modifications au fichier en y mettant des heures au format souhaité et en décalant les lignes et colonnes pour coller à mon modèle.

 

[Robert]

Bonjour le fil, bonjour le forum,

Prend pour habitude de fournir un fichier exemple identique dans son environnement à ton fichier original et, autre remarque, si les résultats que tu affiches ne sont pas exacts, autant ne pas les mettre. Ça nous enduit d'erreur et il nous faut des litres de dissolvant pour tout enlever...
Le code adapté et modifié :

Private Sub CommandButton1_Click() 'bouton "Calcul P1 et P2
Dim OT As Worksheet 'déclare la variable OT (Onglet des Totaux)
Dim DP1 As Date 'déclare la variable DP1 (Début Période 1)
Dim FP1 As Date 'déclare la variable FP1 (Fin Période 1)
Dim DP2 As Date 'déclare la variable DP2 (Début Période 2)
Dim FP2 As Date 'déclare la variable FP2 (Fin Période 2)
Dim O As Worksheet 'déclare la variable O (Onglets)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim CP1(1 To 4) As Variant 'déclare le tableau de 4 variables CP1 (Compteur Période 1)
Dim CP2(1 To 4) As Variant 'déclare le tableau de 4 variables CP2 (Compteur Période 2)
Dim I As Byte 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (incrément)

ActiveCell.Select 'enlèce le focus au bouton
Set OT = Worksheets("TBD") 'définit l'onglet des totaux OT
DP1 = DateSerial(Year(OT.Range("C2").Value), Month(OT.Range("C2").Value), Day(OT.Range("C2").Value)) 'définit la date DP1
FP1 = DateSerial(Year(OT.Range("C3").Value), Month(OT.Range("C3").Value), Day(OT.Range("C3").Value)) 'définit la date DF1
DP2 = DateSerial(Year(OT.Range("C4").Value), Month(OT.Range("C4").Value), Day(OT.Range("C4").Value)) 'définit la date DP2
FP2 = DateSerial(Year(OT.Range("C5").Value), Month(OT.Range("C5").Value), Day(OT.Range("C5").Value)) 'définit la date DF2
For Each O In Worksheets 'boucle 1 : sur tous les onglet O du classeur
    If Not O.Name = "TBD" Then 'condition : si le nom de l'onglet n'est pas "TBD"
        TV = O.Range(O.Cells(9, 6), O.Cells(16, O.Cells(9, 6).End(xlToRight).Column)) 'définit le tableau des valeurs TV
        For I = 5 To UBound(TV, 1) 'boucle 2 : sur toutes les lignes I de tableau des valeurs TV (en partant de la cinquième)
            For J = 1 To UBound(TV, 2) 'boucle 3 : sur toutes les colonnes J de tableau des valeurs TV
                'si la date ligne 1 colonne J de TV est comprise entre (ou égale) entre les date DP1 et DF1
                'incrémente le compteur CP1 de la ligne I - 4 (E1, E2 E3 ou E4) avec la valeur ligne I colonne J de TV
                If DP1 <= TV(1, J) And FP1 >= TV(1, J) Then CP1(I - 4) = CP1(I - 4) + TV(I, J)
                'si la date ligne 1 colonne J de TV est comprise entre (ou égale) entre les date DP2 et DF2
                'incrémente le compteur CP2 de la ligne I - 4 (E1, E2 E3 ou E4) avec la valeur ligne I colonne J de TV
                If DP2 <= TV(1, J) And FP2 >= TV(1, J) Then CP2(I - 4) = CP2(I - 4) + TV(I, J)
            Next J 'prochaine colonne de la boucle 3
        Next I 'prochaine ligne de la boucle 2
    End If 'fin de la condition
Next O 'prochain onglet de la boucle 1
OT.Range("C8").Resize(4, 1).Value = Application.Transpose(CP1) 'renvoie dans C8 redimensionnée le tableau CP1 transposé
OT.Range("D8").Resize(4, 1).Value = Application.Transpose(CP2) 'renvoie dans D8 redimensionnée le tableau CP2 transposé
OT.Range("C8").Resize(4, 1).NumberFormat = "[h]:mm;@" 'mise au format
OT.Range("D8").Resize(4, 1).NumberFormat = "[h]:mm;@" 'mise au format
End Sub

 

[mthlcrx]

J'ai adapté ton code à mon fichier et ça marche au poil.
Merci.