Optimisation du Calcul VBA

Version imprimable

Bonjour à tous,
J'ouvre une deuxième discussion car mon problème est à présent différent.

J'ai testé avec le code ci-dessous :
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 Sub Super_toto() Dim PS_J1 As Range ', PS_J2 As Range, PS_J3 As Range, PS_J4 As Range, PS_J5 As Range, PS_J6 As Range, PS_J7 As Range Dim PlageDest As Range With ThisWorkbook Set PS_J1 = .Worksheets("J1").Cells(2, 1).Resize(.Worksheets("J1").Cells(.Worksheets("J1").Rows.Count, 1).End(xlUp).Row - 1, 1) Set PlageDest = .Worksheets("Articles").Cells(2, 3).Resize(.Worksheets("Articles").Cells(.Worksheets("Articles").Rows.Count, 2).End(xlUp).Row - 1, 1) End With With PlageDest '=SOMMEPROD(('J1'!$A$2:$A$35000=Articles!B2)*('J1'!$C$2:$C$35000=Articles!$C$1)*'J1'!$D$2:$D$35000) .Formula = "=SUMPRODUCT((J1!" & PS_J1.Address(True, True) & "=B2)*('J1'!" & PS_J1.Offset(0, 2).Address(True, True) & "=$C$1)*(J1!" & PS_J1.Offset(0, 3).Address(True, True) & "))" .Value = .Value End With With PlageDest.Offset(0, 1) '=SOMMEPROD(('J1'!$A$2:$A$35000=Articles!B2)*'J1'!$D$2:$D$35000) .Formula = "=SUMPRODUCT((J1!" & PS_J1.Address(True, True) & "=B2)*(J1!" & PS_J1.Offset(0, 3).Address(True, True) & "))" .Value = .Value End With MsgBox "Calculs Terminés" End Sub
Bon très honnêtement, je ne dois pas avoir un cheval de course car pour moi ça prend 5min ^^
D'ailleurs, je ne comprend pas pourquoi mes 4 processeurs chargent 2 fois de 0 à 100% :-/
De plus, j'ai testé avec deux boucles "With" séparées l'une de l’autre comme ci-dessus et deux boucles imbriquées l'une dans l'autre, et je ne remarque pas de différence au niveau du temps de calcul. Pensez-vous que c'est normal ?

Est-ce que vous auriez une astuce pour accélérer les calculs ?

a) En effet, pour le moment, je fais mon calcul avec la feuille "J1", mais je dois faire la même chose de J1 à J7. Cela risque donc de prendre 7 fois plus de temps donc au moins 5min x 7

b) De plus, mes formules =SUMPROD(J1 BLABLA), vont devenir =SUMPROD(J1 BLABLA) + SUMPROD(J2 BLABLA) + ... + SUMPROD(J7 BLABLA)

c) Est-ce qu'il est préférable de faire des opérations dans des feuilles séparées ou grouper toutes les données sur une seul feuille ?

Merci par avance pour votre aide :-)

03/02/2017, 10h27
Jean-Philippe André

Salut,

quelques elements a prendre en compte pour optimiser les temps de calculs :
- bloquer le rafraichissement ecran (screenupdating)
- passer le calcul en mode manuel si c'est possible (calculationmode)
- optimiser en imbriquant au maximum les With

Je ne suis pas au top sur les formules, mais je pense que ta somme prod peut etre simplifiee :/

Citation:

Envoyé par rocktrak

De plus, j'ai testé avec deux boucles "With" séparées l'une de l’autre comme ci-dessus et deux boucles imbriquées l'une dans l'autre, et je ne remarque pas de différence au niveau du temps de calcul. Pensez-vous que c'est normal ?

With n'est pas une boucle mais une assignation.
Tu ne confondrais pas avec While ?

Je trouve qu'avec tes Resize, tu te compliques la vie. C'est plus simple ainsi :
Code:

1 2 3 4 With ThisWorkbook Set PS_J1 = .Worksheets("J1").Range("A2", .Worksheets("J1").Cells(.Rows.Count, 1).End(xlUp)) Set PlageDest = .Worksheets("Articles").Range("C2:C" & .Worksheets("Articles").Cells(.Rows.Count, 2).End(xlUp).Row) End With
Citation:

