Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
2255 connectés 

  FORUM HardWare.fr
  Programmation
  VB/VBA/VBS

  besoin de gagner du temps sur le cacul de recherche dans gros fichier

 
 


 Mot :   Pseudo :  
 
Bas de page
Auteur Sujet :

besoin de gagner du temps sur le cacul de recherche dans gros fichier

n°2278817
trident80
Posté le 02-04-2016 à 19:23:49  profilanswer
 

Bonjour,
 
Je cherche à optimiser une procédure vba sur un très gros fichier excel qui met plusieurs heures pour s'éxecuter.
 
J'ai dans ce fichier plusieurs feuilles relativement grosses, et je dois effectué plusieurs recherchev avec plusieurs conditions pour renseigner des colonnes dans l'onglet principal.
 
Le nombres de lignes à renseigner (entre 65000 et 100000 lignes) ainsi que le nombres de colonnes a renseigner (une vingtaine environ) rend cette procédure très longue (plusieurs heures  :cry:  :sleep:  )...  
 
Je souhaite donc optimiser mon code afin de gagner le plus de temps possible, j'ai effectué plusieurs recherche sur ce forum et sur d'autres et j'ai trouvé des pistes qui me sembles intéressante mais mon niveau en VBA ne me permet pas de savoir si ces pistes seront ou non adapter à mon cas, ni laquelle serait la plus rapide à s'exécuter.
 
Parmi les pistes que j'ai noté les deux qui me semble le plus adaptées seraient: "Les variables tableaux" et/ou "Les Dicos".
 
Mes questions:
 
Pensez-vous que ces méthodes sont adaptées à ma situation?  
 
Laquelle serait la plus efficace en temps de traitement? Pourquoi?
 
Existe t'il une autre méthode qui me ferait gagner plus de temps?
 
Selon la méthode la plus adaptée a ma situation, si vous avez des liens ou tutoriel afin d'apprendre et creuser le sujet je veux bien, sinon je chercherai...
 
Je vous joint ci-dessous une partie de "mon problème": une des 23 colonnes que je dois remplir. J'adapterai ensuite la solution retenue sur tout mon code, pour le test une seule colonne suffira bien!!  :)  
 
Voici donc un exemple concret d'une des colonnes de l'onglet principal que je dois remplir et les deux méthodes que je connaissent actuellement pour y parvenir:
 
Fichier Excel (avec les deux sub ci-dessous intégrées): http://www.cjoint.com/c/FDcrmAtfCwY
 
Code 1 procedure Formule Excel:
 
 

Code :
  1. Sub BofBof()
  3.    Dim Max_Ana As Long 'Déclaration variable ligne max
  5.     Max_Ana = Sheets("Analyse" ).Cells(1048576, 2).End(xlUp).Row 'définition de la ligne max
  6.     Application.Calculation = xlCalculationManual 'désactivation du calcul automatique
  7.     Application.ScreenUpdating = False 'desactivation du rafraichissement
  9.     Sheets("Analyse" ).Cells(2, 71).FormulaLocal = "=SIERREUR(SI(AZ2=""001S"";""X"";RECHERCHEV(M2;Rapport1!$A$1:$Z$3324; 9; FAUX));""Non trouvé"" )"   'copie de la formule dans la cellule BS2
  10.     Sheets("Analyse" ).Cells(2, 71).Select 'selection de la cellule BS2
  11.     Selection.AutoFill Destination:=Range("BS2:BS" & Max_Ana), Type:=xlFillDefault   'incrémentation de la formule jusqu'au bas de la colonne BS
  12.     Calculate 'Calcul de la formule sur toute la colonne
  13.     Sheets("Analyse" ).Range("BS2:BS" & Max_Ana).Copy 'Copier la colonne
  14.     Sheets("Analyse" ).Range("BS2:BS" & Max_Ana).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Coller les valeurs sans les formules
  15.     Sheets("Analyse" ).Range("BS:BS" ).Select 'Selection de toute la colonne
  16.     Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 'Remplacer les 0 par vide
  18.     Application.Calculation = xlCalculationAutomatic 'Activation du calcul automatique
  19.     Application.ScreenUpdating = True 'Activation du rafraichissement
  21. End Sub


 
Code 2 procédure VBA:
 

