XL 2016 INDEX et/ou EQUIV

[bof]

Bonjour,

J’ai toujours été sous doué en formules… et l'aide de crosoft m'a toujours semblé incompréhensible...
Je désire trier un tableau (pour l’exemple de 3 colonnes) selon la colonne C (à compléter par une formule.)
Cette colonne C doit être remplie par l’Index de la colonne H correspondant à B
Euh je ne sais pas si c’est clair, mais j’ai commencé à remplir cette colonne C en manuel YAPUKA trouver la formule qui donne ce résultat et l’étendre jusqu’en bas…
Le but étant que à la fin du tri sur la colonne C, tous les E de Ref se retrouvent en premier et tous les G en dernier…
Merci

 

[CISCO]

Bonsoir

Cela ne serait pas plutôt 1, 6, 25, 30, 54, 58, 33, 37...

@ plus

 

[bof]

Oui in fine quand on aura fini de remplir la colonne C avec une formule et qu'on aura trié sur la colonne C effectivement dans la colonne A on aura bien 1, 6, 25, 30, 54, 58, 33, 37...
Mais je ne sais pas trier comme ça sans indexer la colonne B en fonction du référentiel de tri (colonne H)

 

[RyuAutodidacte]

Bonjour,

une solution toute simple sans formule :

1. Aller dans les options => liste personnalisée puis importer la liste depuis des cellule
   
   
   
   
   
2. Une fois les celllules sélectionnées, faire importer pour avoir la liste,
   valider/fermer la fenêtre et revenir au fichier
   
   
   
   
   
3. Sélectionner la plage à trier et faire "Trier", cocher qu'il y a des entêtes
   Choisir la colonne voulue : ici Ref
   et pour l'ordre sélectionner "Liste personnalisée…"
   
   
   
   
   
4. Sélectionner la liste que l'on a créé
   
   
   
   
   
5. Mettre l'ordre voulue (ordre croissant avec notre liste personnalisée)
   puis valider
   
   
   
   
   
6. Le résultat :
   
   

Comme ça pas de problème pour un sous-doué en formule

 

[bof]

Bonsoir,
Cette solution ne me convient pas car il y a encore plus sous-doué que moi ! Donc je ferai une macro transparente qui fera le tri sans pb...
Ceci dit en réalité j'ai plusieurs milliers de lignes et plusieurs dizaines de colonnes... Si quelqu'un voit une solution qui ne m'oblige pas à créer une 3ème colonne qui ne me servira pas à grand chose... Mais je ne vois guère d'autre solution même en VBA car mon référentiel d'indexation pourrait bien évoluer en une douzaine d'items voire plus...
A+

 

[RyuAutodidacte]

Re,

Autre solution en utilisant la formule EQUIV :
En colonne D on créé la colonne TRI, en D2 on met la formule :

=EQUIV(B2;$H$2:$H$6;0)

on étire la formule, jusqu'à la fin de ligne des donnée
De la colonne A à D on met le filtre automatique

1. si on veut les ID dans l'ordre, on trie dans l'ordre croissant la colonne C
2. on trie dans l'ordre croissant la colonne TRI

 

[RyuAutodidacte]

mon référentiel d'indexation pourrait bien évoluer en une douzaine d'items voire plus...

il faudra juste mettre à jour la formule EQUIV que j'ai donné sur le post 6

De cette manière c'est le filtre automatique qui fera le travail (solution sans Macros)

 

[bof]

Oui c'est bien ça.
C'est parfait.
A+

 

[CISCO]

Bonsoir

Encore une autre possibilité :
Dans le ruban, Données
Trier
Colonne B
A droite, Liste personnalisée

Complètement à droite, Ajouter
OK

Si tu tiens à garder l'ancienne liste, il suffit de travailler sur de nouvelles colonnes contenant les valeurs.

@ plus

 

[CISCO]

Rebonsoir

Comme la plage H$2:H$6 porte le nom Critères, en utilisant la méthode précédente, on peut faire comme suit :

@ plus

 

[bof]

