Optimisation NB.SI.ENS avec macro
Bonjour à tous,
Voici ma problématique : J'ai un fichier excel pour analyse des traces, pour cela j'utilise beaucoup de "NB.SI.ENS" permettant de filtrer par Date / Numero / Critère.
Mais le fichier contient aujourd'hui 500 lignes à analyser, bientot ... 10 000 !!!
Et le traitement est plutot long, pour l'instant quelques secondes, mais quand il sera plein ce ne sera plus gérable !
J'ai voulu donc optimiser en créant un fonction pour remplacer le NB.SI.ENS
Malheureusement je n'ai pas du bien m'y prendre car pour l'instant c'est plus long que par formule.
En PJ le fichier avec la macro "NB_APPEL" qui est celle qui m'interesse.
- Je parcours l'ensemble des lignes, et suivant si la Date et Numéro correspond , je fais un "Like" Mais c'est trop lent.
Vous auriez une idée pour booster le traitement ?
Merci à vous
Voici la tête de la function :
Function NB_APPEL(debut As Date, fin As Date, num As String, valeur As String) As Integer
Dim Lig As Integer ' N° ligne traitée
Dim Nb As Integer ' N° ligne traitée
Dim NbLigne As String
'Lig = Worksheets("Traces").Range("D1048576").End(xlUp).Row
Nb = 0
NbLigne = Worksheets("Traces").Range("D1048576").End(xlUp).Row
If num = "" Then
For Lig = 2 To NbLigne
ddate = Sheets("Traces").Cells(Lig, 2).Value
' ----- Date DEBUT -----
If Sheets("Traces").Cells(Lig, 5).Value >= debut Then
If Sheets("Traces").Cells(Lig, 5).Value <= fin Then
If Sheets("Traces").Cells(Lig, 4).Value Like valeur Then
'MsgBox "valeur OK"
Nb = Nb + 1
End If
End If
End If
Next Lig
Else
For Lig = 2 To NbLigne
ddate = Sheets("Traces").Cells(Lig, 2).Value
' ----- Date DEBUT -----
If Sheets("Traces").Cells(Lig, 6).Value = num Then
If Sheets("Traces").Cells(Lig, 5).Value >= debut Then
If Sheets("Traces").Cells(Lig, 5).Value <= fin Then
If Sheets("Traces").Cells(Lig, 4).Value Like valeur Then
'MsgBox "valeur OK"
Nb = Nb + 1
End If
End If
End If
End If
Next Lig
End If
NB_APPEL = Nb
End FunctionBonjour,
Alors mon esprit est encore brumeux et mon ordinateur est un peu en galère (il faut que je pédale ce matin
Pour avancer :
Application.ScreenUpdating = False
et
Application.ScreenUpdating = True
(désactive les mises à jour de l'écran, d'autant plus pratique si il y a des boucles, désactivé avant la macro et réactivé après)
Bonjour,
du moment où on une boucle "for", sa deviens assez lent avec beaucoup beaucoup de ligne alors avec deux boucle "for"
vous ne pouvez rien faire avec des filtre ?
Salut,
Merci de ta réponse rapide.
Alors j'ai fais un test, et ce n'est pas flagrant ... Mais comme je ne fais pas de select dans la Macro, lors du calcul il n'affiche pas le parcours des lignes, donc pour moi c'était liés :
- Si pas de parcours de ligne, pas besoin d'arrêter la mise à jour de l'affichage.
Enfin j'ai peut etre tord !
Bonjour,
alors il faut mettre la feuille "traces" dans un tableau, ça ira 1000 fois plus vite
P.
minanse a écrit :Bonjour,
du moment où on une boucle "for", sa deviens assez lent avec beaucoup beaucoup de ligne alors avec deux boucle "for"
vous ne pouvez rien faire avec des filtre ?
Merci pour ta réponse,
- Alors en fait les boucles 'for' c'est l'une ou l'autre suivant si la case 'numéro' est vide ou non. Cela évite de faire le test dans le If à chaque itération (c'est un bout de l'optimisation)
Mais il n'y a jamais les 2.
- Pour le filtre, je ne sais pas, on peut appliquer un filtre sur la feuille depuis la macro avant de lancer un for ? comment ?
MErci
patrick1957 a écrit :Bonjour,
alors il faut mettre la feuille "traces" dans un tableau, ça ira 1000 fois plus vite
P.
Ah Ouai !! Euh... 1000 ligne dans un tableau ça pète pas ?
Je vais tester...
Bonjour,
Voici la macro en Tablo (Array) :
Function k_APPEL(debut As Date, fin As Date, num As String, valeur As String) As Integer
Dim i%, iLR%, k%, plage()
k = 0
iLR = Worksheets("Traces").Range("D1048576").End(xlUp).Row
plage = Range("D1:F" & iLR).Value
If num = "" Then
For i = 2 To iLR
' ----- Date DEBUT -----
If plage(i, 2) >= debut Then
If plage(i, 2) <= fin Then
If plage(i, 1) Like valeur Then
k = k + 1
End If
End If
End If
Next i
Else
For i = 2 To iLR
' ----- Date DEBUT -----
If plage(i, 3).Value = num Then
If plage(i, 2) >= debut Then
If plage(i, 2) <= fin Then
If plage(i, 1) Like valeur Then
k = k + 1
End If
End If
End If
End If
Next i
End If
k_APPEL = k
End FunctionAprès si c'est encore un peu lent ya les BD** (fonctions de base de données)...
A+
non non
la taille d'un tableau est limiter par à la valeur maximum d'un "INTEGER" c'est à dire 32 000 environ donc on pourrai mettre 32 000 ligne environ (et sa c'est pour un tableau à 1 dimension)
salut minanse,
hum... Moi je peux pas te dire à combien c'est limité mais surement pas à 32000 car j'ai déjà exploité de nombreux Array multicolonnes à plus de 40 000...
A+
oui mais c'est de array pas des tableaux simple x) y a même des structure qui peut atteindre les 2M après il ne sont pas tous aussi simple à manipuler
Merci à Tous pour votre aide.
Avec un tableau, en tout cas la macro est bien plus performante. comparé au NB.SI.ENS ... faut que je test avec plus de données.
Par contre une autre question, pour optimiser.
Le tableau et les données étant les mêmes entre deux calcul, n'est il pas possible de garder le tableau entre 2 ?
Je pense que ça pourrait améliorer notablement les perf ? non ?
Merci
bonjour,
je ne sais pas trop à qui tu parles... Si tu parles des tableaux Excel de minanse je ne pense pas que ça fasse beaucoup avancer le schmilblik...
Si tu parles de tableau ("Array") dans la macro que j'ai joint, je pense que pour quelques milliers de lignes ça vaut pas la peine de fatiguer.
Si tu envisage de passer à la vitesse supérieure (plusieurs centaines de milliers de lignes) alors là oui, il faudra envisager autre chose...
A+
Merci,
Je parlais bien des Array. Ok les performances sont vraiment meilleurs que par les formules.
En plus le fait d'utiliser une Macro je vais pour pousser beaucoup plus loin mon rendu de résultat (Switch Chiffre/Pourcentage, ...)
Un grand merci à tous pour votre aide.
Faut il marquer le sujet comme résolu ?
Bah... Si c'est pour continuer de travailler sur le même modèle. YAKA continuer...
A+