[trucs et astruces] L'union fait-elle toujours la force ? [Résolu]

Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
- - Dernière réponse : ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
- 10 févr. 2010 à 11:26
Bonjour,

Le titre donné peut vous avoir surpris, mais il est parfaitement adapté.
Nous allons en effet parler de la fonction Union de VBA et ... voir comment il est parfois plus judicieux de diviser pour mieux régner !
Les constations qui suivent sont le résultat d'un décorticage que je fais de VBA (que j'apprends à connaître). La nécessité d'un tel décorticage ? ===>> je m'exerce à la fabrication de fonctions diverses...
J'ai voulu vous faire partager cette petite étude qui conduira certains d'entre vous à prendre des décisions salutaires

Nous avons dit "diviser pour mieux régner ..." ? ===>> voyons donc ===>>

Dim deb As Single, i As Integer, nb As Integer
deb = Timer
Dim toto As Range ' un range unique de destination
tra = 4500 ' le nombre que l'on va traiter
ReDim vla(tra \ 100 + 1) As Range ' <<<<<<======== on prépare un tableau de ranges de destination
For i = 1 To tra Step 2 ' un pas de 2 pour éviter des cellules contigües (et forcer union à ne pas les joindre ensemble)
    nb = (i \ 100)
    If vla(nb) Is Nothing Then Set vla(nb) Cells(i, 1) Else Set vla(nb) Union(Cells(i, 1), vla(nb))
Next
MsgBox Timer - deb & " secondes en traitant sur un plusieurs ranges de destination" & vbCrLf & "voyons les résultats"
nb = 0
For i = 0 To UBound(vla)
  If Not vla(i) Is Nothing Then
    'MsgBox vla(i).Count & " dans vla(" & i & ")"
    nb = nb + vla(i).Count
    Set vla(i) = Nothing
  End If
Next
MsgBox "pour un total de " & nb
'============================================================

MsgBox "on va maintenant traiter d'un coup" & vbCrLf & "préparer vous à un temps énormément plus long"
deb = Timer
For i = 1 To tra Step 2 ' un pas de 2 pour éviter des cellules contigües (et forcer union à ne pas les joindre ensemble)
   If toto Is Nothing Then Set toto Cells(i, 1) Else Set toto Union(Cells(i, 1), toto)
Next
MsgBox Timer - deb & " secondes en traitant d'un sur un seul range de destination" & vbCrLf & "voyons le résultat"
MsgBox "pour un total de " & toto.Count


Parlant, non ?

____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée" en bas d'une solution adéquate est
Afficher la suite 

Votre réponse

7 réponses

Meilleure réponse
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
3
Merci
Bonne nouvelle :
J'y parviens maintenant en environ 3 secondes (presque la même vitesse que celle de RemoveDuplicates d'Excel 2007) pour 60000 articles traités et un pourcentage énorme de doublons à supprimer (près de 90 %).
Pensez-vous que l'on peut aller encore plus vite (et comment ?).
Si réponses négatives suffisamment nombreuses ===>> je déposerai une source commentée à ce propos, après l'avoir nettoyée, améliorée (je compte donner une option : choix entre retenir les 1ers singletons ou, au contraire, les derniers de la liste ... on pourrait en effet considérer qu'il convient mieux de garder les plus anciens ou, au contraire, qu'il convient mieux de garder les plus récents).
Avez-vous une réponse (quant à l'amélioration éventuelle de la rapidité) et quant à l'opportunité de l'ajout d'une telle option ?


____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée" en bas d'une solution adéquate est

Dire « Merci » 3

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

Codes Sources a aidé 102 internautes ce mois-ci

Commenter la réponse de ucfoutu
Messages postés
14010
Date d'inscription
samedi 29 décembre 2001
Dernière intervention
28 août 2015
0
Merci
Salut
Je ne suis un as de Excel, mais une remarque :
Oui, les deux méthodes présentent une différence de temps d'exécution importante, mais on n'a pas exactement le même résultat non plus.
Dans le premier cas, on obtient un tableau de petits Ranges, et dans le deuxième cas, un gros et unique Range.
Le but n'est donc pas le même.

Pour obtenir le même résultat que dans le second cas (gros Range), on pourrait je pense gagner du temps en utilisant une méthode dicotomique :
J'ai 4500 cellules indépendantes à unifier.
A = Union de 1 et 2
B = Union de 3 et 4
C = Union A et B

D = Union de 5 et 6
E = Union de 7 et 8
F = Union D et E

G = Union C et F
etc

Vala
Jack, MVP VB
NB : Je ne répondrai pas aux messages privés

Le savoir est la seule matière qui s'accroit quand on la partage (Socrate)
Commenter la réponse de cs_Jack
Messages postés
14010
Date d'inscription
samedi 29 décembre 2001
Dernière intervention
28 août 2015
0
Merci
dicotomique --> pyramidal
Commenter la réponse de cs_Jack
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
0
Merci
Bonjour, Jack,

Tu "brûles" ....
Mais je laisse les choses avancer un peu avant d'aller plus loin et de faire des comparaisons utiles, quoique assez subjectives.
On verra alors comment tout peu dépendre de facteurs assez diverses et quelle place est laissée au développeur en fonction de ce qu'il "sent" ...


____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée" en bas d'une solution adéquate est
Commenter la réponse de ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
0
Merci
Bon me revoilà avec une petite expérience que je propose aux plus courageux.
Ce petit test est fait sur 1510 cellules seulement (cela immobilisera votre PC de 1 à 2 minutes au total, selon les performances de votre PC et les tâches qu'il accomplit par ailleurs)
4 méthodes s'enchaînent (et sont commentées pour que l'on puisse les identifier)
Les résultats montrent que la meilleure durée est celle de la méthode 3 (par petits paquets, et sans les réunir ensuite)
On voit également que la seconde meilleure durée est obtenue par la méthode 4 (un seul paquet reconstitué à partir de petits paquets préalablement constitués, comme en méthode 3). Il est à noter que l'on pourrait penser que l'on devrait alors retrouver la même durée que pour la méthode 2 ... hé bien non !)
La méthode 2 vient en 3ème rang seulement
La méthode 1 (méthode traditionnelle) s'avère la plus lente.
Le code du test, d'abord :

