Ordonner des nombres

[JJ1]

Bonsoir,

Je ne trouve pas de solution au problème d'ordonnancement de 5 nombres en fonction d'une ligne de référence (ligne 1) en ordre décroissant pour chacun des 5 nombres.
Avez-vous une idée de solution?
Merci de votre aide.
Bonne soirée

 

[JNP]

Re : Ordonner des nombres

Re 🙂,
Bon, le fruit de nos réflexions 😛 :

Sub JJ1()
Dim I%, J%, K%, Tableau(), Total As Long, Comparer As Long
Dim Plage As Range, Résultat As Range, Valeurs As Range
Set Plage = Application.InputBox("Plage à trier ?", "Sélection de la base", , , , , , 8)
Set Résultat = Application.InputBox("1ère cellule de la base triée ?", "Sélection de l'emplacement du tri", , , , , , 8)
Set Résultat = Range(Résultat, Résultat.Offset(Plage.Rows.Count - 1, Plage.Columns.Count - 1))
Set Valeurs = Application.InputBox("Plage des valeurs ?", "Sélection des références", , , , , , 8)
Application.ScreenUpdating = False
ReDim Tableau(Plage.Rows.Count - 1)
For I = 0 To Plage.Rows.Count - 1
For J = 1 To Plage.Columns.Count
For K = 1 To Valeurs.Count
If Plage.Cells(I + 1, J) = Valeurs.Cells(K) Then Total = Total + 2 ^ (Valeurs.Count - K): Exit For
Next K
Next J
Tableau(I) = Total
Total = 0
Next I
For I = 1 To Plage.Rows.Count
Comparer = Application.WorksheetFunction.Large(Tableau, I)
For J = 0 To Plage.Rows.Count - 1
If Comparer = Tableau(J) Then Exit For
Next J
For K = 1 To Plage.Columns.Count
Résultat.Cells(I, K) = Plage.Cells(J + 1, K)
Next K
Next I
Application.ScreenUpdating = True
End Sub

Je pense qu'on devrait être largement en dessous des 3 h 🙄...
A lire le résultat 😎

 

[JJ1]

Re : Ordonner des nombres

Bonjour David, JNP, Le Forum,

Merci à vous deux pour vos "cogitations" ...on avance bien, le tri s'est parfaitement réalisé dans le petit exemple avec ce code (et très rapide !)
mais.....

Je viens de l'installer dans une Feuille XLM avec les vraies valeurs (XLMpour ne pas avoir de problème de taille de feuille).
Plage: A2:E515
1ère cellule de tri: F2
Reference: L1:BI1

J'ai un debuggage immédiat en jaune: ...Total=Total+2 ^¨(Valeurs. Count -K): Exit For

Sais-tu d'où vient ce problème? Avec 33 lignes à trier, acun debuggage.

Autre essai: j'ai trouvé, même avec 33 lignes à trier, si ma ligne de référence va de L1 à BI1: debuggage aussi.

Merci
Bon dimanche

 

[JNP]

Re : Ordonner des nombres

Re 🙂,

J'ai un debuggage immédiat en jaune: ...Total=Total+2 ^¨(Valeurs. Count -K): Exit For
Sais-tu d'où vient ce problème? Avec 33 lignes à trier, acun debuggage.

Le problème vient du dimensionnement des variables 🙄...

J'ai dimensionné Total et Comparer en Long :

Long (entier long) 4 octets : -2 147 483 648 à 2 147 483 647

mais en ayant 50 valeurs à trier, le 50 en première position renvoie avec la pondération 2 puissance 50 soit 1,1259 10 puissance 15 😛... Alors que Long est limité à 2,147 10 puissance 9 😱...

L'idéal, si tu étais comme moi en Excel 64 bit, serait de déclarer en LongLong :

LongLong (LongLong integer) 8 octets : -9 223 372 036 854 775 808 à 9 223 372 036 854 775 807 (Valide uniquement sur les plateformes 64 bits.)

qui garantirait le tri à 1 près 😛...

Mais sinon, on peut les déclarer en Double :

Double (à virgule flottante en double précision) 8 octets : -1,79769313486231E308 à
-4,94065645841247E-324 pour les valeurs négatives ; 4,94065645841247E-324 à 1,79769313486232E308 pour les valeurs positives

en espérant que les petites valeurs seront bien triées avec les arrondis possibles... J'ai fait le test avec 50 49 48 47 1 en première ligne et 50 49 48 47 2 en 2ème ligne, il me les inverse bien, donc ça devrait être bon, mais j'avoue que je ne suis pas un spécialiste en variables 😱...
Bon dimanche 😎

 

[JJ1]

Re : Ordonner des nombres

Re,
Merci pour ces explications, je me doutais bien que c'était un problème de dimensionnement de variable.
J'ai fait un test avec Double, il se fait correctement (après 2 tentatives car j'avais oublié une variable en Double)
Je joins une image du résultat.
Encore merci et bravo car c'était complexe. (A conserver dans les tablettes d'XLD)
Bon dimanche

 

[david84]

Re : Ordonner des nombres

Re,
Une autre approche basée sur un mélange formules-VBA :
- dans la feuille Excel :2 zones nommées (Base et Valeurs)
- dans le code, la pondération n'est plus effectuée par la triple boucle, mais par une formule matricielle :

BaseTri.FormulaArray = _
        "=IF(COUNTIF(Valeurs,Base),Base/2^MATCH(Base,Valeurs,0))"

Le reste du code gère le déroulement de la procédure dans la feuille de calcul via des fonctions VBA.
J'ai fait un test sur une base de 5 colonnes et 1000 lignes et une plage de 20 valeurs (cf fichier). Les résultats sont les mêmes que ceux obtenus par JN🙂, et la vitesse de traitement est sensiblement équivalente (- de 4 sec sur un portable).
Je ne sais pas s'il est possible d'intégrer le traitement de la formule matricielle directement dans un tableau VBA, mais si c'est le cas, peut-être que cela accélèrerait encore le processus (je verrai avec JN s'il a une idée).

Sub TestTri()
Dim Base As Range, DerLigne&, BaseTri As Range, i&, t!
DerLigne = Range("A" & Rows.Count).End(xlUp).Row
Set Base = [A2].CurrentRegion.Offset(1, 0).Resize(DerLigne - 1)
Set BaseTri = [G2].Resize(Base.Rows.Count, Base.Columns.Count)
Application.ScreenUpdating = False
    BaseTri.FormulaArray = _
        "=IF(COUNTIF(Valeurs,Base),Base/2^MATCH(Base,Valeurs,0))"
    BaseTri.Value = BaseTri.Value
    
        For i = 2 To Base.Rows.Count + 1
            Cells(i, 12) = Application.Sum(Range(Cells(i, 7), Cells(i, 11)))
        Next i
           
    For i = 1 To Base.Rows.Count
        Cells(i + 1, 13) = Application.Match(Application.Large(Range(Cells(2, 12), Cells(Base.Rows.Count + 1, 12)), i), Range(Cells(2, 12), Cells(Base.Rows.Count + 1, 12)), 0)
    Next i
    
    BaseTri.Resize(BaseTri.Rows.Count, BaseTri.Columns.Count + 1).ClearContents
    
    For i = 2 To Base.Rows.Count + 1
      Range(Cells((Cells(i, 13).Value) + 1, 1), Cells((Cells(i, 13).Value) + 1, Base.Columns.Count)).Copy Destination:= _
      Range(Cells(i, 7), Cells(i, 11))
    Next i
    Application.ScreenUpdating = True
   End Sub

A+

 

[david84]

Re : Ordonner des nombres