Bon très honnêtement, je ne dois pas avoir un cheval de course car pour moi ça prend 5min ^^

La fonction SOMMEPROD, comme les fonctions matricielles, les SOMME.SI et autres, est très gourmande en ressources.
Si tu l'appliques plusieurs centaines de fois pour traiter une zone elle aussi énorme, il est normal que ça prenne beaucoup de temps et que ça consomme tes processeurs.

03/02/2017, 10h48
pijaku

Salut,

Peut-être éviter les formules SOMMEPROD très gourmandes, tu peux essayer de passer par une variable tableau.
03/02/2017, 11h00
rocktrak

Bonjour et merci pour vos réponses :-)

Je n'ai pas compris ce qu'était une variable tableau ?

Au niveau du Resize, je vais effectivement changer et voir si c'est mieux :-)

Parcontre, je viens de lancer une marco et Excel a le statut "Ne répond pas".
Est-ce que cela veux dire que le logiciel a planté ou il est en train de calculer ?
S'il n'est pas en train de calculer, je ne sais pas pourquoi il a planté...

Citation:

Envoyé par rocktrak

Je n'ai pas compris ce qu'était une variable tableau ?

Un exemple?
Soit une plage de cellules de 5 colonnes (A:E) sur x lignes.
Je veux le résultat de la formule suivante : =SOMMEPROD((A1:Ax="titi")*(C1:Cx="toto")*(E1:Ex))
La fonction VBA est la suivante :
Code:

1 2 3 4 5 6 7 8 9 10 Function Somme_Prod(plage As Range, comp_1 As String, comp_2 As String) As Long Dim tb, somme&, i& Application.Volatile tb = plage For i = 1 To UBound(tb, 1) If tb(i, 1) = comp_1 And tb(i, 3) = comp_2 Then somme = somme + tb(i, 5) Next i Somme_Prod = somme End Function
à appeler, soit dans une cellule :
=Somme_Prod(A1:E3000;"titi";"toto")
soit dans un code VBA :
Code:

1 2 3 Dim SP As Long, plage As Range Set plage = Range("A1:E3000") SP = Somme_Prod(plage;"titi";"toto")
Dans la function sSomme_Prod, tb(i, 1) représente la colonne A, tb(i, 3) la colonne C et tb(i, 5) ==> E (1 = A; 3=C; 5=E).

03/02/2017, 11h44
Menhir

Citation:

Envoyé par rocktrak

Au niveau du Resize, je vais effectivement changer et voir si c'est mieux :-)

Le code que j'ai proposé ne va pas accélérer l'exécution.
Il rend seulement le code plus lisibles et plus simple.

Pour ce qui est de ton problème, peut-être peux-tu essayer de remplacer les SOMMEPROD par des SOMME.SI.ENS (à priori, ça devrait convenir à ton application) qui sont (un peu) moins gourmands en ressources.
03/02/2017, 11h54
unparia

Bonjour
Je trouve en ce qui me concerne, que passer par une formule pour calculer des valeurs de cellules, puis "figer" ces valeurs (par .value = .value) revient à faire travailler Excel deux fois sur les cellules concernées.
Il vaudrait selon moi alors mieux aboutir directement à ce résultat "figé" par utilisation de WorkSheetfunction.SumProduct (en inhibant bien entendu l'affichage pendant la boucle de ces attributions de valeurs).

EDIT :

Citation:

D'ailleurs, je ne comprend pas pourquoi mes 4 processeurs chargent 2 fois de 0 à 100% :-/

Tu vas peut-être commencer à le comprendre en apprenant que les formules de Excel sont calculées dans un fil distinct de celui de VBA, mais que leur "figeage" (par .value = .value) fait intervenir, lui, le fil de VBA.
03/02/2017, 13h08
unparia

Je vais être encore plus précis en ce qui concerne l'aspect de ce que j'ai dit dans mon message précédent :
Lorsque l'on utilise Excel, des Formules et VBA, on met en oeuvre TROIS fils distincts :
- un fil Excel "pur" : il est réservé aux seules cellules traitées
- un fil totalement distinct, réservé aux seules formules : ce fil-là "importe" uniquement les valeurs des cellules évoquées dans la formule utilisée et n' "exporte" que la valeur de cellule abritant la formule. Aucune référence à aucune autre cellule que celles-ci ne pourrait être faite. On peut s'en apercevoir aisément en créant tout simplement des fonctions personnalisées.
- un fil VBA, lui-même distinct du fil Excel.
Lorsque la machine utilise plusieurs processeurs, c'est Windows (et lui seul) qui choisit quel processeur doit utiliser tel ou tel autre fil.

Lorsque l'on en est parfaitement conscient, d'une part, et que, d'autre part, on "voit" bien ce qui va se passer en coulisses, on choisit/privilégie telle ou telle autre méthode de traitement. Ce choix-là n'est pas toujours évident. Lorsqu'il ne l'est pas, on fait des tests de comparaison de performances. Tout le reste ne serait que "récitation" de choses non toujours réellement fondées. ;)

