XL 2021 Trier tableau

julienha · 11 Juillet 2023

Bonjour,

Dans le fichier joint, j'aimerais trier le tableau A1:L19 (avec la fonction trier par éventuellement) avec les critères ci-dessous:
- la colonne L en ordre décroissant si toutes les valeurs de la colonne K sont égales à "Put"
- la colonne L en ordre croissant si toutes les valeurs de la colonne K sont égales à "Call"
- sinon trier la colonne J en ordre décroissant

Merci par avance

julienha · 17 Juillet 2023

oui ma version 2021 est à jour

chris · 17 Juillet 2023

RE
Par formule, je n'ai pas trouvé en une formule

On peut utiliser FILTRE sur le 1er résultat mais ce n'est pas idéal d'avoir 2 tableaux...

Sinon possible avec PowerQuery intgéré à Excel mais il faut actualiser manuellement ou par VBA

julienha · 18 Juillet 2023

chris à dit:
RE
Par formule, je n'ai pas trouvé en une formule

On peut utiliser FILTRE sur le 1er résultat mais ce n'est pas idéal d'avoir 2 tableaux...

Sinon possible avec PowerQuery intgéré à Excel mais il faut actualiser manuellement ou par VBA

Merci pour votre réponse.
Ca donnerait quoi en utilisant la fonction FILTRE?

chris · 18 Juillet 2023

Bonjour

julienha · 18 Juillet 2023

chris à dit:
Bonjour

merci bcp mais du coup les critères de tri ne fonctionne plus:
- car les valeurs de la colonne K sont égales à "Put" et la colonne strike ("L" ds le tableau structuré) n'est pas en ordre décroissant

julienha · 18 Juillet 2023

job75 à dit:

Bonjour julienha, Gégé-45550,

Pour ceux dont la version Excel n'accepte pas la fonction TRIER voici une solution VBA :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
With Range("A1:L" & Application.Match([9^9], [A:A]))
    .Columns(13).Insert xlToRight 'colonne auxiliaire
    .Columns(13) = "=(ROW()>1)*(RC[-2]<>""Put"")"
    .Columns(13) = .Columns(13).Value 'supprime les formules
    If Application.Sum(.Columns(13)) = 0 Then
        .Sort .Columns(12), xlDescending, Header:=xlYes
    Else
        .Columns(13) = "=(ROW()>1)*(RC[-2]<>""Call"")"
        .Columns(13) = .Columns(13).Value 'supprime les formules
        If Application.Sum(.Columns(13)) = 0 Then
            .Sort .Columns(12), xlAscending, Header:=xlYes
        Else
            .Sort .Columns(10), xlDescending, Header:=xlYes
        End If
    End If
    .Columns(13).Delete xlToLeft
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

La macro est dans le code de la feuille et se déclenche quand on modifie une cellule quelconque.

A+

merci bcp, votre macro fonctionne parfaitement

job75 à dit:

Bonjour julienha, Gégé-45550,

Pour ceux dont la version Excel n'accepte pas la fonction TRIER voici une solution VBA :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
With Range("A1:L" & Application.Match([9^9], [A:A]))
    .Columns(13).Insert xlToRight 'colonne auxiliaire
    .Columns(13) = "=(ROW()>1)*(RC[-2]<>""Put"")"
    .Columns(13) = .Columns(13).Value 'supprime les formules
    If Application.Sum(.Columns(13)) = 0 Then
        .Sort .Columns(12), xlDescending, Header:=xlYes
    Else
        .Columns(13) = "=(ROW()>1)*(RC[-2]<>""Call"")"
        .Columns(13) = .Columns(13).Value 'supprime les formules
        If Application.Sum(.Columns(13)) = 0 Then
            .Sort .Columns(12), xlAscending, Header:=xlYes
        Else
            .Sort .Columns(10), xlDescending, Header:=xlYes
        End If
    End If
    .Columns(13).Delete xlToLeft
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

La macro est dans le code de la feuille et se déclenche quand on modifie une cellule quelconque.

A+

