[XL-2007] Remplissage de la charge mensuelle [Résolu]

VIPNO · 28/01/2012, 05h00

Bonjour,

J'ai une liste de personnes qui travaillent d'une date de début à une date de fin avec un certain pourcentage. Je voudrais exprimer mois par mois le taux d'occupation de ces personnes par l'intermédiaire d'un programme car le faire en direct crée des formules trop longue.
Est-ce que quelqu'un a un début de piste ?
Merci d'avance
VIPNO

Daniel.C · 28/01/2012, 10h02

Bonjour,

Tu peux donner un exemple de résultat pour une personne ?

VIPNO · 28/01/2012, 10h42

Bonjour,
J'ai ajouté qq exemples de résultats souhaités.Il faudrait également gérer les jours fériés...ce que je n'ai pas fait dans les exemples.
Une fois ces résultats obtenus, il faudrait n'avoir qu'une ligne pour chaque entité des 5 premières colonnes identiques. Mais cela sera une deuxième étape.

Si qq'un a une idée en programmation. En direct dans les cellules il y aurait des formules trop grandes qui vivent mal qd je lance la macro de transposition qui remet à jour la partie gauche du tableau.
Merci d'avance
VIPNO

Daniel.C · 28/01/2012, 12h14

Bonjour,

Essaie la macro suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Sub Calculs()
    Dim c As Range, MoisDeb As Integer, Col As Integer
    With Sheets("Feuil2")
        .Range(.[A3], .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 10).Resize(, 12).ClearContents
        For Each c In .Range(.[F3], .Cells(.Rows.Count, 6).End(xlUp))
            If Year(c.Offset(, 2)) = 2012 Then
                If Year(c.Offset(, 1)) < 2012 Then
                    MoisDeb = 1
                Else
                    MoisDeb = Month(c.Offset(, 1))
                End If
                For Col = 11 To 22
                    Select Case MoisDeb
                        Case Col - 10
                            If Month(c.Offset(, 2)) = Col - 10 Then
                                .Cells(c.Row, Col) = Application.NetworkDays(c.Offset(, 1), c.Offset(, 2)) * c.Offset(, 3) / 100
                            Else
                                .Cells(c.Row, Col) = Application.NetworkDays(c.Offset(, 1), DateSerial(2012, Col - 9, 0)) * c.Offset(, 3) / 100
                            End If
                        Case Is < Col - 10
                            If Month(c.Offset(, 2)) = Col - 10 Then
                               .Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), c.Offset(, 2)) * c.Offset(, 3) / 100
                            ElseIf Month(c.Offset(, 2)) > Col - 10 Then
                               .Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateSerial(2012, Col - 9, 0)) * c.Offset(, 3) / 100
                            End If
                    End Select
                Next Col
            End If
        Next c
    End With
End Sub

VIPNO · 28/01/2012, 22h07

Bonjour,

Le prog proposé convient parfaitement. Je voudrais maintenant remplir la colonne 10 avec simplement le nombre de jours ouvrés * % si les dates sont dans la bonne année.
Je ne sais pas trop comment faire ni où ajouter ce code dans le programme.
Est-il également possible de tenir compte des jours fériés français en les incorporant dans ce prog ou à un autre endroit sur la feuille par exemple.
D'avance merci pour l'aide apporté
VIPNO

Bonjour,
Si la date de fin est >2012, le prog ne sort rien même si la date de début est <=2012.
J'ai essayé de rajouté un "si" sur la date de fin mais je n'arrive pas à sortir qq chose de concluant.
Auriez-vous me donner une piste.
D'avance merci
VIPNO

Daniel.C · 29/01/2012, 11h50

Oui. Il n'y avait pas de date 2013 dans ton classeur. Essaie :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Sub Calculs()
    Dim c As Range, MoisDeb As Integer, Col As Integer, MoisFin As Integer
    Dim DateDeb As Date, DateFin As Date
    With Sheets("Feuil2")
        .Range(.[A3], .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 10).Resize(, 12).ClearContents
        For Each c In Range(.[F3], .Cells(.Rows.Count, 6).End(xlUp))
            If Year(c.Offset(, 2)) >= 2012 Then
                If Year(c.Offset(, 1)) < 2012 Then
                    MoisDeb = 1
                    DateDeb = DateSerial(2012, 1, 1)
                Else
                    MoisDeb = Month(c.Offset(, 1))
                    DateDeb = c.Offset(, 1)
                End If
                If Year(c.Offset(, 2)) > 2012 Then
                    MoisFin = 13
                    DateFin = DateSerial(2012, 12, 31)
                Else
                    MoisFin = Month(c.Offset(, 2))
                    DateFin = c.Offset(, 2)
                End If
                For Col = 11 To 22
                    Select Case MoisDeb
                        Case Col - 10
                            If MoisFin = Col - 10 Then
                                .Cells(c.Row, Col) = Application.NetworkDays(DateDeb, DateFin) * c.Offset(, 3) / 100
                            Else
                                .Cells(c.Row, Col) = Application.NetworkDays(DateDeb, DateSerial(2012, Col - 9, 0)) * c.Offset(, 3) / 100
                            End If
                        Case Is < Col - 10
                            If MoisFin = Col - 10 Then
                               .Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateFin) * c.Offset(, 3) / 100
                            ElseIf MoisFin > Col - 10 Then
                               .Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateSerial(2012, Col - 9, 0)) * c.Offset(, 3) / 100
                            End If
                    End Select
                Next Col
            End If
        Next c
    End With
End Sub

VIPNO · 29/01/2012, 15h45

Bonjour,
Cela fonctionne bien maintenant, merci. Il n'y avait pas de 2013 car nous sommes en début 2012. Il y en aura des septembre.
J'ai ajouté le traitement de la colonne 10 pour le nombre de jours fériés total.
J'aimerai maintenant tenir compte des jours fériés du calendrier français.

Est-ce que que vous pourriez m'aider ?
Merci d'avance
VIPNO

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
Sub Repartition_Annee()
    Dim c As Range, MoisDeb As Integer, MoisFin As Integer, Col As Integer, NbJO As Double
    Dim DateDeb As Date, DateFin As Date
    With Sheets("Feuil2")
    'efface la plage de 12 cellules dans la ligne qui commence en A3 jusqu'à la fin, hors mis les 9 premieres,
        .Range(.[A3], .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 9).Resize(, 12).ClearContents
                             ' pour chaque c dans plage qui commence en F3 et sur la 6° colonne jusqu'à la fin
        For Each c In .Range(.[F3], .Cells(.Rows.Count, 6).End(xlUp))
                            ' si l'année (fin) dans la 8° colonne (6°+2) est >=2012
           If Year(c.Offset(, 2)) >= 2012 Then
                                ' Si l'année (début) dans la 7° colonne est < à 2012
                If Year(c.Offset(, 1)) < 2012 Then
                                 'alors MoisDeb = 1 et date de debut est le 1° janvier 2012
                    MoisDeb = 1
                    DateDeb = DateSerial(2012, 1, 1)
                Else
                                 ' sinon MoiDeb et DateDeb de la 7° colonne
                    MoisDeb = Month(c.Offset(, 1))
                    DateDeb = c.Offset(, 1)
                End If
                If Year(c.Offset(, 2)) > 2012 Then
                    MoisFin = 13
                    DateFin = DateSerial(2012, 12, 31)
                Else
                    MoisFin = Month(c.Offset(, 2))
                    DateFin = c.Offset(, 2)
                End If
                    'Calcul du nombre total de jours ouvrés de l'année
                     .Cells(c.Row, 10) = Application.NetworkDays(DateDeb, DateFin) * c.Offset(, 3) / 100
                     'Pour la varable col de 11 à 22
                     For Col = 11 To 22
                    Select Case MoisDeb
                        Case Col - 10
                            If MoisFin = Col - 10 Then
                                .Cells(c.Row, Col) = Application.NetworkDays(DateDeb, DateFin) * c.Offset(, 3) / 100
                            Else
                                .Cells(c.Row, Col) = Application.NetworkDays(DateDeb, DateSerial(2012, Col - 9, 0)) * c.Offset(, 3) / 100
                            End If
                        Case Is < Col - 10
                            If MoisFin = Col - 10 Then
                               .Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateFin) * c.Offset(, 3) / 100
                            ElseIf MoisFin > Col - 10 Then
                               .Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateSerial(2012, Col - 9, 0)) * c.Offset(, 3) / 100
                            End If
                    End Select
                Next Col
            End If
        Next c
    End With