Bonjour,
Ça ne va pas coller dans mon contexte (parce que je tiens absolument à finaliser mon tri par macro) mais ça me fera une petite piqure de rappel : Je ne pense jamais assez aux listes personnalisées.
Merci.
A+

 

[CISCO]

Bonjour

Lorsque j'utilise le nom Critères, au lieu d'écrire E, P, M..., j'obtiens la colonne B dans l'ordre alphabétique, et pas dans l'ordre E, P, M...

@ plus

 

[RyuAutodidacte]

Bonjour,
Ça ne va pas coller dans mon contexte (parce que je tiens absolument à finaliser mon tri par macro) mais ça me fera une petite piqure de rappel : Je ne pense jamais assez aux listes personnalisées.
Merci.
A+

Bonjour @bof ,

Par macro, on peut le faire comme cela :

Sub Tri()
Dim DL As Long, Plage As Range
    ' PS le critere trie est a mettre dans le code a CustomOrder => CustomOrder:="E,P,M,X,G"
    ' Il faut bien cibler la feuille et la plage dans le code
    With Sheets(1)
        DL = .Columns("A:C").Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row  'Derniere ligne de la colonne A:C
        Set Plage = .Range("A2:C" & DL)
        Application.ScreenUpdating = False
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Plage.Columns.Item(2), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="E,P,M,X,G", DataOption:=xlSortNormal ' Tri sur la 2eme colonne de la plage
            .SortFields.Add Key:=Plage.Columns.Item(3), Order:=xlAscending, DataOption:=xlSortNormal  ' Tri sur la 3eme colonne de la plage
            .SetRange Plage: .Header = xlYes: .MatchCase = False: .Orientation = xlTopToBottom: .SortMethod = xlPinYin: .Apply
        End With
    End With
        Application.ScreenUpdating = True
        Set Plage = Nothing
End Sub


' code pour le test afin de remettre la donnée rapidement
Sub RecoverData()
    VA = Feuil2.Cells(1).CurrentRegion.Value
    Feuil1.Cells(1).Resize(UBound(VA), UBound(VA, 2)).Value = VA
End Sub

 

[RyuAutodidacte]

Re une petite variante permettant de modifier ses paramètres de tri en évitant d'aller dans le cœur du code (cela évite de faire une erreur) :

Sub Tri()
Dim DL As Long, Plage As Range

    ' La variable "ParamTri" "E,P,M,X,G" pour mettre les criteres de Tri si ils viennent à changer
    ParamTri = "E,P,M,X,G"
  
    ' Il faut bien cibler la feuille et la plage dans le code
    With Sheets(1)
        DL = .Columns("A:C").Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row  'Derniere ligne de la colonne A:C
        Set Plage = .Range("A2:C" & DL)
        Application.ScreenUpdating = False
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Plage.Columns.Item(2), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=CVar(ParamTri), DataOption:=xlSortNormal ' Tri sur la 2eme colonne de la plage
            .SortFields.Add Key:=Plage.Columns.Item(3), Order:=xlAscending, DataOption:=xlSortNormal  ' Tri sur la 3eme colonne de la plage
            .SetRange Plage: .Header = xlYes: .MatchCase = False: .Orientation = xlTopToBottom: .SortMethod = xlPinYin: .Apply
        End With
    End With
        Application.ScreenUpdating = True
        Set Plage = Nothing
End Sub

Il est possible d'inverser ces 2 lignes selon le résultat voulu :

            .SortFields.Add Key:=Plage.Columns.Item(3), Order:=xlAscending, DataOption:=xlSortNormal  ' Tri sur la 3eme colonne de la plage
            .SortFields.Add Key:=Plage.Columns.Item(2), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=CVar(ParamTri), DataOption:=xlSortNormal ' Tri sur la 2eme colonne de la plage

 

[CISCO]

Bonsoir

J'avais essayé avec l'enregistreur de macro... mais comme je ne voyais pas dans le code VBA où était défini E, P, M, X, G, j'ai abandonné...
Merci RyuAutodidacte

@ plus