Discussion :

[rocktrak]

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 :

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
 
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 :-)

[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 :/

[pijaku]

Salut,

Peut-être éviter les formules SOMMEPROD très gourmandes, tu peux essayer de passer par une variable tableau.

[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é...

[pijaku]

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 :

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

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 :

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

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).

[Menhir]

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.

[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 :

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.

[Menhir]

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 :

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

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

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.