Formule tableau et temps de calcul long

Version imprimable

Bonjour à tous,

J'ai un gros tableau de données sur 12 colonnes 33543 lignes. J'ai rajouté une colonne grâce à une macro VBA qui compte le nombre d'apparitions d'un critère (avec les formules SI et NB.SI.ENS).

Voici le code de la macro :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Sub ecrire_colonne_pannes() Dim Wk As Worksheet Dim j As Long Set Wk = Workbooks("BTU.xlsx").Worksheets("Défauts+CTRL (Parcs)") Wk.Range("M1").Value = "nb pannes" j = Application.WorksheetFunction.CountA(Wk.Range("D:D")) With Wk.Range("M2:M" & j) .FormulaR1C1 = _ "=IF(COUNTIFS(C4,RC[-9],C8,""MONPAN*"")>3,""D"",IF(COUNTIFS(C4,RC[-9],C8,""MONPAN*"")=3,""C"",IF(COUNTIFS(C4,RC[-9],C8,""MONPAN*"")=2,""B"",IF(COUNTIFS(C4,RC[-9],C8,""MONPAN*"")=1,""A""))))" .Value = .Value End With End Sub
La macro marche, ma colonne est bien créée, mais en revanche le calcul est très long (5% au bout d'une minute...). N'existe-t-il pas un moyen pour accélérer ceci, par exemple procéder au calculs en mémoire avec un array puis transposer cet array sur la colonne correspondante ?

Merci d'avance

17/09/2018, 13h38
Menhir

Tu pourrais commencer par passer ta feuille en calcul manuel au début de la macro et ne la remettre en calcul automatique qu'à la fin.
Lis ceci :
https://msdn.microsoft.com/fr-fr/lib.../ff841201.aspx
https://msdn.microsoft.com/fr-fr/lib.../ff834658.aspx

Ou ceci :
https://docs.microsoft.com/fr-fr/off...on.calculation
https://docs.microsoft.com/fr-fr/off....xlcalculation
17/09/2018, 15h17
elkaktus

Merci pour la solution, mais ça n'aide pas trop. La colonne n'est plus calculée, même avec F9.

N'existe-t-il aucun moyen de faire le calcul dans un tableau en mémoire puis transposer ce tableau après la dernière colonne de mon tableau de données ?
D'autre part avec cette méthode par VBA, je n'arrive plus à lire la formule quand je clique sur la cellule.
17/09/2018, 15h26
Guy7700

Bonjour,

Est-ce que ceci peut aider ?

Avant tes "dim"

Code:

Application.ScreenUpdating = False

Avant ton "end sub"

Code:

Application.ScreenUpdating = True

Désolé si ça n'aide en rien, j'essaye juste d'apporter ma pierre à l'édifice en fonction de mes (maigres) connaissances.

Bien à vous,
17/09/2018, 16h46
elkaktus

Merci beaucoup pour vos reponses, ça me donne déjà des pistes a creuser. Le screen updating n'a pas d'effet, j'essaie de regarder du coté des xlCalculationManual.
17/09/2018, 20h05
eriiic

Bonjour,

tu utilises 4 fois

Code:

NB.SI.ENS($D:$D;D2;$H:$H;"MONPAN*")

Formule matricielle gourmande.
L'optimisation la plus simple là dessus c'est de ressortir la partie utilisée plusieurs fois dans une colonne dédiée, qu'elle ne soit évaluée qu'une seule fois. Et d'utiliser le résultat plusieurs fois.

C'était pour info car ta formule peut se simplifier puisqu'il y a une progression logique :

Code:

=CAR(64+MIN(4;NB.SI.ENS($D:$D;D3;$H:$H;"MONPAN*")))

En plus tu la mets sur toute la colonne $D:$D...
Il faut soit mettre tes données sous forme de tableau structuré, soit définir un nom dynamique (fait une recherche, les exemples ne manquent pas) pour ne l'appliquer qu'à la plage nécessaire, et non sur 1 millions de lignes.
eric
17/09/2018, 20h49
elkaktus

Merci. J'ai déjà redéfini les plages, mis le nb.si.ens dans une autre colonne et ça va un peu mieux.
Mais ce nb.si.ens, n'y-a-t-il pas moyen de le faire en mémoire via un tableau ?
18/09/2018, 09h27
Menhir

VBA n'a pas beaucoup d'outils pour faire des traitements sur des tableaux de variables.
Il faudrait donc vraisemblablement passer par une boucle, ce qui serait encore plus lent qu'une fonction.

Je pense que la lenteur vient du fait que mon tableau fait 33113 lignes, que j'ai 15 colonnes avec des valeurs texte, et 4 colonnes supplémentaires pour mes calculs. J'utilise abusivement les fonctions NB.SI.ENS et SOMMEPROD pour effectuer mes recherches et tri dans le tableau, et compter le nombre d'éléments distincts en fonction d'autres valeurs du tableau.

Voici le code qui est lent :

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
Sub recup_results()
    Dim wk, Wksrc As Worksheet
    Dim j As Long
 
    Set wk = Workbooks("BTU.xlsx").Worksheets("Resultats")
    Set Wksrc = Workbooks("BTU.xlsx").Worksheets("Défauts+CTRL (Parcs)")
 
    j = Application.WorksheetFunction.CountA(Wksrc.Range("D:D"))
 
    wk.Range("A1").Value = "Manque pièces"
    wk.Range("A2").Value = "2008"
    wk.Range("B2").FormulaR1C1 = "=SUMPRODUCT((('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21=1)*('Défauts+CTRL (Parcs)'!R2C5:R" & j & "C5=""2008""))/COUNTIFS('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21,'Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21&"""",'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4,'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4&""""))"
    wk.Range("A3").Value = "X74"
    wk.Range("B3").FormulaR1C1 = "=SUMPRODUCT((('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21=1)*('Défauts+CTRL (Parcs)'!R2C5:R" & j & "C5=""X74""))/COUNTIFS('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21,'Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21&"""",'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4,'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4&""""))"
    wk.Range("A4").Value = "R82"
    wk.Range("B4").FormulaR1C1 = "=SUMPRODUCT((('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21=1)*('Défauts+CTRL (Parcs)'!R2C5:R" & j & "C5=""R82""))/COUNTIFS('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21,'Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21&"""",'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4,'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4&""""))"
    wk.Range("A5").Value = "R83"
    wk.Range("B5").FormulaR1C1 = "=SUMPRODUCT((('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21=1)*('Défauts+CTRL (Parcs)'!R2C5:R" & j & "C5=""R83""))/COUNTIFS('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21,'Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21&"""",'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4,'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4&""""))"
 
    wk.Range("A7").Value = "SMON/bancs"
    wk.Range("A8").Value = "2008"
    wk.Range("B8").FormulaR1C1 = "=SUMPRODUCT((('Défauts+CTRL (Parcs)'!R2C15:R" & j & "C15=""SMON01"")*('Défauts+CTRL (Parcs)'!R2C5:R" & j & "C5=""2008""))/COUNTIFS('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21,'Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21&"""",'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4,'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4&""""))"
    wk.Range("A9").Value = "X74"
    wk.Range("B9").FormulaR1C1 = "=SUMPRODUCT((('Défauts+CTRL (Parcs)'!R2C15:R" & j & "C15=""SMON01"")*('Défauts+CTRL (Parcs)'!R2C5:R" & j & "C5=""X74""))/COUNTIFS('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21,'Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21&"""",'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4,'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4&""""))"
    wk.Range("A10").Value = "R82"
    wk.Range("B10").FormulaR1C1 = "=SUMPRODUCT((('Défauts+CTRL (Parcs)'!R2C15:R" & j & "C15=""SMON01"")*('Défauts+CTRL (Parcs)'!R2C5:R" & j & "C5=""R82""))/COUNTIFS('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21,'Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21&"""",'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4,'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4&""""))"
    wk.Range("A11").Value = "R83"
    wk.Range("B11").FormulaR1C1 = "=SUMPRODUCT((('Défauts+CTRL (Parcs)'!R2C15:R" & j & "C15=""SMON01"")*('Défauts+CTRL (Parcs)'!R2C5:R" & j & "C5=""R83""))/COUNTIFS('Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21,'Défauts+CTRL (Parcs)'!R2C21:R" & j & "C21&"""",'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4,'Défauts+CTRL (Parcs)'!R2C4:R" & j & "C4&""""))"
 
End Sub

Une 20aine de minutes à tout traiter ...

De plus, j'ai remarqué que mon processus excel reste bloqué à 25% de processeur. C'est normal ?

18/09/2018, 16h04
78chris

Bonjour

Ne serait-il pas plus simple d'utiliser un TCD ? Il se calcule une seule fois et non à chaque calcul...
18/09/2018, 16h10
elkaktus

Merci, je voudrais bien mais le soucis de mes données c'est que c'est un snapshot au jour le jour, a chaque actualisation les résultats changent.
J'ai besoin de garder un historique de ces données (le nombre d'éléments) par jour et par mois. Je vois pas trop comment faire ça avec un TCD ...
18/09/2018, 18h24
78chris

RE

Que ce soit des formules ou un TCD le problème c'est la source : si tu changes les données sources le résultat de tes formules évoluera aussi : il manque une explication... car ton code ne contient pas de remplacement des formules par le résultat...

Je pense par ailleurs qu'une solution PowerQuery serait également adaptée puisque tu as 2016 où il est intégré...
18/09/2018, 19h54
elkaktus

Dans mon code je stocke les données calculées jour par jour.

Peux-tu m'en dire plus sur PowerQuery ? Cela serait adapté pour un tableau de 33000 lignes et 20 colonnes ?
19/09/2018, 12h50
78chris

Bonjour

Citation:

Envoyé par elkaktus

Dans mon code je stocke les données calculées jour par jour.

Dans le code posté on voit des formules...

Citation:

Envoyé par elkaktus

Peux-tu m'en dire plus sur PowerQuery ? Cela serait adapté pour un tableau de 33000 lignes et 20 colonnes ?

PowerQuery contrairement à Excel est fait pour travailler sur des bases de données : il permet des requêtes et pas mal de traitements sur les données de tables et évite souvent le VBA.

Poste un petit exemple avant après...