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

besoin de gagner du temps sur le cacul de recherche dans gros fichier - VB/VBA/VBS - Programmation

Marsh Posté le 02-04-2016 à 19:23:49    

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:

Reply

Marsh Posté le 02-04-2016 à 19:23:49   

Reply

Marsh Posté le 14-04-2016 à 06:08:53    

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
Reply

Marsh Posté le 15-04-2016 à 10:05:22    

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
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed