Discussion :

[Blord]

Bonjour à tous,

Je fait appel à vos compétences pour m'aider à résoudre un problème auquel je n'ai pas trouvé de solution.

Le fichier suivant sert à calculer les besoins en personnel d'un centre d'appel pour chaque période de 15 minutes de l'horaire de travail.

Feuille COM : Matrice qui indique avec la valeur 1 si l'agent possède une compétence.

Feuille LUN (pour lundi) : Matrice qui indique avec la valeur 1 si l'agent est disponible pour prendre des appels pour chaque période de 15 minutes.

Feuille Matrice LUN : Feuille sur laquelle je dois calculer par compétence et pour chaque période de 15 minutes le nombre d'agents disponibles.

C'est ici que ça bloque, si je met des formules SOMMEPROD, le temps de calculs devient très long, si j'utilise un SOMMESI, le temps de calcul est moins long mais quand même innaceptable pour un fichier qui sera modifié très fréquemment.

Ça fait 5700 cellules à calculer et uniquement pour le lundi, je dois répéter l'opération pour tous les jours de la semaine (sauf samedi et dimanche).

Une fois les feuilles Matrice LUN… Matrice VEN calculées, elles seront comparées à des feuilles qui contiennent les cibles à atteindre.

Je me demandais si par une macro savamment programmée, on ne pourrait pas arriver à obtenir les résultats souhaités de façon très rapide. (Note : Les 4 chiffres affichés dans la feuille Matrice LUN sont exacts). De plus, j'ai repris les deux premières plages horaires avec les 2 formules pour démontrer que les résultats sont identiques...

Merci d'avance pour toutes pistes de solutions proposées...

Benoit Lord

[oOVaveOo]

Tu peux essayer de passer par msquery, en SQL.

[neupont]

C'est bizarre, moi je trouve ça quasi instantané.
Qu'est-ce que tu entends par calculs très longs ? C'est quand tu "tires" les formules sur les cellules adjacentes ? C'est le temps qu'il faut pour qu'une modif dans une des deux autres feuilles soit reportée ?

[Blord]

Bonjour,

Ce qui est long c'est de regénérer les feuilles que j'apelle Matrice LUN, Matrice MAR, Matrice MER, Matrice JEU, Matrice VEN

Dans mon fichier, il n'y a que la feuille Matrice LUN pour alléger le tout...

Sur ces feuilles, pour obtenir les résultats désirés, il y a 5700 formules par feuille pour 5 feuilles ce qui donne 28500 formules à recalculer à chaque fois que l'on modifiera l'horaire de travail d'un agent ou sa liste de compétences.

C'est surtout la modification de l'horaire qui posent problème car il y aura beaucoup, beaucoup de modifications de faites, par exemple, une heure de pause est modifiée, une heure de repas, un ou plusieurs agents qui sont absents ou qui s'absentent...

Le calcul doit être pratiquement instantané parce qu'il s'agit d'un tableau de contrôle, donc on modifiera l'horaire pour voir si on est toujours correct pour toutes les plages horaires...

Il est certain que sur des ordinateurs puissants, le temps de calculs est diminué, mais je ne peux me fier à ça pour déployer une application. De mon côté sur mon ordinateur, j'ai fait des tests et une modification prend environ 80 secondes à s'exécuter avec toutes les formules... Si l'utilisateur doit attendre aussi longtemps pour prendre des décisions à chaque modification, ça ne sera pas convenable...

Merci de vous pencher sur mon problème...

Pour ce qui est de msQuerry en SQL, je n'ai jamais travaillé avec ni SQL et MSQUERRY....


Benoit Lord

[neupont]

Pourrais-tu envoyer le fichier complet, juste pour tests ?

[Blord]

Pourrais-tu envoyer le fichier complet, juste pour tests ?

Je veux bien envoyer le fichier mais même compressé il fait 734K..... De qu'elle façon puis-je l'envoyer ?

Merci !

Benoit Lord

[decondelite]

734k ça passe sur le forum.

[Blord]

Bonjour,

Désolé, mais le "Forum" ne me laisse pas joindre un fichier de 734 Ko...

[neupont]

Bon, je me suis amusé...
Néanmoins faute d'avoir pu faire des tests, je ne suis pas absolument sûr que tu y gagneras...

Le but est de remplacer les formules des feuilles Matrice XXX par des valeurs calculées sur changement des cellules des feuilles COM et de jour (LUN, MAR, etc...)

D'où moins de calculs, mais en même temps il s'agit de boucles VB non compilées moins rapides que les formules Excel.

Donc bref :
Mettre le code ci-dessous dans un module

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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
Sub Calculs(varLigne As Long, varCol As Integer, varSheet As String)
 
Application.ScreenUpdating = False
 