Code :
  1. Sub encorebof()
  3.     Dim i, j As Long 'lignes
  4.     Dim Max_Ana, Max_Rap As Long 'lignes max
  5.     Dim Ana13 As String 'reference de recherche
  6.     Dim FlagOk As Boolean
  8.     Max_Rap = Sheets("Rapport1" ).Range("A1048576" ).End(xlUp).Row 'Definition des lignes max
  9.     Max_Ana = Sheets("Analyse" ).Range("B1048576" ).End(xlUp).Row
  10.     Application.Calculation = xlCalculationManual 'désactivation du calcul automatique
  11.     Application.ScreenUpdating = False 'desactivation du rafraichissement
  12.    
  13.         For i = 2 To Max_Ana
  15.                 If Sheets("Analyse" ).Cells(i, 52).Value = "001S" Then 'Si "001S" en colonne AZ de la feuille Analyse
  16.                     Sheets("Analyse" ).Cells(i, 71).Value = "X" 'Je met "X" en colonne BS de la feuille Analyse, je passe a la ligne suivante
  17.                     Else 'Si ce n'etait pas "001S" en AZ alors
  18.                     Ana13 = Sheets("Analyse" ).Cells(i, 13).Value 'je stocke la valeur de M dans ma variable Ana13
  19.                     FlagOk = False 'je met mon drapeau sur faux
  20.                         For j = 2 To Max_Rap 'J'entre dans la recherche de Ana13
  21.                             If Sheets("Rapport1" ).Cells(j, 1).Value = Ana13 Then 'Si je trouve la valeur de Ana13 dans la colonne A de ma feuille Rapport1
  22.                                 Sheets("Analyse" ).Cells(i, 71).Value = Sheets("Rapport1" ).Cells(j, 9).Value 'Alors je copie ma valeur en colonne BS
  23.                                 FlagOk = True 'mon drapeau passe sur vrai
  24.                         Exit For 'On sort de la recherche
  25.                             End If
  26.                         Next j
  27.                     If FlagOk = False Then 'Si le drapeau est toujours sur faux et que Ana13 n'a pas ete trouvee en colonne A de ma feuille Rapport1
  28.                     Sheets("Analyse" ).Cells(i, 71).Value = "Non trouvé" 'Alors je met "Non trouve" en BS de la feuille Analyse
  29.                     End If
  30.                 End If
  31.         Next i
  32.        
  33.     Application.Calculation = xlCalculationAutomatic 'Activation du calcul automatique
  34.     Application.ScreenUpdating = True 'Activation du rafraichissement
  35. End Sub


 
 
Je vous remerci d'avance pour vos suggestions, messages, remarques, critiques... Toute aide est la bienvenue!  :jap:

mood
Publicité
Posté le 02-04-2016 à 19:23:49  profilanswer
 

n°2279442
galopin01
Posté le 14-04-2016 à 06:08:53  profilanswer
 

bonjour,
Les tableaux devraient surement faire l'affaire...
Sous réserve que je n'ai pas trop mal interprété, la procédure suivante devrait faire l'affaire.  
Durée d'exécution 14", mais comme je n'ai aucune formule il n'y a pas de recalcul.
Si tu recalcules ta feuille tu auras en plus le temps de recalcul habituel...  

Code :
  1. Sub galopin()
  2. Dim i&, j&, iA&, iR&, Y As Boolean
  3. Dim WsA As Worksheet, WsB As Worksheet
  4. Dim a, aa, b
  5. Dim Ana13$ 'reference de recherche
  7. Set WsA = Sheets("Analyse" )
  8. Set WsB = Sheets("Rapport1" )
  10. iA = WsA.Range("B1048576" ).End(xlUp).Row
  11. iR = WsB.Range("A1048576" ).End(xlUp).Row
  13. Application.Calculation = xlCalculationManual
  14. Application.ScreenUpdating = False
  16. a = WsA.Range("AZ1:AZ" & iA)  'Colonne de recherche AZ(52)
  17. aa = WsA.Range("M1:M" & iA)
  18. b = WsB.Range("A1:A" & iR)
  20.    For i = 2 To iA
  21.      If a(i, 1) = "001S" Then
  22.          WsA.Cells(i, 71) = "X" 'Je met "X" en colonne BS
  23.      Else
  24.         Ana13 = aa(i, 1)             'je stocke la valeur de M dans Ana13
  25.         Y = False
  26.           For j = 2 To iR 'J'entre dans la recherche de Ana13
  27.               If b(j, 1) = Ana13 Then 'Si je trouve Ana13 dans b
  28.                  WsA.Cells(i, 71) = WsB.Cells(j, 9)  'Alors je copie ma valeur en colonne BS
  29.                  Y = True
  30.                  Exit For
  31.               End If
  32.           Next j
  33.           If Y = False Then WsA.Cells(i, 71) = "Non trouvé"
  34.      End If
  35.    Next i
  37. Application.Calculation = xlCalculationAutomatic
  38. End Sub


Hum... Je n'ai laissé que les commentaires utiles (pour moi)
A+


Message édité par galopin01 le 14-04-2016 à 06:11:30
n°2279523
rufo
Pas me confondre avec Lycos!
Posté le 15-04-2016 à 10:05:22  profilanswer
 

Est-ce que changer d'architecture et passer sur un SGBD (SGBDR ou NoSQL) pour le stockage des données serait envisageable ? Parce que vu le volume, tout faire avec Excel ne me semble pas raisonnable. :/


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta

Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  VB/VBA/VBS

  besoin de gagner du temps sur le cacul de recherche dans gros fichier

 

Sujets relatifs
simulation de temps de réponse de file d'attente m/m/1 en foction de tRecherche de données
Recherche valeur dans classeurparseur et fichier
recherche dans un tableau.Convertir fichier PDF en fichier txt
recherche de code pour, par clic sur bouton, poser correctement l'autilisation d'un fichier d'initialisation
Lire fichier excel écrire dans bddImpact modification "type" d'un fichier
Plus de sujets relatifs à : besoin de gagner du temps sur le cacul de recherche dans gros fichier

Forum MesDiscussions.Net, Version 2010.2
(c) 2000-2011 Doctissimo
Page générée en 0.121 secondes

Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR