Problème temps exécution

[viscere]

Bonjour,


J'ai 2 fichiers excel, un contient une liste de données avec pour chaque ligne un identifiant et des données quelconques, et l autre une liste de données avec pour chaque ligne un identifiant et une valeur X.

Mon but est de parcourir le premier et d'aller chercher pour chaque ligne la valeur X correspondante au meme identifiant de l'autre fichier. Et vu que mon 2 ème fichier contient à peu près 10 000 identifiants (lignes), il en résulte un traitement très long.

Alors au début j'allais chercher la valeur X en comparant l'identifiant de chaque ligne à l'aide d'une boucle, et si il y avait correspondance je renvoyais la valeur X.

Ensuite j'ai essayé d'appliquer un autofilter en espérant gagner du temps mais cela n'a rien changé...

Comment faire pour gagner du temps d'exécution?

PS: A chaque ligne de mon fichier 1 je fais donc un filtre sur le fichier2 pour un identifiant donné. Donc à chaque ligne je vois le résultat du filtrage pendant l'exécution du programme. Est-il possible de mettre le fichier invisible pour gagner le temps d'affichage?

Merci,

Laurent

[BrunoM45]

Salut,

Pourquoi ne pas utiliser la fonction "Find" d'Excel !
Voilà ce que j'utilise dans mes macros :

 
Function LigFind(Feuil As String, NumCol As Integer, Quoi)
  On Error Resume Next
  With Sheets(Feuil).Columns(NumCol)
      ' On recherche une valeur
      LigFind = .Find(What:=Quoi, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False).Row
  End With
  On Error GoTo 0
End Function

Cela me retourne le numéro de la ligne trouvée et 0 si rien trouvé

A adapter selon les besoins

[Gael]

Bonjour Viscere, bonjour Bruno,

Pourquoi ne pas utiliser les tables en mémoire qui sont très simple à mettre en oeuvre et avec des temps de traitement extremement rapides.

Par exemple:

Dim Tablo as variant
Tablo = Sheets("Feuil1").Range("a3").CurrentRegion

Ce code crée un tableau à 2 dimensions qui contient toute la plage de données correspondante de "Feuil1".

Après, tu travaille avec Tablo(i,j) dans des boucles et les recherches seront beaucoup plus rapides.

@+

Gael

[viscere]

Merci pour ces astuces. Pour le tableau si je veux plus d'une colonne par exemple je voudrais la colonne A et B c'est possible?

[viscere]

Bon je gagne pas mal de temps avec la solution du tableau mais c'est pas encore ca!! Je vais tester le find.

[viscere]

Bon résultat des courses ca va largement plus vite avec Find. Néanmoins je n'arrive pas à chercher par valeur et non par chaine de caractère.

Je m'explique mon champs de recherche peut etre soit de la forme :

000025458487
soit 25458487

Avant quand je comparais dans ma boucle je pouvais faire

if Val(X) = Val(Quoi) then .....

Mais dans la fonction Find comment puis je faire?

[viscere]

C'est bon il fallait juste que je mette le paramètre lookAt à XlPart et non XlWhole.

Donc pour résumer

méthode Find = 12 sec
méthode Tableau 55 sec
boucles (non calculée mais encore plus long)

Merci à tous

[BrunoM45]

Salut Viscere,

La fonction ".Find" est en fait la méthode "Rechercher" d'Excel, donc ultra rapide !

Tous les autres codes font des recherches séquentielles, donc forcément plus long

A+

[Gael]

Bonjour à tous,

Je ne pensais pas que la fonction Find était plus rapide, pourtant je l'utilise de temps en temps. du coup j'ai récupéré le code de Bruno qui me paraît plus simple que mon code (merci Bruno!).

Par contre, Viscere, si tu pouvais joindre les codes de tests que tu as réalisé, ce serait sympa.

Merci et @+

Gael

[viscere]

Set Cellule = ListeExhauWorkBook.Sheets("Liste").Range("A2")
Set CelluleTemp = FeuilleTemp.Range("A1")

Dim i As Integer
i = 0

Do While Cellule.Offset(i) <> ""
       
    CelluleTemp.Offset(i, 9) = RenvoieDonneeEta1(Cellule.Offset(i, 7), Eta1Workbook, "B")     i = i + 1
    
Loop

Function RenvoieDonneeEta1(NumSej As String, Eta1Workbook As Workbook, IdCell As String) As String

    Dim i As Integer
    
    i = 0
    
    i = LigFind("eta1", 1, NumSej)
    
    If (i <> 0) Then
    
        RenvoieDonneeEta1 = Eta1Workbook.Sheets("eta1").Range(IdCell & i)
        Exit Function
    
    End If
    
    RenvoieDonneeEta1 = ""

End Function

j'ai effacé les versions boucles et tableau mais c tout simple. tu parcours ta feuille (ou ton tableau) et tu test si la valeur recherché est présente dans la case voulue.

[Gael]

Rebonjour à tous,

J'ai fait quelques essais de vitesse de recherche et de mise à jour que tu trouveras dans le fichier joint. Il est limité à 1000 références pour que sa taille soit en dessous de 50k mais tu peux le compléter.

Je l'ai créé de la façon suivante:

1 - Création d'une suite de 1000 références de A1000 à A2000 en Feuil1 colonne A
2 - Copie de ces 1000 références en Feuil2 colonne A
3 - Création de 1000 valeurs aléatoires en feuil1 colonne B
4 - Copier collage valeurs pour supprimer la fonction aléatoire
5 - Tri de la feuil1 par valeurs pour que les références soient dans un ordre quelconque.

Les 2 procédures font la même chose, l'une avec des tables mémoire et l'autre avec l'instruction Find:

Recherche de chaque code de la feuil 2 dans la feuille 1, récupération da la valeur correspondante et mise à jour en feuil 2 colonne B ou C selon la procédure (avec enregistrement des heures début et fin)

Sur 1000 références, les 2 procédures s'exécutent en 1 seconde environ mais sur 20000 enregistrements, l'utilisation de tables est nettement plus rapide qu'avec .find.

Par ailleurs on peut bien sûr optimiser les temps de réponse en triant les tables et en créant des index intermédiaires.

Qu'en penses-tu et cela ressemble-t-il à ton problème?

@+

Gael

[viscere]

Merci gael pour cet exemple de test complet. J'ai essayé avec un fichier de 14000 lignes de code.

tab 7s
find 8s

Mon find dans mon programme 12s

Vu qu'à chaque itération de ma première boucle je vais récupérer une dizaine de valeur et non une et surtout que ma première boucle je la fais du style

Do While Cellule.Offset(i) <> ""

, la différence est peut être là.

Je vais tester en mettant mon premier fichier dans un tableau et en bouclant sur des find...

En tout cas merci.

Laurent

[viscere]

Bon ben pour mon cas ca marche mieux avec Find je met 12 s contre 27 sec en travaillant avec 2 tableaux.

Merci à tous!