SOMMEPROD trop lourd, remplacer en VBA

Version imprimable

21/09/2012, 11h27
ClaireD

SOMMEPROD trop lourd, remplacer en VBA

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
21/09/2012, 11h47
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:

=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.
23/09/2012, 18h10
ClaireD

1 pièce(s) jointe(s)

SOMMEPROD trop lourd, remplacer en VBA

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

Essaie cette macro.

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

Code:

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

25/09/2012, 10h08
ClaireD

SOMMEPROD trop lourd, remplacer en VBA

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

1 pièce(s) jointe(s)

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:

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

25/09/2012, 14h32
Daniel.C

Citation:

D'autre part, j'avais vu en parcourant les forums que quelqu'un utilisait un bout de code du style "Application.Match"

Libre à eux... Cela correspond à la fonction Excel EQUIV. Je ne vois pas trop comment on pourrait s'en servir.

1 pièce(s) jointe(s)

Bonjour,

Je n'ai pas de nouvelles de toi; je ne sais pas si tu as pu décompresser le fichier zip que j'avais mis en pièce jointe. Il faut le faire avec winrar, winzip ou autres. Si tu ne le peux pas, je t'enverrais le classeur au format xls, mais alors, tu perdras des mises en forme conditionnelles. J'ai repensé à ton problème cette nuit. A chaque modification, on n' a seulement besoin de recalculer les 3 cellules de la ligne modifiée. Je mets en pièce jointe un classeur dans ce sens. Si tu n'arrives pas à le décompresser, mets la macro dans le module de la feuille "Base". Si tu ne sais pas comment faire, dis-le.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
' 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
        'calcul du résultat de la formule de AE
        Cells(Target.Row, "AE") = Evaluate("sumproduct((" & [clé].Address & "=4)*(" & [document_HA].Address & "=B" & _
        Target.Row & ")*(" & [division].Address & "))/2")
        'calcul du résultat de la formule de AF
        Cells(Target.Row, "AF") = Evaluate("sumproduct((" & [clé].Address & "=""    "")*(" & [document_HA].Address & "=B" & _
        Target.Row & ")*(" & [division].Address & "))/2")
        'calcul du résultat de la formule de AG
        Cells(Target.Row, "AG") = Evaluate("sumproduct((" & [clé].Address & "=""ZSNC"")*(" & [document_HA].Address & "=B" & _
        Target.Row & ")*(" & [division].Address & "))/2")
        .ScreenUpdating = True
        .EnableEvents = True
        End With
    End If
End Sub

J'ai terminé trop vite. Il faut d'abord exécuter la macro initiale pour transformer les formules en valeur :

Code:

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

28/09/2012, 09h53
Daniel.C

Bonjour,

Peux-tu me donner une réponse, s'il te plait ?
10/10/2012, 17h33
ClaireD

Bonjour Daniel,

Excusez-moi mais nous travaillons à plusieurs sur cet identifiant... En fait votre macro m'a appris des choses mais elle n'est pas plus rapide dans le calcul que les formules SOMMEPROD que j'avais mis. De plus, vous m'avez proposé que la macro se lance à chaque ajout de ligne mais en réalité, je supprime tout le fichier puis le recrée (avec déjà les lignes en plus)...
Dans tous les cas je vous remercie de votre patience et vous souhaite une bonne journée.
Cordialement
10/10/2012, 20h51
Pierre Fauconnier

Salut.

Vu que tu postes des xlsx, tu aurais intérêt à travailler avec des tables de données (insertion>tableaux>tableau). Cela ne résout pas le problème de la lenteur, mais tu peux alors te passer de DECALER pour travailler avec les références structurées... ou pas, car les références classiques qui s'appuient sur une table de données sont dynamiques également. (Voir le tuto de SilkyRoad sur le sujet).

La macro ne pourra pas aller plus vite puisqu'elle utilise SOMMEPROD...

J'ai téléchargé ton fichier (posté le 23/09 à 18h10), mais je ne comprends pas bien le résultat que tu souhaites avoir, par exemple dans la colonne AE (clé 4). Il est en effet très rare d'utiliser SOMMEPROD dans un tableau de données. Généralement, c'est dans un tableau de résultat que l'on manipule cette fonction.

Peux-tu détailler ce que tu souhaites obtenir?
10/10/2012, 21h04
Daniel.C

@ Pierre Fauconnier :

Citation:

La macro ne pourra pas aller plus vite puisqu'elle utiliser SOMMEPROD...

Si dans l'hypothèse où une cellule modifiée provoquait un recalcul général tandis que la macro évènementielle proposée ne recalculait que les 3 cellules de la ligne de la cellule modifiée.
Comme je viens d'apprendre que le tableau est changé en bloc, effectivement...
10/10/2012, 21h18
Pierre Fauconnier

Salut Daniel,

Je me basais sur ton code du 26/09 qui bouclait sur les lignes du tableau. Si 30000 lignes, ton code calculait donc 90000 SUMPRODUCT (sur 30000 lignes chacune), non?

Cela dit, je me pose la question de l'utilité d'un SOMMEPROD au sein d'une table de données. Habituellement, on utilise SOMMEPROD dans des tableaux de synthèse, parce que dans l'hypothèse fournie par Claire, chaque ligne des 30000 du tableau calcule trois SOMMEPROD sur 30000 lignes... C'est un peu normal que ce soit (très) lent (avec les trois calculs par sommeprod, ça fait plus de 8 milliards de calculs!). Et à mon avis, cela n'a pas beaucoup de sens.
10/10/2012, 21h52
Daniel.C

Citation:

Si 30000 lignes, ton code calculait donc 90000 SUMPRODUCT (sur 30000 lignes chacune), non?

Exact, c'était pour remplacer les formules par des valeurs.

Citation:

Cela dit, je me pose la question de l'utilité d'un SOMMEPROD au sein d'une table de données. Habituellement, on utilise SOMMEPROD dans des tableaux de synthèse, parce que dans l'hypothèse fournie par Claire, chaque ligne des 30000 du tableau calcule trois SOMMEPROD sur 30000 lignes... C'est un peu normal que ce soit (très) lent (avec les trois calculs par sommeprod, ça fait plus de 8 milliards de calculs!). Et à mon avis, cela n'a pas beaucoup de sens.

C'est vrai que j'oublie souvent de me poser la question du "pourquoi ?". J'essaie tout de suite de trouver la potion...
11/10/2012, 17h57
ClaireD

Bonjour Pierre et Daniel,

Merci de continuer à vous intéresser à mon problème...
Mais je crois que je viens de trouver la solution pour "alléger" mes calculs. Il me suffit de faire un TCD avec les différentes clés pour mes contrats et j'aurais le même résultats que toutes mes sommeprod...
En tout cas, merci d'avoir pris le temps.
Cordialement

A bientôt
12/10/2012, 09h49
Pierre Fauconnier

Bonjour Claire,

C'est pour cela que je disais que cela n'avait pas beaucoup de sens d'utiliser SOMMEPROD dans le tableau de données. C'est toujours dans un tableau de résultat que l'on utilise ce genre de formules.

Et effectivement, le TCD est très souvent une alternative très intéressante à SOMMEPROD pour créer des résultats agrégés.

Bonne continuation!
12/10/2012, 15h20
ClaireD

Effectivement le TCD est une bonne alternative :)
Merci encore