XL 2021 Tri Excel vs Macro

[claudio54]

Bonsoir le forum
Je gère une base de données très conséquente (47000 lignes sur 26 colonnes). Les cellules de la base de données sont triées par les colonnes 1, 2 et 3 (dans cet ordre) par le bouton "tri personnalisé" dans le ruban menu. Ensuite je transfère tout le tableau en mémoire (Tablo=range("A1:Z47000").value) ce qui ne pose pas de problème. Au cours de l'exploitation du programme, je suis souvent amené à faire des tris du tableau dynamique Tablo (pas dans la feuille mais en mémoire). Pour faire ces tris, j'utilise une macro trouvée sur le net dont j'ignore l'auteur (peut-être un membre d'Excel Download ?) Le programme fait ce qu'il doit faire avec les données et par la suite je dois refaire le tri de Tablo avec comme critères les colonnes 1, 2 et 3 dans cet ordre. En principe le Tablo devrait être identique à l'origine (image de la feuille) .... eh ben non ! Il y a pas mal de différences.
Un petit exemple en pièce jointe vaut mieux qu'un long discours. C'est un tout petit fichier, cliquer sur un bouton pour générer une base de donnée (10 lignes x 10 col). Puis cliquer sur le bouton pour qu'excel fasse son tri personnalisé. En cliqunat le dernier bouton, la base est transférée dans un tablo T puis triée avec les mêmes critères et affichéé. Et la on voit les différences. Une aide serait la bien venue.
Merci

 

[Eric KERGRESSE]

Bonjour,

Votre code de tri, c'est du regretté Jacques BOISGONTIER dans le texte.

Sinon, j'ai fait un essai en remplissant les cellules vides avec les caractères ZZ dans les 3 premières colonnes et voilà ce que cela donne :

En résumé, on remplit les cases vides avec une chaine statistiquement impossible de se trouver dans les données initiales et on les remplace par "rien" à la fin du tri.

 

[claudio54]

Bonjour, merci pour votre réponse.
Effectivement dans ma précipitation à faire un petit fichier exemple, je n'ai pas fait attention qu'il pouvait y avoir des cellules vides. J'ai modifié le petit fichier pour que ça ne se reproduise pas. Cette fois dans la pièce jointe, j'ai généré un tableau avec des valeurs quelconques. En faisant trier ce tableau par les colonnes A, B, C que ça soit par excel ou par le tri multiple, pas de différence. Si on change le premier critère de tri G au lieu de A par exemple là, il y a une différence entre les 2 tris.
Je joint le nouveau fichier et un copie écran.

 

[Eric KERGRESSE]

J'ai regardé comment se faisait le tri dans le tableau le tableau de référence. Je serais plutôt parti en concaténant les colonnes G, B et C dans une colonne K et en triant la colonne K.

Tableau trié par la méthode VBA

Tableau trié après concaténation des colonnes G, B, C et en triant la colonne K

J'ai appliqué la même méthode de concaténation et de tri à partir de Power Query.

On a un écart entre les deux derniers tableaux, je pense que dans Power Query, on prend strictement l'ordre des caractères dans la table des caractères, le à correspond au chr(224) donc après le caractère k Chr(107).

 

[Eric KERGRESSE]

Si la solution Power Query vous a convaincu, je serais curieux de savoir en combien de temps vous obtenez le résultat sur votre base de travail.

 

[claudio54]

Malheureusement, je ne connais pas Power Query et pour tout vous dire c'est la première fois que j'en entends parler. Je pense comme vous que les différences sont dues aux caractères spéciaux. J'ai même constaté qu'une variable avec un espace se comporte anormalement au cours des tris. La concaténation apparait comme une piste, il faut que je regarde le temps nécessaire à concaténer 3 colonnes sur plus de 47000 lignes. Je vais essayer et je reviendrai mettre le temps en espérant que cela fonctionne.

 

[claudio54]

Test effectué sur ma base:
- temps de concaténation de 3 colonnes = 0,0423 s
- tri sur données concaténées : 0,418 s
Les temps ne sont pas du tout pénalisant pour l'application. Je précise toutefois que cela se fait dans la mémoire (array) car la base sur la feuille excel doit rester intacte. Toutes les manips de tri, de recherche se font dans le tableau dynamique et reproduites dans des listview sur userform.
Le résultat cependant n'est toujours pas satisfaisant. Je vais continuer à chercher à comment y remédier. Si toutefois vous trouvez une solution, bien évidemment je serai preneur.

 

[Eric KERGRESSE]

Malheureusement, je ne connais pas Power Query et pour tout vous dire c'est la première fois que j'en entends parler. Je pense comme vous que les différences sont dues aux caractères spéciaux. J'ai même constaté qu'une variable avec un espace se comporte anormalement au cours des tris. La concaténation apparait comme une piste, il faut que je regarde le temps nécessaire à concaténer 3 colonnes sur plus de 47000 lignes. Je vais essayer et je reviendrai mettre le temps en espérant que cela fonctionne.

Pour le temps de traitement, je pensais plutôt à votre base de 47000 enregistrements 😊

En ce qui concerne la solution, à votre place j'opterais pour Power Query, il n'y a pas besoin de VBA et il suffit d'actualiser la requête pour mettre à jour le résultat si votre base de données change. La requête ne modifie en rien votre base de données. Elle contient les différentes étapes des transformations. Pour y accéder : Données, Requêtes et connexions, clic droit sur le nom dans la fenêtre de droite et modifier pour ouvrir l'éditeur Power Query.

 

[claudio54]

Les temps indiqués sont pour ma base qui compte 47724 lignes et 27 colonnes. En manipulant les tableaux ça va nettement plus vite qu'avec les cellules.
Je pense que la majeure partie de mon problème vient des espaces dans les noms. A titre d'exemple, voir le fichier test (1 seule colonne de 10 noms). Le tri par macro met les noms composés avant les noms simples (même racine) et excel fait l'inverse.
Sinon effectivement je vais voir pour m'informer sur Power Query (apprendre ne fait jamais de mal). Je reviendrai sur le fil pour vous tenir au courrant.

 

[claudio54]

Les temps indiqués sont pour ma base qui compte 47724 lignes et 27 colonnes. En manipulant les tableaux ça va nettement plus vite qu'avec les cellules.
Je pense que la majeure partie de mon problème vient des espaces dans les noms. A titre d'exemple, voir le fichier test (1 seule colonne de 10 noms). Le tri par macro met les noms composés avant les noms simples (même racine) et excel fait l'inverse.
Sinon effectivement je vais voir pour m'informer sur Power Query (apprendre ne fait jamais de mal). Je reviendrai sur le fil pour vous tenir au courrant.

Problème contourné en concaténant les colonnes à trier.