Re re,
Le dernier fichier comportait des lignes avec des doublons au sein de la même ligne. Comme je ne savais pas si c'était le cas dans ton fichier original, j'en ai refait un sans doublon. Ceci-dit, cela m'a permis de m'apercevoir que les résultats entre JNP et les miens comportaient des différences.
j'ai donc refait un test avec un nouveau fichier sur 500 lignes sans 2 fois le même n° dans la même ligne et là les résultats sont les mêmes, mais j'en viens à me demander s'ils correspondent à ton attente. En effet :
19 20 18 16 1
19 18 2 20 13
passent avant :
20 11 18 9 19

Est-ce vraiment ce que tu veux ?
D'autre part, se peut-il qu'il y ait des doublons au sein d'une même ligne (20 10 4 10 19 par exemple) dans ce cas-là, que veux-tu obtenir ?
A+

 

[JJ1]

Re : Ordonner des nombres

Bonjour David, le Forum,

Je viens de lire ta réponse, je n'ai pas encore testé ta méthode.
Dans mes lignes, il n'y a aucun doublon.
La macro de JNP (que je remercie) fonctionne parfaitement avec la variable Double, elle est rapide puisque 500 lignes sont triées en à peine 8 minutes (et encore avec une formule matricielle dans la feuille en plus !)
J'ai joins l'image du classement décroissant tel que je le souhaite (fonction de la ligne1 uniquement).
Je te dirai pour ta solution et je te remercie aussi pour ce nouvel essai, ce sujet était particulièrement complexe, merci de m'avoir aidé à le résoudre aussi rapidement.
Bon mardi.

 

[david84]

Re : Ordonner des nombres

Re

La macro de JNP (que je remercie) fonctionne parfaitement avec la variable Double

Cela n'est pas étonnant au regard de l'expertise de mon "parrain VBA"🙂 !
Vu la macro qu'il t'a faite, c'est juste s'il ne te sert pas le Pastis avec les olives 😎.
Personnellement, c'était juste pour l'intérêt de marier formules et VBA dans un code et voir ce que cela pouvait donner en terme de vitesse d'exécution.
D'ailleurs, au sujet de mon interrogation :

Je ne sais pas s'il est possible d'intégrer le traitement de la formule matricielle directement dans un tableau VBA...

, ci-joint une réponse pour ceux que cela intéresse :

Sub TestTriTableau()
Dim Base, Valeurs, DerLigne&, DerCol%, i&, j%, Resultat, temp
DerLigne = Range("A" & Rows.Count).End(xlUp).Row
DerCol = [A2].CurrentRegion.Columns.Count
Base = [A2].CurrentRegion.Offset(1, 0).Resize(DerLigne - 1)
Valeurs = Range("N1", [N1].End(xlToRight)).Value
Application.ScreenUpdating = False
Dim BaseTri()
ReDim BaseTri(1 To DerLigne - 1)
For i = 2 To DerLigne
    For j = 1 To DerCol
            Resultat = Base(i - 1, j)
            Resultat = Resultat / 2 ^ Application.Match(Resultat, Valeurs, 0)
            Base(i - 1, j) = Resultat
            temp = temp + Resultat
    Next j
    BaseTri(i - 1) = temp
    temp = 0
    Resultat = 0
Next i
Dim Tableau()
ReDim Tableau(1 To DerLigne - 1)
For i = 1 To DerLigne - 1
    Tableau(i) = Application.Match(Application.Large(BaseTri, i), BaseTri, 0)
Next i

For i = 1 To DerLigne - 1
    For j = 1 To DerCol
        Cells(i + 1, j + DerCol + 1) = Cells(Tableau(i) + 1, j)
    Next j
Next i
Application.ScreenUpdating = True
End Sub

Sur le portable, on gagne encore en vitesse d'exécution (2 sec sur les 500 lignes et 5 colonnes sur l'exemple fourni sur mon dernier fichier), mais vu la vitesse du code de JNP, cela n'est pas un élément déterminant.
A+