Tri d'un tableau VBA à 2 dimensions multichamps

david84 · 3 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Re

La méthode de constitution d'une clé unique de tri à partir des champs sur lesquels le tri doit porter me semble aussi un peu différente, si j'ai compris tu concaténait les valeurs séparées par des # ce qui pose potentiellement problème pour les nombres, dates, nombres décimaux...

Même si tu as sûrement raison sur ce que tu dis, je ne te parlais pas de ma proposition en particulier.
Indépendamment du module de Dranreb, regarde également la proposition de Jacques : il me semble qu'elle utilise une méthode d'indexation des lignes et que sa proposition fonctionne correctement sur les dates (mais je vais me replonger dedans.

Concernant ton code, un premier test rapide : sur le tableau proposé dans ton fichier, le code fonctionne (à 1ère vue mais pas trop vérifié pour l'instant).
Si j'efface la colonne 5 et que je lance le code (donc sur 4 colonnes), il plante à

Code:

Donnee = IIf(BoolCasse, Tablo(LigTablo, ColTablo), UCase(Tablo(LigTablo, ColTablo)))

.
A+

tototiti2008 · 3 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Re,

Merci de ton retour,

Concernant ton code, un premier test rapide : sur le tableau proposé dans ton fichier, le code fonctionne (à 1ère vue mais pas trop vérifié pour l'instant).
Si j'efface la colonne 5 et que je lance le code (donc sur 4 colonnes), il plante à

Si tu l'as lancé tel quel, c'est assez logique

Code:

Call TriTablo(ere, Array(1, False, True, 5, True, False, 3, False, False))

Je trie mon tableau sur le 1er champ en décroissant sensible à la casse, le 5ème en croissant non sensible à la casse, puis le 3ème en décroissant non sensible à la casse
Sans 5ème champ...

regarde également la proposition de Jacques : il me semble qu'elle utilise une méthode d'indexation des lignes et que sa proposition fonctionne correctement sur les dates

Merci, je regarde ça

david84 · 4 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Bonjour Marc,

e trie mon tableau sur le 1er champ en décroissant sensible à la casse, le 5ème en croissant non sensible à la casse, puis le 3ème en décroissant non sensible à la casse
Sans 5ème champ...

Ok, j'ai compris !

J'ai testé ton fichier vite fait sur une base de données de plus 5000 lignes sur 4 colonnes pour comparer sa vitesse d'exécution avec les codes de Dranreb et JB (résultats sont identiques) : ceux de JB et de Dranreb : 0,20 sec, le tien : 3 sec.

Même si une comparaison de ce type ne veut pas dire grand chose puisque ton code offre d'autres possibilités de paramétrage, il me semble que tu pourrais peut-être gagner en vitesse de traitement en te passant de l'utilisation de la fonction Iif qui est très consommatrice de temps (jusqu'à 6 fois plus de temps de traitement selon certains car les 2 conditions sont évaluées) et en utilisant à la place un classique If...Then...Else par exemple.

C'est peut-être une piste à explorer.

A+

tototiti2008 · 4 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Bonjour à tous,

Merci David, nos échanges m'apportent exactement ce que j'attendais de ce fil, des pistes de reflexion et d'amélioration
J'utilise aussi une méthode d'indexation mais pour chaque champ, si j'ai compris Jacques en utilise une sur la concaténation des champs (ou alors je n'ai pas trouvé le bon code)
Je pense aussi qu'il va falloir que je regarde de plus près ces histoires de dates, je crois qu'il y a un cas que je n'ai pas pris en compte : s'il y a des heures, il faudrait que je les convertisse en double plutôt qu'en entier
J'essaierais de revenir avec une version modifiée du code

david84 · 4 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Re Marc,

Merci David, nos échanges m'apportent exactement ce que j'attendais de ce fil, des pistes de reflexion et d'amélioration

Pour l'instant, je préfère te donner des pistes pour "déblayer le terrain" plutôt que de rentrer dans le détail, comme cela tu gardes la main.
Une fois le code affiné selon ton désir, on pourra voir s'il y a moyen de l'affiner.

J'utilise aussi une méthode d'indexation mais pour chaque champ, si j'ai compris Jacques en utilise une sur la concaténation des champs (ou alors je n'ai pas trouvé le bon code)

C'est exact, mais je trouve que c'est bien vu car le problème ne se situe pas dans le tri dans l'array mais plutôt dans la restitution des données dans la feuille (la preuve avec ma proposition faite sur la discussion, si tu traites la date en clng, le tri se passe bien par contre les dates affichées dans la feuille de calcul ne sont pas formatées.

Le code de Jacques évite cet écueil car il se sert de l'index pour récupérer les données placé dans l'array de départ (si je me souviens bien).

Je te joins le code de Jacques mais je crois bien que ce n'est pas la version originale (je crois que je l'avais retravaillé de mon côté).

Code:

Sub TriTableau2D_4()
 T = Timer()
 Dim clé() As String, index() As Long
 Set Pl = [A1].CurrentRegion: Set Pl = Pl.Rows(2).Resize(Pl.Rows.Count - 1)
 a = Pl.Value
 Dim b()
 ReDim b(LBound(a) To UBound(a), LBound(a, 2) To UBound(a, 2))
 ReDim clé(1 To UBound(a, 1))
 ReDim index(1 To UBound(a, 1))
 
For i = 1 To UBound(a)
    For j = 1 To UBound(a, 2)
        Select Case VarType(a(i, j))
        Case Is = 5
            clé(i) = clé(i) & CLng(a(i, j))
        Case Is = 7
            clé(i) = clé(i) & Format(a(i, j), "00000")
        Case Else
            clé(i) = clé(i) & a(i, j)
        End Select
    Next j
    index(i) = i
Next i
 Call Tri(clé(), index(), 1, UBound(clé))
 For lig = 1 To UBound(clé)
   For col = 1 To UBound(a, 2): b(lig, col) = a(index(lig), col): Next col
 Next lig
 [J2].Resize(UBound(b), UBound(b, 2)) = b
 MsgBox Timer() - T
End Sub

Sub Tri(clé() As String, index() As Long, gauc, droi) ' Quick sort
   ref = clé((gauc + droi) \ 2)
   g = gauc: d = droi
   Do
      Do While clé(g) < ref: g = g + 1: Loop
      Do While ref < clé(d): d = d - 1: Loop
        If g <= d Then
           temp = clé(g): clé(g) = clé(d): clé(d) = temp
           temp = index(g): index(g) = index(d): index(d) = temp
           g = g + 1: d = d - 1
        End If
    Loop While g <= d
    If g < droi Then Call Tri(clé, index, g, droi)
    If gauc < d Then Call Tri(clé, index, gauc, d)
End Sub

J'essaierais de revenir avec une version modifiée du code

Ok. Ne lâche pas le morceau !
A+

tototiti2008 · 4 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Re,

Sur la méthode d'indexation, je pense que c'est un point important qui mérite que l'on s'arrête dessus
L'idéal est une méthode qui fonctionne quelles que soient les valeurs des champs et pour moi le problème se situe là :

Case Is = 5
clé(i) = clé(i) & CLng(a(i, j))
Case Is = 7
clé(i) = clé(i) & Format(a(i, j), "00000")
Case Else

comment formater un nombre qui peut aller de 1E308 à 1E-324 (limites des Double) pour qu'un tri textuel respecte l'ordre numérique de ces nombres ? à mon avis, pas possible d'où l'indexation de chaque champ : suppression des doublons du champ (dictionnaire), tri du dictionnaire et création du code unique en fonction de la position de la valeur dans le dictionnaire trié
Qu'en penses-tu ?

david84 · 4 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Re
Oui, j'ai bien compris ta position et dans l'absolu c'est vrai que c'est préférable de trier par champ plutôt que de concaténer mais même si c'est ce que tu fais, ton code également formate la valeur en fonction de son type :

If IsDate(Donnee) Then Donnee = CLng(CDate(Donnee))
If IsNumeric(Donnee) Then Donnee = CDbl(Donnee)

alors que le code de Dranreb, lui travaille également sans concaténation, mais sans besoin de formater les données puisqu'il travaille en variant il me semble.
Quant à l'utilisation de dictionnaire, je me suis posé la question sans chercher plus loin pour le moment.
Tu m'obliges à étudier ton code de plus près pour pouvoir me faire une idée plus précise.
Laisse-moi un peu de temps pour m'y plonger dedans.
A+

tototiti2008 · 5 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Bonjour à tous, Bonjour David,

ton code également formate la valeur en fonction de son type

C'est parce que les dictionnaires ne prennent pas bien en compte les valeurs des variant, je dois reconvertir la valeur

Quant à l'utilisation de dictionnaire, je me suis posé la question sans chercher plus loin pour le moment.

La question se discute, je passe par des dictionnaires pour supprimer les doublons en me disant qu'il y aura moins de valeur à trier, mais peut-être est-ce inutile en matière de gain de temps, à étudier....

Tu m'obliges à étudier ton code de plus près pour pouvoir me faire une idée plus précise

Non, on peut continuer à parler du point de vue théorique, je ne manquerais pas de t'expliquer les choix que j'ai fait dans mon code, pas la peine d'éplucher le code en lui-même (à moins que tu n'en ai envie)

