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()
  2.    Dim Max_Ana As Long 'Déclaration variable ligne max
  3.     Max_Ana = Sheets("Analyse" ).Cells(1048576, 2).End(xlUp).Row 'définition de la ligne max
  4.     Application.Calculation = xlCalculationManual 'désactivation du calcul automatique
  5.     Application.ScreenUpdating = False 'desactivation du rafraichissement
  6.     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
  7.     Sheets("Analyse" ).Cells(2, 71).Select 'selection de la cellule BS2
  8.     Selection.AutoFill Destination:=Range("BS2:BS" & Max_Ana), Type:=xlFillDefault   'incrémentation de la formule jusqu'au bas de la colonne BS
  9.     Calculate 'Calcul de la formule sur toute la colonne
  10.     Sheets("Analyse" ).Range("BS2:BS" & Max_Ana).Copy 'Copier la colonne
  11.     Sheets("Analyse" ).Range("BS2:BS" & Max_Ana).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 'Coller les valeurs sans les formules
  12.     Sheets("Analyse" ).Range("BS:BS" ).Select 'Selection de toute la colonne
  13.     Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 'Remplacer les 0 par vide
  14.     Application.Calculation = xlCalculationAutomatic 'Activation du calcul automatique
  15.     Application.ScreenUpdating = True 'Activation du rafraichissement
  16. End Sub


 
Code 2 procédure VBA:
 

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