Merci bcp pour l'aide, votre macro fonctionne très bien.
Est-ce possible de l'adapter au tableau ci-joint avec les critères de tri ci-dessous:
- La colonne AF en ordre décroissant si toutes les valeurs de la colonnes AE="Put"
- La colonne AF en ordre croissant si toutes les valeurs de la colonnes AE="Call"
- La colonne AG en date décroissante si les dates sont différentes, les unes des autres
- sinon trier la colonne AD en ordre décroissant

job75 · 18 Juillet 2023

Bonjour julienha,

Chez moi sur Excel 2019 la fonction BDP n'existe pas, elle renvoie l'erreur #NOM?, je ne peux pas tester.

Par ailleurs les critères de tri sont incohérents.

Que faire avec le critère de tri sur AG si l'on tri sur AF ou AD ? On l'ignore ou il est prioritaire ?

A+

chris · 18 Juillet 2023

RE

julienha à dit:
merci bcp mais du coup les critères de tri ne fonctionne plus:
- car les valeurs de la colonne K sont égales à "Put" et la colonne strike ("L" ds le tableau structuré) n'est pas en ordre décroissant

C'est sûr que dès qu'on ne respecte plus les règles des tableaux structurés les galères commencent...

VB:

=TRIER(Tableau1;SI(OU(NB.SI(Tableau1[C/P];"Put")=NB.SI(Tableau1[C/P];"> ");NB.SI(Tableau1[C/P];"Call")=NB.SI(Tableau1[C/P];"> "));12;10);SI(NB.SI(Tableau1[C/P];"Call")=NB.SI(Tableau1[C/P];"> ");-1;1))

Je vois que le tableau source et la demande ont évolué au fil des posts...

chris · 18 Juillet 2023

Suite
Je n'avais pas vu

julienha à dit:
De plus, les données sont actualisées par des copié-collé.

La pire façon de faire...

job75 · 18 Juillet 2023

Bon je considère que vous avez donné les 4 critères de tri dans l'ordre de leurs priorités, alors utilisez :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
With Range("A1:AG" & Application.Match([9^9], [A:A]))
    .Columns(34).Insert xlToRight 'colonne auxiliaire
    .Columns(34) = "=(ROW()>1)*(RC[-3]<>""Put"")"
    .Columns(34) = .Columns(34).Value 'supprime les formules
    If Application.Sum(.Columns(34)) = 0 Then _
        .Sort .Columns(32), xlDescending, Header:=xlYes: GoTo 1
    .Columns(34) = "=(ROW()>1)*(RC[-3]<>""Call"")"
    .Columns(34) = .Columns(34).Value 'supprime les formules
    If Application.Sum(.Columns(34)) = 0 Then _
        .Sort .Columns(32), xlAscending, Header:=xlYes: GoTo 1
    .Columns(34) = "=(ROW()>1)*(RC[-1]<>R2C[-1])"
    .Columns(34) = .Columns(34).Value 'supprime les formules
    If Application.Sum(.Columns(34)) > 0 Then _
        .Sort .Columns(33), xlDescending, Header:=xlYes: GoTo 1
    .Sort .Columns(30), xlDescending, Header:=xlYes
1   .Columns(34).Delete xlToLeft
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

Je joins le fichier malgré les #NOMS? chez moi (j'ai pu tester en les supprimant).

En Feuil2 j'ai supprimé tous les Feuil2! dans les formules, ils gênaient le tri sur la colonne AD.

julienha · 19 Juillet 2023

job75 à dit:

Bon je considère que vous avez donné les 4 critères de tri dans l'ordre de leurs priorités, alors utilisez :

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
With Range("A1:AG" & Application.Match([9^9], [A:A]))
    .Columns(34).Insert xlToRight 'colonne auxiliaire
    .Columns(34) = "=(ROW()>1)*(RC[-3]<>""Put"")"
    .Columns(34) = .Columns(34).Value 'supprime les formules
    If Application.Sum(.Columns(34)) = 0 Then _
        .Sort .Columns(32), xlDescending, Header:=xlYes: GoTo 1
    .Columns(34) = "=(ROW()>1)*(RC[-3]<>""Call"")"
    .Columns(34) = .Columns(34).Value 'supprime les formules
    If Application.Sum(.Columns(34)) = 0 Then _
        .Sort .Columns(32), xlAscending, Header:=xlYes: GoTo 1
    .Columns(34) = "=(ROW()>1)*(RC[-1]<>R2C[-1])"
    .Columns(34) = .Columns(34).Value 'supprime les formules
    If Application.Sum(.Columns(34)) > 0 Then _
        .Sort .Columns(33), xlDescending, Header:=xlYes: GoTo 1
    .Sort .Columns(30), xlDescending, Header:=xlYes