en attendant, une version qui corrige l'inversion horizontal/vertical, qui convertit les dates en Double pour prendre en compte les heures, et avec une boite de dialogue pour tester pour faire plaisir à Michel, et où j'ai enlevé les IIf en suivant ton conseil

MJ13 · 6 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Bonjour à tous

Merci beaucoup Tototiti pour cette seconde version qui est très bien pour un premier essai et m'a l'air très efficace sur ce que j'ai pu tester, un must sur XLD .

En plus avec le USF, c'est le top .

david84 · 6 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Bonsoir,
Quelques tests pour voir la rapidité d'exécution : très peu de temps de gagné malgré les modifications (à peine 20 centièmes : légèrement moins de 3 secondes sur une plage de plus de 5000 lignes sur 4 colonnes).
Désolé, je croyais que cela irait plus vite sans les Iif...
Bien sûr, tous les possibilités potentiellement offertes par ta version sont vraiment intéressantes mais ce serait bien de gagner en vitesse d'exécution (à voir si possibilité).

Concernant les possibilités, pourrais-tu les présenter plus longuement ? Je constate des différences en fonction de mes choix mais j'ai parfois du mal à les comprendre, notamment la différence entre vertical et horizontal :je ne vois pas trop ce que tu veux obtenir (je croyais initialement que cela équivalait à un transpose mais ce n'est visiblement pas le cas).

