Temps d'exécution de macro vba trop lente ou à améliorer

Version imprimable

Voir 40 message(s) de cette discussion en une page

27/06/2018, 15h52
bpo2018

Citation:

Envoyé par Pierre Fauconnier

Voici un exemple pour rédiger ta formule

Pièce jointe 392070

A adapter à tes cas sur les x colonnes de test ;) (Bon amusement... ;) )

Ouais c'est vraiment cool j'ai essayé d'adapter mais je crois que j'ai fait une erreur dans ma formule car j'ai seulement comme résultat faux alors qu'il doit aussi avoir des vrai voice ma formule:

Citation:

=ET(GAUCHE([@CodeAppro];2)<>"KT";OU([@dispo]="V";[@dispo]="T");[@hyper]>="7";[@market]>="7";[@cash]>="7";[@proxi]>="7";ET([@substit]="oui";[@stocklien]<>"0");ET([@Stock]<="0";[@DateReception]<>"");ET([@Stock]<="0";[@DateReception]="";[@Manquant]="0";OU([@CouvertureBrute]="0";[@CouvertureBrute]="999");[@nvtDacal]="0");ET([@Stock]>"0";[@couverture]>"7");ET([@Stock]>"0";[@encours]="0";[@DateReception]<>""))
28/06/2018, 08h53
Pierre Fauconnier

L'audit de formules te permettra d'y voir plus clair et de comprendre dans quel ordre les conditions sont évalués et comment elles sont regroupées.

Bonjour,

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
35
36
37
38
Sub es() 
 ' COPIE DES LIGNES DESIREES DANS LES FEUILLES DE CALCUL DEDIEES
    Dim BoEcran As Boolean, BoBarre As Boolean, BoEvent As Boolean, BoSaut As Boolean, i As Long, Supp As Boolean,iCalcul As Integer
' on conserve d'abord les configurations existantes
    BoEcran = Application.ScreenUpdating
    BoBarre = Application.DisplayStatusBar
    iCalcul = Application.EnableEvents
    BoSaut = ActiveSheet.DisplayPageBreaks
' on force les configurations
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlManual
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False
    For i = Range("i500000").End(xlUp).Row To 2 Step -1
        Supp = False
        If Left(Cells(i, 22), 2) = "HT" Or Left(Cells(i, 22), 2) = "KA" Then Supp = True
        If Left(Cells(i, 22), 2) = "H1" Or Left(Cells(i, 22), 2) = "H2" Or Left(Cells(i, 22), 2) = "H3" Or Left(Cells(i, 22), 2) = "H4" Or Left(Cells(i, 22), 2) = "H5" Or Left(Cells(i, 22), 2) = "H6" Or Left(Cells(i, 22), 2) = "H7" Or Left(Cells(i, 22), 2) = "H8" Or Left(Cells(i, 22), 2) = "H9" Or Left(Cells(i, 22), 2) = "H0" Then Supp = True
        If Cells(i, 9) = "V" Or Cells(i, 9) = "T" Then Supp = True
        If Cells(i, 45) = "7" Or Cells(i, 45) = "8" Or Cells(i, 45) = "9" Or Cells(i, 45) = " " Then Supp = True
        If Cells(i, 46) = "7" Or Cells(i, 46) = "8" Or Cells(i, 46) = "9" Or Cells(i, 46) = " " Then Supp = True
        If Cells(i, 47) = "7" Or Cells(i, 47) = "8" Or Cells(i, 47) = "9" Or Cells(i, 47) = " " Then Supp = True
        If Cells(i, 48) = "7" Or Cells(i, 48) = "8" Or Cells(i, 48) = "9" Or Cells(i, 48) = " " Then Supp = True
        If Cells(i, 40) = "oui" And Cells(i, 42) <> "0" Then Supp = True
        If Cells(i, 7) <= "0" And Cells(i, 28) <> "" Then Supp = True
        If Cells(i, 7) <= "0" And Cells(i, 28) = "" And Cells(i, 38) = "0" And Cells(i, 98) = "0" And Cells(i, 59) = "999" Then Supp = True
        If Cells(i, 7) <= "0" And Cells(i, 28) = "" And Cells(i, 38) = "0" And Cells(i, 98) = "0" And Cells(i, 59) = "0" Then Supp = True
        If Cells(i, 7) > 0 And Cells(i, 29) = 0 And Cells(i, 27) = "" Then Supp = True
        If Cells(i, 7) > 0 And Cells(i, 58) > 7 Then Supp = True
        If Supp Then Rows(i).Delete  
    Next i
