XL 2016 effectuer un classement des 2 meilleurs resultats d'une equipe

[stapsman]

bonjour
je rencontre le probleme suivant :
sur le fichier joint , j ai sur une feuille les résultats de plusieurs villes à des epreuves datées.
Il y a un ,deux ou trois valeurs par ville.
Sur la feuille 2 , j ai reussi à effectuer un classement grace à un tableau croisé .
Je souhaiterai que ce classement prenne en compte uniquement les deux meilleurs résultats de chaque ville et "oublie" le moins bon quand il existe.
Et donc que cela me classe les villes en fonction de ces 2 meilleurs résultats (si la ville a un seul résultat ,elle est pris en compte en attendant le deuxieme soit le resultat 1 seul )
dois je rester sur un tableau croisé ou passer par une formule ?
Pas évident pour moi ....
Merci
bonne journée
ps: le but est de l'integrer à une appli glide

 

[Dugenou]

Bonjour,
Une proposition qui peut être améliorée : on garde les villes du TCD pour avoir une liste de façon simple. On calcule le total des deux meilleurs score avec une formule matricielle. On calcule le classement sur cette base de score.

Cordialement

 

[stapsman]

merci , ça marche par rapport au tableau proposé.
Petite demande supplémentaire néanmoins :
ce tableau étant inséré dans une application glide , cela permet au participant d ajouter un résultat sur la feuille des villes et score en live .
du coup quand on rentre ce résultat supplémentaire (ex : une nouvelle ville "nice" , comment faire pour que le tableau suivant prenne en compte cette nouvelle donnée .
Cela doit permettre une certaine interactivité .
Enfin , j'ai mis les 2 meilleures valeurs pour faire plus simple mais en fait ce serait les 6 meilleurs valeurs parmi 12 au max .
du coup, dans la formule , où se joue ce choix ( 2 parmi / 6 parmi / X parmi )?
Cordialement

 

[Dugenou]

Re,
Je ne sais pas ce qu'est une application glide ☹
Pour prendre en compte de nouvelles villes il faut mettre à jour le TCD (mais on pourrait aussi le faire par formule).
Enfin le choix des 2 premiers se fait avec le "grande.valeur(...;1) et ...2 : quand il n'y en avait que 2 la formule était encore acceptable, avec 6 et les sierreur en plus ça va être une formule très longue.
Je regarde si on peut faire autrement
Cordialement

 

[job75]

Bonjour stapsman, Dugenou,

Une solution VBA avec ces macros dans le code de la feuille "Classement" :

Private Sub SpinButton1_Change()
Worksheet_Activate 'lance la macro
End Sub

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
Feuil1.[A:B].Copy [A1] 'copier-coller
With Range("A1", Range("B" & Rows.Count).End(xlUp))
    .Sort .Columns(2), xlDescending, Header:=xlYes
    .Rows(2).Offset([D1]).Resize(Rows.Count - [D1] - 1).Delete xlUp
End With
End Sub

A+

 

[job75]

Fichier (2) si l'on veut cumuler les scores :

Private Sub SpinButton1_Change()
Worksheet_Activate 'lance la macro
End Sub

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
With Feuil1 'CodeName
    .Columns(1).Name = "A" 'plage nommée
    .Columns(2).Name = "B" 'plage nommée
    [A,B].Copy [A1] 'copier-coller
End With
With Range("A2", Range("B" & Rows.Count).End(xlUp)(2))
    .Columns(2) = "=IF(RC[-1]="""","""",SUMIF(A,RC[-1],B))"
    .Columns(2) = .Columns(2).Value 'supprime les formules
    .RemoveDuplicates 1, xlNo 'supprime les doublons
    .Sort .Columns(2), xlDescending, Header:=xlNo 'tri
    .Rows(1).Offset([D1]).Resize(Rows.Count - [D1] - 1).Delete xlUp
End With
End Sub

 

[R@chid]

Bonjour @ tous,
Une variante avec Power Query, on ne garde que les deux meilleurs scores pour chaque établissement si l'on a plus que 2, on fait la somme des scores et on procède au tri.

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"Colonnes supprimées" = Table.RemoveColumns(Source,{"date"}),
    #"Texte en majuscules" = Table.TransformColumns(#"Colonnes supprimées",{{"etablissement", Text.Upper, type text}}),
    #"Type modifié" = Table.TransformColumnTypes(#"Texte en majuscules",{{"etablissement", type text}, {"score", Int64.Type}}),
    #"Lignes triées" = Table.Sort(#"Type modifié",{{"etablissement", Order.Ascending}, {"score", Order.Descending}}),
    #"Lignes groupées" = Table.Group(#"Lignes triées", {"etablissement"}, {{"Table", each Table.AddIndexColumn(_,"Index", 1, 1)}}),
    #"Table développé" = Table.ExpandTableColumn(#"Lignes groupées", "Table", {"score", "Index"}, {"score", "Index"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Table développé",{{"etablissement", type text}, {"score", Int64.Type}, {"Index", Int64.Type}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié1", each [Index] < 3),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Lignes filtrées",{"Index"}),
    #"Lignes groupées1" = Table.Group(#"Colonnes supprimées1", {"etablissement"}, {{"Somme Score", each List.Sum([score]), type nullable number}}),
    #"Lignes triées1" = Table.Sort(#"Lignes groupées1",{{"Somme Score", Order.Descending}})
in
    #"Lignes triées1"

Voir PJ

Tu peux ajouter des données sur ton premier tableau, puis tu actualises la requête, avec Bouton Droit sur le tableau Orange / Actualiser


Cordialement

 

[stapsman]

bonsoir
vos formules ont l air tres bien et bien au dela de mes compétences....
l idée première reste cependant à partir de mon tableau de faire un classement pas des 6 premiers du classement mais de prendre pour chaque equipe les 6 meilleurs scores , de les additionner et de les classer ainsi.
Puis si on ajoute "en live" à partir d'une application glide (application hebergé sur internet )une ville et un score
puis le classement se met à jour.
je vous mets en PJ le lien de cette appli pour que vous puissiez voir ce que cela donne.
merci pour vos solutions.

 

[job75]

Oui j'avais lu le post #1 en diagonale, veuillez m'excuser.

Voyez ce fichier (3) où une formule matricielle est entrée en colonne B :

Private Sub SpinButton1_Change()
Worksheet_Activate 'lance la macro
End Sub

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
[D1].Name = "Choix" 'cellule nommée, à adapter
With Feuil1 'CodeName
    .UsedRange.Columns(1).Name = "A" 'plage nommée
    .UsedRange.Columns(2).Name = "B" 'plage nommée
    .[A:B].Copy [A1] 'copier-coller
End With
With Range("A2", Range("B" & Rows.Count).End(xlUp)(2))
    .Cells(1, 2).FormulaArray = "=IF(A2="""","""",SUM(IFERROR(LARGE(IF((A=A2)*(COUNTIF(A,A2)>=Choix),B),ROW(INDIRECT(""1:""&Choix))),0)))"
    If .Rows.Count > 1 Then .Cells(1, 2).AutoFill .Columns(2), xlFillValues
    'End 'pour voir les formules matricielles
    .Columns(2) = .Columns(2).Value 'supprime les formules
    .RemoveDuplicates 1, xlNo 'supprime les doublons
    .Sort .Columns(2), xlDescending, Header:=xlNo 'tri
    If Application.CountIf(.Columns(2), 0) = 0 Then Exit Sub
    .Columns(2).Replace 0, "#N/A", xlWhole
    Intersect(.Columns(2).SpecialCells(xlCellTypeConstants, 16).EntireRow, .Cells).Delete xlUp
End With
End Sub

Pour voir les formules activez le End comme indiqué.

Bien comprendre qu'avec 2 en D1 Marseille n'apparaît plus car elle n'a qu'un seul score.

Bonsoir cher R@chid.

Edit : avec la cellule D1 nommée "Choix" c'est mieux.

 

[stapsman]

c'est super !!! ça fonctionne super bien sur une feuille excel !
j'embête mon monde mais est ce possible de l'intégrer sur le google sheet suivant ?
car quand je cherche à copier la feuille à l'intégrer sur le google sheet ,la formule matricielle ne s'active plus
ce qui devrait me permettre de transformer tout cela en application .
Un grand merci.
bonne soirée

Copie de test challenge unss

Accueil image,texte,date,texte rebours,image reoburs data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAASUAAACsCAMAAAAKcUrhAAAA1VBMVEX///8AYKfkSyEAXqYAWqQAXKUAV6MAWaQAVKIAVaIAUqHb4+7D0+QAT6C3zuMpbq7v9vu+1edKgrg3dbHj7vXs9Ppsk8AAY6lvmsXkSBxvn8k+g7qLstP2+/0AaKzZ5/LjPABYkMDjQg6wxt6Fpst8qM7K3u2UttX98/Cev...

docs.google.com

 

[job75]

j'embête mon monde mais est ce possible de l'intégrer sur le google sheet suivant ?

Les macros Google Spreadsheet n'utilisent pas le langage VBA et ma macro ne peut pas être traduite.

 

[stapsman]

ok
merci quand meme...
je decouvre la puissance des macros.
bonne journée

 

[stapsman]

Bonjour,
Une proposition qui peut être améliorée : on garde les villes du TCD pour avoir une liste de façon simple. On calcule le total des deux meilleurs score avec une formule matricielle. On calcule le classement sur cette base de score.

Cordialement

bonjour
j ai essayé d'integrer votre formule dans mon google sheet: elle fonctionne bien et me permettrai de l'intégrer à mon application.
Par contre, elle avait été prévu pour prendre les deux meilleurs scores de chaque ville .
Si je souhaite prendre en compte les 6 meilleurs résultats de chaque ville , malgré la longueur de la formule , que dois je faire ?
c'est en rapport avec le si erreur ,je suppose ,: si erreur 1, on prend le 2nd plus grande score, si erreur 1 et 2 , on prend le 3e plus grand et ainsi de suite .....un copier/coller peut peut etre aller mais où mettre les parentheses sans se tromper.?
En vous remerciant
cordialement.