Private Sub CommandButton1_Click()
  Dim deb As Single, i As Integer, temps1 As Single, temps2 As Single, temps3 As Single, tra As Integer
  tra = 1510
  Application.ScreenUpdating = False
  'méthode 1 : suppression ligne par ligne
  deb = Timer
  'Application.ScreenUpdating = False
  For i = tra To 1 Step -2 ' un pas de 2 pour éviter des cellules contigües (et forcer union à ne pas les joindre ensemble)
    Cells(i, 1).EntireRow.Delete
  Next
  'Application.ScreenUpdating = True
  temps1 = Timer - deb
  deb = Timer
  ' méthode 2 : un seul rage par union et suppression in fine
  deb = Timer
  Dim toto As Range ' un range unique de destination
  For i = 1 To tra Step 2  ' un pas de 2 pour éviter des cellules contigües (et forcer union à ne pas les joindre ensemble)
     If toto Is Nothing Then Set toto Cells(i, 1) Else Set toto Union(Cells(i, 1), toto)
  Next
  toto.EntireRow.Delete
  temps2 = Timer - deb

  'méthode 3 : division en plusieurs Range par Union et suppression in fine
  Dim nb As Integer, ratio As Integer
  deb = Timer
  ratio = 20
  ReDim vla(tra \ ratio + 1) As Range ' <<<<<<======== on prépare un tableau de ranges de destination
  For i = 1 To tra Step 2 ' un pas de 2 pour éviter des cellules contigües (et forcer union à ne pas les joindre ensemble)
    nb = (i \ ratio)
    If vla(nb) Is Nothing Then Set vla(nb) Cells(i, 1) Else Set vla(nb) Union(Cells(i, 1), vla(nb))
  Next
  nb = 0
  For i = UBound(vla) To 0 Step -1
    If Not vla(i) Is Nothing Then
      vla(i).EntireRow.Delete
      Set vla(i) = Nothing
    End If
  Next
  temps3 = Timer - deb
  Application.ScreenUpdating = True
'méthode 4 : division en plusieurs Range par Union>> vers un seul ensuite ==>> et suppression in fine ===========
  deb = Timer
  ratio = 10
  ReDim vla(tra \ ratio + 1) As Range ' <<<<<<======== on prépare un tableau de ranges de destination
  For i = 1 To tra Step 2 ' un pas de 2 pour éviter des cellules contigües (et forcer union à ne pas les joindre ensemble)
    nb = (i \ ratio)
    If vla(nb) Is Nothing Then Set vla(nb) Cells(i, 1) Else Set vla(nb) Union(Cells(i, 1), vla(nb))
  Next
  nb = 0
  If Not vla(0) Is Nothing Then
    For i = 1 To UBound(vla)
      If Not vla(i) Is Nothing Then
        Set vla(0) = Union(vla(i), vla(0))
        Set vla(i) = Nothing
      Else
        Exit For
      End If
    Next
  End If
  vla(0).EntireRow.Delete
  temps4 = Timer - deb
  Application.ScreenUpdating = True
  MsgBox "méthode 1 ===>> " & temps1 & vbCrLf & "méthode 2 ===>> " & temps2 & _
  vbCrLf & "méthode 3 ===>> " & temps3 & vbCrLf & "méthode 4 === >> " & temps4
End Sub


