Extraire un classement par équipe à partir d'un classement individuel
Bonjour à tous
J'avance dans mon classement de coureurs en voulant le faire évoluer avec un classement par équipes.
Voici mes contraintes
- Extraire uniquement les clubs du département 077 si ces derniers ont au moins 5 coureurs
- Additionner automatiquement les temps sur les 5 premiers coureurs
A partir de mon modèle j'ai extrait les différentes équipes sans doublon avec la fonction unique, fait un calcul manuel avec la fonction somme et leur ai attribué un classement avec la fonction rang
J'ai toutefois un souci c'est que la formule extrait également les clubs hors département et les coureurs non licenciés NL.
Ces feuilles de résultats individuelles seront ensuite compilées pour être traitées avec PowerQuery (que je continue tant bien que mal d'apprivoiser) afin de tenir le classement à jour chez les hommes , chez les femmes et par équipe.
Si l'un d'entre vous peut éclairer ma lanterne excellienne
Merci d'avance
Bonjour
Ton profil indique 2019 mais UNIQUE n'est que dans 2021 et 365 il me semble.
Peux-tu confirmer ta version Excel.
Un classement PowerQuery : reste le calcul des points à ajouter
EDIT : je supprime mes participations : cros post après tout le boulot fourni ici, fallait oser...
Bonjour
J'ai travaillé sur le fichier depuis l'ordi de mon boulot qui est en version LTSC 2021 effectivement.
Il va falloir que je regarde pour trouver autre chose pour que cela fonctionne sur mon pc.
Pour le classement avec PowerQuery je pensais repartir du fichier que tu m'avais fait afin d'y intégrer les équipes.
Puis d'ajouter au fur et à mesure de la saison les classements des différentes courses que je recevrais des chronométreurs.
Je tâtonne pas mal avec PQ... mais j'espère arriver à m'en sortir.
RE
on peut faire un classement par équipe et par course dans PQ.
Le souci c'est que tu n'a pas analysé complètement ton besoin avant de commencer : du coup on doit faire te défaire, ce qui ne t'aide pas à comprendre et optimiser les solutions...
Re 78chris
Effectivement ce qui me semble clair dans ma tête ne l'est pas forcément pour tout le monde. De plus ceci étant une refonte du système actuel qui devient désuet les demandes apparaissent au fil du projet pour automatiser au plus possible les tâches et gagner en efficacité et en simplicité de traitement des données.
Je vais essayer de détailler par étapes comment ça se passe
Je reçois des fichiers de classements par les chronométreurs : Fichiers pas optimisés du tout. (format XLS par défaut et données brutes extraites du logiciel de chronométrage) ce qui occasionne pas mal de retouches
Je dois les retravailler pour modifier les choses suivantes
- Le temps est en format texte, que je retravaille avec une formule TEMPS (ca fonctionne bien je crée une colonne et masque le format texte)
- Je dois récupérer le sexe de chaque participant (je crée une colonne Sexe et avec la fonction STEXT je récupère le sexe à partir de la colonne catégorie.)
Je dois attribuer les points aux coureurs du département en fonction de la catégorie de la course, de leur département et de leur sexe. La formule fonctionne bien actuellement (Merci encore à Baroute78 qui m'a bien aidé sur ce coup là :-)
- J'insère donc cette formule de calcul qui filtre les participants du département 77 H et F à partir des colonnes département et sexe. Elle attribue également les points en fonction de la catégorie de la course grâce au petit tableau qui est en orange. A la réflexion, on doit pouvoir simplifier ça et récupérer le sexe directement de la colonne catégorie.
Tout ceci représente pas mal de travail en amont avant de pouvoir être exploité.
Là j'en suis à l'étape ou je tente d'obtenir le classement par équipe à partir des données que j'ai énumérée (5 coureurs tout sexe confondus uniquement pour les clubs du 77. Il me faudra également réaliser un classement des clubs femmes basé cette fois sur 3 coureuses uniquement.
Les points par équipe sont attribués en fonction de la catégorie de la course
Course A+ 12 11 10 9 8 7 6 5 4 3 2 1
Course A 10 9 8 7 6 5 4 3 2 1 - -
Course B+ 10 9 8 7 6 5 4 3 2 1 - -
Course B 8 7 6 5 4 3 2 1 - - - -
Course V + 12 11 10 9 8 7 6 5 4 3 2 1
Course V 10 9 8 7 6 5 4 3 2 1 - -
Course R + 12 11 10 9 8 7 6 5 4 3 2 1
Course R 10 9 8 7 6 5 4 3 2 1 - -
Une fois ceci fait sur mon fichier, il me faut exporter les données pour alimenter le classement général et le mettre à jour tout au long de la saison via PowerQuery qui me semble t'il devrait nous faire gagner pas mal de temps à l'avenir.
J'essaie au plus possible de comprendre par moi-même ce que je met en place (par exemple à partir du classement par équipe sur 5 coureurs pouvoir faire le classement des clubs femmes et comprendre comment cela fonctionne.)
Je remets mon fichier exemple.
Voilà j'espère avoir été le plus clair possible sur les besoins et je reste à disposition si besoin de détails.
-
Hello,
Une proposition pour les tout sexe confondus. Ce sera le même principe pour les femmes.
Ajout d'une table de correspondance pour les points par équipe en fonction du type de course qui sera renseigné.
Ajout dans un tableau du type de la course pour l'appeler dans PowerQuery
N'hésite pas si besoin
@+
RE
Je reçois des fichiers de classements par les chronométreurs : Fichiers pas optimisés du tout. (format XLS par défaut et données brutes extraites du logiciel de chronométrage) ce qui occasionne pas mal de retouches...
PowerQuery peut synthétiser des xls et y apporter les conversions et autres calculs...
Il faut donc repartir du début
Merci à tous les deux pour vos retours.
Baroute78 . Pour le tableau c'est bien ce que veux obtenir mais je n'arrive pas à comprendre dans PQ comment tu arrives au résultat.
78 Chris: J'ai cru comprendre qu'en passant par PowerQuery je pourrais m'économiser tous les ajouts de colonnes et de formats que je fais actuellement manuellement.
Malheureusement c'est là que je montre mes limites sur excel et encore plus sur PQuery.
Hello,
Il va falloir être plus spécifique sur les traitements que tu ne comprends pas.
PowerQuery peut littéralement tout faire et généralement c’est mieux de partir de l’extraction brute ce sera plus simple.
Si jamais tu peux nous la mettre en PJ avec la liste des traitements que tu veux faire ce serait top.
@+
RE
Il faut
- vérifier que tous les xls sont structurés de la même manière
- poster un ou deux xls représentatifs
- préciser
- toutes les règles de calcul des points et des classements
- les synthèses attendues
Ainsi on saura d'où on part, où on doit arriver et les étapes. Finalement comme pour une course....
EDIT : au moins on est d'accord...
RE
Je me rends compte de la difficulté d'adapter l'existant, le fichier étant brut de chez brut
Je vous poste 3 fichiers tels que je les reçois. dont une épreuve classante
Ces fichiers comportent en général la colonne C qui est vide cela viendrait du logiciel de chronométrage.
Ces fichiers peuvent comporter une colonne supplémentaire lorsque l'épreuve est classante au niveau fédéral
A partir de ces fichiers, il faut
Convertir le temps pour pouvoir l'additionner pour le classement par équipe
Définir la catégorie de la course. Elles sont au nombre de 8 (A,B,V, P et A+,B+, V+, P+ pour ce qui est des championnats départementaux)
C'est la catégorie qui fera le nombre de points à attribuer.
*extrait du règlement*
CLASSEMENTS INDIVIDUELS
Il est établi 2 classements séparés suivant les différentes catégories d'âge et de sexe:
FEMMES (toutes catégories - de Cadettes à Masters)
HOMMES (toutes catégories - de Cadets à Masters)
L'attribution des points se fait de la façon suivante (H et F) en individuel.
CAT A et V 50 points au 1er puis 49, 48, 47 etc puis 1 point jusqu'au 200 ème
CAT B 40 points au 1er puis 39,38,37 etc puis 1 point jusqu'au 200 ème
CAT P 30 points au 1er puis 29,28,27 etc puis 1 point jusqu'au 200 ème
Pour les championnats (catégorie "+" ) un bonus de 20 points est appliqué quelque soit la catégorie de la course ce qui donne.
CAT A+ et V+ 70points
CAT B+ 60points
CAT P+ 50points
L'attribution de points ne s'applique qu'aux licenciés du 77 , il faut donc filtrer ceux qui sont hors 77 et les "sans club".
A l'issue de la saison
Les 13 meilleurs résultats sont retenus pour établir ces classements, à savoir :
Les 4 meilleurs résultats en catégorie A
Les 4 meilleurs résultats en catégorie B
Les 4 meilleurs résultats en catégorie V
Le meilleur résultat en catégorie P
Voilà pour la partie classements individuels
Il faut également classer les équipes selon les règles suivantes
CLASSEMENTS CLUBS du 77
CLUBS (sur 5 athlètes du même club licenciés, toutes catégories, hommes ou femmes confondues)
CLUBS FEMININ (sur 3 femmes du même club, licenciées, toutes catégories confondues)
Ils sont réalisés à partir du cumul des temps. Une seule équipe par club.
Barême (idem H/F)
Course A+ 12 11 10 9 8 7 6 5 4 3 2 1
Course A 10 9 8 7 6 5 4 3 2 1 - -
Course B+ 10 9 8 7 6 5 4 3 2 1 - -
Course B 8 7 6 5 4 3 2 1 - - - -
Course V + 12 11 10 9 8 7 6 5 4 3 2 1
Course V 10 9 8 7 6 5 4 3 2 1 - -
Pour les relais (1 seule course dans l'année pour le moment) Elle est automatiquement championnat donc barême R+
Course R + 12 11 10 9 8 7 6 5 4 3 2 1
Course R 10 9 8 7 6 5 4 3 2 1 - -
Toutes ces tâches se faisaient jusqu'alors en manuel avec un bénévole qui passait un certain de temps à traiter les fichiers. Ce bénévole n'étant plus là, et étant le seul à maitriser le système tel qu'il l'avait mis au point, il nous repenser ce qu'il avait fait pour tenter si ce n'est de simplifier au moins d'automatiser le plus possible le traitement de ces données.
Rien qu'à lister l'ensemble du cahier des charges je me dis que c'est un challenge dans le challenge que de remettre quelque chose sur pied à partir des fichiers que l'on nous communique.
Voilà je pense ne rien avoir oublier en route, vous avez l'étendu du problème. La finalité sera d'intégrer les fichiers des chronométreurs afin que PowerQuery y applique les différentes données que j'ai énuméré.
Je vous remercie du temps consacré à essayer de m'éclairer :-)
RE
Pas compris si les relais s'intègrent dans le reste ou pas...
A partir des 3 fichiers rangés dans un même dossier dont le chemin est en jaune dans l'onglet Références, j'ai listé
- les Courses,
- les points individuels
- le classement par Club (tous)
- le classement par Club femmes
Vérifie déjà cela en modifiant la cellule jaune puis Données, Actualiser Tout
Au préalable
Lancer PowerQuery, puis Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité
Cela évitera une erreur venant de la récupération du chemin.
Cette option par défaut de niveau de confidentialité ne sert à rien dans 99% des cas sauf à créer des problèmes
EDIT : je supprime mes participations : cros post après tout le boulot fourni ici, fallait oser...
RE 78chris
J'ai remplacé mon chemin d'accès et mis à jour , ça m'a pris en compte toutes les courses de mon dossier :-)
Ce qui m'évite au passage d'avoir un onglet par course dans mon tableur
J'ai remarqué que les féminines obtenaient toutes le maximum de points dans les classements.
Pour la mise en forme de mes classements H F et équipes, je cherche comment avoir un tableau récapitulatif avec le nom de mon coureur ainsi que la liste de ses courses sur une seule ligne un peu dans le format que tu m'avais fait précédemment. Je suis preneur d'explications pour trouver comment on fait :-)
En tout cas merci pour le boulot accompli parce que l'on part vraiment de loin ! On tient le bon bout !
Ce qui est sûr c'est qu'une fois à jour, ce format va nous faire gagner un temps considérable pour effectuer les classements !
Bonjour
Pour ce premier jet, je n'ai pas cherché à savoir quelles colonnes conserver et dans quel ordre
Il faut maintenant affiner cela mais si la logique de ce que j'ai fourni est bonne, c'est vite fait...
Fait une liste des tableaux nécessaires et, pour chaque, des colonnes dans l'ordre voulu : j''expliquerai les modifs à apporter ou ferai selon la difficulté
RE
La logique est effectivement la bonne les coureurs ont leur point qui sont bien attribués a chaque épreuve , les équipes sont bien classées par temps avec le nombre de coureurs pour composer une équipe. Les relais ne s'intègrent pas au classement général.
Comme tu le dis maintenant il faut affiner pour établir un classement général et le classement par catégorie d'âges Hommes et Femmes
Et procéder au classement des équipes.
J'ai créer 3 fichiers pour répercuter les différents classements. l'idée est que les courses s'y placent au fur et à mesure au long de la saison lorsque l'on charge les mises à jour.
J'ai rempli un peu le classement équipe de manière aléatoire pour voir à quoi ca ressemble.
Je te les joint dis moi si ça convient.
Pour résumer, si j'ai bien compris pour cette nouvelle saison.
Je place mes fichiers bruts reçus des chronométreurs dans mon dossier (chemin d'accès dans la cellule jaune)
J'ouvre mon fichier qui traite l'information en faisant actualiser tout.
J'ai bon ?
"un jour je serai grand"....sur PowerQuery
Encore merci
RE
Je vois cela en fin de journée ou ce week end...
Bonjour
Dans la mesure on on ne garde que le 77 pourquoi une colonne département ?
Comment établir le lien entre le nom des onglets et le contenu des fichiers?
La Catégorie étant différente selon les courses, cette colonne ne peut être renseignée sur une unique ligne affichant N courses.
Pour les clubs on prend les points de tous leurs participants ?
Bonjour
Dans ma première mouture je me servais de la colonne département comme condition d'attribution des points avec une formule SI.
Elle reste utile pour confirmer que le club est bien un club FFA et n'est pas par exemple un club entreprise, un club d'une autre fédération ou une équipe de copains "Team apéro"
Pour le nom des onglets j'avais pensé à me servir de la colonne catégorie ( SEF, M0H, etc etc) des courses pour les répercuter dans le fichier catégories des tableaux que je t'ai fait parvenir. c'est pourquoi j'ai nommé les onglets sur la base des catégories en vigueur.
Pour les catégorie de courses dans le tout premier fichier que tu m'avais envoyé j'avais pour chaque onglet un tableau excel correspondant a chaque classement à travailler. La formule si que j'utilisais attribuait le bon nombre de points en fonction de la lettre de la catégorie de la course.
Je le remet ici
Le modèle était bien ficelé et en un coup d'oeil on pouvait lister qui avait fait quelle course et combien de points il avait obtenu à chacune d'entre elle.
L'idée serait d'arriver à cela avec en plus les catégories d'âges et le classement équipe. ^^
"Si j'avais su dans quoi je m'embarquais"...
RE
Dans ma première mouture je me servais de la colonne département comme condition d'attribution des points avec une formule SI.Elle reste utile pour confirmer que le club est bien un club FFA et n'est pas par exemple un club entreprise, un club d'une autre fédération ou une équipe de copains "Team apéro"
Mais comme ces personnes sont automatiquement exclues pour calculer les points ça sera forcément 077...
Pour le nom des onglets j'avais pensé à me servir de la colonne catégorie ( SEF, M0H, etc etc) des courses pour les répercuter dans le fichier catégories des tableaux que je t'ai fait parvenir. c'est pourquoi j'ai nommé les onglets sur la base des catégories en vigueur.
Ce n'est pas la catégorie du coureur la colonne catégorie / année naissance ? Tu veux donc ventiler les résultats par catégorie de coureur ?
Pour les catégorie de courses dans le tout premier fichier que tu m'avais envoyé j'avais pour chaque onglet un tableau excel correspondant a chaque classement à travailler. La formule si que j'utilisais attribuait le bon nombre de points en fonction de la lettre de la catégorie de la course.
Je le remet ici
Les onglets portent le noms des courses dans le fichier joint...
Un seul onglet et un segment permettrait de visualiser une course à la fois... : personne ne regarde simultanément 30 onglets
Le modèle était bien ficelé et en un coup d'oeil on pouvait lister qui avait fait quelle course et combien de points il avait obtenu à chacune d'entre elle.
L'idée serait d'arriver à cela avec en plus les catégories d'âges et le classement équipe. ^^
"Si j'avais su dans quoi je m'embarquais"...
Tu n'as pas répondu aux 2 autres questions...
Une fois compris ce que tu veux, je t'expliquerais comme découper les bases complètes (individuelles et clubs) en 50 000 ventilations ou comment ventiler par TCD...