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

Question

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

ucfoutu · Accepted Answer

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

cs_Jack · Answer

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)

cs_Jack · Answer

dicotomique --> pyramidal

ucfoutu · Answer

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

ucfoutu · Answer

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

ucfoutu · Answer

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

ucfoutu · Answer

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

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

7 réponses

Votre réponse

Discussions similaires