Discussion :

[bpo2018]

Première question: One Shot ou travail à répéter par la suite et donc intérêt d'automatiser?


Ensuite: que ne sais-tu pas réaliser dans les points suivants?

1. Ajouter une colonne à droite dans laquelle tu vas formuler tes conditions (à coup de SI, de ET, et OU, de GAUCHE) pour récupérer VRAI ou FAUX
2. Trier le tableau sr cette colonne
3. Déterminer le bloc de lignes à supprimer
4. Supprimer ces lignes

Déjà, réalise cela en Excel (sur une copie du tableau), pour voir si tu obtiens bien ce que tu désires. Si oui, on écrira ces étapes en VBA.

oui c'est un travail à reproduire chaque matin d'où l'intéret pour moi de l'automatiser

[Pierre Fauconnier]

Voici un exemple pour rédiger ta formule



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

[bpo2018]

Voici un exemple pour rédiger ta formule



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:

=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]<>""))

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

[Invité]

Bonjour,

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

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

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