'cas où la modif est sur la feuille COM :
If varSheet = "COM" Then
    'dans ce cas il faut reporter la modif sur toutes les feuilles Matrice XXX, ligne de la compétence modifiée
    For Each s1 In ThisWorkbook.Sheets 's1 est une feuille "Matrice XXX"
        If Left(s1.Name, 7) = "Matrice" Then
            For Each s2 In ThisWorkbook.Sheets 's2 est la feuille du jour correspondant à la matrice en cours de traitement
                'on recherche la feuille de présence du jour concernant la matrice en cours de traitement
                If InStr(1, s1.Name, s2.Name, 1) > 0 And s1.Name <> s2.Name Then
                    a = 2
                    Do While s1.Cells(1, a) <> "" 'pour chaque cellule de la feuille "matrice" en cours correspondant à la ligne de la compétence touchée...
                        decompte = 0
                        b = 2
                        Do While Sheets("COM").Cells(1, b) <> "" 'pour chaque agent de la compétence visée
 
                            decompte = decompte + Sheets("COM").Cells(varLigne, b) * s2.Cells(a, b)
 
                            b = b + 1
                        Loop
 
                        s1.Cells(varLigne, a) = decompte
                        a = a + 1
                    Loop
                End If
            Next s2
        End If
    Next s1
 
Else 'cas où la modif est une autre feuille (une feuille de jour) :
    'il faut chercher la feuille de matrice qui correspond à la feuille jour modifiée
    For Each s1 In ThisWorkbook.Sheets 's1 est une feuille "Matrice XXX"
        If Left(s1.Name, 7) = "Matrice" And InStr(1, s1.Name, varSheet, 1) > 0 Then
            Set s2 = Sheets(varSheet) 's2 est la feuille du jour modifiée
            a = 2 'a = ligne en cours des feuilles Matrice XXX et COM
            Do While s1.Cells(a, 1) <> ""
                decompte = 0
                b = 2 'b = colonne en cours de la feuille de jour modifiée
                Do While s2.Cells(1, b) <> ""
 
                    decompte = decompte + Sheets("COM").Cells(a, b) * s2.Cells(varLigne, b)
 
                    b = b + 1
                Loop
                s1.Cells(a, varLigne) = decompte
                a = a + 1
            Loop
        End If
    Next s1
End If
 
Application.ScreenUpdating = True
 
End Sub
 
Public Sub Initialisation()
On Error Resume Next
'provoque un changement dans les feuilles concernées pour initialiser les feuilles Matrice XXX
For Each s1 In ThisWorkbook.Sheets
    If Left(s1.Name, 7) <> "Matrice" Then
        s1.Activate
        a = 2
        Do While Cells(a, 1) <> ""
            valeur = CInt(Cells(a, 2))
 
            If valeur = 0 Then Cells(a, 2) = 1
            If valeur = 1 Then Cells(a, 2) = 0
            DoEvents
            DoEvents
 
            Cells(a, 2) = valeur
            DoEvents
            DoEvents
 
            a = a + 1
        Loop
    End If
Next s1
End Sub

Et mettre le code ci-dessous dans toutes les feuilles susceptibles de déclencher les calculs (COM, LUN, MAR... mais pas les Matrice XXX).

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
 
Private Sub Worksheet_Change(ByVal Target As Range)
'Cette procédure intercepte tout changement manuel ou macro dans la feuille (sauf les chgt par formule)...
 
On Error Resume Next
 
'Déclaration des variables :
Dim varCol As Integer, varLigne As Long, varSheet As String
 
 
For Each c In Target
    'on affecte le n° de colonne et le n° de ligne de la cellule modifiée aux variables :
    varCol = c.Column
    varLigne = c.Row
    varSheet = ActiveSheet.Name
 
    'on se fiche d'un changement sur la 1ère ligne ou la 1ère colonne
    If (varCol > 1 And varLigne > 1) Then
        If c.Value <> 0 And c.Value <> 1 Then
            MsgBox "La valeur saisie doit être 1 ou 0 !", vbOKOnly, "Erreur"
            c.Value = 0
            c.Select
            End
        End If
 
        Call Calculs(varLigne, varCol, varSheet)
    End If
 
Next c
 
End Sub

1 - Commence par faire une copie de ton classeur.
2 - Vide toutes les feuilles Matrice XXX de leurs formules (indispensable !).
3 - comme les calculs se déclenchent sur l'événement Change, il faut modifier au moins une fois une colonne entière de chaque feuille : lance la macro Initialisation qui le fera automatiquement (cette macro sera inutile ensuite) => ça peut être long, plusieurs minutes au moins (et beaucoup plus si tu n'as pas viré les formules !).

Je ne prétends pas que c'est du code optimisé, hein, et il y a peut-être d'autres manières de faire plus rapides ou/et plus simples.


Et si ça marche du premier coup je me fais moine

[Blord]

Bonjour,

Merci beaucoup pour le code, je vais assurément le tester dans les prochains jours selon ce que j'aurai comme urgences sur mon bureau. Je donne des nouvelles dès que possible...

Encore une fois, merci beaucoup !

Benoit Lord