Sinon, il y avait une erreur dans le code que je t'avais fourni dans mon message précédent : je le replace donc avec un format de nombre "rallongé" pour prendre en compte des valeurs numériques plus longues si nécessaire.

Code:

Sub TriTableau2D_4()
 T = Timer()
 Dim clé() As String, index() As Long
 Set Pl = [A1].CurrentRegion: Set Pl = Pl.Rows(2).Resize(Pl.Rows.Count - 1)
 a = Pl.Value
 Dim b()
 ReDim b(LBound(a) To UBound(a), LBound(a, 2) To UBound(a, 2))
 ReDim clé(1 To UBound(a, 1))
 ReDim index(1 To UBound(a, 1))
 
For i = 1 To UBound(a)
    For j = 1 To UBound(a, 2)
        Select Case VarType(a(i, j))
        Case Is = 5
            clé(i) = clé(i) & Format(a(i, j), "000000000000000000000000000000")
        Case Is = 7
            clé(i) = clé(i) & CLng(a(i, j))
        Case Else
            clé(i) = clé(i) & a(i, j)
        End Select
    Next j
    index(i) = i
Next i
 Call Tri(clé(), index(), 1, UBound(clé))
 For lig = 1 To UBound(clé)
   For col = 1 To UBound(a, 2): b(lig, col) = a(index(lig), col): Next col
 Next lig
 [J2].Resize(UBound(b), UBound(b, 2)) = b
 MsgBox Timer() - T
End Sub

Sub Tri(clé() As String, index() As Long, gauc, droi) ' Quick sort
   ref = clé((gauc + droi) \ 2)
   g = gauc: d = droi
   Do
      Do While clé(g) < ref: g = g + 1: Loop
      Do While ref < clé(d): d = d - 1: Loop
        If g <= d Then
           temp = clé(g): clé(g) = clé(d): clé(d) = temp
           temp = index(g): index(g) = index(d): index(d) = temp
           g = g + 1: d = d - 1
        End If
    Loop While g <= d
    If g < droi Then Call Tri(clé, index, g, droi)
    If gauc < d Then Call Tri(clé, index, gauc, d)
