Tri automatique dans un tableau

Bonjour à tous.

J'ai besoin de votre aide.

Voilà le tableau :

On est environ 400 participants (une ligne par participant), et on note le nombre de kilomètre parcouru par jour dans une colonne différente.

Une dernière colonne correspond à la somme totale des kilomètres effectués par participants.

J'aimerais que le tableau trie automatiquement (à chaque fois qu'une valeur change) le "classement" (mettre en 1er celui qui a fait le plus de kilomètre, etc...)

J'ai cherché, je suis tombé sur des macros VBA, mais j'avoue que je n'y comprend rien...

Pouvez-vous m'aider svp à faire ce truc qui semble simple pour les "pro d'excel" ?

Merci à vous...

Bonjour,

Le code n'est certainement pas optimal car en apprentissage mais regarde ceci... Ca fait ce que tu veux... je crois

Cordialement,

Vbabeginner

Bonjour @ toi tout seul

Ci-joint le fichier avec quelque modifications :

tout d'abord j'ai transformé votre table en "tableau Excel" avec l'outil "insertion tableau", ceci apporte beaucoup d'option

ensuite j'ai déplacé la colonne Total juste après les noms, comme ceci il suffit de rajouter une date en entête de dernière colonne et elle est prise en compte dans la formule.

@ bientôt

LouReeD

Bonjour LouReed (Très bon pseudo )

Merci pour ton tableau, ça m'aide aussi...

Je vais pouvoir m'en sortir maintenant...

C'est génial votre entraide

VBABeginner, j'avais envoyé une réponse mais elle n'est pas apparue...

Merci à toi aussi pour ton tableau.

C'est également ça que je cherchais.

Merci en tout cas de votre aide, car en 5 minutes, vous avez résolu un problème sur lequel j'ai cherché pendant plus de 3h....

Merci de vos mercis !

Mais un peu d'explication :

le fait de transformer une table de données en tableau permet pas mal de chose.

