Discussion :

[ClaireD]

Bonjour à toutes et à tous,

Je travaille aujourd'hui pour une société où l'on a besoin de faire des contrôles de paramétrages de commandes. Après une extraction de tous les paramètres de mes commandes, je viens faire plusieurs tests et j'utilise la formule SOMMEPROD pour les réaliser. Malheureusement, j'ai cette formule sur 3 colonnes et 36000 lignes (nombre qui croît par 300 environ chaque semaine). Le fichier est lourd et le temps de calcul est variable entre 30 minutes et 1 heure (suivant si je l’exécute sur le serveur ou en local. Idéalement il devrait être sur un serveur). Comment puis-je remplacer cette formule par un code VBA ? J’imagine avec une boucle pour n’avoir que quelques lignes de code. Voici ma formule type pour chaque colonne qui est répétée sur aujourd’hui 36690 lignes mais demain il y en aura de plus en plus (j’ai donc une plage variable) :
Première colonne : =SOMMEPROD(($A$3:$A$299689=$AG$2)*($B$3:$B$299689=$B3)*($N$3:$N$299689))/2 J’ai mis volontairement le bas des colonnes à 299689 car le nombre de ligne va tout le temps augmenter.
Deuxième colonne : =SOMMEPROD(($A$3:$A$299689=$AH$2)*($B$3:$B$299689=$B3)*($N$3:$N$299689))/2
Troisième colonne : =SOMMEPROD(($A$3:$A$299689=$AI$2)*($B$3:$B$299689=$B3)*($N$3:$N$299689))/2

D'autre part, pouvez-vous me dire si le fait de tout mettre en VBA améliorera la vitesse de calcul du fichier ? Je pense à toutes mes autres colonnes qui suivent où il y a beaucoup de formules avec des SI.

D'avance merci,
Bonne journée
Claire

[Daniel.C]

Bonjour,

D'abord, c'est une erreur que de mettre une plage de 300000 cellules si tu n'as besoin que de 36000 cellules. Tu peux utiliser les plages nommées dynamiques pour te limiter à ce qui est utile. Par exemple, tu remplaces $A$3:$A$299689 par une plage nommée "PlageA" définie par :

Code :

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

=DECALER(Feuil1!$A$3;;;NBVAL(Feuil1!$A:$A)-2)

La seule contrainte est qu'il ne faut pas qu'il y ait de cellules vides à l'intérieur de la plage. Tu fais de même pour les autres colonnes. Essaie de remplacer les plages de tes formules par des plages nommées. Si, comme je le pense, le temps de recalcul est encore trop long, il faudra envisager un solution par macro. Mets alors un classeur de test - sans données confidentielles - en pièce jointe.

[ClaireD]

Bonjour Daniel,
Merci pour votre réponse rapide.. J'ai nommé mes plages dynamiques comme vous me l'avez dit mais rien n'y fait, le fichier est encore très lourd et prend toujours autant de temps pour tout calculer.
J'ai essayé de trouver la façon de coder moi-même mais je sèche... J'ai donc mis en pièce jointe une petite partie de mon fichier en enlevant les quelques colonnes (données confidentielles) donc si vous m'aidez pour le VBA, attention à ne pas coder avec des "déplacements de cellules" non nommées. Je suis pas sûr de me faire comprendre mais n'hésitez pas à revenir vers moi.
Pour vous expliquer le fichier rapidement, il y a 3 colonnes en orange qu'il faudrait que "j'allège".
Je vous remercie d'avance et vous souhaite une bonne soirée.
Cordialement
Claire

[Daniel.C]

Essaie cette macro.