End Sub

A+

tototiti2008 · 8 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Bonjour à tous,

Désolé, je suis là en pointillés en ce moment

@Michel : content que ça te plaise, ça facilitera surement les tests

@David : concernant les possibilités :
1) Possibilité de préciser que le tableau est vertical / horizontal (transposé)
si vertical, les "lignes" sont triées, si "vertical" le colonnes sont triées
si tu observes des soucis avec cette fonctionnalité donne nous des données exemple pour lesquelles ça ne marche pas
2) Possibilité de préciser qu'il y a une ligne de titre : la 1ère ligne (ou colonne) n'est pas triée
3) Si un champ contient plusieurs types de données, possibilité de choisir comment classer les différents types de données (Dates - Nombres - Textes - Erreurs - Vides), cet ordre prend le pas sur l'ordre croissant / décroissant du champ
4) Possibilité pour chaque champ de tri de choisir un ordre croissant / décroissant, et sensible à la casse ou non
Sur la casse, toutes les majuscules s'affichent avant toutes les minuscules à cause de la comparaison binaire en VBA, je ne vois pas trop comment éviter ça facilement

Désolé, je croyais que cela irait plus vite sans les Iif...

ça va déjà un tout petit peu plus vite, toujours ça de pris
Je vais voir pour faire une version sans dictionnaire, donc qui ne supprime pas les doublons, peut-être que ça fera gagner du temps, le temps de création des dictionnaire sera gagné, mais les tris s'appliqueront sur plus de données, je ne sais pas trop ce qui est le plus long... à tester

Edit : Pour ton code modifié, comment formater pour prendre en compte des données de type Double (de 1E308 à 1E-324) ? d'où pour moi je ne vois que la solution d'indexation par champ, têtu mais toujours pas convaincu par l'autre possibilité...

Dranreb · 8 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Bonjour.

Sur la casse, toutes les majuscules s'affichent avant toutes les minuscules à cause de la comparaison binaire en VBA, je ne vois pas trop comment éviter ça facilement

En mettant Option Compare Text en tête du module.
P.S. S'il est absolument indispensable de pouvoir effectuer aussi la comparaison binaire dans le même module, utiliser la fonction StrComp en précisant 0 comme 3ième paramètre.

Je vous informe par ailleurs que j'ai ajouté de quoi classer un tableau de Variant sur certaines colonnes en ordre croissant ou décroissant dans OutIdx.
Joint ici :
https://www.excel-downloads.com/thr...taire-modules-de-classe-et-indexation.187857/
Cordialement.

Zon · 31 Juillet 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Salut à tous,

intéressant ce fil , car d'une part on voit la puissance d'un dictionnary (plutôt que la collection, ça simplifie le code) et d'autre part on a le lien vers le complément tout simplement génial de dranreb (à part l'indentation du code.... arrêtes le VBA 2 ou 3 ans , tu verras que relire son propre code aprés , c'est pas forcément évident.)

tototiti2008, en regardant le code en travers,

ça change rien mais un select case dans typedonnee ....

as variant ça sert à rien ..

A+++

tototiti2008 · 1 Août 2012

Re : Tri d'un tableau VBA à 2 dimensions multichamps

Bonjour à tous,

@Dranreb : pas encore eu le courage de regarder ton code, va falloir que je m'y remette, un petit coup de mou en ce moment
@Zon : ça fait plaisir de voir des "anciens" du forum repasser/revenir, merci de tes remarques
Je vais essayer de me remotiver sur le sujet

Tri d'un tableau VBA à 2 dimensions multichamps

david84

XLDnaute Barbatruc

tototiti2008

XLDnaute Barbatruc

david84

XLDnaute Barbatruc

tototiti2008

XLDnaute Barbatruc

david84

XLDnaute Barbatruc

tototiti2008

XLDnaute Barbatruc

david84

XLDnaute Barbatruc

tototiti2008

XLDnaute Barbatruc

Pièces jointes

MJ13

XLDnaute Barbatruc

david84

XLDnaute Barbatruc

tototiti2008

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Zon

XLDnaute Impliqué

tototiti2008

XLDnaute Barbatruc

Discussions similaires