Bonjour,

pour prolonger les bases qui ont été proposées dans ta discussion précédente.

Elle injectait les formules pour ensuite ne conserver que les valeurs.

Hélas, ça ne suffit pas au vu de la masse de données que tu as.

Alors, on peut immédiatement "évaluer" le résultat pour ne transmettre que la valeur à la cellule excel
Et comme astucieusement souligné par des contributeurs de ce fil, SOMMEPROD() est une formule lourde, avec un SOMME.SI.ENS on va gagner du temps

L'inconvénient de l'évaluation, tout du moins me concernant, c'est que je n'arrive pas à faire une évaluation matricielle et je dois passer par un boucle sur chaque cellule de la feuille Articles

Voici une nouvelle proposition "de compréhension" où

- les deux colonnes utilisent l'évaluation directe
- la première colonne n'est plus en SOMMEPROD(), elle est en SOMME.SI.ENS

On continue les optimisations, avis aux amateurs, que personne n'hésite à retoucher à cette piste
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 Sub Super_toto() Dim PS_J1 As Range ', PS_J2 As Range, PS_J3 As Range, PS_J4 As Range, PS_J5 As Range, PS_J6 As Range, PS_J7 As Range Dim PlageDest As Range, Cell As Range Dim Result() Application.ScreenUpdating = False With ThisWorkbook Set PS_J1 = .Worksheets("J1").Cells(2, 1).Resize(.Worksheets("J1").Cells(.Worksheets("J1").Rows.Count, 1).End(xlUp).Row - 1, 1) Set PlageDest = .Worksheets("Articles").Cells(2, 3).Resize(.Worksheets("Articles").Cells(.Worksheets("Articles").Rows.Count, 2).End(xlUp).Row - 1, 1) End With With PlageDest For Each Cell In .Cells Cell.Value = Evaluate("=SUMIFS(J1!" & PS_J1.Offset(0, 3).Address(True, True) & ",J1!" & PS_J1.Address(True, True) & ",B" & Cell.Row & ",'J1'!" & PS_J1.Offset(0, 2).Address(True, True) & ",$C$1)") Next Cell End With With PlageDest.Offset(0, 1) For Each Cell In .Cells Cell.Value = Evaluate("=SUMPRODUCT((J1!" & PS_J1.Address(True, True) & "=B" & Cell.Row & ")*(J1!" & PS_J1.Offset(0, 3).Address(True, True) & "))") Next Cell End With Application.ScreenUpdating = True MsgBox "Calculs Terminés" End Sub
Clairement, SOMME.SI.ENS semble être 5 fois plus rapide à traiter les éléments, au travers de tests que j'ai pu faire tout à l'heure

07/02/2017, 08h34
rocktrak

Je vous remercie tous pour votre aide.
Joe, ton code fonctionne à merveille et je suis en train de l'utiliser.

Pour passer à un niveau au dessus, est-ce que ce genre de traitement de données en "masse" ne serai pas plus adapté à Access ?
Pour le moment, je n'y connais rien mais si j'ai une confirmation de votre part qu'Access est capable de faire du traitement de données plus rapidement qu'Excel, alors je vais me lancer dedans :-)

Merci par avance :-D