1   .Columns(34).Delete xlToLeft
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

Je joins le fichier malgré les #NOMS? chez moi (j'ai pu tester en les supprimant).

En Feuil2 j'ai supprimé tous les Feuil2! dans les formules, ils gênaient le tri sur la colonne AD.

Tout d'abord merci infiniment.
Désolé pour les formules BDP, j'ai oublié de les retirer.
En fait, le tri sur AG devient prioritaire uniquement si les dates en AG sont différentes.
Je vous renvoie le fichier sans les formules BDP.
Merci encore

job75 · 19 Juillet 2023

Bonjour julienha,

1) Vos dates en colonne AG n'étaient pas de vraies dates (nombres) mais des textes.

Dans le fichier joint je les ai converties en les revalidant.

2)

julienha à dit:
En fait, le tri sur AG devient prioritaire uniquement si les dates en AG sont différentes.

Si vous voulez dire que le tri de la colonne AG doit se faire même avec des "Put" ou des "Call" sur toute la colonne AE il faut mettre les 4 lignes qui le concernent en 1er :

VB:

    .Columns(34) = "=(ROW()>1)*(RC[-1]<>R2C[-1])"
    .Columns(34) = .Columns(34).Value 'supprime les formules
    If Application.Sum(.Columns(34)) > 0 Then _
        .Sort .Columns(33), xlDescending, Header:=xlYes: GoTo 1

A+

julienha · 24 Juillet 2023

job75 à dit:
Bonjour julienha,

1) Vos dates en colonne AG n'étaient pas de vraies dates (nombres) mais des textes.

Dans le fichier joint je les ai converties en les revalidant.

2)

Si vous voulez dire que le tri de la colonne AG doit se faire même avec des "Put" ou des "Call" sur toute la colonne AE il faut mettre les 4 lignes qui le concernent en 1er :

VB:

.Columns(34) = "=(ROW()>1)*(RC[-1]<>R2C[-1])" .Columns(34) = .Columns(34).Value 'supprime les formules If Application.Sum(.Columns(34)) > 0 Then _ .Sort .Columns(33), xlDescending, Header:=xlYes: GoTo 1

A+

Merci bcp. Ca fonctionne très bien mise à part les dates. Je reçevrais tjs les dates en AG avec une formule (via Bloomberg), du coup 2 possibilité peut être:
- soit convertir automatiquement avec la macro, la colonne AG en vraies dates
- soit: si les dates en AG sont différentes, trier la colonne AB en ordre décroissant (tri prioritaire)

Merci milles fois

job75 · 24 Juillet 2023

Bonjour julienha,

Vous utilisez une formule en AG, utilisez-la pour convertir le texte en nombre, en AG2 :

Code:

=SI(B2="";"";SIERREUR(--BDP(V2;"MATURITY");""))

ou peut-être plus simplement :

Code:

=SIERREUR(--BDP(V2;"MATURITY");"")

Et mettez la colonne AG au format Date.

A+

julienha · 24 Juillet 2023

job75 à dit:
Bonjour julienha,

Vous utilisez une formule en AG, utilisez-la pour convertir le texte en nombre, en AG2 :

Code:

=SI(B2="";"";SIERREUR(--BDP(V2;"MATURITY");""))

ou peut-être plus simplement :

Code:

=SIERREUR(--BDP(V2;"MATURITY");"")

Et mettez la colonne AG au format Date.

A+

merci. Dernier point, les données du range A2:AG19 s'actualisent manuellement en 2 étapes:
- les données de A2:AG19 sont effacées
- puis collé de nouvelles données
Quand j'efface les données de A2:AG19, j'ai le message d'erreur ci-joint:
Erreur d'execution '13'
Icompatibilité de type

Merci bcp

XL 2021 Trier tableau

XLDnaute Occasionnel

Pièces jointes

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

XLDnaute Occasionnel

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

Pièces jointes

Discussions similaires