Si c'est correct, on peut la déclencher à chaque modification de la colonne B ou des plages nommées.

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
Sub test1()
    Dim C As Range, Tabl, Plage As Range
    With Application
    .ScreenUpdating = False
    Set Plage = Range([AE3], Cells(Rows.Count, "AE").End(xlUp)).Resize(, 3)
    Tabl = .Transpose(.Transpose(Plage))
    For i = 1 To UBound(Tabl, 1)
        Tabl(i, 1) = Evaluate("sumproduct((" & [clé].Address & "=4)*(" & [document_HA].Address & "=B" & _
        i + 2 & ")*(" & [division].Address & "))/2")
        Tabl(i, 2) = Evaluate("sumproduct((" & [clé].Address & "=""    "")*(" & [document_HA].Address & "=B" & _
        i + 2 & ")*(" & [division].Address & "))/2")
        Tabl(i, 3) = Evaluate("sumproduct((" & [clé].Address & "=""ZSNC"")*(" & [document_HA].Address & "=B" & _
        i + 2 & ")*(" & [division].Address & "))/2")
    Next i
    Plage.Value = Tabl
    .ScreenUpdating = True
    End With
End Sub

[ClaireD]

Bonjour,
Merci d'avoir répondu à ma demande... Effectivement le code fonctionne bien mais il met quasiment autant de temps que si j'écris les formules dans les cellules. De plus, je ne comprends pas tout dans le code, pouvez-vous me mettre quelques commentaires en vert svp ? (j'aimerai surtout bien comprendre l'astuce de la boucle avec le "i" incrémenté que j'ai du mal à créer de moi-même mais qui je suis sûr nous facilite beaucoup de choses quand on sait la manier).

D'autre part, j'avais vu en parcourant les forums que quelqu'un utilisait un bout de code du style "Application.Match". Est-ce que cela vous dit quelque chose et peut-être est-ce plus intéressant et moins lourd que la programmation que vous m'avez donné ?

Dans tous les cas, déjà merci d'avoir pris le temps..
Cordialement

Claire

[Daniel.C]

Bonjour,

C'est clair que le code met presque autant de temps que le recalcul des formules. L'avantage est que tu n'as besoin de la macro qu'en cas d'ajout de lignes ou de modification dans les colonnes A, B ou M. Tu évites de recalculer si la valeur d'une formule se modifie ailleurs. Le code suivant, commenté doit être mis dans le module de la feuille. La macro se déclenchera automatiquement en cas de modification dans les colonnes A, B ou M.

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
' la macro se déclenche à chaque modification sur la feuille
Private Sub Worksheet_Change(ByVal Target As Range)
    ' si la modification concerne les colonnes A, B ou M
    If Target.Column < 3 Or Target.Column = 13 Then
        Dim C As Range, Tabl, Plage As Range
        With Application
        'on désactive les macros éventielles (celle-ci)
        .EnableEvents = False
        'on désactive les modifications d'écran
        .ScreenUpdating = False
        'définition de la plage à recalculer (colonnes AE, AF, AG)
        Set Plage = Range([AE3], Cells(Rows.Count, "AE").End(xlUp)).Resize(, 3)
        'chargement de cette plage dans une variable tableau en mémoire pour accélérer les calculs
        Tabl = .Transpose(.Transpose(Plage))
        '"Tabl, 1" représente la première colonne (AE)
        'de la première à la dernière cellule de cette plage
        For i = 1 To UBound(Tabl, 1)
            'calcul du résultat de la formule de AE
            Tabl(i, 1) = Evaluate("sumproduct((" & [clé].Address & "=4)*(" & [document_HA].Address & "=B" & _
            i + 2 & ")*(" & [division].Address & "))/2")
            'calcul du résultat de la formule de AF
            Tabl(i, 2) = Evaluate("sumproduct((" & [clé].Address & "=""    "")*(" & [document_HA].Address & "=B" & _
            i + 2 & ")*(" & [division].Address & "))/2")
            'calcul du résultat de la formule de AG
            Tabl(i, 3) = Evaluate("sumproduct((" & [clé].Address & "=""ZSNC"")*(" & [document_HA].Address & "=B" & _
            i + 2 & ")*(" & [division].Address & "))/2")
        Next i
        'Recopie de la variable tableau sur la feuille
        Plage.Value = Tabl
        .ScreenUpdating = True
        .EnableEvents = True
        End With
    End If
End Sub