'les configurations sont restaurées
    Application.ScreenUpdating = BoEcran
    Application.DisplayStatusBar = BoBarre
    Application.Calculation = iCalcul
    Application.EnableEvents = BoEvent
    ActiveSheet.DisplayPageBreaks = BoSaut
End Sub

28/06/2018, 10h27
Pierre Fauconnier

Salut Robert,

As-tu testé ta solution sur un tableau rempli?

Temps d'exécution: beaucoup trop long. j'ai dû planter ma machine après 10 minutes, en n'ayant que deux colonnes de test remplies (I et V).

Avec ta méthode VBA, il me semblerait utile d'optimiser sur les conditions à partir de la deuxième. Car dès que Sup passe à TRUE (si ce que tu as exprimé comme conditions correspond au souhait du demandeur), on devrait stopper les tests et aller de suite à la suppression. Là, on pourrait gagner du temps, notamment si on peut ordonner les tests par fréquence estimée décroissante (mettre les tests qui ont le plus de chance de renvoyer TRUE au dessus, et sortir du test dès que True). Ca permettrait de grignoter un peu de temps, mais je pense qu'une réelle optimisation ne viendra pas de là.

Ma solution par tableau pourrait être intéressante, car il n'est pas nécessaire de charger tout le tableau (mémoire insuffisante, cfr plus haut). On pourrait charger x tableaux (un par colonne sur laquelle on opère les tests), puis tester par colonne. En optimisant pour tester vecteur par vecteur (comme dit plus haut, pas besoin de tout tester dès lors que la condition est remplie), on pourrait gagner un peu de temps, mais suite à un test, ça reste très très lent (et encore, uniquement sur deux colonnes de test). Traiter 500.000 lignes en boucle restera un processus très lent avec Excel.

Je maintiens aussi la solution par formule Excel, le tri puis la suppression en bloc. La formule pourrait être éclatée sur plusieurs colonnes pour plus de clarté.

Une autre solution pourrait venir de POWERQUERY, qui travailler peut-être plus rapidement et qui pourrait recevoir la table de départ, la traiter puis la restituer testée et triée. Il ne resterait alors qu'à supprimer les lignes en un seul bloc.

Une (encore) autre solution pourrait venir d'une simplification éventuelle de test de suppression, et donc de revoir la logique métier.

Une (encore) autre solution serait de travailler en amont d'Excel. Je suppose que ces données sont amenées dans Excel par un extract d'un logiciel type sap, erp ou autre bidule du genre, et il serait peut-être intéressant de travaille de ce côté pour amener moins de données en Excel.

De plus, mais c'est périphérique: pourquoi For i = Range("i500000"). Tant qu'à faire, autant utiliser i1048576.
28/06/2018, 17h46
Invité

Bonsoir Pierre,
En fait j'ai voulu lui montrer l'inutilité de multiple boucle en reprenant son code dans une seule boucle.

Ici nous avons dune base de données avec champs calculé et un volume de données presque aberrant pour un fichier Excel.

Vue le volume,bien Access me paresse plus confirme pour ce genre de traitement, j'aurais fais une requête sql update(ColonneA='Kill") avec Thisworbook.fullname en connexion

Ensuite for i step -1 pour supprimer les lignes.

Mais ajouter une colonne calculer me parait bien également.

Si les données proviennent effectivement d'un ERP, oui revoir la méthode d'import.
28/06/2018, 17h52
Pierre Fauconnier

Oui Robert. La requête, c'est une bonne idée, c'est ce que je proposais en disant qu'il faudrait travailler en amont. Je serais curieux, si l'amont est un fichier Excel, de travailler en MSQuery ou en connexion directe avec une belle clause WHERE, pour voir si le moteur sql d'Excel est optimisé de manière significative par rapport à une feuille Excel.
28/06/2018, 18h25
Invité

J'ai proposé une solution similaire à un quidams se ce forum, pour cette fois un export vers Access.

Maintenant si c'est un fichier texte requêté dessus n'est pas un problème pour l'import dans Excel.

Voir 40 message(s) de cette discussion en une page