Par exemple (je crois que c'est une option par défaut), lorsque vous ajoutez une entête à la suite des entêtes existantes, celle-ci s'intègre automatiquement dans le tableau. Il en est de même pour les lignes. Si dans votre cas vous rajoutez le nom d'une personne alors elle s'intégrera automatiquement au tableau.

Dans les deux cas, l'intégration est "complète" : MFC, formules etc...

Voyez vous même, ajoutez un nom en colonne A puis [entrée] (normal quoi )

et bien il y a recopie automatique de la formule en colonne B !

Si la formule ne vous convient plus et que vous la modifier, il suffit alors de la modifier à la première ligne et elle se modifie sur toutes les lignes en dessous dès que vous tapez [entrée] !

Ce système d'insertion de tableau depuis Excel 2007 est le successeur (en mieux) de la fonction "liste" qui existait sur les versions antérieures.

Explication de la formule (peut-être y a t il une solution plus "propre") :

=SOMME(INDIRECT("l"&LIGNE()&"c3:l"&LIGNE()&"c"&$A$1;FAUX))

Somme : vous connaissez

dans votre cas vous mettiez =SOMME(B3:I3)

afin de sommer les cellules allant de B3 à I3 pour la ligne 3 avec la formule en J3.

Hors au vu des entêtes, vous avez peut-être, me suis-je dis, envie de rajouter encore des colonnes, donc de faire une insertion avant la colonne J, et en plus de devoir "ré écrire" les formule pour les transformer en =SOMME(B3:J3) et ceci dans la colonne K suite à l'insertion de colonne. Au niveau pratique, pas top. En plus de cela s'il y a beaucoup de dates, le total se sépare d'autant du nom de la personne, donc du coup j'ai préféré mettre le total à coté du nom, d'une part c'est plus lisible, et d'autre part cela laisse le champ libre pour l'ajout des dates que vous voulez.

La ligne 1 et cachée, mais il y a en cellule A1 une formule qui permet de connaître le nombre de colonne du tableau, la formule étant : =NBVAL(2:2), qui donne le nombre de valeur en ligne 2 qui n'est autre que la ligne des entêtes, donc le résultat correspond au nombre de colonne du tableau.

J'ai besoin de cette valeur pour transformer votre formule =SOMME(C3:I3) en une formule dont les "références" sont variables.

En effet le I3 dépend si vous ajouter ou supprimer une colonne date, en ajoutant une colonne comme vu ci dessus alors la fin du tableau n'est plus la colonne I mais la colonne J, il faut donc des références variables... J'utilise (personnellement, mais il doit y avoir d'autre solution comme le DECALER) donc la fonction INDIRECT qui permet d'aller à une adresse ou une plage de cellule indirectement par des variables. Chrnologie :

=SOMME(C3:I3)

=SOMME(INDIRECT("C3:I3") (ici l'adresse est transformée en variable "string" donc avec des & et des variables de valeur on transforme cette référence en référence variable)

Pour avoir le 3 du numéro de ligne on utilise la fonction LIGNE() qui renvoie le numéro de ligne où se trouve cette instruction.

L'avantage, avec la recopie de formule vers le bas (qui est automatique dans un tableau inséré) en ligne 4 cette valeur prendra la valeur 4 etc...

=SOMME(INDIRECT("C"&LIGNE()&:"I"&LIGNE()) (si on est en ligne 3 on a bien C3:I3)

Le problème c'est le nombre de colonne du tableau, et c'est là que la valeur de la cellule A1 va nous servir...

Hors si notre tableau va jusqu'en colonne I cela fait 9 en valeur de la cellule A, et le calcul commence en colonne C qui vaut 3, donc

=SOMME(INDIRECT("3"&LIGNE()&":"&$A$1$&LIGNE()) (ce qui donne 33:93 ! ce qui n'est pas reconnu par Excel !!!)

Pourquoi ? parce que un A ne vaut pas un 1 en adresse de cellule A1<>11 !

Alors comment faire pour utiliser des référence de cellule numérique à la place de référence alphanumérique avec INDIRECT ?

il suffit de mettre "FAUX" en paramètre de la fonction INDIRECT est d'écrire la référence de la cellule sous la forme L1C1 qui veut dire Ligne 1 Colonne 1, voilà on a nos référence en numérique !

Ce qui donne :

=SOMME(INDIRECT("L"&LIGNE()&"C3:L"&LIGNE()&"C"&$A$1))

ce qui donne si on remplace les variables par leur valeurs pour la ligne 3 :

=SOMME(INDIRECT("L3C3:L3C9") où L3C3 = la cellule C3 et L3C9 = la cellule I3 donc la somme de la plage C3:I9 !

La formule étant faite reste plus qu'à trier par ordre de grandeur, cela se passe en VBA sur la feuille concernée avec la procédure événementielle de changement de valeur sur la feuille.

Pour y accéder [Alt]+[F11]

Le code permet de filtrer sur la colonne Total du tableau2 de la feuille1 en triant du plus grand au plus petit.

L'avantage que ce soit un tableau nommé, il n'y a pas besoin de connaître sa taille, c'est Excel et VBA qui s'en charge dans ce cas.

Après le top c'est de mettre une vérification quant à la zone de changement de valeur de cellule, en effet si vous avez autre chose sur la feuille en dehors du tableau, c'est dommage que la procédure soit exécutée si ce n'est pas une valeur interne du tableau, non ?

Enfin là c'est pour les puriste

Je ne sais pas s'il fallait expliquer...

S'il ne fallait pas j'éditerais ce message et je ne laisserai que mon merci pour votre merci

@ bientôt

LouReeD

Re

Voici un code de "puriste" ?!

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("Tableau2")) Is Nothing Then ' si la cellule modifiée fait partie du tableau2
        Application.ScreenUpdating = False ' pour éviter que le tableau "clignote" on arrête la mise à jour de l'écran
            ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau2").Sort.SortFields. _
                Clear ' supprime les filtres éventuels
            ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau2").Sort.SortFields. _
                Add Key:=Range("Tableau2[[#All],[Total]]"), SortOn:=xlSortOnValues, Order _
                :=xlDescending, DataOption:=xlSortNormal ' tri par ordre croissant sur la colonne Total
            With ActiveWorkbook.Worksheets("Feuil1").ListObjects("Tableau2").Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        Application.ScreenUpdating = True ' on remet en marche la mise à jour de l'écran
    End If
End Sub

@ bientôt

LouReeD

Bonjour,

Bonjour LouReed,

Mes meilleurs vœux pour cette nouvelle année.

Je me permets de réviser ta procédure de tri.

Cdlt.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lo As ListObject

    If Not Target.ListObject Is Nothing And Target.Column > 1 Then
        Application.ScreenUpdating = False
        Set lo = Me.ListObjects(1)
        With lo.Sort
            .SortFields.Add _
                    Key:=lo.ListColumns(2).DataBodyRange, _
                    SortOn:=xlSortOnValues, _
                    Order:=xlDescending, _
                    DataOption:=xlSortNormal
            .Apply
            .SortFields.Clear
        End With
    End If

    Set lo = Nothing

End Sub

Bonjour,

@ LouReed et Jean-Eric : merci de me faire sentir ridicule avec mon code pourri

Bonne soirée,

Vbabeginner

Bonjour Eriiic et bonne année @ vous également !

Je disais bien :

Voici un code de "puriste" ?!

avec le petit bonhomme vert !!!

En fait dans différent mail j'écris souvent : en attendant la version "Pro"...

J'ai beau les regarder mais "j'imprime pas" du coup je fais avec mes "gros sabots"

Mais ça marche !

Et sinon au niveau de la formule en colonne B, vous n'auriez pas plus simple ?

@ bientôt donc

LouReeD

Re,

LouReed,

Ton désir de début d'année est exaucé :

B3 : =SOMME(DECALER($C3;;;;EQUIV("zzz";$2:$2;1)))

Au lieu de :

A1 : =NBVAL(2:2)
B3 : =SOMME(INDIRECT("l"&LIGNE()&"c3:l"&LIGNE()&"c"&$A$1;FAUX))

Cdlt.

nota : moi, c'est Jean-Eric et pas Eriiic.

Ce doit être des reste de bulles pour le nom !!!!

Désolé Jean-Eric

Bonjour à Eriiic...

Et bien voilà avec DECALER comme je disais :

J'utilise (personnellement, mais il doit y avoir d'autre solution comme le DECALER) donc la fonction INDIRECT

faut que je travail encore un peu, mais pour cela il faut attendre que les bulles s'en aillent...

@ bientôt

LouReeD