End Sub

Daniel.C · 29/01/2012, 16h10

Citation:

J'aimerai maintenant tenir compte des jours fériés du calendrier français.

Tu dois définir une plage contenant les jours fériés (1 cellule par date). Si tu appelles cette plage "Fériés" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Sub Calculs()
    Dim c As Range, MoisDeb As Integer, Col As Integer, MoisFin As Integer
    Dim DateDeb As Date, DateFin As Date
    With Sheets("Feuil2")
        .Range(.[A3], .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 10).Resize(, 12).ClearContents
        For Each c In Range(.[F3], .Cells(.Rows.Count, 6).End(xlUp))
            If Year(c.Offset(, 2)) >= 2012 Then
                If Year(c.Offset(, 1)) < 2012 Then
                    MoisDeb = 1
                    DateDeb = DateSerial(2012, 1, 1)
                Else
                    MoisDeb = Month(c.Offset(, 1))
                    DateDeb = c.Offset(, 1)
                End If
                If Year(c.Offset(, 2)) > 2012 Then
                    MoisFin = 13
                    DateFin = DateSerial(2012, 12, 31)
                Else
                    MoisFin = Month(c.Offset(, 2))
                    DateFin = c.Offset(, 2)
                End If
                For Col = 11 To 22
                    Select Case MoisDeb
                        Case Col - 10
                            If MoisFin = Col - 10 Then
                                .Cells(c.Row, Col) = Application.NetworkDays(DateDeb, DateFin, [Fériés]) * c.Offset(, 3) / 100
                            Else
                                .Cells(c.Row, Col) = Application.NetworkDays(DateDeb, DateSerial(2012, Col - 9, 0), [Fériés]) * c.Offset(, 3) / 100
                            End If
                        Case Is < Col - 10
                            If MoisFin = Col - 10 Then
                               .Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateFin, [Fériés]) * c.Offset(, 3) / 100
                            ElseIf MoisFin > Col - 10 Then
                               .Cells(c.Row, Col) = Application.NetworkDays(DateSerial(2012, Col - 10, 1), DateSerial(2012, Col - 9, 0), _
                               [Fériés]) * c.Offset(, 3) / 100
                            End If
                    End Select
                Next Col
            End If
        Next c
    End With
End Sub

VIPNO · 29/01/2012, 21h34

Bonjour
Merci beaucoup, cela répond exactement à ce que je voulais.
Je réfléchis maintenant à la feuille de synthese ...
Cordialement,
VIPNO

29/01/2012, 21h34	#9
VIPNO Candidat au titre de Membre du Club Ingénieur Support BE Inscription : décembre 2011 Messages : 66 Détails du profil Informations professionnelles : Activité : Ingénieur Support BE Informations forums : Inscription : décembre 2011 Messages : 66 Points : 12 Points : 12	Parfait Bonjour Merci beaucoup, cela répond exactement à ce que je voulais. Je réfléchis maintenant à la feuille de synthese ... Cordialement, VIPNO
	00

28/01/2012, 22h07	#5
VIPNO Candidat au titre de Membre du Club Ingénieur Support BE Inscription : décembre 2011 Messages : 66 Détails du profil Informations professionnelles : Activité : Ingénieur Support BE Informations forums : Inscription : décembre 2011 Messages : 66 Points : 12 Points : 12	Bonjour, Le prog proposé convient parfaitement. Je voudrais maintenant remplir la colonne 10 avec simplement le nombre de jours ouvrés * % si les dates sont dans la bonne année. Je ne sais pas trop comment faire ni où ajouter ce code dans le programme. Est-il également possible de tenir compte des jours fériés français en les incorporant dans ce prog ou à un autre endroit sur la feuille par exemple. D'avance merci pour l'aide apporté VIPNO Bonjour, Si la date de fin est >2012, le prog ne sort rien même si la date de début est <=2012. J'ai essayé de rajouté un "si" sur la date de fin mais je n'arrive pas à sortir qq chose de concluant. Auriez-vous me donner une piste. D'avance merci VIPNO
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email