Mes conclusions "pour l'instant", ensuite :
- En " écriture ", la fonction Union est ralentie au fur et à mesure que la plage résultante s'accroit. L'écriture de petits paquets est très nettement plus rapide.
- on gagne certes à l'écriture, mais on ne peut, bien évidemment, gagner à l'exploitation de la ou des plage(s), ce qui explique la faible différence entre les 4 résultats.

Amusez-vous maintenant à modifier la valeur de la variable ratio ===>> résultats différents, pas toujours en faveur de la méthode 3 ....

Je ne veux par ailleurs pas immobiliser votre PC en traitant un très grand nombre de cellules (variable tra). Sachez cependant que je me suis prêté à cette expérience (plusieurs, en fait, en modifiant également la variable ratio) et là ====>> les différences de durée sont plus nettes.
Nota bene : vous avez pu noter (mon step 2) que je me suis efforcé d'imposer à la fonction Union des cellules non contigües0 Le seraient-elles, en effet, que tout serait bouleversé dans les durées, puisque la fonction Union regroupe celles qui sont contigües ici et là, avec alors pour effet de favoriser la méthode 2 )

Qu'en dire ? utiliser la méthode 2 chaque fois que traitement avec des contigüités. Utiliser la méthode 3 dans les autres cas. Tacher de trouver le bon "ratio" (parait être d'enviiron 10 à 2% du nombre de cellules à traiter... et là .... je ne vois rien d'autre que le flair en fonction de ce que l'on connaît de ses données et de ce qu'on veut en faire)

Notez également que j'ai choisi d'exploiter par suppression des lignes. Je n'ai pas encore tenté de voir les réactions en matière d'attribution d'une valeur à la plage à traiter.

Voilà ... je vous ai livré là l'état actuel de mes tests...
A vous de voir ...




____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée" en bas d'une solution adéquate est
Commenter la réponse de ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
0
Merci
Ce qui "bouffe" l'essentiel du temps, ce sont les EntireRow.Delete (et ce : quelle que soit la méthode utilisée, parmi les trois précédentes)...
Cela se fait sacrément sentir lorsque l'on a quelques milliers de lignes à supprimer (de très longues minutes si le nombre de lignes à supprimer est de plusieurs dizaines de milliers et que les lignes en causes sont réparties un peu partout (ce sera la plupart du temps vrai en ce qui concerne la suppression des doublons, par exemple . Que l'on ait 12000 doublons à supprimer parmi 60000 lignes de données, et c'est une catastrophe !)...
Ainsi donc, toutes les méthodes connues pour recense les doublons (même en utilisant la rapidité d'un dictionnary), ne serviront qu'à économiser une goutte d'eau comparée à l'immense océan à dépenser pour procéder à leur suppression (une fois recensés !).
Et pourtant : Excel 2007 possède une fonction (RemoveDuplicates) qui, elle, est très rapide !
Les versions inférieures ne possèdent malheureusement pas cette fonction et il convient donc de la remplacer, mais par une méthode, sinon aussi rapide, tout au moins beaucoup moins pénalisante que celles généralement mises en oeuvre pour faire face)
J'en suis là à chercher et j'ai déjà fait dans ce sens un énorme pas (temps d'exécution déjà divisé par plus de 100 , ce qui veut dire, par exemple : 10 secondes au lieu de 17 minutes pour traiter 60000 lignes "dotées" de plus de 25 % de doublons, le tout sur 3 colonnes).
Le "pas" est certes appréciable et je vais continuer dans ce sens.
D'autres ont-ils déjà tenté de se frotter à ce genre de problème ? Ont-ils réussi à aller encore plus vite ? Si oui, peut-on connaître leur propre méthode ?
Pour que tout soit clair ; une précision : supprimer les doublons sans bouleverser par un tri préalable (sinon, bien évidemment, la donne est différente !)
_______________

Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée" en bas d'une solution adéquate est
Commenter la réponse de ucfoutu
Messages postés
18039
Date d'inscription
lundi 7 décembre 2009
Statut
Contributeur
Dernière intervention
11 avril 2018
0
Merci
Bingo !
moins de 1,5 secondes pour éliminer les doublons (sur valeurs de 3 colonnes) répartis aléatoirement sur 60000 lignes remplies et en nombre élevé (90 % ... en rappelant que moins ce pourcentage est élevé, plus rapide est l'exécution).
C'est carrément là la même vitesse que celle de la fonction RemoveDuplicates de la version 2007 ! (en rappelant que toutes les méthodes connues et jusqu'à présent utilisées nécessitent plusoieurs dizaines de minutes dans le même cas !)
Je ne vais donc pas chercher à améliorer encore plus.
Il me reste à préparer mon ^petit exposé, mes commentaires, et ===>> à déposer !



____________________
Vous aimez Codes-Sources ? Il vous aide ? Cliquez ici pour l'aider à continuer
Cliquer sur "Réponse acceptée" en bas d'une solution adéquate est
Commenter la réponse de ucfoutu

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.